JDBC使用与操作

项目创建

新建maven项目,导入MySQL的驱动包.pom文件如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.otnios.jdbc</groupId>
    <artifactId>jdbc_test</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>21</maven.compiler.source>
        <maven.compiler.target>21</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

</project>

JDBC快速入门

1. 驱动注册,jdk6之后java会自动检索文件中的driver,故可以省略不写;
2. 建立连接, 通过DriverManager.getConnection()得到建立对象后进行后续操作;
3. 获取查询对象statement,使用connection连接对象获得执行查询的对象;
4. 执行SQL语句后得到返回的结果-resultSet;
5. 处理结果,通过判断resultSet.next()是否为true,进而通过get('列名')方法获得返回集合中的查询信息;
6. 资源释放,先开后关.

public class JDBCQuick {
    public static void main(String[] args) throws Exception {

        // 驱动注册 jdk6之后java中自动会检索driver 可省略不写
        //Class.forName("com.mysql.cj.jdbc.Driver");

        // 获取连接对象 /数据库名称
        String url = "jdbc:mysql://localhost:3306/test1";
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, username, password);

        // 获取执行查询的对象
        Statement statement = connection.createStatement();

        // 编写SQL执行语句,得到返回的结果
        String sql = "SELECT * FROM user;";
        ResultSet resultSet = statement.executeQuery(sql);

        // 处理结果
        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            double userSalary = resultSet.getDouble("user_salary");
            int userAge = resultSet.getInt("user_age");
            System.out.println(userId+"\t" +userName+"\t" +userSalary+"\t" +userAge);
        }

        //释放资源,先开后关
        resultSet.close();
        statement.close();
        connection.close();
    }
}

JDBC的SQL注入问题

使用statement查询对象,会存在SQL注入问题,原因是这种方式进行SQL查询时,SQL语句是拼接的,所以会通过输入1=1使判断为真,所以存在注入隐患.

下面的查询中,如果我们在输入查询姓名时,输入张顺飞' or '1' = '1时,这是会拼接为

select * from user where user_name = '张顺飞' or '1' = '1'

导致查询成功.这就是SQL注入. 

public class JDBCInjection {
    public static void main(String[] args) throws Exception {

        //注册驱动 省略

        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");

        //获取执行sql的statement
        Statement statement = connection.createStatement();

        //sql编写与执行
        System.out.println("输入查询姓名:");
        Scanner scanner = new Scanner(System.in);
        String name = scanner.nextLine();
        //张顺飞' or '1' = '1 sql注入问题
        String sql = "select * from user where user_name = '"+name+"'";
        ResultSet resultSet = statement.executeQuery(sql);

        //展示结果
        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            double userSalary = resultSet.getDouble("user_salary");
            int userAge = resultSet.getInt("user_age");
            System.out.println(userId+"\t" +userName+"\t" +userSalary+"\t" +userAge);
        }

        //释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

SQL注入问题的解决方式

我们可以通过另一个查询对象来避免statement的SQL注入问题.那就是PreparedStatement.

PreparedStatement是Statement接口的一个子接口,通过预编译SQL语句的方式避免SQL注入情况的发生
预编译SQL语句,采用?占位符的方式将传入的参数使用单引号包裹起来,使得无论传入什么都将其作为值处理.

我们需要对占位符?进行填充,使用

preparedStatement.setString(1, name);

方法,执行查询方法

ResultSet resultSet = preparedStatement.executeQuery();

执行更新方法

int row = preparedStatement.executeUpdate();

row为完成更新影响的行数

public class JDBCPrepared {
    public static void main(String[] args) throws Exception {

        //注册驱动 省略

        //建立数据库连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");

        String sql = "select * from user where user_name = ?";

        //实例化PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        System.out.println("输入用户名:");
        Scanner scanner = new Scanner(System.in);
        String name = scanner.nextLine();

        //执行sql 填充?占位符
        preparedStatement.setString(1, name);
        ResultSet resultSet = preparedStatement.executeQuery();

        //得到结果
        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            double userSalary = resultSet.getDouble("user_salary");
            int userAge = resultSet.getInt("user_age");
            System.out.println(userId+"\t" +userName+"\t" +userSalary+"\t" +userAge);
        }

        //释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

JDBC工具类封装

public class JDBCUtil {

    private JDBCUtil(){}

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
       return DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");
    }

    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        if (resultSet == null) {
            try {
                resultSet.close();
            }catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (preparedStatement == null) {
            try {
                preparedStatement.close();
            }catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (connection == null) {
            try {
                connection.close();
            }catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

JDBC增删改查测试

public class JDBCOperaTest {

    @Test
    public void testQuerySingleRowAndCol() throws SQLException {
        //查询单行单列
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", "root", "root");

        String sql = "select count(*) as count from user;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        // 如果明确只有一个结果,可以使用if判断 resultSet至少要做一次next判断
        if (resultSet.next()){
            int count = resultSet.getInt("count");
            System.out.println(count);
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
    
    @Test
    public void testQuerySingleRow() throws SQLException {
        //查询单行多列
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");

        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where user_id = ?");
        preparedStatement.setInt(1,3);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            double userSalary = resultSet.getDouble("user_salary");
            int userAge = resultSet.getInt("user_age");

            System.out.println(userId+"\t" +userName+"\t" +userSalary+"\t" +userAge+"\t");
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

    }

    @Test
    public void testQueryMoreRow() throws SQLException{
        //查询多行多列
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");

        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where user_age > ?");
        preparedStatement.setInt(1,25);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            double userSalary = resultSet.getDouble("user_salary");
            int userAge = resultSet.getInt("user_age");

            System.out.println(userId+"\t" +userName+"\t" +userSalary+"\t" +userAge+"\t");
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testQueryInsert() throws SQLException {
        //新增
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");

        PreparedStatement preparedStatement = connection.prepareStatement("insert into user values(?,?,?,?)");
        preparedStatement.setInt(1,57);
        preparedStatement.setString(2,"八分");
        preparedStatement.setDouble(3,123.45);
        preparedStatement.setInt(4,45);
        int row = preparedStatement.executeUpdate();

        System.out.println(row);

        //row.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdateSingleRow() throws SQLException {
        //修改
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");

        PreparedStatement preparedStatement = connection.prepareStatement("update user set user_name = ? where user_id = ?");
        preparedStatement.setString(1,"飞马");
        preparedStatement.setInt(2,57);
        int row = preparedStatement.executeUpdate();
        System.out.println(row);

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDeleteRow() throws SQLException {
        //删除
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", "root", "root");

        PreparedStatement preparedStatement = connection.prepareStatement("delete from user where user_id = ?");
        preparedStatement.setInt(1,57);
        int row = preparedStatement.executeUpdate();

        System.out.println(row);

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testJDBCUtils() throws SQLException {
        //工具类测试
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where user_id = ?");
        preparedStatement.setInt(1,1);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            double userSalary = resultSet.getDouble("user_salary");
            int userAge = resultSet.getInt("user_age");

            System.out.println(userId+"\t" +userName+"\t" +userSalary+"\t" +userAge+"\t");
        }
        JDBCUtil.close(connection,preparedStatement,resultSet);
    }
}