쏘니의 개발블로그

spring 에서 mybatis 로 bulk upsert 를 해줘야 할때 아래와 같이 작업을 했다.

jpa 를 사용하면 saveall 메소드로 bulk upsert 가 가능했는데, mybatis 에서는 메소드를 직접 만들다 보니 그런게 없어서, 직접 만들어줘야 한다.

일반적으로는 pk 로 데이터 조회 후 없으면 insert 있으면 Update 를 해줘야 하나 매번 select 를 하기 db 커넥션도 아깝고 코드가 번거롭다.

하지만 mysql 에는 ON DUPLICATE KEY UPDATE 기능 이 있어 이미 데이터가 있다면 update 를 할 수 있다. 

해당 방식은 INSERT를 하다가 PK값이 존재하면 UPDATE를 한다는 조건이 있는데 이 조건을 만족하기 위해 테이블에 무조건 PK값이 지정이 되어있어야 정상적으로 동작한다.

ㅡ

Mapper

@Repository
public interface TestMapper {

    int upsertData(List<Data> dataList);

}

mybatis 쿼리

<insert id="upsertData" parameterType="Data">
        INSERT INTO 테이블명 (id, data_name, data_category)
        VALUES
        <foreach collection="list" index="index" item="item" separator=",">
            (
            #{item.id},
            #{item.dataName},
            #{item.dataCategory}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE building_name=VALUES(dataName), building_category=VALUES(dataCategory);
</insert>

parameterType 에는 List 객체 내부 클래스를 사용하고, collection 에 list 를 명시하고 다음과 같이 foreach 구문을 사용한다.

ON DUPLICATE KEY UPDATE 에는 pk를 제외한 update 쳐야 하는 컬럼명을 명시해준다.

ON DUPLICATE KEY UPDATE  도 for 문을 작성할 필요는 없다.

데이터 클래스

public class Data {
     private int id;
     private String dataName;
     private String dataCategory;
 }

사용예시

List<Data> list = new ArrayList<>();
list.add(data1);
list.add(data2);
list.add(data3);
~~

testMapper.upsertData(list);

위의 예시가 bulk upsert 이니깐, bulk insert 및 bulk update 는 ON DUPLICATE KEY UPDATE 키워드만 빼고, foreach 구문만 사용하면 된다.

참고문헌 > https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html [ON DUPLICATE KEY UPDATE 공식문서]

사용이유

datasource 커넥션을 로직이나 상황별로 나눠야 하는 상황이 올 수 도 있고, 어플리케이션단에서 샤딩을 구현해야 할 수 도 있고, read 커넥션 및 write 커넥션의 datasource 를 나눠야 하는 경우가 있다.

애초에 다른 datasoure 파일을 만들어도 되지만, 동적으로 쿼리 요청시마다 datasource 를 변경해야 할 때 AbstractRoutingDataSource 를 사용하면 된다.

AbstractRoutingDataSource란?

spring-jdbc 모듈에 포함되어 있는 클래스로, 여러 DataSource를 등록하고 특정 상황에 맞게 원하는 DataSource를 사용할 수 있도록 추상화한 클래스이다.

구현방식

AbstractRoutingDataSource는 이름 기반으로 다중 DataSource 정보를 등록하는 방법이다.

AbstractRoutingDataSource의

public void setTargetDataSources(Map<object, object=""> targetDataSource)를 호출하여

String:DataSource을 키:값으로하는 Datasource를 Map에 저장할 수 있다.

AbstractRoutingDataSource 의 determineCurrentLooupKey()를 오버라이드해서 상황에 맞게 Key를 반환하도록 구현해야 한다.

어떤 데이터 소스를 쓸지에 대한 결정은 determineCurrentLooupKey 메소드 내부에서 쿼리 호출시마다 동적으로 결정된다.

return 되는 String 결과값에 해당되는 datasource 를 실행하게 된다.

public class MyRoutingDataSource extends AbstractRoutingDataSource {
	@Override
	protected Object determineCurrentLookupKey() {
        boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        if (isReadOnly) {
            return "slave";
        } else {
            return "master";
        }	
	}
}

참고로 boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly(); 를 사용하려면

transactionManager 에 LazyConnectionDataSourceProxy 를 설정해 주어야 한다.

    @Bean("RoutingDataSource")
    public DataSource routingDataSource() {
        HikariDataSource masterDataSource = createDatasource(~~);
        HikariDataSource slaveDataSource = createDatasource(~~);

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("master", masterDataSource);
        dataSourceMap.put("slave", slaveDataSource);

        DynamicRoutingDataSource routingDataSource = new MyRoutingDataSource();
        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        return routingDataSource;
    }
    
    @Bean("routingLazyDataSource")
    public DataSource routingLazyDataSource(@Qualifier("RoutingDataSource") DataSource dataSource) {
        return new LazyConnectionDataSourceProxy(dataSource);
    }
    
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("routingLazyDataSource") DataSource dataSource) {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(dataSource);
        return transactionManager;
    }
    
    public HikariDataSource createDatasource() {
    	
        // 데이터 connection 정보
        ~~
        return new HikariDataSource(config);
    }

