springboot+mybatis整合，实现增删改查

springboot+mybatis项目整合，实现增删改查

一、创建数据表

CREATE TABLE `user_info` (
  `user_id` int(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) NOT NULL COMMENT '用户名称',
  `login_name` varchar(64) NOT NULL COMMENT '登录名称',
  `login_pass` varchar(32) NOT NULL COMMENT '登录密码',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

二、springboot+mybatis 项目整合

1、创建项目file—new–maven project–next

2、选择以quickstart结尾的或者webapp结尾的，点击next

3、设置好group id 和项目名称。finish

4、在pom文件里面配置相关的依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.tc.user</groupId>
	<artifactId>user</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>user</name>
	<description>Demo project for Spring Boot</description>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<!-- web -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.1</version>
		</dependency>
		<!-- mysql -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- 日志 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-logging</artifactId>
		</dependency>
		<!-- 模板引擎 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

5、引入相关依赖过后项目会报错

6、解决项目错误：右键项目–maven–update project勾选项目点击ok

7、创建相关文件及包

8、在application.properties里面配置数据库

spring.datasource.url = jdbc:mysql://192.168.3.21:3306/tc_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username = root
spring.datasource.password = 123456
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.initial-size=10
mybatis.mapper-locations= classpath:mapper/*.xml

9、写实体类（User）并添加gettters和setters方法

package com.tc.demo.entity;

import java.util.Date;

public class User {

	private Integer userId;
	private String userName;
	private String loginName;
	private String loginPass;
	private Date createTime;
	public Integer getUserId() {
		return userId;
	}
	public void setUserId(Integer userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getLoginName() {
		return loginName;
	}
	public void setLoginName(String loginName) {
		this.loginName = loginName;
	}
	public String getLoginPass() {
		return loginPass;
	}
	public void setLoginPass(String loginPass) {
		this.loginPass = loginPass;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	@Override
	public String toString() {
		return "User [userId=" + userId + ", userName=" + userName + ", loginName=" + loginName + ", loginPass="
				+ loginPass + ", createTime=" + createTime + "]";
	}
	
	
}

10、写mapper接口，mapper接口用@Repository注解

package com.tc.demo.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import com.tc.demo.entity.User;

@Repository
public interface UserMapper {
	
	/**
	 * 查询全部用户
	 * @return
	 */
	public List<User> listUser();
	
	/**
	 * 新增用户信息
	 * @param user 用户信息
	 * @return	返回主键id
	 */
	public Integer insertUser(User user);
	
	/**
	 * 根据主键userId查询对象
	 * @param userId 用户id
	 * @return 用户信息
	 */
	public User queryByUserId(@Param("userId")Integer userId);
	
	/**
	 * 修改用户信息
	 * @param user
	 * @return
	 */
	public Integer updateUser(User user);
	
	/**
	 * 根据用户id删除用户
	 * @param userId
	 * @return 返回  受影响的行
	 */
	public Integer delete(@Param("userId")Integer userId);

}

11、写mapper.xml文件(sql语句里面对应的id值是mapper类里面对应方法的方法名)

<?xml version = "1.0" encoding = "UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tc.demo.mapper.UserMapper">
	<resultMap id="result" type="com.tc.demo.entity.User">
		<!-- 
			property：是对应的实体属性名称
			column ：  是对应数据表中的字段名称
		 -->
		<result property="userId" column="user_id" />
		<result property="userName" column="user_name" />
		<result property="loginName" column="login_name"/>
		<result property="loginPass" column="login_pass"/>
		<result property="createTime" column="create_time"/>
	</resultMap>
	<!-- 查询所有用户信息（建议不要使用select * ） -->
	<select id="listUser" resultMap="result" parameterType="com.tc.demo.entity.User">
		SELECT 
			 user_id
			,user_name
			,login_name
			,login_pass
			,create_time
		FROM user_info
	</select>
	
	<!-- 根据用户id查询用户信息(用户id必须传) -->
	<select id="queryByUserId" resultMap="result" parameterType="com.tc.demo.entity.User">
		SELECT 
			 user_id
			,user_name
			,login_name
			,login_pass
		FROM user_info where user_id=#{userId}
	</select>
	
	<!--新增用户信息  -->
	<insert id="insertUser" parameterType="com.tc.demo.entity.User">
		INSERT INTO user_info (
			 user_name
			, login_name
			, login_pass
			, create_time
		)VALUES(
			 #{userName}
			,#{loginName}
			,#{loginPass}
			,#{createTime}
		)
	</insert>
	
	<!-- 根据用户id修改用户信息 -->
	<update id="updateUser" parameterType="com.tc.demo.entity.User">
		UPDATE user_info 
		SET
			  user_name = #{userName}
			, login_name = #{loginName} 
			, login_pass = #{loginPass}
		WHERE
			user_id = #{userId}
	</update>
	
		<!-- 根据用户id删除用户信息 -->
	<delete id="delete"  parameterType="java.lang.Integer">
		DELETE FROM user_info WHERE user_id = #{userId}
	</delete>
