Java JDBC开发指导

01: Always get connection from Connection pool
Examples
import java.io.*;
public static void main(String[] arg)
{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/eBBS");
Connection con = ds.getConnection();
}
Rationale
Use Connection pool to get connection instead of DriverManager.getConeection .Since it is already having connections in pool, so it will take less time to get connection.

02: Always use Prepared statement instead of statement for executing Regular SQL queries (Not Dynamic Queries)
Examples
PreparedStatement st = con.prepareStatement(str_SQL);
Rationale
Prepared statements are precompiled statements and faster .It will create the execution package in DB level and reuse the same package when the same query executed multiple times.

03: Close All Connections, PreparedStatements, and Resultsets immediately once Execution /operations completes.
Examples
finally {
try {
if (resultset != null)
resultset.close();
if (preparestatement != null)
preparestatement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
throw new ApplicationException("SYS", "1000", e.getMessage()) }
Rationale
All Connections, Preparedstatements, Resultsets need to be close once the execution completes. Always Close Resultset first, then Prepared statements and Connections once the operation completes and also in Finally block at the end. This will avoid memory related issues and avoid Connection leakage related issues

04: Always specify a column list with in select/insert/Update/Delete SQL statements
Examples
String sqlQuery = "SELECT COUNTRYCODE, NAME, RISK FROM CNTRY "
Rationale
Always specify required column list with in select/insert/update/Delete statements (avoid "select *").

 

05:Use Isolation level appropriately
Examples
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED)
Rationale
Use the isolation level appropriately based on operations requirements. i.e. , if the Database connection requires operating only Selection operation then use Isolation level for only reading the values from DB which will output good performance.

 

06: Handle SQL related specific exceptions
Examples
catch (SQLException e) {
throw new ApplicationException("SYS", "1000", e.getMessage())
}
Rationale
Always handle the SQL related specific exceptions logic during performing Database operations like SQLException, BatchUpdateException.This will helpful to Debug for any exceptions by using Error messages and Reason codes.

 

07: Use Batch update / Batch insert instead of insert/update for multiple rows of data in same time
Examples
import java.io.*;
public static void main(String[] arg)
{
Connection conn = ds.getConnection();
Statement statement = conn.createStatement();
String insertQuery1 = "INSERT INTO ACCOUNT VALUES(01,XXX,'C')";
statement.addBatch(insertQuery1);
String insertQuery2 = "INSERT INTO ACCOUNT VALUES(01,YYY,'D')";
statement.addBatch(insertQuery2);
statement.executeBatch();
}
Rationale
Always use Batch Update /Insert instead of single update/insert for multiple rows of data hits in same table at same time (especially during batch operations for insert & update) , this will avoid the multiple database hits and provide Good response. Also handle Batch Exception handling to proper debugging.

 

08: Always Use Optimized query and minimize the size of Result set
Examples
import java.io.*;
public static void main(String[] arg)
{
pstmt = con.prepareStatement("SELECT id,name, age FROM CustInfo WHERE age > 10 ");
rs = pstmt.executeQuery();
}
Rationale
Always use optimized query before applying in program (Get suggestion from DataBase experts for new /Complex SQL queries) and make sure the result set will fetch only required data only.

09: Use appropriate data types instead of using ”string ” for all the data types.
Examples
import java.io.*;
public static void main(String[] arg)
{
rs.getDate(1);
rs.getBigDecimal(2);
rs.getInt(3)
}
Rationale
Please use appropriate data types instead using “string” for all the data types. If DB column is BigDecimal and if you are fetching this value use getBigDecimal to store the value. Also Use Date, Time, Timestamp objects as host Variables fields instead of Strings.

 

10: If Collection object using in program to store database values Nullify the object once the operation completes
Examples
HashMap hm = new HashMap();
pstmt = con.prepareStatement(SELECT_SQL);
rs = pstmt.executeQuery();
id= rs.getBigDecimal(1);
name= rs.getString(2);
hm.put(id,name);
hm.clear();
Rationale
If any collections object - Vector/Hashmap using to store Resultset values, please nullify that collection object once the operation is over. This will avoid the memory leakage issue

 