LazyConnectionDataSourceProxy 는 데이터소스를 한번 더 감싸는 역할을 해주어, LazyConnectionDataSourceProxy 로 여러 데이터 소스를 묶어 PlatformTransactionManager 에 datasource 를 세팅해야, tranaction 이 정확히 동작한다.

다음과 같이 PlatformTransactionManager 에 LazyConnectionDataSourceProxy 로 datasource 여러개를 감싼 datasource 를 set 해줘야 

TransactionSynchronizationManager.isCurrentTransactionReadOnly() 를 할 경우 정확히 readOnly 를 사용하는 쿼리를 캐치해서 분리 할 수 있다.

@Transactional(readOnly = true)
public List<Test> findTestList() {
	return testMapper.selectTestList();
}

다음과 같이 readOnly 가 true 인 경우는 isCurrentTransactionReadOnly 가 true 이므로 determineCurrentLookupKey 메소드에서 "slave" datasource 를 반환하게 된다.

만약에 위에서 만든 여러 datasource 를 LazyConnectionDataSourceProxy 로 감싸지 않으면 readOnly 가 true 인지를 알 수 가 없다.

위처럼도 사용가능하고, determineCurrentLookupKey 를 어떻게 구현하느냐에 따라 db 정보를 다르게 사용할 수 있다. 

spring boot 에서 mybatis 를 연동하는 법을 해보자

pom.xml

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

일단 pom.xml 에 mybatis 라이브러리부터 추가하자. dbms 에 맞는 driver 는 추가됐다고 가정하고 진행한다.

사용버젼 표

아래 표에 맞게 version 명시하면 된다.

spring boot 2.5 이상부터 mybatis-spring-boot-starter 2.2 버젼이 사용가능하다.

config 클래스

@Configuration
@MapperScan(value="com.juntcom.api.mapper.*", sqlSessionFactoryRef="dbMybatisSqlSessionFactory")
@Slf4j
public class DbMybatisConfig {
    @Autowired
    private Environment env;
 
    private static final String prefix = "spring.db.datasource.hikari.";
        
    @Bean(name = "dbMybatisSource", destroyMethod = "close")
    @Primary
    public HikariDataSource dbMybatisSource() {
    	HikariConfig config = new HikariConfig();
    	config.setUsername(env.getProperty(prefix+"username")); 
    	config.setPassword(env.getProperty(prefix+"password")); 
//    	config.setDriverClassName(env.getProperty(prefix+"driverClassName"));
    	config.setJdbcUrl( env.getProperty(prefix+"jdbc-url") );
    	config.setMaxLifetime( Long.parseLong(env.getProperty(prefix+"max-lifetime")) );
    	config.setConnectionTimeout(Long.parseLong( env.getProperty(prefix+"connection-timeout")));
    	config.setValidationTimeout(Long.parseLong( env.getProperty(prefix+"validation-timeout")));
    	
    	
    	config.addDataSourceProperty( "cachePrepStmts" ,  env.getProperty(prefix+"data-source-properties.cachePrepStmts"));
        config.addDataSourceProperty( "prepStmtCacheSize" , env.getProperty(prefix+"data-source-properties.prepStmtCacheSize"));
        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , env.getProperty(prefix+"data-source-properties.prepStmtCacheSqlLimit") );	
        config.addDataSourceProperty( "useServerPrepStmts" , env.getProperty(prefix+"data-source-properties.useServerPrepStmts") );
        
    	
    	HikariDataSource dataSource = new HikariDataSource( config );
        
        return dataSource;
    }
    
    @Bean(name = "dbMybatisSqlSessionFactory")
    @Primary
    public SqlSessionFactory dbMybatisSqlSessionFactory(@Qualifier("dbMybatisSource") DataSource dbMybatisSource) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dbMybatisSource);
//        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:sql/read/*.xml"));
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:sql/**/*.xml"));
        return sqlSessionFactoryBean.getObject();
    } 

    @Bean(name = "dbMybatisSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate dbMybatisSqlSessionTemplate(SqlSessionFactory dbMybatisSqlSessionTemplate) throws Exception {
        return new SqlSessionTemplate(dbMybatisSqlSessionTemplate);
    }


    
   
    @Bean(name="dbMybatisTransactionManager")
    @Primary
    public PlatformTransactionManager dbMybatisTransactionManager(@Qualifier("dbMybatisSource") DataSource dbMybatisSource) {
  
    	DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
    	transactionManager.setDataSource(dbMybatisSource);
        return transactionManager;
    }
}

