JDBC--PreparedStatement

JDBC–PreparedStatement

SQl注入

用户输入的数据中有SQl关键字或语法并参与了SQl语句的编译

案例

import java.sql.*;

public class TestJdbc4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //驱动名
        String driverName = "com.mysql.jdbc.Driver";
        //连接数据库的url
        String url = "jdbc:mysql://localhost:3306/mydbjdbc?useSSL=false";
        //用户名
        String username = "root";
        //密码
        String pass = "root";

        //加载驱动
        Class.forName(driverName);

        //假设用户输入的数据
        String n = "abc' OR 1=1 OR '1=1";
        String p = "123";

        //SQL语句
        String sql = "SELECT * FROM user WHERE username='" + n + "' AND password='" + p + "'";
        System.out.println(sql);
        //建立连接
        Connection connection = DriverManager.getConnection(url, username, pass);

        //System.out.println(connection);
        Statement statement = connection.createStatement();
        //发送SQL语句
        ResultSet resultSet = statement.executeQuery(sql);

        //处理结果
        while(resultSet.next()) {
            System.out.println(resultSet.getString("username"));
            System.out.println(resultSet.getString("password"));
        }

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

如何避免SQl注入 就要使用preparedStatement

PreparedStatement

继承了Statement接口

可以对SQl语句预编译

预编译SQL语句

//SQL语句
String sql = "SELECT * FROM user WHERE username=? AND password=?";

//预编译SQL语句
PreparedStatement statement = connection.prepareStatement(sql);

设置参数

String n = "abc' OR 1=1 OR '1=1";
String p = "123";

//设置参数
statement.setString(1, n);
statement.setString(2, p);

使用JDBC进行增删改查操作 **** 重点掌握

添加

package Jdbc1;

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

public class MyExercise3{
    // preparedStatement -- 添加

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

        String driverName = "com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/mydbjdbc?useSSL=false";
        String username = "root";
        String pass = "1234";

        Class.forName(driverName);
        Connection conn = DriverManager.getConnection(url,username,pass);
        String name ="Peter";
        int age = 10;
        String gender = "male";

        String sql = "insert into `tb_stu`(`sname`, `sage`,`sgender`) values(?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        //设置参数
        pst.setString(1, name);
        pst.setInt(2, age);
        pst.setString(3, gender);
        //发送SQL语句
        int i = pst.executeUpdate();
        //处理结果
       if(i == 1){
           System.out.println("添加成功");
       }
       // 关闭资源
        pst.close();
        conn.close();

    }

}

删除

package Jdbc1;

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

public class MyExercise3_2 {
    // preparedStatement -- 删除

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

        String driverName = "com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/mydbjdbc?useSSL=false";
        String username = "root";
        String pass = "1234";

        Class.forName(driverName);
        Connection conn = DriverManager.getConnection(url,username,pass);
        String name ="Peter";
        int id = 7;
        String gender = "famale";

        String sql = "delete from `tb_stu` where `sid` = ?";
        PreparedStatement pst = conn.prepareStatement(sql);
        //设置参数
        pst.setInt(1, id);
        //发送SQL语句
        int i = pst.executeUpdate();
        //处理结果
       if(i == 1){
           System.out.println("删除成功");
       }
       // 关闭资源
        pst.close();
        conn.close();

    }

}
  

修改

package Jdbc1;

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

public class MyExercise3_1 {
    // preparedStatement -- 修改

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

        String driverName = "com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/mydbjdbc?useSSL=false";
        String username = "root";
        String pass = "1234";

        Class.forName(driverName);
        Connection conn = DriverManager.getConnection(url,username,pass);
        String name ="Peter";
        int id = 7;
        String gender = "famale";

        String sql = "Update `tb_stu` set `sname`= ?,`sgender`= ? where `sid` = ?";
        PreparedStatement pst = conn.prepareStatement(sql);
        //设置参数
        pst.setString(1, name);
        pst.setString(2, gender);
        pst.setInt(3, id);
        //发送SQL语句
        int i = pst.executeUpdate();
        //处理结果
       if(i == 1){
           System.out.println("修改成功");
       }
       // 关闭资源
        pst.close();
        conn.close();

    }

}

查询

package Jdbc1;

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

public class MyExercise3_3 {
    // preparedStatement -- 查询

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

        String driverName = "com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/mydbjdbc?useSSL=false";
        String username = "root";
        String pass = "1234";

        Class.forName(driverName);
        Connection conn = DriverManager.getConnection(url,username,pass);
        String name ="o";

        String sql = "select * from `tb_stu` where `sname` like ?";
        PreparedStatement pst = conn.prepareStatement(sql);
        //设置参数
        pst.setString(1, "%" +name + "%");
        //发送SQL语句
        ResultSet resultSet = pst.executeQuery();
        //处理结果
        while (resultSet.next()) {
            int sid = resultSet.getInt(1);
            String sname = resultSet.getString(2);
            int sage = resultSet.getInt(3);
            String sgender = resultSet.getString(4);
            System.out.println(sid + ";" + sname + ";" + sage + ";" + sgender);

        }
       // 关闭资源
        pst.close();
        conn.close();

    }

}