【2019.10.13】Java 抽取JDBC工具类，实现登录案例

不念不忘少年蓝@ 2023-06-05 08:50 58阅读 0赞

#### 工具类 ####

*  JDBCUtils.java

package ybs.utils;
    
    import java.io.FileReader;
    import java.io.IOException;
    import java.net.URL;
    import java.sql.*;
    import java.util.Properties;
    
    /* JDBC工具类 */
    public class JDBCUtils { 
        private static String url;
        private static String user;
        private static String password;
        private static String driver;
        /** * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块 */
        static { 
            // 读取资源文件，获取值
    
            try { 
                // 1. Properties 集合类
                Properties pro = new Properties();
                // 2. 加载文件
                // 获取src路径下的文件的方式 ——--> ClassLoader 类加载器
                ClassLoader classLoader = JDBCUtils.class.getClassLoader();
                URL resource = classLoader.getResource("jdbc.properties");
                String path = resource.getPath();
                System.out.println(path);
                pro.load(new FileReader(path));
                // 3. 获取属性赋值
                url = pro.getProperty("url");
                user = pro.getProperty("user");
                password = pro.getProperty("password");
                driver = pro.getProperty("driver");
    
                Class.forName(driver);
            } catch (IOException | ClassNotFoundException e) { 
                e.printStackTrace();
            }
        }
        /** * 获取连接 * 使用配置文件 * @return 连接对象 */
        public static Connection getConnection() throws SQLException { 
            //DriverManager.getConnection("jdbc:mysql:///db3?serverTimezone=UTC", "root", "mima");
            return DriverManager.getConnection(url, user, password);
        }
    
        /** * 释放资源 * @param stmt * @param conn */
        public static void close(Statement stmt, Connection conn){ 
            if(stmt != null){ 
                try { 
                    stmt.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
            if(conn != null){ 
                try { 
                    conn.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    
        public static void close(ResultSet rs, Statement stmt, Connection conn){ 
            if(rs != null){ 
                try { 
                    rs.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
            if(stmt != null){ 
                try { 
                    stmt.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
            if(conn != null){ 
                try { 
                    conn.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

#### 配置文件 ####

*  jdbc.properties

url=jdbc:mysql:///db3?serverTimezone=UTC
    user=root
    password=mima
    driver=com.mysql.cj.jdbc.Driver

# 测试类（使用） #

*  JdbcDemo4.java

// 导入JDBCUtils工具类
    import ybs.utils.JDBCUtils;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    public class JdbcDemo4 { 
        public List<Account> findAll(){ 
            Connection conn = null;
            Statement statement = null;
            ResultSet rs = null;
            List<Account> list = null;
            try { 
                conn = JDBCUtils.getConnection();
                String sql = "select * from account";
                statement = conn.createStatement();
                rs = statement.executeQuery(sql);
                Account account = null;
                list = new ArrayList<Account>();
                while (rs.next()){ 
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int balance = rs.getInt("balance");
                    account = new Account();
                    account.setId(id);
                    account.setName(name);
                    account.setBalance(balance);
                    list.add(account);
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                JDBCUtils.close(rs, statement, conn);
            }
            return list;
        }
    
        public static void main(String[] args) { 
            List<Account> list = new JdbcDemo4().findAll();
            System.out.println(list);
            System.out.println(list.size());
        }
    }

#### 将数据库表映射到类 ####

*  Account .java

package ybs.jdbc;
    
    public class Account { 
        private int id;
        private String name;
        private int balance;
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getName() { 
            return name;
        }
    
        public void setName(String name) { 
            this.name = name;
        }
    
        public int getBalance() { 
            return balance;
        }
    
        public void setBalance(int balance) { 
            this.balance = balance;
        }
    
        @Override
        public String toString() { 
            return "Account{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", balance=" + balance +
                    '}';
        }
    }

### 使用JDBCUtils工具类进行登录联系 ###

> 需求：  
> 1、通过键盘录入用户名和密码  
> 2、判断用户名是否登录成功

##### jdbc登录案例 #####

*  创建数据库

create table user(
        id int primary key auto_increment,
        username varchar(20),
        password varchar(20)
    );
    
    insert into user values(null,'zhangsan','123');
    insert into user values(null,'lisi','456');
    insert into user values(null,'paulson','789');

package ybs.jdbc;
    
    import ybs.utils.JDBCUtils;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    /** * 需求：通过键盘录入用户名和密码，判断用户名是否登录成功 */
    public class JdbcDemoLogin { 
    
        public static void main(String[] args) { 
            // 1. 键盘录入
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入用户名：");
            String username = sc.nextLine();
            System.out.println("请输入密码：");
            String password = sc.nextLine();
    
            // 2. 调用方法
            boolean flag = new JdbcDemoLogin().login(username, password);
            if(flag)
                System.out.println("登录成功");
            else
                System.out.println("用户名或密码错误");
            // 3. 判断结果
        }
    
    
        public boolean login(String username, String password){ 
            if(username == null || password == null){ 
                return false;
            }
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try { 
                conn = JDBCUtils.getConnection();
                String sql = "select * from user where username = '"+username+"' and password = '" + password +"'";
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                return rs.next();
    
            } catch (SQLException e) { 
                e.printStackTrace();
            }finally { 
                JDBCUtils.close(rs, stmt, conn);
            }
            return false;
        }
    }

但是此登录方法有被SQL注入的风险，我们不能直接用字符串去拼接SQL语句，使用sun公司提供的PreparedStatement 方法来执行SQL

public boolean login2(String username, String password){ 
            if(username == null || password == null){ 
                return false;
            }
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            try { 
                conn = JDBCUtils.getConnection();
                String sql = "select * from user where username = ? and password = ?";
                // 执行查询，不需要传参
                pstmt = conn.prepareStatement(sql);
                // 给？赋值
                pstmt.setString(1, username);
                pstmt.setString(2, password);
                rs = pstmt.executeQuery();
                return rs.next();
    
            } catch (SQLException e) { 
                e.printStackTrace();
            }finally { 
                JDBCUtils.close(rs, pstmt, conn);
            }
            return false;
        }