MySQL——PreparedStatement对象

PreparedStatement防止sql注入的本质：把传进来的参数当做字符

• 增

package com.wen.lesson03;

import com.wen.lesson02.utils.JdbcUtils;

import java.sql.*;
import java.util.Date;

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, "xiaoming"); //name
            st.setString(3, "123456"); //password
            st.setString(4, "123456789@qq.com"); //email
            //注意点： sql.Date    数据库  java.sql.Date
            //        util.Date  Java   new Date().getTime()  获得时间戳
            st.setDate(5, new java.sql.Date(new Date().getTime())); //birthday

            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }

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

• 删

package com.wen.lesson03;

import com.wen.lesson02.utils.JdbcUtils;

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

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,4); //id

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

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

• 改

package com.wen.lesson03;

import com.wen.lesson02.utils.JdbcUtils;

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

public class TetsUpdate {
    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`=?,`email`=? WHERE `id`=?";
            st = conn.prepareStatement(sql); //预编译sql，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1,"张三");
            st.setString(2, "123456789@qq.com");
            st.setInt(3, 2);

            int i = st.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }


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

• 查

package com.wen.lesson03;

import com.wen.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";

            st = conn.prepareStatement(sql);

            rs = st.executeQuery();
            while (rs.next()){
                System.out.println("id="+rs.getString("id"));
                System.out.println("name="+rs.getString("NAME"));
                System.out.println("password="+rs.getString("PASSWORD"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birth="+rs.getString("birthday"));
                System.out.println("=========================");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

}