11: Avoid using Dynamic SQL Queries
Examples
String SELECT_SQL = “select systemdate from db2inst1.system ";
SELECT_SQL1 = SELECT_SQL + "where systemdate=?”
Rationale
Always avoid usage of Dynamic SQL Queries, Dynamic queries will form based on
concatenation “+” operation and which will cause performance issue.

 

12: Limit the usage of Platform specific features in SQL
Examples
String SELECT_SQL = “select systemdate from db2inst1.Account Fetch first 10 rows only";
Rationale
Always try to avoid usage of platform specific commands, it means the Query which we have written should be compatible to run at any database like DB2/ORACLE.

 

13: Always Use Column Number instead of Column Name for get Methods in Result sets.
Examples
String sqlQuery = "SELECT COUNTRYCODE, NAME, RISK FROM CNTRY ";
When fetching values from Resultset -rs use as below to get countrycode value.
rs.getString(1); instead of rs.getString(“COUNTRYCODE”);
Rationale
Use Column Number instead of Column name in result set get methods to get value of the SQL columns.

 

14: Avoid to use PreparedStatements inside loops
Examples
SQL1 = "SELECT COUNTRYCODE, NAME, RISK FROM CNTRY "
SQL2 = "SELECT EMPID,EMPNAME,DESG FROM EMP "
While(rs.next())
{
PreparedStatement emp_ps = con.prepareStatement(SQL2);
---
---
}
Rationale
Always avoid to write / execute PreparedStatements inside loops which will cause performance issues.

 

15: Limit the usage of Column functions (Aggregate/Sum/Count) in SQL
Examples
"SELECT SUM(AMOUNT) FROM SAL ";
"SELECT COUNT(NAME) FROM EMP ";
Rationale
Always try to restrict usage of Column functions (Aggregate/Sum/Count) in queries to avoid Performance/Portability issues

 

16: Use Stored Procedures using JDBC
Examples
CREATE PROCEDURE DB2INST1.MOVE_TO_HIST_RETTRN()
BEGIN
DECLARE r_stmt VARCHAR(1500);
DECLARE h_stmt VARCHAR(2500);
SET r_stmt = 'insert INTO db2inst1.RETTRNHIST (BATCHNO,BATCHENTRYDATE,SEQNO,CURRENCYCODE,ACCOUNTNO)select BATCHNO,
BATCHENTRYDATE,SEQNO,CURRENCYCODE,ACCOUNTNO from db2inst1.RETTRN where batchentrydate < (SELECT PREVWORKINGDATE FROM DB2INST1.SYSTEM)';
PREPARE Q1 FROM r_stmt;
EXECUTE Q1;
SET h_stmt = 'DELETE FROM DB2INST1.RETTRN WHERE BATCHENTRYDATE <
(SELECT PREVWORKINGDATE FROM DB2INST1.SYSTEM)';
PREPARE Q2 FROM h_stmt;
EXECUTE Q2;
END
Rationale
Stored procedures are ideal when there is a complex piece of business logic that needs to be performed involving a lot of database access.

 

事务的隔离级别（5个）:

ISOLATION_DEFAULT

ISOLATION_READ_UNCOMMITTED：会产生脏读，不可重复读和幻像读

ISOLATION_READ_COMMITTED：可以避免脏读出现，但是可能会出现不可重复读和幻像读

ISOLATION_REPEATABLE_READ：可以防止脏读，不可重复读。但是可能出现幻像读

ISOLATION_SERIALIZABLE：可以防止脏读，不可重复读外，还避免了幻像读

 

事务传播行为（7个）： 

PROPAGATION_REQUIRED ：如果存在一个事务，则支持当前事务。如果没有事务则开启一个新的事务;PROPAGATION_REQUIRED：应该是我们首先的事务传播行为。它能够满足我们大多数的事务需求。

PROPAGATION_SUPPORTS ：如果存在一个事务，支持当前事务。如果没有事务，则非事务的执行

PROPAGATION_MANDATORY ：如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常。

PROPAGATION_REQUIRES_NEW ：总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起。

PROPAGATION_NOT_SUPPORTED ：总是非事务地执行，并挂起任何存在的事务。

PROPAGATION_NEVER ：总是非事务地执行，如果存在一个活动事务，则抛出异常

PROPAGATION_NESTED ：如果一个活动的事务存在，则运行在一个嵌套的事务中. 如果没有活动事务, 则按TransactionDefinition.PROPAGATION_REQUIRED 属性执行