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

        以往在作项目开发的时候，总是需要写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了。对这个工具有兴趣或有更好想法的人可以找我进一步沟通，大家共同提升，共同进步！