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