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

最新推荐文章于 2024-02-01 10:28:53 发布

李淳罡（两袖青蛇）

最新推荐文章于 2024-02-01 10:28:53 发布

阅读量1.6k

点赞数 4

文章标签： spring boot java 数据库

本文链接：https://blog.csdn.net/qq_43730903/article/details/122775322

版权

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=.html

server.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 {
    @Autowired
    private 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.user
    set 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 dname
    from springboot.development d,springboot.employee e
    where 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.employee
    set 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>