[大数据]jdbc_

1.jdbc原理示意图

2.JDBC快速入门

url中

package com.lwb.jdbc.myjdbc;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class jdbc01 {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        Driver driver = new Driver();
        //2.得到连接
        String url = "jdbc:mysql://localhost:3306/lwb_db02";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","lwb");
        Connection connect = driver.connect(url, properties);
        //3.执行sql
        String sql = "INSERT INTO actor VALUES (1,'刘德华','男','1964-06-23','1100')";
        Statement statement = connect.createStatement();
        int i = statement.executeUpdate(sql);//dml返回影响行数
        System.out.println(i>0?"成功":"失败");

        //4.关闭连接
        statement.close();
        connect.close();
    }
}

3.数据库连接的五种方式

package com.lwb.jdbc.myjdbc;


import com.mysql.jdbc.Driver;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class jdbcConn {
    public static void main(String[] args) throws SQLException, IllegalAccessException, InstantiationException, ClassNotFoundException, IOException {
        connect01();
        connect02();
        connect03();
        connect04();
        connect05();
    }
    //方式一
    public static void connect01() throws SQLException {
        Driver driver = new Driver();
        String url = "jdbc:mysql://localhost:3306/lwb_db02";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","lwb");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
        connect.close();
    }

    //方式二
    public static void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //利用反射加载Driver类
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        String url = "jdbc:mysql://localhost:3306/lwb_db02";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","lwb");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
        connect.close();
    }

    //方式三：利用DriverManager
    public static void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //使用反射创建driver对象
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();

        //创建url,user和password
        String url = "jdbc:mysql://localhost:3306/lwb_db02";
        String user = "root";
        String password = "lwb";

        //注册Driver驱动
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
        connection.close();
    }

    //方式4：使用Class.forname自动注册驱动
    public static void connect04() throws ClassNotFoundException, SQLException {
        //使用反射加载了Driver类
        Class.forName("com.mysql.jdbc.Driver");

        //创建url,user和password
        String url = "jdbc:mysql://localhost:3306/lwb_db02";
        String user = "root";
        String password = "lwb";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
        connection.close();
    }

    //方式5：在方式4的基础上增加配置文件，让mysql更加灵活
    public static void connect05() throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
        connection.close();
    }
}

4.ResultSet

查询下面表

package com.lwb.jdbc.myjdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class resultSet {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //加载驱动
        Class.forName(driver);
        //连接数据库
        Connection connection = DriverManager.getConnection(url, user, password);
        //执行sql
        Statement statement = connection.createStatement();
        //sql语句集
        String sql = "select * from actor";
        ResultSet resultSet = statement.executeQuery(sql);
        
        //遍历结果集
        while(resultSet.next()){
            int id = resultSet.getInt(1);//得到第一列
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id+","+name+","+sex+","+date);
        }
        
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

5.sql语句

5.1 sql注入

statement 会存在sql注入问题，开发过程中一般不会使用

5.2 preparestatement

