首页 > 代码库 > 自己写的Dao框架
自己写的Dao框架
在使用数据库连接池的时候,每次写Dao都很烦,要写很多重复的Sql语句,还有各种针对查询好的Rs后fetch出Bean,真的让我很不能忍。
于是在经过了一下午的研究,终于针对Dao进行的封装。
废话不多说 先看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 org.apache.commons.lang.StringUtils;import org.apache.log4j.Logger;public class DAO { public String tablename = ""; public Class<?> clazz = null; Logger log = Logger.getLogger(DAO.class); protected Connection conn; public DAO(Connection conn) { this.conn = conn; } 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 synchronized long add(Object oaBean) throws SQLException { HashMap<String, String> valueMap = this.getBeanHash(oaBean); String sqlsString = this.getInsertSqlString(tablename, valueMap); Statement statement = conn.createStatement(); long count = statement.executeUpdate(sqlsString,Statement.RETURN_GENERATED_KEYS); if(count > 0){ ResultSet rs = statement.getGeneratedKeys(); if(rs.next()){ count = rs.getInt(1); } } return count; } public synchronized long update(Object oaBean) throws SQLException{ HashMap<String, String> valueMap = this.getBeanHash(oaBean); String sqlString = this.getUpdateSqlString(tablename, valueMap); System.out.println(sqlString); Statement statement = conn.createStatement(); long count = statement.executeUpdate(sqlString); return count; } public ArrayList<Object> getAllObj() throws SQLException{ String sqlStr = "SELECT * FROM "+tablename+";"; PreparedStatement ps = conn.prepareStatement(sqlStr); ResultSet rs = ps.executeQuery(); return this.fetchList(rs); } public Object 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 deleteObj(Object obj) throws SQLException{// // } 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 Object fetch(ResultSet rs) { Object 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<Object> fetchList(ResultSet rs) { ArrayList<Object> returnList = new ArrayList<Object>(); try { if (rs.next()) { Object 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(); 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(); } } } 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 = http://www.mamicode.com/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("‘"); valueBuffer.append(valueMap.get(key)); 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 = http://www.mamicode.com/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(valueMap.get(key)); sqlBuffer.append("‘ "); } } sqlBuffer.append(whereBuffer); return sqlBuffer.toString(); } //根据bean现有的属性 获取对应的key put到map中并返回 public HashMap<String, String> getBeanHash(Object oaBean) { HashMap<String, String> returnHash = new HashMap<String, String>(); try { BeanInfo beanInfo = Introspector.getBeanInfo(oaBean.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(oaBean, oParam); // System.out.println(propertyDesc[i].getName() + ":" // + value); if (!StringUtils.isBlank(value) && !value.equalsIgnoreCase("null")) { returnHash.put(key, value); } } } catch (Exception e) { e.printStackTrace(); } } } catch (Exception e) { // TODO: handle exception } return returnHash; } 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; } }
这里最重要的两个属性是 tablename 和 clazz。在子类Dao中必须要设置这两个属性:tablename为Dao要操作的表的表明;clazz为对应JavaBean的反射类对象。
同时最后一个方法convert拷贝自詹波的JFinal中,感谢詹波的慷慨开源。
下面简单写了个JavaBean,注意,javabean中的各属性名必须和表中的各属性名保持一直,不能有丝毫差错。因为父类Dao中采用反射获取属性名,并把属性名当作字段名。
package testPackage;
import java.sql.Connection;
import org.sojex.finance.bean.GroupMessageBean;
import org.sojex.finance.dao.DAO;
import org.sojex.finance.dao.DBIns;
public class TestDao extends DAO {
public TestDao(Connection conn) {
super(conn);
tablename = "`tb_group_message`";
clazz = TestBean.class;
}
// public static void main(String args[]){
// Connection conn = null;
// try {
//
// conn = DBIns.getInstance().getConnection();
// TestDao tDao = new TestDao(conn);
// TestBean gm = (TestBean)tDao.getById("238");
//
// if(gm == null){
// System.out.println("return null");
// }else {
// System.out.println(gm.getMessage_content());
// }
//
// long key = tDao.add(gm);
// if(key > 0){
// gm.setId(key + "");
// gm.setMessage_content("test my strong dao");
// tDao.update(gm);
// }
//
// } catch (Exception e) {
// e.printStackTrace();
// }finally{
// DBIns.getInstance().release(conn);
// }
//
// }
}
注:本文默认使用的MySql数据库,若使用其他数据库可能会存在Sql语句错误的现象。