一、创建项目包 package

  1. 文件夹命名全部小写
  3. mvc:
  4. com.entity
  5.    放和表对应的实体类
  6. com.dao
  7.     数据访问层不做业务处理
  8.         判断逻辑
  9.         数据转换
  10. com.service
  11.     业务层接口 业务层做业务处理
  12.     com.service.impl
  13.         业务层接口实现类
  14. com.controller
  15.     控制层代码
  17. test.com.controller
  18.     控制层代码测试
  19. test.com.dao
  20.     数据访问层测试
  21. test.com.entity
  22.     实体类层测试

二、创建雇员表

  1. CREATE TABLE `t_employee` (
  2.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  3.   `addTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
  4.   `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  5.   `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '员工姓名',
  6.   `position` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '位置',
  7.   `tel` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电话',
  8.   `addr` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址',
  9.   `school` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校',
  10.   `sex` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',
  11.   PRIMARY KEY (`id`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

三、创建雇员实体类

  1. package com.entity;
  3. import java.util.Date;
  5. /**
  6.  * 雇员
  7.  */
  8. public class Employee {
  9.     //主键ID
  10.     private Long id;
  12.     //入库时间
  13.     private Date addTime;
  15.     //出生日期
  16.     private Date birthday;
  18.     //员工姓名
  19.     private String ename;
  21.     //电话
  22.     private String tel;
  24.     //地址
  25.     private String addr;
  27.     //学校
  28.     private String school;
  30.     //性别
  31.     private String sex;
  33.     public Long getId() {
  34.         return id;
  35.     }
  37.     public void setId(Long id) {
  38.         this.id = id;
  39.     }
  41.     public Date getAddTime() {
  42.         return addTime;
  43.     }
  45.     public void setAddTime(Date addTime) {
  46.         this.addTime = addTime;
  47.     }
  49.     public Date getBirthday() {
  50.         return birthday;
  51.     }
  53.     public void setBirthday(Date birthday) {
  54.         this.birthday = birthday;
  55.     }
  57.     public String getEname() {
  58.         return ename;
  59.     }
  61.     public void setEname(String ename) {
  62.         this.ename = ename;
  63.     }
  65.     public String getTel() {
  66.         return tel;
  67.     }
  69.     public void setTel(String tel) {
  70.         this.tel = tel;
  71.     }
  73.     public String getAddr() {
  74.         return addr;
  75.     }
  77.     public void setAddr(String addr) {
  78.         this.addr = addr;
  79.     }
  81.     public String getSchool() {
  82.         return school;
  83.     }
  85.     public void setSchool(String school) {
  86.         this.school = school;
  87.     }
  89.     public String getSex() {
  90.         return sex;
  91.     }
  93.     public void setSex(String sex) {
  94.         this.sex = sex;
  95.     }
  96. }

四、添加JDBC 驱动包、junit测试包

G@L()4ZZ71U$GDBFULBJ(GC.png
){BH7L$0{HUTD%~BNP)BSNR.png

五、创建db.properties数据库配置文件

在src下面创建db.properties

  1. driver=com.mysql.cj.jdbc.Driver
  2. url=jdbc:mysql://localhost:3306/erp
  3. userName=root
  4. password=root

六、创建DBUtils

  1. package com.utils;
  3. import java.io.BufferedInputStream;
  4. import java.io.FileInputStream;
  5. import java.io.InputStream;
  6. import java.sql.Connection;
  7. import java.sql.DriverManager;
  8. import java.sql.SQLException;
  9. import java.util.Properties;
  11. /**
  12.  * 数据库访问 工具类
  13.  */
  14. public class DBUtils {
  15.     //ctrl+shift+u
  16.     //默认值是null
  17.     public static String DRIVER;
  18.     public static String URL;
  19.     public static String USERNAME;
  20.     public static String PASSWORD;
  22.     /**
  23.      * 在这个工具包第一次使用的时候就会加载这一块
  24.      * 只要程序不停止,就是一次在内存中的
  25.      * 可以通过类名直接调用等方式
  26.      */
  27.     static {
  28.         try{
  29.             InputStream in = new BufferedInputStream(new FileInputStream("src/db.properties")) ;
  30.             Properties p = new Properties();
  31.             p.load(in);
  32.             DRIVER =p.getProperty("driver");
  33.             URL =p.getProperty("url");
  34.             USERNAME =p.getProperty("userName");
  35.             PASSWORD =p.getProperty("password");
  37.             Class.forName(DRIVER);
  39.         }catch (Exception e){
  40.             e.printStackTrace();
  41.         }
  42.     }
  44.     /**
  45.      * 获得数据库连接
  46.      * @return
  47.      */
  48.     public static Connection getConn(){
  50.         try{
  51.             Connection conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
  52.             return conn;
  53.         }catch (Exception e){
  54.             e.printStackTrace();
  55.             throw new RuntimeException("数据库连接报错："+e.getMessage());//告诉调用方这里报错了
  56.         }finally {
  57. //            System.out.println("....");
  58.         }
  60.         //这里执行不到
  61.         //return null;
  62.     }
  64.     /**
  65.      * 关闭数据库连接
  66.      * @param conn
  67.      */
  68.     public static void close(Connection conn){
  69.         try{
  70.             if(conn != null){
  71.                 conn.close();
  72.             }
  73.         }catch (SQLException e){
  74.             e.printStackTrace();
  75.             throw new RuntimeException(e.getMessage());//告诉调用方这里报错了
  76.         }
  77.     }
  79.     public static void main(String[] args) {
  80.         System.out.println(DBUtils.DRIVER);
  81.     }
  83. }

七、创建数据访问层dao

  1. package com.dao;
  3. import com.entity.Employee;
  4. import com.utils.DBUtils;
  6. import java.sql.*;
  7. import java.util.ArrayList;
  8. import java.util.List;
  10. /**
  11.  * 员工 数据访问层
  12.  * 新增、修改、查询、删除、分页
  13.  */
  14. public class EmployeeDao {
  16.     /**
  17.      * INSERT INTO `erp`.`t_employee` (`id`, `addTime`, `birthday`, `ename`, `tel`, `addr`, `school`, `sex`)
  18.      * VALUES ('2', '2022-04-13 10:07:00', '2022-04-13 10:07:00', '老王同学', '1234567890', '福州市区。。。', '高中', '男');
  19.      * 新增雇员
  20.      * @param emp
  21.      * @return 新增的行数
  22.      */
  23.     public int insert(Employee emp){
  25.         try{
  26.             //获得连接
  27.             Connection conn = DBUtils.getConn();
  29.             //拼装SQL语句
  30.             StringBuilder sql = new StringBuilder();
  31.             sql.append(" insert into t_employee(birthday,ename,tel,addr,school,sex) ");
  32.             sql.append(" values(?,?,?,?,?,?) ");
  34.             //获得预编译执行
  35.             PreparedStatement stmt = conn.prepareStatement(sql.toString());
  37.             //设置参数
  38.             stmt.setTimestamp(1,new Timestamp(emp.getBirthday().getTime()));
  39.             stmt.setString(2,emp.getEname());
  40.             stmt.setString(3,emp.getTel());
  41.             stmt.setString(4,emp.getAddr());
  42.             stmt.setString(5,emp.getSchool());
  43.             stmt.setString(6,emp.getSex());
  45.             //执行update 新增操作 返回新增的行数
  46.             int c = stmt.executeUpdate();
  48.             return c;
  49.         }catch (SQLException e){
  50.             e.printStackTrace();
  51.             throw new RuntimeException(e.getMessage());
  52.         }
  53.     }
  55.     /**
  56.      *
  57.      update t_employee set
  58.      addTime = '2022-4-13 10:03:09',
  59.      birthday = '2022-4-13 10:02:01',
  60.      ename = '小张同学',
  61.      tel = '123451234512',
  62.      addr = '深圳市罗湖区...',
  63.      school = '中学',
  64.      sex='男'
  65.      where id = 1
  66.      -----------------------------------
  67.      UPDATE `erp`.`t_employee` SET `id`='2', `addTime`='2022-04-13 10:07:00', `birthday`='2022-04-13 10:07:00',
  68.      `ename`='老王同学', `tel`='1234567890', `addr`='福州市区。。。', `school`='高中', `sex`='男' WHERE (`id`='2');
  70.      *
  71.      * 基于ID进行 修改 属性
  72.      * @param emp
  73.      * @return
  74.      */
  75.     public int updateById(Employee emp){
  77.         try{
  78.             //获得连接
  79.             Connection conn = DBUtils.getConn();
  81.             //拼装SQL语句
  82.             StringBuilder sql = new StringBuilder();
  83.             sql.append(" update t_employee set  ");
  84.             sql.append(" addTime = ?, ");
  85.             sql.append(" birthday = ?, ");
  86.             sql.append(" ename = ?, ");
  87.             sql.append(" tel = ?, ");
  88.             sql.append(" addr = ?, ");
  89.             sql.append(" school = ?, ");
  90.             sql.append(" sex= ? ");
  91.             sql.append(" where id = ? ");
  93.             //获得预编译执行
  94.             PreparedStatement stmt = conn.prepareStatement(sql.toString());
  96.             //设置参数
  97.             stmt.setTimestamp(1,new Timestamp(new java.util.Date().getTime()));
  98.             stmt.setTimestamp(2,new Timestamp(emp.getBirthday().getTime()));
  99.             stmt.setString(3,emp.getEname());
  100.             stmt.setString(4,emp.getTel());
  101.             stmt.setString(5,emp.getAddr());
  102.             stmt.setString(6,emp.getSchool());
  103.             stmt.setString(7,emp.getSex());
  104.             stmt.setLong(8,emp.getId());
  106.             //执行update 修改操作 返回修改的行数
  107.             int c = stmt.executeUpdate();
  109.             return c;
  110.         }catch (SQLException e){
  111.             e.printStackTrace();
  112.             throw new RuntimeException(e.getMessage());
  113.         }
  115.     }
  117.     /**
  118.      * 根据ID查询 雇员
  119.      * @param id
  120.      * @return
  121.      */
  122.     public Employee getById(Long id){
  124.         try{
  125.             //获得连接
  126.             Connection conn = DBUtils.getConn();
  128.             //拼装SQL语句
  129.             StringBuilder sql = new StringBuilder();
  130.             sql.append(" select id,addTime,birthday,ename,tel,addr,school,sex  ");
  131.             sql.append(" from t_employee where id = ? ");
  133.             //获得预编译执行
  134.             PreparedStatement stmt = conn.prepareStatement(sql.toString());
  136.             //设置参数
  137.             stmt.setLong(1,id);
  139.             //执行Query
  140.             ResultSet rs = stmt.executeQuery();
  142.             //用来接收返回值
  143.             Employee emp = new Employee();
  144.             //判断是否有下一行
  145.             while(rs.next()){
  146.                 emp.setId(rs.getLong("id"));
  147.                 emp.setAddTime(rs.getTimestamp("addTime"));
  148.                 emp.setBirthday(rs.getTimestamp("birthday"));
  149.                 emp.setEname(rs.getString("ename"));
  150.                 emp.setTel(rs.getString("tel"));
  151.                 emp.setAddr(rs.getString("addr"));
  152.                 emp.setSchool(rs.getString("school"));
  153.                 emp.setSex(rs.getString("sex"));
  154.             }
  156.             return emp;
  157.         }catch (SQLException e){
  158.             e.printStackTrace();
  159.             throw new RuntimeException(e.getMessage());
  160.         }
  162.     }
  164.     /**
  165.      -- select * from t_employee limit (current - 1) * pageSize,pageSize
  166.      -- m:从0开始的位置
  167.      -- n:返回多少条
  168.      -- pageSize 每页10条
  169.      -- 第一页
  170.      select * from t_employee limit (1 -1 ) * 10,10
  171.      -- 第二页
  172.      select * from t_employee limit (2 -1 ) * 10,10
  173.      -- 第三页
  174.      select * from t_employee limit (3 - 1) * 10,10
  175.      * @param current 第几页的页码
  176.      * @param pageSize 每页多少条数据
  177.      * @return
  178.      */
  179.     public List<Employee> page(Integer current,Integer pageSize){
  181.         try{
  182.             //获得连接
  183.             Connection conn = DBUtils.getConn();
  185.             //拼装SQL语句
  186.             StringBuilder sql = new StringBuilder();
  187.             sql.append(" select id,addTime,birthday,ename,tel,addr,school,sex  ");
  188.             sql.append(" from t_employee limit ?,? ");
  190.             //获得预编译执行
  191.             PreparedStatement stmt = conn.prepareStatement(sql.toString());
  193.             //设置参数
  194.             stmt.setInt(1,(current - 1) * pageSize);
  195.             stmt.setInt(2,pageSize);
  197.             //执行Query
  198.             ResultSet rs = stmt.executeQuery();
  200.             //用来接收返回值
  201.             List<Employee> emps = new ArrayList<>();
  203.             //判断是否有下一行
  204.             while(rs.next()){
  205.                 //每一次创建一个新的Employee
  206.                 Employee emp = new Employee();
  207.                 emp.setId(rs.getLong("id"));
  208.                 emp.setAddTime(rs.getTimestamp("addTime"));
  209.                 emp.setBirthday(rs.getTimestamp("birthday"));
  210.                 emp.setEname(rs.getString("ename"));
  211.                 emp.setTel(rs.getString("tel"));
  212.                 emp.setAddr(rs.getString("addr"));
  213.                 emp.setSchool(rs.getString("school"));
  214.                 emp.setSex(rs.getString("sex"));
  215.                 //添加到List集合中
  216.                 emps.add(emp);
  218.             }
  220.             return emps;
  221.         }catch (SQLException e){
  222.             e.printStackTrace();
  223.             throw new RuntimeException(e.getMessage());
  224.         }
  226.     }
  228. }

八、创建测试类

  1. package test.com.dao;
  3. import com.dao.EmployeeDao;
  4. import com.entity.Employee;
  5. import com.utils.DBUtils;
  6. import org.junit.Test;
  7. import static org.junit.Assert.*;
  9. import java.util.Date;
  10. import java.util.List;
  12. /**
  13.  * 测试雇员数据访问层
  14.  */
  15. public class TestEmployeeDao {
  16.     //成员对象 在成员方法中可以直接访问到
  17.     EmployeeDao employeeDao = new EmployeeDao();
  19.     @Test
  20.     public void testInsert(){
  21.         Employee emp = new Employee();
  22.         emp.setBirthday(new Date());
  23.         emp.setAddr("福州市区。。。");
  24.         emp.setEname("小王同学");
  25.         emp.setSchool("小学5年级");
  26.         emp.setSex("女");
  27.         emp.setTel("1234567890");
  29.         for (int i = 0; i < 50; i++) {
  30.             int c = employeeDao.insert(emp);
  32.             assertEquals(c,1);
  33.         }
  36.     }
  38.     @Test
  39.     public void testUpdate(){
  40.         Employee emp = new Employee();
  41.         emp.setId(2L);
  42.         emp.setBirthday(new Date());
  43.         emp.setAddr("福州市区。。。");
  44.         emp.setEname("老王同学");
  45.         emp.setSchool("高中");
  46.         emp.setSex("男");
  47.         emp.setTel("1234567890");
  50.         int c = employeeDao.updateById(emp);
  52.         assertEquals(c,1);
  53.     }
  55.     @Test
  56.     public void testGetById(){
  57.         Long id = 3L;
  58.         Employee emp = employeeDao.getById(id);
  60.         assertNotNull(emp.getId());
  62.         System.out.println(emp.getId());
  64.     }
  66.     @Test
  67.     public void testPage(){
  68.         //前台:网页 小程序 app
  69.         Integer current = 10;
  70.         //每页多少条有的后台给定好的 也有的前台传过来的
  71.         Integer pageSize = 10;
  73.         List<Employee> emps = employeeDao.page(current,pageSize);
  75.         for (Employee emp : emps) {
  76.             System.out.println("id = " + emp.getId() + " ename = " + emp.getEname());
  77.         }
  79.     }
  81. }