@MapperScan(value="com.juntcom.api.mapper.*", sqlSessionFactoryRef="dbMybatisSqlSessionFactory")

@MapperScan -> mapper 클래스를 놓을 클래스 경로가 맞아야 한다. 

mapper 라는 패키지 하위에 mapper interface 를 위치 시켜야 mapper 를 스캔한다.

private static final String prefix = "spring.db.datasource.hikari."; 

라고 선언부가 application.yml 파일에서 가져오기 위한 prefix 이다. 따로 설정하지 않고, class 파일 내부에서 작성해도 되나, 설정값을 한눈에 보기 위해 application.yml 에 기입하는게 낫다.

("classpath:sql/**/*.xml"));

resources 하위에 sql 폴더 생성 후 그 밑에 xml 파일을 만들자.

경로는 sql/*.xml 로 sql 폴더 바로 밑에 xml 파일 두고 싶은경우는 다음과 같이 하자. 

나 같은 경우는 read 폴더 및 writer 폴더를 나누는게 좋아서 /**/ 를 추가했다.

참고

datasource 를 하나만 둘 거면 yml 에 설정값을 추가함으로써 db connection 을 할 수 있지만,

java 파일에서 한 이유는

여러개의 db 를 사용할 경우 multi datasource 를 사용하기 위함이다.

yml 에 db1, db2 라고 추가해 java class 파일을 별도로 

class1 {

  private static final String prefix = "spring.db1.datasource.hikari."; 

  ...

}

class2 {

  private static final String prefix = "spring.db2.datasource.hikari."; 

  ...

}

와 같이 class 를 별도로 두면 된다.

application.yml

spring:
  db: #db1 이든 db2 이든 상관없다. java class 의 prefix 의 값과 맞춰주면 된다.
    datasource:
      hikari:
        jdbc-url: jdbc:mysql://localhost:3306?&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
        username: root
        password: 비밀번호
        driverClassName: com.mysql.cj.jdbc.Driver
        maximum-pool-size: 10
        #minimum-idle: 100
        max-lifetime: 1800000 #1800000
        connection-timeout: 30000 #30000
        validation-timeout: 5000
        #connection-test-query: SELECT 1
        data-source-properties:
          cachePrepStmts: true
          prepStmtCacheSize: 250
          prepStmtCacheSqlLimit: 2048
          useServerPrepStmts: true

위까지만 하면 datasource 가 뜨기까지의 설정은 되었으나, mapper 클래스 및 resource 하위에 쿼리가 비어있기 때문에 샘플 하나는 만들어야 어플리케이션이 뜬다.

mapper 클래스

다음과 같이 위치시켜주자. 패키지명을 달리하려면 config 클래스에서 MapperScan 의 값을 변경하자.

@Mapper
public interface ReadMapper {
    List<Map<String, Object>> selectCode() throws Exception;
}

. 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="com.juntcom.api.mapper.read.ReadMapper">

    <select id="selectCode" resultType="map">
		SELECT
		    *
		FROM
            TEST
    </select>


</mapper>

참고문헌

> https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/ [mybatis spring boot starter 공식문서]

firebase 가 클라이언트 사이드에서 구현하기 위한 severless db 로 많이 쓰이는데, 

나 같은 경우는 db 를 구축하기 일단 비용 및 인프라가 없어서 간단히 만들려고 firebase 를 서버에서 사용하기 위한 db 로 일단은 선택했다.

일정량 이하는 free 로 사용할 수 있다.

firebase 프로젝트 생성

대쉬보드에서 프로젝트 추가를 통해 프로젝트를 생성

console.firebase.google.com/

firestore Database 를 생성하자.

주의 할 것은 realtime database 와 firestore database 는 다르다.

다른 점으로는 과금 정책도 다르고, realtime 이 더 비싸다고 한다.

둘의 차이점은 아래 링크 문서에 있다.

firebase.google.com/docs/database/rtdb-vs-firestore?hl=ko

프로젝트 설정 에서 account 관련 키 파일을 다운받아야 한다.

프로젝트 개요 > 프로젝트 설정 > 서비스 계정 > Firebase Admin SDK 

에서 자바 를 클릭 후 

새 비공개 키 생성 으로 파일을 다운받자. 

다운 받으면 ~~~~.json 파일로 다운받아진다.

스프링 spring boot 코드 

pom.xml 라이브러리 다운

<dependency>
	<groupId>com.google.firebase</groupId>
	<artifactId>firebase-admin</artifactId>
	<version>6.11.0</version>
</dependency>