```java
package com.lwb.jdbc.myjdbc;

import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class PrepareStatement {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入管理员的名字：");
        String admin_name = sc.nextLine();
        System.out.println("请输入管理员的密码：" );
        String admin_pwd = sc.nextLine();

        //获取配置文件信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));

        //获取相关值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //注册驱动
        Class.forName(driver);
        //得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //sql语句
        String sql ="select * from admin where name = ? and pwd = ?";
        //得到preparestatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给问号赋值
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);
        //执行查询语句,注意括号不能加sql
        ResultSet resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){
            System.out.println("登录成功！");
        }else{
            System.out.println("登录失败！");
        }
        //释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

}

5.3 预处理DML

package com.lwb.jdbc.myjdbc;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class PrepareStatementDML {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入管理员的名字：");
        String admin_name = sc.nextLine();
        System.out.println("请输入管理员的密码：" );
        String admin_pwd = sc.nextLine();

        //获取配置文件信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));

        //获取相关值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //注册驱动
        Class.forName(driver);
        //得到连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //sql语句
        //  1.添加语句
        //String sql ="insert into admin values(?,?)";
        //  2.修改记录
        //String sql = "update admin set pwd = ? where name = ?";
        //  3.删除记录
        String sql = "delete from admin where name = ?";


        //得到preparestatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给问号赋值
        preparedStatement.setString(1,admin_name);
        //preparedStatement.setString(2,admin_name);
        //执行语句,注意括号不能加sql
        int row = preparedStatement.executeUpdate();
        if(row>0){
            System.out.println("执行成功!");
        }
        //释放资源
        preparedStatement.close();
        connection.close();
    }

}

6.JDBC API小结

7.JDBCUtils封装

7.1 封装代码

public static void testSelect() {
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //编写sql语句
            String sql = "select * from actor";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(id+","+name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

7.2 封装测试

package com.lwb.jdbc.util;

import java.sql.*;

public class JDBCUtils_Use {
    public static void main(String[] args) {
        //testDML();
        testSelect(); 
    }

    public static void testDML(){
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //编写sql语句
            String sql = "update actor set name = ? where id = ?";
           preparedStatement = connection.prepareStatement(sql);
           preparedStatement.setString(1,"林伟波");
           preparedStatement.setInt(2,1);
           preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    public static void testSelect() {
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //编写sql语句
            String sql = "select * from actor";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(id+","+name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

}

8.事务

package com.lwb.jdbc.myjdbc;

import com.lwb.jdbc.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Transaction_ {

    public static void main(String[] args) {
       // notransaction();
        transaction();
    }

    public static void notransaction(){
        //没有事务模拟转账
        //1.得到连接
        Connection connection = null;
        //2.组织sql
        String sql1 = "update account set balance = balance - 100 where id = 1";
        String sql2 = "update account set balance = balance + 100 where id = 2";
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            //此时id为1的账户减少100元
            //由于下面该条语句捕捉到异常，id为2的账户增加钱的语句不执行
            int num = 1/0;
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    //使用事务
    public static void transaction(){
        //没有事务模拟转账
        //1.得到连接
        Connection connection = null;
        //2.组织sql
        String sql1 = "update account set balance = balance - 100 where id = 1";
        String sql2 = "update account set balance = balance + 100 where id = 2";
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtils.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            //没有自动提交，执行到异常时表中数据并无发生变化
            int num = 1/0;
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            //捕捉到异常时进行回滚，即撤销执行的sql
            //当不设置保存点时默认回滚到事务刚开始时
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

9.批处理

用有无批处理两种方法插入5000条语句

package com.lwb.jdbc.batch;

import com.lwb.jdbc.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Batch_ {

    public static void main(String[] args) throws SQLException {
       //nobatch();
       batch_();
    }

    //无批处理,无批处理的时间：63199ms
    public static void nobatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into test values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for(int i = 0;i<5000;i++){
            preparedStatement.setString(1,"111");
            preparedStatement.setString(2,"tom"+i);
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("无批处理的时间："+(end-start));

        JDBCUtils.close(null,preparedStatement,connection);
    }

    //批处理,批处理的时间：532ms
    public static void batch_() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into test values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for(int i = 0;i<5000;i++){
            preparedStatement.setString(1,"111");
            preparedStatement.setString(2,"tom"+i);
            //将sql语句加入到批处理包中
            preparedStatement.addBatch();
            if((i+1)%1000 == 0){//1000条sql执行一次
                //执行批处理包中语句
                preparedStatement.executeBatch();
                //清空批处理包中语句
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("批处理的时间："+(end-start));

        JDBCUtils.close(null,preparedStatement,connection);
    }
}

10.数据库连接池

10.1 传统连接的弊端

10.2 数据库连接池介绍

java程序创建一个数据库连接池，连接池里有若干连接，连接与数据库已经连通，当java程序需要使用时就从数据库连接池中取出一个连接。当连接全部被占用时，java程序想要取出连接就会进入等待队列，等待其他连接被放回数据库连接池时才会取出连接。取出和放回不会使连接与数据库的联系断开。


分类

10.3 C3P0两种连接方式

package com.lwb.jdbc.dataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class C3P0 {
    public static void main(String[] args) throws IOException, PropertyVetoException, SQLException {
//        testC3P0();
        testC3P02();
    }

    //方式一：相关参数，在程序中指定user，url，password
    public static void testC3P0() throws SQLException, PropertyVetoException, IOException {
        //1.创建数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //2.通过配置文件获取相关连接信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src//mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");

        //给数据源combopooledDataSource设置相关参数
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //设置初始化连接数(初始时连接池中的连接数)
        comboPooledDataSource.setInitialPoolSize(30);
        //设置最大连接数（连接池中连接最多可以添加到的个数）
        comboPooledDataSource.setMaxPoolSize(50);
        long start = System.currentTimeMillis();
        for(int i = 0;i<5000;i++) {
            Connection connection = comboPooledDataSource.getConnection();
            //System.out.println("连接成功!");
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("连接时间："+(end-start));
    }

    //第二种方式：使用配置文件模板来完成
    public static void testC3P02() throws SQLException {
        //1.创建数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
        System.out.println("开始执行。。");
        long start = System.currentTimeMillis();
        for(int i = 0;i<5000;i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(end-start));
    }

}

10.4 德鲁伊连接池使用

package com.lwb.jdbc.dataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;

public class Druid_ {
    //读取配置文件
    public static void main(String[] args) throws Exception {
        druid01();
    }

    //Druid连接数据库
    public static void druid01() throws Exception {
        //读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src//druid.properties"));

        //创建指定参数的数据库连接池，Druid连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();
        System.out.println("连接成功");
        connection.close();
    }
}

10.5 德鲁伊连接池工具类

工具类代码

package com.lwb.jdbc.dataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtilsByDruid {
    private static DataSource ds = null;
    //静态代码块初始化ds
    static{
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //得到连接
    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭连接
    public static void close(ResultSet resultSet,Statement statement,Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

工具类使用

package com.lwb.jdbc.dataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCUtilsDruid_use {
    public static void main(String[] args) {
        testDruidUtils();
    }
    public static void testDruidUtils() {
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = JDBCUtilsByDruid.getConnection();
            //编写sql语句
            String sql = "select * from actor";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(id+","+name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
    }

}

10.6 DBUtils

10.6.1 引出

10.6.2 自己编写的代码解决ResultSet问题

（只截取方法部分）

 //自己编写代码解决resultSet问题
    public static void resultSetToArrayList() {
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        ArrayList<Actor> list = new ArrayList<Actor>();
        try {
            //获取连接
            connection = JDBCUtilsByDruid.getConnection();
            //编写sql语句
            String sql = "select * from actor";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date birthday = resultSet.getDate("birthday");
                String phone = resultSet.getString("phone");

                //把得到的记录封装到Actor对象，放入到集合
                list.add(new Actor(id,name,sex,birthday,phone));
            }
            System.out.println(list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
    }

10.6.3 DBUtils基本介绍和使用

基本介绍
实际上就是用来执行sql语句的

增删查改

package com.lwb.jdbc.dataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class DBUtils_Use {
    public static void main(String[] args) throws SQLException {
//        testQueryMary();
//        testQuerySingle();
//        testScalar();
        testDML();
    }

    //使用apache-DBUtils 工具类+druid完成对表的crud操作
    //多行查询
    public static void testQueryMary() throws SQLException {
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.引入DBUtils相关的jar包，加入Project
        //3.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.执行相关方法返回结果集
        String sql = "select * from actor where id >= ?";
        //(1)query方法就是执行sql语句，得到resultSet封装到ArrayList集合中
        //(2)返回的是一个集合
        //(3)sql：被执行的sql语句
        //(4)new BeanListHandler(Actor.class)将resultSet集合中的结果封装为对象封装到集合中
        //      底层使用反射机制获取Actor的类属性进行封装
        //(5)1赋值给sql语句中？，可以有多个值，因为是可变参数
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        System.out.println("集合信息：");
        for(Actor actor : list){
            System.out.println(actor);
        }
        //释放资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //单行查询
    public static void testQuerySingle() throws SQLException {
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3.执行相关方法，返回单个对象
        String sql = "select * from actor where id = ?";
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
        System.out.println(actor);
        //释放资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //单行单列查询
    public static void testScalar() throws SQLException {
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3.执行相关方法，返回单个对象
        String sql = "select name from actor where id = ?";
        Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 1);
        System.out.println(obj);
        //释放资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //演示apache-dbutils+druid完成dml(update,insert,delete)
    public static void testDML() throws SQLException {
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3.组织sql，完成DML操作
//        String sql = "update actor set name = ? where id = ?";
//        String sql = "insert into actor values(null,?,?,?,?)";
        String sql = "delete from actor where id = ?";
        //返回受影响行数
            //改
//        int affectedRow = queryRunner.update(connection, sql, "张三丰", 1);
            //增
//        int affectedRow = queryRunner.update(connection, sql, "林青霞", "女","2001-03-05","112");
            //删
        int affectedRow = queryRunner.update(connection, sql, 3);
        System.out.println(affectedRow>0?"执行成功":"执行没有影响数据库");
        //释放资源
        JDBCUtilsByDruid.close(null,null,connection);
    }
}

10.7 BasicDAO

10.7.1 引出

10.7.2 应用实例

BasicDAO代码

package com.lwb.jdbc.dao_.dao;

import com.lwb.jdbc.dao_.utils.JDBCUtilsByDruid;
import com.lwb.jdbc.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

//其他DAO的父类
public class BasicDAO<T> {//泛型指定具体类型
    private QueryRunner queryRunner = new QueryRunner();

    //开发通用的dml方法真对任意对表
    public int update(String sql,Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int row = queryRunner.update(connection, sql, parameters);
            return row;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,null,connection);
        }

    }

    //开发通用的多行查询
    public List<T> queryMuti(String sql,Class<T> clazz,Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            List<T> list = queryRunner.query(connection, sql, new BeanListHandler<T>(clazz),parameters);
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,null,connection);
        }
    }

    //开发通用的单行查询
    public T querySingle(String sql,Class<T> clazz,Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection, sql, new BeanHandler<T>(clazz),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,null,connection);
        }
    }

    //开发通用的单行单列查询
    public Object queryScalar(String sql,Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection, sql, new ScalarHandler(),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,null,connection);
        }
    }
}

ActorDAO代码

package com.lwb.jdbc.dao_.dao;

import com.lwb.jdbc.dao_.domain.Actor;

public class ActorDAO extends BasicDAO<Actor>{
    //1.拥有BasicDAO所有的方法
    //2.根据业务需求，可以编写特有的方法
}

Actor代码

package com.lwb.jdbc.dao_.domain;

import java.util.Date;

public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date birthday;
    private String phone;

    public Actor() {
    }

    public Actor(Integer id, String name, String sex, Date birthday, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", phone='" + phone + '\'' +
                '}';
    }
}

JDBCUtilsByDruid代码

package com.lwb.jdbc.dao_.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtilsByDruid {
    private static DataSource ds = null;
    //静态代码块初始化ds
    static{
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //得到连接
    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭连接
    public static void close(ResultSet resultSet,Statement statement,Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

testDAO代码

package com.lwb.jdbc.dao_.test;

import com.lwb.jdbc.dao_.dao.ActorDAO;
import com.lwb.jdbc.dao_.domain.Actor;

import java.util.List;

public class TestDAO {
    public static void main(String[] args) {
        //演示多行查询
        ActorDAO actorDAO = new ActorDAO();
        List<Actor> actors = actorDAO.queryMuti("select * from actor where id >= ?", Actor.class, 1);
        System.out.println("===多行查询===");
        for (Actor actor : actors) {
            System.out.println(actor);
        }

        //演示单行查询
        Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 1);
        System.out.println("===单行查询===");
        System.out.println(actor);

        //演示单行单列查询
        Object obj = actorDAO.queryScalar("select name from actor where id = ?",1);
        System.out.println("===单行单列查询===");
        System.out.println(obj);

        //演示dml操作
        int update = actorDAO.update("insert into actor values(null,?,?,?,?)", "张无忌", "男", "1999-01-01", "115");
        System.out.println(update>=0?"执行成功":"执行失败");

    }
}