Basic JDBC Theory and Practice

This work is licensed using the "knowledge sharing signature"-"non-commercial use"-2.5 mainland China License Agreement in the same way.

 

 

In Java technology, JDBC is the basis for all database operations. Whether it is ibatis or hibernate, its foundation is all from JDBC. Having mastered JDBC makes it easy to understand the general library.

I. Database Connector

Database Connector(Java. SQL. Connection) is the core of JDBC operations. A complete database operation starts with the Database Connector.

There are multiple ways to create a database connector. There are two basic methods: The drivermanager method and the datasource method. Among them, drivermanager is the most primitive connection method. datasource unifies the interface and completes more tasks through different implementations. For example, Apache DBCP provides a powerful buffer pool technology based on datasource, this greatly improves the speed of creating database connectors.

When creating a database connector, you must provide four basic data:

JDBC driver: driverclassname
Database Connection word: URL
Username required for database access: Username
Password required for database access: Password

Where

The JDBC driver value can be:

MySQL: "com. MySQL. JDBC. Driver"
PostgreSQL: "org. PostgreSQL. Driver"
ORACLE: "oracle. JDBC. Driver. oracledriver"
SYBASE: "com. Sybase. jdbc2.jdbc. sybdriver"
Sqlserver: "com. Microsoft. JDBC. sqlserver. sqlserverdriver"
DB2: "com.ibm.db2.jdbc.net. db2driver"

The database connection word value can be:

MySQL: "JDBC: mysql: // dbcomputernameorip: 3306/dbname"
PostgreSQL: "JDBC: PostgreSQL: // dbcomputernameorip: 3306/dbname"
ORACLE: "JDBC: oracle: thin: @ dbcomputernameorip: 1521: dbname"
SYBASE: "JDBC: Sybase: dbname: dbcomputernameorip: 2638"
Sqlserver: "JDBC: Microsoft: sqlserver: // dbcomputernameorip: 1433; databasename = dbname"
DB2: "JDBC: DB2: // dbcomputernameorip/dbname"

The code for creating a database connector using drivermanager is as follows:

