JDBC
1.BaseDao ：抽象基类，其中定义了用于打开连接，得到Statement，执行SQL，关闭资源的基础数据库操作方法。
2.I***Dao ：操作指定数据表的接口：定义了操作数据表的抽象方法。
3***DaoImpl：具体工具类。实现I***Dao接口，继承BaseDao抽象类。用BaseDao中的方法实现I***Dao接口中定义的抽象接口。
基类用来提供访问数据库的基础方法，接口中定义了操作一个数据表所用到的方法。而具体类继承了基类就可以访问数据库，实现了接口就可以使用接口来操作数据库。
这样使得对类的依赖停留在接口层次。
下面用Oracle数据库中的 CUSINFO 表为目标数据表实现：
1.BaseDao 抽象基类
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:上官云清
*@time:2014年8月5日
*@todo:基础的DAO 工具类
*@version: v1.0
*/
/**
* @author：上官云清
*
*/
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;
/**
* 加载数据库驱动
*
*/
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 语句
*@param params 参数
*@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接口：
package org.oracal.dao;
import java.util.List;
import org.oracal.vo.Cusinfo;
/*
*@author:上官云清
*@time:2014年8月5日
*@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 具体类，具体工具类
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:上官云清
*@time:2014年8月5日
*@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;
}
}
