Java (JDBC connection to the database) [encapsulate Statement]

[Java]
Package com. iflytec. ex02;
 
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. ResultSetMetaData;
Import java. SQL. SQLException;
Import java. SQL. Statement;
Import java. util. ArrayList;
Import java. util. HashMap;
Import java. util. List;
Import java. util. Map;
 
/**
* Data Connection
*
* @ Author iflytek
* @ Version 1.0
*/
Public class ConnectionDB {
/**
* Database Driver Class Name
*/
Private static final String DRIVER = "com. microsoft. sqlserver. jdbc. SQLServerDriver ";
 
/**
* Connection string
*/
Private static final String URLSTR = "jdbc: sqlserver: // localhost: 1433; databaseName = Northwind ";
 
/**
* User Name
*/
Private static final String USERNAME = "sa ";
 
/**
* Password
*/
Private static final String USERPASSWORD = "111111 ";
 
/**
* Create a database connection class
*/
Private Connection connnection = null;
 
/**
* Create operation object
*/
Private Statement statement = null;
 
/**
* Create a result set object
*/
Private ResultSet resultSet = null;

Static {
Try {
// Load the database driver
Class. forName (DRIVER );
} Catch (ClassNotFoundException e ){
System. out. println ("loading driver error ");
System. out. println (e. getMessage ());
}
}
 
/**
* Establish a database connection
*
* @ Return
*/
Public Connection getConnection (){
Try {
// Obtain the connection
Connnection = DriverManager. getConnection (URLSTR, USERNAME,
USERPASSWORD );
} Catch (SQLException e ){
System. out. println (e. getMessage ());
}
Return connnection;
}
 
/**
* Unified insert update delete Method
*
* @ Param SQL
* Insert, update, and delete SQL statements
* @ Return: Number of affected rows
*/
Public int executeUpdate (String SQL ){
Int affectedLine = 0;
Try {
// Obtain the connection
Connnection = this. getConnection ();
 
// Create an object
Statement = connnection. createStatement ();
 
// Execute the SQL statement
AffectedLine = statement.exe cuteUpdate (SQL );
} Catch (SQLException e ){
System. out. println (e. getMessage ());
} Finally {
// Release resources
CloseAll ();
}
Return affectedLine;
}
 
/**
* Database query method
*
* @ Param SQL statement
*
* @ Return result set
*/
Private ResultSet executeQueryRS (String SQL ){
Try {
// Obtain the connection
Connnection = this. getConnection ();
 
// Create a Statement object
Statement = connnection. createStatement ();
 
// Execute the SQL statement
ResultSet = statement.exe cuteQuery (SQL );
} Catch (SQLException e ){
System. out. println (e. getMessage ());
}
Return resultSet;
}

/**
* Obtain the result set and put the result in the List.
* @ Param SQL SQL statement
* @ Return List result set
*/
Public List <Object> excuteQuery (String SQL ){
ResultSet rs = executeQueryRS (SQL );
ResultSetMetaData rsmd = null;
Int columnCount = 0;
Try {
Rsmd = rs. getMetaData ();
} Catch (SQLException e2 ){
System. out. println (e2.getMessage ());
}
Try {
ColumnCount = rsmd. getColumnCount ();
} Catch (SQLException e1 ){
System. out. println (e1.getMessage ());
}

List <Object> list = new ArrayList <Object> ();

Try {
While (rs. next ()){
Map <String, Object> map = new HashMap <String, Object> ();
For (int I = 1; I <= columnCount; I ++ ){
Map. put (rsmd. getColumnLabel (I), rs. getObject (I ));
}
List. add (map );
}
} Catch (SQLException e ){
System. out. println (e. getMessage ());
} Finally {
CloseAll ();
}

Return list;
}

 
/**
* Release all resources
*/
Private void closeAll (){
// Release the result set connection
If (resultSet! = Null ){
Try {
ResultSet. close ();
} Catch (SQLException e ){
System. out. println (e. getMessage ());
}
}
 
// Release the declarative connection
If (statement! = Null ){
Try {
Statement. close ();
} Catch (SQLException e ){
System. out. println (e. getMessage ());
}
}
 
// Release the database connection
If (connnection! = Null ){
Try {
Connnection. close ();
} Catch (SQLException e ){
System. out. println (e. getMessage ());
}
}
}
}

Author: haifengzhilian