스프링 init 코드

@Service
public class FirebaseInitialize {

    @PostConstruct //has to be Run during the Start of
    public void initialize() {
        try {
            FileInputStream serviceAccount =
                    new FileInputStream("src/main/resources/serviceAccountKey.json");

            FirebaseOptions options = new FirebaseOptions.Builder()
                    .setCredentials(GoogleCredentials.fromStream(serviceAccount))
                    .setDatabaseUrl("https://{{데이터베이스명}}.firebaseio.com")
                    .build();

            FirebaseApp.initializeApp(options);
        } catch (Exception e){
            e.printStackTrace();
        }
    }

}

위에서 다운받은 json 파일을 이름을 변경해주고 resources 하위 경로에 넣자.

databaseUrl 은 따로 나오는 곳은 없어 보였는데, 프로젝트 명 에 뒤에 .firebaseio.com 을 붙이면 된다.

데이터베이스명은 firestore 조회 화면에 바로 나온다.

데이터 샘플로 넣고 조회하기

@Slf4j
@Service
public class FirebaseService {

    public static final String COLLECTION_NAME = "user";

    public void insertUser() throws Exception {

        Firestore db = FirestoreClient.getFirestore();
        User user = new User();
        user.setId("4444");
        user.setName("4444");
        ApiFuture<WriteResult> apiFuture = db.collection(COLLECTION_NAME).document("user_4").set(user);

        log.info(apiFuture.get().getUpdateTime().toString());
    }


    public void selectUser() throws Exception {

        Firestore db = FirestoreClient.getFirestore();
        User user = null;
        ApiFuture<DocumentSnapshot> apiFuture = db.collection(COLLECTION_NAME).document("user_4").get();
        DocumentSnapshot documentSnapshot = apiFuture.get();
        if(documentSnapshot.exists()) {
            user = documentSnapshot.toObject(User.class);
            log.info(user.toString());
        }
    }
}

User 라는 pojo 클래스를 만들고 넣어줘도 되고 HashMap 으로 만들어서 넣어줘도 된다.

collection 과 document 라는 개념이 몽고db 와 흡사하다.

collection 이라는 개념은 db와 같고, 하위의 document 가 테이블 과 개념이 같다. 조금 다른 점이 있다면 document 가 하위로 계속 들어갈 수 있다.

사용 

    @Resource
    FirebaseService firebaseService;

    @Test
    public void 테스트() throws Exception {
        firebaseService.insertUser();
        firebaseService.selectUser();
    }

이 정도 코드는 사실 안남겨도 되는데, 혹시나.

결과 

나 같은 경우는 저 코드를 작성했는데 한참 동작하지 않아서 이것저것 삽질을 조금 했다.

나중에 시간이 지나고 되는걸 보면 database 를 생성하고 일정시간이 지나야 되는거 같긴 한데, 이건 확실치 않다.

혹시 안되는 사람은 2시간 정도 경과하고 한번 다시 해보도록

혹시나 권한 때문에 안되는 사람은 규칙(rule)을 확인하자.

production 모드로 한 사람은 규칙에서 test 모드로 변경하면 된다.

2021 년 6월 3일 까지는 read write 권한이 된다는 코드이다.

외부에 물리db 를 생성하기 어려울떄가 있다.

나 같은 경우 사이드 프로젝트를 하고 싶은데 물리 db 를 생성하기 부담스러울때 간단히 내부 db 를 사용하고 싶어 인메모리 h2 를 사용한다.

h2 는 보통 프로덕션보다는 보통 test 용으로 사용한다.

왜냐면 휘발성이기 때문이다.

• 디스크가 아닌 주 메모리에 모든 데이터를 보유하고 있는 데이터베이스입니다. 
• 디스크 검색보다 자료 접근이 훨씬 빠른 것이 큰 장점입니다. 단점은 매체가 휘발성이기 때문에 DB 서버가 꺼지면 모든 데이터가 유실된다는 단점이 있습니다.
• 스프링 부트에서 H2, HSQL 같은 인메모리, 디스크 기반 DB를 지원합니다.

라이브러리 추가

현재 spring boot 버젼 2.4.5

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

• H2 데이터베이스 의존성을 추가하고 난 후, 설정 파일에 아무 설정이 되어 있지 않으면 스프링 부트는 자동적으로 H2 데이터베이스를 기본 데이터베이스로 선택한다
• spring-boot-starter-jdbc 의존성을 추가하면 DataSource, JdbcTemplate을 별다른 설정없이 @Autowired 같은 빈 주입 어노테이션만 가지고도 쓸 수 있다.

@Component
public class H2Runner implements ApplicationRunner {

