2. 技术人生
    3. 全能的SQLTool类，抛弃繁杂的DAO和映射文件

    全能的SQLTool类，抛弃繁杂的DAO和映射文件

    技术2022-05-11  60

            以往在作项目开发的时候，总是需要写N多的DAO，每个DAO中还有N多的查询方法，最近自己写了个工具类来简化这些工作，具体代码如下：

     

    package  com.hing.tools; import  java.lang.reflect.Field; import  java.lang.reflect.Method; import  java.sql.Connection; import  java.sql.PreparedStatement; import  java.sql.ResultSet; import  java.sql.ResultSetMetaData; import  java.sql.Timestamp; import  java.text.SimpleDateFormat; import  java.util.ArrayList; import  java.util.Date; import  java.util.HashMap; import  java.util.Iterator; import  java.util.List; import  java.util.Set; public   class  SQLTool  {    // 用于无参数的统计查询    public static Integer getCount(String sql) {        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        try {            conn = DBTool.getConnection();            pstm = conn.prepareStatement(sql);            rs = pstm.executeQuery();            if (rs.next()) {                return rs.getInt(1);            } else                return 0;        } catch (Exception e) {            LogTool.error(SQLTool.class, e);            return 0;        } finally {            DBTool.close(conn, pstm, rs);        }    }    // 用于有多个参数的统计查询    public static Integer getCount(String sql, String[] args) {        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        try {            conn = DBTool.getConnection();            pstm = conn.prepareStatement(sql);            for (int i = 1; i < args.length + 1; i++) {                pstm.setString(i, args[i - 1]);            }            rs = pstm.executeQuery();            if (rs.next()) {                return rs.getInt(1);            } else                return 0;        } catch (Exception e) {            LogTool.error(SQLTool.class, e);            return 0;        } finally {            DBTool.close(conn, pstm, rs);        }    }    // 用于无参数的查询，将每一行记录封装到一个HashMap，主键为列名的小写，然后放到List中    public static List getList(String sql, String className) {        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        try {            List list = new ArrayList();            conn = DBTool.getConnection();            pstm = conn.prepareStatement(sql);            rs = pstm.executeQuery();            ResultSetMetaData rsmd = rs.getMetaData();            Integer columns = rsmd.getColumnCount();            while (rs.next()) {                HashMap hm = new HashMap();                for (int i = 1; i < columns + 1; i++) {                    String columnName = rsmd                            .getColumnName(i);                    String columnValue = rs                            .getString(columnName);                    hm.put(columnName.toLowerCase(),                            columnValue);                }                list.add(hm);            }            System.out.println("get "+list.size()+" records.");            if(className!=null){                return SQLTool.getObjectList(list, className);            }            else return list;        } catch (Exception e) {            LogTool.error(SQLTool.class, e);            return null;        } finally {            DBTool.close(conn, pstm, rs);        }    }    // 用于有参数的查询    public static List getList(String sql, String[] args,            String className) {        Long l=(new Date()).getTime();        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        try {            List list = new ArrayList();            conn = DBTool.getConnection();            pstm = conn.prepareStatement(sql);            for (int i = 1; i < args.length + 1; i++) {                pstm.setString(i, args[i - 1]);            }            rs = pstm.executeQuery();            ResultSetMetaData rsmd = rs.getMetaData();            Integer columns = rsmd.getColumnCount();            while (rs.next()) {                HashMap hm = new HashMap();                for (int i = 1; i < columns + 1; i++) {                    String columnName = rsmd                            .getColumnName(i);                    String columnValue = rs                            .getString(columnName);                    // LogTool.info(SQLTool.class, "get                    // column:"+columnName+" "+columnValue);                    hm.put(columnName.toLowerCase(),                            columnValue);                }                list.add(hm);            }            // System.out.println("get list:"+list.size());            System.out.println("get "+list.size()+" records.");            if(className!=null){                return SQLTool.getObjectList(list, className);            }            else return list;        } catch (Exception e) {            LogTool.error(SQLTool.class, e);            return null;        } finally {            DBTool.close(conn, pstm, rs);        }    }    public static List getObjectList(List list,            String className) {        try {            Class cls = Class.forName(className);            Field[] fields = cls.getDeclaredFields();            String[] fieldName = new String[fields.length];            Class[] fieldClass = new Class[fields.length];            for (int i = 0; i < fields.length; i++) {                fieldName[i] = fields[i].getName();                fieldClass[i] = fields[i].getType();            }            Iterator it = list.iterator();            List objects = new ArrayList();            while (it.hasNext()) {                HashMap hm = (HashMap) it.next();                Object obj = cls.newInstance();                Set keys = hm.keySet();                Iterator iter = keys.iterator();                while (iter.hasNext()) {                    String key = (String) iter.next();                    String value = (String) hm.get(key);                    if (value != null) {                        String realFieldName = null;                        Class realFieldClass = null;                        for (int j = 0; j < fieldName.length; j++) {                            if (key.equals(fieldName[j]                                    .toLowerCase())) {                                realFieldName = fieldName[j];                                realFieldClass = fieldClass[j];                                j = fieldName.length + 10;                            }                        }                        Object realObj = SQLTool                                .typeConvert(                                        realFieldClass,                                        value);                        if (realFieldName != null) {                            String methodName = "set"                                    + realFieldName                                            .substring(0, 1)                                            .toUpperCase()                                    + realFieldName                                            .substring(1);                            Method method = cls                                    .getDeclaredMethod(                                            methodName,                                            realFieldClass);                            if (method != null&&realObj!=null) {                                method.invoke(obj, realObj);                            }                        }                    }                }                objects.add(obj);            }            return objects;        } catch (Exception e) {            LogTool.error(SQLTool.class, e);            return null;        }    }    public static Object typeConvert(Class cls, String value) {        try {            if (cls.getName().equals("java.lang.String")) {                return value;            } else if (cls.getName().equals(                    "java.lang.Integer")) {                return Integer.valueOf(value);            } else if (cls.getName().equals(                    "java.lang.Long")) {                return Long.valueOf(value);            } else if (cls.getName().equals(                    "java.lang.Boolean")) {                return Boolean.valueOf(value);            } else if (cls.getName().equals(                    "java.util.Date")) {                if(value.length()==10){                    return (new SimpleDateFormat("yyyy-MM-dd")).parse(value);                }else if(value.length()==19){                    return (new SimpleDateFormat("yyyy-MM-dd kk:mm:ss")).parse(value);                }else{                    return new Date(value);                }                            } else if (cls.getName().equals(                    "java.lang.Float")) {                return Float.valueOf(value);            } else if (cls.getName().equals(                    "java.sql.Timestamp")) {                return Timestamp.valueOf(value);            } else if (cls.getName().equals(                    "java.lang.Double")) {                return Double.valueOf(value);            } else                return null;        } catch (Exception e) {            LogTool.error(SQLTool.class,                    "Type convert failed! "+cls.getName()+" "+value);            LogTool.error(SQLTool.class, e);            return null;        }    }}

     

    这个工具使用起来非常方便，如

    List list=SQLTool.getList("select * from User limit ?",new String[]{"70"},"com.sms.pojo.User");

    这么简单的一句代码就能实现相应的SQL查询，并将结果自动映射到User这个POJO中去，完全不需要写N多的DAO方法，更不需要去维护映射文件，数据库各列的数据类型和POJO中各属性的数据类型不需要一一对应，一句话，方便实用！由于时间比较仓促，估计当中还有不少问题，但我在Sqlserver2000及Mysql下都做了测试，基本上没什么问题，等过完春节再来慢慢完善，再增加增加记录、修改记录及删除记录的操作基本就OK了。对这个工具有兴趣或有更好想法的人可以找我进一步沟通，大家共同提升，共同进步！

    专利

    最新回复(0)