JDBC补充 PreparedStatement对象

JDBC补充 PreparedStatement对象

**PreparedStatement 可以防止SQL注入问题，效率更好 **

新增 create

import com.lantian.lesson02.utils.jdbcUtils;

import java.sql.*;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        //获取连接
        try {
            conn = jdbcUtils.getConnection();    //获取数据库连接
            //与statement对象的区别
            //使用?占位符代替参数
            String sql = "update users set `name`=? where id=?;";

            pst = conn.prepareStatement(sql);        //预编译sql,xianxiesql,但不执行
            //手动给参数赋值
            pst.setString(1,"Lantian");
            pst.setInt(2,4);
            //执行
            int in = pst.executeUpdate();
            if (in>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

删除 dalete

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        //获取连接
        try {
            conn = jdbcUtils.getConnection();    //获取数据库连接
            //与statement对象的区别
            //使用?占位符代替参数
            String sql = "delete from users where id=?";

            pst = conn.prepareStatement(sql);        //预编译sql,xianxiesql,但不执行
            //手动给参数赋值
            pst.setInt(1,5);
            //执行
            int in = pst.executeUpdate();
            if (in>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

更新 update

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        //获取连接
        try {
            conn = jdbcUtils.getConnection();    //获取数据库连接
            //与statement对象的区别
            //使用?占位符代替参数
            String sql = "update users set `name`=? where id=?;";

            pst = conn.prepareStatement(sql);        //预编译sql,xianxiesql,但不执行
            //手动给参数赋值
            pst.setString(1,"Lantian");
            pst.setInt(2,4);
            //执行
            int in = pst.executeUpdate();
            if (in>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

---

检索 查找 Retrieve

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = jdbcUtils.getConnection();
            //    SQL
            String sql = "select * from users where id = ?;";
            //预编译SQL
            pst = conn.prepareStatement(sql);

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

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

            if (rs.next()){
                System.out.println("查询成功");
                System.out.println("id="+ rs.getObject("id"));
                System.out.println("name="+ rs.getObject("name"));
                System.out.println("pwd="+ rs.getObject("password"));
                System.out.println("email="+ rs.getObject("email"));
                System.out.println("birthday="+ rs.getObject("birthday"));
                System.out.println("=======================================================");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

防止SQL注入

prepareStatement 防止SQL注入的本质，把传递进来的参数当作字符
假设其中存在转义字符，比如 ` 会被直接转义

public class SQLinject {
    public static void main(String[] args) {
        login("Lantian","123456");
        System.out.println("==========================");
        login("``or 1=1","123456");//使用PreparedStatement对象后 这种方式什么也查不出来了 
    }

    //登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        //获取连接
        try {
            conn = jdbcUtils.getConnection();    //获取数据库连接
            //SQL
            //prepareStatement 防止SQL注入的本质，把传递进来的参数当作字符
            //假设其中存在转义字符，比如  `  会被直接转义
            String sql = "SELECT * FROM users WHERE `name` = ? AND `password`= ?;";
            pst   = conn.prepareStatement(sql);
            //传递参数
            pst.setString(1,username);
            pst.setString(2,password);
            rs  = pst.executeQuery();
            while(rs.next()){
                System.out.println("查询成功");
                System.out.println("id="+ rs.getObject("id"));
                System.out.println("name="+ rs.getObject("name"));
                System.out.println("pwd="+ rs.getObject("password"));
                System.out.println("email="+ rs.getObject("email"));
                System.out.println("birthday="+ rs.getObject("birthday"));
                System.out.println("=======================================================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}