[JAVA] [Spring] [MyBatis] Use GROUP BY in SQL Builder



Suppose you have the following table "shops"

shop_cd prefecture
0001 Kanagawa Prefecture
0002 Kanagawa Prefecture
0002 Kanagawa Prefecture
0003 Kanagawa Prefecture
0003 Kanagawa Prefecture
0004 Tokyo

I want a unique list of store codes that specify "Kanagawa Prefecture".

public interface ShopMapper {
   *Get a list of store codes by specifying a prefecture.
   * @return List<Map<String, String>>
   *Map per result
   *     - prefecture:Specified prefecture name (Japanese)
   *     - shopCd:site code
  @SelectProvider(type = SqlProvider.class, method = "getShopCodesByPrefecture")
  @Results(id = "ShopCodesByPrefecture", value = {
          @Result(column = "shop_cd", property = "shopCd"),
          @Result(column = "prefecture", property = "prefecture"),
  List<Map<String, String>> getShopCodesByPrefecture(String brandCd, String prefecture);

  class SqlProvider {
    public String getShopCodesByPrefecture(String prefecture) {
      SQL sql = new SQL() {
          SELECT("shop_cd", "prefecture");
          WHERE("shops.prefecture = #{prefecture}");
          ORDER_BY("shop_cd ASC");
      return sql.toString();

I specified @Results and @Result so that the search results can be obtained on the Map.

Recommended Posts

[JAVA] [Spring] [MyBatis] Use GROUP BY in SQL Builder
Use inequality comparison operators in MyBatis SQL
Use Interceptor in Spring
Use OpenCV in Java
Use PreparedStatement in Java
Use Redis Stream in Java
How to call and use API in Java (Spring Boot)
How to use Java enums (Enum) in MyBatis Mapper XML
How to use Lombok in Spring
Let's use Twilio in Java! (Introduction)
[Java] Do not use "+" in append!
Use composite keys in Java Map.
How to use classes in Java?
Do you use Stream in Java?
[Java Spring MVC] I want to use DI in my own class
Multilingual Locale in Java How to use Locale
Use OpenCV_Contrib (ArUco) in Java! (Part 2-Programming)
Duplicate Map sorted by key in Java
Step by Step: Java + gradle + mybatis + postgresql + log4j
Create Java Spring Boot project in IntelliJ
Use "Rhino" which runs JavaScript in Java
Use DynamoDB query method in Spring Boot
Switching beans by profile annotation in Spring
[* Java *] I participated in JJUG CCC 2019 Spring
Spring Java
Use OpenCV_Contrib (ArUco) in Java! (Part 1-Build) (OpenCV-3.4.4)
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
[Java] Use of final in local variable declaration
Use Servlet filter in Spring Boot [Spring Boot 1.x, 2.x compatible]
Java tips-Create a Spring Boot project in Gradle
[Java] Judgment by entering characters in the terminal
Method name of method chain in Java Builder + α
Why use setters/getters instead of public/private in Java
Replacing system environment variables by reflection in java