面向对象的思想——操作数据库

1 、面向对象的思想操作数据库

数据库与java的对应关系：

java	数据库
类	表
属性类型	列类型
属性名	列名
对象	记录

分层思想：单人单职

• bean：实体类，与数据表的属性、字段对应
• dao：数据访问层，与数据库交互
• view：视图层，与用户交互
• service：业务逻辑层

增删改方法代码相同，只有SQL语句及SQL语句中的占位符？赋值不同

2、分层案例

该案例，由于业务层过于简单，就不再进行封装，同时，后面使用JdbcUtils封装通用SQL（CURD基本操作）

分层小案例：
users：用户表，用户登录后操作员工表
emps：员工表

包结构：

2、1创建java工程并导入jar包

2、2 创建JavaBean【对应数据库】

User类：

public class User {
    private String username;
    private String password;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

Employee类：

public class Employee {
    private int id;
    private String empName;
    private int empAge;
    private String empSal;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public int getEmpAge() {
        return empAge;
    }

    public void setEmpAge(int empAge) {
        this.empAge = empAge;
    }

    public String getEmpSal() {
        return empSal;
    }

    public void setEmpSal(String empSal) {
        this.empSal = empSal;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", empName='" + empName + '\'' +
                ", empAge=" + empAge +
                ", empSal='" + empSal + '\'' +
                '}';
    }
}

创建JdbcUtils类，用来封装基本的JDBC操作，简化代码

import java.sql.*;

public class JdbcUtils {
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/open?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true ";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    //静态加载驱动
    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return connection;
    }

    /**
     * 关闭操作
     * @param connection
     * @param pstmt
     * @param rs
     */
    public static void close(Connection connection, PreparedStatement pstmt, ResultSet rs){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt != null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 通用sql方法
     * @param sql
     * @param params
     */
    public static int executeUpdate(String sql, Object...params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; ++i) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            int rows = pstmt.executeUpdate();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, pstmt, null);
        }
        return -1;
    }

}

2、3 创建业务入口【view层】

import com.ali.bean.Employee;
import com.ali.bean.User;
import com.ali.dao.EmployeeDao;
import com.ali.dao.UserDao;
import com.ali.dao.impl.EmployeeDaoImpl;
import com.ali.dao.impl.UserDaoImpl;

import java.util.List;
import java.util.Scanner;

public class MyViews {
    Scanner sc = new Scanner(System.in);
    UserDao userDao = new UserDaoImpl();
    EmployeeDao employeeDao = new EmployeeDaoImpl();
    /**
     * 主菜单，登录入口
     */
    public void mainmenu(){
        System.out.println("1、登录\t 2、注册\t 0、退出");
        System.out.print("请选择：");
        int choose = sc.nextInt();
        switch (choose){
            case 1:
                login();
                break;
            case 2:
                register();
                break;
            case 0:
                System.out.println("谢谢使用!");
                break;
        }
    }

    public void menu(){
        System.out.println("1、查看员工信息\t2、添加信息\t3、修改信息\t4、删除信息\t0、返回");
        int choose = sc.nextInt();
        switch (choose){
            case 1:
                show();
                menu();
                break;
            case 2:
                addEmp();
                menu();
                break;
            case 3:
                updateEmp();
                menu();
                break;
            case 4:
                deleteEmp();
                menu();
                break;
            case 0:
                mainmenu();
                break;
        }
    }

    /**
     * 展示员工信息
     */
    private void show() {
        List<Employee> employees = employeeDao.getAllEmployees();
        if(employees == null){
            System.out.println("暂无员工信息");
        }else{
            System.out.println("-------------------");
            for (Employee employee : employees) {
                System.out.println(employee);
            }
            System.out.println("-------------------");
        }
    }

    /**
     * 用户登录
     */
    public void login(){
        System.out.println("请输入用户名：");
        String username = sc.next();
        System.out.println("请输入密码：");
        String password = sc.next();
        User user = userDao.findByNameAndPassword(username, password);
        if(user == null){
            System.out.println("登录失败，用户名或密码错误");
            mainmenu();
        }else{
            System.out.println("登录成功，欢迎:"+user.getUsername());
            menu();
        }
    }

    public void register(){
        System.out.println("请输入用户名：");
        String username = sc.next();
        User user = userDao.findByName(username);
        if(user != null){
            System.out.println("注册失败,用户名已存在");
            mainmenu();
        }else {
            System.out.println("请输入密码：");
            String password = sc.next();
            int i = userDao.saveUser(username, password);
            if(i <= 0){
                System.out.println("注册失败");
                mainmenu();
            }else{
                System.out.println("注册成功");
                mainmenu();
            }
        }
    }

