JDBC

编写步骤 前提：导入jar包 mysql-connector-java 方式一：

1. 注冊驱动 创建Driver对象
2. 得到连接 创建url对象 创建Properties对象设置user和password属性 通过driver对象获得connection对象
3. 执行sql 创建sql对象 通过connection对象获得statement对象 执行executeUpdate判断是否影响了数据
4. 关闭连接资源

提示： Driver driver = new Driver() Connection connect = driver.connect(url, properties); 等于 Class<?> aClass1 = Class.forName(driver); Driver driver = (Driver) aClass.newInstance(); DriverManager.registerDriver(driver); Connection connection = DriverManager.getConnection(url,user,password) 等于(推荐) Class.forName(driver); Connection connection = DriverManager.getConnection(url,user,password);

public class Jdbc01 {
    public static void main(String[] args) throws SQLException {
        //1. 注册驱动
        Driver driver = new Driver();
        //2. 从驱动得到连接(url，properties)
        //jdbc：mysql:// 规定好的协议 3306表示监听端口号
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url, properties);
        //3. 执行sql  
        String sql = "insert into actor values(null,'流','男','1970-11-11','110')";
        //创建一个statement
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);//如果是dml语句，返回的就是影响行数
        System.out.println(rows>0?"成功":"失败");

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

方式2 动态加载 使用DriverManager类中的静态方法getConnection加载 步骤：

1. 创建连接
   Class.forName(driver);
2. 得到连接 Connection connection = DriverManager.getConnection(url, user, password);
3. 关闭资源

代码：

user=root
password=123456
url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8"
driver=com.mysql.jdbc.Driver

 @Test
    public void connect02() throws Exception{
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\com\\kk\\jdbc\\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);
        //通过DriverManager获得connection
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8",user,password);
        //通过connection创建statement
        Statement statement = connection.createStatement();
        //执行sql
        String sql = "insert into actor values(null,'流','男','1970-11-11','110')";
        int rows = statement.executeUpdate(sql);
        System.out.println(rows > 0 ? "success":"failure");
        
        statement.close();
        connection.close();

    }

执行查询statement.executeQuery(sql)时，采用ResultSet接收结果集

@Test
    public void ResultSet_() throws Exception{

        //获取数据
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\com\\kk\\jdbc\\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);

        Statement statement = connection.createStatement();
        String sql = "select * from actor";
        ResultSet resultSet = statement.executeQuery(sql);//保存的结果集
        while(resultSet.next()){//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();
    }

Statement Statement对象 用于执行SQL语句 建立连接可以通过：

• Statement (存在SQL注入问题 用'or'可以卡bug)
• PreparedStatement（预处理）
• CallableStatement(存储过程)

使用PreparedStatement 在Statement的基础上设置了‘？’参数后续设置 避免了sql注入问题

 @Test
    public void ResultSet_() throws Exception{

        //获取数据
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\com\\kk\\jdbc\\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);

        String sql = "select * from actor where name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"流");
        //此时这里不用写入sql 在上面已经设置过值了
        ResultSet resultSet = preparedStatement.executeQuery();//保存的结果集
        while(resultSet.next()){//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();
        preparedStatement.close();
        connection.close();
    }

JDBCUtils 正常步骤：

1. 连接
2. sql执行
3. 关闭连接

utils可以提出1和3 代码：

public class JDBCUtils {
    //
    private static String user;
    private static String password;
    private static String driver;
    private static String url;
    //获取数据
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\com\\kk\\jdbc\\mysql.properties"));

            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //得到连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    //关闭连接
   public static void close(ResultSet set, Statement statement,Connection connection) throws SQLException {
        if (set != null){
            set.close();
        } if (statement != null){
           statement.close();
       } if (connection != null){
           connection.close();
       }
   }
}

public class useUtils {
    public static void main(String[] args) throws SQLException {
        //得到连接
        Connection connection = JDBCUtils.getConnection();
        //编写sql
        String sql = "select * from actor";
        //创建Statement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        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);
        }
        JDBCUtils.close(null,preparedStatement,connection);

    }
}

事务处理 在默认情况下 connection是默认提交的 假设出现错误可能出现一方改变另一方不变的现象(如转账) 解决方法 添加事务 connection.setAutoCommit(false) 在底部添加提交connection.commit() 如果出现错误可以在catch当中进行回滚 connection.rollback(); 代码：

public class Shiwu {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();

            connection.setAutoCommit(false);
            Scanner scanner = new Scanner(System.in);
            double money = scanner.nextDouble();

