SpringBoot整合Mybatis开发后台管理系统

这是上一个升级版，上一次是模拟数据库，这次是连接数据库开发，在原来的基础上进行开发。

准备工作

• 创建数据库springboot，一共三个表，User,Employee,Development

  /*
  SQLyog Ultimate v12.09 (64 bit)
  MySQL - 5.5.53 : Database - springboot
  *********************************************************************
  *//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;USE `springboot`;/*Table structure for table `development` */DROP TABLE IF EXISTS `development`;CREATE TABLE `development` (`id` int(10) NOT NULL AUTO_INCREMENT,`developmentName` varchar(10) COLLATE utf8_unicode_ci NOT NULL,PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/*Data for the table `development` */insert  into `development`(`id`,`developmentName`) values (101,'教育部'),(102,'市场部'),(103,'人事部'),(104,'技术部'),(105,'运营部');/*Table structure for table `employee` */DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` (`id` int(10) NOT NULL AUTO_INCREMENT,`lastName` varchar(20) COLLATE utf8_unicode_ci NOT NULL,`email` varchar(20) COLLATE utf8_unicode_ci NOT NULL,`gender` int(1) NOT NULL DEFAULT '0' COMMENT '1是男，0是女',`development` int(10) NOT NULL,`birth` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `FK_ID` (`development`),CONSTRAINT `FK_ID` FOREIGN KEY (`development`) REFERENCES `development` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1010 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/*Data for the table `employee` */insert  into `employee`(`id`,`lastName`,`email`,`gender`,`development`,`birth`) values (1001,'Aa','A115768355@qq.com',0,101,'2022-01-29 20:36:32'),(1002,'Bb','B115762355@qq.com',1,104,'2022-01-29 16:00:00'),(1003,'Cc','C113526236@qq.com',0,101,'2022-01-29 16:00:00'),(1004,'Dd','D113526364@qq.com',1,102,'2022-01-29 16:00:00'),(1005,'Ee','E3526365236@qq.com',0,104,'2022-02-01 19:19:16'),(1006,'Ff','F3526365236@qq.com',0,105,'2022-02-01 16:00:00'),(1007,'AAA','1132898427@qq.com',0,102,'2010-02-04 16:00:00'),(1008,'张杰','1132898427@qq.com',1,105,'2000-02-04 16:00:00'),(1009,'王姐','32905241@qq.com',0,105,'2003-05-21 16:00:00');/*Table structure for table `user` */DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (`id` int(10) NOT NULL AUTO_INCREMENT,`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,`password` varchar(20) COLLATE utf8_unicode_ci NOT NULL,`gender` int(1) NOT NULL DEFAULT '0' COMMENT '1是男，0是女',`age` int(10) NOT NULL,`telphone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=10009 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/*Data for the table `user` */insert  into `user`(`id`,`username`,`password`,`gender`,`age`,`telphone`,`date`) values (10001,'唐华','123456',1,23,'15326761783','2002-11-28 16:00:00'),(10002,'test2','123456',1,23,'15326761783','2012-11-28 16:00:00'),(10003,'test3','123456',0,19,'15326761783','2005-11-28 16:00:00'),(10004,'test4','123456',1,30,'15326761783','0000-00-00 00:00:00'),(10005,'test5','123456',0,18,'15326761783','0000-00-00 00:00:00'),(10007,'test5','123456',0,18,'15326761783','2000-06-01 16:00:00'),(10008,'admin','root',1,22,'15326761783','2022-01-29 19:15:57');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

• 导入依赖

  <dependencies><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.8</version></dependency><!--thymeleaf--><dependency><groupId>org.thymeleaf</groupId><artifactId>thymeleaf-spring5</artifactId></dependency><!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.1</version></dependency><!--Lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.thymeleaf.extras</groupId><artifactId>thymeleaf-extras-java8time</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency>
  </dependencies>
  

• 资源配置类application.properties

  #时间格式化
  spring.mvc.date-format=yyyy-MM-dd
  #关闭thymleaf缓存机制
  spring.thymeleaf.cache=false
  spring.thymeleaf.mode=HTML5
  spring.thymeleaf.encoding=utf-8
  spring.thymeleaf.suffix=.htmlserver.port=8081
  #国际化信息设置
  spring.messages.basename=i18n.login#连接数据库
  spring.datasource.username=root
  spring.datasource.password=root
  spring.datasource.url=jdbc:mysql://localhost:3306/springboot?zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
  spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.datasource.type=com.alibaba.druid.pool.DruidDataSource#整合mybatis
  mybatis.mapper-locations=classpath:mapper/*.xml
  mybatis.type-aliases-package=com.zyb.pojo
  

Dao层开发

• UserDao

  public interface UserDao {User login(@PathVariable("username") String username,@PathVariable("password") String password);List<User> queryUserList();User queryUserById(int id);int addUser(User user);int updateUser(User user);int deleteUser(int id);
  }

• DevelopmentDao

  public interface DevelopmentDao {//获得所有的部门信息List<Development> getDevelopments();//根据id获得部门信息List<Development> getDevelopmentById(Integer id);
  }
  

• EmployeeDao

  public interface EmployeeDao {List<Employee> queryEmployeeList();Employee queryEmployeeById(int id);//    Employee searchEmployee(Employee employee);int addEmplyee(Employee employee);int updateEmplyee(Employee employee);int deleteEmplyee(int id);}
  

Controller层

controller层的开发与上一个基本上没有什么变化，就是多了一个UserController，还有就是LoginController也做出了调整。

public class UserController {@Autowiredprivate UserDao userDao;//查询功能@RequestMapping("/users")public String list(Model model){List<User> userList = userDao.queryUserList();for (User user : userList) {System.out.println(user);}model.addAttribute("users",userList);return "user/list1";}//增加功能@GetMapping("/user")public String addTopage(){return "/user/add1";}@PostMapping("/user")public String addUser(User user){System.out.println("addUser" + user);userDao.addUser(user);return "redirect:/users";}//编辑功能@GetMapping("/updateuser/{id}")public String updateTopage(@PathVariable("id") Integer id,Model model){User user = userDao.queryUserById(id);model.addAttribute("user",user);return "/user/update1";}@PostMapping("/updateuser")public String updaeUser(User user){userDao.updateUser(user);return "redirect:/users";}//删除功能@RequestMapping("/deleteuser/{id}")public String deleteUser(@PathVariable("id") Integer id){userDao.deleteUser(id);return "redirect:/users";}
}

User login = userDao.login(username, password);
if (login!=null){session.setAttribute("loginUser",username);System.out.println("登陆成功");return "redirect:/main.html";
}

Mapper.xml的开发

• UserDaoMapper.xml

  <select id="queryUserList" resultType="User">select *from springboot.user;
  </select><select id="login" resultType="user">select *from springboot.user where username=#{username} and password=#{password};
  </select><select id="queryUserById" resultType="user">select *from springboot.user where id=#{id};
  </select><insert id="addUser" parameterType="user">insert into springboot.user(username, password, gender, age, telphone,date)values (#{username},#{password},#{gender},#{age},#{telphone},#{date});
  </insert><update id="updateUser" parameterType="user">update springboot.userset username =#{username},password=#{password},gender=#{gender},age=#{age},telphone=#{telphone},date=#{date}where id=#{id};
  </update><delete id="deleteUser" parameterType="int">delete from springboot.user where id=#{id};
  </delete>
  

• DevelopmentDaoMapper.xml

  <select id="getDevelopments" resultType="Development">select * from springboot.development;
  </select><select id="getDevelopmentById" resultType="Development" parameterType="int">select * from springboot.development where id=#{id};
  </select>
  

• EmployeeDaoMapper.xml

  <resultMap id="EmployeeMap" type="Employee"><id property="id" column="eid"/><result property="lastName" column="lastName"/><result property="email" column="email"/><result property="gender" column="gender"/><result property="development" column="development"/><result property="birth" column="birth"/><association property="eDevelopment"  javaType="Development"><id property="id" column="did"/><result property="developmentName" column="dname"/></association>
  </resultMap><select id="queryEmployeeList" resultMap="EmployeeMap">select e.id as eid,lastName,email,gender,development,birth,d.id as 		did,d.developmentName as dnamefrom springboot.development d,springboot.employee ewhere d.id = e.development order by e.id asc;
  </select><insert id="addEmplyee" parameterType="Employee">insert into springboot.employee (lastName,email,gender,development,birth)values (#{lastName},#{email},#{gender},#{development},#{birth});
  </insert><select id="queryEmployeeById" resultType="Employee">select * from springboot.employee where id = #{id}
  </select><update id="updateEmplyee" parameterType="Employee">update springboot.employeeset lastName = #{lastName},email=#{email},gender=#{gender},development=#{development},birth=#{birth}where id=#{id};
  </update><delete id="deleteEmplyee" parameterType="int">delete from springboot.employee where id = #{id}
  </delete>
  

• 页面部分改变add.html

  <!--下拉框-->
  <div class="form-group"><label>Development</label><select class="form-control" name="development"><!--/*@thymesVar id="getDevelopmentName" type="com.zyb.pojo.Development"*/--><option th:each="development:${developments}"th:text="${development.getDevelopmentName()}"th:value="${development.getId()}">1</option></select>
  </div>
  

• update.html

  <!--下拉框-->
  <div class="form-group"><label>Development</label><select class="form-control" name="development"><option th:selected="${development.getId()==emp.getDevelopment()}"th:each="development:${developments}"th:text="${development.getDevelopmentName()}"th:value="${development.getId()}">1</option></select>
  </div>
  

展示最终的效果

• 登录页面

• 主页面

• 用户管理

• 用户管理–增加功能

• 用户管理–编辑功能

• 删除功能

还有一个员工管理的展示效果就不演示了，以上就是spring boot整合Mybatis开发的后台管理系统的内容，有什么建议或者指出不足，还望提出，我会加以改正。