PrepareStatement对象（新增、删除、更新、查询、防止SQL注入）

MySQL三十三：PrepareStatement对象

PrepareStatement 可以防止SQL注入，并且效率高！

1、新增

package lesson03;

import lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st=null;
        ResultSet rs=null;


        try {
            // Connection connection = JdbcUtils.getConnection();
            conn = JdbcUtils.getConnection();

            //PreparedStatement preparedStatement = conn.prepareStatement();
            //使用？占位符代替参数
            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,"xiaoliu");
            st.setString(3,"123456");
            st.setString(4,"30666864@qq.com");
            // 注意点  sql.Date 数据库    java.sql.Date()
            //        util.Date  JAVA   new Date().getTime()
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

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


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

2、删除

package lesson03;

import lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st=null;
        ResultSet rs=null;


        try {
            // Connection connection = JdbcUtils.getConnection();
            conn = JdbcUtils.getConnection();

            //PreparedStatement preparedStatement = conn.prepareStatement();
            //使用？占位符代替参数
            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 throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

3、更新

package lesson03;

import lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st=null;
        ResultSet rs=null;


        try {
            // Connection connection = JdbcUtils.getConnection();
            conn = JdbcUtils.getConnection();

            //PreparedStatement preparedStatement = conn.prepareStatement();
            //使用？占位符代替参数
            String sql="update users set `name`=? where id=?";
            st = conn.prepareStatement(sql);  //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1,"王五");
            st.setInt(2,2);


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


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

4、查询

package lesson03;

import 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 {
            // Connection connection = JdbcUtils.getConnection();
            conn = JdbcUtils.getConnection();

            //PreparedStatement preparedStatement = conn.prepareStatement();
            //使用？占位符代替参数
            String sql="select  * from users where id=?";
            st = conn.prepareStatement(sql);  //预编译SQL，先写sql，然后不执行

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

            rs=st.executeQuery(); //执行

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


        } catch (
                SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

5、防止SQL注入

package lesson02.utils;

import java.sql.*;

public class SQL注入 {

    public static void main(String[] args) {
        //login("xiaochen","123456");  //正常登陆 正常输出
        //login("'or'1=1 ","'or'1=1");  //不正常输出，结果是全部用户名和密码

        login("xiaochen","123456");

    }
    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 users where `name`=? and `password` =?";

            st=conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(1,password);

            rs=st.executeQuery();

            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("=======================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}