JAVA basics: JDBC optimizes database connections

JAVA basics: JDBC optimizes database connections-general Linux technology-Linux programming and kernel information. The following is a detailed description. The proper construction of enterprise database connectivity is very important, and the application is being built for the Connection Limited Device Configuration (CLDC), and the compilation is specific to MIDP (Mobile Interface) which of the following is the best choice between interfaces depends on the j2s and Java technologies.

Method
This article introduces a simple principle for establishing JDBC connections when connecting enterprise data to wireless mobile devices. This helps you make the solution more scalable and efficient.

Connection Pool
Any application must first access the active database connection before accessing the database. Database connection is a resource-consuming operation with high I/O overhead. If you want to create a database connection every time, it will become your performance bottleneck.

For example, if you use Java servlet (Java servlet is created by using the init () method and is destroyed at the end of its lifecycle (by using the destroy () method, although you avoid re-establishing a connection every time the servlet is instantiated. This method will significantly reduce the performance of the application. A better way to accomplish the same function is to use the Connection Pool. You can initialize multiple connections in the Connection Pool (and the parameters can be read from the XML configuration file ).

The connection itself consists of a set of objects and a user request that keeps the connection open throughout the request process. The key to creating a connection pool is to use the following blocks in the database access code: try {}... catch {}... finally {}... Then you use the close () method to confirm that the connection is indeed returned to the connection pool, rather than being completely closed. Specify the close () method in the "finally {}" block so that exceptions occurred during execution will be caught, and the statement is still executed-the connection is returned to the connection pool, this prevents "connection leak" in the application.

The following is an example of building a JDBC connection:

Connection con = null;
Try {ds = (DataSource) myContext. lookup ("");
PooledCon = ds. getConnection ("scott", "tiger ");
// Processing Code goes here
} Catch (Exception ignored ){
// Catch JNDI or JDBC exceptions here
} Finally {
If (pooledCon! = Null)
PooledCon. close ();
}

Use PreparedStatement
People think that the efficiency of a PreparedStatement object is higher than that of multiple Statement objects. Especially if you have to execute the same Statement multiple times, the difference is that the parameters are different. PreparedStatement allows you to "compile" an SQL statement once (although this compilation takes a lot of time for the first time) and save it in the cache for effective reuse. It also provides code with better readability.

Another additional advantage is the automatic escape of the string that the user passes to the statement completed by the driver. For example, this means that when you try to insert the string "D' Marco" into a character-based data field (which may be VARCHAR, VARCHAR2, CHAR, etc, SQL statements do not cause catastrophic failures when they encounter the first apostrophes.

Another good habit when using the PreparedStatement object is to call the close () method of the object to "close the object". This method will be used to run SQL statements. This will close any cursor associated with the SQL statement being executed, so as to prevent the opened cursor from messing up the database.

The following is an example of creating PreparedSatement:

PreparedStatement sqlstmt = dbCon. prepareStatement ("select *
From table1 where field_1 =? ");
Sqlstmt. setInt (1, 12 );
ResultSet rs = sqlstmt.exe cuteQuery ();
// Close the resultset statement to avoid hanging cursors in database
Sqlstmt. close ();
// Processing of new statement
Sqlstmt = dbCon. prepareStatement ("select * from table2 where field_2 =? ");
// Repeat creating the result set

Use transactions appropriately
When updating dynamic database tables and data, it is often forgotten that when updating or inserting data to a table that represents more than one logical transaction, this transaction should be reflected in all tables, or, when a transaction fails, the transaction is rolled back and is not reflected in every table.

Some core JDBC packages support the transaction isolation mode, which allows the program to specify the behavior they want the transaction to show. Most programs support at least two modes: read committed (default) and serializable )". When the non-repeated reads should allow modifications made by one transaction between multiple queries to be visible to another transaction, use "read commit "; to make changes made by another transaction visible during a query run, use phantom read (phantom read ). When you need a database view that is completely consistent across multiple operations, you should use a more rigorous "serializable" setting. It is useful to remember to set Automatic Connection submission to "false" (autocommit = "false.

The following is an example of building a connection and setting its attribute parameters:

Connection con = null;
Try {
Dtsr = (DataSource ");
PConn = dtsr. getConnection ("","");
PConn. setAutoCommit (false); // transaction are not committed uponm execution

PConn. setTransactionIsolation (
Connection. TRANSACTION_SERIALIZABLE );
// PConn is pooled connection
PConn. commit ();
} Catch (Exception ignored ){
Try {pConnn. rollback ();} catch (SQLException esgl ){}
} Finally {
If (pConn! = Null ){
PConn. setAutoCommit (true); // reset autocommit
PConn. close ();
}
}

You can also use an optional JDBC package-JTA (Java Transaction API), which allows easy integration with a completely independent Transaction Server.