jdbc 反射

JDBC+反射 实现查询(bean与值绑定)

 

package com.tb.crm.db.hvisit;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import java.lang.reflect.Method;
import com.tb.crm.entity.UsergroupVO;
import com.tb.crm.exceptions.CRMDBConnException;
import com.tb.crm.exceptions.CRMSQLException;

/**
 * 使用jdbc prepareStatement 查询
 * 结果集包装成list
 */
public class JDBCBindQuery {

    /**
     * 使用prepareStatement 执行sql查询 使用说明:<br>
     * 1.传入beanClass属性名称必须和数据库字段一致<br>
     * 2.bean的属性必须包含 查询语句返回的所有字段<br>
     * 3.bean必须要有空构造<br>
     * 4.bean属性的get set方法必须要有<br>
     *
     * @param sql
     *            本地sql
     * @param clazz
     *            绑定类Class
     * @param Connection
     *            数据库连接
     * @return
     * @throws CRMSQLException
     */
    public static List analysisQuery(String sql, Object[] params, Class clazz,
            Connection conn) throws CRMSQLException {

        List list = new ArrayList();
        Object o = null;
        // Connection conn = SessionManager.getSession().connection();
        PreparedStatement ps;
        try {
            ps = conn.prepareStatement(sql);

            if (params != null) {
                if (params.length != 0) {
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, (Object) params[i]);
                    }
                }
            }

            ResultSet rst = ps.executeQuery();
            ResultSetMetaData rsd = rst.getMetaData();

            int columnCount = rsd.getColumnCount();
            Field[] field = clazz.getDeclaredFields();
            int flag = 0;
            if (field.length < columnCount) {
                throw new CRMSQLException("列于bean属性不匹配 info:bean属性数量少于列数量");
            }
            while (rst.next()) {
                o = clazz.newInstance();
                for (int i = 1; i <= columnCount; i++) {

                    flag = 0;
                    String columnName = rsd.getColumnName(i);
                    for (int j = 0; j < field.length; j++) {

                        String fieldName = field[j].getName();
                        if (!fieldName.equalsIgnoreCase(columnName)) {

                            continue;
                        } else {
                            flag++;
                            String method = "set"
                                    + Character
                                            .toUpperCase(fieldName.charAt(0))
                                    + fieldName.substring(1);

                            Class fieldClass = field[j].getType();
                            Class[] param = { fieldClass };
                            Method m = o.getClass().getMethod(method, param);

                            Method rstm = rst.getClass().getMethod(

                            generationRstGet(fieldClass.toString()),
                                    String.class);

                            m.invoke(o, rstm.invoke(rst, columnName));

                            break;
                        }
                    }

                    if (flag == 0) {
                        throw new CRMSQLException("bean属性缺失 info:" + columnName
                                + " 在bean中没有该列,或者没有set方法");
                    }
                }
                list.add(o);
            }
        } catch (SQLException e) {
            throw new CRMSQLException(e);
        } catch (NoSuchMethodException e) {
            throw new CRMSQLException(e);
        } catch (IllegalArgumentException e) {
            throw new CRMSQLException(e);
        } catch (InvocationTargetException e) {
            throw new CRMSQLException(e);
        } catch (InstantiationException e) {
            throw new CRMSQLException(e);
        } catch (IllegalAccessException e) {
            throw new CRMSQLException(e);
        }

        return list;
    }

    public static String generationRstGet(String s) {

        String[] ss = s.split("//.");
        if (ss.length == 1) {
            return "get" + Character.toUpperCase(s.charAt(0)) + s.substring(1);
        }
        String sss = ss[ss.length - 1];

        return "get" + sss;
    }

    public static void main(String[] args) throws CRMSQLException {

        List list = JDBCBindQuery.analysisQuery(
                "select * from Usergroup", null, UsergroupVO.class,
                SessionManager.getSession().connection());

        for (Iterator iterator = list.iterator(); iterator.hasNext();) {
            UsergroupVO o = (UsergroupVO) iterator.next();
            System.out.println(o);
        }
    }
}