PreparedStatement对象

PreparedStatement可以防止sql注入，效率更好

1.PreparedStatement是预编译的,对于批量处理可以大大提高效率. 也叫JDBC存储过程

1.新增：

package lesson02;

import lesson01.jdbcUtils;

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

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，然后不执行

            //手动给参数赋值
            st.setInt(1,4);// id赋值
            st.setString(2,"qingchen");// name赋值
            st.setString(3,"24244234");//password赋值
            st.setString(4,"2424242@qq.com");//email赋值

            //注意点：sql.Data 数据库    java.sql.Data()
            //      util.Data  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 e) {
            throw new RuntimeException(e);
        }
        finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

2.删除：

package lesson02;

import lesson01.jdbcUtils;

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

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.Data 数据库    java.sql.Data()
            //      util.Data  java   new Date().getTime() 获得时间戳

            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 lesson02;

import lesson01.jdbcUtils;

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

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，然后不执行

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




            //执行
            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 lesson02;

import lesson01.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 = ?";//编写sql
             st = conn.prepareStatement(sql);//预编译
             st.setInt(1,1);//传递参数
            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注入：

package lesson02;

import lesson01.jdbcUtils;

import java.sql.*;

public class SQLzhuru {
    public static void main(String[] args) {
       //正常登录：
        //login("lisi","123456");
        login("'' or 1=1","12456 ' or ' 1=1"); //非正常登录,技巧
    }

    //登录业务
    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(); //查询完毕会返回一个结果集;
            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("====================");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            jdbcUtils.release(conn,st,rs);
        }
    }



}