（模板方法的运用）持久层操作的java代码
2008年刚从学校毕业就应聘到了一家外包小公司工作
对加拿大的客户开发一些web项目
公司里算我就2个人是做java的
于是我们变成了一个人身兼数个项目的程序员
经理只给我们客户的需求
然后数据库设计、编码等事你一个人搞定。。。
我当时刚出来对java的理解还没有那么深，没有商业项目的任何经验就让我搞着搞那。
当时连数据库连接的操作我都很苦恼，是用简单的jdbc还是hibernate呢，
最后为了方便而达到目的我选用了前者。赶项目的效率还是很重要的。
我给出当时自己写的操作数据库的代码
这家公司大多都是.net的程序所以用的数据库也为sql server 2005
所以不知道这样写是不是会有很多问题，因为我总觉得有问题。
3年后虽然已经离开公司但再回头看这些代码，我还是觉得有问题，所以必须发出来大家审视一下。
这些代码是2008年4月刚出道时自己写的。当时正在读老师推荐给我的java设计模式，看到了template这个模式，于是在工作中突然想到可以把这个模式应用在操作数据库的代码上
于是就产生了以下代码。
第一个是操作数据库方法的抽象接口，
其实这个接口也很简单，定义了那么几个方法，说白了就是操作数据库的。
为什么要写成泛型的接口，为了就是后面大家的业务有针对性，一个实体一个业务功能类。
package com.yd.idao;import java.util.List;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import com.yd.support.JDataSet;/** * 一个定义了所有我所需要的数据库操作的方法接口， * 为什么定义为抽象，我自己都搞不清楚， * 这里定义了泛型，这个很关键，你会看到泛型在这里的使用 * @author kanny * * @param <T> */public abstract interface ISqlHelper<T> {/** * 执行sql语句，大多为单句插入语句 * @param sql单句的sql语句 * @param params插入的参数 * @param only但为true时，sql语句为查询数量的查询语句 * @return * @throws SQLException */public boolean execute(String sql, Object[] params, boolean only) throws SQLException;/** * 执行sql的批处理 * @param sqlBatch多条sql语句 * @return * @throws SQLException */public boolean executeBatch(String[] sqlBatch) throws SQLException;/** * 执行存储过程 * @param procName存储过程名称 * @param params存储过程说需要的参数 * @return * @throws SQLException */public boolean executeCall(String procName, Object[] params) throws SQLException;/** * 查询一行数据封装成java的实体 * @param sqlsql语句 * @param paramssql条件参数 * @param viewName视图名字，在查询多表关联的数据时用于区分 * @param executeCall是否是存储过程，如果为true，第一个sql的参数为存储过程名称 * @return * @throws SQLException */public T find(String sql, Object[] params, String viewName, boolean executeCall) throws SQLException;/** * 查询多行数据封装成java的实体加入一个List里 * @param sqlsql语句 * @param paramssql条件参数 * @param viewName视图名字，在查询多表关联的数据时用于区分，循环封装实体比单一的石头封装要复杂 * @param executeCall是否是存储过程，如果为true，第一个sql的参数为存储过程名称 * @return * @throws SQLException */public List<T> findList(String sql, Object[] params, String viewName, boolean executeCall) throws SQLException;/** * 为了方便操作，我还特意写定义了这个返回ResultSet的方法，便于直接操作 * @param sql * @param params * @param executeCall * @return * @throws SQLException */public ResultSet returnResultSet(String sql, Object[] params, boolean executeCall) throws SQLException;/** * 我的底层分页方法，我会给出具体代码，但这里用的只是sql server 2008的数据库分页 * @param sql * @param orderby排序列 * @param currentPage当前页 * @param pageSize每页多少行 * @return * @throws SQLException */public List<T> findListAsPager(String sql, String orderby, int currentPage, int pageSize) throws SQLException;/** * 后来为了方便操作，想朋友要来了这个JDataSet类，类似.net的DataTable的作用 * @param sql * @param params * @param fillColumnNames是否填充类名，请看方法代码 * @param executeCall * @return */public JDataSet getJDataSet(String sql, Object[] params, boolean fillColumnNames, boolean executeCall);/** * 由于有了JDataSet这个类，于是我有写了调用这个类的分页方法 * @param sql * @param orderby * @param currentPage * @param pageSize * @return * @throws SQLException */public JDataSet getJDataSetAsPager(String sql, String orderby, int currentPage, int pageSize) throws SQLException;/** * 为了方便起见，我多写了一个直接传入ResultSet而封装JDataSet的多余方法 * @param rs * @param fillColumnNames * @return * @throws SQLException */public JDataSet loadJDataSet(ResultSet rs, boolean fillColumnNames) throws SQLException;/** * 得到查询数据的行数 * @param sql * @return * @throws SQLException */public int getRowCount(String sql) throws SQLException;/** * 请看源码 * @param rs * @param column * @return * @throws SQLException */public String changeFont(ResultSet rs, String column) throws SQLException;/** * 得到连接 * @return * @throws SQLException */public Connection returnConn() throws SQLException; /** * 清楚所有数据库操作对象 * @throws SQLException */public void clearAllsql() throws SQLException; }package com.yd.dao;import java.sql.*;import java.util.*;import com.yd.db.DBConnPoolMgr;import com.yd.idao.ISqlHelper;import com.yd.support.JDataSet;public abstract class SqlHelper<T> implements ISqlHelper<T> {protected java.sql.Connection conn = null;protected java.sql.PreparedStatement pst = null;protected java.sql.Statement st = null;protected java.sql.CallableStatement cs = null;protected java.sql.ResultSet rs = null;protected java.sql.ResultSetMetaData rm = null;public Connection returnConn() throws SQLException { //DBConnPoolMgr是自己写的一个简单连接池类，用来得到连接，我后面会给出这个类的代码return (conn = DBConnPoolMgr.getInctence().getConnect());}private PreparedStatement returnPst(String sql, Object[] params) throws SQLException {if (conn == null || conn.isClosed()) conn = returnConn();pst = conn.prepareStatement(sql);if (params != null)for (int i = 0; i < params.length; i++)pst.setObject(i + 1, params[i]);return pst;}protected CallableStatement returnCs(String procName, Object[] params) throws SQLException {if (conn == null || conn.isClosed()) conn = returnConn();String call = "";if (params != null) {call = "{call " + procName + "(";for (int c = 0; c < params.length - 1; c++)call += "?,";call += "?)}";} elsecall = "{call " + procName + "()}";cs = conn.prepareCall(call);if (params != null)for (int i = 0; i < params.length; i++)cs.setObject(i + 1, params[i]);return cs;}public void clearAllsql() {try { if (rs != null) rs.close();if (cs != null) cs.close();if (st != null) st.close();if (pst != null) pst.close();if (conn != null) {DBConnPoolMgr.getInctence().returnConnect(conn);}rs = null;cs = null;st = null;pst = null;} catch (SQLException ex) { ex.printStackTrace(); }}public boolean execute(String sql, Object[] params, boolean only) {boolean bVal = false;try {if (only) {rs = returnPst(sql, params).executeQuery();while (rs.next()) bVal = true;} else {returnPst(sql, params).executeUpdate();bVal = true;}} catch (SQLException ex) { ex.printStackTrace(); } finally { clearAllsql(); }return bVal;}public boolean executeBatch(String[] sqlBatch) {boolean bVal = false;try {conn = returnConn();st = conn.createStatement();boolean autoCommit = conn.getAutoCommit();for (int i = 0; i < sqlBatch.length; i++) {if (sqlBatch[i] != null && !sqlBatch[i].equals(""))st.addBatch(sqlBatch[i] + ";");}conn.setAutoCommit(false);st.executeBatch();conn.commit();conn.setAutoCommit(autoCommit);bVal = true;} catch (SQLException ex) {try { conn.rollback(); } catch (SQLException e) { e.printStackTrace(); }ex.printStackTrace();} finally { clearAllsql(); }return bVal;}public boolean executeCall(String procName, Object[] params) {boolean bVal = false;try {returnCs(procName, params).executeUpdate();bVal = true;} catch (Exception ex) { ex.printStackTrace(); } finally { clearAllsql(); }return bVal;}public T find(String sql, Object[] params, String viewName, boolean executeCall) {T t = null;try {if (executeCall) rs = returnCs(sql, params).executeQuery();else rs = returnPst(sql, params).executeQuery();t = loadResultSet(rs, viewName);} catch (Exception ex) { ex.printStackTrace(); } finally { clearAllsql(); }return t;}public List<T> findList(String sql, Object[] params, String viewName, boolean executeCall) {List<T> lt = null;try {if (executeCall) rs = returnCs(sql, params).executeQuery();else rs = returnPst(sql, params).executeQuery();lt = loadList(rs, viewName);} catch (Exception ex) { ex.printStackTrace(); } finally { clearAllsql(); }return lt;}public ResultSet returnResultSet(String sql, Object[] params, boolean executeCall) {try {if (executeCall) rs = returnCs(sql, params).executeQuery();else rs = returnPst(sql, params).executeQuery();} catch (Exception ex) { ex.printStackTrace(); }return rs;}private T loadResultSet(ResultSet rs, String viewName) throws SQLException {T t = null;if (rs != null) while (rs.next()) t = loadDataBean(rs, viewName);return t;}private List<T> loadList(ResultSet rs, String viewName) throws SQLException {List<T> tlist = new ArrayList<T>();if (rs != null) while (rs.next()) tlist.add(loadDataBean(rs, viewName));return tlist;}public String changeFont(ResultSet rs, String column) throws SQLException {return rs.getString(column) == null ? "" : rs.getString(column);}public int returnColumnCount(ResultSet rs) throws SQLException {return rs.getMetaData().getColumnCount();} //两个非常关键的模版方法，继承此类的操作类都要实现这2个方法，我到时候会给出操作类protected abstract T loadDataBean(ResultSet rs, String viewName) throws SQLException;protected abstract T loadDataBeanSelf(ResultSet rs) throws SQLException;public List<T> findListAsPager(String sql, String orderby, int currentPage, int pageSize) {List<T> lt = null;try {String strVal = strPager(sql, orderby, currentPage, pageSize);rs = returnPst(strVal, null).executeQuery();lt = loadList(rs, "");} catch (SQLException ex) { ex.printStackTrace(); } finally { clearAllsql(); }return lt;} //因为用的sql server 2008所以只写了这个数据库的分页private String strPager(String sql, String orderby, int currentPage, int pageSize) {int start = 1;if (currentPage > 1) start = (currentPage - 1) * pageSize + 1;int end = start + pageSize - 1;String sqlColumn = "*";String sqlDo = "";if(sql.indexOf("@#") != -1) {String []sqlArr = sql.split("@#");sqlColumn = sqlArr[0];sqlDo = sqlArr[1];} else {sqlColumn = "*";sqlDo = sql;}String strVal = "select * from (select " + sqlColumn + ",ROW_NUMBER()";strVal += " Over(order by " + orderby + ")";strVal += " as rowNum " + sqlDo + ")";strVal += " as myTable where rowNum between " + start + " and " + end;return strVal;}public int getRowCount(String sql) throws SQLException {String sqlDo = "";if(sql.indexOf("@#") != -1) {String []sqlArr = sql.split("@#");sqlDo = sqlArr[1];} else sqlDo = sql;int count = 0;try {rs = this.returnResultSet("select count(*) " + sqlDo, null, false);while (rs.next()) count = rs.getInt(1);} catch (Exception ce) { ce.printStackTrace();} finally { clearAllsql(); }return count;}public JDataSet getJDataSetAsPager(String sql, String orderby, int currentPage, int pageSize) {String strVal = strPager(sql, orderby, currentPage, pageSize);return getJDataSet(strVal, null, true, false);}public JDataSet getJDataSet(String sql, Object[] params, boolean fillColumnNames, boolean executeCall) {JDataSet jds = null;try {if (executeCall) rs = returnCs(sql, params).executeQuery();else rs = returnPst(sql, params).executeQuery();jds = loadJDataSet(rs, fillColumnNames);} catch (Exception ex) { ex.printStackTrace(); } finally { clearAllsql(); }return jds;}public JDataSet loadJDataSet(ResultSet rs, boolean fillColumnNames) {JDataSet jds = new JDataSet();try {int columnCount = returnColumnCount(rs);if (fillColumnNames) {String[] columnNames = new String[columnCount];String[] columnTypeNames = new String[columnCount];for (int i = 0; i < columnCount; i++) {columnNames[i] = rs.getMetaData().getColumnName(i + 1);columnTypeNames[i] = rs.getMetaData().getColumnTypeName(i + 1);}jds.setColumnNames(columnNames);jds.setColumnTypeNames(columnTypeNames);}while (rs.next()) {String[] row = new String[columnCount];for (int i = 0; i < columnCount; i++)row[i] = rs.getString(i + 1) == null ? "" : rs.getString(i + 1).trim();jds.addRow(row);}} catch (Exception ex) { ex.printStackTrace(); }return jds;}}public Class Student{private int id;private String name;private int age;以下get和set方法省略...}public interface IStudent extends ISqlHelper<Student>{//插入Studentpublic boolean insertStudent(Student stu);//查询全部Studentpublic List<Student> findStudent();//按id查询某一个Studentpublic Student findStudentAsId(int stuid);}public Class StudentBO extends SqlHelper<Student> implements IStudent{protected Student loadDataBean(ResultSet rs, String viewName) throws SQLException;{Student stu = loadDataBeanSelf(ResultSet rs);return stu;}protected Student loadDataBeanSelf(ResultSet rs) throws SQLException;{Student stu = new Student();stu.setId(rs.getInt("id"));stu.setName(rs.changeFont(rs,"name"));stu.setAge(rs.getInt("age"));return stu;}//实现：插入Student的函数public boolean insertStudent(Student stu){String sql = "insert into Student(id,name,age) values(?,?,?)";Object [] params = new Object[]{stu.getId(),stu.getName,stu.getAge};return this.execute(sql, params, false);}//实现：查询全部Studentpublic List<Student> findStudent(){String sql = "select * from Student";return this.findList(sql, null, "",false);}//实现：按id查询某一个Studentpublic Student findStudentAsId(int stuid){String sql = "select * from Student where id=" + stuid;return this.find(sql, null, "",false);}}