    /**
     * 添加员工
     */
    public void addEmp(){
        System.out.println("请输入员工名称：");
        String empName = sc.next();
        System.out.println("请输入员工年龄：");
        int empAge = sc.nextInt();
        System.out.println("请输入员工薪资：");
        String empSal = sc.next();
        boolean flag = employeeDao.saveEmployee(empName, empAge, empSal);
        if(flag){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
    }

    /**
     * 删除员工
     */
    public void deleteEmp(){
        System.out.println("请输入要删除的员工的姓名：");
        String empName = sc.next();
        Employee employee = new Employee();
        employee.setEmpName(empName);
        boolean flag = employeeDao.deleteEmployee(employee);
        if(flag){
            System.out.println("删除成功!");
        }else{
            System.out.println("删除失败");
        }
    }

    /**
     * 更新员工
     */
    public void updateEmp(){
        System.out.println("请输入要更新的员工姓名：");
        String updateName = sc.next();
        Employee employee = new Employee();
        System.out.println("请输入员工的姓名：");
        String empName = sc.next();
        System.out.println("请输入员工的年龄：");
        int empAge = sc.nextInt();
        System.out.println("请输入员工的薪资：");
        String empSal = sc.next();
        employee.setEmpName(empName);
        employee.setEmpAge(empAge);
        employee.setEmpSal(empSal);
        boolean flag = employeeDao.updateEmployee(updateName, employee);
        if(flag){
            System.out.println("更新成功");
        }else{
            System.out.println("更新失败");
        }
    }

}

2、4 创建dao层及实现类

1）UserDao：

import com.ali.bean.User;

public interface UserDao {
    //根据用户名和密码查询用户
    public User findByNameAndPassword(String username, String password);
    //根据用户名查询用户
    public User findByName(String name);
    //保存用户
    public int saveUser(String username, String password);
}

2） EmployeeDao：

import com.ali.bean.Employee;

import java.util.List;

public interface EmployeeDao {
    //查询所有员工
    public List<Employee> getAllEmployees();
    //添加员工
    public boolean saveEmployee(String empName, int empAge, String empSal);
    //更新用户
    public boolean updateEmployee(String updateName, Employee employee);
    //删除员工
    public boolean deleteEmployee(Employee employee);
}

3）UserDaoImpl：

import com.ali.bean.User;
import com.ali.dao.UserDao;
import com.ali.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    @Override
    public User findByNameAndPassword(String username, String password) {
        try {
            conn = JdbcUtils.getConnection();//已经静态加载URL，USERNAME，PASSWORD等信息
            pstmt = conn.prepareStatement("select username, password from users where username = ? and password = ?");
            pstmt.setString(1,username);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();
           if(rs.next()){
               User user = new User();
               String username1 = rs.getString("username");
               String password1 = rs.getString("password");
               user.setUsername(username1);
               user.setPassword(password1);
               return user;
           }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(conn,pstmt,rs);
        }
        return null;
    }

    @Override
    public User findByName(String name) {
        try {
            conn = JdbcUtils.getConnection();//已经静态加载URL，USERNAME，PASSWORD等信息
            pstmt = conn.prepareStatement("select username, password from users where username = ?");
            pstmt.setString(1,name);
            rs = pstmt.executeQuery();
            if(rs.next()){
                User user = new User();
                String username = rs.getString("username");
                String password = rs.getString("password");
                user.setUsername(username);
                user.setPassword(password);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(conn,pstmt,rs);
        }
        return null;
    }

    @Override
    public int saveUser(String username, String password) {
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement("insert into users (username, password) values (?, ?)");
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            int row = pstmt.executeUpdate();
            return row;//返回受影响的行数
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(conn, pstmt, rs);
        }
        return -1;
    }
}

4）EmployeeDaoImpl：

import com.ali.bean.Employee;
import com.ali.dao.EmployeeDao;
import com.ali.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    /**
     * 查询所有员工
     * @return
     */
    @Override
    public List<Employee> getAllEmployees() {
        ArrayList<Employee> employees = new ArrayList<Employee>();
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement("select empid, empname, empage, empsal from emps");
            rs = pstmt.executeQuery();
            while(rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getInt("empid"));
                employee.setEmpName(rs.getString("empname"));
                employee.setEmpAge(rs.getInt("empage"));
                employee.setEmpSal(rs.getString("empsal"));
                employees.add(employee);
            }
            return employees;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加员工
     * @param empName
     * @param empAge
     * @param empSal
     * @return
     */
    @Override
    public boolean saveEmployee(String empName, int empAge, String empSal) {
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement("insert into emps(empname, empage, empsal) values (?, ?, ?)");
            pstmt.setString(1, empName);
            pstmt.setInt(2, empAge);
            pstmt.setString(3, empSal);
            int rows = pstmt.executeUpdate();
            if(rows > 0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
        return false;
    }

    /**
     * 更新用户
     * @param updateName 要更新用户的姓名
     * @param employee
     * @return
     */
    @Override
    public boolean updateEmployee(String updateName, Employee employee) {
//        try {
//            conn = JdbcUtils.getConnection();
//            pstmt = conn.prepareStatement("update emps set empname = ? , empage = ? , empsal = ? where empname = ?");
//            pstmt.setString(1, employee.getEmpName());
//            pstmt.setInt(2, employee.getEmpAge());
//            pstmt.setString(3, employee.getEmpSal());
//            pstmt.setString(4, updateName);
//            int row = pstmt.executeUpdate();
//            return row > 0 ? true : false;
            String sql = "update emps set empname = ? , empage = ? , empsal = ? where empname = ?";
            int row = JdbcUtils.executeUpdate(sql, employee.getEmpName(), employee.getEmpAge(), employee.getEmpSal(), updateName);
            return row > 0 ? true : false;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            JdbcUtils.close(conn, pstmt, rs);
//        }
//        return false;
    }

    /**
     * 删除员工
     * @param employee
     * @return
     */
    @Override
    public boolean deleteEmployee(Employee employee) {
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement("delete from emps where empname = ?");
            pstmt.setString(1, employee.getEmpName());
            int row = pstmt.executeUpdate();
            return row > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(conn, pstmt,rs);
        }
        return false;
    }
}