【Web开发】JDBC数据库访问技术与MVC模型

说明:JDBC数据库访问技术与MVC模型

 一、JDBC访问技术

JDBC数据库访问技术共分为七步骤:

1、加载驱动

一般情况下,需要将MySQL的jre包放在项目的lib文件下(没有这个文件需要新建这个文件夹,并将其设置为Resource Root)

然后不同版本的MySQL所使用的驱动类是不同的。具体如下:

1、jre驱动包:
MySQL5.5:mysql-connector-java-5.1.36.jar
MySQL8.0:mysql-connector-java-8.0.11.jar

2、JDBC驱动类:
MySQL5.5:com.mysql.jdbc.Driver
MySQL8.0:com.mysql.cj.jdbc.Driver

总之加载驱动类就是一行代码:

 Class.forName("com.mysql.cj.jdbc.Driver");

2、创建连接

使用Connection对象创建数据库的连接对象:

 String user="root2"; //连接数据库用户名 String password="loginpasswd"; //连接数据库密码 String url="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=true";
    Connection connection = DriverManager.getConnection(url,user,password);

3、编写SQL

4、获取Statement对象

5、执行SQL,得到结果集

 String sql = "select * from tb_user";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();

6、处理结果集

7、关闭资源

需要关闭的资源有:

  • ResultSet
  • PreparedStatement
  • Connection
 public void colseConn(ResultSet rs,PreparedStatement pstm,Connection conn){ if(rs != null){ try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } if(pstm != null){ try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } if(conn != null){ try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

二、MVC模型下的增删改查

1、MVC结构框架

  • 首先按照下图创建各个包(Package)、类(class)、接口(Interface)

  • 其中各个包、类、接口的作用如下:
  • |—com.zhongruan包:
  • |———model包:内放置数据库同类表的实例
  • |—————user类:数据库中user表的实例
  • |———util包:工具包
  • |—————DBUtil类:实现数据库的连接与释放操作
  • |———view包:视图
  • |—————LoginUI类:显示控制台
  • |—Dao包:数据库操作包
  • |———LoginDao接口:包含增删改查的操作接口函数
  • |———daoImpl包:内含接口函数的具体实现
  • |—————LoginDaoImple包:LoginDao接口中函数的具体实现
  • |—service包:服务包
  • |———LoginServer接口:逻辑控制
  • |———serviceImple包:接口的具体实现类放在这里边
  • |—————LoginServiceImpl类:接口具体实现类


2、数据库表

3、完整代码

3.1 User.java:

package com.zhongruan.model; public class User { private int id; private String username; private String userpassword; public int getId() { return id;
    } public String getUsername() { return username;
    } public String getUserpassword() { return userpassword;
    } public void setUsername(String username) { this.username = username;
    } public void setUserpassword(String userpassword) { this.userpassword = userpassword;
    } public void setId(int id) { this.id = id;
    } public User(int id, String username, String userpassword) { this.id = id; this.username = username; this.userpassword = userpassword;
    } public User(String username, String userpassword) { this.username = username; this.userpassword = userpassword;
    } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", userpassword='" + userpassword + '\'' + '}';
    }
} 


3.2 DBUtil.java:

package com.zhongruan.util; import java.sql.*; public class DBUtil { public Connection getConn(){

        String user="root"; //连接数据库用户名 String password="123456"; //连接数据库密码 String driverName="com.mysql.jdbc.Driver"; //数据库驱动 String url="jdbc:mysql://127.0.0.1:3306/db_jdbc?characterEncoding=utf8&useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=true";

        Connection conn = null; try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url,user,password); //System.out.println("数据库连接成功!!!"); return conn;
        } catch (Exception e) {
            e.printStackTrace();
        } return conn;
    } public void colseConn(ResultSet rs,PreparedStatement pstm,Connection conn){ if(rs != null){ try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } if(pstm != null){ try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } if(conn != null){ try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}


3.3 LoginDao.java:

package dao; import com.zhongruan.model.User; public interface LoginDao { boolean doLogin(User loginUser); boolean insertUser(User user); boolean deleteUser(int id); boolean updateUser(User user);
}


3.4 LoginDaoImpl.java:

package dao.daoImpl; import com.zhongruan.model.User; import com.zhongruan.util.DBUtil; import dao.LoginDao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class LoginDaoImpl implements LoginDao { /**
     * 用户登录Dao
     * @param loginUser
     * @return */ @Override public boolean doLogin(User loginUser) {

        Connection conn = new DBUtil().getConn();
        ResultSet rs = null;
        PreparedStatement pstm = null;

        String sql = "select count(*) as num from tb_user where username=? and userpassword=?"; //String sql = "select count(*) from tb_user where username=? and password=?"; try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,loginUser.getUsername());
            pstm.setString(2,loginUser.getUserpassword()); //四种数据库语言 rs = pstm.executeQuery(); while(rs.next()){ int num = rs.getInt("num"); if(num>0){ return true;
                } else{ return false;
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally { new DBUtil().colseConn(rs,pstm,conn);
        } return false;
    } /**
     *
     * 用户添加Dao
     *
     * @param user
     * @return */ @Override public boolean insertUser(User user) { int num=0;
        Connection conn = new DBUtil().getConn();
        PreparedStatement pstm = null;
        ResultSet rs = null;

        String sql0 = "select MAX(id) from tb_user";
        String sql = "insert into tb_user value(?,?,?)"; try {
            pstm = conn.prepareStatement(sql0);
            rs = pstm.executeQuery(); while (rs.next()){
                num = rs.getInt(1) + 1;
            }

            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,num);
            pstm.setString(2,user.getUsername());
            pstm.setString(3,user.getUserpassword());
            pstm.executeUpdate(); return true;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally { new DBUtil().colseConn(rs,pstm,conn);

        } return false;
    } @Override public boolean deleteUser(int id) {
        Connection conn = new DBUtil().getConn();
        PreparedStatement pstm = null;

        String sql = "delete from tb_user where id=?"; try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id); int temp =  pstm.executeUpdate(); if(temp!=0){ return true;
            } else{ return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally { new DBUtil().colseConn(null,pstm,conn);
        } return false;
    } @Override public boolean updateUser(User user) {
        Connection conn = new DBUtil().getConn();
        PreparedStatement pstm = null;

        String sql = "update tb_user set username=?,userpassword=? where id=?"; try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,user.getUsername());
            pstm.setString(2,user.getUserpassword());
            pstm.setInt(3,user.getId()); int temp1 = pstm.executeUpdate(); if(temp1!=0){ return true;
            } else{ return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally { new DBUtil().colseConn(null,pstm,conn);
        } return false;
    }
}


3.5 LoginService.java:

package service; import com.zhongruan.model.User; public interface LoginService { boolean login(User loginuser); boolean insert(User user); boolean delete(int id); boolean update(User user);
}


3.6 LoginServiceImpl.java:

package service.serviceImpl; import com.zhongruan.model.User; import dao.LoginDao; import dao.daoImpl.LoginDaoImpl; import service.LoginService; public class LoginServiceImpl implements LoginService {
    LoginDao loginDao = new LoginDaoImpl(); @Override public boolean login(User loginuser) { return loginDao.doLogin(loginuser);
    } @Override public boolean insert(User user) { return loginDao.insertUser(user);
    } @Override public boolean delete(int id) { return loginDao.deleteUser(id);
    } @Override public boolean update(User user) { return loginDao.updateUser(user);
    }
}


3.7 loginUI.java:

package com.zhongruan.view; import com.zhongruan.model.User; import dao.LoginDao; import dao.daoImpl.LoginDaoImpl; import service.LoginService; import service.serviceImpl.LoginServiceImpl; import java.util.Scanner; public class loginUI { public static Scanner sc = new Scanner(System.in); public static void main(String[] args) { int id = 0;
        String username = "";
        String userpassword = "";
        User user = null; boolean b = false;
        LoginService loginService = new LoginServiceImpl(); while(true){
            System.out.println("----菜单----");
            System.out.println("1:用户登录(查)");
            System.out.println("2:添加用户(增)");
            System.out.println("3:删除用户(删)");
            System.out.println("4:修改用户(改)");
            System.out.println("请输入选项:"); int choice = sc.nextInt(); switch (choice){ case 1:
                    System.out.println("请输入用户名:");
                    username = sc.next();
                    System.out.println("请输入用户密码:");
                    userpassword = sc.next();

                    user = new User(username,userpassword);

                    b = loginService.login(user); if(b){
                        System.out.println(username+"登录成功!!");
                    }else{
                        System.out.println("登录失败,用户名不存在或者密码错误!!");
                    } break; case 2:
                    System.out.println("请输入新用户名:");
                    username = sc.next();
                    System.out.println("请输入新用户密码:");
                    userpassword = sc.next();

                    user = new User(username,userpassword);

                    b = loginService.insert(user); if(b){
                        System.out.println(username+"添加成功!!");
                    }else{
                        System.out.println("添加失败!");
                    } break; case 3:
                    System.out.println("请输入要删除的用户ID:");
                    id = sc.nextInt();

                    b = loginService.delete(id); if(b){
                        System.out.println(id+"删除成功!!");
                    }else{
                        System.out.println("删除失败!可能该用户ID不存在!");
                    } break; case 4:
                    System.out.println("请输入要修改的用户ID:");
                    id = sc.nextInt();
                    System.out.println("请输入新用户名:");
                    username = sc.next();
                    System.out.println("请输入新用户密码:");
                    userpassword = sc.next();

                    user = new User(id,username,userpassword);

                    b = loginService.update(user); if(b){
                        System.out.println(id+"修改成功!!");
                    }else{
                        System.out.println("修改失败!可能是用户名不存在!");
                    } break;

            }
        }
    }
}


4、运行结果截图:

4.1 查询成功:

4.2 查询失败:

4.3  插入成功:

成功插入到数据库数据库表中:

4.4 删除成功与失败(未找到请求删除的记录):

4.5 修改成功与失败(请求修改的记录不存在):


至此,关键JDBC的增删改查基本操作的实现结束!!!


相关推荐

发表评论

路人甲
看不清楚?点图切换

网友评论(0)