SpringBoot 踩坑记录(九)- Mybatis 整合笔记

create table t_user(
   id INT(12) NOT NULL AUTO_INCREMENT,
   user_name VARCHAR(60) NOT NULL,
   sex int(3) NOT NULL default 1 check (sex in(1,2)),
   note varchar(256) null,
   PRIMARY KEY (id)
);

ALTER TABLE t_user ADD UNIQUE user_name (user_name);

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

# 数据库
spring.datasource.url=jdbc:mysql://localhost:3306/site?serverTimezone=UTC
spring.datasource.username=xxx
spring.datasource.password=xxx
# mybatis mapper.xml 路径
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=top.nicelee.site.pojo
#mybatis.config-location=
# 调试 top.nicelee.site.dao.UserDao
logging.level.top.nicelee.site.dao.UserDao=DEBUG

@Configuration //该注解表示配置
public class WebConfig {
	...    
	/**
	 * 设置Mybatis 扫描装配
	 * @return
	 */
	@Bean
	public MapperScannerConfigurer mapperScannerConfig() {
		MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
		mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
		mapperScannerConfigurer.setBasePackage("top.nicelee.site.dao"); // 指定扫描包 top.nicelee.*.dao
		mapperScannerConfigurer.setAnnotationClass(Repository.class); // 制定扫描特定的注解类
		return mapperScannerConfigurer;
	}
}

@Alias(value = "user")
public class User {	
	Long id = null;
	String userName = null;
	Integer sex = null;
	String note = null;
	/* Setter + Getter */
}

package top.nicelee.site.dao;
import top.nicelee.site.pojo.User;
@Repository // 也可以是@Mapper，看个人喜好
public interface UserDao {	
	public User getUserById(Long id);
	public List<User> getUserAll();
	public int insertUser(User user);
	public int updateUser(User user);
	public int saveUser(User user);
	public int deleteUser(Long id);
}

<?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="top.nicelee.site.dao.UserDao">
	<select id="getUserAll" resultType="user"> 
		select id, user_name as userName, sex, note from t_user
	</select>
	<select id="getUserById" resultType="user" parameterType="long"> 
		select id, user_name as userName, sex, note from t_user where id= #{id}
	</select>	
	<insert id="insertUser" parameterType="user" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
	  insert into t_user (user_name, sex, note)
	  values (#{userName}, #{sex}, #{note})
	</insert>	
	<update id="updateUser" parameterType="user">
	    update t_user
		<set>
			<if test="id != null and userName != null">
				user_name = #{userName},
			</if>
			<if test="sex != null">
				sex = #{sex},
			</if>
			<if test="note != null">
				note = #{note},
			</if>
		</set>
		<where>
			<if test="id != null">
				id = #{id}
			</if>
			<if test="id == null">
				user_name = #{userName}
			</if>
		</where>
	</update>
	<delete id="deleteUser" parameterType="long">
	  delete from t_user where id = #{id}
	</delete>
	
	<insert id="saveUser" parameterType="user" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
	  insert into t_user
		<trim prefix="(" suffix=")" suffixOverrides=",">
		user_name, 
		<if test="sex != null">sex,</if>
		<if test="note != null">note,</if>
		</trim>
	    <trim prefix="values (" suffix=")" suffixOverrides=",">
	    #{userName},
	    <if test="sex != null">#{sex},</if>
	    <if test="note != null">#{note},</if>
	    </trim>
	  ON DUPLICATE KEY UPDATE
	  <trim prefix="" suffix="" suffixOverrides=",">
		<if test="sex != null">sex = #{sex},</if>
		<if test="note != null">note = #{note},</if>
		</trim>
	</insert>
</mapper>

@Controller
public class UserController {
	@Autowired
	UserDao userDao;	
	@RequestMapping("query/{id}")
	@ResponseBody
	public User index(@PathVariable Long id) {
		return userDao.getUserById(id);
	}	
	@RequestMapping("query")
	@ResponseBody
	public List<User> query() {
		return userDao.getUserAll();
	}	
	@RequestMapping("insert")
	@ResponseBody
	public String insert(User user) {
		int result = userDao.insertUser(user);
		return "insert请求生效: " + result;
	}	
	@RequestMapping("delete/{id}")
	@ResponseBody
	public String delete(@PathVariable Long id) {
		int result = userDao.deleteUser(id);
		return "delete请求生效: " + result;
	}	
	@RequestMapping("update")
	@ResponseBody
	public String update(User user) {

		int result = userDao.updateUser(user);
		return "update请求生效: " + result;
	}	
	@RequestMapping("save")
	@ResponseBody
	public String save(User user) {
		int result = userDao.saveUser(user);
		return "save请求生效: " + result;
	}
}