Shulou(Shulou.com)06/03 Report--

SQL statement execution process

When JDBC executes the database operation statement, the sql statement needs to be packaged into a network byte stream and passed to the database. The database is unpackaged, then the sql statement is compiled and finally executed, and then the result is returned to JDBC API in the form of byte stream.

To put it simply, it can be roughly divided into the following points:

JDBC package sql statement

Send a byte stream to the database

Database unpacking

Check the sql syntax and compile sql

Execute sql statement

Return the sql statement to the JDBC interface

Link steps for the database

Register driver (Driver)

Establish a connection (create Connection)

Create and execute sql statements (usually create Statement or its subclasses)

Execute statement

Process the execution result (in non-query statements, this step can be omitted)

Release related resources

Case

@ Test public void wholeExample () {try {/ / 1. Register the driver Class.forName ("com.mysql.jdbc.Driver"); / / 2. Get database connection Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/test", "root", "123456"); / / 3. Create the execution handle Statement stmt = conn.createStatement (); / / 4. Execute the sql statement ResultSet rs = stmt.executeQuery ("select * from user"); / / 5. Processing execution result while (rs.next ()) {System.out.println ("id:" + rs.getInt (1) + "\ tname:" + rs.getString (2) + "\ tbirthday:" + rs.getDate (3) + "\ tmoney:" + rs.getFloat (4));} / / 6. Release resource rs.close (); stmt.close (); conn.close ();} catch (SQLException e) {e.printStackTrace ();} catch (ClassNotFoundException e) {e.printStackTrace ();}} data type

CLOB

When we store a large amount of text information, the varchar or varchar2 in the database must not be satisfied. Varchar2 seems to have a maximum length of 4000, storing a very long article or a text message. We use the CLOB type.

BLOB

Access to binary files, such as pictures, audio, etc.

/ / stream file stream PreparedStatement.setBlob (index, stream) SQL data type and Java data type

Business

The transaction of the database is a mechanism to ensure the integrity of the data, in short, how to ensure that the execution of the data is either successful or failed.

ACID mechanism

Atomicity (atomicity): the statements that make up a transaction form a logical unit, and cannot execute only part of it

Consistency: the database is consistent before and after the transaction is executed (either both accounts change or remain the same)

Isolcation: one transaction has no effect on another transaction

Durability: the effect of a transaction can be permanently preserved

The transaction of Jdbc is turned on by default, that is, if each operation is performed, the transaction will be committed implicitly. Before throwing an exception, our change operation has been synchronized to the database.

Transaction control function

/ / commit transaction

Connection.commit ()

/ / rollback the transaction

Connection.rollback (); distributed transaction

The so-called cross-database transaction requires the transactions of several databases to be consistent in one application. JTA was born to solve this problem.

PreparedStatement interface

PreparedStatement will bring great convenience in database operation, reduce the trouble caused by spelling sql strings and prevent the occurrence of SQL injection.

PreparedStatement is a subclass of Statement

PreparedStatement is a preprocessing command Statement implementation of the CallableStatement interface

Call the stored procedure in the database in the database operation

Case: execute a stored procedure with parameters and return values

Create a stored procedure create or replace procedure test1 (in id integer,in name varchar (20), in money float,out counter integer) asbegin insert into user values (id,name,now (), money); select count (1) into counter from user; commit;end test1; write test code that you need to register @ Test public void callProcedureWithParamWithResult () throws SQLException {Connection conn = null; CallableStatement stmt = null when you need to return a value ResultSet rs = null; try {conn = ConnCreate.getConnection ("jdbc:mysql://localhost:3306/test", "root", "123456"); String sql = "{call test1 (?)}"; stmt = conn.prepareCall (sql); stmt.setInt (1,17) Stmt.setString (2, "test"); stmt.setFloat (3, 6000); stmt.registerOutParameter (1, Types.INTEGER); stmt.executeUpdate (); int counter = stmt.getInt (4); System.out.println (counter);} finally {ConnCreate.close (conn, stmt, rs);}} batch processing

/ / add a strip of sql

Stmt.addBatch (sql)

/ / execute batch processing

Stmt.executeBatch ()

Paging technology

Use SQL statements for paging (eg: limit of mysql, one offsize offset, another number of pagesize pages)

@ Test public void page () throws SQLException {page (100Power20);} static void page (int start,int total) throws SQLException {Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try {conn = ConnCreate.getConnection ("jdbc:mysql://localhost:3306/test", "root", "123456") String sql = "select * from user limit?,?"; stmt = conn.prepareStatement (sql); stmt.setInt (1, start); stmt.setInt (2, total); rs = stmt.executeQuery () While (rs.next ()) / / scroll down {System.out.println ("name:" + rs.getString (2) + "id:" + rs.getInt (1));}} finally {ConnCreate.close (conn, stmt, null);}} connection pool

One of the biggest expenses of using jdbc is to create a database. When we create a database frequently, it is bound to affect the efficiency of the application, or when there is a problem with the database shutdown, we can not release it immediately, for a longer time, the resources of the whole database will be exhausted by our application.

C3P0

DBCPResultSetMetaData

If we don't know how many columns of result sets are queried by one of our sql statements, and the column name, type and other information of each column, we should use ResultSetMetaData at this time.

Case @ Test public void resultMeta () throws SQLException {String sql= "select * from user"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try {conn = ConnFactory.getConnection (); stmt = conn.prepareStatement (sql); rs = stmt.executeQuery (); ResultSetMetaData rsmd = rs.getMetaData () Int count = rsmd.getColumnCount (); for (int item1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.