在使用数据库连接池的时候，每次写Dao都很烦，要写很多重复的Sql语句，还有各种针对查询好的Rs后fetch出Bean，真的让我很不能忍。
于是在经过了一下午的研究，终于针对Dao进行的封装。
废话不多说 先看Dao父类：
package com.tim.DaoProject.dao;
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
//import java.util.logging.Logger;
import com.tim.DaoProject.util.StringUtils;
public class DAO<T> {
private static boolean LOG_CONSOLE = true;
protected Connection conn;
public DAO(Connection conn) {
this.conn = conn;
}
public String tablename = "";
public Class<T> clazz = null;
// Logger log = Logger.getLogger(DAO.class);
protected static final int timeStampLen = "2011-01-18 16:18:18".length();
protected static final String timeStampPattern = "yyyy-MM-dd HH:mm:ss";
protected static final String datePattern = "yyyy-MM-dd";
public void setConnection(Connection connection){
conn = connection;
}
public synchronized long add(T daoBean)
throws SQLException {
HashMap<String, String> valueMap = this.getBeanHash(daoBean);
String sqlsString = this.getInsertSqlString(tablename, valueMap);
log(sqlsString);
PreparedStatement statement = conn.prepareStatement(sqlsString,Statement.RETURN_GENERATED_KEYS);
int index = 1;
for (String key : valueMap.keySet()) {
statement.setString(index++, valueMap.get(key));
}
long count = statement.executeUpdate();
if(count > 0){
ResultSet rs = statement.getGeneratedKeys();
if(rs.next()){
count = rs.getInt(1);
}
}
return count;
}
public synchronized long update(T daoBean) throws SQLException{
HashMap<String, String> valueMap = this.getBeanHash(daoBean);
String sqlString = this.getUpdateSqlString(tablename, valueMap);
PreparedStatement statement = conn.prepareStatement(sqlString);
int index = 1;
for (String key : valueMap.keySet()) {
if(key.equals("id")){
continue;
}
statement.setString(index++, valueMap.get(key));
}
long count = statement.executeUpdate();
return count;
}
public ArrayList<T> getAllObj() throws SQLException{
return this.getAllObj("");
}
public ArrayList<T> getAllObj(String append) throws SQLException{
String sqlStr = "SELECT * FROM "+tablename+" " + append + ";";
log("sqlstr is " + sqlStr);
PreparedStatement ps = conn.prepareStatement(sqlStr);
ResultSet rs = ps.executeQuery();
return this.fetchList(rs);
}
public T getById(String id) throws SQLException{
String sqlStr = "SELECT * FROM "+tablename+" where id = ?;";
PreparedStatement ps = conn.prepareStatement(sqlStr);
ps.setString(1, id);
ResultSet rs = ps.executeQuery();
return this.fetch(rs);
}
public int deleteById(String id) throws SQLException{
String sqlStr = "DELETE FROM "+tablename+" where id = ?;";
PreparedStatement ps = conn.prepareStatement(sqlStr);
ps.setString(1, id);
return ps.executeUpdate();
}
private T fetch(ResultSet rs) {
T returnObj = null;
try {
if (rs.next()) {
returnObj = clazz.newInstance();
BeanInfo beanInfo = Introspector.getBeanInfo(returnObj.getClass());
PropertyDescriptor[] propertyDesc = beanInfo
.getPropertyDescriptors();
for (int i = 0; i < propertyDesc.length; i++) {
if (propertyDesc[i].getName().compareToIgnoreCase("class") == 0)
continue;
String key = propertyDesc[i].getName();
String value = rs.getString(key);
if (value != null) {
try {
Method mr = propertyDesc[i].getWriteMethod();
Class<?> class1 = mr.getParameterTypes()[0];
mr.invoke(returnObj, this.convert(class1, value));
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
// log.error(e, e);
}
return returnObj;
}
private ArrayList<T> fetchList(ResultSet rs) {
ArrayList<T> returnList = new ArrayList<T>();
try {
while (rs.next()) {
T tempObj = clazz.newInstance();
BeanInfo beanInfo = Introspector.getBeanInfo(tempObj.getClass());
PropertyDescriptor[] propertyDesc = beanInfo
.getPropertyDescriptors();
for (int i = 0; i < propertyDesc.length; i++) {
if (propertyDesc[i].getName().compareToIgnoreCase("class") == 0)
continue;
String key = propertyDesc[i].getName();
try {
String value = rs.getString(key);
if (value != null) {
try {
Method mr = propertyDesc[i].getWriteMethod();
Class<?> class1 = mr.getParameterTypes()[0];
mr.invoke(tempObj, this.convert(class1, value));
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
// log.error(e,e);
}
}
returnList.add(tempObj);
}
} catch (Exception e) {
e.printStackTrace();
// log.error(e, e);
}
return returnList;
}
/***
* 根据startString 和valueMap 拼接出插入的sqlStr
* @param startString
* @param valueMap
* @return
*/
public String getInsertSqlString(String tableName, HashMap<String, String> valueMap ){
StringBuffer sqlBuffer = new StringBuffer();
StringBuffer valueBuffer = new StringBuffer(" values(");
sqlBuffer.append("INSERT INTO " + tableName + " (");
//是否是第一次循环 如果不是 则先append字符 ，
boolean isFirstValue = true;
for (String key : valueMap.keySet()) {
if(key.equals("id")){
continue;
}
if(!isFirstValue){
sqlBuffer.append(",");
valueBuffer.append(",");
}
isFirstValue = false;
sqlBuffer.append("`");
sqlBuffer.append(key);
sqlBuffer.append("`");
valueBuffer.append("?");
}
sqlBuffer.append(")");
valueBuffer.append(")");
sqlBuffer.append(valueBuffer);
return sqlBuffer.toString();
}
public String getUpdateSqlString(String tableName, HashMap<String, String> valueMap ){
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("Update" + tableName + " set ");
StringBuffer whereBuffer = new StringBuffer();
whereBuffer.append(" where `id` = '");
//是否是第一次循环 如果不是 则先append字符 ，
boolean isFirstValue = true;
for (String key : valueMap.keySet()) {
if(key.equals("id")){
whereBuffer.append(valueMap.get(key));
whereBuffer.append("';");
}else {
if(!isFirstValue){
sqlBuffer.append(",");
}
isFirstValue = false;
sqlBuffer.append(" `");
sqlBuffer.append(key);
sqlBuffer.append("`");
sqlBuffer.append(" = ");
sqlBuffer.append("?");
}
}
sqlBuffer.append(whereBuffer);
return sqlBuffer.toString();
}
//根据bean现有的属性 获取对应的key put到map中并返回
public HashMap<String, String> getBeanHash(Object daoBean) {
HashMap<String, String> returnHash = new HashMap<String, String>();
try {
BeanInfo beanInfo = Introspector.getBeanInfo(daoBean.getClass());
PropertyDescriptor[] propertyDesc = beanInfo
.getPropertyDescriptors();
for (int i = 0; i < propertyDesc.length; i++) {
try {
if (propertyDesc[i].getName().compareToIgnoreCase("class") == 0)
continue;
String key = propertyDesc[i].getName();
Method m = propertyDesc[i].getReadMethod();
if (m != null) {
Object[] oParam = new Object[] {};
String value = "" + m.invoke(daoBean, oParam);
if (!StringUtils.isBlank(value) && !value.equalsIgnoreCase("null")) {
returnHash.put(key, value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
// TODO: handle exception
}
return returnHash;
}
//感谢詹波的jfinal开源
protected Object convert(Class<?> clazz, String s)
throws ParseException {
// mysql type: varchar, char, enum, set, text, tinytext, mediumtext,
// longtext
if (clazz == String.class) {
return ("".equals(s) ? null : s); // 用户在表单域中没有输入内容时将提交过来 "",
// 因为没有输入,所以要转成 null.
}
s = s.trim();
if ("".equals(s)) { // 前面的 String跳过以后,所有的空字符串全都转成 null, 这是合理的
return null;
}
// 以上两种情况无需转换,直接返回, 注意, 本方法不接受null为 s 参数(经测试永远不可能传来null, 因为无输入传来的也是"")
Object result = null;
// mysql type: int, integer, tinyint(n) n > 1, smallint, mediumint
if (clazz == Integer.class || clazz == int.class) {
result = Integer.parseInt(s);
}
// mysql type: bigint
else if (clazz == Long.class || clazz == long.class) {
result = Long.parseLong(s);
}
// 经测试java.util.Data类型不会返回, java.sql.Date,
// java.sql.Time,java.sql.Timestamp 全部直接继承自 java.util.Data, 所以
// getDate可以返回这三类数据
else if (clazz == java.util.Date.class) {
if (s.length() >= timeStampLen) {
// Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
// result = new
// java.util.Date(java.sql.Timestamp.valueOf(s).getTime()); //
// error under jdk 64bit(maybe)
result = new SimpleDateFormat(timeStampPattern).parse(s);
} else {
// result = new
// java.util.Date(java.sql.Date.valueOf(s).getTime()); // error
// under jdk 64bit
result = new SimpleDateFormat(datePattern).parse(s);
}
}
// mysql type: date, year
else if (clazz == java.sql.Date.class) {
if (s.length() >= timeStampLen) {
// result = new
// java.sql.Date(java.sql.Timestamp.valueOf(s).getTime()); //
// error under jdk 64bit(maybe)
result = new java.sql.Date(new SimpleDateFormat(
timeStampPattern).parse(s).getTime());
} else {
// result = new
// java.sql.Date(java.sql.Date.valueOf(s).getTime()); // error
// under jdk 64bit
result = new java.sql.Date(new SimpleDateFormat(datePattern)
.parse(s).getTime());
}
}
// mysql type: time
else if (clazz == java.sql.Time.class) {
result = java.sql.Time.valueOf(s);
}
// mysql type: timestamp, datetime
else if (clazz == java.sql.Timestamp.class) {
result = java.sql.Timestamp.valueOf(s);
}
// mysql type: real, double
else if (clazz == Double.class) {
result = Double.parseDouble(s);
}
// mysql type: float
else if (clazz == Float.class) {
result = Float.parseFloat(s);
}
// mysql type: bit, tinyint(1)
else if (clazz == Boolean.class) {
result = Boolean.parseBoolean(s);
}
// mysql type: decimal, numeric
else if (clazz == java.math.BigDecimal.class) {
result = new java.math.BigDecimal(s);
}
// mysql type: binary, varbinary, tinyblob, blob, mediumblob, longblob.
// I have not finished the test.
else if (clazz == byte[].class) {
result = s.getBytes();
}
return result;
}
private void log(String string){
if(LOG_CONSOLE){
System.out.println(string);
}
}
}
这里最重要的两个属性是 tablename 和 clazz。在子类Dao中必须要设置这两个属性：tablename为Dao要操作的表的表明；clazz为对应JavaBean的反射类对象。
同时最后一个方法convert拷贝自詹波的JFinal中，感谢詹波的慷慨开源。
下面简单写了个JavaBean，注意，javabean中的各属性名必须和表中的各属性名保持一直，不能有丝毫差错。因为父类Dao中采用反射获取属性名，并把属性名当作字段名。
package com.tim.DaoProject.dao;
import java.sql.Connection;
import com.tim.DaoProject.bean.UserBean;
public class UserDao extends DAO<UserBean> {
public UserDao(Connection conn) {
super(conn);
this.clazz = UserBean.class;
this.tablename = "`jdbc_db`.`tb_user`";
}
}
注:本文默认使用的MySql数据库，若使用其他数据库可能会存在Sql语句错误的现象。