spring boot 使用mybatis对数据库操作

spring boot 使用mybatis对数据库操作

1. 创建工程
   
   
   工程目录结构如图所示
   
2. 修改build.gradle文件

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	compile 'org.webjars:bootstrap:4.3.1'
	compile 'org.springframework.boot:spring-boot-devtools'
	implementation 'org.springframework.boot:spring-boot-starter-jdbc'	
	compile 'mysql:mysql-connector-java:5.1.47'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.0.1'
		
}

3. 修改application.properties文件

welcome.message: advanced web programming
spring.thymeleaf.cache=false
spring.devtools.restart.enabled=false

#datasource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/lab
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5

mybatis.type-aliases-package=com.example.pojo

mybatis.mapper-locations=classpath:/com/example/mapper/UsersMapper.xml

4. 编写Users实体类，放在com.example.pojo包下

package com.example.pojo;

public class Users {
	private Integer id;
	private String name;
	private Integer age;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	

}

5. 编写UsersMapper接口，放在com.example.mapper包下，定义对数据库的增删改查操作的方法

package com.example.mapper;

import java.util.List;

import com.example.pojo.Users;

public interface UsersMapper {
	
	void insertUser(Users users);
	List<Users> selectUsersAll();
	Users selectUsersById(Integer id);
	void updateUser(Users users);
	void deleteUserById(Integer id);

}

6. 编写UsersMapper.xml文件，放在com.example.mapper包下，为UsersMapper接口中的方法编写相应的SQL语句

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mtbatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UsersMapper">

	<insert id="insertUser" parameterType="users">
		insert into Users(name,age) values(#{name},#{age})
	</insert>
	
	<select id="selectUsersAll" resultType="users">
		select id,name,age from Users
	</select>
	
	<select id="selectUsersById" resultType="users">
		select id,name,age from Users where id=#{value}
	</select>
	
	<update id="updateUser" parameterType="users">
		update Users set name=#{name},age=#{age} where id=#{id}
	</update>
	
	<delete id="deleteUserById">
		delete from Users where id=#{value}
	</delete>

</mapper>

7. 编写UsersService接口，定义对数据库的操作，放在com.example.service包下

package com.example.service;

import java.util.List;

import com.example.pojo.Users;

public interface UsersService {
	
	void addUsres(Users users);
	List<Users> findUserAll();
	Users findUserById(Integer id);
	void updateUser(Users users);
	void deleteUserById(Integer id);

}

8. 编写UsersService接口的实现类UsersServiceImpl，放在com.example.service.impl包下，对UsersService接口中的方法进行实现

package com.example.service.impl;

import java.util.List;

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

import com.example.mapper.UsersMapper;
import com.example.pojo.Users;
import com.example.service.UsersService;

@Service
@Transactional
public class UsersServiceImpl implements UsersService {
	
	@Autowired
	private UsersMapper usersMapper;

	@Override
	public void addUsres(Users users) {
		
		this.usersMapper.insertUser(users);
		
	}

	@Override
	public List<Users> findUserAll() {
		return this.usersMapper.selectUsersAll();
	}

	@Override
	public Users findUserById(Integer id) {
		return this.usersMapper.selectUsersById(id);
	}

	@Override
	public void updateUser(Users users) {
		this.usersMapper.updateUser(users);
		
	}

	@Override
	public void deleteUserById(Integer id) {
		this.usersMapper.deleteUserById(id);
		
	}

}

9. 编写UsersController类，放在com.example.controller包下，调用UsersServiceImpl类中的各个方法

package com.example.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import com.example.pojo.Users;
import com.example.service.UsersService;

@Controller
@RequestMapping("/users")
public class UsersController {
	
	@Autowired
	private UsersService usersService;
	
	/**
	 * 页面跳转
	 * 
	 */
	@RequestMapping("/{page}")
	public String showPage(@PathVariable String page)
	{
		return page;
	}
	
	/**
	 * 添加用户
	 * 
	 */
	@RequestMapping("/addUser")
	public String addUser(Users users)
	{
		this.usersService.addUsres(users);
		return "ok";
		
	}
	
	/**
	 * 查询全部用户
	 * 
	 */
	@RequestMapping("/findUserAll")
	public String findUserAll(Model model)
	{
		List<Users> list=this.usersService.findUserAll();
		model.addAttribute("list", list);
		return "showUsers";
	}
	
	/**
	 * 根据用户ID查询用户
	 * 
	 */
	@RequestMapping("/findUserById")
	public String findUserById(Integer id,Model model)
	{
		Users user=this.usersService.findUserById(id);
		model.addAttribute("user", user);
		return "updateUser";
	}
	
	/**
	 * 更新用户
	 * 
	 * 
	 */
	@RequestMapping("/editUser")
	public String editUser(Users users)
	{
		this.usersService.updateUser(users);
		return "ok";
	}
	
	/**
	 * 删除用户
	 * 
	 * 
	 */
	@RequestMapping("/delUser")
	public String delUser(Integer id)
	{
		this.usersService.deleteUserById(id);
		return "redirect:/users/findUserAll";	
	}

}

10. 界面，在src/main/resources中的templates中编写html文件
    input.html，用于在插入数据时输入数据

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加用户</title>
</head>
<body>
	<form th:action="@{/users/addUser}" method="post">
		用户姓名：<input type="text" name="name"/><br>
		用户年龄<input type="text" name="age"/><br>
		<input type="submit" value="确定"/>
	</form>
</body>
</html>

showUsers.html，用于显示查询到的数据

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>展示用户数据</title>
</head>
<body>

	<table border="1" style="width:400px">
		<tr>
			<th>用户ID</th>
			<th>用户姓名</th>
			<th>用户年龄</th>
			<th>操作</th>
		</tr>
		
		<tr th:each="user : ${list}">
			<td th:text=${user.id}></td>
			<td th:text=${user.name}></td>
			<td th:text=${user.age}></td>
			<td>
				<a th:href="@{/users/findUserById(id=${user.id})}">更新用户</a>
				<a th:href="@{/users/delUser(id=${user.id})}">删除用户</a>
			</td>
		</tr>
	</table>

</body>
</html>

updateUser.html，用于输入修改后的数据

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form th:action="@{/users/editUser}" method="post">
	<input type="hidden" name="id" th:field="${user.id}"/>
		用户姓名：<input type="text" name="name" th:field="${user.name}"/><br>
		用户年龄<input type="text" name="age" th:field="${user.age}"/><br>
		<input type="submit" value="确定"/>
	</form>

</body>
</html>

11. 运行项目，在浏览器中输入http://localhost:8080/users/input便可查看插入数据的功能，其他功能类似