首页 > 代码库 > 自己写的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语句错误的现象。