    @Autowired
    DataSource dataSource;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void run(ApplicationArguments args) throws Exception {

        try(Connection connection = dataSource.getConnection()){
            System.out.println(connection);
            String URL = connection.getMetaData().getURL();
            System.out.println(URL);
            String User = connection.getMetaData().getUserName();
            System.out.println(User);

            Statement statement = connection.createStatement();
            String sql = "CREATE TABLE USER(ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID) )";
            statement.executeUpdate(sql);
        }

        jdbcTemplate.execute("INSERT INTO USER VALUES(1, 'testuser')");
    }
}

위의 코드 까지만 작성하면 h2 가 정상적으로 동작하는 지 확인 가능하다.

아래 기본 ddl sql 로드 방법을 사용하면 위의 자바 class 는 필요가 없다.

spring:
  h2:
    console:
      enabled: true
      path: /h2-console

다음과 같이 yml 에 설정하면 웹에서 sql 접근 가능

localhost:8080/h2-console   로 접근 가능

별도의 yml 로 디비 접속 설정을 안하면 

connection.getMetaData().getURL()  -> JDBC URL

connection.getMetaData().getUserName() -> User Name, 디폴트값 : sa

로 로그 찍은 값으로 접속 가능하다.

디폴트 값으로 하지 않고 yml 에서 설정하려면

datasource:
    url: jdbc:h2:mem:testdb
    username: sa

다음과 같이 작성하면 된다.

기본 ddl sql script 로드

기본적으로 jpa 와 h2 라이브러리 import 하는 거 만으로 schema.sql 과 data.sql 를 초기에 로딩 할 수 있다.

스키마 파일은 

schema.sql  로 명명 하고

resources > 하위에 schema.sql 을 두면 초기 구동 되면서 sql 문을 실행한다.

CREATE TABLE Employee
(
    id integer NOT NULL,
    firstName varchar(255) not null,
    lastName varchar(255) not null,
    email varchar(255),
    phoneNumber varchar(255),
    hireDate timestamp,
    salary integer,
    commissionPct integer,
    primary key(id)
);

data 삽입도 같이 data.sql 파일안에 insert 문을 넣어주면 된다.

별도로 해줘야 할 작업은 application.yml 파일에

spring:
  jpa:
    hibernate:
      ddl-auto: none

같이 ddl-auto 를 none 으로 설정해야 resources 하위의 sql 문을 실행한다.

지금 하는 sql 초기 구동시 로딩을 해주면 위에서 작성한 H2Runner.class 를 따로 생성하지 않아도 된다.

결과

최종 yml 파일

spring:
  h2:
    console:
      enabled: true
      path: /h2-console
  jpa:
    hibernate:
      ddl-auto: none
  datasource:
    url: jdbc:h2:mem:testdb
    username: sa

세팅 옵션

- autoCommit:

auto-commit설정 (default: true)

- connectionTimeout:

pool에서 커넥션을 얻어오기전까지 기다리는 최대 시간, 허용가능한 wait time을 초과하면 SQLException을 던짐. 설정가능한 가장 작은 시간은 250ms (default: 30000 (30s))

- idleTimeout:

pool에 일을 안하는 커넥션을 유지하는 시간. 이 옵션은 minimumIdle이 maximumPoolSize보다 작게 설정되어 있을 때만 설정. pool에서 유지하는 최소 커넥션 수는 - - - - minimumIdle (A connection will never be retired as idle before this timeout.). 최솟값은 10000ms (default: 600000 (10minutes))

- maxLifetime:

커넥션 풀에서 살아있을 수 있는 커넥션의 최대 수명시간. 사용중인 커넥션은 maxLifetime에 상관없이 제거되지않음. 사용중이지 않을 때만 제거됨. 풀 전체가아닌 커넥션 별로 적용이되는데 그 이유는 풀에서 대량으로 커넥션들이 제거되는 것을 방지하기 위함임. 강력하게 설정해야하는 설정 값으로 데이터베이스나 인프라의 적용된 connection time limit보다 작아야함. 0으로 설정하면 infinite lifetime이 적용됨(idleTimeout설정 값에 따라 적용 idleTimeout값이 설정되어 있을 경우 0으로 설정해도 무한 lifetime 적용 안됨). (default: 1800000 (30minutes))

- connectionTestQuery:

JDBC4 드라이버를 지원한다면 이 옵션은 설정하지 않는 것을 추천. 이 옵션은 JDBC4를 지원안하는 드라이버를 위한 옵션임(Connection.isValid() API.) 커넥션 pool에서 커넥션을 획득하기전에 살아있는 커넥션인지 확인하기 위해 valid 쿼리를 던지는데 사용되는 쿼리 (보통 SELECT 1 로 설정) JDBC4드라이버를 지원하지않는 환경에서 이 값을 설정하지 않는다면 error레벨 로그를 뱉어냄.(default: none)

