数据库JDBC操作和数据库连接池

数据库操作

1. JDBC

1.1 根据driver、url、username、password信息建立连接然后进行数据库操作；

主要步骤：

//1.加载驱动
Class.forname();
//2. 获取链接
Connection connnection = DriverManager.getConnection(url,username,password);
//3. 获取Statement对象
Statement statement = connection.createStatement();
//4. 定义SQL
String sql = "select * from users";
//5. 执行
ResultSet rs  = statement.executeQuery(sql);
//6. 获取结果
String id,username,passowrd;
List<User> users = new ArrayList<User>();
while(rs.next())
{
    id = rs.getInt("id");
	username = rs.getString("username");
	password = rs.getString("password");
	//TODO:
	
	User user = new User();
	user.setID(id,username,password);
	users.add(user);
}
//7. 回收资源 rs,statement,connection，安装资源创建顺序的逆序；
try...catch... finally 
在finally内回收资源，防止前面抛异常，资源无法回收。
if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if(stmt!=null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if(conn!=null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }

1.2 待优化点

(1) JDBC参数可配

InputStream in = ResourceLoader.class.getClassLoader().getResourceAsStream();
ps = new Properties();
ps.load(in);
value = ps.getProperty(property);

(2) 结果集封装
定义对象类RowRapper

(3) Statement优化
a.statement重复编译SQL语句，当语句相同时，效率较低；
b.当用户传递参数时，如果添加一个语句如“or 1=1”，会将数据全部查询出来。存在安全性问题（SQL注入攻击）；
因此引入PreparedStatemet对象,对sql进行预编译；

           
String sql = "insert into users(id,name,email) values(null,?,?)";
preStmt = connection.prepareStatement(sql);
preStmt.setObject(1,"李三");
preStmt.setObject(2,"123456");
 
 //返回影响的条数
int i = preStmt.executeUpdate();

2. 数据库连接池

2.1 为什么需要连接池

每操作一次数据库，都需要创建一次链接和释放一次链接，比较浪费资源。（创建、断开connection会消耗一定的时间和IO资源。）

图来自于网络，侵删；

2.2 如何实现连接池

在初始化时，创建一定数量的数据库连接放在连接池。当连接不够时，会再重新创建连接池；

    DataSource 接口，提供了获取数据库链接的方法
    
    Connection getConnection() throws SQLException;
    Connection getConnection(String username, String password)
    throws SQLException;

2.3 常用的数据库连接池

2.3.1 DBCP

2.3.2 C3P0

实现DataSource接口
类的层次
public interface PooledDataSource extends DataSource {

public abstract class AbstractComboPooledDataSource extends AbstractPoolBackedDataSource implements PooledDataSource,

public final class ComboPooledDataSource extends AbstractComboPooledDataSource

获取链接
 ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass(driver);
        cpds.setJdbcUrl(url);
        cpds.setUser(usernmae);
        cpds.setPassword(password);

        cpds.getConnection();

进一步优化：读取配置文件获取链接；
（1）默认配置
（2）指定配置

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
<!--		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>-->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/c3p0_db?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8</property>
		<property name="user">root</property>
		<property name="password">000</property>
	</default-config>
	<named-config name="my_ds">
		<!--		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>-->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/c3p0_db?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8</property>
		<property name="user">root</property>
		<property name="password">000</property>
	</named-config>
</c3p0-config>

获取链接的方式：

ComboPooledDataSource ds = new ComboPooledDataSource();
Connnection connection = ds.getConnection(); //默认的配置
Connnection connection = ds.getConnection("my_ds"); //指定的配置

2.3 常用的DB工具：DBUtils

主要接口：

public interface ResultSetHandler<T> {
    T handle(ResultSet var1) throws SQLException;
}

public class QueryRunner extends AbstractQueryRunner {
    public QueryRunner() {
    }
    public QueryRunner(DataSource ds) {
        super(ds);
    }
    public <T> T query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
        return this.query(conn, false, sql, rsh, param);
    }
    public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
        Connection conn = this.prepareConnection();
        return this.query(conn, true, sql, rsh, param);
    }
    public int update(Connection conn, String sql) throws SQLException {
        return this.update(conn, false, sql, (Object[])null);
    }
      public int update(String sql) throws SQLException {
        Connection conn = this.prepareConnection();
        return this.update(conn, true, sql, (Object[])null);
    }

    public int update(String sql, Object param) throws SQLException {
        Connection conn = this.prepareConnection();
        return this.update(conn, true, sql, param);
    }
     public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
        return this.insert(this.prepareConnection(), true, sql, rsh, (Object[])null);
    }

    public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return this.insert(this.prepareConnection(), true, sql, rsh, params);
    }

    public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
        return this.insert(conn, false, sql, rsh, (Object[])null);
    }