JAVA连接数据库增强DAO.docx

1、JAVA连接数据库增强DAOJAVA连接数据库增强DAO在简单的连接数据库的基础上，通过JAVA反射机制可以将对象与数据库表之间映射，直接保存对象、得到对象集合package com.jgj.dao;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQ

2、LException;import java.sql.Statement;import java.util.List;import java.util.Vector;/* * 数据库管理类（增强DAO）V1.0，请注意及时释放资源作者：蒋固金 * * author 蒋固金 * */public class DBManager /数据库连接 Connection conn = null; Statement stm = null; PreparedStatement pstm = null; /结果集 ResultSet rs = null; /驱动字符串 private String DRIV

3、ER = com.microsoft.sqlserver.jdbc.SQLServerDriver; /连接URL，默认master private String fullDB_URL = jdbc:sqlserver:/localhost:1433;databaseName=master; private String DB_URL = jdbc:sqlserver:/localhost:1433;databaseName=; /用户名 private String userName = sa; /密码 private String password = 123; /是否显示SQL语句 pr

4、ivate boolean show_sql = false; /* * 默认构造方法，连接数据库master */ public DBManager() /* * 连接指定数据库 * * param databaseName */ public DBManager(String databaseName) fullDB_URL = DB_URL + databaseName; /* * 设置驱动字符串 * * param driver */ public void setDRIVER(String driver) this.DRIVER = driver; /* * 得到驱动字符串 * *

5、return 驱动字符串 */ public String getDRIVER() return DRIVER; /* * 设置数据库名称，以默认方式自动生成连接URL * * param databaseName */ public void setDataBaseName(String databaseName) fullDB_URL = DB_URL + databaseName; /* * 设置完整连接URL * * param fullDB_URL */ public void setFullDB_URL(String fullDB_URL) this.fullDB_URL = fu

6、llDB_URL; /* * 得到完整URL * * return 完整URL */ public String getFullDB_URL() return fullDB_URL; /* * 设置用户名 * * param userName */ public void setUserName(String userName) this.userName = userName; /* * 得到用户名 * * return 用户名 */ public String getUserName() return userName; /* * 设置密码 * * param password */ pu

7、blic void setPassword(String password) this.password = password; /* * 得到密码 * * return 密码 */ public String getPassword() return password; /* * 设置是否显示SQL语句 * * param show */ public void setShowSql(boolean show) this.show_sql = show; /* * 得到是否显示SQL语句 * * return true 显示 */ public boolean getShowSql() re

8、turn show_sql; /* * 获得数据库连接 * * return conn */ public Connection getConnect() throws Exception try / 加载驱动 Class.forName(DRIVER); / 获得连接 conn = DriverManager.getConnection(fullDB_URL, userName, password); catch (Exception e) throw e; return conn; /* * 获得结果集 * * param sql sql语句 * return 结果集 * throws E

9、xception */ public ResultSet getResultSet(String sql) throws Exception checkSQL(sql); conn = getConnect(); try stm = conn.createStatement(); rs = stm.executeQuery(sql); catch (Exception e) throw e; return rs; /* * 获得PreparedStatement * * param sql * return PreparedStatement对象 * throws Exception */ p

10、ublic PreparedStatement getpreparedStatement(String sql) throws Exception checkSQL(sql); pstm = getConnect().prepareStatement(sql); return pstm; /* * 获得Statement * * return stm * throws Exception */ public Statement getStatement() throws Exception stm = getConnect().createStatement(); return stm; /*

11、 * 获得查询结果的向量集合 * * param sql * return VectorVector */ public VectorVector DBQuery(String sql) throws Exception checkSQL(sql); conn = getConnect(); VectorVector vvo = new VectorVector(); try stm = conn.createStatement(); rs = stm.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); int colum

