开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> JDBC总结 -> 正文阅读

[大数据]JDBC总结

文章目录

JDBC：Java Database Connectivity
1、JDBC的使用步骤
2、批处理
3、JDBC中的事务
4、使用JDBC操作学生对象
5、数据库连接池
- 5.1 德鲁伊连接池技术
6、封装JDBCTools

JDBC：Java Database Connectivity

1、JDBC的使用步骤

注册驱动
- new Driver();
- DriverManager.registerDriver(new Driver());
- Class.forName(“com.mysql.jdbc.Driver”);
获取连接
发送sql执行
关闭资源

在注册驱动前，首先需要在模块下创建libs目录，将mysql-connector-java-5.1.49-bin.jar加入并Add as library。

更新案例：

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo01 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //1.注册驱动
//        new Driver();
//        DriverManager.registerDriver(new Driver());
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建连接
        String url = "jdbc:mysql://localhost:3306/jdbc_test";
        String user = "root";
        String password = "111111";
        Connection connection = DriverManager.getConnection(url, user, password);

        //3.注入sql
        Statement statement = connection.createStatement();

        String sql = "insert into stu(id,name,age) values(1,'zyn',18)";
        statement.executeUpdate(sql);
        //4. 关闭资源
        statement.close();
        connection.close();
    }
}

查询案例：

import com.mysql.jdbc.Driver;

import java.sql.*;

public class JDBCDemo02 {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        DriverManager.registerDriver(new Driver());
        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/jdbc_test";
        String user = "root";
        String password = "111111";
        Connection connection = DriverManager.getConnection(url, user, password);
        //3.注入sql

        Statement statement = connection.createStatement();
        String sql = "select * from stu";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            int age = resultSet.getInt(3);

            System.out.println(id + "\t" + name + "\t" + age);
        }

        //4.关闭资源
        statement.close();
        connection.close();
    }
}

以上案例中sql中为固定值，不适用于实际生产情况，存在问题如下：

当修改数据自定义时，sql拼接困难；

String sql="insert into stu values(null,'"+name+"',"+age+")";

sql注入值没办法限定

String sql="select * from stu where id="+id;
//id可能输入的不是整型，而是字符串或者1 ro 1=1

为此，我们使用connection接口中的prepareStatement方法解决以上问题。

DML案例：

import com.mysql.jdbc.Driver;

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

public class JDBCDemo01 {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        DriverManager.registerDriver(new Driver());
//        new Driver();
        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/jdbc_test";
        String user = "root";
        String password = "111111";
        Connection connection = DriverManager.getConnection(url, user, password);
        //3.sql
//        String sql = "insert into stu values(null,?,?)";
        String sql = "update stu set name=? where id=?";
        PreparedStatement pst = connection.prepareStatement(sql);
        pst.setString(1,"yyy");
        pst.setInt(2,4);

        pst.executeUpdate();
        //4.关闭资源
        pst.close();
        connection.close();
    }
}

DQL案例：

package com.hpu.my.jdbc.demo02;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class JDBCDemo02 {
    public static void main(String[] args) throws SQLException {
        new Driver();

        String url ="jdbc:mysql://localhost:3306/jdbc_test";
        String user = "root";
        String password = "111111";
        Connection connection = DriverManager.getConnection(url, user, password);

        String sql = "select * from stu where id > ?";

        PreparedStatement pst = connection.prepareStatement(sql);
        pst.setInt(1,4);
        ResultSet resultSet = pst.executeQuery();

        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");

            System.out.println(id + "\t" + name + "\t" + age);
        }

        pst.close();
        connection.close();
    }
}

2、批处理

url中增加参数rewriteBatchedStatements=true，使用?分割;当url后加多个参数时，多个参数间使用&间隔。
批处理的大小要适中；
PreparedStatement中的addBatch方法将一些处理手机
PreparedStatement中的executeBatch方法批处理。

对比案例：效率显著提升

package com.hpu.my.jdbc.demo03;

import com.mysql.jdbc.Driver;

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

public class Batch {
    public static void main(String[] args) throws SQLException {
        long start = System.currentTimeMillis();
        update();//16.882
//        batch();//2.4
        long stop = System.currentTimeMillis();
        System.out.println(stop-start);
    }

