Servlet的JDBCUtils模板

有druid：

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public final class JDBCUtils {
    //声明连接池
    private static DruidDataSource ds;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    static{
        //实例化配置对象
        Properties p = new Properties();
        try {
//            InputStream is=JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            p.load(JDBCUtils.class.getResourceAsStream("/db.properties"));
//            p.load(is);
            ds= (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static DataSource getDataSource(){
        return ds;
    }
    //获取连接对象
    public static Connection getConnection(){
        Connection connection = threadLocal.get();
        try {
            if(connection==null){
                connection = ds.getConnection();
                threadLocal.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放资源
    public static void close(Connection conn, Statement state, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (state!=null){
                    state.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    if(conn!=null){
                        conn.close();
                        threadLocal.remove();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    //开启事务
    public static void start_transAction() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        //设置自动提交为false
        connection.setAutoCommit(false);
    }
    //提交事务
    public static void commit() throws SQLException {
        JDBCUtils.getConnection().commit();
    }
    //回滚事务
    public static void roolback() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
//        System.out.println("正在事务回滚...");
        connection.rollback();
    }
}

db.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myblog
username=root
password=123456
initialSize=10
maxActive=50
minIdle=5
maxWait=5000
#关闭空闲连接超时时间
remove-abandoned-timeout=1800
# 配置获取连接等待超时的时间
max-wait=60000
##配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
time-between-eviction-runs-millis=60000

---

无druid：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 跨平台方案
 * 
 * ThreadLocal set(objcet) get() remove()
 */
public final class JdbcUtil2 {

	private static String driver = null;
	private static String url = null;
	private static String user = null;
	private static String password = null;
	//
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

	private JdbcUtil2() {

	}

	static {
		try {
			// 读取配置文件
			Properties p = new Properties();
			// 加载文件
			p.load(new FileInputStream(new File("src/db.properties")));
			// 赋值
			driver = p.getProperty("jdbc_driver");
			url = p.getProperty("jdbc_url");
			user = p.getProperty("jdbc_user");
			password = p.getProperty("jdbc_password");

			// 注册驱动 (只做一次)
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 创建连接对象
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		// 先判断tl 是否有Connection
		Connection conn = null;
		if (tl.get() == null) {
			conn = DriverManager.getConnection(url, user, password);
			tl.set(conn);
		}
		return tl.get();
	}

	/**
	 * 开启事务
	 * 
	 * @throws SQLException
	 */
	public static void begin() throws SQLException {
		Connection conn = JdbcUtil2.getConnection();
		conn.setAutoCommit(false);//
	}

	public static void commit() throws SQLException {
		Connection conn = JdbcUtil2.getConnection();
		conn.commit();//
	}

	public static void rollBack() throws SQLException {
		Connection conn = JdbcUtil2.getConnection();
		conn.rollback();//
	}

	/**
	 * 关闭数据库资源
	 * 
	 * @param conn
	 * @param state
	 * @param rs
	 */
	public static void close(Connection conn, Statement state, ResultSet rs) {
		// 释放资源 先创建的后回收
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (state != null) {
					state.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (conn != null) {
						conn.close();
						tl.remove();// 将 conn 移除。
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
name=root
password=123456