JDBC Concept Grooming

# # SQL statement Execution flow

> JDBC Executes the database operation statement, first needs to package the SQL statement into a network byte stream, pass it to the database, unpack the database, compile the SQL statement, execute it, and then return the result to the JDBC API in the form of a byte stream

In simple terms, it is broadly divided into the following points:

-JDBC Packaged SQL statements

-Send byte stream to database

-Database Unpacking

-Check SQL syntax, compile SQL

-Execute SQL statement

-Return SQL statements to the JDBC interface

# # Link steps for a database

-Registration Driver (Driver)

-Establish connection (create connection)

-Create an Execute SQL statement (usually create statement or its subclasses)

-Execute statement

-Process Execution results (this step can be omitted in a non-query statement)

-Release related Resources

> Cases

@Testpublic  void wholeexample () {    try {         //1. Register driver         class.forname ("Com.mysql.jdbc.Driver ");         //2. Get Database connection          Connection conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/test",  "root", " 123456 ");         //3. Create execution handle          statement stmt = conn.createstatement ();         / /4. Execute the SQL statement         resultset rs = stmt.executequery (" Select * from user ");         //5. Processing execution Results          while (Rs.next ()) {             system.OUT.PRINTLN ("ID:" +rs.getint (1) + "\tname:" +rs.getstring (2) + "\tbirthday:" +rs.getdate (3) + "\tmoney:" +rs.getfloat (4) );         }        //6. Releasing Resources         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 database of varchar or VARCHAR2 must not be satisfied, VARCHAR2 seems to have a maximum of 4,000 length, a long article or a text message, we use the CLOB type

# # BLOB

> Access to binary files, chips, audio and other information

```

Stream Stream File

Preparedstatement.setblob (index, stream)

```

# # Transactions

> Database transactions are a mechanism for ensuring data integrity, in short, how to ensure that data execution is either successful or fails

# # # acid mechanism

-Atomicity (atomicity): The statement that makes up the transaction forms a logical unit and cannot execute only part of it

-Consistency (consistency): The database is consistent before and after transaction execution (two accounts are either changed or unchanged)

-Isolation (isolcation): One transaction has no effect on another transaction processing

-Persistence (Durability): The effect of transaction processing can be permanently saved

The > JDBC transaction is open by default, that is, the transaction is committed implicitly, and before the exception is thrown, our change operation is synchronized to the database.

# # # Transaction control function

Commit a transaction

-Connection.commit ();

Rolling back a transaction

-Connection.rollback ();

# # Distributed Transactions

> commonly referred to as cross-library transactions, requiring several database transactions to be consistent in one application, JTA was born to solve this problem

# # PreparedStatement Interface

> PreparedStatement in database operations can be a great convenience, reducing the hassle of spelling SQL strings and preventing SQL injection from occurring.

-PreparedStatement is a subclass of statement

-PreparedStatement is a statement implementation of a preprocessing command

# # CallableStatement Interface

> Call a stored procedure in a database in a database operation

> Case: Executing a stored procedure with parameters and return values

-Create a stored procedure

Create or Replace procedure test1 (in ID integer,in name varchar (a), in Money float,out counter integer) Asbegininsert into User values (Id,name,now (), Money), select COUNT (1) to counter from User;commit;end test1;

-write test code that needs to be registered when the value needs to be returned

@Testpublic  void callprocedurewithparamwithresult ()  throws SQLException{     Connection conn = null;    CallableStatement stmt =  null;    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 section of SQL

-Stmt.addbatch (SQL)

Perform batch processing

-Stmt.executebatch ()

# # Pagination Technology

> Paging with SQL statements (Eg:mysql limit??, one offsize offset, and another pagesize number of pages)

@Testpublic  void page ()  throws sqlexception{    page (100,20);} 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);   &nbsP;     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 costs of using JDBC is to create a database, when we create the database frequently, it will affect the efficiency of the application, or in the event of a database shutdown problem, we can not be released immediately, a long time, the entire database of resources would be exhausted by our application

-C3P0

-DBCP

# # ResultSetMetaData

> If we do not know that one of our SQL statements queries the result set of several columns, and the column name, type, etc. of each column, this time we should use ResultSetMetaData

# # # Case

@Testpublic  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 i=1;i<=count;++i) {             system. Out.println ("Type:" +rsmd.getcolumntype (i));             system.out.println ("ColumnName:" +rsmd.getcolumnname (i));             system.out.println ("Columnlable:" +rsmd.getcolumnlabel (i));         }    } finally{         connfactory.close (conn, stmt, rs);     }}

This article is from the "MU" blog, be sure to keep this source http://manunited1985.blog.51cto.com/13432434/1982268

JDBC Concept Grooming