[原创] Java JDBC连接数据库，反射创建实体类对象并赋值数据库行记录（支持存储过程）

1、SqlHelper.java

import java.lang.reflect.*;
import java.sql.*;
import java.util.*;

public class SqlHelper {
    // SQL Server
    /**
     * JDBC驱动名称
     */
    public static final String CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    /**
     * 数据库连库字符串
     */
    public static final String URL = "jdbc:sqlserver://192.168.1.254:1433;databaseName=BBSDB";
    /**
     * 用户名
     */
    public static final String UID = "sa";
    /**
     * 密码
     */
    public static final String PWD = "";
    /**
     * JDBC驱动类型
     */
    public static Class CLS = null;

    // Oracle
    // public static final String CLASS_NAME =
    // "oracle.jdbc.driver.OracleDriver";
    // public static final String URL =
    // "jdbc:oracle:thin:@localhost:1522:accp11g";
    // public static final String UID = "system";
    // public static final String PWD = "manager";
    /**
     * 获取数据库连接对象
     * 
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException,
            SQLException {
        if (CLS == null) {
            CLS = Class.forName(CLASS_NAME);
        }
        return DriverManager.getConnection(URL, UID, PWD);
    }

    /**
     * 执行SQL语句不返回查询的操作，返回受影响的行数
     * 
     * @param sql
     *            SQL语句
     * @return 受影响的行数
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static int executeNonQuery(String sql) {
        int result = -1;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, null);
        }
        return result;
    }

    /**
     * 执行Insert语句，返回Insert成功之后标识列的值
     * 
     * @param sql
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static int executeIdentity(String sql) {
        int identity = -1;
        Connection con = null;
        Statement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.createStatement();
            ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                // identity = rs.getInt("GENERATED_KEYS");
                identity = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, null);
        }
        return identity;
    }

    /**
     * 执行不返回结果集的存储过程
     * 
     * @param sql
     *            存储过程名称
     * @param params
     *            存储过程参数
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void executeNonQuery(String sql, SqlParameter... params) {
        Connection con = null;
        CallableStatement cs = null;
        try {
            con = getConnection();
            cs = con.prepareCall(sql);
            setSqlParameter(cs, params);
            cs.executeUpdate();
            getSqlParameter(cs, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, cs, null);
        }
    }

    /**
     * 执行返回聚合函数的操作
     * 
     * @param sql
     *            含有聚合函数的SQL语句
     * @return 聚合函数的执行结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static int executeScalar(String sql) {
        int result = -1;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, rs);
        }
        return result;
    }

    /**
     * 执行返回泛型集合的SQL语句
     * 
     * @param cls
     *            泛型类型
     * @param sql
     *            查询SQL语句
     * @return 泛型集合
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> executeList(Class<T> cls, String sql) {
        List<T> list = new ArrayList<T>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                T obj = executeResultSet(cls, rs);
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, rs);
        }
        return list;
    }

    /**
     * 执行返回泛型集合的存储过程
     * 
     * @param cls
     *            泛型类型
     * @param sql
     *            存储过程名称
     * @param params
     *            存储过程参数
     * @return 泛型集合
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> executeList(Class<T> cls, String sql,
            SqlParameter... params) {
        List<T> list = new ArrayList<T>();
        Connection con = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            cs = con.prepareCall(sql);
            setSqlParameter(cs, params);
            rs = cs.executeQuery();
            while (rs.next()) {
                T obj = executeResultSet(cls, rs);
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, cs, rs);
        }
        return list;
    }

    /**
     * 执行返回泛型类型对象的SQL语句
     * 
     * @param cls
     *            泛型类型
     * @param sql
     *            SQL语句
     * @return 泛型类型对象
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T executeEntity(Class<T> cls, String sql) {
        T obj = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                obj = executeResultSet(cls, rs);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, rs);
        }
        return obj;
    }

    /**
     * 执行返回泛型类型对象的存储过程
     * 
     * @param cls
     *            泛型类型
     * @param sql
     *            SQL语句
     * @param params
     *            存储过程参数
     * @return 泛型类型对象
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T executeEntity(Class<T> cls, String sql,
            SqlParameter... params) {
        T obj = null;
        Connection con = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            cs = con.prepareCall(sql);
            setSqlParameter(cs, params);
            rs = cs.executeQuery();
            while (rs.next()) {
                obj = executeResultSet(cls, rs);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, cs, rs);
        }
        return obj;
    }

    /**
     * 将一条记录转成一个对象
     * 
     * @param cls
     *            泛型类型
     * @param rs
     *            ResultSet对象
     * @return 泛型类型对象
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws SQLException
     */
    private static <T> T executeResultSet(Class<T> cls, ResultSet rs)
            throws InstantiationException, IllegalAccessException, SQLException {
        T obj = cls.newInstance();
        ResultSetMetaData rsm = rs.getMetaData();
        int columnCount = rsm.getColumnCount();
        // Field[] fields = cls.getFields();
        Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            String fieldName = field.getName();
            for (int j = 1; j <= columnCount; j++) {
                String columnName = rsm.getColumnName(j);
                if (fieldName.equalsIgnoreCase(columnName)) {
                    Object value = rs.getObject(j);
                    field.setAccessible(true);
                    field.set(obj, value);
                    break;
                }
            }
        }
        return obj;
    }

    /**
     * 设置存储过程参数名称，参数值，参数方向
     * 
     * @param cs
     * @param params
     * @throws SQLException
     */
    private static void setSqlParameter(CallableStatement cs,
            SqlParameter... params) throws SQLException {
        if (params != null) {
            for (SqlParameter param : params) {
                if (param.OutPut) {
                    String paramName = param.Name;
                    if (paramName == null || paramName.equals("")) {
                        cs.registerOutParameter(1, param.Type);// 设置返回类型参数
                    } else {
                        cs.registerOutParameter(paramName, param.Type);// 设置输出类型参数
                    }
                } else {
                    cs.setObject(param.Name, param.Value);// 设置输入类型参数
                }
            }
        }
    }

    /**
     * 得到存储过程参数执行结果
     * 
     * @param cs
     * @param params
     * @throws SQLException
     */
    private static void getSqlParameter(CallableStatement cs,
            SqlParameter... params) throws SQLException {
        for (SqlParameter param : params) {
            if (param.OutPut) {
                String paramName = param.Name;
                if (paramName == null || paramName.equals("")) {
                    param.Value = cs.getObject(1);// 返回类型参数值
                } else {
                    param.Value = cs.getObject(paramName);// 输出类型参数值
                }
            }
        }
    }

    /**
     * 关闭JDBC对象，释放资源。
     * 
     * @param con
     *            连接对象
     * @param ps
     *            命令对象
     * @param rs
     *            结果集对象
     * @throws SQLException
     */
    private static void close(Connection con, Statement ps, ResultSet rs) {
        try {
            rs.close();
            if (rs != null) {

                rs = null;
            }
            if (ps != null) {
                ps.close();
                ps = null;
            }
            if (con != null) {
                con.close();
                con = null;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

2、SqlParameter.java

/**
 * 存储过程参数类型
 * @author Administrator
 *
 */
public class SqlParameter {
    /**
     * 参数名称
     */
    public String Name;
    /**
     * 参数值
     */
    public Object Value;
    /**
     * true表示参数为输出类型
     */
    public boolean OutPut;
    /**
     * 参数类型
     */
    public int Type;
    /**
     * 输入类型参数的构造函数
     * @param name 存储过程 输入类型 参数名称
     * @param value 存储过程 输入类型 参数值
     */
    public SqlParameter(String name,Object value){
        this.Name = name;
        this.Value= value;
    }
    /**
     * 输出类型参数的构造函数
     * @param type 存储过程 输出类型 参数类型
     * @param name 存储过程 输出类型 参数名称
     */
    public SqlParameter(int type,String name){
        this.Name = name;
        this.OutPut = true;
        this.Type = type;
    }
    /**
     * 返回类型参数的构造函数
     * @param type 存储过程 返回类型
     */
    public SqlParameter(int type){
        this.Name = "";
        this.OutPut = true;
        this.Type = type;
    }
}

3、Program.java

import java.util.List;

public class Program {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        
        String sql = "INSERT INTO [dbo].[UserInfo] ([UserName] ,[LoginName] ,[LoginPwd] ,[UserSex] ,[Birthday]) VALUES ('%s','%s','%s','%s','%s')";
        sql = String.format(sql, "wyp1","wyp1","wyp1","1","1981-04-21");
        int identyValue = SqlHelper.executeIdentity(sql);
        System.out.println(String.format("Identity Value:%d",identyValue));
        
//        List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "select * from UserInfo");
//        for (UserInfo userInfo : list) {
//            System.out.println(String.format(
//                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",
//                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));
//        }
        
//        SqlParameter param = new SqlParameter("sortField", "[UserInfoId] DESC");
//        List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectAll(?)}",param);
//        for (UserInfo userInfo : list) {
//            System.out.println(String.format(
//                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",
//                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));
//        }
        
//        SqlParameter paramSortField = new SqlParameter("sortField", "[UserInfoId] DESC");
//        SqlParameter paramPageSize = new SqlParameter("pageSize", 10);
//        SqlParameter paramPageIndex = new SqlParameter("pageIndex", 1);
//        SqlParameter paramWhere = new SqlParameter("where", "1=1");
//        List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectByPagerParams(?,?,?,?)}",paramSortField,paramPageSize,paramPageIndex,paramWhere);
//        for (UserInfo userInfo : list) {
//            System.out.println(String.format(
//                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",
//                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));
//        }
        
//        SqlParameter paramWhere = new SqlParameter("where", "1=1");
//        SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount");
//        SqlHelper.executeNonQuery("{call dbo.UserInfoCountByWhere(?,?)}", paramWhere,paramRecordCount);
//        if(paramRecordCount.Value instanceof Integer){
//            Integer recordCount = (Integer)paramRecordCount.Value;
//            System.out.println(String.format("RecordCount:%d",recordCount));
//        }
        
//        SqlParameter paramWhere = new SqlParameter("where", "1=1");
//        SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount");
//        SqlParameter paramReturnValue = new SqlParameter(java.sql.Types.INTEGER);
//        SqlHelper.executeNonQuery("{? = call dbo.UserInfoCountByWhere(?,?)}", paramReturnValue,paramWhere,paramRecordCount);
//        if(paramRecordCount.Value instanceof Integer){
//            Integer recordCount = (Integer)paramRecordCount.Value;
//            System.out.println(String.format("RecordCount:%d",recordCount));
//        }
//        if(paramReturnValue.Value instanceof Integer){
//            Integer returnValue = (Integer)paramReturnValue.Value;
//            System.out.println(String.format("ReturnValue:%d",returnValue));
//        }
    }

}