Java tutorial
package com.opensource.dbhelp; import java.sql.Connection; import java.sql.SQLException; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import com.opensource.dbhelp.dbutils.BasicRowProcessor; import com.opensource.dbhelp.dbutils.CamelBeanProcessor; import com.opensource.dbhelp.dbutils.DbUtils; import com.opensource.dbhelp.dbutils.QueryRunner; import com.opensource.dbhelp.dbutils.RowProcessor; import com.opensource.dbhelp.dbutils.handlers.ArrayHandler; import com.opensource.dbhelp.dbutils.handlers.ArrayListHandler; import com.opensource.dbhelp.dbutils.handlers.BeanHandler; import com.opensource.dbhelp.dbutils.handlers.BeanListHandler; import com.opensource.dbhelp.dbutils.handlers.MapHandler; import com.opensource.dbhelp.dbutils.handlers.MapListHandler; import com.opensource.dbhelp.dbutils.handlers.ScalarHandler; import com.opensource.dbhelp.dbutils.handlers.StringArrayHandler; import com.opensource.dbhelp.dbutils.handlers.StringArrayListHandler; import com.opensource.dbhelp.dbutils.handlers.StringMapHandler; import com.opensource.dbhelp.dbutils.handlers.StringMapListHandler; import com.opensource.dbhelp.page.ListPage; import com.opensource.dbhelp.page.ListPagedStatement; import com.opensource.dbhelp.page.Page; /** * ??? * <p/> * Copyright: Copyright (c) Feb 6, 2009 3:39:21 PM * <p/> * Company: GNU General Public License * <p/> * Author: GNU General Public License * <p/> * Version: 1.1 * <p/> */ public class DbHelper { /** * logger */ protected static final Log logger = LogFactory.getLog(DbHelper.class); /** * sql loggerSQL???? */ protected static final Log sqllogger = LogFactory.getLog("com.opensource.SQL"); /** * ?? */ protected DataSource dataSource; /** * ? */ protected String dialect; /** * Singleton processor instance that handlers share to save memory. */ public static final RowProcessor BEAN_ROW_PROCESSOR = new BasicRowProcessor(new CamelBeanProcessor()); /** * * * @param dataSource * ?? */ public DbHelper(DataSource dataSource) { setDataSource(dataSource); } /** * ??? * * @return ?? */ public DataSource getDataSource() { return dataSource; } /** * ?? * * @param dataSource * ?? */ public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } /** * ?? * * @return mysql or oracle */ public String getDialect() { return dialect; } /** * ? * * @param dialect * mysql or oracle */ public void setDialect(String dialect) { this.dialect = dialect; } /** * ?? * * @return DbTransaction */ public DbTransaction getDbTransaction() { return new DbTransaction(this.dataSource); } /** * ?? * * @return ? */ public Connection getConn() { try { return dataSource.getConnection(); } catch (SQLException e) { logger.error(e.getMessage(), e); return null; } } /** * ? * * @param conn * ?? */ public void closeConn(Connection conn) { DbUtils.closeQuietly(conn); } // /////////////////////////////////////////////// /** * ?SQL ? null * * @param sql * SQL? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public String queryScalar(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); Object obj = run.query(sql, new ScalarHandler<Object>(1), params); if (obj != null) { return obj.toString(); } return null; } /** * ? INSERT?UPDATE DELETE ? * * @param sql * ?sql? * @param params * ? * @return ? * @throws SQLException * - if there is any problem executing the sql */ public int[] batch(String sql, List<Object[]> params) throws SQLException { debug(sql, "batch sql, count:" + ((params == null) ? -1 : params.size())); QueryRunner run = new QueryRunner(dataSource); if (params == null) { throw new SQLException("Null parameters. If parameters aren't need, pass an empty array."); } Object[][] o = new Object[params.size()][]; for (int i = 0; i < params.size(); i++) { o[i] = params.get(i); } return run.batch(sql, o); } /** * ? SELECT ?Map<String, String>? ? null null * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public Map<String, String> getMap(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new StringMapHandler(), params); } /** * ? SELECT ?Map<String, Object>? ? null * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public Map<String, Object> getNativeMap(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new MapHandler(), params); } /** * ? SELECT ?? ? null null * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public String[] getArray(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new StringArrayHandler(), params); } /** * ? SELECT ?? ? null * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public Object[] getNativeArray(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new ArrayHandler(), params); } /** * SQL? ? null java?? update_time updateTime * * @param sql * ?sql? * @param type * * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public <T> T getBean(String sql, Class<T> type, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new BeanHandler<T>(type, BEAN_ROW_PROCESSOR), params); } /** * ? SELECT ?Map<String, String> null * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public List<Map<String, String>> getMapList(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new StringMapListHandler(), params); } /** * ? SELECT ?Map<String, Object> * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public List<Map<String, Object>> getNativeMapList(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new MapListHandler(), params); } /** * ? SELECT ? null * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public List<String[]> getArrayList(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new StringArrayListHandler(), params); } /** * ? SELECT ? * * @param sql * ?sql? * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public List<Object[]> getNativeArrayList(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new ArrayListHandler(), params); } /** * SQL java?? update_time updateTime * * @param sql * ?sql? * @param type * * @param params * ? * @return * @throws SQLException * - if there is any problem executing the sql */ public <T> List<T> getBeanList(String sql, Class<T> type, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.query(sql, new BeanListHandler<T>(type, BEAN_ROW_PROCESSOR), params); } /** * UPDATE DELETE ? * * @param sql * ?sql? * @param params * ? * @return ? * @throws SQLException * - if there is any problem executing the sql */ public int execute(String sql, Object... params) throws SQLException { debug(sql, params); QueryRunner run = new QueryRunner(dataSource); return run.update(sql, params); } // //////////////Page///////////////////// /** * ??sql??? * * @param sql * SQL * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<String[]> getArrayPage(String sql) throws SQLException { return getArrayPage(sql, 1, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??? * * @param sql * SQL * @param curPage * * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<String[]> getArrayPage(String sql, int curPage) throws SQLException { return getArrayPage(sql, curPage, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??? * * @param sql * SQL * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<String[]> getArrayPage(String sql, int curPage, int pageSize) throws SQLException { return getArrayPage(sql, curPage, pageSize, (Object[]) null); } /** * ??sql??? * * @param sql * SQL * @param params * ? * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<String[]> getArrayPage(String sql, int curPage, int pageSize, Object... params) throws SQLException { ListPagedStatement<String[]> pst = new ListPagedStatement<String[]>(dataSource, dialect, sql, curPage, pageSize, params); return pst.executeQuery(0); } /** * ??sql??Map?Map? * * @param sql * SQL * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, Object>> getNativeMapPage(String sql) throws SQLException { return getNativeMapPage(sql, 1, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??Map?Map? * * @param sql * SQL * @param curPage * * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, Object>> getNativeMapPage(String sql, int curPage) throws SQLException { return getNativeMapPage(sql, curPage, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??Map?Map? * * @param sql * SQL * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, Object>> getNativeMapPage(String sql, int curPage, int pageSize) throws SQLException { return getNativeMapPage(sql, curPage, pageSize, (Object[]) null); } /** * ??sql??Map?Map? * * @param sql * SQL * @param params * ? * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, Object>> getNativeMapPage(String sql, int curPage, int pageSize, Object... params) throws SQLException { ListPagedStatement<Map<String, Object>> pst = new ListPagedStatement<Map<String, Object>>(dataSource, dialect, sql, curPage, pageSize, params); return pst.executeQuery(2); } /** * ??sql??Bean? * * @param sql * SQL * @param clazz * ?bean * @return ?sql * @throws SQLException * if a database access error occurs */ public <T> ListPage<T> getPage(String sql, Class<T> clazz) throws SQLException { return getPage(sql, clazz, 1, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??Bean? * * @param sql * SQL * @param clazz * ?bean * @param curPage * * @return ?sql * @throws SQLException * if a database access error occurs */ public <T> ListPage<T> getPage(String sql, Class<T> clazz, int curPage) throws SQLException { return getPage(sql, clazz, curPage, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??Bean? * * @param sql * SQL * @param clazz * ?bean * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public <T> ListPage<T> getPage(String sql, Class<T> clazz, int curPage, int pageSize) throws SQLException { return getPage(sql, clazz, curPage, pageSize, (Object[]) null); } /** * ??sql??Bean? * * @param sql * SQL * @param params * ? * @param clazz * ?bean * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public <T> ListPage<T> getPage(String sql, Class<T> clazz, int curPage, int pageSize, Object... params) throws SQLException { ListPagedStatement<T> pst = new ListPagedStatement<T>(dataSource, dialect, sql, curPage, pageSize, params); pst.setClazz(clazz); return pst.executeQuery(-1); } /** * ??sql??Map?Map * * @param sql * SQL * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, String>> getMapPage(String sql) throws SQLException { return getMapPage(sql, 1, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??Map?Map * * @param sql * SQL * @param curPage * * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, String>> getMapPage(String sql, int curPage) throws SQLException { return getMapPage(sql, curPage, Page.DEFAULT_PAGESIZE, (Object[]) null); } /** * ??sql??Map?Map * * @param sql * SQL * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, String>> getMapPage(String sql, int curPage, int pageSize) throws SQLException { return getMapPage(sql, curPage, pageSize, (Object[]) null); } /** * ??sql??Map?Map * * @param sql * SQL * @param params * ? * @param curPage * * @param pageSize * ? * @return ?sql * @throws SQLException * if a database access error occurs */ public ListPage<Map<String, String>> getMapPage(String sql, int curPage, int pageSize, Object... params) throws SQLException { ListPagedStatement<Map<String, String>> pst = new ListPagedStatement<Map<String, String>>(dataSource, dialect, sql, curPage, pageSize, params); return pst.executeQuery(1); } /** * debug? * * @param sql * SQL? * @param params * ? */ protected void debug(String sql, Object... params) { if (sqllogger.isDebugEnabled()) { if (params == null) { params = new Object[] {}; } StringBuilder sb = new StringBuilder(400); sb.append(sql); if (params.length != 0) { sb.append(" [params:"); for (Object obj : params) { sb.append(obj == null ? "<null>" : obj.toString()).append(", "); } sb.setLength(sb.length() - 2); sb.append("]"); } sqllogger.debug(sb.toString()); } } }