12、nCount = rsmd.getColumnCount(); while (rs.next() Vector vo = new Vector(); for (int m = 1; m = columnCount; m+) vo.add(rs.getObject(m); vvo.add(vo); catch (Exception e) throw e; finally try freeConnect(); catch (Exception ex) return vvo; /* * 获得影响记录的条数 * * param sql * return 影响记录的条数 * throws Excepti

13、on */ public int getAffectRecordCount(String sql) throws Exception checkSQL(sql); conn = getConnect(); int count = 0; try stm = conn.createStatement(); count = stm.executeUpdate(sql); catch (Exception e) throw e; finally try freeConnect(); catch (Exception ex) return count; /* * 执行SQL语句，判断是否成功 * * p

14、aram sql * param result * return true 影响记录条数为result */ public boolean exeSQL(String sql, int result) throws Exception int temp = 0; try temp = this.getAffectRecordCount(sql); catch (Exception e) throw e; if (temp = result) return true; return false; /* * 执行SQL语句，判断是否成功 * * param sql * return true 影响

15、记录条数为1 */ public boolean exeSQL(String sql) throws Exception return exeSQL(sql, 1); /* * 检查SQL语句 * * param sql * throws Exception */ private void checkSQL(String sql) throws Exception if (sql = null | sql.trim().equals() throw new Exception(the sql is null or invalid); if (show_sql) System.out.print

16、ln(sql); /* * 释放资源 * * throws SQLException */ public void freeConnect() throws SQLException try if (rs != null) rs.close(); rs = null; if (pstm != null) pstm.close(); pstm = null; if (stm != null) stm.close(); stm = null; if (conn != null) conn.close(); conn = null; catch (SQLException e) throw e; /

17、* * 向表中插入数据，未释放资源，不适合主键为自增列 * * param object * param tableName * return true 插入成功 * throws Exception */ private boolean insertObject(Object object, String tableName) throws Exception boolean flag = false; Class cls = object.getClass(); /得到所有已定义字段 Field fields = cls.getDeclaredFields(); /得到字段值不为空的字段集

18、合 Vector notNull = new Vector(); /SQL语句 StringBuffer sql = new StringBuffer(insert into + tableName + (); /动态参数的?号 StringBuffer que = new StringBuffer(); try /获得字段名称，生成SQL语句 for (int i = 0; i fields.length; i+) Field field = fieldsi; field.setAccessible(true); if (field.get(object) != null) notNull.

19、addElement(field); sql.append(getFieldName(field) + ,); que.append(?,); /最后一位为,，去除 if (sql.charAt(sql.length() - 1) = ,) sql.deleteCharAt(sql.length() - 1); if (que.charAt(que.length() - 1) = ,) que.deleteCharAt(que.length() - 1); sql.append() values( + que + ); getpreparedStatement(sql.toString();

20、for (int i = 0; i notNull.size(); i+) pstm.setObject(i + 1, notNull.elementAt(i).get(object); if (pstm.executeUpdate() = 1) flag = true; catch (Exception ex) throw ex; finally try if (pstm != null) pstm.close(); catch (Exception e) return flag; /* * 向表中插入数据，未释放资源，适合主键为自增列 * * param object * param ta

21、bleName * param primaryKeyName * return true 插入成功 * throws Exception */ private boolean insertObjectA(Object object, String tableName, String primaryKeyName) throws Exception boolean flag = false; Class cls = object.getClass(); /得到所有已定义字段 Field fields = cls.getDeclaredFields(); /得到字段值不为空的字段集合 Vector

22、 notNull = new Vector(); /SQL语句 StringBuffer sql = new StringBuffer(insert into + tableName + (); /动态参数的?号 StringBuffer que = new StringBuffer(); try /获得字段名称，生成SQL语句 for (int i = 0; i fields.length; i+) Field field = fieldsi; field.setAccessible(true); if (field.get(object) != null & !getFieldName(f

23、ield).equals(primaryKeyName) notNull.addElement(field); sql.append(getFieldName(field) + ,); que.append(?,); /最后一位为,，去除 if (sql.charAt(sql.length() - 1) = ,) sql.deleteCharAt(sql.length() - 1); if (que.charAt(que.length() - 1) = ,) que.deleteCharAt(que.length() - 1); sql.append() values( + que + );

24、getpreparedStatement(sql.toString(); for (int i = 0; i notNull.size(); i+) pstm.setObject(i + 1, notNull.elementAt(i).get(object); if (pstm.executeUpdate() = 1) flag = true; catch (Exception ex) throw ex; finally try if (pstm != null) pstm.close(); catch (Exception e) return flag; /* * 将对象保存在数据库中，表名

25、为类名小写，适合主键不是自增列 * * param object * return true 成功 */ public boolean saveObject(Object object) throws Exception Class cls = object.getClass(); return saveObject(object, getClassNameWithoutPackage(cls).toLowerCase(); /* * 将对象保存在数据库的对应表中，仅保存不为空的字段，适合主键不是自增列 * * param object * param tableName * return t

26、rue 成功 * throws Exception */ public boolean saveObject(Object object, String tableName) throws Exception boolean flag = false; try flag = insertObject(object, tableName); catch (Exception e) throw e; finally try freeConnect(); catch (Exception e) return flag; /* * 将对象保存在数据库中，表名为类名小写，适合主键为自增列 * * par

27、am object * param primaryKeyName 主键 * return true 成功 */ public boolean saveObjectA(Object object, String primaryKeyName) throws Exception Class cls = object.getClass(); return saveObjectA(object, getClassNameWithoutPackage(cls).toLowerCase(), primaryKeyName); /* * 将对象保存在数据库对应表中，适合主键为自增列 * * param object * param tableName * param primaryKeyName * return true 保存成功 * throws Exception */ public boolean saveObjectA(Object object, String tableName, String primaryKeyName) throws Exception boolean flag = false; try flag = insertObjectA(object, tableName, primaryKeyName); catch (Excepti