时间:2021-07-01 10:21:17 帮助过:11人阅读
数据库的连接对象创建工作,比较消耗性能。
一开始在内存中开辟一块空间(集合),一开始先往池子里面放置多个连接对象。后面需要连接的话,直接从池子里面去。不要去自己创建连接了。使用完毕,要记得归还连接。
- <code>package com.itheima.jdbc.util;
- import java.io.PrintWriter;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.sql.SQLFeatureNotSupportedException;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.logging.Logger;
- import javax.sql.DataSource;
- public class MyDataSource implements DataSource{
- List<Connection> list = new ArrayList<Connection>();
- public MyDataSource() {
- System.err.println("对象创建成功了...");
- for(int i=0;i<10;i++) {
- Connection conn = JDBCUtil.getConn();
- list.add(conn);
- }
- }
- @Override
- public Connection getConnection() throws SQLException {
- //来拿如果连接池是空的
- if(list.size()==0) {
- for(int i=0;i<10;i++) {
- Connection conn = JDBCUtil.getConn();
- list.add(conn);
- }
- }
- //拿走了,就获取第一个,并移除第一个。
- Connection con = list.remove(0);
- System.err.println("拿到了...");
- return con;
- }
- public void addBack(Connection con) {
- System.err.println("归还了...");
- list.add(con);
- }
- /*------------------下面的没用到------------------*/
- @Override
- public PrintWriter getLogWriter() throws SQLException {
- // TODO Auto-generated method stub
- return null;
- }
- @Override
- public void setLogWriter(PrintWriter out) throws SQLException {
- // TODO Auto-generated method stub
- }
- @Override
- public void setLoginTimeout(int seconds) throws SQLException {
- // TODO Auto-generated method stub
- }
- @Override
- public int getLoginTimeout() throws SQLException {
- // TODO Auto-generated method stub
- return 0;
- }
- @Override
- public Logger getParentLogger() throws SQLFeatureNotSupportedException {
- // TODO Auto-generated method stub
- return null;
- }
- @Override
- public <T> T unwrap(Class<T> iface) throws SQLException {
- // TODO Auto-generated method stub
- return null;
- }
- @Override
- public boolean isWrapperFor(Class<?> iface) throws SQLException {
- // TODO Auto-generated method stub
- return false;
- }
- @Override
- public Connection getConnection(String username, String password) throws SQLException {
- // TODO Auto-generated method stub
- return null;
- }
- }</code>
- <code>package com.itheima.jdbc.util;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- public class dataBases {
- public static void main(String[] args) {
- // TODO Auto-generated method stub
- Connection conn = null;
- PreparedStatement ps = null;
- MyDataSource mydatasource = new MyDataSource();
- try {
- conn = mydatasource.getConnection();
- String sql = "insert into bank values(null,'wangwu',1000)";
- ps = (PreparedStatement) conn.prepareStatement(sql);
- ps.executeUpdate();
- } catch (SQLException e) {
- try {
- ps.close();
- } catch (SQLException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- // TODO Auto-generated catch block
- e.printStackTrace();
- }finally {
- mydatasource.addBack(conn);
- }
- }
- }</code>
database connection pool
- <code>package com.java1995;
- import com.itheima.jdbc.util.JDBCUtil;
- import org.apache.commons.dbcp.BasicDataSource;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- public class DBCPDemo {
- public static void main(String[] args){
- Connection conn = null;
- PreparedStatement ps = null;
- try {
- /*1、构建数据源对象*/
- BasicDataSource dataSource = new BasicDataSource();
- //配置数据库信息
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://localhost/soft03");
- dataSource.setUsername("root");
- dataSource.setPassword("w19980315");
- /*2、得到连接对象*/
- conn = dataSource.getConnection();
- String sql = "insert into bank values(null,?,?)";
- ps = conn.prepareStatement(sql);
- ps.setString(1, "zhaoliu");
- ps.setInt(2, 1000);
- ps.executeUpdate();
- } catch (SQLException e) {
- e.printStackTrace();
- }finally {
- JDBCUtil.release(conn, ps);
- }
- }
- }</code>
还是导入那两个包,另外在配置文件里写上数据库信息:
- <code>package com.java1995;
- import com.itheima.jdbc.util.JDBCUtil;
- import org.apache.commons.dbcp.BasicDataSourceFactory;
- import javax.sql.DataSource;
- import java.io.FileInputStream;
- import java.io.InputStream;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.util.Properties;
- public class DBCPDemo02 {
- public static void main(String [] args) {
- Connection conn = null;
- PreparedStatement ps = null;
- BasicDataSourceFactory factory = new BasicDataSourceFactory();
- Properties properties = new Properties();
- try {
- InputStream is = new FileInputStream("src/dbcpconfig.properties");
- properties.load(is);
- DataSource dataSource = factory.createDataSource(properties);
- conn = dataSource.getConnection();
- String sql = "insert into bank values(null,?,?)";
- ps = conn.prepareStatement(sql);
- ps.setString(1, "aobama");
- ps.setInt(2, 400);
- ps.executeUpdate();
- } catch (Exception e) {
- e.printStackTrace();
- }finally {
- JDBCUtil.release(conn, ps);
- }
- }
- }</code>
- <code>package com.java1995;
- import com.itheima.jdbc.util.JDBCUtil;
- import com.mchange.v2.c3p0.ComboPooledDataSource;
- import java.beans.PropertyVetoException;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- public class C3P0Demo {
- public static void main(String[] args){
- Connection con = null;
- PreparedStatement ps = null;
- try {
- /*1、创建datasource*/
- ComboPooledDataSource dataSource = new ComboPooledDataSource();
- /*2、设置数据库连接信息*/
- dataSource.setDriverClass("com.mysql.jdbc.Driver");
- dataSource.setJdbcUrl("jdbc:mysql://localhost/soft03");
- dataSource.setUser("root");
- dataSource.setPassword("w19980315");
- /*3、得到连接对象*/
- con = dataSource.getConnection();
- String sql = "insert into bank values(null , ? ,?)";
- ps = con.prepareStatement(sql);
- ps.setString(1, "luhan");
- ps.setInt(2, 100);
- ps.executeUpdate();
- } catch (PropertyVetoException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- }finally {
- JDBCUtil.release(con, ps);
- }
- }
- }</code>
- <code>package com.java1995;
- import com.itheima.jdbc.util.JDBCUtil;
- import com.mchange.v2.c3p0.ComboPooledDataSource;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- public class C3P0Demo {
- public static void main(String[] args){
- Connection con = null;
- PreparedStatement ps = null;
- try {
- ComboPooledDataSource dataSource = new ComboPooledDataSource();
- con = dataSource.getConnection();
- String sql = "insert into bank values(null , ? ,?)";
- ps = con.prepareStatement(sql);
- ps.setString(1, "wangbo");
- ps.setInt(2, 1001111);
- ps.executeUpdate();
- } catch (SQLException e) {
- e.printStackTrace();
- }finally {
- JDBCUtil.release(con, ps);
- }
- }
- }</code>
- <code> //dbutils 只是帮我们简化了CRUD 的代码, 但是连接的创建以及获取工作。 不在他的考虑范围
- QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
- //增加
- //queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);
- //删除
- //queryRunner.update("delete from account where id = ?", 5);
- //更新
- //queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);</code>
直接new接口的匿名实现类
- <code> QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
- Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){
- @Override
- public Account handle(ResultSet rs) throws SQLException {
- Account account = new Account();
- while(rs.next()){
- String name = rs.getString("name");
- int money = rs.getInt("money");
- account.setName(name);
- account.setMoney(money);
- }
- return account;
- }
- }, 6);
- System.out.println(account.toString());</code>
直接使用框架已经写好的实现类
QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource()); //查询单个对象 Account account = queryRunner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class), 8);
查询多个对象
QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource()); List<Account> list = queryRunner.query("select * from account ", new BeanListHandler<Account>(Account.class));
以下两个是使用频率最高的
BeanHandler, 查询到的单个数据封装成一个对象
BeanListHandler, 查询到的多个数据封装 成一个List<对象>
- <code>ArrayHandler, 查询到的单个数据封装成一个数组
- ArrayListHandler, 查询到的多个数据封装成一个集合 ,集合里面的元素是数组。
- MapHandler, 查询到的单个数据封装成一个map
- MapListHandler,查询到的多个数据封装成一个集合 ,集合里面的元素是map。</code>
数据库连接池
标签:rest connect money actor nts rac where except tps