PreparedStatement对象

• 可以防止SQL注入，效率更好。

1、新增

package com.sw.kuangshen.lesson03;

import com.sw.kuangshen.utils.JdbcUtils;

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

/**
 * @Author suaxi
 * @Date 2020/11/9 10:12
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = JdbcUtils.getConnection();
            //PreparedStatement与Statement的区别
            //使用？占位符代替参数
            String sql = "insert into users(id,name,passwd,email,birthday) values(?,?,?,?,?)";
            pstm = conn.prepareStatement(sql); //预编译SQl，先编译sql但不执行

            //手动给参数赋值
            pstm.setInt(1,5);
            pstm.setString(2,"liubo");
            pstm.setString(3,"12345");
            pstm.setString(4,"liubo@qq.com");
            //注：sql.Date 数据库使用 java.sql.Date()
            //    util.Date Java     new Date().getTime() 获得当前时间戳
            pstm.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = pstm.executeUpdate();
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pstm,null);
        }
    }
}

2、删除

package com.sw.kuangshen.lesson03;

import com.sw.kuangshen.utils.JdbcUtils;

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

/**
 * @Author suaxi
 * @Date 2020/11/9 10:28
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = JdbcUtils.getConnection();
            //PreparedStatement与Statement的区别
            //使用？占位符代替参数
            String sql = "delete from users where id=?";
            pstm = conn.prepareStatement(sql); //预编译SQl，先编译sql但不执行

            //手动给参数赋值
            pstm.setInt(1,5);

            //执行
            int i = pstm.executeUpdate();
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pstm,null);
        }
    }
}

3、修改

package com.sw.kuangshen.lesson03;

import com.sw.kuangshen.utils.JdbcUtils;

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

/**
 * @Author suaxi
 * @Date 2020/11/9 10:28
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = JdbcUtils.getConnection();
            //PreparedStatement与Statement的区别
            //使用？占位符代替参数
            String sql = "update users set `name`=? where id=?";
            pstm = conn.prepareStatement(sql); //预编译SQl，先编译sql但不执行

            //手动给参数赋值
            pstm.setString(1,"孙笑川");
            pstm.setInt(2,2);

            //执行
            int i = pstm.executeUpdate();
            if(i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pstm,null);
        }
    }
}

4、查询

package com.sw.kuangshen.lesson03;

import com.sw.kuangshen.utils.JdbcUtils;

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

/**
 * @Author suaxi
 * @Date 2020/11/9 10:34
 */
public class TestSelect {
    public static void main(String[] args) {

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

        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from users where id=?";
            pstm = conn.prepareStatement(sql); //预编译
            pstm.setInt(1,2); //传递参数
            rs = pstm.executeQuery(); //执行

            if (rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pstm,rs);
        }
    }
}

5、防止SQL注入

package com.sw.kuangshen.lesson03;

import com.sw.kuangshen.utils.JdbcUtils;

import java.sql.*;

/**
 * @Author suaxi
 * @Date 2020/11/9 9:57
 */
public class SQlzhuru {
    public static void main(String[] args) {
        //login("dd","12345"); //正常登录
        login("''or 1=1","12345");
    }

    //登录
    public static void login(String username,String passwd){
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs =null;

        try {
            conn = JdbcUtils.getConnection();
            //PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符
            //假设其中存在转义字符，例如 ' 会被直接转义
            String sql = "select * from users where `name`=? and passwd=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,username);
            pstm.setString(2,passwd);

            rs = pstm.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("passwd"));
                System.out.println("=================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pstm,rs);
        }
    }

}