JDBC登录功能实现

模拟淘宝登录

package com.bjsxt.jdbc2;

import com.bjsxt.entity.User;
import entity.Emp;

import java.nio.file.attribute.UserDefinedFileAttributeView;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * 模拟淘宝登录的功能
 *
 *
 */
public class TestLogin {
    /**
     * 前台
     * @param args
     */
    public static void main(String[] args) {
        //1.输入用户名和密码
        Scanner input = new Scanner(System.in);
        System.out.println("请输入用户名");
        String userId = input.next();
        System.out.println("请输入密码");
        String password = input.next();

        //2.调用后台判断登录是否成功并返回结果给前台
        User user  = login(userId,password);

        //3.在前台输入结果
        if(user == null){
            System.out.println("登录失败");
        }else{
            System.out.println("欢迎您："+user.getRealName());
        }
    }

    public static User login(String userId,String pwd){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        User user2 = null; //默认登录失败
        try{
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user = "root";
            String password = "root";
            //1.加载驱动
            Class.forName(driver);

            //2.和数据库建立连接
            conn = DriverManager.getConnection(url, user, password);
            //3.创建一个SQL命令发送器（手枪）
            stmt = conn.createStatement();
            //4.准备一个SQL命令，并使用SQL命令发送器发送过去，并返回结果
            String sql = "select * from t_user where userid = '"+userId+
                    "' and password='"+pwd+"'";
            System.out.println(sql);
            rs = stmt.executeQuery(sql);
            //5.处理结果(将ResultSet的数据封装到一个List中)
            if(rs.next()){
                //获取当前行各个字段的值

                //String userId = rs.getString("userid");
                String realName = rs.getString("REALNAME");
                //String password = rs.getString("password");
                double money = rs.getDouble("money");
                //将当前行各个字段的值封装到一个Emp对象中
                user2 = new User(userId,realName,pwd,money);
            }
        }catch (SQLException e){
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            //6.关闭资源
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(stmt != null){
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return user2;

    }


}

缺点：有sql注入风险
解决：使用PreparedStatement

package com.bjsxt.jdbc2;

import com.bjsxt.entity.User;

import java.sql.*;
import java.util.Scanner;

/**
 * 模拟淘宝登录的功能
 *
 * 关系
 * public interface PreparedStatement extends Statement
 *
 *
 * PreparedStatement优点
 * 1.安全性高  避免SQL注入 因为不是基于字符串拼接
 * 2.代码不繁琐，可读性高。不用进行复杂的字符串拼接
 * 3.性能高
 *
 */
public class TestLogin2 {
    /**
     * 前台
     * @param args
     */
    public static void main(String[] args) {
        //1.输入用户名和密码
        Scanner input = new Scanner(System.in);
        System.out.println("请输入用户名");
        String userId = input.next();
        System.out.println("请输入密码");
        String password = input.next();

        //2.调用后台判断登录是否成功并返回结果给前台
        User user  = login(userId,password);

        //3.在前台输入结果
        if(user == null){
            System.out.println("登录失败");
        }else{
            System.out.println("欢迎您："+user.getRealName());
        }
    }

    public static User login(String userId,String pwd){
        Connection conn = null;
        //Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        User user2 = null; //默认登录失败
        try{
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user = "root";
            String password = "root";
            //1.加载驱动
            Class.forName(driver);

            //2.和数据库建立连接
            conn = DriverManager.getConnection(url, user, password);
            //3.创建一个SQL命令发送器（手枪）
            //stmt = conn.createStatement();
            String sql = "select * from t_user where userid =? and password=?";//? 占位符
            pstmt = conn.prepareStatement(sql);
            //4.准备一个SQL命令，并使用SQL命令发送器发送过去，并返回结果
            //System.out.println(sql);
            //rs = stmt.executeQuery(sql);
            pstmt.setString(1, userId);//  从1开始
            pstmt.setString(2, pwd);
            rs = pstmt.executeQuery();
            //5.处理结果(将ResultSet的数据封装到一个List中)
            if(rs.next()){
                //获取当前行各个字段的值

                //String userId = rs.getString("userid");
                String realName = rs.getString("REALNAME");
                //String password = rs.getString("password");
                double money = rs.getDouble("money");
                //将当前行各个字段的值封装到一个Emp对象中
                user2 = new User(userId,realName,pwd,money);
            }
        }catch (SQLException e){
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            //6.关闭资源
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(pstmt != null){
                    pstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return user2;

    }


}

事务管理

package com.bjsxt.jdbc2;

import java.sql.*;

/**
 * 银行转账
 */
public class TestTransaction {
    public static void main(String[] args){
        Connection conn = null;
        CallableStatement cst;
        try{
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user = "root";
            String password = "root";
            //1.加载驱动
            //Class.forName(driver);

            //2.和数据库建立连接
            conn = DriverManager.getConnection(url, user, password);

            //3.创建一个SQL命令发送器（手枪）
            Statement stmt = conn.createStatement();

            //设置事务不再自动提交
            conn.setAutoCommit(false);

            //4.准备一个SQL命令，并使用SQL命令发送器发送过去，并返回结果
            stmt.executeUpdate("update t_user set money = money - 2000 where userid = 'lisi'");

            //stmt.executeUpdate("update t_user set money = money1 + 2000 where userid = 'zhangsan'");
            stmt.executeUpdate("update t_user set money = money + 2000 where userid = 'zhangsan'");
            //事务成功结束
            conn.commit();
        }catch (SQLException e){
            e.printStackTrace();

            //事务失败结束
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //6.关闭资源
//            stmt.close();
//            conn.close();
        }
    }
}