美文网首页
Java EE之旅13-JDBC(下)

Java EE之旅13-JDBC(下)

作者: 小楠总 | 来源:发表于2018-01-31 09:51 被阅读34次

DBUtils的封装

1.1 创建配置文件

    在src目录下面创建一个文件为:dbinfo.properties
    相关内容为:
        driverClass=com.mysql.jdbc.Driver
        url=jdbc:mysql:///demo
        username=root
        password=123456

1.2 DBUtils的封装

    主要的工作是:
        1. 加载配置文件,初始化些参数,注册数据库驱动
        2. 封装一个得到连接的方法
        3. 封装一个安全关闭资源的方法
    示例如下:

        public class DBUtils {
            private static String driverClass;
            private static String url;
            private static String username;
            private static String password;
        
            static {
                //此对象是用于加载properties文件数据的
                ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
                driverClass = rb.getString("driverClass");
                url = rb.getString("url");
                username = rb.getString("username");
                password = rb.getString("password");
                try {
                    Class.forName(driverClass);
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
        
            //得到连接的方法
            public static Connection getConnection() throws Exception {
                return DriverManager.getConnection(url, username, password);
            }
        
            //关闭资源的方法
            public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
                //关闭资源
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    rs = null;
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    stmt = null;
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    conn = null;
                }
            }
        }

使用DBUtils实现增删改查


    2.1 为了方便,我们需要封装一个Bean类:
    
        public class User {
        
            private int id;
            private String name;
            private String email;
            private String password;
        
            public User(int id, String name, String email, String password) {
                this.id = id;
                this.name = name;
                this.email = email;
                this.password = password;
            }
        
            public User() {
            }
        
            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 String getEmail() {
                return email;
            }
        
            public void setEmail(String email) {
                this.email = email;
            }
        
            public String getPassword() {
                return password;
            }
        
            public void setPassword(String password) {
                this.password = password;
            }
        
            @Override
            public String toString() {
                return "User{" +
                        "id=" + id +
                        ", name='" + name + '\'' +
                        ", email='" + email + '\'' +
                        ", password='" + password + '\'' +
                        '}';
            }
        }

    2.2 这里用JUnit单元测试框架进行测试,相关的使用可以自行百度,4个方法分别代表增删改查的示例:

        @Test
        public void insert() throws Exception {
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO  users(id,name,email,password) VALUE(?,?,?,?)");
            User user = new User(2, "wuhuannan", "123@email.com", "321");
            stmt.setInt(1, user.getId());
            stmt.setString(2, user.getName());
            stmt.setString(3, user.getEmail());
            stmt.setString(4, user.getPassword());
    
            int num = stmt.executeUpdate();
            if (num > 0) {
                System.out.println("success");
            }
            DBUtils.closeAll(null, stmt, conn);
        }
    
        @Test
        public void delete() throws Exception {
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement("DELETE FROM users WHERE id = ?");
            stmt.setInt(1, 1);
    
            int num = stmt.executeUpdate();
            if (num > 0) {
                System.out.println("success");
            }
            DBUtils.closeAll(null, stmt, conn);
        }
    
        @Test
        public void update() throws Exception {
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement("UPDATE users SET name = ? WHERE id = ?");
            stmt.setString(1, "nan");
            stmt.setInt(2, 1);
    
            int num = stmt.executeUpdate();
            if (num > 0) {
                System.out.println("success");
            }
            DBUtils.closeAll(null, stmt, conn);
        }
    
        @Test
        public void query() throws Exception {
            Connection conn = DBUtils.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet res = stmt.executeQuery("SELECT * FROM users");
            List<User> users = new ArrayList<>();
            while (res.next()) {
                User user = new User();
                user.setId(res.getInt("id"));
                user.setEmail(res.getString("email"));
                user.setName(res.getString("name"));
                user.setPassword(res.getString("password"));
                users.add(user);
            }
            System.out.println(Arrays.asList(users));
            DBUtils.closeAll(res, stmt, conn);
        }

实现一个简单的用户登录功能

核心逻辑:通过传入的用户名和密码,利用DBUtils查找User,如果查找有值则登录成功,否则登录失败。

相关的类图如下:

image.png

首先,我们先封装一个DoLogin类专门用于处理用户登录的业务逻辑:

    public class DoLogin {
    
        public static User findUser(String name, String password) {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet res = null;
            User user = null;
            try {
                conn = DBUtils.getConnection();
                stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ? AND password = ?");
                stmt.setString(1, name);
                stmt.setString(2, password);
    
                res = stmt.executeQuery();
                if (res.next()) {
                    user = new User();
                    user.setId(res.getInt("id"));
                    user.setEmail(res.getString("email"));
                    user.setName(res.getString("name"));
                    user.setPassword(res.getString("password"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                DBUtils.closeAll(res, stmt, conn);
            }
            return user;
        }
    }

然后,进行测试:

    public class Client {
    
        public static void main(String[] args) {
    
            String name = "wuhuannan";
            String password = "321";
    
            User user = DoLogin.findUser(name, password);
            if (user != null) {
                System.out.println("登陆成功!");
            } else {
                System.out.println("登陆失败!");
            }
    
        }
    
    }

SQL注入问题

当SQL语句需要传入参数的时候,使用PreparedStatement代替Statement,就可以防止SQL注入问题(参数被传入SQL语句):


stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ? AND password = ?");
stmt.setString(1, name);
stmt.setString(2, password);
res = stmt.executeQuery();

PreparedStatement:预编译对象, 是Statement对象的子类

PreparedStatement特点:

  • 性能要比Statement高
  • 会把sql语句先编译
  • sql语句中的参数会发生变化,过滤掉用户输入的关键字

相关文章

网友评论

      本文标题:Java EE之旅13-JDBC(下)

      本文链接:https://www.haomeiwen.com/subject/tmfogxtx.html