    private static void batch() throws SQLException {
        new Driver();

        String url = "jdbc:mysql://localhost:3306/jdbc_test?rewriteBatchedStatements=true";
        String user ="root";
        String password = "111111";
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "insert into stu values(null,?,?)";
        PreparedStatement pst = connection.prepareStatement(sql);
        pst.setString(1,"zyn");
        pst.setInt(2,18);
        for (int i = 0; i < 10000; i++) {
            if (i%500==0){
                pst.executeBatch();
            }
            pst.addBatch();
        }
        pst.executeBatch();
        pst.close();
        connection.close();
    }


    private static void update() throws SQLException {
        new Driver();

        String url = "jdbc:mysql://localhost:3306/jdbc_test";
        String user ="root";
        String password = "111111";
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "insert into stu values(null,?,?)";
        PreparedStatement pst = connection.prepareStatement(sql);
        pst.setString(1,"zyn");
        pst.setInt(2,18);
        for (int i = 0; i < 10000; i++) {
            pst.executeUpdate();
        }

        pst.close();
        connection.close();
    }
}

3、JDBC中的事务

mysql默认连接自动提交事务。关闭自动提交方式和手动提交的方式如下：

执行前，设置手动提交

Connection对象.setAutoCommit(false);
成功：

Connection对象.commit();

失败：

Connection对象.rollback();
使用结束后将手动提交变更为自动提交

Connection对象.setAutoCommit(true);

注意事项：

? 后面实际开发中，每次获取的连接，不一定是新的连接，而是从连接池中获取的旧的连接，而且你关闭也不是真关闭，而是还给连接池，供别人接着用。以防别人拿到后，以为是自动提交的，而没有commit，最终数据没有成功。

package com.hpu.my.jdbc.demo04;

import com.mysql.jdbc.Driver;

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

public class JDBCTransaction {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        new Driver();
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "111111");
        //3.将连接设置为手动提交
        connection.setAutoCommit(false);

        PreparedStatement pst = null;
        String sql1 = "update stu set age=500 where id=1";
        String sql2 = "update stu set age=1500 where id=2";

        try {
            //事务1
            pst = connection.prepareStatement(sql1);
            pst.executeUpdate();

            System.out.println(1/0);
            //事务2
            pst = connection.prepareStatement(sql2);
            pst.executeUpdate();
            connection.commit();
            System.out.println("执行成功");
        } catch (Exception e){
            System.out.println("执行失败");
            connection.rollback();
        } finally {
            pst.close();
            connection.setAutoCommit(true);
            connection.close();

        }
    }
}

4、使用JDBC操作学生对象

Student类：

package com.hpu.my.jdbc.demo05;

public class Student {
    private int id;
    private String name;
    private int age;

    public Student() {
    }

    public Student(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return id + "\t" + name + "\t" + age;
    }
}

测试类：

import com.mysql.jdbc.Driver;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCTest {
    public static void main(String[] args) throws SQLException {
//        Student s1 = new Student("qwer", 12);
//        Student s2 = new Student("rtyu", 13);
//        addStudent(s1);
//        addStudent(s2);
//        deleteStudent(1);
        List<Student> allStudent = getAllStudent();
        System.out.println(allStudent);
    }

    private static List<Student> getAllStudent() throws SQLException {
        ArrayList<Student> students = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            new Driver();
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "111111");

            String sql = "select * from stu";
            pst = connection.prepareStatement(sql);

            ResultSet resultSet = pst.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Student student = new Student(id, name, age);
                students.add(student);
            }
        } catch (Exception e) {
            System.out.println("添加失败！");
        } finally {
            pst.close();
            connection.close();
        }
        return students;
    }

    private static void deleteStudent(int sid) throws SQLException {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            new Driver();
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "111111");

            String sql = "delete from stu where id = ?";
            pst = connection.prepareStatement(sql);
            pst.setInt(1,sid);
            pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("删除失败！");
        } finally {
            pst.close();
            connection.close();
        }
    }

    private static void addStudent(Student s) throws SQLException {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            new Driver();
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "111111");

            String sql = "insert into stu values(null,?,?)";
            pst = connection.prepareStatement(sql);
            pst.setString(1,s.getName());
            pst.setInt(2,s.getAge());
            pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("添加失败！");
        } finally {
            pst.close();
            connection.close();
        }
    }
}

