JDBC Encapsulation of database connections

1.BaseDao: Abstract base class, which defines the underlying database operation methods used to open the connection, get statement, execute SQL, and close the resource.

2.i***dao: An interface that operates on a specified data table: An abstract method that defines the operation data table.

3***daoimpl: Specific tool class. Implement I***dao interface, inherit Basedao abstract class. The abstract interface defined in the I***dao interface is implemented using the method in Basedao.

The base class is used to provide the underlying method for accessing the database, and the interface defines the method used to manipulate a data table. And the concrete class inherits the base class to be able to access the database, implements the interface can use the interface to manipulate the database.

This makes the dependency on the class stay at the interface level.

The following is implemented using the Cusinfo table in the Oracle database as the Target data table:

1.BaseDao abstract base class

Package Org.oracal.dao;

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;
Import java.util.List;

/*
* @author: Guan yunqing
* @time: August 5, 2014
* @todo: The underlying DAO tool class
* @version: v1.0
*/

/**
* @author: Guan yunqing
*
*/
Public abstract class Basedao {

Private final static String driver= "Oracle.jdbc.driver.OracleDriver";
Private final String url= "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL";
Private final String name= "Gsuser";
Private final String passw= "Gsuser";
private Connection con = null;
Private PreparedStatement pstmt = null;
Private ResultSet set = NULL;

/**
* Load Database Driver
*
*/
static {
try {
Class.forName (DRIVER);
} catch (ClassNotFoundException e) {
E.printstacktrace ();
}
}

Private Connection getconnection () {
Connection con =null;
try {
con = drivermanager.getconnection (URL, NAME, PASSW);
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
return con;
}
/**
* @param sql:select statement
* @param params parameter
* @return:
*/
public int executeupdate (String sql, list<object> params) {
int i = 0;
This.con = This.getconnection ();
try {
this.pstmt = con.preparestatement (sql);
Add params
if (params!=null) {
for (Int J =0;j<params.size (); j + +) {
This.pstmt.setObject (J+1, Params.get (j));
}
}
This.pstmt.executeQuery ();
} catch (SQLException e) {
E.printstacktrace ();
}finally{
This.closeall ();
}

return i;
}

Public ResultSet executeQuery (String sql, list<object> params) {
This.con = This.getconnection ();
try {
this.pstmt = con.preparestatement (sql);
Add params
if (params!=null) {
for (Int J =0;j<params.size (); j + +) {
This.pstmt.setObject (J+1, Params.get (j));
}
}
Set = Pstmt.executequery ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}finally{
}
return set;
}

private void Closeresultset () {
try{
if (set!=null) {
Set.close ();
Set=null;
}
}
catch (SQLException e) {
E.printstacktrace ();
}
}

private void Closepreparedstatement () {
if (this.pstmt!=null) {
try {
This.pstmt.close ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
This.pstmt =null;
}
}

private void Closeconncetion () {
if (this.con!=null) {
try {
This.con.close ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
This.con = null;
}
}

public void CloseAll () {
This.closeresultset ();
This.closepreparedstatement ();
This.closeresultset ();
}
}

2.ICusinfoDao Interface:

Package Org.oracal.dao;

Import java.util.List;

Import Org.oracal.vo.Cusinfo;

/*
* @author: Guan yunqing
* @time: August 5, 2014
* @todo: Todo
* @version:
*/
Public interface Icusinfodao {

public int Save (cusinfo cusinfo);
public int Delete (String cusno);
Public list<cusinfo> selectallinfo ();
Public Cusinfo Selectoneinfo (String cusno);

}

3.CusinfoDaoImpl concrete class, specific tool class

Package Org.oracal.dao.impl;

Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;
Import java.util.List;

Import Org.oracal.dao.BaseDao;
Import Org.oracal.dao.ICusinfoDao;
Import Org.oracal.vo.Cusinfo;

/*
* @author: Guan yunqing
* @time: August 5, 2014
* @todo: Todo
* @version:
*/
public class Cusinfodaoimpl extends Basedao implements Icusinfodao {

@Override
public int Save (Cusinfo cusinfo) {
TODO auto-generated Method Stub
String sql = "INSERT into Cusinfo (Cusno, Cusname) VALUES (?,?)";
list<object> params = new arraylist<object> ();
Params.add (Cusinfo.getcusno ());
Params.add (Cusinfo.getcusname ());
return this.executeupdate (SQL, params);
}

@Override
public int Delete (String cusno) {
TODO auto-generated Method Stub
return 0;
}

@Override
Public list<cusinfo> Selectallinfo () {
TODO auto-generated Method Stub
list<cusinfo> list = new arraylist<cusinfo> ();
String sql = "SELECT * from Cusinfo";
ResultSet rs = this.executequery (sql, NULL);
try {
while (Rs.next ()) {
Cusinfo cus = new Cusinfo ();
Cus.setcusno (rs.getstring ("Cusno"));
Cus.setcusname (rs.getstring ("Cusname"));
List.add (cus);
}
} catch (SQLException e) {
E.printstacktrace ();
}
This.closeall ();
return list;
}

@Override
Public Cusinfo Selectoneinfo (String cusno) {
TODO auto-generated Method Stub
Cusinfo cus = new Cusinfo ();
String sql = "SELECT * from cusinfo where Cusno =?";
list<object> params = new arraylist<object> ();
Params.add (CUSNO);
ResultSet rs= this.executequery (sql, params);
if (rs!=null) {
try {
Rs.next ();
Cus.setcusno (rs.getstring ("Cusno"));
Cus.setcusname (rs.getstring ("Cusname"));
} catch (SQLException e) {
E.printstacktrace ();
}
}
This.closeall ();
return cus;
}

}