• Druid 连接池 JDBCUtils 工具类的使用


    Druid工具介绍

    它不仅仅是一个数据库连接池,它还包含一个ProxyDriver,一系列内置的JDBC组件库,一个SQL Parser。 支持所有JDBC兼容的数据库,包括Oracle、MySQL、Derby、Postgresql、SQL Server、H2等等。 Druid针对oracle和mysql做了特别优化,比如Oracle的PS Cache内存占用优化,MySql的ping检测优化。Druid提供了MySql、Oracle、Postgresql、SQL-92的SQL的完整支持,这是一个手写的高性能SQL Parser,支持Visitor模式,使得分析SQL的抽象语法树很方便。简单SQL语句用时10微秒以内,复杂SQL用时30微秒。 通过Druid提供的SQL Parser可以在JDBC层拦截SQL做相应处理,比如说分库分表、审计等。Druid防御SQL注入攻击的WallFilter就是通过Druid的SQL Parser分析语义实现的

    性能上图标分析

    这个图标是从别人的blog上面copy过了 ,这个是sql语句进行1000次以后的查询提醒druid 连接池的性能分析,大家可以做为参考,是否准确待定。。

    工具的使用

    这个是在代码中去注册一些配置信息,不常用的 大家随便看看就好

    package com.ruirui.druid;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    import org.junit.Test;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.ruirui.decoratedesgin.Utils;
    
    public class DruidDemo {
        @Test
        public void druidTest(){
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql:///stu");
            dataSource.setUsername("root");
            dataSource.setPassword("123456");
            try{
                // 获得连接:
                conn = dataSource.getConnection();
                // 编写SQL:
                String sql = "select * from student";
                pstmt = conn.prepareStatement(sql);
                // 执行sql:
                rs = pstmt.executeQuery();
                while(rs.next()){
                    System.out.println(rs.getInt("id")+"   "+rs.getString("name"));
                }
            }catch(Exception e){
                e.printStackTrace();
            }finally{
    
                Utils.releaseResouce(rs, ps
    tmt, conn);
            }
    
        }
    
    }

    注册信息在配置文件中

    @Test
        public void demo2(){
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
    
            try{
                Properties properties = new Properties();
                properties.load(new FileInputStream("src/druid.properties"));
                DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
                // 获得连接:
                conn = dataSource.getConnection();
                // 编写SQL:
                String sql = "select * from account";
                pstmt = conn.prepareStatement(sql);
                // 执行sql:
                rs = pstmt.executeQuery();
                while(rs.next()){
                    System.out.println(rs.getInt("id")+"   "+rs.getString("name")+"   "+rs.getDouble("money"));
                }
            }catch(Exception e){
                e.printStackTrace();
            }finally{
                JDBCUtils.release(rs, pstmt, conn);
            }
        }

    配置文件

    driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql:///day04
    username=root
    password=123
    
    这里配置信息有很多 最大连接数量 , 最小连接数量等。。。配置的信息很多啊 大家可以去读一下官方文档。。 跟C3P0使用的情况 基本上都差不多
    
    > 这里是下载地址druid 文档 jar包  http://pan.baidu.com/s/1jHYEPpk

    JDBCUtils工具类的使用

    package com.ruirui.jdbcutils;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.Arrays;
    import java.util.List;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.ArrayHandler;
    import org.apache.commons.dbutils.handlers.ArrayListHandler;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.apache.commons.dbutils.handlers.ColumnListHandler;
    import org.apache.commons.dbutils.handlers.KeyedHandler;
    import org.apache.commons.dbutils.handlers.MapHandler;
    import org.apache.commons.dbutils.handlers.MapListHandler;
    import org.apache.commons.dbutils.handlers.ScalarHandler;
    import org.junit.Test;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    public class JDBCUtils {
        /**
         *   QueryRunner:核心执行类
         *   ResultSetHandler:提供对查询结果封装
         *   DbUtils    :工具类
         */
    
        //c3p0 连接池
        public DataSource comboPooledDataSource = new ComboPooledDataSource();
    
        public Connection getConn() throws SQLException{
    
            return comboPooledDataSource.getConnection();
        }
    
        //插入数据
        public void saveData() throws SQLException {
    
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
    
            String sql = "insert into student values(null,'ruirui',?,?);";
    
            queryRunner.update(sql, "shenyang",27);
        }
    
        // 修改表
    
        public void fixData() throws SQLException{
    
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql = "update student set name=? where id=?;";
            queryRunner.update(sql,"pp", 13);
        }
    
    
        // 删除操作
        public void demo3() throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql ="delete from account where id =?";
            queryRunner.update(sql, 4);
        }
    
    
        // ArrayHandler:将查询到的一条记录封装到数组当中
        public void demo1() throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql  = "select * from account where id = ?";
            Object[] objs = queryRunner.query(sql, new ArrayHandler(), 1); // ”1“ 代表一条数据
            System.out.println(Arrays.toString(objs));
        }
    
        //ArrayListHandler 
        //一条查询是ArrayHandler 一个数组
        //多条查询 就是将多个数组 存入集合中
        public void demo2() throws SQLException {
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql  = "select * from student";
    
            List<Object[]> query = queryRunner.query(sql, new ArrayListHandler());
    
            for (Object[] objects : query) {
    
                for (Object object : objects) {
                    System.out.println(object.toString());
                }
            }
        }
    
        //查询一条记录 返回的是一个bean对象
        public void demo4() throws SQLException {
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql  = "select * from student where id = ?";
            Man man = queryRunner.query(sql, new BeanHandler<>(Man.class),5);
            System.out.println(man.toString());
        }
    
        //注意返回的是一个标准的javabean对象,所在定义bean对象时候成员变量必须用private定义
    
        //查询一条记录 返回的是一个bean对象
        public void demo5() throws SQLException {
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql  = "select * from student";
            List<Man> query = queryRunner.query(sql, new BeanListHandler<>(Man.class ));
            System.out.println(query.toString());
        }
    
        // MapHandler:封装一条记录到Map中
        public void demo6()throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql = "select * from student where id = ?";
            Map<String,Object> map = queryRunner.query(sql, new MapHandler() ,2);
            System.out.println(map);
        } 
    
        // MapListHandler: //查询多条 将map集合存入list 集合中
        public void demo7()throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql = "select * from student";
            List<Map<String,Object>> list = queryRunner.query(sql, new MapListHandler());
            for (Map<String, Object> map : list) {
                System.out.println(map);
            }
        }
    
        // ColumnListHandler ,返回的是一个列值的集合
        public void demo8()throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql = "select id from student";
            List<Object> list  = queryRunner.query(sql, new ColumnListHandler());
            for (Object object : list) {
                System.out.println(object.toString());
            }
        }
    
        //ScalarHandler:单值查询
        public void demo9()throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql = "select sum(age) from student;";
            Long count = (Long)queryRunner.query(sql, new ScalarHandler());
            System.out.println(count);
        }
        @Test
        // KeyedHandler:
        public void demo10()throws SQLException{
            QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
            String sql = "select * from student";
            Map<Object,Map<String,Object>> map= queryRunner.query(sql, new KeyedHandler("name"));
            for (Object key : map.keySet()) {
                System.out.println(key + "   "+map.get(key));
            }
        }
    
    }

    JDBUtils 文档以及jar 下载链接

    http://pan.baidu.com/s/1c13IXxu

  • 相关阅读:
    fiddler的使用
    redis pipeline
    redis hash map
    redis队列的实现
    PHP-redis中文文档-命令
    websocket
    c++之socket,阻塞模式
    Django上传文件和修改date格式
    通过字符串导入模块
    'CSRFCheck' object has no attribute 'process_request' 报错
  • 原文地址:https://www.cnblogs.com/wpcnblog/p/8668859.html
Copyright © 2020-2023  润新知