[原创] Java JDBC连接数据库，反射创建实体类对象并赋值数据库行记录（支持存储过程）
1、SqlHelper.java
1 import java.lang.reflect.*; 2 import java.sql.*; 3 import java.util.*; 4 5 public class SqlHelper { 6 // SQL Server 7 /** 8 * JDBC驱动名称 9 */ 10 public static final String CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; 11 /** 12 * 数据库连库字符串 13 */ 14 public static final String URL = "jdbc:sqlserver://192.168.1.254:1433;databaseName=BBSDB"; 15 /** 16 * 用户名 17 */ 18 public static final String UID = "sa"; 19 /** 20 * 密码 21 */ 22 public static final String PWD = ""; 23 /** 24 * JDBC驱动类型 25 */ 26 public static Class CLS = null; 27 28 // Oracle 29 // public static final String CLASS_NAME = 30 // "oracle.jdbc.driver.OracleDriver"; 31 // public static final String URL = 32 // "jdbc:oracle:thin:@localhost:1522:accp11g"; 33 // public static final String UID = "system"; 34 // public static final String PWD = "manager"; 35 /** 36 * 获取数据库连接对象 37 * 38 * @return 39 * @throws ClassNotFoundException 40 * @throws SQLException 41 */ 42 public static Connection getConnection() throws ClassNotFoundException, 43 SQLException { 44 if (CLS == null) { 45 CLS = Class.forName(CLASS_NAME); 46 } 47 return DriverManager.getConnection(URL, UID, PWD); 48 } 49 50 /** 51 * 执行SQL语句不返回查询的操作，返回受影响的行数 52 * 53 * @param sql 54 * SQL语句 55 * @return 受影响的行数 56 * @throws ClassNotFoundException 57 * @throws SQLException 58 */ 59 public static int executeNonQuery(String sql) { 60 int result = -1; 61 Connection con = null; 62 PreparedStatement ps = null; 63 try { 64 con = getConnection(); 65 ps = con.prepareStatement(sql); 66 result = ps.executeUpdate(); 67 } catch (Exception e) { 68 e.printStackTrace(); 69 } finally { 70 close(con, ps, null); 71 } 72 return result; 73 } 74 75 /** 76 * 执行Insert语句，返回Insert成功之后标识列的值 77 * 78 * @param sql 79 * @return 80 * @throws ClassNotFoundException 81 * @throws SQLException 82 */ 83 public static int executeIdentity(String sql) { 84 int identity = -1; 85 Connection con = null; 86 Statement ps = null; 87 ResultSet rs = null; 88 try { 89 con = getConnection(); 90 ps = con.createStatement(); 91 ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); 92 rs = ps.getGeneratedKeys(); 93 if (rs.next()) { 94 // identity = rs.getInt("GENERATED_KEYS"); 95 identity = rs.getInt(1); 96 } 97 } catch (Exception e) { 98 e.printStackTrace(); 99 } finally { 100 close(con, ps, null); 101 } 102 return identity; 103 } 104 105 /** 106 * 执行不返回结果集的存储过程 107 * 108 * @param sql 109 * 存储过程名称 110 * @param params 111 * 存储过程参数 112 * @throws ClassNotFoundException 113 * @throws SQLException 114 */ 115 public static void executeNonQuery(String sql, SqlParameter... params) { 116 Connection con = null; 117 CallableStatement cs = null; 118 try { 119 con = getConnection(); 120 cs = con.prepareCall(sql); 121 setSqlParameter(cs, params); 122 cs.executeUpdate(); 123 getSqlParameter(cs, params); 124 } catch (Exception e) { 125 e.printStackTrace(); 126 } finally { 127 close(con, cs, null); 128 } 129 } 130 131 /** 132 * 执行返回聚合函数的操作 133 * 134 * @param sql 135 * 含有聚合函数的SQL语句 136 * @return 聚合函数的执行结果 137 * @throws SQLException 138 * @throws ClassNotFoundException 139 */ 140 public static int executeScalar(String sql) { 141 int result = -1; 142 Connection con = null; 143 PreparedStatement ps = null; 144 ResultSet rs = null; 145 try { 146 con = getConnection(); 147 ps = con.prepareStatement(sql); 148 rs = ps.executeQuery(); 149 if (rs.next()) { 150 result = rs.getInt(1); 151 } 152 } catch (Exception e) { 153 e.printStackTrace(); 154 } finally { 155 close(con, ps, rs); 156 } 157 return result; 158 } 159 160 /** 161 * 执行返回泛型集合的SQL语句 162 * 163 * @param cls 164 * 泛型类型 165 * @param sql 166 * 查询SQL语句 167 * @return 泛型集合 168 * @throws ClassNotFoundException 169 * @throws SQLException 170 * @throws InstantiationException 171 * @throws IllegalAccessException 172 */ 173 public static <T> List<T> executeList(Class<T> cls, String sql) { 174 List<T> list = new ArrayList<T>(); 175 Connection con = null; 176 PreparedStatement ps = null; 177 ResultSet rs = null; 178 try { 179 con = getConnection(); 180 ps = con.prepareStatement(sql); 181 rs = ps.executeQuery(); 182 while (rs.next()) { 183 T obj = executeResultSet(cls, rs); 184 list.add(obj); 185 } 186 } catch (Exception e) { 187 e.printStackTrace(); 188 } finally { 189 close(con, ps, rs); 190 } 191 return list; 192 } 193 194 /** 195 * 执行返回泛型集合的存储过程 196 * 197 * @param cls 198 * 泛型类型 199 * @param sql 200 * 存储过程名称 201 * @param params 202 * 存储过程参数 203 * @return 泛型集合 204 * @throws ClassNotFoundException 205 * @throws SQLException 206 * @throws InstantiationException 207 * @throws IllegalAccessException 208 */ 209 public static <T> List<T> executeList(Class<T> cls, String sql, 210 SqlParameter... params) { 211 List<T> list = new ArrayList<T>(); 212 Connection con = null; 213 CallableStatement cs = null; 214 ResultSet rs = null; 215 try { 216 con = getConnection(); 217 cs = con.prepareCall(sql); 218 setSqlParameter(cs, params); 219 rs = cs.executeQuery(); 220 while (rs.next()) { 221 T obj = executeResultSet(cls, rs); 222 list.add(obj); 223 } 224 } catch (Exception e) { 225 e.printStackTrace(); 226 } finally { 227 close(con, cs, rs); 228 } 229 return list; 230 } 231 232 /** 233 * 执行返回泛型类型对象的SQL语句 234 * 235 * @param cls 236 * 泛型类型 237 * @param sql 238 * SQL语句 239 * @return 泛型类型对象 240 * @throws SQLException 241 * @throws ClassNotFoundException 242 * @throws InstantiationException 243 * @throws IllegalAccessException 244 */ 245 public static <T> T executeEntity(Class<T> cls, String sql) { 246 T obj = null; 247 Connection con = null; 248 PreparedStatement ps = null; 249 ResultSet rs = null; 250 try { 251 con = getConnection(); 252 ps = con.prepareStatement(sql); 253 rs = ps.executeQuery(); 254 while (rs.next()) { 255 obj = executeResultSet(cls, rs); 256 break; 257 } 258 } catch (Exception e) { 259 e.printStackTrace(); 260 } finally { 261 close(con, ps, rs); 262 } 263 return obj; 264 } 265 266 /** 267 * 执行返回泛型类型对象的存储过程 268 * 269 * @param cls 270 * 泛型类型 271 * @param sql 272 * SQL语句 273 * @param params 274 * 存储过程参数 275 * @return 泛型类型对象 276 * @throws SQLException 277 * @throws ClassNotFoundException 278 * @throws InstantiationException 279 * @throws IllegalAccessException 280 */ 281 public static <T> T executeEntity(Class<T> cls, String sql, 282 SqlParameter... params) { 283 T obj = null; 284 Connection con = null; 285 CallableStatement cs = null; 286 ResultSet rs = null; 287 try { 288 con = getConnection(); 289 cs = con.prepareCall(sql); 290 setSqlParameter(cs, params); 291 rs = cs.executeQuery(); 292 while (rs.next()) { 293 obj = executeResultSet(cls, rs); 294 break; 295 } 296 } catch (Exception e) { 297 e.printStackTrace(); 298 } finally { 299 close(con, cs, rs); 300 } 301 return obj; 302 } 303 304 /** 305 * 将一条记录转成一个对象 306 * 307 * @param cls 308 * 泛型类型 309 * @param rs 310 * ResultSet对象 311 * @return 泛型类型对象 312 * @throws InstantiationException 313 * @throws IllegalAccessException 314 * @throws SQLException 315 */ 316 private static <T> T executeResultSet(Class<T> cls, ResultSet rs) 317 throws InstantiationException, IllegalAccessException, SQLException { 318 T obj = cls.newInstance(); 319 ResultSetMetaData rsm = rs.getMetaData(); 320 int columnCount = rsm.getColumnCount(); 321 // Field[] fields = cls.getFields(); 322 Field[] fields = cls.getDeclaredFields(); 323 for (int i = 0; i < fields.length; i++) { 324 Field field = fields[i]; 325 String fieldName = field.getName(); 326 for (int j = 1; j <= columnCount; j++) { 327 String columnName = rsm.getColumnName(j); 328 if (fieldName.equalsIgnoreCase(columnName)) { 329 Object value = rs.getObject(j); 330 field.setAccessible(true); 331 field.set(obj, value); 332 break; 333 } 334 } 335 } 336 return obj; 337 } 338 339 /** 340 * 设置存储过程参数名称，参数值，参数方向 341 * 342 * @param cs 343 * @param params 344 * @throws SQLException 345 */ 346 private static void setSqlParameter(CallableStatement cs, 347 SqlParameter... params) throws SQLException { 348 if (params != null) { 349 for (SqlParameter param : params) { 350 if (param.OutPut) { 351 String paramName = param.Name; 352 if (paramName == null || paramName.equals("")) { 353 cs.registerOutParameter(1, param.Type);// 设置返回类型参数 354 } else { 355 cs.registerOutParameter(paramName, param.Type);// 设置输出类型参数 356 } 357 } else { 358 cs.setObject(param.Name, param.Value);// 设置输入类型参数 359 } 360 } 361 } 362 } 363 364 /** 365 * 得到存储过程参数执行结果 366 * 367 * @param cs 368 * @param params 369 * @throws SQLException 370 */ 371 private static void getSqlParameter(CallableStatement cs, 372 SqlParameter... params) throws SQLException { 373 for (SqlParameter param : params) { 374 if (param.OutPut) { 375 String paramName = param.Name; 376 if (paramName == null || paramName.equals("")) { 377 param.Value = cs.getObject(1);// 返回类型参数值 378 } else { 379 param.Value = cs.getObject(paramName);// 输出类型参数值 380 } 381 } 382 } 383 } 384 385 /** 386 * 关闭JDBC对象，释放资源。 387 * 388 * @param con 389 * 连接对象 390 * @param ps 391 * 命令对象 392 * @param rs 393 * 结果集对象 394 * @throws SQLException 395 */ 396 private static void close(Connection con, Statement ps, ResultSet rs) { 397 try { 398 rs.close(); 399 if (rs != null) { 400 401 rs = null; 402 } 403 if (ps != null) { 404 ps.close(); 405 ps = null; 406 } 407 if (con != null) { 408 con.close(); 409 con = null; 410 } 411 } catch (SQLException e) { 412 // TODO Auto-generated catch block 413 e.printStackTrace(); 414 } 415 } 416 }
2、SqlParameter.java
1 /** 2 * 存储过程参数类型 3 * @author Administrator 4 * 5 */ 6 public class SqlParameter { 7 /** 8 * 参数名称 9 */ 10 public String Name; 11 /** 12 * 参数值 13 */ 14 public Object Value; 15 /** 16 * true表示参数为输出类型 17 */ 18 public boolean OutPut; 19 /** 20 * 参数类型 21 */ 22 public int Type; 23 /** 24 * 输入类型参数的构造函数 25 * @param name 存储过程 输入类型 参数名称 26 * @param value 存储过程 输入类型 参数值 27 */ 28 public SqlParameter(String name,Object value){ 29 this.Name = name; 30 this.Value= value; 31 } 32 /** 33 * 输出类型参数的构造函数 34 * @param type 存储过程 输出类型 参数类型 35 * @param name 存储过程 输出类型 参数名称 36 */ 37 public SqlParameter(int type,String name){ 38 this.Name = name; 39 this.OutPut = true; 40 this.Type = type; 41 } 42 /** 43 * 返回类型参数的构造函数 44 * @param type 存储过程 返回类型 45 */ 46 public SqlParameter(int type){ 47 this.Name = ""; 48 this.OutPut = true; 49 this.Type = type; 50 } 51 }
3、Program.java
1 import java.util.List; 2 3 public class Program { 4 5 public static void main(String[] args) { 6 // TODO Auto-generated method stub 7 8 String sql = "INSERT INTO [dbo].[UserInfo] ([UserName] ,[LoginName] ,[LoginPwd] ,[UserSex] ,[Birthday]) VALUES ('%s','%s','%s','%s','%s')"; 9 sql = String.format(sql, "wyp1","wyp1","wyp1","1","1981-04-21"); 10 int identyValue = SqlHelper.executeIdentity(sql); 11 System.out.println(String.format("Identity Value:%d",identyValue)); 12 13 // List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "select * from UserInfo"); 14 // for (UserInfo userInfo : list) { 15 // System.out.println(String.format( 16 // "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s", 17 // userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday()))); 18 // } 19 20 // SqlParameter param = new SqlParameter("sortField", "[UserInfoId] DESC"); 21 // List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectAll(?)}",param); 22 // for (UserInfo userInfo : list) { 23 // System.out.println(String.format( 24 // "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s", 25 // userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday()))); 26 // } 27 28 // SqlParameter paramSortField = new SqlParameter("sortField", "[UserInfoId] DESC"); 29 // SqlParameter paramPageSize = new SqlParameter("pageSize", 10); 30 // SqlParameter paramPageIndex = new SqlParameter("pageIndex", 1); 31 // SqlParameter paramWhere = new SqlParameter("where", "1=1"); 32 // List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectByPagerParams(?,?,?,?)}",paramSortField,paramPageSize,paramPageIndex,paramWhere); 33 // for (UserInfo userInfo : list) { 34 // System.out.println(String.format( 35 // "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s", 36 // userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday()))); 37 // } 38 39 // SqlParameter paramWhere = new SqlParameter("where", "1=1"); 40 // SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount"); 41 // SqlHelper.executeNonQuery("{call dbo.UserInfoCountByWhere(?,?)}", paramWhere,paramRecordCount); 42 // if(paramRecordCount.Value instanceof Integer){ 43 // Integer recordCount = (Integer)paramRecordCount.Value; 44 // System.out.println(String.format("RecordCount:%d",recordCount)); 45 // } 46 47 // SqlParameter paramWhere = new SqlParameter("where", "1=1"); 48 // SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount"); 49 // SqlParameter paramReturnValue = new SqlParameter(java.sql.Types.INTEGER); 50 // SqlHelper.executeNonQuery("{? = call dbo.UserInfoCountByWhere(?,?)}", paramReturnValue,paramWhere,paramRecordCount); 51 // if(paramRecordCount.Value instanceof Integer){ 52 // Integer recordCount = (Integer)paramRecordCount.Value; 53 // System.out.println(String.format("RecordCount:%d",recordCount)); 54 // } 55 // if(paramReturnValue.Value instanceof Integer){ 56 // Integer returnValue = (Integer)paramReturnValue.Value; 57 // System.out.println(String.format("ReturnValue:%d",returnValue)); 58 // } 59 } 60 61 }
