중요 학습 내용 [MyBatis]
MyBatis
- Singleton: 1개를 만드는데에 resource가 많이 든다.
- Factory Pattern: builder가 config 정보를 기반으로 factory를 singleton하게 만듦
Connection config
JAVA 방식
-
Commit setting
- JdbcTransactionFactory: 수동 커밋 (2개 이상의 query가 생성될 것을 고려)
- ManagedTransactionFactory: 자동 커밋
-
Pool setting
- PooledDataSource: ConnectionPool 사용
- UnpooledDataSource: ConnectionPool 미사용
// Application.java
public class Application {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/test";
private static String user = "test";
private static String password = "test";
public static void main(String[] args) {
Environment environment = new Environment(
"dev",
new JdbcTransactionFactory(),
new PooledDataSource(driver, url, user, password)
);
Configuration configuration = new Configuration(environment);
configuration.addMapper(Mapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
SqlSession session = sqlSessionFactory.openSession(false); // 수동 커밋
Mapper mapper = session.getMapper(Mapper.class);
String date = mapper.selectNow();
session.close();
}
}// Mapper.java
public interface Mapper {
@Select("SELECT NOW()")
DATE selectNow();
}XML 방식
// Application.java
public class Application {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession(false);
Date date = session.selectOne("mapper.selectNow");
System.out.println("date = " + date);
session.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
<!-- 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="mapper">
<select id="selectNow" resultType="java.util.Date">
SELECT NOW()
</select>
</mapper><!-- mybatis-config.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="test"/>
<property name="password" value="test"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper.xml"/>
</mappers>
</configuration>remix 방식
-
이론적으로 정립된 바는 없으나, java 방식과 xml 방식의 혼용으로 사용
-
java 방식의 config와 xml 방식의 query를 사용
-
Config 설정
// Template.java
public class Template {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/test";
private static String user = "test";
private static String password = "test";
private static SqlSessionFactory sqlSessionFactory;
public static SqlSession getSqlSession() {
if(sqlSessionFactory == null) {
Environment environment =
new Environment("dev"
, new JdbcTransactionFactory()
, new PooledDataSource(driver, url, user, password));
Configuration configuration = new Configuration(environment);
configuration.addMapper(MenuMapper.class);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
}
return sqlSessionFactory.openSession(false);
}
}- 매퍼 인터페이스 설정
// MenuMapper.java
public interface MenuMapper {
List<MenuDTO> selectAllMenus();
MenuDTO selectMenu(int menuCode);
int insertMenu(MenuDTO menu);
int updateMenu(MenuDTO menu);
int deleteMenu(int menuCode);
}- 인터페이스와 같은 tree level에 인터페이스와 같은 이름으로 mapper 생성
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="MenuMapper">
<resultMap id="menuResultMap" type="classpath.MenuDTO">
<id property="menuCode" column="MENU_CODE"/>
<result property="menuName" column="MENU_NAME"/>
<result property="menuPrice" column="MENU_PRICE"/>
<result property="categoryCode" column="CATEGORY_CODE"/>
<result property="orderableStatus" column="ORDERABLE_STATUS"/>
</resultMap>
<select id="selectAllMenus" resultMap="menuResultMap">
SELECT
MENU_CODE
, MENU_NAME
, MENU_PRICE
, CATEGORY_CODE
, ORDERABLE_STATUS
FROM TBL_MENU;
</select>
<!-- <select id="selectMenu" resultMap="menuResultMap" parameterType="integer">-->
<select id="selectMenu" resultMap="menuResultMap" parameterType="_int">
SELECT
MENU_CODE
, MENU_NAME
, MENU_PRICE
, CATEGORY_CODE
, ORDERABLE_STATUS
FROM TBL_MENU
WHERE MENU_CODE = #{ menuCode };
</select>
<insert id="insertMenu" parameterType="classpath.MenuDTO">
INSERT
INTO TBL_MENU
(
MENU_NAME
, MENU_PRICE
, CATEGORY_CODE
, ORDERABLE_STATUS
)
VALUES
(
#{ menuName }
, #{ menuPrice }
, #{ categoryCode }
, 'Y'
)
</insert>
<update id="updateMenu" parameterType="classpath.MenuDTO">
UPDATE
TBL_MENU
SET MENU_NAME = #{ menuName }
, MENU_PRICE = #{ menuPrice }
WHERE MENU_CODE = #{ menuCode }
</update>
<delete id="deleteMenu" parameterType="_int">
DELETE
FROM
TBL_MENU
WHERE MENU_CODE = #{ menuCode }
</delete>
</mapper>Dynamic sql
- ibatis에서 Mybatis로 넘어오면서 동적쿼리를 작성할 수 있다.
OGNL(Object Graph Navigation Language)
- single quatation('') 구간의 값은 리터럴 값으로 보고 그게 아닌 이름은 객체의 필드 또는 변수로 인식하게 작성
- gte(>=): greater than equal
- gt(>): greater than
- lte(<=): less than equal
- lt(<): less than
- eq(==): equal
- neq(!=): not equal
- character data: 캐릭터 데이터라는 범위를 인지
- uses
<select id="selectMenuByPrice" parameterType="_int" resultMap="menuResultMap">
SELECT
A.MENU_CODE
, A.MENU_NAME
, A.MENU_PRICE
, A.CATEGORY_CODE
, A.ORDERABLE_STATUS
FROM TBL_MENU A
WHERE A.ORDERABLE_STATUS = 'Y'
<if test="price gte 0 and price lte 10000">
<![CDATA[
AND A.MENU_PRICE < #{ price }
]]>
</if>
<if test="price gt 10000 and price lte 20000">
AND A.MENU_PRICE BETWEEN 10000 AND 20000
</if>
<if test="price gt 20000 and price lte 30000">
AND A.MENU_PRICE BETWEEN 20000 AND 30000
</if>
<if test="price gt 30000">
AND A.MENU_PRICE >= 30000
</if>
</select>- 종류:
- if
- choose when/otherwise
... <choose> <when test="value == '식사'"> AND A.CATEGORY_CODE IN (4, 5, 6, 7) </when> <when test="value == '음료'"> AND A.CATEGORY_CODE IN (8, 9, 10) </when> <when test="value == '디저트'"> AND A.CATEGORY_CODE IN (11, 12) </when> </choose> - foreach
... <foreach collection="list" item="menuCode" open="(" close=")" separator=", "> #{ menuCode } </foreach> - trim(where, set)
<!-- <where> 는 하위 요소가 다른 조건을 갖지 않으면 WHERE를 추가 --> <!-- where --> <where> <if test="nameValue != null"> A.MENU_NAME LIKE CONCAT('%', #{ nameValue }, '%' ) </if> <if test="categoryValue != null"> AND A.CATEGORY_CODE = #{ categoryValue } </if> </where><!-- trim --> <trim prefix="WHERE" prefixOverrides="AND | OR"> <if test="nameValue != null"> A.MENU_NAME LIKE CONCAT('%', #{ nameValue }, '%' ) </if> <if test="categoryValue != null"> AND A.CATEGORY_CODE = #{ categoryValue } </if> </trim> - set
<set> <if test="menuName != null and menuName != ''"> MENU_NAME = #{ menuName } </if> <if test="orderableStatus != null and orderableStatus != ''"> , ORDERABLE_STATUS = #{ orderableStatus } </if> WHERE MENU_CODE = #{ menuCode } </set>
엔지니어로의 성장일지