- minimumIdle:

아무런 일을 하지않아도 적어도 이 옵션에 설정 값 size로 커넥션들을 유지해주는 설정. 최적의 성능과 응답성을 요구한다면 이 값은 설정하지 않는게 좋음. default값을 보면 이해할 수있음. (default: same as maximumPoolSize)

- maximumPoolSize:

pool에 유지시킬 수 있는 최대 커넥션 수. pool의 커넥션 수가 옵션 값에 도달하게 되면 idle인 상태는 존재하지 않음.(default: 10)

- poolName:

이 옵션은 사용자가 pool의 이름을 지정함. 로깅이나 JMX management console에 표시되는 이름.(default: auto-generated)

- initializationFailTimeout:

이 옵션은 pool에서 커넥션을 초기화할 때 성공적으로 수행할 수 없을 경우 빠르게 실패하도록 해준다. 상세 내용은 한국말보다 원문이 더 직관적이라 생각되어 다음 글을 인용함.

- readOnly:

pool에서 커넥션을 획득할 때 read-only 모드로 가져옴. 몇몇의 database는 read-only모드를 지원하지 않음. 커넥션이 read-only로 설정되어있으면 몇몇의 쿼리들이 최적화 됨.(default: false)

- driverClassName:

HikariCP는 jdbcUrl을 참조하여 자동으로 driver를 설정하려고 시도함. 하지만 몇몇의 오래된 driver들은 driverClassName을 명시화 해야함. 어떤 에러 메시지가 명백하게 표시 되지않는다면 생략해도됨.

- validationTimeout:

valid 쿼리를 통해 커넥션이 유효한지 검사할 때 사용되는 timeout. 250ms가 설정될 수 있는 최솟값(default: 5000ms)

- leakDetectionThreshold:

커넥션이 누수 로그메시지가 나오기 전에 커넥션을 검사하여 pool에서 커넥션을 내보낼 수 있는 시간. 0으로 설정하면 leak detection을 이용하지않음. 최솟값 2000ms (default: 0)

MySQL Performance Tips

- prepStmtCacheSize:

커넥션당 캐시할 prepared statementsts 숫자. 250 ~ 500 개 추천

- prepStmtCacheSqlLimit:

드라이버가 캐시할 sql문의 최대길이. 기본값은 256. Hibernate 와 같은 ORM 프레임워크의 기본값은 낮다. 권장 설정은 2048 이다.

- cachePrepStmts:

캐시가 기본적으로 비활성화 되어있는 경우 위의 매개 변수 는 효과가 없어진다. 이 매개변수를 true 로 해야 설정이 된다.

- useServerPrepStmts: 최신버젼의 MySql 은 서버 측 명령문을 지원하므로 상당한 성능 향상을 제공할 수 잇다. 이 속성을 true 로 해야 한다.

참고문헌

> https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby

ElasticsearchRepository search 메소드 내부에 NativeSearchQuery 를 넘겨 사용 가능하다.

또는 ElasticsearchTemplate 및 elasticsearchOperations 을 사용할떄도 NativeSearchQuery 를 사용할 수 있다.

쿼리 클래스의 종류로는 

CriteriaQuery, StringQuery, NativeSearchQuery

NativeSearchQuery 란 

복잡한 쿼리를 사용하려 할때 Criteria 로는 표현이 불가할때 사용한다.

CriteriaQuery 와 StringQuery 의 단점을 보완한 클래스이다.

ElasticsearchRepository 를 상속받아 사용하는 방식

@Repository
public interface PersonRepository extends ElasticsearchRepository<Person, String> {

}

또는 

ElasticsearchOperations elasticsearchOperations 을 사용하여 사용가능하다.

@Resource
ElasticsearchOperations elasticsearchOperations;

@Resource
PersonRepository personRepository;

두 클래스 중 하나를 사용하면 된다. elasticsearchOperations 은 elasticsearchTemplate 을 구현한 것이라 같게 봤다.

쿼리메소드 종류

matchQuery

