利用java封装JDBC操作

[size=large]BaseDao.java

package util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

import javax.naming.NamingException;

public abstract class BaseDao<T extends Object> {
/**
* select a,b,c,from table where username='hao' limit 0,20
*
* @param sql
* @param para
* @param rsObjCall
* @return
*/
protected List<T> executeQueryList(String sql, String[] para,ResultObjectCall<T> rsObjCall) {
List<T> list = new ArrayList<T>();
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = ConnectionFactory.getConnectionDB();
pstm = conn.prepareStatement(sql);
for (int i = 0; i < para.length; i++) {
if (para[i] == null)
para[i] = "";
pstm.setObject(i + 1, para[i]);
}
rs = pstm.executeQuery();
while (rs.next()) {
list.add(rsObjCall.getResultObject(rs));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
this.destoryResource(conn, pstm, rs);
}
return list;
}

/**
* select a,b,c from table where id=25
*
* @param sql
* @param para
* @param objCall
* @return
*/
protected T executeQuery(String sql, String[] para,ResultObjectCall<T> objCall) {
T t = null;
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = ConnectionFactory.getConnectionDB();
pstm = conn.prepareStatement(sql);
for (int i = 0; i < para.length; i++) {
if (para[i] == null)
para[i] = "";
pstm.setObject(i + 1, para[i]);
}
rs = pstm.executeQuery();
if (rs.next()) {
t = objCall.getResultObject(rs);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
this.destoryResource(conn, pstm, rs);
}
return t;
}

/**
* select count(id) from table where id=?
* select a from table where id=?
* @param sql
* @param para
* @return
*/
protected Object getColumn(String sql, String[] para) {
Object obj = null;
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = ConnectionFactory.getConnectionDB();
pstm = conn.prepareStatement(sql);
for (int i = 0; i < para.length; i++) {
if (para[i] == null)
para[i] = "";
pstm.setObject(i + 1, para[i]);
}
rs = pstm.executeQuery();
if (rs.next()) {
obj = rs.getObject(1);
}
} catch (SQLException e) {
} catch (NamingException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
this.destoryResource(conn, pstm, rs);
}
return obj;
}
/**
* update table set col=? where id=?
*
* @param sql
* @param para
* @throws SQLException
*/
protected int executeUpdate(String sql, String[] para) {
int result=0;
Connection conn = null;
PreparedStatement pstm = null;
try {
conn = ConnectionFactory.getConnectionDB();
pstm = conn.prepareStatement(sql);
for (int i = 0; i < para.length; i++) {
if (para[i] == null)
para[i] = "";
pstm.setObject(i + 1, para[i]);
}
result=pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
this.destoryResource(conn, pstm);
}
return result;
}


/**
* insert into diary(userid,username,title,content)values(?,?,?,?)
*
* @param sql
* @param para
* @return
* @throws SQLException
*/
protected long executeInsert(String sql, String[] para) {
long currentId = 0;
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = ConnectionFactory.getConnectionDB();
pstm = conn.prepareStatement(sql);
for (int i = 0; i < para.length; i++) {
if (para[i] == null)
para[i] = "";
pstm.setObject(i + 1, para[i]);
}
pstm.executeUpdate();
rs = pstm.getGeneratedKeys();
if (rs.next()) {
currentId = rs.getLong(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
this.destoryResource(conn, pstm, rs);
}
return currentId;
}

/**
* destoryResource
*
* @param conn
* @param pstm
* @param rs
*/
private void destoryResource(Connection conn, PreparedStatement pstm,ResultSet rs) {
try {
if (pstm != null)
pstm.close();
if (rs != null)
rs.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}

/**
* destoryResource
*
* @param conn
* @param pstm
*/
private void destoryResource(Connection conn, PreparedStatement pstm) {
try {
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
}

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

ResultObjectCall.java

package util;

import java.sql.ResultSet;
import java.sql.SQLException;

public abstract class ResultObjectCall<T extends Object> {
public abstract T getResultObject(ResultSet rs) throws SQLException;
}
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

这样我们在做程序的时候，只需要写sql和sql里面的参数了。

定义个java类TestBaseDaoImpl来实现对日志的一些操作，需要这样定义，Diary是我们的实例类：

public class TestBaseDaoImpl extends BaseDao<Diary>

例如插入记录：

public long intertRecord(){
String sql="insert into diary(userid,username,title,content)values(?,?,?,?)";
return this.executeInsert(sql, new String[]{"1","bokee","photobokee","photo.bokee.com/huangjunhua"});
}

例如查询一个列表：

public List<Diary> loadDiaryList(){
String sql="select diaryid,userid,username,title,content,publishtime from diary where diaryid=? order by diaryid desc";
List<Diary> list= this.executeQueryList(sql,new String[]{"1"}, new ResultObjectCall<Diary>() {
public Diary getResultObject(ResultSet rs) throws SQLException {
Diary d=new Diary();
d.setDiaryId(rs.getLong(1));
d.setUserId(rs.getLong(2));
d.setUserName(rs.getString(3));
d.setTitle(rs.getString(4));
d.setContent(rs.getString(5));
d.setPublishtime(rs.getTimestamp(6));
return d;
}
});
return list;
}

这样就ok了，非常简单吧

我为了能够控制sql的书写，所以把sql的控制放在自己的程序里面了，没有再做封装。

数据库目前针对msyql，jdk版本是1.6.0[/size]