[大数据]jdbc基础

一、获取连接

import org.junit.Test;
import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class ConnectionTest {
    /**
     * 方式一
     */
    @Test
    public void testConnect1() throws Exception {
        //加载驱动
        Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
        //提供连接数据
        String url = "jdbc:mysql://localhost:3306/school";
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","123456");
        //获取连接
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }
    /**
     * 方式二：使用DriverManger
     */
    @Test
    public void testConnect2() throws Exception {
        //获取Driver对象
        Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
        //加载驱动
        DriverManager.registerDriver(driver);
        //获取连接
        Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");
        System.out.println(connect);
    }
    /**
     * 方式三：使用DriverManger
     */
    @Test
    public void testConnect3() throws Exception {
        //获取Driver对象并加载驱动
        Class.forName("com.mysql.jdbc.Driver");//Driver类的静态代码块中注册加载驱动
//        public class Driver extends NonRegisteringDriver implements java.sql.Driver {
//            public Driver() throws SQLException {
//            }
//            static {
//                try {
//                    DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//                } catch (SQLException var1) {
//                    throw new RuntimeException("Can't register driver!");
//                }
//            }
//        }
        //获取连接
        Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");
        System.out.println(connect);
    }
    /**
     * 方式四：最终版
     */
    @Test
    public void testConnect4() throws Exception {
        //读取配置文件
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        //获取Driver对象并加载驱动
        Class.forName(driver);
        //获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }
}

二、批量插入

url=jdbc:mysql://localhost:3306/school?useSSL=false&rewriteBatchedStatements=true
user=root
password=123456
driver=com.mysql.jdbc.Driver

public class JDBCUtil {
    //获取连接
    public static Connection getConnection() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pt = new Properties();
        pt.load(is);
        String driver = pt.getProperty("driver");
        String url = pt.getProperty("url");
        String user = pt.getProperty("user");
        String password = pt.getProperty("password");
        Class.forName(driver);
        return DriverManager.getConnection(url,user,password);
    }
    //关闭连接
    public static void closeResource(Connection conn, PreparedStatement ps){
        try {
            if(conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public class BatchTest {
    public static void main(String[] args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取连接
            conn = JDBCUtil.getConnection();
            //测试数据删数据
            String deleteSql = "truncate table my_test";
            PreparedStatement deletePs = conn.prepareStatement(deleteSql);
            deletePs.execute();
            //设置不非自动提交
            long start = System.currentTimeMillis();
            conn.setAutoCommit(false);
            String sql = "insert into my_test (id,name) values(?,?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 10000000; i++) {
                ps.setInt(1,i);
                ps.setString(2,"name_"+i);
                ps.addBatch();
                if(i % 1000 == 0){
                    ps.executeBatch();
                    ps.clearBatch();
//                    conn.commit();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("耗时："+(end-start)/1000+"秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(conn,ps);
        }
    }
}

• rewriteBatchedStatements=true 启动批量插入，否则executeBatch也是一个一个的插入。
• 批量插入自动提交效率比手动最后一把提交效率低。

三、数据库连接池

• JDBC的数据库连接池使用 javax.sql.DataSource 来表示，DataSource只是接口，需要具体的实现类。
• DBCP：tomcat自带，速度比c3p0较快，但自身存在bug。
• C3P0：速度较慢，稳定性可以。
• Proxool：有监控连接池状态功能，稳定性较差。
• Druid（德鲁伊）：阿里巴巴提供，集以上优点与一身。

德鲁伊连接池

public class DruidTest {
	//传统方式
    @Test
    public void test1() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/school");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        //设置最大活跃数等等
        dataSource.setMaxActive(10);
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
    }
    //使用配置文件-推荐
    @Test
    public void test2() throws Exception {
        Properties properties = new Properties();
        //方式一：
//        FileInputStream is = new FileInputStream(new File("druid.properties"));
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        properties.load(is);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
    }
}

四、Apache-DBUtils实现CRUD操作

开源工具类，对jdbc简单封装

• 增删改通用操作

    @Test
    public void test1() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "insert into my_test (id,name) values(?,?)";
        int count = runner.update(conn, sql, 1, "张三");
        conn.close();
        System.out.println("影响条数："+count);
    }

• 查询一个对象

    @Test
    public void test2() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "select id,name from my_test where id = ?";
        BeanHandler<MyTest> rsh = new BeanHandler<>(MyTest.class);
        MyTest myTest = runner.query(conn, sql, rsh, 1);
        conn.close();
        System.out.println(myTest.toString());
    }

• 查询多个对象

    @Test
    public void test3() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "select id,name from my_test where id > ?";
        BeanListHandler<MyTest> rsh = new BeanListHandler<>(MyTest.class);
        List<MyTest> myTestList = runner.query(conn, sql, rsh, 0);
        conn.close();
        myTestList.forEach(System.out::println);
    }

• 查询对象以map返回

    @Test
    public void test4() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "select id,name from my_test where id > ?";
        MapListHandler rsh = new MapListHandler();
        List<Map<String, Object>> mapList = runner.query(conn, sql, rsh, 0);
        conn.close();
        System.out.println(mapList);
    }

• 查询特殊值

    @Test
    public void test5() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "select count(*) from my_test";
        ScalarHandler<Long> rsh = new ScalarHandler<>();
        Long count = runner.query(conn, sql, rsh);
        conn.close();
        System.out.println(count);
    }

• 批量插入

    @Test
    public void test7() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "insert into my_test (id,name) values(?,?)";
        Object[][] params = new Object[3][2];
        params[0][0] = 5;
        params[0][1] = "赵5";
        params[1][0] = 6;
        params[1][1] = "赵6";
        params[2][0] = 7;
        params[2][1] = "赵7";
        MapListHandler rsh = new MapListHandler();
        List<Map<String, Object>> maps = runner.insertBatch(conn, sql, rsh, params);
        conn.close();
        System.out.println(maps);
    }

• 查询自定义

    @Test
    public void test6() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        QueryRunner runner = new QueryRunner();
        String sql = "select count(*) from my_test where id = ?";
        ResultSetHandler<MyTest> rsh = new ResultSetHandler(){
            @Override
            public MyTest handle(ResultSet rs) throws SQLException {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                return new MyTest(id,name);
            }
        };
        MyTest test = runner.query(conn, sql, rsh, 1);
        conn.close();
        System.out.println(test);
    }