JDBC学习总结（1）

岛雨QA
193 阅读7分钟

一、JDBC 入门

数据准备：

create database web_test;
use web_test;

create table user(
	id int primary key auto_increment,
	username varchar(20),
	password varchar(20),
	nickname varchar(20),
	age int
);

insert into user values (null, 'zhangsan', 'test123', '风清扬', 34);
insert into user values (null, 'lisi', 'wang22test', '逍遥子', 12);
insert into user values (null, 'wangwu', '8899test', '东方不败', 36);
insert into user values (null, 'zhaoliu', '123456', '西施', 23);
insert into user values (null, 'wuba', 'qwer!', '杨玉环', 60);

select * from user;
image.png

下载引入jar包：dev.mysql.com/downloads/c… image.png

image.png

添加成功：

image.png

基础代码案例：

public class JDBCDemo1 {

    @Test
    public void demo1() throws ClassNotFoundException, SQLException {
        // 第一步：加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 第二步：获得连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test", "root", "root");

        // 第三步：基本操作-执行SQL
        // 1、获得执行SQL语句的对象
        Statement statement = connection.createStatement();
        // 2、编写SQL
        String sql = "select * from user;";
        // 3、执行SQL
        ResultSet resultSet = statement.executeQuery(sql);
        // 4、结果集遍历使用
        while (resultSet.next()) {
            System.out.println("[id]：" + resultSet.getInt("id") + "  "
                    + "[username]：" + resultSet.getString("username") + "  "
                    + "[password]：" + resultSet.getString("password") + "  "
                    + "[nickname]：" + resultSet.getString("nickname") + "  "
                    + "[age]：" + resultSet.getInt("age"));
        }

        // 第四步：释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

二、JDBC API详解

1、DriverManager

DriverManager：驱动管理类

作用一：注册驱动

// 一般开发中使用以下方式，可以防止驱动被注册两次：
Class.forName("com.mysql.jdbc.Driver");

作用二：获得与数据库连接的方法

// 参数：url（与数据库连接的路径）
// username（与数据库连接的用户名）
// password（与数据库连接的密码）
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test", "root", "root1234");

其中url的写法：

  • jdbc:mysql://localhost:3306/web_test
  • jdbc:mysql:///web_test --- 连接本机的省略写法

2、Connection

Connection：接口，与数据库的连接对象

// 3.1 获得执行SQL语句的对象
Statement statement = connection.createStatement();

作用一：创建执行SQL语句的对象

  • createStatement(); 创建一个Statement对象来将SQL语句发送到数据库
    • Statement-执行SQL
  • prepareCall(String sql); 创建一个CallableStatement对象来调用数据库存储过程
    • CallableStatement-执行数据库中的存储过程
  • prepareStatement(String sql); 创建一个PrepareStatement对象来将参数化的SQL语句发送到数据库
    • PrepareStatement-执行SQL，可以对SQL进行预处理，可以解决SQL注入的漏洞

作用二：管理事务

  • setAutoCommit(boolean autoCommit); 将此连接的自动提交模式设置为给定模式
  • commit(); 使上一次提交/回滚后进行的更改成为持久更改
  • rollback(); 取消在当前事务中进行的所有更改，并释放数据库锁

3、Statement

Statement：接口，用于执行静态SQL语句并返回它所生成结果的对象。子接口CallableStatement、 PrepareStatement

// 3：基本操作-执行SQL
// 3.1 获得执行SQL语句的对象
Statement statement = connection.createStatement();
// 3.2 编写SQL语句
String sql = "select * from user;";
// 3.3 执行SQL语句
ResultSet resultSet = statement.executeQuery(sql);

作用一：执行SQL语句

  • Boolean execute(String sql);
    • 执行SQL语句，可以用来执行查询、修改、添加、删除的语句。如果结果是ResultSet，则返回true；否则返回false
  • ResultSet executeQuery( String sql );
    • 执行SQL语句，返回单个ResultSet对象。专门用来执行查询（只能执行select语句）
  • int executeUpdate();
    • 执行SQL语句，可以用来执行修改、添加、删除的语句。返回的值是影响的行数

作用二：执行批处理

  • addBatch(String sql);
    • 将SQL命令添加到此Statement对象的命令列表中
  • clearBatch();
    • 清空此Statement对象的SQL命令列表
  • int[] executeBatch();
    • 执行批处理命令

4、ResultSet

ResultSet：表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。

// 3、执行SQL
ResultSet resultSet = statement.executeQuery(sql);
// 4、结果集遍历使用
while (resultSet.next()) {
    System.out.println("[id]：" + resultSet.getInt("id") + "  "
            + "[username]：" + resultSet.getString("username") + "  "
            + "[password]：" + resultSet.getString("password") + "  "
            + "[nickname]：" + resultSet.getString("nickname") + "  "
            + "[age]：" + resultSet.getInt("age"));
}
  • 结果集的遍历
    • next() 使光标向下移动一位
  • 结果集的获取
    • getXXX(int columnIndex) 获取数据
    • getXXX(String columnName ) 获取数据

5、资源释放

Connection对象一定要尽量晚创建，尽早释放。

注意：将资源释放的代码写到finally的代码块中；

三、JDBC CRUD操作 🔥

首先将公共代码抽取工具类：

public class JDBCUtils {
    private static final String DRIVER_CLASSNAME;
    private static final String URL;
    private static final String USER_NAME;
    private static final String PASS_WORD;

    static {
        DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";
        URL = "jdbc:mysql://localhost:3306/web_test";
        USER_NAME = "root";
        PASS_WORD = "root";
    }

    /**
     * 注册驱动方法
     */
    public static void loadDriver() {
        try {
            Class.forName(DRIVER_CLASSNAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得连接的方法
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            loadDriver(); // 前提是注册驱动
            connection = DriverManager.getConnection(URL, USER_NAME, PASS_WORD);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    /**
     * 释放资源的方法
     */
    public static void release(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            connection = null;
        }
    }

    public static void release(Statement statement, Connection connection, ResultSet resultSet) {
        release(statement, connection);
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            resultSet = null;
        }
    }
}

1、查询操作

public class Crud1 {
    @Test
    public void select() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where id=5;";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println("[id]：" + resultSet.getInt("id") + "  "
                        + "[username]：" + resultSet.getString("username") + "  "
                        + "[password]：" + resultSet.getString("password") + "  "
                        + "[nickname]：" + resultSet.getString("nickname") + "  "
                        + "[age]：" + resultSet.getInt("age"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.release(statement, connection, resultSet);
        }
    }
}

2、保存操作

public class Crud2 {
    @Test
    public void insert() {
        Connection connection = null;
        Statement statement = null;

        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "insert into user values (null, 'jdbcUser', '1234', '哈哈JDBC', 27);";
            int num = statement.executeUpdate(sql);
            if (num > 0) {
                System.out.println("保存用户成功");
            } else {
                System.out.println("插入失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.release(statement, connection);
        }
    }
}

3、修改操作

// 在保存操作里面，只需要修改SQL语句：
String sql = "update user set username='newName' where id=6";

4、删除操作

// 在保存操作里面，只需要修改SQL语句：
String sql = "delete from user where id=3;";

5、使用配置文件Properties

定义配置文件：db.properties

driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/web_test
userName = root
passWord = root1234

修改工具类：

static {
    // 获取属性文件内容
    Properties properties = new Properties();
    try {
        properties.load(new FileInputStream("src/db.properties"));
    } catch (IOException e) {
        e.printStackTrace();
    }
    DRIVER_CLASSNAME = properties.getProperty("driverClassName");
    URL = properties.getProperty("url");
    USER_NAME = properties.getProperty("userName");
    PASS_WORD = properties.getProperty("passWord");
}

四、JDBC SQL注入

1、演示SQL注入

/*
基本登录功能
 */
public class UserDao {
    public boolean login(String username, String password) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        boolean flag = false;

        try {
            connection = JDBCUtilsNew.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where username='" + username + "' "
                    + "and password='" + password + "';";
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                flag = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection, resultSet);
        }

        return flag;
    }
}

public class UserDaoTest {
    @Test
    public void demo(){
        UserDao userDao = new UserDao();
        // boolean flag = userDao.login("zhangsan", "test1234"); // false
        // boolean flag = userDao.login("zhangsan", "test123"); // true
        boolean flag = userDao.login("zhangsan' or '1=1", "qqq"); // true
        boolean flag1 = userDao.login("zhangsan' -- ", "qqq"); // true

        if (flag){
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
    }
}

2、SQL注入漏洞分析

String sql = "select * from user where username='" + username + "' " + "and password='" + password + "';";

正常情况下：

select * from user where username='zhangsan' and password='test123';

第一种情况：

boolean flag = userDao.login("zhangsan' or '1=1", "qqq");

select * from user where username='zhangsan' or '1=1' and password='qqq';

or是SQL的关键字，所以两个条件满足一个即可

第二种情况：

boolean flag1 = userDao.login("zhangsan' -- ", "qqq");

select * from user where username='zhangsan' -- and password='qqq';

and是SQL的关键字，代表注释

3、如何解决SQL注入

解决方案：使用Prepare Statement

这个对象将SQL预先进行编译，使用问号?作为占位符，?所代表的内容是SQL所固定的。

再次传入的变量（有可能包含SQL关键字），这个时候也不会识别这些关键字。

五、JDBC PreparedStatement 🔥

1、保存操作

public class PreparedStatementDemo1 {
    @Test
    public void save() {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JDBCUtilsNew.getConnection();
            String sql = "insert into user values (null, ?, ?, ?, ?);";
            // 预编译SQL
            statement = connection.prepareStatement(sql);
            statement.setString(1, "username111");
            statement.setString(2, "password111");
            statement.setString(3, "测试");
            statement.setInt(4, 30);
            int result = statement.executeUpdate();
            if (result > 0) {
                System.out.println("保存成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection);
        }
    }
}
image.png

2、修改操作

public class PreparedStatementDemo2 {
    @Test
    public void update() {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JDBCUtilsNew.getConnection();
            String sql = "update user set username=? where id=?;";
            // 预编译SQL
            statement = connection.prepareStatement(sql);
            statement.setString(1, "cehsihhhh");
            statement.setInt(2, 7);

            int result = statement.executeUpdate();
            if (result > 0) {
                System.out.println("修改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection);
        }
    }
}
image.png

3、删除操作

public class PreparedStatementDemo3 {
    @Test
    public void delete() {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JDBCUtilsNew.getConnection();
            String sql = "delete from user where username=?";
            // 预编译SQL
            statement = connection.prepareStatement(sql);
            statement.setString(1, "newName");

            int result = statement.executeUpdate();
            if (result > 0) {
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection);
        }
    }
}
image.png

4、查询操作

public class PreparedStatementDemo4 {
    @Test
    public void select() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtilsNew.getConnection();
            String sql = "select * from user;";
            // 预编译SQL
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println("[id]：" + resultSet.getInt("id") + "  "
                        + "[username]：" + resultSet.getString("username") + "  "
                        + "[password]：" + resultSet.getString("password") + "  "
                        + "[nickname]：" + resultSet.getString("nickname") + "  "
                        + "[age]：" + resultSet.getInt("age"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection);
        }
    }
}

六、JDBC 批处理操作

1、批处理方法

addBatch(String sql); 将SQL命令添加到此Statement对象的命令列表中

clearBatch(); 清空此Statement对象的SQL命令列表

int[] executeBatch(); 执行批处理命令

2、使用Statement

public class BatchDemo1 {
    @Test
    public void batchSql() {
        Connection connection = null;
        Statement statement = null;

        try {
            connection = JDBCUtilsNew.getConnection();
            statement = connection.createStatement();
            // SQL语句
            String sql1 = "create table user1(id int primary key auto_increment," +
                    "name varchar(20));";
            String sql2 = "insert into user1 values (null, 'aaa');";
            String sql3 = "insert into user1 values (null, 'bbb');";
            String sql4 = "insert into user1 values (null, 'ccc');";
            String sql5 = "update user1 set name = 'new' where id = 2;";
            String sql6 = "delete from user1 where id = 1;";
            // 添加到批处理
            statement.addBatch(sql1);
            statement.addBatch(sql2);
            statement.addBatch(sql3);
            statement.addBatch(sql4);
            statement.addBatch(sql5);
            statement.addBatch(sql6);
            // 执行批处理
            statement.executeBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection);
        }
    }
}

2、使用PreparedStatement

public class BatchDemo2 {
    @Test
    public void batchSql() {
        long begin = System.currentTimeMillis();
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JDBCUtilsNew.getConnection();
            // 编写SQL语句
            String sql = "insert into user1 values (null, ?)";
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < 10000; i++){
                statement.setString(1, "name" + i);
                // 添加到批处理
                statement.addBatch();
                // 执行批处理
                if (i % 1000 == 0){
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsNew.release(statement, connection);
        }

        // 记录结束时间
        long end = System.currentTimeMillis();
        System.out.println("花费时间(秒)：" + (end - begin) / 1000);
    }
}
avatar
文章
阅读
粉丝