dbutils通用类,dbutils分页查询,第一部分是公共类,封装好了操作数据库的方法。第二部分是分页的bean。
第一部分:
- publicclass DBUtilsTemplate {
- private DataSource dataSource;
- private QueryRunner queryRunner;
- privatestaticfinal Log LOG = LogFactory.getLog(DBUtilsTemplate.class);
- public DBUtilsTemplate(DataSource dataSources) {
- this();
- }
- public DBUtilsTemplate() {
- dataSource = MyDataSource.getdataSource();
- }
- /**
- *
- * @param sql
- * 插入sql语句
- * @param params
- * 插入参数
- * @return 返回影响行数
- */
- publicint insert(String sql, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- int affectedRows = 0;
- try {
- if (params == null) {
- affectedRows = queryRunner.update(sql);
- } else {
- affectedRows = queryRunner.update(sql, params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("insert.插入记录错误:" + sql, e);
- }
- return affectedRows;
- }
- /**
- * 插入数据库,返回自动增长的主键
- *
- * @param sql -
- * 执行的sql语句
- * @return 主键 注意;此方法没关闭资源
- */
- publicint insertForKeys(String sql, Object[] params) {
- int key = 0;
- Connection conn = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- conn = dataSource.getConnection();
- stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
- ParameterMetaData pmd = stmt.getParameterMetaData();
- if (params.length < pmd.getParameterCount()) {
- thrownew SQLException("参数错误:" + pmd.getParameterCount());
- }
- for (int i = 0; i < params.length; i++) {
- stmt.setObject(i + 1, params[i]);
- }
- stmt.executeUpdate();
- rs = stmt.getGeneratedKeys();
- if (rs.next()) {
- key = rs.getInt(1);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("insertForKey.插入返回主键错误:" + sql, e);
- } finally {
- if (rs != null) { // 关闭记录集
- try {
- rs.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- if (stmt != null) { // 关闭声明
- try {
- stmt.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- if (conn != null) { // 关闭连接对象
- try {
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- return key;
- }
- private ScalarHandler scalarHandler = new ScalarHandler() {
- @Override
- public Object handle(ResultSet rs) throws SQLException {
- Object obj = super.handle(rs);
- if (obj instanceof BigInteger)
- return ((BigInteger) obj).longValue();
- return obj;
- }
- };
- publiclong count(String sql, Object... params) {
- Number num = 0;
- try {
- queryRunner = new QueryRunner(dataSource);
- if (params == null) {
- num = (Number) queryRunner.query(sql, scalarHandler);
- } else {
- num = (Number) queryRunner.query(sql, scalarHandler, params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("count.统计数量错误" + sql, e);
- }
- return (num != null) ? num.longValue() : -1;
- }
- /**
- * 执行sql语句
- *
- * @param sql
- * sql语句
- * @return 受影响的行数
- */
- publicint update(String sql) {
- return update(sql, null);
- }
- /**
- * 单条修改记录
- *
- * @param sql
- * sql语句
- * @param param
- * 参数
- * @return 受影响的行数
- */
- publicint update(String sql, Object param) {
- return update(sql, new Object[] { param });
- }
- /**
- * 单条修改记录
- *
- * @param sql
- * sql语句
- * @param params
- * 参数数组
- * @return 受影响的行数
- */
- publicint update(String sql, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- int affectedRows = 0;
- try {
- if (params == null) {
- affectedRows = queryRunner.update(sql);
- } else {
- affectedRows = queryRunner.update(sql, params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("update.单条修改记录错误:" + sql, e);
- }
- return affectedRows;
- }
- /**
- * 批量修改记录
- *
- * @param sql
- * sql语句
- * @param params
- * 二维参数数组
- * @return 受影响的行数的数组
- */
- publicint[] batchUpdate(String sql, Object[][] params) {
- queryRunner = new QueryRunner(dataSource);
- int[] affectedRows = newint[0];
- try {
- affectedRows = queryRunner.batch(sql, params);
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("update.批量修改记录错误:" + sql, e);
- }
- return affectedRows;
- }
- /**
- * 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
- *
- * @param sql
- * sql语句
- * @return 查询结果
- */
- public List<Map<String, Object>> find(String sql) {
- return find(sql, null);
- }
- /**
- * 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
- *
- * @param sql
- * sql语句
- * @param param
- * 参数
- * @return 查询结果
- */
- public List<Map<String, Object>> find(String sql, Object param) {
- return find(sql, new Object[] { param });
- }
- /**
- * 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
- *
- * @param sql
- * sql语句
- * @param params
- * 参数数组
- * @return 查询结果
- */
- @SuppressWarnings("unchecked")
- public List<Map<String, Object>> findPage(String sql, int page, int count, Object... params) {
- sql = sql + " LIMIT ?,?";
- queryRunner = new QueryRunner(dataSource);
- List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
- try {
- if (params == null) {
- list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), new Integer[] { page,
- count });
- } else {
- list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), ArrayUtils.addAll(
- params, new Integer[] { page, count }));
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("map 数据分页查询错误", e);
- }
- return list;
- }
- /**
- * 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
- *
- * @param sql
- * sql语句
- * @param params
- * 参数数组
- * @return 查询结果
- */
- @SuppressWarnings("unchecked")
- public List<Map<String, Object>> find(String sql, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
- try {
- if (params == null) {
- list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler());
- } else {
- list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("map 数据查询错误", e);
- }
- return list;
- }
- /**
- * 执行查询,将每行的结果保存到Bean中,然后将所有Bean保存到List中
- *
- * @param entityClass
- * 类名
- * @param sql
- * sql语句
- * @return 查询结果
- */
- public <T> List<T> find(Class<T> entityClass, String sql) {
- return find(entityClass, sql, null);
- }
- /**
- * 执行查询,将每行的结果保存到Bean中,然后将所有Bean保存到List中
- *
- * @param entityClass
- * 类名
- * @param sql
- * sql语句
- * @param param
- * 参数
- * @return 查询结果
- */
- public <T> List<T> find(Class<T> entityClass, String sql, Object param) {
- return find(entityClass, sql, new Object[] { param });
- }
- /**
- * 执行查询,将每行的结果保存到Bean中,然后将所有Bean保存到List中
- *
- * @param entityClass
- * 类名
- * @param sql
- * sql语句
- * @param params
- * 参数数组
- * @return 查询结果
- */
- @SuppressWarnings("unchecked")
- public <T> List<T> find(Class<T> entityClass, String sql, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- List<T> list = new ArrayList<T>();
- try {
- if (params == null) {
- list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass));
- } else {
- list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass), params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("Error occured while attempting to query data", e);
- }
- return list;
- }
- /**
- * 查询出结果集中的第一条记录,并封装成对象
- *
- * @param entityClass
- * 类名
- * @param sql
- * sql语句
- * @return 对象
- */
- public <T> T findFirst(Class<T> entityClass, String sql) {
- return findFirst(entityClass, sql, null);
- }
- /**
- * 查询出结果集中的第一条记录,并封装成对象
- *
- * @param entityClass
- * 类名
- * @param sql
- * sql语句
- * @param param
- * 参数
- * @return 对象
- */
- public <T> T findFirst(Class<T> entityClass, String sql, Object param) {
- return findFirst(entityClass, sql, new Object[] { param });
- }
- /**
- * 查询出结果集中的第一条记录,并封装成对象
- *
- * @param entityClass
- * 类名
- * @param sql
- * sql语句
- * @param params
- * 参数数组
- * @return 对象
- */
- @SuppressWarnings("unchecked")
- public <T> T findFirst(Class<T> entityClass, String sql, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- Object object = null;
- try {
- if (params == null) {
- object = queryRunner.query(sql, new BeanHandler(entityClass));
- } else {
- object = queryRunner.query(sql, new BeanHandler(entityClass), params);
- }
- } catch (SQLException e) {
- LOG.error("返回一条记录错误:findFirst" + e.getMessage());
- e.printStackTrace();
- }
- return (T) object;
- }
- /**
- * 查询出结果集中的第一条记录,并封装成Map对象
- *
- * @param sql
- * sql语句
- * @return 封装为Map的对象
- */
- public Map<String, Object> findFirst(String sql) {
- return findFirst(sql, null);
- }
- /**
- * 查询出结果集中的第一条记录,并封装成Map对象
- *
- * @param sql
- * sql语句
- * @param param
- * 参数
- * @return 封装为Map的对象
- */
- public Map<String, Object> findFirst(String sql, Object param) {
- return findFirst(sql, new Object[] { param });
- }
- /**
- * 查询出结果集中的第一条记录,并封装成Map对象
- *
- * @param sql
- * sql语句
- * @param params
- * 参数数组
- * @return 封装为Map的对象
- */
- @SuppressWarnings("unchecked")
- public Map<String, Object> findFirst(String sql, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- Map<String, Object> map = null;
- try {
- if (params == null) {
- map = (Map<String, Object>) queryRunner.query(sql, new MapHandler());
- } else {
- map = (Map<String, Object>) queryRunner.query(sql, new MapHandler(), params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("findFirst.查询一条记录错误" + sql, e);
- }
- return map;
- }
- /**
- * 查询某一条记录,并将指定列的数据转换为Object
- *
- * @param sql
- * sql语句
- * @param columnName
- * 列名
- * @return 结果对象
- */
- public Object findBy(String sql, String params) {
- return findBy(sql, params, null);
- }
- /**
- * 查询某一条记录,并将指定列的数据转换为Object
- *
- * @param sql
- * sql语句
- * @param columnName
- * 列名
- * @param param
- * 参数
- * @return 结果对象
- */
- public Object findBy(String sql, String columnName, Object param) {
- return findBy(sql, columnName, new Object[] { param });
- }
- /**
- * 查询某一条记录,并将指定列的数据转换为Object
- *
- * @param sql
- * sql语句
- * @param columnName
- * 列名
- * @param params
- * 参数数组
- * @return 结果对象
- */
- public Object findBy(String sql, String columnName, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- Object object = null;
- try {
- if (params == null) {
- object = queryRunner.query(sql, new ScalarHandler(columnName));
- } else {
- object = queryRunner.query(sql, new ScalarHandler(columnName), params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("findBy。错误" + sql, e);
- }
- return object;
- }
- /**
- * 查询某一条记录,并将指定列的数据转换为Object
- *
- * @param sql
- * sql语句
- * @param columnIndex
- * 列索引
- * @return 结果对象
- */
- public Object findBy(String sql, int columnIndex) {
- return findBy(sql, columnIndex, null);
- }
- /**
- * 查询某一条记录,并将指定列的数据转换为Object
- *
- * @param sql
- * sql语句
- * @param columnIndex
- * 列索引
- * @param param
- * 参数
- * @return 结果对象
- */
- public Object findBy(String sql, int columnIndex, Object param) {
- return findBy(sql, columnIndex, new Object[] { param });
- }
- /**
- * 查询某一条记录,并将指定列的数据转换为Object
- *
- * @param sql
- * sql语句
- * @param columnIndex
- * 列索引
- * @param params
- * 参数数组
- * @return 结果对象
- */
- public Object findBy(String sql, int columnIndex, Object[] params) {
- queryRunner = new QueryRunner(dataSource);
- Object object = null;
- try {
- if (params == null) {
- object = queryRunner.query(sql, new ScalarHandler(columnIndex));
- } else {
- object = queryRunner.query(sql, new ScalarHandler(columnIndex), params);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- LOG.error("findBy.错误" + sql, e);
- }
- return object;
- }
- /**
- *
- * @param <T>分页查询
- * @param beanClass
- * @param sql
- * @param page
- * @param count
- * @param params
- * @return
- */
- public <T> List<T> findPage(Class<T> beanClass, String sql, int page, int pageSize, Object... params) {
- if (page <= 1) {
- page = 0;
- }
- return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[] { page, pageSize }));
- }
- public <T> List<T> query(Class<T> beanClass, String sql, Object... params) {
- try {
- queryRunner = new QueryRunner(dataSource);
- return (List<T>) queryRunner.query(sql, isPrimitive(beanClass) ? columnListHandler : new BeanListHandler(
- beanClass), params);
- } catch (SQLException e) {
- e.printStackTrace();
- }
- returnnull;
- }
- private List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>() {
- {
- add(Long.class);
- add(Integer.class);
- add(String.class);
- add(java.util.Date.class);
- add(java.sql.Date.class);
- add(java.sql.Timestamp.class);
- }
- };
- // 返回单一列时用到的handler
- privatefinalstatic ColumnListHandler columnListHandler = new ColumnListHandler() {
- @Override
- protected Object handleRow(ResultSet rs) throws SQLException {
- Object obj = super.handleRow(rs);
- if (obj instanceof BigInteger)
- return ((BigInteger) obj).longValue();
- return obj;
- }
- };
- // 判断是否为原始类型
- privateboolean isPrimitive(Class<?> cls) {
- return cls.isPrimitive() || PrimitiveClasses.contains(cls);
- }
- // map
- }
public class DBUtilsTemplate {
private DataSource dataSource;
private QueryRunner queryRunner;
private static final Log LOG = LogFactory.getLog(DBUtilsTemplate.class);
public DBUtilsTemplate(DataSource dataSources) {
this();
}
public DBUtilsTemplate() {
dataSource = MyDataSource.getdataSource();
}
/**
*
* @param sql
* 插入sql语句
* @param params
* 插入参数
* @return 返回影响行数
*/
public int insert(String sql, Object[] params) {
queryRunner = new QueryRunner(dataSource);
int affectedRows = 0;
try {
if (params == null) {
affectedRows = queryRunner.update(sql);
} else {
affectedRows = queryRunner.update(sql, params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("insert.插入记录错误:" + sql, e);
}
return affectedRows;
}
/**
* 插入数据库,返回自动增长的主键
*
* @param sql -
* 执行的sql语句
* @return 主键 注意;此方法没关闭资源
*/
public int insertForKeys(String sql, Object[] params) {
int key = 0;
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ParameterMetaData pmd = stmt.getParameterMetaData();
if (params.length < pmd.getParameterCount()) {
throw new SQLException("参数错误:" + pmd.getParameterCount());
}
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if (rs.next()) {
key = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("insertForKey.插入返回主键错误:" + sql, e);
} finally {
if (rs != null) { // 关闭记录集
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) { // 关闭声明
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) { // 关闭连接对象
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return key;
}
private ScalarHandler scalarHandler = new ScalarHandler() {
@Override
public Object handle(ResultSet rs) throws SQLException {
Object obj = super.handle(rs);
if (obj instanceof BigInteger)
return ((BigInteger) obj).longValue();
return obj;
}
};
public long count(String sql, Object... params) {
Number num = 0;
try {
queryRunner = new QueryRunner(dataSource);
if (params == null) {
num = (Number) queryRunner.query(sql, scalarHandler);
} else {
num = (Number) queryRunner.query(sql, scalarHandler, params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("count.统计数量错误" + sql, e);
}
return (num != null) ? num.longValue() : -1;
}
/**
* 执行sql语句
*
* @param sql
* sql语句
* @return 受影响的行数
*/
public int update(String sql) {
return update(sql, null);
}
/**
* 单条修改记录
*
* @param sql
* sql语句
* @param param
* 参数
* @return 受影响的行数
*/
public int update(String sql, Object param) {
return update(sql, new Object[] { param });
}
/**
* 单条修改记录
*
* @param sql
* sql语句
* @param params
* 参数数组
* @return 受影响的行数
*/
public int update(String sql, Object[] params) {
queryRunner = new QueryRunner(dataSource);
int affectedRows = 0;
try {
if (params == null) {
affectedRows = queryRunner.update(sql);
} else {
affectedRows = queryRunner.update(sql, params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("update.单条修改记录错误:" + sql, e);
}
return affectedRows;
}
/**
* 批量修改记录
*
* @param sql
* sql语句
* @param params
* 二维参数数组
* @return 受影响的行数的数组
*/
public int[] batchUpdate(String sql, Object[][] params) {
queryRunner = new QueryRunner(dataSource);
int[] affectedRows = new int[0];
try {
affectedRows = queryRunner.batch(sql, params);
} catch (SQLException e) {
e.printStackTrace();
LOG.error("update.批量修改记录错误:" + sql, e);
}
return affectedRows;
}
/**
* 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
*
* @param sql
* sql语句
* @return 查询结果
*/
public List<Map<String, Object>> find(String sql) {
return find(sql, null);
}
/**
* 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
*
* @param sql
* sql语句
* @param param
* 参数
* @return 查询结果
*/
public List<Map<String, Object>> find(String sql, Object param) {
return find(sql, new Object[] { param });
}
/**
* 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
*
* @param sql
* sql语句
* @param params
* 参数数组
* @return 查询结果
*/
@SuppressWarnings("unchecked")
public List<Map<String, Object>> findPage(String sql, int page, int count, Object... params) {
sql = sql + " LIMIT ?,?";
queryRunner = new QueryRunner(dataSource);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
if (params == null) {
list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), new Integer[] { page,
count });
} else {
list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), ArrayUtils.addAll(
params, new Integer[] { page, count }));
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("map 数据分页查询错误", e);
}
return list;
}
/**
* 执行查询,将每行的结果保存到一个Map对象中,然后将所有Map对象保存到List中
*
* @param sql
* sql语句
* @param params
* 参数数组
* @return 查询结果
*/
@SuppressWarnings("unchecked")
public List<Map<String, Object>> find(String sql, Object[] params) {
queryRunner = new QueryRunner(dataSource);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
if (params == null) {
list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler());
} else {
list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("map 数据查询错误", e);
}
return list;
}
/**
* 执行查询,将每行的结果保存到Bean中,然后将所有Bean保存到List中
*
* @param entityClass
* 类名
* @param sql
* sql语句
* @return 查询结果
*/
public <T> List<T> find(Class<T> entityClass, String sql) {
return find(entityClass, sql, null);
}
/**
* 执行查询,将每行的结果保存到Bean中,然后将所有Bean保存到List中
*
* @param entityClass
* 类名
* @param sql
* sql语句
* @param param
* 参数
* @return 查询结果
*/
public <T> List<T> find(Class<T> entityClass, String sql, Object param) {
return find(entityClass, sql, new Object[] { param });
}
/**
* 执行查询,将每行的结果保存到Bean中,然后将所有Bean保存到List中
*
* @param entityClass
* 类名
* @param sql
* sql语句
* @param params
* 参数数组
* @return 查询结果
*/
@SuppressWarnings("unchecked")
public <T> List<T> find(Class<T> entityClass, String sql, Object[] params) {
queryRunner = new QueryRunner(dataSource);
List<T> list = new ArrayList<T>();
try {
if (params == null) {
list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass));
} else {
list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass), params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("Error occured while attempting to query data", e);
}
return list;
}
/**
* 查询出结果集中的第一条记录,并封装成对象
*
* @param entityClass
* 类名
* @param sql
* sql语句
* @return 对象
*/
public <T> T findFirst(Class<T> entityClass, String sql) {
return findFirst(entityClass, sql, null);
}
/**
* 查询出结果集中的第一条记录,并封装成对象
*
* @param entityClass
* 类名
* @param sql
* sql语句
* @param param
* 参数
* @return 对象
*/
public <T> T findFirst(Class<T> entityClass, String sql, Object param) {
return findFirst(entityClass, sql, new Object[] { param });
}
/**
* 查询出结果集中的第一条记录,并封装成对象
*
* @param entityClass
* 类名
* @param sql
* sql语句
* @param params
* 参数数组
* @return 对象
*/
@SuppressWarnings("unchecked")
public <T> T findFirst(Class<T> entityClass, String sql, Object[] params) {
queryRunner = new QueryRunner(dataSource);
Object object = null;
try {
if (params == null) {
object = queryRunner.query(sql, new BeanHandler(entityClass));
} else {
object = queryRunner.query(sql, new BeanHandler(entityClass), params);
}
} catch (SQLException e) {
LOG.error("返回一条记录错误:findFirst" + e.getMessage());
e.printStackTrace();
}
return (T) object;
}
/**
* 查询出结果集中的第一条记录,并封装成Map对象
*
* @param sql
* sql语句
* @return 封装为Map的对象
*/
public Map<String, Object> findFirst(String sql) {
return findFirst(sql, null);
}
/**
* 查询出结果集中的第一条记录,并封装成Map对象
*
* @param sql
* sql语句
* @param param
* 参数
* @return 封装为Map的对象
*/
public Map<String, Object> findFirst(String sql, Object param) {
return findFirst(sql, new Object[] { param });
}
/**
* 查询出结果集中的第一条记录,并封装成Map对象
*
* @param sql
* sql语句
* @param params
* 参数数组
* @return 封装为Map的对象
*/
@SuppressWarnings("unchecked")
public Map<String, Object> findFirst(String sql, Object[] params) {
queryRunner = new QueryRunner(dataSource);
Map<String, Object> map = null;
try {
if (params == null) {
map = (Map<String, Object>) queryRunner.query(sql, new MapHandler());
} else {
map = (Map<String, Object>) queryRunner.query(sql, new MapHandler(), params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("findFirst.查询一条记录错误" + sql, e);
}
return map;
}
/**
* 查询某一条记录,并将指定列的数据转换为Object
*
* @param sql
* sql语句
* @param columnName
* 列名
* @return 结果对象
*/
public Object findBy(String sql, String params) {
return findBy(sql, params, null);
}
/**
* 查询某一条记录,并将指定列的数据转换为Object
*
* @param sql
* sql语句
* @param columnName
* 列名
* @param param
* 参数
* @return 结果对象
*/
public Object findBy(String sql, String columnName, Object param) {
return findBy(sql, columnName, new Object[] { param });
}
/**
* 查询某一条记录,并将指定列的数据转换为Object
*
* @param sql
* sql语句
* @param columnName
* 列名
* @param params
* 参数数组
* @return 结果对象
*/
public Object findBy(String sql, String columnName, Object[] params) {
queryRunner = new QueryRunner(dataSource);
Object object = null;
try {
if (params == null) {
object = queryRunner.query(sql, new ScalarHandler(columnName));
} else {
object = queryRunner.query(sql, new ScalarHandler(columnName), params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("findBy。错误" + sql, e);
}
return object;
}
/**
* 查询某一条记录,并将指定列的数据转换为Object
*
* @param sql
* sql语句
* @param columnIndex
* 列索引
* @return 结果对象
*/
public Object findBy(String sql, int columnIndex) {
return findBy(sql, columnIndex, null);
}
/**
* 查询某一条记录,并将指定列的数据转换为Object
*
* @param sql
* sql语句
* @param columnIndex
* 列索引
* @param param
* 参数
* @return 结果对象
*/
public Object findBy(String sql, int columnIndex, Object param) {
return findBy(sql, columnIndex, new Object[] { param });
}
/**
* 查询某一条记录,并将指定列的数据转换为Object
*
* @param sql
* sql语句
* @param columnIndex
* 列索引
* @param params
* 参数数组
* @return 结果对象
*/
public Object findBy(String sql, int columnIndex, Object[] params) {
queryRunner = new QueryRunner(dataSource);
Object object = null;
try {
if (params == null) {
object = queryRunner.query(sql, new ScalarHandler(columnIndex));
} else {
object = queryRunner.query(sql, new ScalarHandler(columnIndex), params);
}
} catch (SQLException e) {
e.printStackTrace();
LOG.error("findBy.错误" + sql, e);
}
return object;
}
/**
*
* @param <T>分页查询
* @param beanClass
* @param sql
* @param page
* @param count
* @param params
* @return
*/
public <T> List<T> findPage(Class<T> beanClass, String sql, int page, int pageSize, Object... params) {
if (page <= 1) {
page = 0;
}
return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[] { page, pageSize }));
}
public <T> List<T> query(Class<T> beanClass, String sql, Object... params) {
try {
queryRunner = new QueryRunner(dataSource);
return (List<T>) queryRunner.query(sql, isPrimitive(beanClass) ? columnListHandler : new BeanListHandler(
beanClass), params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
private List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>() {
{
add(Long.class);
add(Integer.class);
add(String.class);
add(java.util.Date.class);
add(java.sql.Date.class);
add(java.sql.Timestamp.class);
}
};
// 返回单一列时用到的handler
private final static ColumnListHandler columnListHandler = new ColumnListHandler() {
@Override
protected Object handleRow(ResultSet rs) throws SQLException {
Object obj = super.handleRow(rs);
if (obj instanceof BigInteger)
return ((BigInteger) obj).longValue();
return obj;
}
};
// 判断是否为原始类型
private boolean isPrimitive(Class<?> cls) {
return cls.isPrimitive() || PrimitiveClasses.contains(cls);
}
// map
}
第二部分:
- publicclass PageHelp {
- privateint pageSize;
- privateint totalCount;
- privateint currentPage;
- privateint startIndex;
- privateint[] indexes = newint[0];
- privateint nextIndex;
- privateint previousIndex;
- privateint pageCount;
- private List items;
- privateint lastIndex;
- private String currentUrl;
- public String getCurrentUrl() {
- return currentUrl;
- }
- publicvoid setCurrentUrl(String currentUrl) {
- this.currentUrl = currentUrl;
- }
- public PageHelp(List items, int totalCount, int startIndex) {
- pageSize = Constants.PAGE_SIZE;
- setPageSize(pageSize);
- setTotalCount(totalCount);
- setItems(items);
- setStartIndex(startIndex);
- }
- publicvoid setTotalCount(int totalCount) {
- if (totalCount > 0) {
- this.totalCount = totalCount;
- int count = totalCount / pageSize;
- if (totalCount % pageSize > 0) {
- count++;
- }
- indexes = newint[count];
- for (int i = 0; i < count; i++) {
- indexes[i] = pageSize * i;
- }
- } else {
- this.totalCount = 0;
- }
- }
- /**
- * 得到总记录数
- *
- * @return
- */
- publicint getTotalCount() {
- return totalCount;
- }
- publicvoid setIndexes(int[] indexes) {
- this.indexes = indexes;
- }
- /**
- * 得到分页索引的数组
- *
- * @return
- */
- publicint[] getIndexes() {
- return indexes;
- }
- publicvoid setStartIndex(int startIndex) {
- if (totalCount <= 0) {
- this.startIndex = 0;
- } elseif (startIndex >= totalCount) {
- this.startIndex = indexes[indexes.length - 1];
- } elseif (startIndex < 0) {
- this.startIndex = 0;
- } else {
- this.startIndex = indexes[startIndex / pageSize];
- }
- }
- /**
- * 当前页
- *
- * @return
- */
- publicint getStartIndex() {
- return startIndex;
- }
- publicvoid setNextIndex(int nextIndex) {
- this.nextIndex = nextIndex;
- }
- /**
- * 下一页
- *
- * @return
- */
- publicint getNextIndex() {
- int nextIndex = getStartIndex() + pageSize;
- if (nextIndex >= totalCount) {
- return getStartIndex();
- } else {
- return nextIndex;
- }
- }
- publicvoid setPreviousIndex(int previousIndex) {
- this.previousIndex = previousIndex;
- }
- /**
- * 上一页
- *
- * @return
- */
- publicint getPreviousIndex() {
- int previousIndex = getStartIndex() - pageSize;
- if (previousIndex < 0) {
- return0;
- } else {
- return previousIndex;
- }
- }
- publicvoid setPageCount(int pageCount) {
- this.pageCount = pageCount;
- }
- publicint getPageCount() {
- int count = totalCount / pageSize;
- if (totalCount % pageSize > 0)
- count++;
- return count;
- }
- publicint getCurrentPage() {
- return getStartIndex() / pageSize + 1;
- }
- publicvoid setCurrentPage(int currentPage) {
- this.currentPage = currentPage;
- }
- publicvoid setLastIndex(int lastIndex) {
- this.lastIndex = lastIndex;
- }
- publicint getLastIndex() {
- if (indexes.length == 0) {
- return0;
- } else {
- return indexes[indexes.length - 1];
- }
- }
- publicint getPageSize() {
- return pageSize;
- }
- publicvoid setPageSize(int pageSize) {
- this.pageSize = pageSize;
- }
- /**
- * 得到已分页好的结果集
- *
- * @return
- */
- public List getItems() {
- return items;
- }
- publicvoid setItems(List items) {
- this.items = items;
- }
public class PageHelp {
private int pageSize;
private int totalCount;
private int currentPage;
private int startIndex;
private int[] indexes = new int[0];
private int nextIndex;
private int previousIndex;
private int pageCount;
private List items;
private int lastIndex;
private String currentUrl;
public String getCurrentUrl() {
return currentUrl;
}
public void setCurrentUrl(String currentUrl) {
this.currentUrl = currentUrl;
}
public PageHelp(List items, int totalCount, int startIndex) {
pageSize = Constants.PAGE_SIZE;
setPageSize(pageSize);
setTotalCount(totalCount);
setItems(items);
setStartIndex(startIndex);
}
public void setTotalCount(int totalCount) {
if (totalCount > 0) {
this.totalCount = totalCount;
int count = totalCount / pageSize;
if (totalCount % pageSize > 0) {
count++;
}
indexes = new int[count];
for (int i = 0; i < count; i++) {
indexes[i] = pageSize * i;
}
} else {
this.totalCount = 0;
}
}
/**
* 得到总记录数
*
* @return
*/
public int getTotalCount() {
return totalCount;
}
public void setIndexes(int[] indexes) {
this.indexes = indexes;
}
/**
* 得到分页索引的数组
*
* @return
*/
public int[] getIndexes() {
return indexes;
}
public void setStartIndex(int startIndex) {
if (totalCount <= 0) {
this.startIndex = 0;
} else if (startIndex >= totalCount) {
this.startIndex = indexes[indexes.length - 1];
} else if (startIndex < 0) {
this.startIndex = 0;
} else {
this.startIndex = indexes[startIndex / pageSize];
}
}
/**
* 当前页
*
* @return
*/
public int getStartIndex() {
return startIndex;
}
public void setNextIndex(int nextIndex) {
this.nextIndex = nextIndex;
}
/**
* 下一页
*
* @return
*/
public int getNextIndex() {
int nextIndex = getStartIndex() + pageSize;
if (nextIndex >= totalCount) {
return getStartIndex();
} else {
return nextIndex;
}
}
public void setPreviousIndex(int previousIndex) {
this.previousIndex = previousIndex;
}
/**
* 上一页
*
* @return
*/
public int getPreviousIndex() {
int previousIndex = getStartIndex() - pageSize;
if (previousIndex < 0) {
return 0;
} else {
return previousIndex;
}
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getPageCount() {
int count = totalCount / pageSize;
if (totalCount % pageSize > 0)
count++;
return count;
}
public int getCurrentPage() {
return getStartIndex() / pageSize + 1;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public void setLastIndex(int lastIndex) {
this.lastIndex = lastIndex;
}
public int getLastIndex() {
if (indexes.length == 0) {
return 0;
} else {
return indexes[indexes.length - 1];
}
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
/**
* 得到已分页好的结果集
*
* @return
*/
public List getItems() {
return items;
}
public void setItems(List items) {
this.items = items;
}
使用方法:
考虑到分层的话可以这样使用:
- publicclass CommonDaoImpl extends DBUtilsTemplate implements CommonDao
public class CommonDaoImpl extends DBUtilsTemplate implements CommonDao
还可以直接new DBUtilsTemplate () 使用。
分页使用方法:
- //(String sql, int page, int count, Object... params);
- List list= ucd.findPage(sql, p, Constants.PAGE_SIZE, "y");
- int totalRows=总记录数
- returnnew PageHelp(list, totalRows, p);
//(String sql, int page, int count, Object... params);
List list= ucd.findPage(sql, p, Constants.PAGE_SIZE, "y");
int totalRows=总记录数
return new PageHelp(list, totalRows, p);
在jsp中显示分页方法:
- <table width="100%" border="0" align="center" cellpadding="0"
- cellspacing="0">
- <tr align="center">
- <td>
- <a href="${pageList.currentUrl}&page=0">第一页</a>
- <a href="${pageList.currentUrl}&page=${pageList.previousIndex}">上一页</a>
- <c:forEach items="${pageList.indexes}" var="itempage"
- varStatus="stuts">
- <c:choose>
- <c:when test="${pageList.currentPage ==stuts.index+1}">
- <a style="color: red"> ${stuts.index+1}</a>
- </c:when>
- <c:otherwise>
- </c:otherwise>
- </c:choose>
- </c:forEach>
-
- <a href="${pageList.currentUrl}&page=${pageList.nextIndex}">
- 下一页</a>
- <a href="${pageList.currentUrl}&page=${pageList.lastIndex}">最后页</a>
- 总数: ${ pageList.totalCount}
- 总页数: ${ pageList.pageCount}
- </td>
- </tr>
- </table>
<table width="100%" border="0" align="center" cellpadding="0"
cellspacing="0">
<tr align="center">
<td>
<a href="${pageList.currentUrl}&page=0">第一页</a>
<a href="${pageList.currentUrl}&page=${pageList.previousIndex}">上一页</a>
<c:forEach items="${pageList.indexes}" var="itempage"
varStatus="stuts">
<c:choose>
<c:when test="${pageList.currentPage ==stuts.index+1}">
<a style="color: red"> ${stuts.index+1}</a>
</c:when>
<c:otherwise>
</c:otherwise>
</c:choose>
</c:forEach>
<a href="${pageList.currentUrl}&page=${pageList.nextIndex}">
下一页</a>
<a href="${pageList.currentUrl}&page=${pageList.lastIndex}">最后页</a>
总数: ${ pageList.totalCount}
总页数: ${ pageList.pageCount}
</td>
</tr>
</table>
解释:currentUrl 是在action中 获取PageHelp 对象后,对他的属性currentUrl 重新赋值(即:当前请求url)
放上去一个效果图吧:
补充:数据源,使用的是dbcp
- publicclass MyDataSource {
- privatestatic Properties properties;
- privatestatic DataSource dataSource;
- static {
- try {
- properties = new Properties();
- properties.load(MyDataSource.class.getResourceAsStream("/dbcpconfig.properties"));
- BasicDataSourceFactory b = new BasicDataSourceFactory();
- dataSource = b.createDataSource(properties);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- publicstatic DataSource getdataSource() {
- return dataSource;
- }
- }
public class MyDataSource {
private static Properties properties;
private static DataSource dataSource;
static {
try {
properties = new Properties();
properties.load(MyDataSource.class.getResourceAsStream("/dbcpconfig.properties"));
BasicDataSourceFactory b = new BasicDataSourceFactory();
dataSource = b.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource getdataSource() {
return dataSource;
}
}
数据源配置文件:
- #连接设置
- driverClassName=com.mysql.jdbc.Driver
- url=jdbc:mysql://localhost:3306/cc
- username=root
- password=cc
- #初始化连接
- initialSize=5
- #最大连接数量
- maxActive=40
- #最大空闲连接
- maxIdle=20
- #最小空闲连接
- minIdle=5
- #超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
- maxWait=10000
- #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
- #注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
- connectionProperties=useUnicode=true;characterEncoding=UTF-8
- #指定由连接池所创建的连接的自动提交(auto-commit)状态。
- defaultAutoCommit=true
- #driver default 指定由连接池所创建的连接的只读(read-only)状态。
- #如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
- defaultReadOnly=
- #是否自动回收超时连接(一般是忘了释放的)
- removeAbandoned=true
- # 将被遗弃的数据库连接的回收记入日志。
- logAbandoned=true
- # 数据库连接过多长时间不用将被视为被遗弃而收回连接池中。
- removeAbandonedTimeout=30
- #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
- #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
- #defaultTransactionIsolation=REPEATABLE_READ
- #db.pool.default.whenExhaustedAction=grow
- #新增参数,用于8小时问题
- testOnBorrow=true
- testOnReturn=false
- testWhileIdle=true
- validationQuery=select 1