上面的操作有以下缺点：

代码冗余
连接对象频繁获取，每次都通过DriverManager获取新连接，用完直接抛弃断开，连接的利用率太低，太浪费。

5、数据库连接池

DAO：Data Access Object—数据访问对象

针对以上问题，可以使用数据库连接池解决。

概念：

连接对象的缓冲区。负责申请，分配管理，释放连接的操作。

? 可以建立一个连接池，这个池中可以容纳一定数量的连接对象，一开始，我们可以先替用户先创建好一些连接对象，等用户要拿连接对象时，就直接从池中拿，不用新建了，这样也可以节省时间。然后用户用完后，放回去，别人可以接着用。可以提高连接的使用率。当池中的现有的连接都用完了，那么连接池可以向服务器申请新的连接放到池中。直到池中的连接达到“最大连接数”，就不能在申请新的连接了，如果没有拿到连接的用户只能等待。

5.1 德鲁伊连接池技术

在注册驱动前，首先需要在模块下创建libs目录，将druid-1.1.10.jar加入并Add as library。

步骤：

建立一个数据库连接池
设置连接池的参数
获取连接

package com.hpu.my.jdbc.demo06;

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

import java.sql.SQLException;

public class TestPool {
    public static void main(String[] args) throws SQLException {
        //1.创建数据库连接池对象
        DruidDataSource druidDataSource = new DruidDataSource();

        //2.设置参数
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://localhost:3306/jdbc_test");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("111111");
        //设置连接数
        druidDataSource.setMaxActive(30);
        //设置等待时长
        druidDataSource.setMaxWait(5000);

        //3.获取连接
        for (int i = 0; i < 50; i++) {
            DruidPooledConnection connection = druidDataSource.getConnection();
            System.out.println(i+1 +":" + connection);
            connection.close();
        }

    }
}

如果connection.close();未注释，则总共打印50次，且每次结果相同；用完就放回去，每次拿的都是同一个；

如果connection.close();注释，则总共打印30次，每次都不一样，超过等待时间后报错。

6、封装JDBCTools

Student类同上；

JDBC_Tools工具类：

package com.hpu.my.jdbc.demo07;

import com.alibaba.druid.pool.DruidDataSourceFactory;


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

