jdbcTypeForNull : JDBC타입을 파라미터에 제공하지 않을때 null값을 처리한 JDBC타입을 명시한다. 일부 드라이버는 칼럼의 JDBC타입을 정의하도록 요구하지만 대부분은 NULL, VARCHAR 나 OTHER 처럼 일반적인 값을 사용해서 동작한다.
mapUnderscoreToCamelCase : 전통적인 데이터베이스 칼럼명 형태인 A_COLUMN을 CamelCase형태의 자바 프로퍼티명 형태인 aColumn으로 자동으로 매핑하도록 함
3. jdbc datasource 설정
resources 밑에 application.yml 생성
내용
spring:
profiles:
active: local
---
# local development server
spring:
profiles: local
datasource:
jdbc-url: jdbc:postgresql://localhost:5432/dbName
driver-class-name: org.postgresql.Driver
username: test
password: test@!
4. DB 접속 및 sqlSession 설정
패키지 밑에 DataBaseConfig 생성
내용
package com.export.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
@Configuration
//////매퍼 xml 파일이 바라볼 패키지 설정
@MapperScan(value="com.export.mapper.*", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataBaseConfig {
/////application.yml에 설정한 정보를 가져와 dataSource 빈 생성
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean(
@Autowired @Qualifier("dataSource") DataSource dataSource,
ApplicationContext applicationContext) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean
.setConfigLocation(applicationContext.getResource("classpath:mybatis/mybatis-config.xml"));
factoryBean
.setMapperLocations(applicationContext.getResources("classpath:mybatis/mapper/*.xml"));
return factoryBean.getObject();
}
@Primary
@Bean(name = "sqlSession")
public SqlSessionTemplate sqlSession(
@Autowired @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Primary
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(
@Autowired @Qualifier("dataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
설정은 끝
매퍼 xml 파일 예시
resources > mybatis > 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="com.export.mapper.DataMapper">
<select id="select11" parameterType="DataDto" resultType="Integer">
SELECT COUNT(*)
FROM ${tableName}
WHERE ${dataTimeColumn} BETWEEN TO_TIMESTAMP(#{startTime},'YYYY-MM-DD') AND TO_TIMESTAMP(#{endTime},'YYYY-MM-DD')
</select>
</mapper>
<select id="selectByCareerYear" resultType="HashMap">
select user_career_year || '년'
,count(user_career_year) || '명'
from(select
trunc(months_between(sysdate,user_comp_enter)/12) user_career_year
from user_info)
where not user_career_year is NULL
group by user_career_year
order by user_career_year desc
</select>
keySet() 으로 확인해보니 key는 [COUNT(USER_CAREER_YEAR)||'명', USER_CAREER_YEAR||'년'] 이고
values() 로 확인해보니 values는 [1명,9년]
이렇게 뜨는데...
그럼 쿼리문에서 이름을 바꾸자..
as로 alias를 명명했다
<select id="selectByCareerYear" resultType="HashMap">
select user_career_year || '년' as year
,count(user_career_year) || '명' as count
from(select
trunc(months_between(sysdate,user_comp_enter)/12) user_career_year
from user_info)
where not user_career_year is NULL
group by user_career_year
order by user_career_year desc
</select>
