简易电子通讯录

package yyc.dao; import java.util.ArrayList; import java.sql.*; import java.util.List; import yyc.dao.bean.PropertiesBean; import yyc.dao.bean.ConnectionBean; /** * * <p>Title: 数据库操作类(DAO)</p> * * <p>Description: 实现操作数据库的各种方法:包括数据的增加,修改,删除和查询</p> * * <p>Copyright: 杨允长 (c) 2008/8/13</p> * * <p>Company: 北大青鸟(怀化方元)</p> * * @author 杨允长 * @version 1.0 */ public class SQLCommand { private ConnectionManager dbAccess; private PreparedStatement commonPst; /** * 在规范中数据库连接只限于DAO,所以提供受保护的连接 * @return Connection */ protected Connection getConnection() { //返回以属性文件连接的连接对象 //return this.getConnectionByProperties(); return this.getConnectionByClass(); /** * 返回以程序类写的连接对象 * return this.getConnectionByClass(); */ } protected PreparedStatement getCommonPst() { return this.commonPst; } /** * 获取已经参数化的PreparedStatement对象 * 以便处理SQL语句中存在"?"的情况 * @param commonPst PreparedStatement */ protected void setCommonPst(PreparedStatement commonPst) { this.commonPst = commonPst; } /** * 通过属性文件来获取数据库连接对象 * @return Connection */ private Connection getConnectionByProperties() { PropertiesBean properties = new PropertiesBean(); properties.setPropertyFileName("/dbConn.properties"); properties.setDriverClassPropertyName("driverName"); properties.setConnStringPropertyName("connString"); properties.setUserNamePropertyName("userName"); properties.setUserPasswordPropertyName("userPass"); this.dbAccess = new ConnectionManager(properties); return this.dbAccess.getConnectionByProperties(); } /** * 通过程序类来获取数据库连接对象 * @return Connection */ private Connection getConnectionByClass() { ConnectionBean connbean = new ConnectionBean(); //在这里采用的是纯JAVA的连接,如果要采用桥连接,直接改一下便可 connbean.setDriverName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); connbean.setConnString("jdbc:microsoft:sqlserver://localhost:1433;databaseName=addressBook"); connbean.setUserName("sa"); connbean.setUserPassword(""); this.dbAccess = new ConnectionManager(connbean); return this.dbAccess.getConnectionByClass(); } /** * 封装PreparedStatement(commonPst)对象/ * 参数sql为传入的SQL语句;params参数化设置的对象数据 * @param sql String * @param params Object[] */ private void encapsulationParams(String sql, Object[] params) { Connection dbConn = null; try { dbConn = this.getConnection(); PreparedStatement pst = dbConn.prepareStatement(sql); for (int i = 1; i <= params.length; i++) { pst.setObject(i, params[i - 1]); } this.setCommonPst(pst); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } /** * 封装PreparedStatement(commonPst)对象/ * 参数sql为传入的SQL语句;params参数化设置的对象(List集合) * @param sql String * @param params List */ private void encapsulationParams(String sql, List params) { Connection dbConn = null; try { dbConn = this.getConnection(); PreparedStatement pst = dbConn.prepareStatement(sql); for (int i = 1; i <= params.size(); i++) { pst.setObject(i, params.get(i)); } this.setCommonPst(pst); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } /** * 处理数据的添加,修改,删除;返回受影响的行数 * @param sql String * @return int */ public int executeUpdate(String sql) { int affectRows = 0; Connection dbConn = null; try { dbConn = this.getConnection(); if (this.getCommonPst() == null && sql != null) this.setCommonPst(dbConn.prepareStatement(sql)); affectRows = this.getCommonPst().executeUpdate(); } catch (SQLException ex) { ex.printStackTrace(); } finally { try { dbConn.close(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } return affectRows; } /** * 功能如方法 executeUpdate(String sql); * 此方法不同之处在于可构造参数化的SQL语句 * @param sql String * @param params Object[] * @return int */ public int executeUpdate(String sql, Object[] params) { this.encapsulationParams(sql, params); int affectRows = this.executeUpdate(null); return affectRows; } public int executeUpdate(String sql, List params) { this.encapsulationParams(sql, params); int affectRows = this.executeUpdate(null); return affectRows; } /** * 执行没有参数的存储过程,返回执行的结果true/false * @param proc_string String * @return boolean */ public boolean proc_executeUpdate(String proc_string) { boolean flag = false; Connection dbConn = null; try { dbConn = this.getConnection(); if (this.getCommonPst() == null && proc_string != null) this.setCommonPst(dbConn.prepareCall(proc_string)); int affectRows = this.getCommonPst().executeUpdate(); flag = true; } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } finally { try { dbConn.close(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } return flag; } /** * 可参数化的执行带参数的存储过程,返回true/false * @param proc_string String * @param params Object[] * @return boolean */ public boolean proc_executeUpdate(String proc_string, Object[] params) { this.encapsulationParams(proc_string, params); boolean flag = this.proc_executeUpdate(null); return flag; } public boolean proc_executeUpdate(String proc_string, List params) { this.encapsulationParams(proc_string, params); boolean flag = this.proc_executeUpdate(null); return flag; } /** * 根据传入的SQL查询信息,每条信息以一个ArrayList对象保存 * 如果用户须要封装JavaBean则可依次从ArrayList取出再封装 * 觉得此方法麻烦,或是效率不高,则可采用executeQueryResultSet() * 方法每取出一条记录,封装一个JavaBean以提高执行效率 * @param secSql String * @return List */ public List<ArrayList> executeQuery(String secSql) { List<ArrayList> secResult = new ArrayList<ArrayList>(); Connection dbConn = null; try { dbConn = this.getConnection(); if (this.getCommonPst() == null && secSql != null) this.setCommonPst(dbConn.prepareStatement(secSql)); ResultSet rs = this.getCommonPst().executeQuery(); while (rs.next()) { int columns = rs.getMetaData().getColumnCount(); ArrayList aInfo = new ArrayList(); for (int i = 0; i < columns; i++) aInfo.add(rs.getObject(i + 1)); secResult.add(aInfo); } } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } finally { try { dbConn.close(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } } return