JDBC学习日志三，Statement和SQL注入

若java程序想要对数据库数据进行增删改查，只需要执行statement，这样可以向数据库发送sql语句。

使用executeUpdate方法适合增删改操作，返回的结果是一个被影响的行数，也就是增删改的操作对数据库表几行数据发生了变化

executeUpdate执行完返回的是一个整数（即增删改语句导致了数据库几行数据发生变化）

statement.executeUpdate() 方法用于向数据库发送查询语句

statement.executeQuery()用于查询数据库表的数据，返回的是一个结果集ResultSet，数据被封装到里面

CRUD

CRUD--delete

  String sql = "DELETE from users WHERE id=2 ";
         int i = statement.executeUpdate(sql);
         if (i > 0){
             System.out.println("删除成功");
}

CRUD--insert

Statement statement = connection.createStatement();
         String sql = "INSERT INTO users(...) VALUES(...),(...),(...) ";
         int i = statement.executeUpdate(sql);
         if (i > 0){
             System.out.println("添加成功");
         }

CRUD--update

Statement statement = connection.createStatement();
         String sql = "UPDATE users set `name`= '...' WHERE id = ... ";
         int i = statement.executeUpdate(sql);
         if (i > 0){
             System.out.println("修改成功");
         }

CRUD--select

//4.执行SQL的对象 Statement 执行SQL的对象
        Statement statement = connection.createStatement();

//5.执行SQL的对象去执行SQL
        String sql = "SQL语句";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询出来的结果
        while (resultSet.next()) {
            System.out.println("id" + resultSet.getObject("id"));
            System.out.println("NAME" + resultSet.getObject("NAME"));
            System.out.println("..." + resultSet.getObject("..."));
            System.out.println(...);
            ...
        }

提取工具类

建立一个util包，里面用来存放相同操作的代码，包括：

• 用户信息和url

• 连接数据库对象

• 关闭资源

另外，得新建一个properties文件，放在src文件夹下，用于存放用户信息以及加载驱动的实现类

driver = com.mysql.cj.jdbc.Driver

username = 你的用户名称

password = 你的密码

url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true

创建工具类

package org.example.Utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class Jdbcutils {
        private static String driver = null;
        private static String url = null;
        private static String username = null;
        private static String password = null;




    static {
        try {
            InputStream in = Jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只需要加载一次
            Class.forName(driver);


        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }


    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet set) {
        if (set != null){
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

CRUD操作

package org.example.Utils;

import com.alibaba.druid.util.JdbcUtils;

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

public class test {
    public static void main(String[] args)  {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = Jdbcutils.getConnection();
            statement = connection.createStatement();
            String sql = "DELETE from users WHERE id=9";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}
public class TestInsert {
    public static void main(String[] args)  {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "INSERT INTO users(`name`,`password`,`email`,`birthday`) VALUES('zhangsan','232322','zhangsan@163.com','1995-08-06'),('lisi','232322','lisi@163.com','2002-08-06'),('wangwu','232322','wangwu@163.com','1989-08-06'),('hanqing','232322','1438617560@qq.com','1995-08-06') ";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("添加成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}
public class TestUpdate {
    public static void main(String[] args)  {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "UPDATE users set `name`= '汉青' WHERE id=13";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}
public class TestSelect {
    public static void main(String[] args)  {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from `users` where id=13";
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
                System.out.println(resultSet.getString("email"));
                System.out.println(resultSet.getDate("birthday"));
                System.out.println("=====================");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

SQL注入

问题：而statement编译sql时会出现sql注入问题

模拟登陆操作

package com.kuangstudy.lesson02;
 ​
 import com.kuangstudy.lesson02.util.JdbcUtil;
 ​
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 ​
 public class SQL注入 {
     public static void main(String[] args) {
         //使用字符串拼接一个or，表示有一个为真则结果为真
         login("' or '1=1","232322");
     }
     //用户登录
     public static void login(String username,String password){
         Connection connection = null;
         Statement statement = null;
         ResultSet resultSet = null;
         try {
             connection = JdbcUtil.getConnection();
             statement = connection.createStatement();
             String sql = "select * from `users` where `name`='"+ username+"' and `password`='"+password+"' ";
             resultSet = statement.executeQuery(sql);
             if (resultSet.next()){
                 System.out.println("登录成功");
             }
         } catch (SQLException e) {
             e.printStackTrace();
         }finally {
             JdbcUtil.release(connection,statement,resultSet);
         }
 ​
 ​
     }
 }
```解决办法，使用preparedstatement方法提前将预编译sql语句，并使用?站位符