SpringBoot结合Mybatis

01.sql

DROP TABLE IF EXISTS `t_coffee`;
CREATE TABLE `t_coffee` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `price` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t_coffee
-- ----------------------------
BEGIN;
INSERT INTO `t_coffee` VALUES (1, 'espresso', '2022-11-04 12:17:50', '2022-11-04 12:17:50', 2000);
INSERT INTO `t_coffee` VALUES (2, 'latte', '2022-11-04 12:17:50', '2022-11-04 12:17:50', 2500);
INSERT INTO `t_coffee` VALUES (3, 'capuccino', '2022-11-04 12:17:50', '2022-11-04 12:17:50', 2500);
INSERT INTO `t_coffee` VALUES (4, 'mocha', '2022-11-04 12:17:50', '2022-11-04 12:17:50', 3000);
INSERT INTO `t_coffee` VALUES (5, 'macchiato', '2022-11-04 12:17:50', '2022-11-04 12:17:50', 3000);
INSERT INTO `t_coffee` VALUES (9, 'mocha2', '2022-11-04 13:54:55', '2022-11-04 13:54:55', 3000);
COMMIT;

02.pom.xml

    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.joda</groupId>
            <artifactId>joda-money</artifactId>
            <version>LATEST</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

03.配置

mybatis:
  type-handlers-package: cn.ccut.handler
  configuration:
    map-underscore-to-camel-case: true
    #   控制台打印sql日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:/mapper/*.xml
spring:
  datasource:
    url: jdbc:mysql://216.127.*.*:3766/db2?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: root

04.配置类

• 数据库中金额通常用long类型

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.joda.money.CurrencyUnit;
import org.joda.money.Money;

/**
 * 在 Money 与 Long 之间转换的 TypeHandler，处理 CNY 人民币
 */
public class MoneyTypeHandler extends BaseTypeHandler<Money> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Money parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setLong(i, parameter.getAmountMinorLong());
  }

  @Override
  public Money getNullableResult(ResultSet rs, String columnName) throws SQLException {
    return parseMoney(rs.getLong(columnName));
  }

  @Override
  public Money getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    return parseMoney(rs.getLong(columnIndex));
  }

  @Override
  public Money getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    return parseMoney(cs.getLong(columnIndex));
  }

  private Money parseMoney(Long value) {
    return Money.of(CurrencyUnit.of("CNY"), value / 100.0);
  }
}

05.实体类

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.joda.money.Money;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Coffee {
    private Long id;
    private String name;
    private Money price;
    private Date createTime;
    private Date updateTime;
}

06.mapper

import cn.ccut.model.Coffee;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CoffeeMapper {
    @Insert("insert ignore into t_coffee (name, price, create_time, update_time)"
            + "values (#{name}, #{price}, now(), now())")
    @Options(useGeneratedKeys = true, keyProperty="id", keyColumn="id")
    int save(Coffee coffee);

    @Select("select * from t_coffee where id = #{id}")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "create_time", property = "createTime"),
            // map-underscore-to-camel-case = true 可以实现一样的效果
            // @Result(column = "update_time", property = "updateTime"),
    })
    Coffee findById(@Param("id") Long id);

    @Select("select * from t_coffee where name like concat('%',#{es},'%')")
    List<Coffee> getInfoByName(@Param("es") String es);


    @Select(
            "  CREATE TABLE IF NOT EXISTS `demo11111111111` (\n" +
            "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
            "  `path` varchar(128) NOT NULL COMMENT '路径'\n" +
            "  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;\n"
           )
    void createTim();

    @Insert("insert ignore into t_coffee (name, price)"
            + "values (#{name}, #{price})")
    @Options(useGeneratedKeys = true, keyProperty="id", keyColumn="id")
    int save1(Coffee coffee);


    List<Coffee> selectByOne(@Param("list") List<Integer> id);

}

07.mapper.xml 类

<?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="cn.ccut.mapper.CoffeeMapper">



    <select id="selectByOne" resultType="cn.ccut.model.Coffee">
        select * from t_coffee
        <where>

            <if test="!@org.springframework.util.CollectionUtils@isEmpty(list)">
                and id in
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            </if>

        </where>
    </select>
</mapper>

08.测试类

import cn.ccut.mapper.CoffeeMapper;
import cn.ccut.model.Coffee;
import lombok.extern.slf4j.Slf4j;
import org.joda.money.CurrencyUnit;
import org.joda.money.Money;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@Slf4j
@MapperScan("cn.ccut.mapper")
public class MybatisDemoApplication implements ApplicationRunner {

  @Autowired
  private CoffeeMapper coffeeMapper;

  public static void main(String[] args) {
    SpringApplication.run(MybatisDemoApplication.class, args);
  }

  @Override
  public void run(ApplicationArguments args) throws Exception {
    Coffee c = Coffee.builder().name("espresso")
        .price(Money.of(CurrencyUnit.of("CNY"), 20.0)).build();
    int count = coffeeMapper.save1(c);
    log.info("Save {} Coffee: {}", count, c);

//    c = Coffee.builder().name("latte")
//        .price(Money.of(CurrencyUnit.of("CNY"), 25.0)).build();
//    count = coffeeMapper.save(c);
//    log.info("Save {} Coffee: {}", count, c);

    c = coffeeMapper.findById(c.getId());
    log.info("Find Coffee: {}", c);
    
    
    
    //        org.apache.commons.lang3.StringUtils.@isNotBlank()
        System.out.println(org.apache.logging.log4j.util.Strings.isNotBlank(" "));

        List<Integer> list=new ArrayList<>();
        list.add(2);
        list.add(2);

        list=list.stream().distinct().collect(Collectors.toList());
        List<Coffee> ff=  coffeeMapper.selectByOne(null);
        System.out.println(ff);
  }
}