PreparedStatement对象

MySQL 数据库学习笔记（续）

十、JDBC 重点！！！

10.7 PreparedStatement对象

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

---

1. 增

package com.guo.lesson03;

import com.guo.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数
            String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译SQL 先写sql，不执行

            //手动给参数赋值
            st.setInt(1,4);//id
            st.setString(2,"chenguo");//NAME
            st.setString(3,"123456");//PASSWORD
            st.setString(4,"2981367375@qq.com");//email
            //注意点：  sql.Date        数据库    java.sql.Date()
            //         util.Date       Java     new Date().getTime() --- 获得时间戳
            st.setDate(5,new java.sql.Date(new Date(2001,12,5).getTime()));//birthday

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }

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

2. 删

package com.guo.lesson03;

import com.guo.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数
            String sql = "DELETE FROM users where id=?";

            st = conn.prepareStatement(sql);//预编译SQL 先写sql，不执行

            //手动给参数赋值
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }

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

3. 改

package com.guo.lesson03;

import com.guo.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数
            String sql = "UPDATE users SET `NAME` =? WHERE id =?";

            st = conn.prepareStatement(sql);//预编译SQL 先写sql，不执行

            //手动给参数赋值
            st.setString(1,"李悦");//NAME
            st.setInt(2,4);//id

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功");
            }

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

4. 查

package com.guo.lesson03;

import com.guo.lesson02.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where id =?";

            st = conn.prepareStatement(sql);

            st.setInt(1,4);//传递参数

            rs = st.executeQuery();

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
          JdbcUtils.release(conn,st,rs);
        }
    }
}

5. 防止SQL注入

-- PreparedStatement 防止SQL注入的本质：把传递进来的参数当做字符
-- 假设其中存在转义字符，就直接忽略，比如说：引号会被直接转义

package com.guo.lesson03;

import com.guo.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {

        //login("李悦","123456"); // 正常登录
        login(" ' or '1=1"," ' or '1=1");  //SQl注入失败

    }

    //登录业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // PreparedStatement 防止SQL注入的本质：把传递进来的参数当做字符
            // 假设其中存在转义字符，就直接忽略。比如说：引号会被直接转义
            String sql = "SELECT * FROM users WHERE `NAME` =? AND `PASSWORD` =?";

            st = conn.prepareStatement(sql);

            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery(); //查询完毕会返回一个结果集

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("==================================");
            }else {
                System.out.println("用户名或密码错误");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

---

总结

1. PreparedStatement 采用预编译SQL，使用 ? 作为参数占位符
2. 通过 setXxx() 方法赋值，自动转义特殊字符，彻底解决 SQL 注入
3. 性能比 Statement 更高，可重复执行
4. 企业开发中必须使用 PreparedStatement，禁止使用 Statement