PreparedStatement对象

目的：

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

（1）增加

package com.gt.lesson03;

import com.gt.lesson02.utils.JdbcUtils;

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

public class Testlnsert {
    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, 5);//id
            st.setString(2, "gitgfds");
            st.setString(3, "123456");
            st.setString(4, "121654@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, null);
        }
    }
}

输出结果：

（2）删除

package com.gt.lesson03;

import com.gt.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, 4);

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

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

}

输出结果：

 

（3）更新

package com.gt.lesson03;

import com.gt.lesson02.utils.JdbcUtils;

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

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,"sanmu");
            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.gt.lesson03;

import com.gt.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();//执行
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }

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

输出结果： 

（5）防止SQL注入

package com.gt.lesson03;

import com.gt.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {

              login("sanmu","123456");//正常登录
//        login("''or 1=1", "123456");//非正常登录
    }

    //登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        //SQL
        try {
            conn = JdbcUtils.getConnection();

            //PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，比如说''会被直接转义
            //SQL
            String sql = "SELECT * FROM users WHERE `NAME`= ? AND `PASSWORD` = ?";//Mybatis

            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 throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);

        }
    }
}

输出结果：

        错误代码，SQL注入不了，查不到任何结果

        正确密码，SQL注入