java操作数据库的工具类

public abstract class AbstractDao {protected final Log log = LogFactory.getLog(this.getClass());protected DBHelper helper = new DBHelper();private Map<String, Object> params;private String sqlQry;protected long size;protected long start;/** * 取得资料的比數 *  * @param sqlQry *            sql语句 * @return 资料笔数 */public int executeCount(String sqlQry) {return executeCount(sqlQry, new ArrayList<Object>());}/** * 取得符合条件的笔数 *  * @param sqlQry *            sql语句(参数格式 <span style="color:red">:参数名</span>) * @param params *            参数Map<String key, Object value> * @return 资料笔数 */public int executeCount(String sqlQuery, Map<String, Object> params) {ResultSet rs = null;int count = 0;try {this.sqlQry = sqlQuery;this.params = params;this.size = -1;this.start = -1;setParams();rs = helper.createStatement().executeQuery(sqlQry);if (rs.next()) {count = rs.getInt(1);}} catch (SQLException e) {log.error(e.toString(), e);} finally {log.debug(sqlQry);if (rs != null) {try {rs.close();} catch (SQLException e) {log.error(e.toString(), e);}}close();}return count;}/** * 取得符合条件的笔数 *  * @param sqlQuery *            sql语句(参数用<span style="color:red">?</span>代替) * @param params *            参数List<Object> * @return 资料笔数 */public int executeCount(String sqlQuery, List<Object> params) {ResultSet rs = null;int count = 0;try {PreparedStatement pstmt = helper.createStatement(sqlQuery);setParams(pstmt, params);rs = pstmt.executeQuery();if (rs.next()) {count = rs.getInt(1);}} catch (SQLException e) {log.error(e.toString(), e);} finally {log.debug(sqlQry);if (rs != null) {try {rs.close();} catch (SQLException e) {log.error(e.toString(), e);}}close();}return count;}public int executeCount(String sqlQuery, Object[] params) {List<Object> list = new ArrayList<Object>();if (params != null) {for (Object o : params) {list.add(o);}}return executeCount(sqlQuery, list);}/** * 根据提供的SQL查询数据 *  * @param c *            数据类型 可以是Model.class 后 Object[].class * @param sqlQry *            查询SQL * @return 指定类型的List */public <T> List<T> executeQuery(Class<T> c, String sqlQry) {return executeQuery(c, sqlQry, new ArrayList<Object>());}/** *  * 根据提供的SQL查询数据 *  * @param c *            数据类型 可以是Model.class 后 Object[].class * @param sqlQuery *            查询SQL(参数格式 <span style="color:red">:参数名</span>) * @param params *            参数Map<String key, Object value> * @return 指定类型的List */@SuppressWarnings("unchecked")public <T> List<T> executeQuery(Class<T> c, String sqlQuery,Map<String, Object> params,Page page) {List<T> list = new ArrayList<T>();ResultSet rs = null;try {this.sqlQry = sqlQuery;this.params = params;if(page!=null){this.sqlQry+=" limit :size offset :start";this.start=page.getstartR(page.getCurpage(), page.getPagesize());this.size=page.getPagesize();}if (start >= 0 && size > 0) {setBigDecimal(":start", new BigDecimal(start));setBigDecimal(":size", new BigDecimal(size));}setParams();rs = helper.createStatement().executeQuery(sqlQry);if (c.toString().equals("class java.lang.Object")) {int cNum = rs.getMetaData().getColumnCount();while (rs.next()) {list.add((T) copyRsData(rs, cNum));}} else {try {// ResultSetDynaClass rsdc = new ResultSetDynaClass(rs);// Iterator<T> rows = rsdc.iterator();while (rs.next()) {T t = (T) c.newInstance();copyProperties(t, rs, log);// DynaBean row = (DynaBean) rows.next();// BeanUtils.copyProperties(t, row);list.add(t);}} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}} catch (SQLException e) {log.error(e.toString(), e);} finally {if (rs != null) {try {rs.close();} catch (SQLException e) {log.error(e.toString(), e);}}close();log.debug(sqlQry);}return list;}/** *  * 根据提供的SQL查询数据 *  * @param c *            数据类型 可以是Model.class 后 Object[].class * @param sqlQry *            查询SQL(参数用<span style="color:red">?</span>代替) * @param params *            参数List<Object> * @return 指定类型的List */@SuppressWarnings("unchecked")public <T> List<T> executeQuery(Class<T> c, String sqlQry,List<Object> params) {List<T> list = new ArrayList<T>();ResultSet rs = null;try {PreparedStatement pstmt = helper.createStatement(sqlQry);setParams(pstmt, params);rs = pstmt.executeQuery();if (c.toString().equals("class java.lang.Object")) {int cNum = rs.getMetaData().getColumnCount();while (rs.next()) {list.add((T) copyRsData(rs, cNum));}} else {try {// ResultSetDynaClass rsdc = new ResultSetDynaClass(rs);// Iterator<T> rows = rsdc.iterator();while (rs.next()) {T t = (T) c.newInstance();copyProperties(t, rs, log);// DynaBean row = (DynaBean) rows.next();// BeanUtils.copyProperties(t, row);list.add(t);}} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}} catch (SQLException e) {log.error(e.toString(), e);} finally {if (rs != null) {try {rs.close();} catch (SQLException e) {log.error(e.toString(), e);}}close();log.debug(sqlQry);}return list;}private static void copyProperties(Object dst, ResultSet row, Log log) {if (row == null || dst == null) {return;}try {// ResultSetMetaData rsmd = row.getMetaData();String field;Object value;Field[] fields = dst.getClass().getDeclaredFields();for (Field f : fields) {field = f.getName();f.setAccessible(true);try {value = row.getObject(field);f.set(dst, value);} catch (SQLException e) {log.debug("数据栏位与Model不一致."+ e);} catch (IllegalArgumentException e) {log.debug("数据栏位类型与Model不一致."+ e);} catch (IllegalAccessException e) {log.debug("Model属性无法访问."+e);}}//} catch (SQLException e) {//LALog4DB.data("数据栏位与Model不一致.", e, "copyProperties");//} catch (IllegalArgumentException e) {//LALog4DB.data("数据栏位类型与Model不一致.", e, "copyProperties");//} catch (IllegalAccessException e) {//LALog4DB.data("Model属性无法访问.", e, "copyProperties");} catch (Exception e) {LALog4DB.data("其他错误.", e, "copyProperties");}}public <T> List<T> executeQuery(Class<T> c, String sqlQry, Object[] params) {List<Object> list = new ArrayList<Object>();if (params != null) {for (Object o : params) {list.add(o);}}return executeQuery(c, sqlQry, list);}/** * 根据提供的SQL查询第一笔资料 *  * @param c *            数据类型 可以是Model.class 后 Object[].class * @param sqlQry *            查询SQL(参数格式 <span style="color:red">:参数名</span>) * @param params *            参数Map<String key, Object value> * @return 指定类型的对象 */public <T> T queryFirstOrDefault(Class<T> c, String sqlQry,Map<String, Object> params,Page page) {T t = null;sqlQry += " limit 1";List<T> list = executeQuery(c, sqlQry, params,page);if (list != null && list.size() > 0) {t = list.get(0);}return t;}/** * 根据提供的SQL查询第一笔资料 *  * @param c *            数据类型 可以是Model.class 后 Object[].class * @param sqlQry *            查询SQL(参数用<span style="color:red">?</span>代替) * @param params *            参数List<Object> <div style="color:red">可以是数组</div> * @return 指定类型的对象 */public <T> T queryFirstOrDefault(Class<T> c, String sqlQry,List<Object> params) {T t = null;sqlQry += " limit 1";List<T> list = executeQuery(c, sqlQry, params);if (list != null && list.size() > 0) {t = list.get(0);}return t;}/** * {@link com.lingan.common.AbstractDao#queryFirstOrDefault(Class, String, List) * 查看queryFirstOrDefault} */public <T> T queryFirstOrDefault(Class<T> c, String sqlQry, Object[] params) {List<Object> list = new ArrayList<Object>();if (params != null) {for (Object o : params) {list.add(o);}}return queryFirstOrDefault(c, sqlQry, list);}/** * {@link com.lingan.common.AbstractDao#executeUpdate(String,List) * 查看executeUpdate} */public int executeUpdate(String sqlQry) {return executeUpdate(sqlQry, new ArrayList<Object>());}/** * 执行update/insert/delete语句 *  * @param sqlQry *            执行的SQL(参数格式 <span style="color:red">:参数名</span>) * @param params *            参数Map<String key, Object value> * @return 执行成功笔数 */public int executeUpdate(String sqlQry, Map<String, Object> params) {int count = 0;try {this.sqlQry = sqlQry;this.params = params;this.size = -1;this.start = -1;setParams();count = helper.createStatement().executeUpdate(sqlQry);} catch (SQLException e) {log.error(e.toString(), e);} finally {log.debug(sqlQry);if (!helper.trans) {try {helper.commit();} catch (SQLException e) {log.error(e.getMessage(), e);}close();}}return count;}/** * 执行update/insert/delete语句 *  * @param sqlQry *            执行SQL(参数用<span style="color:red">?</span>代替) * @param params *            参数List<Object><br> *            <div style="color:red"> 可以是数组,也可以不传入<br> *            如果不传入代表SQL中没有参数 </div> * @return 执行成功笔数 */public int executeUpdate(String sqlQry, List<Object> params) {int count = 0;try {PreparedStatement pstmt = helper.createStatement(sqlQry);setParams(pstmt, params);count = pstmt.executeUpdate();} catch (SQLException e) {log.error(e.toString(), e);} finally {log.debug(sqlQry);if (!helper.trans) {try {helper.commit();} catch (SQLException e) {log.error(e.getMessage(), e);}close();}}return count;}/** * {@link com.lingan.common.AbstractDao#executeUpdate(String,List) * 查看executeUpdate} */public int executeUpdate(String sqlQry, Object[] params) {List<Object> list = new ArrayList<Object>();if (params != null) {for (Object o : params) {list.add(o);}}return executeUpdate(sqlQry, list);}private void setParams(PreparedStatement pstmt, List<Object> params)throws SQLException {if (params != null) {Object o;for (int i = 1; i <= params.size(); i++) {o = params.get(i - 1);if (o instanceof String) {pstmt.setString(i, (String) o);} else if (o instanceof Date) {Date d = (Date) o;String t = DateUtils.format(d, "HHmmss");if (t.matches("[1-9]")) {pstmt.setTimestamp(i,new java.sql.Timestamp(d.getTime()));} else {pstmt.setDate(i, new java.sql.Date(d.getTime()));}} else if (o instanceof BigDecimal) {pstmt.setBigDecimal(i, (BigDecimal) o);} else if (o instanceof Long) {pstmt.setLong(i, (Long) o);} else if (o instanceof Integer) {pstmt.setInt(i, (Integer) o);}}}}/** * 将rs中的资料copy到Object数组中 *  * @param rs *            查询结果集 * @return Object数组 * @throws SQLException *             无法取得结果集列数时, 抛出异常 */protected Object[] copyRsData(ResultSet rs) throws SQLException {return copyRsData(rs, -1);}/** * 将rs中的资料copy到Object数组中 *  * @param rs *            查询结果集 * @param cNum *            Object长度(如果cNum大于实际列数, 则以实际列数为准) * @return Object 数组 * @throws SQLException *             无法取得结果集列数时, 抛出异常 */protected Object[] copyRsData(ResultSet rs, int cNum) throws SQLException {Object[] objs = new Object[cNum];if (cNum > rs.getMetaData().getColumnCount()) {cNum = rs.getMetaData().getColumnCount();}for (int i = 0; i < cNum; i++) {try {objs[i] = rs.getObject(i + 1);} catch (SQLException e) {e.printStackTrace();}}return objs;}private void close() {helper.close();}private void setParams() throws SQLException {// 添加分頁if (start >= 0 && size > 0) {setBigDecimal(":start", new BigDecimal(start));setBigDecimal(":size", new BigDecimal(size));}// 設定參數if (params == null) {return;}Object value;for (String s : params.keySet()) {value = params.get(s);if (!s.startsWith(":")) {s = ":" + s;}setValue(s, value);}}private void setBigDecimal(String field, BigDecimal x) throws SQLException {setValue(field, x);}private void setValue(String field, Object value) throws SQLException {String regex = field + "[+,=,\\-,*,<,>,!,\\s,(,),]";String repStr = "";if (value == null) {repStr = "null";} else if (value instanceof String) {repStr = "'" + StringEscapeUtils.escapeSql((String) value) + "'";} else if (value instanceof Number) {repStr = value.toString();} else if (value instanceof Timestamp) {repStr = "'"+ new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S").format(DateUtils.toDate((Timestamp) value)) + "'";} else if (value instanceof Date) {repStr = "'" + new SimpleDateFormat("yyyy/MM/dd").format(value)+ "'";} else if (value instanceof List) {@SuppressWarnings("unchecked")List<Object> list = (List<Object>) value;for (Object o : list) {repStr += ",";if (o instanceof String) {repStr += "'" + StringEscapeUtils.escapeSql((String) o)+ "'";} else if (o instanceof Number) {repStr += o.toString();} else if (o instanceof Timestamp) {repStr += "'"+ new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S").format(DateUtils.toDate((Timestamp) o))+ "'";} else if (o instanceof Date) {repStr += "'"+ new SimpleDateFormat("yyyy/MM/dd").format(o)+ "'";}}if (repStr.length() > 0) {repStr = repStr.substring(1);} else {repStr = "''";}} else {repStr = "null";}Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(this.sqlQry + ")");boolean matchFound = matcher.find();StringBuffer strBuffer = new StringBuffer(this.sqlQry);int position = 0;while (matchFound) {for (int i = 0; i <= matcher.groupCount(); i++) {String groupStr = matcher.group(i);strBuffer.replace(position + matcher.start(), position+ matcher.start() + groupStr.length() - 1, repStr);position += repStr.length() - (groupStr.length() - 1);}if (matcher.end() + 1 > this.sqlQry.length())break;matchFound = matcher.find(matcher.end());}this.sqlQry = strBuffer.toString();}/** * 启用事务 */public void beginTrans() {helper.beginTrans();log.debug("事务已经启用, 请在交易完成时调用closeTrans");}/** * 结束事务并提交 */public void closeTrans() {helper.closeTrans();}}

声明此文章并非转载，是同事刘所写，我只是总结下，为以后的工作积累点经验。