第二十一章、整合Mybatis3.x增删改查实操, 控制台打印sql语句(SpringBoot2.x)

本文链接：https://blog.csdn.net/wangqing84411433/article/details/103652692

SpringBoot2.x整合Mybatis3.x增删改查实操, 控制台打印sql语句

   1、控制台打印sql语句
       #增加打印sql语句，一般用于本地开发测试

#开启打印sql
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

2、使用之前的实体类和配置文件（上一篇博客）

3、Mapper文件

package net.xdclass.base_project.mapper;

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import net.xdclass.base_project.domain.User;

/**
 * 功能描述：访问数据库的接口
 * @author wq
 *
 */
public interface UserMapper {
	
	//推荐使用#{}取值，不要用${},因为存在注入的风险
	@Insert("INSERT INTO user(name,phone,create_time,age) VALUES(#{name},#{phone},#{createTime},#{age})")
	@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")//keyProperty表示java对象属性     keyColumn表示数据库的字段
	//添加成功后会将主键映射到实体类中，可以在实体类中拿到生成的主键
	public int insert(User user);
	
	
	/**
	 * 功能描述：查找全部
	 * @return
	 */
	@Select("SELECT * FROM user")
	@Results({
		@Result(column="create_time",property="createTime")
	})//实体字段名和数据库字段不一致使用该注解
	public List<User> getAll();
	
	/**
	 * 功能描述：根据id查询
	 * @return
	 */
	@Select("SELECT * FROM user WHERE id = #{id}")
	@Results({
		@Result(column = "create_time", property = "createTime")
	})//实体字段名和数据库字段不一致使用该注解
	public User findById(Long id);
	
	/**
	 * 功能描述：更新对象
	 * @param user
	 */
	@Update("UPDATE user SET name = #{name} WHERE id = #{id}")
	public void update(User user);

	/**
	 * 功能描述：根据id删除用户
	 * @param userId
	 */
	@Delete("DELETE FROM user WHERE id = #{userId}")
	public void delete(Long userId);
}

4、业务层接口以及实现类

package net.xdclass.base_project.service;

import java.util.List;

import net.xdclass.base_project.domain.User;

public interface UserService {
	
	public int add(User user);//添加
	public List<User> getAll();//查找全部
	public User findById(Long id);//根据id查询
	public void update(User user);//更新对象
	public void delete(Long userId);//根据id删除用户
}

package net.xdclass.base_project.service.impl;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import net.xdclass.base_project.domain.User;
import net.xdclass.base_project.mapper.UserMapper;
import net.xdclass.base_project.service.UserService;

@Service
public class UserServiceImpl implements UserService{

	@Autowired
	private UserMapper userMapper;
	
	@Override
	public int add(User user) {
		userMapper.insert(user);
		int id = user.getId();
		return id;
	}

	@Override
	public List<User> getAll() {
		return userMapper.getAll();
	}

	@Override
	public User findById(Long id) {
		return userMapper.findById(id);
	}

	@Override
	public void update(User user) {
		userMapper.update(user);
	}

	@Override
	public void delete(Long userId) {
		userMapper.delete(userId);		
	}
}

5、控制器：

package net.xdclass.base_project.controller;

import java.util.Date;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import net.xdclass.base_project.domain.JsonData;
import net.xdclass.base_project.domain.User;
import net.xdclass.base_project.service.UserService;

@RestController
@RequestMapping("/api/v1/user")
public class UserController {
	
	@Autowired
	private UserService userService;
	
	/**
	 * 功能描述：user保存接口
	 * @return
	 */
	@GetMapping("/add")
	public Object add(){
		User user = new User();
		user.setAge(11);
		user.setCreateTime(new Date());
		user.setName("xdclass");
		user.setPhone("10010000");
		int id = userService.add(user);
		return JsonData.buildSuccess(id);
	}
	
	//测试Mapper中的方法
	/**
	 * 功能描述：查找全部用户
	 * @return
	 */
	@GetMapping("/findAll")
	public Object findAll(){
		List<User> user = userService.getAll();
		return JsonData.buildSuccess(user);
	}
	
	/**
	 * 功能描述：根据id查询
	 * @param id
	 * @return
	 */
	@GetMapping("/findById")
	public Object findById(long id){
		return JsonData.buildSuccess(userService.findById(id));
	}
	
	/**
	 * 功能描述：更新对象
	 * @param user
	 */
	@GetMapping("/update")
	public Object update(String name, int id){
		User user = new User();
		user.setName(name);
		user.setId(id);
		userService.update(user);
		return JsonData.buildSuccess();
	}
	
	/**
	 * 功能描述：根据id删除用户
	 * @param user
	 */
	@GetMapping("/delete")
	public Object deleteById(Long userId){
		userService.delete(userId);
		return JsonData.buildSuccess();
	}
}

访问测试：

查找全部用户

控制台输出：

==>  Preparing: SELECT * FROM user 
==> Parameters: 
<==    Columns: id, name, phone, create_time, age
<==        Row: 2, xdclass, 10010000, 2019-12-22 13:15:13.0, 11
<==        Row: 3, xdclass, 10010000, 2019-12-22 13:15:15.0, 11
<==        Row: 4, xdclass, 10010000, 2019-12-22 13:15:17.0, 11
<==        Row: 5, xdclass, 10010000, 2019-12-22 13:29:05.0, 11
<==        Row: 6, xdclass, 10010000, 2019-12-22 13:39:01.0, 11
<==        Row: 7, xdclass, 10010000, 2019-12-22 13:58:41.0, 11
<==        Row: 10, TOM, 10010000, 2019-12-22 14:11:46.0, 11
<==      Total: 7

根据id查询

控制台输出：

==>  Preparing: SELECT * FROM user WHERE id = ? 
==> Parameters: 10(Long)
<==    Columns: id, name, phone, create_time, age
<==        Row: 10, TOM, 10010000, 2019-12-22 14:11:46.0, 11
<==      Total: 1

更新对象

控制台输出：

==>  Preparing: UPDATE user SET name = ? WHERE id = ? 
==> Parameters: Lucy(String), 10(Integer)
<==    Updates: 1

根据id删除用户

控制台输出：

==>  Preparing: DELETE FROM user WHERE id = ? 
==> Parameters: 7(Long)
<==    Updates: 1