</mapper>

12、编写service接口，service层使用@Service注解

package com.tc.demo.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.tc.demo.entity.User;

@Service
public interface UserService {

	/**
	 * 查询所有用户信息
	 * @return
	 */
	public List<User> listUser();
	
	 /**
	  * 根据用户id查询用户信息
	  * @param userId
	  * @return 返回User实体信息
	  */
	public User queryByUserId(Integer userId);
	
	/**
	 * 新增用户信息
	 * @param user
	 * @return
	 */
	public Integer insertUser(User user);
	
	/**
	 * 修改用户信息
	 * @param user
	 * @return
	 */
	public Integer updateUser(User user);
	
	/**
	 * 根据用户id删除用户信息
	 * @param userId
	 * @return
	 */
	public Integer delete(Integer userId);
	
}

13、实现service接口（implements 需要实现接口的名）

package com.tc.demo.service.impl;

import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.tc.demo.entity.User;
import com.tc.demo.mapper.UserMapper;
import com.tc.demo.service.UserService;

@Service
public class  UserServiceImpl implements UserService{
	//注入UserMapper
		@Autowired
		private UserMapper userMapper;
	
		@Override
		public List<User> listUser() {
			//查询所有，调用usermapper中的查询方法listUser
			return userMapper.listUser();
		}
		//新增用户信息
		@Override
		public Integer insertUser(User user) {
			//给时间赋值
			user.setCreateTime(new Date());
			//调用usermapper中的新增方法insertUser
			return userMapper.insertUser(user);
		}
		
		@Override
		public User queryByUserId(Integer userId) {
			//调用usermapper中的根据用户id查询用户信息的方法queryByUserId
			return userMapper.queryByUserId(userId);
		}

		
		@Override
		public Integer updateUser(User user) {
			//调用usermapper中的修改方法updateUser
			return userMapper.updateUser(user);
		}
		
		@Override
		public Integer delete(Integer userId) {
			//调用usermapper中的删除方法delete
			return userMapper.delete(userId);
		}

}

14、编写controller类，controller层使用@Controller注解

package com.tc.demo.controller;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.tc.demo.entity.User;
import com.tc.demo.service.UserService;


/**
 * 
 * @ClassName: UserController
 * @Description: 用户访问层
 * @author 唐程
 * @date 2020-04-02 10:22:42
 */
//@RestController 是 @Controller + @ResponseBody
@Controller
public class UserController {
	//日志
	private static final Logger logger = LoggerFactory.getLogger(UserController.class);
	
	
	//@Autowired：实例化UserService类
	@Autowired
	private UserService userservice;
	/**
	 * 新增用户信息
	 * @param user
	 * @return
	 */
	@RequestMapping("/addUser")
	public String insert(User user,Model model){
		
		//1. 获取表单数据 == user
		
		//2. 调用业务方法
		int rows = userservice.insertUser(user);
		if(rows > 0){
			//前端保存的返回值
			model.addAttribute("msg", "新增成功");
		}
		
		return "success";

		}
	/**
	 * 根据用户id查询用户信息
	 * @param model
	 * @param userId
	 * @return
	 */
	@RequestMapping("/selectUserId")
	public String queryByUserId(Model model,Integer userId){
		User user=userservice.queryByUserId(userId);
		//通过model把user查询到的值传到页面
		model.addAttribute("u",user );
		return "update";
	}
		/**
		 * 修改用户信息
		 * @param user
		 * @return
		 */
		@RequestMapping("/update")
		public String update(User user,Model model){
			//获取user对象
			
			//调用修改方法
		Integer rows= userservice.updateUser(user);
		if( rows==null ){
			//前端保存的返回值
			model.addAttribute("msg", "修改成功");
		}
			return "redirect:/selectUserList";
			
		}
		
