JDBC自学笔记002_Real

JDBC自学笔记002_Real

一、练习，登录界面实现

package com.bjpowernode.jdbc.LoginStudy;

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

public class Login {
    public static void main(String[] args) {
        //初始化一个界面
        Map<String, String> userLoginInfo = initUI();
        //验证密码与登录
        boolean loginSuccess = login(userLoginInfo);
        //输出最后的结果
        System.out.println(loginSuccess ? "登陆成功": "登陆失败");
    }

    private static boolean login(Map<String, String> userLoginInfo) {
        //JDBC代码
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        boolean loginsuccess = false;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/rapiddesignsystemdatabase", "root", "123456");
            stmt = conn.createStatement();
            String sql = "select id, password from t_user where id = '"+userLoginInfo.get("loginName")+"' and password = '"+userLoginInfo.get("loginPwd")+"'";
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                loginsuccess = true;
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if(rs != null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try{
                    conn.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(stmt != null){
                try{
                    stmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return loginsuccess;
    }

    private static Map<String,String> initUI() {
        Scanner s = new Scanner(System.in);
        System.out.println("用户名：");
        String loginName = s.nextLine();

        System.out.println("密码：");
        String loginPwd = s.nextLine();

        Map<String, String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName", loginName);
        userLoginInfo.put("loginPwd", loginPwd);

        return userLoginInfo;
    }
}

二、SQL注入问题的解决

核心方法：只要不让用户参与SQL语句的编译过程即可

利用PreparedStatement类，他继承了Stsatement类

package com.bjpowernode.jdbc.LoginStudy;

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

public class Login {
    public static void main(String[] args) {
        //初始化一个界面
        Map<String, String> userLoginInfo = initUI();
        //验证密码与登录
        boolean loginSuccess = login(userLoginInfo);
        //输出最后的结果
        System.out.println(loginSuccess ? "登陆成功": "登陆失败");
    }

    private static boolean login(Map<String, String> userLoginInfo) {
        //单独定义的变量
        String loginName = userLoginInfo.get("loginName");
        String loginPwd = userLoginInfo.get("loginPwd");

        //JDBC代码
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        boolean loginsuccess = false;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/rapiddesignsystemdatabase", "root", "123456");
            //获取预编译的数据库操作对象
            //SQL语句的框，其中一个？是占位符，不能再占位符两侧加引号
            String sql = "select id, password from t_user where id = ? and password = ?";
            ps = conn.prepareStatement(sql);
            //给占位传值，第一个标识是1 第二个标识是2 JDBC从1开始
            ps.setString(1,  loginName);
            ps.setString(2, loginPwd);
            rs = ps.executeQuery();
            if(rs.next()){
                loginsuccess = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(rs != null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try{
                    conn.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try{
                    ps.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return loginsuccess;
    }

    private static Map<String,String> initUI() {
        Scanner s = new Scanner(System.in);
        System.out.println("用户名：");
        String loginName = s.nextLine();

        System.out.println("密码：");
        String loginPwd = s.nextLine();

        Map<String, String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName", loginName);
        userLoginInfo.put("loginPwd", loginPwd);

        return userLoginInfo;
    }
}

三、JDBC自动提交事务机制

出现的问题：JDBC执行SQL语句会执行一条语句就提交一次，如果两个语句之间出现错误，会导致第二条sql语句无法执行，所以有时会出现一些系统错误，所以需要保证所有的sql语句要么一起执行成功，要么一起执行失败。

重点的三行代码：

conn.setAtuoCommit(false); //将JDBC自动提交机制关闭
conn.commit(); //全部sql语句执行完毕后，再次提交
conn.rollback(); // 如果出现错误，需要再catch中进行conn回滚

package com.bjpowernode.jdbc;

import java.sql.*;

public class JdbcTest08 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2、建立连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/rapiddesignsystemdatabase", "root", "123456");

            //将自动提交机制手动关闭/
            conn.setAutoCommit(false);

            //3、获取预编译的数据库操作对象
            String sql = "update t_user set uname = ? where id = ?";
            ps = conn.prepareStatement(sql);
            //给？传值
            ps.setString(1, "南理工");
            ps.setString(2, "120101010027");
            int count = ps.executeUpdate();

            //String s = null;
            //s.toString();

            //给？传值
            ps.setString(1, "TATA");
            ps.setString(2, "1002");

            //4、进行sql语句执行
            count += ps.executeUpdate();
            //5、查询结果处理
            System.out.println(count == 2 ? "提交成功" : "提交失败");

            //所有sql语句执行完毕后再全部提交 /
            conn.commit();
        } catch (Exception e) {
            if(conn != null){
                try {
                    conn.rollback(); //如果发生错误，不能只想完毕，就进行conn回滚 /
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            //6、释放资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}