            String sql1 = "update account set money = money - ? where name = ?" ;
            String sql2 =  "update account set money = money + ? where name = ?";
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement1.setDouble(1,money);
            preparedStatement1.setString(2,"kk");
            preparedStatement2.setDouble(1,money);
            preparedStatement2.setString(2,"kkk");

            preparedStatement1.executeUpdate();
            int i =1/0;
            preparedStatement2.executeUpdate();

            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        }
    }
}

批处理 批量处理 使用connection.addBatch() 底层分析： 创建一个ArrayList---elementData => Object[] 存放预处理的sql语句 满了就按1.5倍扩容 直到遇上executeBatch才进行批量处理 相当于缓存机制 在url后面加上rewriteBatchedStatements=true

url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true

public class Shiwu {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();

            connection.setAutoCommit(false);

            String sql = "insert into account values (?,'kk',100)" ;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            long begin = System.currentTimeMillis();
            for (int i = 0; i < 1000; i++) {
//                preparedStatement.executeQuery();
                preparedStatement.setInt(1,i);
                preparedStatement.addBatch();
                if ((i+1)%100 ==0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println(end-begin);
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        }
    }
}

数据库连接池 相当于规定同时可以进入多少个连接 JDBC的数据库连接池使用 javax.sql.DataSource 来表示，DataSource只是一个接口 该接口通常由第三方提供实现 数据库连接池负责分配、管理和释放数据库连接 它允许应用陈旭重复使用一个现有的数据库连接 而不是重新建立一个 当达到一定会数量时 这些请求将被加入到等待队列当中

常用的数据库连接池 C3PO 速度相对较慢 稳定性强 DBCP 相对C3P0快一点 但是不稳定 Proxool 有监控连接池状态的功能 稳定性较差与C3P0 BoneCP 速度快 Druid(德鲁伊) 阿里提供的数据库连接吃 集DBCP C3P0 Proxool 优点于一身的数据库连接池

C3P0 如果使用 c3p0-0.9.5.2.jar需要下载mchange-commons-java-0.2.12.jar 代码：

public class C3P0_ {
    public static void main(String[] args) throws Exception{
        //创建数据源对象  实现了DataSource接口
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //通过配置文件.properties 获取相关信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\com\\kk\\jdbc\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        //给数据源 comboPooledDataSource 设置相关信息
        //注意：连接管理都是由comboPooledDataSource 管理故要配置信息
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        //设置初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //最大连接数
        comboPooledDataSource.setMaxPoolSize(50);
        //连接
        Connection connection = comboPooledDataSource.getConnection();//这个方法是从DataSource 接口实现的
        //关闭连接
        connection.close();
    }
}

Druid 导入druid包 配置druid.properties文件

public class Druid_ {
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\com\\kk\\jdbc\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        connection.close();
    }
}

Druid工具包

public class JDBCUtilsByDruid  {
    private static DataSource dataSource;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\com\\kk\\jdbc\\druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }

    //编写getConnection方法
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
    //关闭连接 不是真的断掉连接  而是吧使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement,Connection connection) throws SQLException {
        if (resultSet != null){
            resultSet.close();
        }if (statement != null){
            statement.close();
        }if (connection != null){
            connection.close();
        }
    }
}

Druid工具包的使用

public class JDBCUtilsByDruid_Use {
   @Test
    public void testSelect() throws SQLException {
       Connection connection = null;

       String sql = "select * from actor where id = ?";
       PreparedStatement preparedStatement = null;
       ResultSet resultSet = null;

       connection = JDBCUtilsByDruid.getConnection();
       preparedStatement = connection.prepareStatement(sql);
       preparedStatement.setInt(1,15);
       resultSet = preparedStatement.executeQuery();
       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);
       }
       JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
   }
}

Apache——DBUtils 出现原因：关闭connection后 resultSet结果无法使用 resultSet无法复用数据 故需要将数据持久化就需要将其封装成一个对象(pojo javaBean domain)保存

public class DBUtils_Use {
    @Test
    //使用apache-DBUtils工具类 + druid完成对表的crud
    public void testQueryMany() throws Exception{
        //得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //使用DBUtils类和接口 先引入DBUtils相关的jar
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //就可以执行相关的方法 返回ArrayList 结果集
        String sql = "select * from actor where id >= ?";

        //BeanListHandler接收多条数据
        //BeanHandler    接收单条数据
        //ScalarHandler  接收单个字段
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        for (Actor actor:list){
            System.out.println(actor);
        }
        JDBCUtilsByDruid.close(null,null,connection);
    }
}

---

本文章使用limfx的vscode插件快速发布