//    GET /person/_search
//    {
//        "query": {
//        	"age": 30
//    	   }
//    }
// age 가 30 인 데이터
PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
MatchQueryBuilder matchQueryBuilder = QueryBuilders.matchQuery("age", 30);

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(matchQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();

String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = sampleRepository.search(nativeSearchQuery).getContent();
// 쿼리스트링으로도 가능

termsQuery

//{
//  "query": {
//    "terms": {
//      "firstname": ["vera", "kari"]
//    }
//  }
//}

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
TermsQueryBuilder termsQueryBuilder = QueryBuilders.termsQuery("firstname", "vera", "kari", "blake");
//term 
//TermsQueryBuilder termsQueryBuilder = QueryBuilders.termsQuery("firstname", "Vera", "Kari", "Blake");
NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(termsQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

multiMatchQuery

// id 와 age 가 44 인 것 찾기

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
MultiMatchQueryBuilder multiMatchQueryBuilder = QueryBuilders.multiMatchQuery("44", "id", "age");
NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(multiMatchQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

BoolQueryBuilder

 PageRequest pageRequest = PageRequest.of(0, 50);
 FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
 BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery()
 .must(QueryBuilders.matchQuery("firstname", "Effie"))
 .mustNot(QueryBuilders.matchQuery("gender", "M"))
 .should(QueryBuilders.matchQuery("balance", "3607"));

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(boolQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

IdsQueryBuilder

//    GET /person/_search
//    {
//        "query": {
//        "ids": {
//            "values": [
//            "25",
//                    "44",
//                    "126"
//      ]
//        }
//    }
//    }
PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
IdsQueryBuilder idsQueryBuilder = QueryBuilders.idsQuery().addIds("25", "44", "126");
NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(idsQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

DisMaxQueryBuilder

//    GET /person/_search
//    {
//        "query": {
//        "dis_max": {
//            "tie_breaker": 0.7,
//                    "boost": 1.2,
//                    "queries": [
//            {
//                "term": {
//                "firstname": {
//                    "value": "Effie"
//                }
//            }
//            },
//            {
//                "match": {
//                "gender": "m"
//            }
//            }
//      ]
//        }
//    }
//    }

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

DisMaxQueryBuilder disMaxQueryBuilder = QueryBuilders.disMaxQuery()
.add(QueryBuilders.termQuery("firstname", "Effie"))
.add(QueryBuilders.matchQuery("gender", "m"))
.boost(1.2f)
.tieBreaker(0.7f);

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(disMaxQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();
    

FuzzyQueryBuilder

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

FuzzyQueryBuilder fuzzyQueryBuilder = QueryBuilders.fuzzyQuery("firstname", "effie");

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(fuzzyQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

prefixQuery

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

PrefixQueryBuilder prefixQueryBuilder = QueryBuilders.prefixQuery("firstname", "b");
NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(prefixQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

rangeQuery

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("age").from(20).to(30).includeLower(true).includeUpper(false);
NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(rangeQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

PageRequest pageRequest = PageRequest.of(0, 50);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);
//address: "398 Dearborn Court",  有3个term  查询court 结束的位置为 3
SpanFirstQueryBuilder spanFirstQueryBuilder = QueryBuilders.spanFirstQuery(QueryBuilders.spanTermQuery("address", "court"), 3);
NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(spanFirstQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

boolQuery

PageRequest pageRequest = PageRequest.of(0, 10);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery()
.must(QueryBuilders.matchQuery("gender", "f"))
.should(QueryBuilders.termQuery("address", "court"))
.should(QueryBuilders.termQuery("state","md"))
.filter(QueryBuilders.rangeQuery("age").gte(30))
.filter(QueryBuilders.rangeQuery("balance").gte(2726));

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(boolQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
List<Person> content = personRepository.search(nativeSearchQuery).getContent();

/**
     *
     *
    GET /person/_search
    {
        "query": {
            " function_score": {
                "query": {
                    "match": {
                        "gender": "F"
                    }
                },
                "field_value_factor": {
                    "field": "balance",
                            "modifier": "log1p",
                            "factor": 0.5
                }
          , "boost_mode": "sum"
            }
        }
    }*/
    
PageRequest pageRequest = PageRequest.of(0, 10);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

//bool query
BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery().must(QueryBuilders.matchQuery("gender", "F"));

//feild_value_factor
FieldValueFactorFunctionBuilder functionBuilder = ScoreFunctionBuilders.fieldValueFactorFunction("balance")
.modifier(FieldValueFactorFunction.Modifier.LOG1P)
.factor(0.5f);

FunctionScoreQueryBuilder functionScoreQueryBuilder = QueryBuilders.functionScoreQuery(boolQueryBuilder, functionBuilder).boostMode(CombineFunction.SUM);

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(functionScoreQueryBuilder)
.withPageable(pageRequest)
.withSort(idSortBuilder)
.build();
String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
Page<Person> page = personRepository.search(nativeSearchQuery);
logger.info("page: "+page.toString());
List<Person> content = page.getContent();

functionScoreQuery

/**

     GET /person/_search
     {
         "query": {
             "function_score": {
                 "query": {
                     "match": {
                         "gender": "F"
                    }
             },
             "gauss": {
                 "balance": {
                     "origin": "43951",
                     "scale": "100",
                     "offset": "10"
                 }
             },
             "boost_mode": "sum"
             }
         }
     }
     */
PageRequest pageRequest = PageRequest.of(0, 10);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

//bool query
BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery().must(QueryBuilders.matchQuery("gender", "F"));
GaussDecayFunctionBuilder gaussDecayFunctionBuilder = ScoreFunctionBuilders.gaussDecayFunction("balance", "43951", "100", "10");

//builder
FunctionScoreQueryBuilder functionScoreQueryBuilder = QueryBuilders.functionScoreQuery(boolQueryBuilder, gaussDecayFunctionBuilder).boostMode(CombineFunction.SUM);

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(functionScoreQueryBuilder)
.withPageable(pageRequest)
//.withSort(idSortBuilder)
.build();

String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
Page<Person> page = personRepository.search(nativeSearchQuery);
logger.info("page: "+page.toString());
List<Person> content = page.getContent();

/**   function_score weight query
    GET /person/_search
    {
        "query": {
            "function_score": {
                    "query": {
                        "bool": {
                            "must": [
                            {
                                "match": {
                                "gender": "F"
                            }
                            },{
                                "range": {
                                    "age": {
                                        "gte": 25,
                                                "lte": 30
                                    }
                                }
                            }
                  ]
                        }
                    },
                    "functions": [
                    {
                        "weight": 2
                    }
                ]
            }
        }
    }
     **/
PageRequest pageRequest = PageRequest.of(0, 10);
FieldSortBuilder idSortBuilder = SortBuilders.fieldSort("id").order(SortOrder.ASC);

//bool query
BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery()
.must(QueryBuilders.matchQuery("gender", "F"))
.must(QueryBuilders.rangeQuery("age").gte(25).lte(30));

//weight buil
WeightBuilder weightBuilder = ScoreFunctionBuilders.weightFactorFunction(2.0f);

//builder
FunctionScoreQueryBuilder functionScoreQueryBuilder = QueryBuilders.functionScoreQuery(boolQueryBuilder, weightBuilder).boostMode(CombineFunction.SUM);

NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder()
.withQuery(functionScoreQueryBuilder)
.withPageable(pageRequest)
//.withSort(idSortBuilder)
.build();

String query = nativeSearchQuery.getQuery().toString();
logger.info(query);
Page<Person> page = personRepository.search(nativeSearchQuery);
logger.info("page: "+page.toString());
List<Person> content = page.getContent();

엘라스틱 을 조회할떄 네이밍에 언더바(언더스코어)가 들어간 경우 일반적인 방법으로 ElasticsearchRepository 라이브러리로 조회가 가능하지 않다.

EX) name_field : '데이터'

이거 오류 해결 방법 좀 알고 싶었으나 지지고 볶아도 오류해결 방법이 없다. 

애초에 네이밍을 카멜로 했거나 그게 안되면 ElasticsearchOperations 을 사용해서 엘라스틱 조회를 해야한다.

간편하게 사용하기에는 아무래도 ElasticsearchRepository 가 jpa 랑 같아 사용하기 편한데, 복잡한 쿼리에는 불편한 점이 따르니 어쩔 수 없다.

굳이 언더바를 검색하려면 방법은 있다.

@Repository
public interface SampleRepository extends ElasticsearchRepository<SampleData, String> {

    @Query("{\"bool\": {\"must\": {\"match\": {\"name_field\": \"?0\"}}}}")
    List<SampleData> findByName(String name);
}

이런 식으로 네이티브 쿼리를 보내는 방법 밖에 알 수가 없다.

하지만 이렇게 할거면 굳이 ElasticsearchRepository 를 쓰는 이유가 있을까 싶다. 그냥 ElasticsearchOperations 로만 사용해도 된다.

또는 ElasticsearchRepository 에서 search 메소드에 queryBuilder 를 넣어서 ElasticsearchOperations처럼 사용해도 된다.

새로 업데이트 된 엘라스틱에서는 가능해졌는지는 모르겠지만,

내가 사용중인 버젼 spring boot 2.1.2, 

spring-data-elasticsearch:3.1.4 에서는 가능하지 않다.

아마 상위버젼에서도 불가 할 것이다.

jpa 에서는 @Colunm(name="name_field") 이런식으로 해결 가능한데, elastic 에는 이러한 어노테이션이 존재하지가 않아 되지 않는다.

구글링을 하면 @@JsonProperty("name_field") 이렇게 해결하라는 글이 하나 있는데, 동작하지 않는다. 찾아보면 다 동작이 안된다는 말이 더 많다.

처음 엘라스틱 데이터를 만들때 언더바, 언더스코어 등을 네이밍에 사용하지 말고 카멜케이스로 만들자..

참고

stackoverflow.com/questions/50528299/query-elasticsearch-using-spring-data-elasticsearch-with-fields-containing-under