public class JDBC_Tools {
    private  static DataSource ds;
    //私有化构造器
    private JDBC_Tools() {}
    //静态代码块初始化数据库连接池
    static {
        try {
            Properties prop = new Properties();
            prop.load(JDBC_Tools.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            throw new RuntimeException("数据库连接池初始化失败！");
        }
    }
    public static Connection getConnection() {
        Connection connection =null;
        try {
            connection = ds.getConnection();
        } catch (Exception e) {
            throw new RuntimeException("创建连接对象失败！");
        }
        return connection;
    }
    //关闭connection资源
    public static void close(Connection connection){
        try {
            if (connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("关闭connection资源失败！");
        }
    }
    //关闭prepareStatement资源和connection资源
    public static void close(Statement Statement, Connection connection){
        try {
            if (Statement != null){
                Statement.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("关闭connection资源失败！");
        } finally {
            close(connection);
        }
    }
    //关闭resultSet资源、prepareStatement资源和connection资源
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(statement,connection);
        }

    }
}

StudentBasicDAO接口：

package com.hpu.my.jdbc.demo07;

import java.util.List;

public interface StudentBasicDAO {
    //提供添加学生对象的方法
    void addStudent (Student s);
    //提供删除学生对象的方法
    void deleteStudent (int sid);
    //提供修改学生对象的方法
    void updateStudent(Student s);
    //提供查询所有学生的方法
    List<Student> getAllStudent();
    //提供通过指定ID查询学生的方法
    Student getStudent(int sid);
}

StudentBasicDAO接口的实现类StudentDAOImp：

package com.hpu.my.jdbc.demo07;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImp implements StudentBasicDAO {

    @Override
    public void addStudent(Student s) {
//        Connection connection = JDBC_Tools.getConnection();
//        PreparedStatement pst =null;
//        String sql = "insert into stu(id,name,age) values(null,?,?)";
//        try {
//            pst = connection.prepareStatement(sql);
//            pst.setString(1,s.getName());
//            pst.setInt(2,s.getAge());
//            pst.executeUpdate();
//        } catch (SQLException e) {
//            System.out.println("添加学生信息失败！");
//        } finally {
//            //关闭pst和connection
//            JDBC_Tools.close(pst,connection);
//        }
        update("insert into stu(id,name,age) values(null,?,?)",s.getName(),s.getAge());
    }

    private void update(String sql,Object... arr) {
        Connection connection = JDBC_Tools.getConnection();
        PreparedStatement pst =null;
        try {
            pst = connection.prepareStatement(sql);
            for (int i = 0; i < arr.length; i++) {
                pst.setObject(i+1,arr[i]);
            }
            pst.executeUpdate();
        } catch (SQLException e) {
            System.out.println("添加学生信息失败！");
        } finally {
            //关闭pst和connection
            JDBC_Tools.close(pst,connection);
        }
    }


    @Override
    public void deleteStudent(int sid) {
        Connection connection = JDBC_Tools.getConnection();
        PreparedStatement pst =null;
        String sql = "delete from stu where id =?";
        try {
            pst = connection.prepareStatement(sql);
            pst.setInt(1,sid);
            pst.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除学生信息失败！");
        } finally {
            //关闭pst和connection
            JDBC_Tools.close(pst,connection);
        }
    }

    @Override
    public void updateStudent(Student s) {
        Connection connection = JDBC_Tools.getConnection();
        PreparedStatement pst =null;
        String sql = "update stu set name=?,age=? where id =?";
        try {
            pst = connection.prepareStatement(sql);
            pst.setString(1,s.getName());
            pst.setInt(2,s.getAge());
            pst.setInt(3,s.getId());
            pst.executeUpdate();
        } catch (SQLException e) {
            System.out.println("更新学生信息失败！");
        } finally {
            //关闭pst和connection
            JDBC_Tools.close(pst,connection);
        }
    }

    @Override
    public List<Student> getAllStudent() {
        ArrayList<Student> students = new ArrayList<>();
        Connection connection = JDBC_Tools.getConnection();
        PreparedStatement pst =null;
        String sql = "select * from stu";
        ResultSet resultSet =null;
        try {
            pst = connection.prepareStatement(sql);
            resultSet = pst.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Student student = new Student(id, name, age);
                students.add(student);
            }
        } catch (SQLException e) {
            System.out.println("更新学生信息失败！");
        } finally {
            //关闭pst和connection
            JDBC_Tools.close(resultSet,pst,connection);
        }
        return students;
    }

    @Override
    public Student getStudent(int sid) {
        Connection connection = JDBC_Tools.getConnection();
        PreparedStatement pst =null;
        String sql = "select * from stu where id =?";
        Student s = new Student();
        ResultSet resultSet =null;
        try {
            pst = connection.prepareStatement(sql);
            pst.setInt(1,sid);
            resultSet = pst.executeQuery();
            while (resultSet.next()) {
                s.setId(sid);
                s.setName(resultSet.getString(2));
                s.setAge(3);
            }
        } catch (SQLException e) {
            System.out.println("删除学生信息失败！");
        } finally {
            //关闭pst和connection
            JDBC_Tools.close(resultSet,pst,connection);
        }

        return s;
    }
}

JDBCTest测试类：

package com.hpu.my.jdbc.demo07;

import java.util.List;

public class JDBCTest {
    public static void main(String[] args) {
        Student s1 = new Student("iii", 12);
        Student s2 = new Student("jjj", 19);
        StudentDAOImp studentDAOImp = new StudentDAOImp();
//        studentDAOImp.addStudent(s1);
//        studentDAOImp.addStudent(s2);
//        studentDAOImp.deleteStudent(10);
//        studentDAOImp.updateStudent(new Student(9,"ppp",56));
//        List<Student> allStudent = studentDAOImp.getAllStudent();
//        System.out.println(allStudent);
        Student s = studentDAOImp.getStudent(9);
        System.out.println(s);
    }
}