JDBC的Prepar执行对象

PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更高！

JDBC工具类

db.properties文件

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/school?serverTimezone=UTC
username = root
password = 123456

JdbcUtils

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");
            //驱动只用加载一次
            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 conn, Statement st, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

1.新增

package jdbc.preparestatement;

import utils.JdbcUtils;

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

public class InsertTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
             conn = JdbcUtils.getConnection();
             //prepareStatement与statement的区别：
            //使用？占位符代替参数
             String sql = "INSERT INTO testmd5 (`name`,`pwd`)VALUES(?,?)";
             st = conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
            //手动赋值
            st.setString(1,"yuan_boss");
            st.setString(2,"666666");
            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

2.删除

package jdbc.preparestatement;

import utils.JdbcUtils;

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

public class DeleteTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            //prepareStatement与statement的区别：
            //使用？占位符代替参数
            String sql = "delete  from testmd5 where id=?";
            st = conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
            //手动赋值
            st.setInt(1,8);
            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

3.更新

package jdbc.preparestatement;

import utils.JdbcUtils;

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

public class UpdateTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            //prepareStatement与statement的区别：
            //使用？占位符代替参数
            String sql = "update testmd5 set pwd=? where id = ?";
            st = conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
            //手动赋值
            st.setString(1,"888888");
            st.setInt(2,2);
            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

4.查询

package jdbc.preparestatement;

import utils.JdbcUtils;

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

public class SelectTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //prepareStatement与statement的区别：
            //使用？占位符代替参数
            String sql = "select * from testmd5 where id=?";
            st = conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
            //手动赋值
            st.setInt(1,2);//传递参数
            //执行
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println("Name:"+rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

5.防止SQL注入

prepareStatement 防止SQL注入的本质，把传递进来的参数当做字符

假设SQL中存在转义字符，就直接忽略，比如说 ’ 会被直接转义

package jdbc.preparestatement;

import utils.JdbcUtils;

import java.sql.*;

public class SQL注入2 {
    public static void main(String[] args) {
        //login("yuan","666666");
        login("''or '1=1'","''or '1=1'");//技巧
    }
    //登录业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //prepareStatement 防止SQL注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，就直接忽略，比如说 ' 会被直接转义
            String sql = "SELECT * FROM testmd5 WHERE name = ? AND pwd = ? ";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println("id:"+rs.getInt("id"));
                System.out.println("name:"+rs.getString("name"));
                System.out.println("pwd:"+rs.getString("pwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}