	/**
	 * 查询所有用户
	 * @param model 保存页面需要的参数
	 * @return 返回到的页面
	 */
	@RequestMapping("/selectUserList")
	public String queryUserList(Model model){
		//1.调用业务方法
		List<User> list= userservice.listUser();
		//2.把查询的用户集合信息
		//通过model的addAttribute方法，把list集合值传给前端页面
		//model.addAttribute("前端接收的变量名", "结果值");
		model.addAttribute("userList", list);
		//3.返回到页面
		return "userlist";
	}
	/**
	 * 根据用户id删除用户信息
	 * @param userId
	 * @return
	 */
	@RequestMapping("/deleteUser")
	public String deleteUser(Integer userId){
		Integer rows = userservice.delete(userId);
		logger.info("删除,影响行:"+rows);
		return "redirect:/selectUserList";
	}
	
}

15、画前端页面userlist.html

<!DOCTYPE html>
<html xmls:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<table border="1" style="width: 100%">
	<tr>
		<td colspan="7">
			<button type="button" onclick="addUser()">添加</button>
		</td>
	</tr>
	<tr>
		<td>序列</td>
		<td>用户ID</td>
		<td>用户名称</td>
		<td>登录名称</td>
		<td>登录密码</td>
		<td>创建时间</td>
		<td>操作</td>
	</tr>
	<tr th:each="obj,objStat:${userList}">
		<td th:text="${objStat.index + 1}"></td>
		<td th:text="${obj.userId}"></td>
		<td th:text="${obj.userName}">Onions</td>
		<td th:text="${obj.loginName}"></td>
		<td th:text="${obj.loginPass}"></td>
		<td th:text="${#dates.format(obj.createTime,'yyyy-MM-dd')}"></td>		
		<td colspan="2">
			<a th:href="@{/selectUserId(userId=${obj.userId})}">编辑</a>
			<a th:href="@{/deleteUser(userId=${obj.userId})}">删除</a>
		</td>
	</tr>
	
</table>

<script type="text/javascript">
function addUser(){
	window.location.href="add.html";
}
</script>
</body>
</html>

16、add.html页面

<!DOCTYPE html>
<!-- 引入模板引擎标签 -->
<html xmls:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="/addUser" method="post">
	<table border="1">
		<tr>
			<input type="text" id="" name="userId" th:value="${u?.userId}"/> 
			<td>用户名称 : </td>
			<td><input type="text" id="" name="userName" th:value="${u?.userName}"/> </td>
		</tr>
		<tr>
			<td>登录名称 : </td>
			<td><input type="text" id="" name="loginName" th:value="${u?.loginName}"/> </td>
		</tr>
		<tr>
			<td>登录密码 : </td>
			<!--u对象：是通过controller类的toAddUser方法里面model参数传值到页面  -->
			<td><input type="text" id="" name="loginPass" th:value="${u?.loginPass}"/> </td>
		</tr>
		<tr>
			<td colspan="2" style="text-align: center;">
				<button type="submit">确定</button>
			</td>
		</tr>
	</table>
</form>

</body>
</html>

17、success.html页面

<!DOCTYPE html>
<html xmls:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
 <span th:text=${msg}></span>
 <a href="selectUserList">
 进入列表页
 </a>
</body>
</html>

18、update.html页面

<!DOCTYPE html>
<html xmls:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
 <span th:text=${msg}></span>
    <!--超链接，返回到查询页-->
 <a href="selectUserList">
 进入列表页
 </a>
</body>
</html>

所有html页面中action的值都是对应的是对应方法的方法名

19、运行项目浏览器访问 localhost:8080/selectUserList ，点击按钮实现对应功能