Java tutorial
/** * Copyright (c) 2012-2014 http://www.eryansky.com * * Licensed under the Apache License, Version 2.0 (the "License"); */ package com.eryansky.core.db; import java.math.BigInteger; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import javax.sql.DataSource; import com.eryansky.common.utils.collections.Collections3; import org.apache.commons.dbcp.BasicDataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.eryansky.common.exception.DaoException; import com.eryansky.common.orm.Page; import com.eryansky.common.orm.PageSqlUtils; /** * Apache Commons DBUtil?? DBCP????Spring?? <br> * <br> ?MySQL?Oracle?Postgresql. * <br> .<code> * @Autowired * private DbUtilsDao dbUtilsDao; * public void setDbUtilsDao(DbUtilsDao dbUtilsDao) { * this.dbUtilsDao = dbUtilsDao; * } * </code> * * @author &Eryan eryanwcp@gmail.com * @date 2013-4-16 ?8:08:51 * @version 1.0 */ //@Repository public class DbUtilsDao { private Logger logger = LoggerFactory.getLogger(getClass()); private DataSource dataSource; private QueryRunner queryRunner; public DbUtilsDao() { } public DbUtilsDao(BasicDataSource dataSource) { this.dataSource = dataSource; } // DBCP?? public void setDataSource(BasicDataSource dataSource) { this.dataSource = dataSource; } public DataSource getDataSource() { return dataSource; } /** * sql?,????? * <br>?. * <br>Oracle?:SEQ_NAME.nextval * @param sql * sql? * @return ?? * @deprecated */ public int update(String sql) throws DaoException { return update(sql, null); } /** * sql?,????? <code> * <br>?. * executeUpdate("update user set username = 'kitty' where username = ?", "hello kitty"); * * </code> * * @param sql * sql? * @param param * ? * @return ?? * @deprecated */ public int update(String sql, Object param) throws DaoException { return update(sql, new Object[] { param }); } /** * sql?,????? * <br>?. * @param sql * sql? * @param params * ? * @return ?? * @deprecated */ public int update(String sql, Object[] params) throws DaoException { queryRunner = new QueryRunner(dataSource); int affectedRows = 0; try { if (params == null) { affectedRows = queryRunner.update(sql); } else { affectedRows = queryRunner.update(sql, params); } } catch (SQLException e) { logger.error("Error occured while attempting to update data", e); throw new DaoException(e); } return affectedRows; } /** * ?sql?,????? * <br>?. * @param sql * sql? * @param params * ? * @return ?? * @deprecated */ public int[] batchUpdate(String sql, Object[][] params) throws DaoException { queryRunner = new QueryRunner(dataSource); int[] affectedRows = new int[0]; try { affectedRows = queryRunner.batch(sql, params); } catch (SQLException e) { logger.error("Error occured while attempting to batch update data", e); throw new DaoException(e); } return affectedRows; } /** * ??Map?Map?List * * @param sql * sql? * @return */ public List<Map<String, Object>> find(String sql) throws DaoException { return find(sql, null); } /** * ??Map?Map?List * * @param sql * sql? * @param param * ? * @return */ public List<Map<String, Object>> find(String sql, Object param) throws DaoException { return find(sql, new Object[] { param }); } /** * ??Map?Map?List * * @param sql * sql? * @param params * ? * @return */ public List<Map<String, Object>> find(String sql, Object[] params) throws DaoException { 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) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return list; } /** * ??Bean?Bean?List * * @param entityClass * ?? * @param sql * sql? * @return */ public <T> List<T> find(Class<T> entityClass, String sql) throws DaoException { 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) throws DaoException { return find(entityClass, sql, new Object[] { param }); } /** * ??Bean?Bean?List * * @param entityClass * ?? * @param sql * sql? * @param params * ? * @return */ @SuppressWarnings({ "unchecked", "rawtypes" }) public <T> List<T> find(Class<T> entityClass, String sql, Object[] params) throws DaoException { 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) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return list; } /** * ??Bean?Bean?List * * @param entityClass * ?? * @param sql * sql? * @param page * ? * @param pageSize * ?? * @return */ public <T> List<T> find(Class<T> entityClass, String sql, int page, int pageSize) throws DaoException { return find(entityClass, sql, null, page, pageSize); } /** * ??Bean?Bean?List * * @param entityClass * ?? * @param sql * sql? * @param param * ? * @param page * ? * @param pageSize * ?? * @return */ public <T> List<T> find(Class<T> entityClass, String sql, Object param, int page, int pageSize) throws DaoException { return find(entityClass, sql, new Object[] { param }, page, pageSize); } /** * ??Bean?Bean?List * * @param entityClass * ?? * @param sql * sql? * @param params * ? * @param page * ? * @param pageSize * ?? * @return */ @SuppressWarnings({ "unchecked", "rawtypes" }) public <T> List<T> find(Class<T> entityClass, String sql, Object[] params, int page, int pageSize) throws DaoException { queryRunner = new QueryRunner(dataSource); List<T> list = new ArrayList<T>(); // int startFlag = (((page < 1 ? 1 : page) - 1) * pageSize); String pageSql = PageSqlUtils.createPageSql(sql, page, pageSize); try { if (params == null) { list = (List<T>) queryRunner.query(pageSql, new BeanListHandler(entityClass)); } else { list = (List<T>) queryRunner.query(pageSql, new BeanListHandler(entityClass), params); } } catch (SQLException e) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return list; } /** * ??Bean?Bean?List,?List??PageResult * * @param entityClass * ?? * @param sql * sql? * @param page * ? * @param pageSize * ?? * @return PageResult */ public <T> Page<T> findPage(Class<T> entityClass, String sql, int page, int pageSize) throws DaoException { return findPage(entityClass, sql, null, page, pageSize); } /** * ??Bean?Bean?List,?List??PageResult * * @param entityClass * ?? * @param sql * sql? * @param param * ? * @param page * ? * @param pageSize * ?? * @return PageResult */ public <T> Page<T> findPage(Class<T> entityClass, String sql, Object param, int page, int pageSize) throws DaoException { return findPage(entityClass, sql, new Object[] { param }, page, pageSize); } /** * ??Bean?Bean?List,?List??PageResult * * @param entityClass * ?? * @param sql * sql? : "select * T_BASE_MENU where name = ?" * @param params * ? * @param page * ? * @param pageSize * ?? * @return PageResult */ @SuppressWarnings({ "unchecked", "rawtypes" }) public <T> Page<T> findPage(Class<T> entityClass, String sql, Object[] params, int page, int pageSize) throws DaoException { queryRunner = new QueryRunner(dataSource); List<T> list = new ArrayList<T>(); int startPage = page < 1 ? 1 : page; // int startFlag = ((startPage - 1) * pageSize); String pageSql = PageSqlUtils.createPageSql(sql, startPage, pageSize); try { if (params == null) { list = (List<T>) queryRunner.query(pageSql, new BeanListHandler(entityClass)); } else { list = (List<T>) queryRunner.query(pageSql, new BeanListHandler(entityClass), params); } } catch (SQLException e) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } // long count = getCount(sql, params); int newCurrentPage = getBeginPage(startPage, pageSize, count); Page<T> p = new Page<T>(pageSize); p.setPageNo(newCurrentPage); p.setResult(list); p.setTotalCount(count); return p; } /** * ??? * * @param entityClass * ?? * @param sql * sql? * @return */ public <T> T findFirst(Class<T> entityClass, String sql) throws DaoException { return findFirst(entityClass, sql, null); } /** * ??? * * @param entityClass * ?? * @param sql * sql? * @param param * ? * @return */ public <T> T findFirst(Class<T> entityClass, String sql, Object param) throws DaoException { return findFirst(entityClass, sql, new Object[] { param }); } /** * ??? * * @param entityClass * ?? * @param sql * sql? * @param params * ? * @return */ @SuppressWarnings({ "unchecked", "rawtypes" }) public <T> T findFirst(Class<T> entityClass, String sql, Object[] params) throws DaoException { 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) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return (T) object; } /** * ???Map * * @param sql * sql? * @return ?Map */ public Map<String, Object> findFirst(String sql) throws DaoException { return findFirst(sql, null); } /** * ???Map * * @param sql * sql? * @param param * ? * @return ?Map */ public Map<String, Object> findFirst(String sql, Object param) throws DaoException { return findFirst(sql, new Object[] { param }); } /** * ???Map * * @param sql * sql? * @param params * ? * @return ?Map */ public Map<String, Object> findFirst(String sql, Object[] params) throws DaoException { 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) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return map; } /** * ????Object * * @param sql * sql? * @param columnName * ?? * @return */ public Object findBy(String sql, String columnName) throws DaoException { return findBy(sql, columnName, null); } /** * ????Object * * @param sql * sql? * @param columnName * ?? * @param param * ? * @return */ public Object findBy(String sql, String columnName, Object param) throws DaoException { return findBy(sql, columnName, new Object[] { param }); } /** * ????Object * * @param sql * sql? * @param columnName * ?? * @param params * ? * @return */ @SuppressWarnings({ "rawtypes", "unchecked" }) public Object findBy(String sql, String columnName, Object[] params) throws DaoException { 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) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return object; } /** * ????Object * * @param sql * sql? * @param columnIndex * * @return */ public Object findBy(String sql, int columnIndex) throws DaoException { return findBy(sql, columnIndex, null); } /** * ????Object * * @param sql * sql? * @param columnIndex * * @param param * ? * @return */ public Object findBy(String sql, int columnIndex, Object param) throws DaoException { return findBy(sql, columnIndex, new Object[] { param }); } /** * ????Object * * @param sql * sql? * @param columnIndex * * @param params * ? * @return */ @SuppressWarnings({ "rawtypes", "unchecked" }) public Object findBy(String sql, int columnIndex, Object[] params) throws DaoException { 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) { logger.error("Error occured while attempting to query data", e); throw new DaoException(e); } return object; } /** * ? * * @param sql * sql? * @return */ public long getCount(String sql) throws DaoException { return getCount(sql, null); } /** * ? * * @param sql * sql? * @param param * ? * @return */ public long getCount(String sql, Object param) throws DaoException { return getCount(sql, new Object[] { param }); } /** * ? * * @param sql * sql? * @param params * ? * @return */ public long getCount(String sql, Object[] params) throws DaoException { String newSql = "select count(1) from (" + sql + ") _c"; if (params == null) { return ((Long) findBy(newSql, 1)).intValue(); } else { return ((Long) findBy(newSql, 1, params)).intValue(); } } private int getBeginPage(int begenPage, int pageSize, long count) { if (count == 0) { return 1; } int newCurrentPage = begenPage; if (begenPage > 1) { if ((begenPage - 1) * pageSize >= count) { newCurrentPage = (int) (Math.ceil((count * 1.0) / pageSize)); } } return newCurrentPage; } /** * ???? Microsoft SQL Server / Oracle .... * @return * @throws com.eryansky.common.exception.DaoException */ protected String getDatabaseProductName() throws DaoException { try { Connection conn = this.dataSource.getConnection(); return conn.getMetaData().getDatabaseProductName(); } catch (SQLException ex) { throw new DaoException(ex); } } /** * ?Catalog * @return String * @throws com.eryansky.common.exception.DaoException */ protected String GetCurrentCatalog() throws DaoException { //Microsoft SQL Server????SQL? //final String sql = "select db_name()"; try { Connection conn = this.dataSource.getConnection(); return conn.getCatalog(); } catch (SQLException ex) { throw new DaoException(ex); } } 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) throws DaoException { Number num = 0; try { queryRunner = new QueryRunner(dataSource); if (params == null || params.length == 0) { num = (Number) queryRunner.query(sql, scalarHandler); } else { num = (Number) queryRunner.query(sql, scalarHandler, params); } } catch (SQLException e) { // e.printStackTrace(); logger.error(",SQL:" + sql, e); throw new DaoException(e); } return (num != null) ? num.longValue() : -1; } }