PreparedStatement对象

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

1、新增

package com.hua.lesson03;

import com.hua.lesson02.utils.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，先写SQL，然后不执行

            //手动给参数赋值
            st.setInt(1,6);
            st.setString(2,"lisi");
            st.setString(3,"126466");
            st.setString(4,"123456@qq.com");

            //注意点： sql.Date    数据库   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,null);
        }

    }
}

2、删除

package com.hua.lesson03;

import com.hua.lesson02.utils.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，然后不执行

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

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

3、修改

package com.hua.lesson03;

import com.hua.lesson02.utils.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，先写SQL，然后不执行

            //手动给参数赋值
            st.setString(1,"杨不悔");
            st.setInt(2,1);

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

4、查询

package com.hua.lesson03;

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

            String sql = "select * from users where id = ?";//编写SQL

            st = conn.prepareStatement(sql);//预编译

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

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

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

5、防止SQL注入

package com.hua.lesson03;

import com.hua.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) {
//        login("lisi","123456");
      login("' ' or '1=1","' ' 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` = ?";//mybatis

             st = conn.prepareStatement(sql);

             st.setString(1,username);
             st.setString(2,password);
            //SQl
            //select * from users where `NAME` = '' or '1=1' and `password` = ''  or '1=1'

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

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

    }
}