Alex tech

Transaction isolation level is a value that determines the level at which inconsistent data is allowed in a transaction, that is, the degree of isolation of one transaction from another. A higher level of isolation improves the accuracy of the data, but it can reduce the number of concurrent transactions. On the other hand, a lower level of isolation allows more concurrent transactions, but reduces the accuracy of the data. During the use of transactions, to ensure data integrity, the DBMS uses locks to block other access to the data involved in the transaction. Such locks are necessary to prevent: "dirty" read - reading data, added or modified by a transaction, which later will not be confirmed (rolled back); non-repeatable read - when re-reading within one transaction, previously read data is changed; phantom reads - a situation when, when reading repeatedly within the same transaction, the same sample gives different sets of rows. Transaction isolation levels are de...

Registration of drivers; Establishing a connection to the database; Creation of query(s) to the database; Executing the query(s) to the database; Processing the result(s); Closing the connection to the database. Register the JDBC driver Driver registration can be done in several ways: java.sql.DriverManager.registerDriver(%driver class object%) Class.forName("fully qualified driver class name").NewInstance() Class.forName("fully qualified driver class name") Establish a connection to the database The java.sql.DriverManager.getConnection(...) static call is used to establish a connection to the database. The parameter can be passed: Database URL static Connection getConnection(String url) Database URL and set of properties to initialize static Connection getConnection(String url, Properties info) Database URL, username and password static Connection getConnection(String url, String user, String password) As a result of the cal...

JDBC has two parts: JDBC API, which contains a set of classes and interfaces that define access to databases. These classes and methods are declared in two packages - java.sql and javax.sql; JDBC driver, a component specific to each database. JDBC turns API calls into native commands for a particular database server. List the main JDBC classes and interfaces java.sql.DriverManager - Lets you download and register the required JDBC driver, and then get a connection to the database. javax.sql.DataSource - solves the same tasks as DriverManager, but in a more convenient and versatile way. There are also javax.sql.ConnectionPoolDataSource and javax.sql.XADataSource whose job it is to provide connection pooling. java.sql.Connection - Provides data source queries and transaction management. The javax.sql.PooledConnection and javax.sql.XAConnection interfaces are also provided. java.sql.Statement, java.sql.PreparedStatement, java.sql.CallableStatement - These interfaces allow you t...

JDBC, Java DataBase Connectivity (connection to databases in Java) - the industry standard for the interaction of Java applications with various DBMS. Implemented as the java.sql package included with Java SE. JDBC is based on the concept of drivers that allow you to get a connection to a database at a specially described URL. When loaded, the driver registers itself in the system and is then automatically called when the program requires a URL containing the protocol for which this driver is responsible. Benefits of using JDBC The advantages of JDBC are: Ease of development: the developer may not know the specifics of the database with which he works; The code practically does not change if the company moves to another database (the number of changes depends solely on the differences between the SQL dialects); There is no need to additionally install the client program; Any database can be connected via an easily descriptive URL. JDBC URL JDBC URL consists of: <protoco...

Stored Procedure Stored procedure is a database object that is a set of SQL statements stored on the server. Stored procedures are very similar to ordinary procedures in high-level languages, they can have input and output parameters and local variables, they can perform numeric calculations and operations on symbolic data, the results of which can be assigned to variables and parameters. Stored procedures can perform standard database operations (both DDL and DML). In addition, loops and branches are possible in stored procedures, that is, they can use instructions to control the execution process. Stored procedures improve performance, enhance programmability, and support data security features. In most DBMSs, the first time a stored procedure is run, it is compiled (parsed and a data access plan is generated) and further processing it faster. Trigger Trigger is a stored procedure of a special type, which the user does not call directly, but whose execution is conditioned by the ...

PRIMARY KEY is a set of fields (1 or more), the values of which form a unique combination and are used to uniquely identify a record in the table. Only one such constraint can be created for a table. This constraint is used to enforce the integrity of the entity that is described by the table. CHECK is used to restrict the set of values that can be placed in a given column. This constraint is used to ensure the integrity of the domain, which is described by the tables in the database. UNIQUE ensures that there are no duplicates in a column or set of columns. FOREIGN KEY protects against actions that could break relationships between tables. FOREIGN KEY in one table points to PRIMARY KEY in another. Therefore, this restriction is intended to ensure that there are no FOREIGN KEY entries that do not correspond to PRIMARY KEY entries. Differences between PRIMARY and UNIQUE constraints By default, the PRIMARY constraint creates a clustered index on a column, and UNIQUE creates a no...

IN - defines a set of values. SELECT * FROM Persons WHERE name IN ('Ivan','Petr','Pavel'); BETWEEN defines a range of values. Unlike IN, BETWEEN is order sensitive, and the first value in the sentence must be the first in alphabetical or numeric order. SELECT * FROM Persons WHERE age BETWEEN 20 AND 25; LIKE is applicable only to fields of type CHAR or VARCHAR with which it is used to find substrings. As a condition, wildcards are used - special characters that can match something: _ stands for any single character. For example, 'b_t' will match the words 'bat' or 'bit', but will not match 'brat'. % replaces a sequence of any number of characters. For example '%p%t' will match 'put', 'posit', or 'opt', but not 'spite'. SELECT * FROM UNIVERSITY WHERE NAME LIKE '%o'; UNION keyword In SQL, the UNION keyword is used to combine the results of two SQL queries int...