Apache dbutils practices

1. Dao base class (database operation base class)
 
Here we use the layer superclass mode, code reuse, unified Exception Handling, logs, and so on ..
 
Basedao:
 
Package com. d1zhan;
 
Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. sqlexception;
Import java. util. List;
 
Import org. Apache. commons. dbutils. dbutils;
Import org. Apache. commons. dbutils. queryrunner;
Import org. Apache. commons. dbutils. Handlers. beanlisthandler;
 

Public class basedao {
/**
* Getting database connections
* @ Return
*/
Public connection getconnection (){
Connection conn = NULL;
String jdbcurl = "JDBC: mysql: // localhost/dbname ";
String jdbcdriver = "com. MySQL. JDBC. Driver ";
String user = "root ";
String Password = "root ";
Try {
Dbutils. loaddriver (jdbcdriver );
Conn = drivermanager. getconnection (jdbcurl, user, password );
} Catch (sqlexception e ){
// Handle the exception
E. printstacktrace ();
} Finally {
Dbutils. closequietly (conn );
}
Return conn;
}
 
/**
* Search for multiple objects
* @ Param sqlstring
* @ Param clazz
* @ Return
*/
Public list query (string sqlstring, class clazz ){
List beans = NULL;
Connection conn = NULL;
Try {
Conn = getconnection ();
Queryrunner qrunner = new queryrunner ();
Beans =
(List) qrunner. Query (
Conn,
Sqlstring,
New beanlisthandler (clazz ));
} Catch (sqlexception e ){
E. printstacktrace ();
} Finally {
Dbutils. closequietly (conn );
}
Return beans;
}
 
/**
* Search for objects
* @ Param sqlstring
* @ Param clazz
* @ Return
*/
Public object get (string sqlstring, class clazz ){
List beans = NULL;
Object OBJ = NULL;
Connection conn = NULL;
Try {
Conn = getconnection ();
Queryrunner qrunner = new queryrunner ();
Beans =
(List) qrunner. Query (
Conn,
Sqlstring,
New beanlisthandler (clazz ));
} Catch (sqlexception e ){
E. printstacktrace ();
} Finally {
Dbutils. closequietly (conn );
}
If (beans! = NULL &&! Beans. isempty () {// note this
OBJ = beans. Get (0 );

}

Return OBJ;
}
 
/**
* Execute the updated SQL statement, insert, modify, and delete
* @ Param sqlstring
* @ Return
*/
Public Boolean Update (string sqlstring ){
Connection conn = NULL;
Boolean flag = false;
Try {
Conn = getconnection ();
Queryrunner qrunner = new queryrunner ();
Int I = qrunner. Update (Conn, sqlstring );
If (I> 0 ){
Flag = true;
}
} Catch (sqlexception e ){
E. printstacktrace ();
} Finally {
Dbutils. closequietly (conn );
}
Return flag;
}
}
 
 
2. The database operation DAO class of the employee (employee)
 
 
As you can see, the following DAO class inherits basedao, and the method body is an assembly of SQL statements, so you don't have to worry about Exception Handling and Resource Management (connection, statement, etc.). This code is concise, high quality.
 
Employeedao:
 
Package com. d1zhan;
 
Import java. util. List;
 
Public class employeedao extends basedao {
 
/**
* The attributes, names, and addresses of searchmodel are query parameters.
* @ Param searchmodel
* @ Return
*/
Public list query (employee searchmodel ){
String SQL = "select * from employee where 1 = 1 ";
 
// If the employee name is not null, add the employee name to the where query condition.
If (searchmodel. getname ()! = NULL ){
SQL + = "and employee. name like '" + searchmodel. getname () + "'";
}
Return this. Query (SQL, employee. Class );
}
 
/**
* Modify employee information
* @ Param EMP
* @ Return
*/
Public Boolean edit (employee EMP ){
String SQL = "Update employee set"; // Note: Set is added out.
 
// If the name is not null, modify its value to the database.
If (EMP. getname ()! = NULL ){
SQL + = "employee. Name = '" + EMP. getname () + "',";
}
 
// If address is not null, modify its value to the database.
If (EMP. getaddress ()! = NULL ){
SQL + = "employee. Address = '" + EMP. getaddress () + "',";
}

SQL = SQL. substring (0, SQL. Length ()-1); // remove the last ","
SQL + = "where employee. ID =" + EMP. GETID ();
Return this. Update (SQL );
}
 
/**
* Delete an employee based on the employee ID
* @ Param ID
* @ Return
*/
Public Boolean Delete (int id ){
String SQL = "delete from employee where id =" + ID;
Return this. Update (SQL );
}
 
/**
* Search for employees by employee ID
* @ Param ID
* @ Return
*/
Public Employee get (int id ){
String SQL = "select * from employee where id =" + ID;
Return (employee) this. Get (SQL, employee. Class );
}
}
 
3. employee information
 
Employee:
Package com. PCM. netrender. model;

Public class employee {
Private int ID;
Private string name;
Private string address;
/**
* @ Return
*/
Public String getaddress (){
Return address;
}
 
/**
* @ Return
*/
Public int GETID (){
Return ID;
}
 
/**
* @ Return
*/
Public String getname (){
Return name;
}
 
/**
* @ Param string
*/
Public void setaddress (string ){
Address = string;
}
 
/**
* @ Param I
*/
Public void setid (int I ){
Id = I;
}
 
/**
* @ Param string
*/
Public void setname (string ){
Name = string;
}
 
}

4. Now we can call our DAO method at the service layer to implement business logic.
In service, we need to process business logic, database operation transactions, and so on.
Then struts action calls the service class method to complete the software.

The code is like this.

Employee EMP = new employee ();

EMP. setname ("test employee ");

EMP. setaddress ("Shenzhen Nanshan Science Park ");

Employeedao Dao = new employeedao ();

Dao. Save (EMP );