2021-07-18

statement预处理语句对象

作用：

• 可以避免SQL注入攻击
• SQL注入：由于我们用户输入改变了程序中sql语句的本意
• 提高SQL语句在程序中的可读性
• 提高jdbc中同一条sql语句（装载参数）多次执行的效率

*使用

• List item
• 使用占位符？SQL语句需要拼接的时候
• delete from t_user where id=?
• 实例化：conn.preparedStatement();

编码步骤

• 1.建立连接
• 2.sql语句
• 3.设置参数----装载占位符
  PreparedStatement ps = conn.prepareStatement(sql);----对sql语句设置参数
  ps.setString(1, username);
  ps.setString(2, password);-----------------按顺序装载占位符
  ps.executeUpdate();
  ResultSet rs = ps.executeQuery();----------------发送sql语句
  （当返回值为结果集时）ResultSet rs = ps.executeQuery();-----------返回结果集
• 4.关闭连接

账号管理模块

保存信息（INSERT/UPDATE）

sql语句：
INSERT INTO t_user(username,password,email,phone,image,gender,created_user,created_time,modified_user,modified_time) VALUES(?,?,?,?,null,2,‘2’,‘2021-01-01’,‘system’,NOW());
发送sql语句：
ps.executeUpdate();

用户登录（SELECT/QUERY）

sql语句：
SELECT * FROM t_user where username=? and password =?
发送sql语句：
ResultSet rs = ps.executeQuery();-----结果集
if (rs.next()) {}----------------------若结果集有内容则……

修改密码（UPDATE/UPDATE）

sql语句：
UPDATE t_user SET password=? WHERE username=?
发送sql语句：
ps.executeUpdate();

将三个方法的代码放在UserDao中

package cn.aust.shopping.dao;

import cn.aust.jdbc.JDBCUtil;
import cn.aust.shopping.entity.User;

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


/**
 * 账号管理模块
 */
public class UserDAO {
    /**
     * 保存信息
     */
    public void save(User user) {
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            //2.SQL
            String sql = "INSERT INTO t_user(username,password,email,phone,image,gender,created_user,created_time,modified_user,modified_time) " +
                    "         VALUES(?,?,?,?,null,2,'2','2021-01-01','system',NOW()); ";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            ps.setString(4, user.getPhone());
            ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.release(conn);
        }

    }

    public void login(User user)  throws  Exception{
        /**
         * 用户登录
         */
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            String sql = "SELECT * FROM t_user where username=? and password =?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //2.装载占位符
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println("登录成功！！！");
            } else {
                System.out.println("账号或密码错误！！！");
            }
        }finally {
            JDBCUtil.release(conn);
        }
    }

    public void modify_password(User user)  throws  Exception{
        /**
         * 修改密码
         */
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            String sql = "UPDATE t_user SET password=? WHERE username=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //2.装载占位符
            ps.setString(1, user.getPassword());
            ps.setString(2, user.getUsername());
            int row = ps.executeUpdate();
            if (row > 0) {
                System.out.println("修改密码成功！！！");
            } else {
                System.out.println("修改密码失败！！！");
            }
        } finally {
            JDBCUtil.release(conn);
        }
    }

    public void modify_phone(User user)  throws  Exception{
        /**
         * 修改手机号
         */
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            String sql = "UPDATE t_user SET phone=? WHERE username=? and password=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //2.装载占位符
            ps.setString(1, user.getPhone());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            int row = ps.executeUpdate();
            if (row > 0) {
                System.out.println("修改手机号成功！！！");
            } else {
                System.out.println("修改手机号失败！！！");
            }
        } finally {
            JDBCUtil.release(conn);
        }
    }

    public void modify_email(User user)  throws  Exception{

        /**
         * 修改手机号
         */
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            String sql = "UPDATE t_user SET email=? WHERE username=? and password=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //2.装载占位符
            ps.setString(1, user.getEmail());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            int row = ps.executeUpdate();
            if (row > 0) {
                System.out.println("修改邮箱成功！！！");
            } else {
                System.out.println("修改邮箱失败！！！");
            }
        } finally {
            JDBCUtil.release(conn);
        }
    }

    public void query_username(User user)  throws  Exception{
        /**
         * 查询账号是否被占用
         */
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            String sql = "SELECT * FROM t_user WHERE username=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //2.装载占位符
            ps.setString(1, user.getUsername());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println("账号已存在！！！");
            } else {

            }
        }  finally {
            JDBCUtil.release(conn);
        }
    }

    public void query_email(User user) throws  Exception{
        /**
         * 查询邮箱是否已存在
         */
        Connection conn = null;
        try {
            //1.
            conn = JDBCUtil.getConn();
            String sql = "SELECT * FROM t_user WHERE email=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            //2.装载占位符
            ps.setString(1, user.getEmail());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println("该邮箱已被注册！！！");
            } else {

            }
        } finally {
            JDBCUtil.release(conn);
        }
    }


    public void query_phone(User user)  throws  Exception{
        /**
         * 查询手机号是否已存在
         */
            Connection conn = null;
            try {
                //1.
                conn = JDBCUtil.getConn();
                String sql = "SELECT * FROM t_user WHERE phone=?";
                PreparedStatement ps = conn.prepareStatement(sql);
                //2.装载占位符
                ps.setString(1, user.getPhone());
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    System.out.println("手机号已被注册！！！");
                } else {

                }
            } finally {
                JDBCUtil.release(conn);
            }
        }
}