Try {<br/> // load the JDBC driver <br/> class. forname (driverclassname); <br/> // create a database connection <br/> connection conn = drivermanager. getconnection (<br/> URL, username, password); <br/>} catch (classnotfoundexception E) {<br/> // driver loading failed <br/>} catch (sqlexception e) {<br/> // failed to establish database connection <br/>}

The code for creating a database connector using datasource is as follows:

// Create a data source <br/> Org. apache. commons. DBCP. basicdatasource = <br/> New Org. apache. commons. DBCP. basicdatasource (); <br/> // set basic attributes <br/> datasource. setdriverclassname (driverclassname); <br/> datasource. seturl (URL); <br/> datasource. setusername (username); <br/> datasource. setpassword (password); <br/> try {<br/> // create a Database Connector <br/> connection conn = datasource. getconnection (); <br/>}catch (sqlexception e) {<br/> // Database Connector creation failed <br/>}

Ii. SQL statement

In a Database Connector, we can call multiple SQL statements. Each SQL statement is executed bySQL statement(Java. SQL. Statement) completed.

The Database Connector has three methods to declare SQL: general statements, pre-compilation statements, and stored procedure statements. The essential difference between several declarations lies in the timing specified by the SQL statement. Generally, the statement specifies the SQL statement during execution, and the other two statements specify the SQL statement at creation.

The SQL statement can be executed in two ways: the executequery Method Based on search, used for select statements, and the change-based executeupdate Method for insert, update, and delete statements. It also provides a more universal execute method.

General statement

The code for creating a general declaration is as follows:

// Create a general statement <br/> statement state = conn. createstatement ();

 

It is generally declared that a query statement (including select) is executed through executequery, And the execution result of the query statement isResult set(Java. SQL. resultset. The Code is as follows:

String querysql = "Select..."; <br/> resultset rs = state.exe cutequery (querysql );

The above code can also be completed as follows:

String querysql = "select... "; <br/> Boolean success = state.exe cute (querysql); <br/> If (SUCCESS) {<br/> resultset rs = state. getresultset (); <br/>}

 

It is generally declared that an update Statement (including update, insert, and delete) is executed through executeupdate. The number of elements affected by the update operation determines whether the statement is successful or not. The Code is as follows:

String updatesql = "Update..."; <br/> int result = state.exe cuteupdate (updateqql );

The above code can also be completed as follows:

String updatesql = "update... "; <br/> Boolean success = state.exe cute (updatesql); <br/> If (SUCCESS) {<br/> int result = state. getupdatecount (); <br/>}

 

Pre-compiled statement

Unlike general statements, pre-compiled statements are used to pre-process SQL statements before execution. The greatest use of pre-compiled statements is that SQL statements can be parameterized. When creating a pre-compilation declaration, you must specify an SQL statement. for uncertain parameters in the statement, use "?" Parameters are set by a series of Set Methods Based on the serial number (starting with 1. The execution of the pre-compilation declaration is similar to that of the general declaration, but no parameters are provided. The sample code is as follows:

// Create an SQL statement with parameters. Use "?" . <Br/> stringbuffer SQL = new stringbuffer (); <br/> SQL. append ("insert into t_doc001") <br/>. append ("(doc_id, lang_type, doc_name, pub_date, source, summary)") <br/>. append ("values (?, ?, ?, ?, ?, ?) "); <Br/> // create a pre-compilation statement <br/> preparedstatement prestate = Conn. preparestatement (SQL. tostring (); <br/> // set the parameter value by the serial number (starting from 1) <br/> prestate. setstring (1, docid); <br/> prestate. setint (2, langtype); <br/> prestate. setstring (3, docname); <br/> prestate. setdate (4, pubdate); <br/> prestate. setblob (5, source); <br/> prestate. setstring (6, summary); <br/> // execute the SQL statement <br/> // The execution method is similar to the general declaration, but no parameters are provided. <Br/> prestate.exe cuteupdate ();

 

Stored Procedure statement

Stored procedures are database technologies. stored procedures are directly stored in the database and optimized by the database. The Database Connector uses preparecall to create a stored procedure declaration. The Code is as follows:

// Create a stored procedure declaration <br/> callablestatement callstate = <br/> conn. preparecall ("{? = Call <procedure-Name> [(<arg1>, <arg2>,...)]} ");

 

For detailed usage of stored procedure statements, refer:
Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0802tiwary/

 

Iii. Transaction Processing

Multiple SQL statements can form a transaction based on different services. In a transaction, all SQL statements or all statements are successfully executed or not executed. Simply put, a transaction is to divide one thing into several steps, and make sure that all the steps are completed or not done. There is no possibility that only a few steps are performed.

In JDBC, transactions are processedDatabase ConnectorThe smallest unit of transaction processing isSQL statement. There are two types of transactions in the Database Connector: commit and rollback. Commit immediately takes effect for SQL statement execution, while rollback does not.

Depending on the business, the Database Connector provides two transaction processing policies: automatic submission and manual submission. By default, the transaction processing policy of the Database Connector is automatic commit. In the automatic commit mode, commit and rollback do not have to be manually called. JDBC provides an independent transaction for each SQL statement. When executing each statement, it commit immediately and automatically rollback when an error occurs. This traditional commit method cannot fulfill our requirements for transactions, so we must manually commit the transaction. In the manual submission mode, you must call the commit and rollback methods at the appropriate location based on the business logic. The code for setting the transaction processing policy of the Database Connector to manual submission is as follows:

// Set the Database Connector to manual submission mode <br/> Conn. setautocommit (false); <br/> try {<br/> // execute the first SQL statement <br/>{< br/> conn.exe cute (...); <br/>}< br/> // execution of the second SQL statement <br/>{< br/> conn.exe cute (...); <br/>}< br/> // manually submit the transaction <br/> Conn. commit (); <br/>} catch (sqlexception e) {<br/> // when a database error occurs, the transaction will be rolled. <Br/> conn. rollback (); <br/>}