图书网站系统，纯java开发

package com.chongchong.res; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Iterator; import java.util.List; public class JdbcTemplate { // 连接数据库资源 private String driverType = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; private String url = "jdbc:sqlserver://localhost:1433;DatabaseName=WormNet"; private String user = "sa"; private String password = "salmon"; // sql 处理的对象 private Connection conn = null; private Statement st = null; private ResultSet rs = null; private PreparedStatement ps = null; private CallableStatement cs = null; /** * 无参数的构造方法 */ public JdbcTemplate() { } /** * 有参数的构造方法 */ public JdbcTemplate(String driverType, String url, String user, String password) { this.driverType = driverType; this.url = url; this.user = user; this.password = password; } /** * 通过参数得到连接 */ private void getConnection() { try { Class.forName(driverType); conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false); } catch (Exception ex) { ex.printStackTrace(); } } /** * 通过连接，得到通道 */ private void getStatement() { getConnection(); try { st = conn.createStatement(); } catch (SQLException ex) { ex.printStackTrace(); } } /** * 通过连接得到预状态通道 */ private void getPreparedStatement(String sql) { this.getConnection(); try { ps = conn.prepareStatement(sql); } catch (SQLException ex) { ex.printStackTrace(); } } /** * 通过连接得到存储过程通道 */ private void getCallableStatement(String callsql) { this.getConnection(); try { cs = conn.prepareCall(callsql); } catch (SQLException ex) { ex.printStackTrace(); } } /** * 状态通道下的数据操作 * * @param sql * String * @return boolean */ public boolean execOperate(String sql) { int count = -1; getStatement(); try { count = st.executeUpdate(sql); this.runCommit(); } catch (SQLException ex) { ex.printStackTrace(); this.runRollback(); } finally { this.colseResource(); } return count >= 0 ? true : false; } /** * 状态通道下的查询操作 * * @param sql * String * @return ResultSet */ public ResultSet execQuery(String sql) { this.getStatement(); try { rs = st.executeQuery(sql); return rs; } catch (SQLException ex) { ex.printStackTrace(); } return null; } /** * 状态通道下的批处理操作 * * @param sqls * List * @return boolean */ public boolean execBatch(List sqls) { boolean isCorrect = false; this.getStatement(); try { this.operateCols(sqls); st.executeBatch(); this.runCommit(); isCorrect = true; } catch (SQLException ex) { ex.printStackTrace(); this.runRollback(); } finally { this.colseResource(); } return isCorrect; } private void operateCols(List args) throws SQLException { if (args != null && args.size() > 0) { for (Iterator iter = args.iterator(); iter.hasNext();) { st.addBatch(iter.next().toString()); } } } /** * 预通道下的查询操作 * * @param sql * String * @param args * String[] * @return ResultSet ex:select * from dept where deptname = ? and deptnum=? * String[] args = new String[]{"行政部","20"} */ public ResultSet execQuery(String sql, String[] args) { this.getPreparedStatement(sql); try { this.operateParm(args); rs = ps.executeQuery(); return rs; } catch (SQLException ex) { ex.printStackTrace(); } return null; } /** * * @param sql * String * @param args * String[] * @return boolean ex: insert into dept values(?,?,?,?) String[] args = new * String[]{"50","行政部","20","行政部相关"} */ public boolean execOperate(String sql, String[] args) { boolean isCorrect = false; this.getPreparedStatement(sql); try { this.operateParm(args); ps.executeUpdate(); this.runCommit(); isCorrect = true; } catch (SQLException ex) { ex.printStackTrace(); this.runRollback(); } finally { this.colseResource(); } return isCorrect; } private void operateParm(String[] args) throws SQLException { if (args != null && args.length > 0) { for (int i = 0; i < args.length; i++) { ps.setString(i + 1, args[i]); // 通过数组传递的值与与问号一一绑定 } } } /** * 预备通道下的批处理操作 * * @param sql * String * @param args * String[][] 外层控制操作数量，内层控制参数的绑定 * @return boolean */ public boolean doBatch(String sql, String[][] args) { boolean isCorrect = false; this.getPreparedStatement(sql); try { for (int i = 0; i < args.length; i++) { for (int j = 0; j < args[i].length; j++) { ps.setString(j + 1, args[i][j]); } ps.addBatch(); } ps.executeBatch(); this.runCommit(); isCorrect = true; } catch (SQLException e) { e.printStackTrace(); } finally { this.colseResource(); } return isCorrect; } /** * 存储过程通道下操作查询 * * @param callsql * String {call name(?,?...)} * @param args * String[] 传递的参数要与问号一一绑定 * @return ResultSet * */ public ResultSet callQuery(String callsql, String[] args) { this.getCallableStatement(callsql); try { this.operateargs(args); rs = cs.executeQuery(); return rs; } catch (SQLException ex) { ex.printStackTrace(); } return null; } /** * 存储过程通道下数据操作 * * @param callsql * String {call name(?,?...)} * @param args * String[] 传递的参数要与问号一一绑定 * @return boolean */ public boolean callOperate(String callsql, String[] args) { this.getCallableStatement(callsql); int count = -1; try { this.operateargs(args); count = cs.executeUpdate(); this.runCommit(); } catch (SQLException ex) { ex.printStackTrace(); this.runRollback(); } finally { this.colseResource(); } return count >= 0 ? true : false; } private void operateargs(String[] args) throws SQLException { if (args != null && args.length > 0) { for (int i = 0; i < args.length; i++) { cs.setString(i + 1, args[i]); } } } /** * 聚合函数语句执行方法 * * @param sql * String 只能聚合函数语句 * @return String */ public String functionOperate(String sql) { rs = this.execQuery(sql); String value = ""; try { if (rs.next()) { value = rs.getString(1); } } catch (SQLException ex) { ex.printStackTrace(); } finally { this.colseResource(); } return value; } /** * primaryKeyautoValue 得到主键 * * @return int */ public int primaryKeyautoValue(String sql) { if(functionOperate(sql)!=null){ return Integer.parseInt(functionOperate(sql)) + 5; }else{ return 1; } } /** * 得到数据库的元数据 */ public DatabaseMetaData getDatabaseMetaData() throws SQLException { return conn.getMetaData(); } /** * 得到结果集的元数据 */ /** * 提交事务 */ private void runCommit() { try { conn.commit(); } catch (SQLException ex) { ex.printStackTrace(); } } /** * 回滚事务 */ private void runRollback() { try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } /** * 关闭资源 */ public void colseResource() { try { if (rs != null) rs.close(); if (st != null)