java开发的第二十四个博客(连接池和元数据)

1.连接池

1.自定义连接池类
通过自定义连接池书写伪代码了解连接池底层原理

public class MyDataSource implements DataSource{
    private LinkedList<Connection> pool = new LinkedList<>();
    public MyDataSource(){
        for(int i=1;i<=10;i++){
            //获得Connecion连接对象
            Connection connection = JDBCUtils.getConnection();
            //传递Connection对象进行包装并传入连接集合使其可以重新放入集合中
            MyConnection myConnection = new MyConnection(connection,pool);
            //将包装后的连接对象放入连接集合中
            pool.addFirst(myConnection);
        }
    }
    public Connection getConnection(){
        //从连接集合中取出一个包装连接对象并返回
        Connection connection = pool.removeFirst();
        return connection;
    }
    ....
}

自定义连接对象类

public class MyConnection implements Connection{
    private Connection connection;
    private LinkedList<Connection> pool;
    public MyConnection (Connection connection,LinkedList<Connection> pool){
        //接收连接对象和存储连接集合
        this.connection = connection;
        this.pool = pool;
    }
    public void close(){
        //自定义连接类重写close方法不释放对象而是将其放入连接集合底部
        pool.addLast(this);
    }
    //其他方法继承Connection的方法
    public Statement createStatement(){
        return connection.createStatement();
    }
    public PreparedStatement preparedStatement(String sql){
        return connection.preparedStatement(sql);
    }
    ....
}

2.使用C3P0连接池工具类

注意先导入C3P0的jar包和其配置文件(c3p0-config.xml)

  public class Demo{
        public static void main(String[] args){
            ComboPooledDataSource pool = new ComboPooledDataSource();
            //无参构造默认读取default-config的配置文件信息
            Connetion connection = pool.getConnection();
            //有参构造则读取配置文件指定参数列表的信息
            //Connetion connection = pool.getConnection("itheimac3p0");
            PreparedStatement preparedStatement = connection.prepareStatement("select * from demo where id=?");
            preparedStatement.setInt(1,2);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resuleSet.next()){
                System.out.println(result.getInt("id"));
            }
            resultSet.close();//释放结果集对象
            preparedStatement.close();//释放SQL执行对象
            connection.close();//因为其自定义了连接对象类重写了close方法,该连接对象会回到连接池中,而不会被释放
        }
    }

3.使用Druid(德鲁伊)连接池工具类

注入导入Druid的jar包和其配置文件(druid.properties)

 public class Demo{
        public static void main(String[] args){
            //定义了一个属性集
            Properties p = new Properties();
            //通过属性集对象加载(通过类加载的输入流读取的文件内容)
            p.load(Demo.class.getClassLoader().getResourceAsStream("druid.properties"));
            //通过德鲁伊的方法读取属性集对象内容并返回连接池工厂对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(p);
            //通过连接池工厂类对象获取连接对象
            Connetion connection = dataSource.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement("select * from demo where id=?");
            preparedStatement.setInt(1,2);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resuleSet.next()){
                System.out.println(result.getInt("id"));
            }
            resultSet.close();//释放结果集对象
            preparedStatement.close();//释放SQL执行对象
            connection.close();//因为其自定义了连接对象类重写了close方法,该连接对象会回到连接池中,而不会被释放
        }
    }

4.使用JdbcTemplate使用数据库增删改查

public class Test5 {
    public static void main(String[] args) throws Exception {
        InputStream is = Test5.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties p = new Properties();
        p.load(is);
        JdbcTemplate jt = new JdbcTemplate(DruidDataSourceFactory.createDataSource(p));

        int i = jt.update("insert into mo values(?,?,?)", 3, "王五", 2000);
        System.out.println(i);

        int i = jt.update("update mo set mname=? where id=?", "赵六", 3);
        System.out.println(i);

        int i = jt.update("delete from mo where id=?", 3);
        System.out.println(i);

        String s = jt.queryForObject("select mname from mo where id=?", String.class, 1);
        System.out.println(s);

        Map<String, Object> map = jt.queryForMap("select * from mo where id=?", 1);
        System.out.println(map);

        List<Map<String, Object>> list = jt.queryForList("select * from mo");
        System.out.println(list);

        User user = jt.queryForObject("select * from mo where id=?", new BeanPropertyRowMapper<>(User.class),1);
        System.out.println(user);

        List<User> use = jt.query("select * from mo", new BeanPropertyRowMapper<>(User.class));
        System.out.println(use);


    }
}

2.元数据

1.数据库元数据DatabaseMetaData

• getURL():返回一个代表数据库URL的字符串
• getUserName():返回当前数据库连接系统的用户名
• getDriverName():返回驱动程序名

  Connection connection = JDBCUtils.getConnection();
    //通过连接对象返回数据库元数据对象
    DatabaseMetaData metaData = connection.getMetaData();
    Sysytem.out.println(metaData.getURL());
    Sysytem.out.println(metaData.getUserName());
    Sysytem.out.println(metaData.getDriverName());
    connection.close();

2.参数元数据ParameterMetaData

• getParameterCount():获得参数元数据内的参数个数
• getParameterType(int param):获得指定索引处的参数类型(没用)

  Connection connection = JDBCUtils.getConnection();
    String sql = "select * from demo where dname=? and pwd=?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    //通过SQL语句执行对象返回参数元数据对象
    ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
    Sysytem.out.println(parameterMetaData.getParameterCount());
    Sysytem.out.println(parameterMetaData.getParameterType(1));
    Sysytem.out.println(parameterMetaData.getParameterType(2));
    connection.close();
    preparedStatement.close();

3.结果集元数据ResultSetMetaData

• getColumnCount():返回结果集对象列(字段)数(常用)
• getColumnName(int column):返回结果集对象指定列(字段)名
• getColumnTypeName(int column):返回结果集对象指定列(字段)类型

  Connection connection = JDBCUtils.getConnection();
    String sql = "select * from demo";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    ResultSet resultSet = preparedStatement.executeQuery();
    //通过结果集对象返回结果集元数据对象
    ResultSetMetaData metaData = resultSet.getMetaData();
    
    Sysytem.out.println(metaData.getColumnCount());
    Sysytem.out.println(metaData.getColumnName(1));
    Sysytem.out.println(metaData.getColumnTypeName(2));
    
    while(resultSet.next()){
        for(int i=1;i<=metaData.getColumnCount();i++){
            //利用个数实现自动获取每条记录内容
            System.out.println(resultSet.getObject(i));
        }
    }
    resultSet.close();
    preparedStatement.close();
    connection.close();