42.PreparedStatement对象

PreparedStatement对象

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

1. 新增

   package com.faq.lesson3;
   
   import com.faq.lesson2.utils.JdbcUtils;
   
   import javax.xml.crypto.Data;
   import java.sql.*;
   
   public class TestInsert {
       public static void main(String[] args) {
           Connection conn = null;
           PreparedStatement st = null;
           ResultSet rs = 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,"tomato");
               st.setString(3,"123456");
               st.setString(4,"3276938@qq.com");
               //注意点：sql.Date 数据库   java.sql.Date()
               //      util.Date  Java   new Date().getTime()获得时间戳
               st.setDate(5,new java.sql.Date(new 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 com.faq.lesson3;
   
   import com.faq.lesson2.utils.JdbcUtils;
   
   
   import com.faq.lesson2.utils.JdbcUtils;
   
   import javax.xml.crypto.Data;
   import java.sql.*;
   import java.sql.*;
   
   public class TestDelete {
   
   
           public static void main(String[] args) {
               Connection conn = null;
               PreparedStatement st = null;
               ResultSet rs = null;
   
               try {
                   conn = JdbcUtils.getConnection();
   
                   //区别（）
                   //使用？占位符代替参数
                   String sql = "delete from users where id =?";
   
                   st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
   
                   //手动给参数赋值
                   st.setInt(1,1);//id
   
   
   
                   //执行
                   int i = st.executeUpdate();
                   if(i>0){
                       System.out.println("删除成功");
                   }
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }finally {
                   JdbcUtils.release(conn,st,rs);
               }
           }
       }
   
   
   
   

3. 更新

package com.faq.lesson3;

import com.faq.lesson2.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 {
                conn = JdbcUtils.getConnection();

                //区别（）
                //使用？占位符代替参数
                String sql = "update users set `NAME`=? where id=?;";

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

                //手动给参数赋值
                st.setString(1,"ushsua");
                st.setInt(2,3);//id



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

4. 查询

package com.faq.lesson3;

import com.faq.lesson2.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;
        ResultSet rs = null;
        PreparedStatement st = null;

        try {
             conn = JdbcUtils.getConnection();
             String sql = "select * from users where id = ?";
             st = conn.prepareStatement(sql);

             st.setInt(1,3);

             rs = st.executeQuery();
             while(rs.next()){
                 System.out.println(rs.getString("NAME"));
             }

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

PreparedStatement防止SQL注入的本质：把传递进来的参数当作字符

假设其中存在转义字符，比如’，会被直接转义