PrepareStatement对象

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

1、新增

package com.yang.lesson03;

import com.yang.lesson02.utils.JdbcUtils;


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

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,4);//id
            st.setString(2,"yang");
            st.setString(3,"123456");
            st.setString(4,"1356619932@qq.com");
            //注意点：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 e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

2、删除

package com.yang.lesson03;

import com.yang.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 e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

3、更新

package com.yang.lesson03;

import com.yang.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 e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

4、查询

package com.yang.lesson03;


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

5、防止SQL注入

package com.yang.lesson03;

import com.yang.lesson02.utils.JdbcUtils;

import java.sql.*;

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

        //login("lisi","123456");
        login("''or 1=1","123456");

    }

    //登录业务
    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);

            //查询完毕会返回一个结果集
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("=========================================");
            }

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


    }

}

使用IDEA连接数据库：

连接成功后可以查看数据库

 双击数据库

更新数据