第一个是操作数据库方法的抽象接口,
其实这个接口也很简单,定义了那么几个方法,说白了就是操作数据库的。
为什么要写成泛型的接口,为了就是后面大家的业务有针对性,一个实体一个业务功能类。
- 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 sql sql语句
- * @param params sql条件参数
- * @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 sql sql语句
- * @param params sql条件参数
- * @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;
- }
第2个类是实现这个抽象接口的抽象模版方法类
这个类最为关键,它肯定是实现了ISqlHepler.java里面的所有的方法。
其中:
protected abstract T loadDataBean(ResultSet rs, String viewName) throws SQLException;
protected abstract T loadDataBeanSelf(ResultSet rs) throws SQLException;
这2个方法是SqlHelper.java里留出来了,就是为了大家可以自己封装实体javabean来用
- 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 += "?)}" ;
- } else
- call = "{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 -