JDBC通用工具类
由于公司的框架体系太成熟，所以工作一段时间后导致最底层的东西几乎忘掉，今天趁闲暇时间又把jdbc基础捡了一下并写了一个较为通用的工具类，其中包括事务模板，简单查询，修改，有不足的地方忘大牛们多提建议，拍转，工具类主要包—aoSupport、DaoSupportTemplate、DaoSupportAction。
1、DaoSupport 工具类，封装了自己实现的连接池和jdbc简单的查询和修改。
/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
import com.jinfeng.practice.util.impl.DaoSupportTemplateImpl;
/**
* dao支持类
*
* @author feng.jin
* @version $Id: DaoSupport.java, v 0.1 2013-8-7 上午10:58:28 feng.jin Exp $
*/
public class DaoSupport {
??? /** 数据连接池 */
??? private static final List<Connection> connectionPool??? = new LinkedList<Connection>();
??? /** 驱动 */
??? private static String???????????????? driver;
??? /** url*/
??? private static String???????????????? url;
??? /** username */
??? private static String???????????????? username;
??? /** password */
??? private static String???????????????? password;
??? /** properties */
??? private static final Properties?????? properties??????? = new Properties();
??? /** 最大连接数 */
??? private static final int????????????? MAX?????????????? = 10;
??? /** 活跃连接数 */
??? private static int??????????????????? activeNum???????? = 10;
??? /** 已用连接数 */
??? private static int??????????????????? usedNum?????????? = 0;
??? /** 连接 */
??? private Connection??????????????????? conn????????????? = null;
??? /** 语句平台 */
??? private PreparedStatement???????????? preparedStatement = null;
??? /** 模板 */
??? private final DaoSupportTemplate????? daoSupportTemplate;
??? /**
???? * constructor
???? */
??? public DaoSupport() {
??????? daoSupportTemplate = new DaoSupportTemplateImpl();
??????? daoSupportTemplate.setDaoSupport(this);
??? }
??? static {
??????? try {
??????????? //1.【初始化数据连接需要的数据】
??????????? properties.load(DaoSupport.class.getResourceAsStream("/config/datasource.properties"));
??????????? driver = properties.getProperty("jdbc.driver");
??????????? url = properties.getProperty("jdbc.url");
??????????? username = properties.getProperty("jdbc.username");
??????????? password = properties.getProperty("jdbc.password");
??????????? Class.forName(driver);
??????????? //2.【初始化连接池】
??????????? for (int i = 0; i < 10; i++) {
??????????????? Connection conn = DriverManager.getConnection(url, username, password);
??????????????? connectionPool.add(conn);
??????????? }
??????? } catch (IOException e) {
??????????? System.out.println(e);
??????? } catch (ClassNotFoundException e) {
??????????? System.out.println(e);
??????? } catch (SQLException e) {
??????????? System.out.println(e);
??????? }
??? }
??? /**
???? * 获取连接
???? *
???? * @return
???? */
??? public synchronized Connection getConnection() {
??????? if (conn == null) {
??????????? if (connectionPool != null && connectionPool.size() >= 0) {
??????????????? conn = connectionPool.get(0);
??????????????? connectionPool.remove(0);
??????????????? activeNum--;
??????????????? usedNum++;
??????????? } else {
??????????????? throw new RuntimeException("当前使用的连接数已经超过" + MAX + ", 连接池没有足够的数据库连接!");
??????????? }
??????? } else {
??????????? return conn;
??????? }
??????? return conn;
??? }
??? /**
???? * 获取语句执行器
???? *
???? * @param conn
???? * @param sql
???? * @param params
???? * @return
???? */
??? public PreparedStatement getPreparedStatement(Connection conn, String sql, List<Object> params) {
??????? try {
??????????? if (preparedStatement == null) {
??????????????? preparedStatement = conn.prepareStatement(sql);
??????????? }
??????????? if (params != null) {
??????????????? for (int i = 0; i < params.size(); i++) {
??????????????????? Object param = params.get(i);
??????????????????? preparedStatement.setObject(i + 1, param);
??????????????? }
??????????? }
??????? } catch (SQLException e) {
??????????? System.out.println(e);
??????? }
??????? return preparedStatement;
??? }
??? public int excuteUpdate(String sql, List<Object> params) {
??????? conn = getConnection();
??????? int i = 0;
??????? PreparedStatement preparedStatement = getPreparedStatement(conn, sql, params);
??????? try {
??????????? i = preparedStatement.executeUpdate(sql);
??????? } catch (SQLException e) {
??????????? System.out.println(e);
??????? }
??????? return i;
??? }
??? /**
???? *? 释放连接
???? */
??? public synchronized void close() {
??????? if (conn != null) {
??????????? connectionPool.add(conn);
??????????? conn = null;
??????????? activeNum++;
??????????? usedNum--;
??????? }
??? }
??? /**
???? *? 释放指定连接
???? */
??? public synchronized void close(Connection conn) {
??????? if (conn != null) {
??????????? connectionPool.add(conn);
??????????? conn = null;
??????????? activeNum++;
??????????? usedNum--;
??????? }
??? }
??? /** *
???? * 执行查询
???? *
???? * @param sql
???? * @param params
???? * @return
???? */
??? public ResultSet excuteQuery(String sql, List<Object> params) {
??????? Connection conn = getConnection();
??????? PreparedStatement preparedStatement = getPreparedStatement(conn, sql, params);
??????? ResultSet resultSet = null;
??????? try {
??????????? resultSet = preparedStatement.executeQuery();
??????? } catch (SQLException e) {
??????????? System.out.println(e);
??????? }
??????? return resultSet;
??? }
??? /**
???? * Getter method for property <tt>daoSupportTemplate</tt>.
???? *
???? * @return property value of daoSupportTemplate
???? */
??? public DaoSupportTemplate getDaoSupportTemplate() {
??????? return daoSupportTemplate;
??? }
}
2、DaoSupportTemplate 模板，用于处理事务相关的操作，中间可用用户自行实现
/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util;
import java.sql.Connection;
/**
* 模板
*
* @author feng.jin
* @version $Id: DaoSupportTemplate.java, v 0.1 2013-8-7 下午03:51:59 feng.jin Exp $
*/
public interface DaoSupportTemplate {
??? /**
???? * 执行
???? *
???? * @param conn
???? * @param daoSupportCallBack
???? */
??? public void excute(Connection conn, DaoSupportCallBack daoSupportCallBack);
??? /**
???? * 获取
???? *
???? * @return
???? */
??? public DaoSupport getDaoSupport();
??? /**
???? * 设置
???? *
???? * @param daoSupport
???? */
??? public void setDaoSupport(DaoSupport daoSupport);
}
3、DaoSupportTemplateImpl 模板实现
/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util.impl;
import java.sql.Connection;
import com.jinfeng.practice.util.DaoSupport;
import com.jinfeng.practice.util.DaoSupportCallBack;
import com.jinfeng.practice.util.DaoSupportTemplate;
/**
* 模板
*
* @author feng.jin
* @version $Id: DaoSupportTemplateImpl.java, v 0.1 2013-8-7 下午03:54:00 feng.jin Exp $
*/
public class DaoSupportTemplateImpl implements DaoSupportTemplate {
??? private DaoSupport daoSupport;
??? /**
???? * @see com.jinfeng.practice.util.DaoSupportTemplate#excute(java.sql.Connection)
???? */
??? @Override
??? public void excute(Connection conn, DaoSupportCallBack daoSupportCallBack) {
??????? try {
??????????? conn.setAutoCommit(false);
??????????? daoSupportCallBack.process();
??????????? conn.commit();
??????? } catch (Exception e) {
??????????? System.out.println(e);
??????????? try {
??????????????? conn.rollback();
??????????? } catch (Exception e1) {
??????????????? System.out.println("回滚失败" + e);
??????????? }
??????? } finally {
??????????? daoSupport.close(conn);
??????? }
??? }
??? /**
???? * Getter method for property <tt>daoSupport</tt>.
???? *
???? * @return property value of daoSupport
???? */
??? public DaoSupport getDaoSupport() {
??????? return daoSupport;
??? }
??? /**
???? * Setter method for property <tt>daoSupport</tt>.
???? *
???? * @param daoSupport value to be assigned to property daoSupport
???? */
??? public void setDaoSupport(DaoSupport daoSupport) {
??????? this.daoSupport = daoSupport;
??? }
}
4、DaoSupportCallBack 回调
/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util;
/**
* 回调
*
* @author feng.jin
* @version $Id: DaoSupportCallBack.java, v 0.1 2013-8-7 下午03:59:18 feng.jin Exp $
*/
public interface DaoSupportCallBack {
??? public void process();
}
?