Java tutorial
/* * Created on 13-6-5 * * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except * in compliance with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software distributed under the License * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express * or implied. See the License for the specific language governing permissions and limitations under * the License. * * Copyright @2013 the original author or authors. */ package dbutils; import java.sql.*; import java.util.ArrayList; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.dbutils.DbUtils; 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; /** * Apache Commons DBUtil?? * c3p0????Spring?? * ?Spring??set??? * <code> * private DbUtilsTemplate dbUtilsTemplate; * public void setDbUtilsTemplate(DbUtilsTemplate dbUtilsTemplate) { * this.dbUtilsTemplate = dbUtilsTemplate; * } * * @author XiongNeng * @version 1.0 * @since 13-6-5 */ public class DbUtilsTemplate { private DataSource dataSource; private QueryRunner queryRunner; private boolean pmdKnownBroken = false; private static final Logger LOG = LoggerFactory.getLogger(DbUtilsTemplate.class); public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } public void setPmdKnownBroken(boolean pmdKnownBroken) { this.pmdKnownBroken = pmdKnownBroken; } /** * sql? * * @param sql sql? * @return ?? */ public int update(String sql) throws SQLException { return update(sql, null); } /** * sql? * <code> * executeUpdate("update user set username = 'kitty' where username = ?", "hello kitty"); * </code> * * @param sql sql? * @param param ? * @return ?? */ public int update(String sql, Object param) throws SQLException { return update(sql, new Object[] { param }); } /** * sql?,????? * * @param sql sql? * @param params ? * @return ?? */ public int update(String sql, Object[] params) throws SQLException { queryRunner = new QueryRunner(); int affectedRows = 0; Connection conn = null; try { conn = dataSource.getConnection(); if (params == null) { affectedRows = queryRunner.update(conn, sql); } else { affectedRows = queryRunner.update(conn, sql, params); } } catch (SQLException e) { LOG.error("Error occured while attempting to update data", e); if (conn != null) { conn.rollback(); } throw e; } finally { if (conn != null) DbUtils.commitAndClose(conn); } return affectedRows; } /** * sql? * * @param sql sql? * @return ?? */ public long insert(String sql) throws SQLException { return insert(sql, null); } /** * sql? * <code> * executeUpdate("insert user(name, age) values(?,?)); * </code> * * @param sql sql? * @param param ? * @return ?? */ public long insert(String sql, Object param) throws SQLException { return insert(sql, new Object[] { param }); } /** * ?? * * @param sql sql? * @param params ? * @return (?, -1) * @throws SQLException */ public long insert(String sql, Object[] params) throws SQLException { long result = -1L; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); fillStatement(stmt, params); int affectCount = stmt.executeUpdate(); if (affectCount <= 0) return -1L; rs = stmt.getGeneratedKeys(); result = rs.next() ? rs.getLong(1) : -1; conn.commit(); } catch (SQLException e) { LOG.error("Error occured while attempting to insert data", e); if (conn != null) { conn.rollback(); } throw e; } finally { DbUtils.closeQuietly(conn, stmt, rs); } return result; } public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException { // check the parameter count, if we can ParameterMetaData pmd = null; if (!pmdKnownBroken) { pmd = stmt.getParameterMetaData(); int stmtCount = pmd.getParameterCount(); int paramsCount = params == null ? 0 : params.length; if (stmtCount != paramsCount) { throw new SQLException( "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount); } } // nothing to do here if (params == null) { return; } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { // VARCHAR works with many drivers regardless // of the actual column type. Oddly, NULL and // OTHER don't work with Oracle's drivers. int sqlType = Types.VARCHAR; if (!pmdKnownBroken) { try { /* * It's not possible for pmdKnownBroken to change from * true to false, (once true, always true) so pmd cannot * be null here. */ sqlType = pmd.getParameterType(i + 1); } catch (SQLException e) { pmdKnownBroken = true; } } stmt.setNull(i + 1, sqlType); } } } /** * ?sql? * * @param sql sql? * @param params ? * @return ?? */ public int[] batchUpdate(String sql, Object[][] params) throws SQLException { queryRunner = new QueryRunner(); int[] affectedRows = new int[0]; Connection conn = null; try { conn = dataSource.getConnection(); affectedRows = queryRunner.batch(conn, sql, params); } catch (SQLException e) { LOG.error("Error occured while attempting to batch update data", e); if (conn != null) { conn.rollback(); } throw e; } finally { if (conn != null) { DbUtils.commitAndClose(conn); } } 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 */ public List<Map<String, Object>> find(String sql, Object[] params) { queryRunner = new QueryRunner(); List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Connection conn = null; try { conn = dataSource.getConnection(); if (params == null) { list = queryRunner.query(conn, sql, new MapListHandler()); } else { list = queryRunner.query(conn, sql, new MapListHandler(), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } 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(); Connection conn = null; List<T> list = new ArrayList<T>(); try { conn = dataSource.getConnection(); if (params == null) { list = (List<T>) queryRunner.query(conn, sql, new BeanListHandler(entityClass)); } else { list = (List<T>) queryRunner.query(conn, sql, new BeanListHandler(entityClass), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } 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) { 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) { 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") public <T> List<T> find(Class<T> entityClass, String sql, Object[] params, int page, int pageSize) { queryRunner = new QueryRunner(); Connection conn = null; List<T> list = new ArrayList<T>(); int startFlag = (((page < 1 ? 1 : page) - 1) * pageSize); String pageSql = " limit " + startFlag + " , " + startFlag + pageSize; try { conn = dataSource.getConnection(); if (params == null) { list = (List<T>) queryRunner.query(conn, sql + pageSql, new BeanListHandler(entityClass)); } else { list = (List<T>) queryRunner.query(conn, sql + pageSql, new BeanListHandler(entityClass), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } return list; } /** * ??Bean?Bean?List,?List??PageResult * * @param entityClass ?? * @param sql sql? * @param page ? * @param pageSize ?? * @return PageResult */ public <T> PageResult findPageResult(Class<T> entityClass, String sql, int page, int pageSize) { return findPageResult(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> PageResult findPageResult(Class<T> entityClass, String sql, Object param, int page, int pageSize) { return findPageResult(entityClass, sql, new Object[] { param }, page, pageSize); } /** * ??Bean?Bean?List,?List??PageResult * * @param entityClass ?? * @param sql sql? * @param params ? * @param page ? * @param pageSize ?? * @return PageResult */ @SuppressWarnings("unchecked") public <T> PageResult findPageResult(Class<T> entityClass, String sql, Object[] params, int page, int pageSize) { queryRunner = new QueryRunner(); Connection conn = null; List<T> list = new ArrayList<T>(); int startPage = page < 1 ? 1 : page; int startFlag = ((startPage - 1) * pageSize); String pageSql = " limit " + startFlag + " , " + startFlag + pageSize; try { conn = dataSource.getConnection(); if (params == null) { list = (List<T>) queryRunner.query(conn, sql + pageSql, new BeanListHandler(entityClass)); } else { list = (List<T>) queryRunner.query(conn, sql + pageSql, new BeanListHandler(entityClass), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } // int count = getCount(sql, params); // ?? int currentPage = getBeginPage(startPage, pageSize, count); return new PageResult(currentPage, pageSize, list, count); } /** * ??? * * @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(); Connection conn = null; Object object = null; try { conn = dataSource.getConnection(); if (params == null) { object = queryRunner.query(conn, sql, new BeanHandler(entityClass)); } else { object = queryRunner.query(conn, sql, new BeanHandler(entityClass), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } 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(); Connection conn = null; Map<String, Object> map = null; try { conn = dataSource.getConnection(); if (params == null) { map = queryRunner.query(conn, sql, new MapHandler()); } else { map = queryRunner.query(conn, sql, new MapHandler(), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } return map; } /** * ????Object * * @param sql sql? * @param columnName ?? * @return */ public Object findBy(String sql, String columnName) { return findBy(sql, columnName, 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(); Connection conn = null; Object object = null; try { conn = dataSource.getConnection(); if (params == null) { object = queryRunner.query(conn, sql, new ScalarHandler(columnName)); } else { object = queryRunner.query(conn, sql, new ScalarHandler(columnName), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } 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(); Connection conn = null; Object object = null; try { conn = dataSource.getConnection(); if (params == null) { object = queryRunner.query(conn, sql, new ScalarHandler(columnIndex)); } else { object = queryRunner.query(conn, sql, new ScalarHandler(columnIndex), params); } } catch (SQLException e) { LOG.error("Error occured while attempting to query data", e); } finally { if (conn != null) { DbUtils.closeQuietly(conn); } } return object; } /** * ? * * @param sql sql? * @return */ public int getCount(String sql) { return getCount(sql, null); } /** * ? * * @param sql sql? * @param param ? * @return */ public int getCount(String sql, Object param) { return getCount(sql, new Object[] { param }); } /** * ? * * @param sql sql? * @param params ? * @return */ public int getCount(String sql, Object[] params) { 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 beginPage, int pageSize, int count) { if (count == 0) { return 1; } int newCurrentPage = beginPage; if (beginPage > 1) { if ((beginPage - 1) * pageSize >= count) { newCurrentPage = (int) (Math.ceil((count * 1.0) / pageSize)); } } return newCurrentPage; } }