MyBatis
-
MyBatis (3) SQL Mapper와 Bean 간의 property 전달2012.11.09
-
MyBatis (2) CRUD 예제 만들기2012.11.09
-
MyBatis (1) MyBatis 설정하기2012.11.09
MyBatis (3) SQL Mapper와 Bean 간의 property 전달
목차.
- MyBatis 기본 설정
- CRUD 예제 만들기
- SQL mapper 파라메터 전달
MyBatis는 SQL Mapping XML 구문을 Java 코드로 변환시 디폴트로 PreparedStatement 구문으로 바꾸고 전달되는 파라메터 들을 PreparedStatement 구문의 파라메터 '?'로 대응시킨다.
<select id="user" parameterType="int" resultType="User">
SELECT * FROM user WHERE id = #{id}
</select>'#{id}'는 int 타입의 파라메터이며 결과는 User 객체에 저장된다. 이 SQL Mapping XML 구문은 다음과 같은 내용의 Java 구문으로 변환된다.
String user = "SELECT * FROM user WHERE id = ?"; PreparedStatement ps = conn.preparedStatement(user); ps.setInt(1, id);
빈과 SQL 매퍼간의 파라메터 전달시 MyBatis는 Java 원시타입(Primitive type)의 경우 프로퍼티 getter/setter를 가지지 않고 파라메터 전체가 값을 대신한다.
만일 여러 개의 파라메터를 전달해야 할 경우는 2 가지 방법 중 선택하여 사용할 수 있다. Map을 사용하는 방법과 Mapper Interface Class에 @Param 어노테이션을 사용하는 방법이 있다.
Map을 사용
SQL Mapping XML
<insert id="user" parameterType="map" resultType="User">
INSERT INTO user (username, password)
VALUES (#{username}, #{password})
</insert>Mapper Interface Class
public int addParamMap(Map<string, string> map);
Running Java Code
...
Map<string, string> param = new HashMap<string, string>();
param.put("username", "user1");
param.put("password", "1234");
mapper.addParamMap(param);@Param 어노테이션을 사용
SQL Mapping XML
<intert id="user">
INSERT INTO user (username, password)
VALUES (#{username}, #{password})
</insert>Mapper Interface Class
public int addParamAnnotation(
@Param("username") String username,
@Param("password") String password);Running Java Code
mapper.addParamAnnotation("user1", "1234");MyBatis (2) CRUD 예제 만들기
목차.
- MyBatis 기본 설정
- CRUD 예제 만들기
- SQL mapper 파라메터 전달
SQL Mapper
UserMapper.xml
<mapper namespace="exercise.mybatis3.persistence.UserMapper">
<insert id="add" parameterType="User"
useGeneratedKeys="true" keyProperty="id">
INSERT INTO user (username, password, level, reg_date)
VALUES (#{username}, #{password}, #{level}, #{regDate})
</insert>
<select id="get" parameterType="int" resultType="User">
SELECT
id, username, password, level, reg_date AS 'regDate'
FROM user
WHERE id = #{id}
</select>
<select id="getAll" resultType="User">
SELECT
id, username, password, level, reg_date AS 'regDate'
FROM user
</select>
<delete id="delete" parameterType="int">
DELETE FROM user
WHERE id = #{id}
</delete>
<delete id="deleteAll">
DELETE FROM user
</delete>
<select id="count" resultType="int">
SELECT COUNT(*) FROM user
</select>
<select id="lastId" resultType="int">
SELECT id
FROM user
ORDER BY id DESC
LIMIT 1
</select>
</mapper>Mapper Interface Class
UserMapper.java
public interface UserMapper {
public void add(User user);
public User get(int id);
public List<user> getAll();
public void delete(int id);
public void deleteAll();
public int count();
public int lastId();
}Domain Object
User.java
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String username;
private String password;
private Integer level;
private String regDate;
public User() {
// TODO Auto-generated constructor stub
}
public User(String username, String password,
Integer level, String regDate) {
this.username = username;
this.password = password;
this.level = level;
this.regDate = regDate;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getLevel() {
return level;
}
public void setLevel(Integer level) {
this.level = level;
}
public String getRegDate() {
return regDate;
}
public void setRegDate(String regDate) {
this.regDate = regDate;
}
@Override
public boolean equals(Object comp) {
User target = (User)comp;
if (this.id.equals(target.id) &&
this.username.equals(target.username) &&
this.password.equals(target.password) &&
this.level.equals(target.level) &&
this.regDate.equals(target.regDate))
return true;
else
return false;
}
}Unit test
TestUserMapper.java
public class TestUserMapper {
static SqlSessionFactory sf;
List<user> users;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
String resource =
"exercise/mybatis3/persistence/mybatis-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
sf = new SqlSessionFactoryBuilder().build(reader, "testing");
}
@Before
public void setUp() {
users = Arrays.asList(
new User("user1", "1234", 1, "2012-11-09"),
new User("user2", "1234", 1, "2012-11-09"),
new User("user3", "1234", 1, "2012-11-09")
);
}
@Test
public void testAdd() {
SqlSession session = sf.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
mapper.deleteAll();
mapper.add(users.get(0));
assertThat(1, is(mapper.count()));
} finally {
session.close();
}
}
@Test
public void testGet() {
SqlSession session = sf.openSession();
try {
UserMapper mapper =
session.getMapper(UserMapper.class);
mapper.deleteAll();
mapper.add(users.get(0));
User user = mapper.get(mapper.lastId());
assertTrue((users.get(0)).equals(user));
} finally {
session.close();
}
}
@Test
public void testGetAll() {
SqlSession session = sf.openSession();
try {
UserMapper mapper =
session.getMapper(UserMapper.class);
mapper.deleteAll();
for (User user : users) {
mapper.add(user);
}
assertThat(users.size(), is(mapper.count()));
} finally {
session.close();
}
}
@Test
public void testDelete() {
SqlSession session = sf.openSession();
try {
UserMapper mapper =
session.getMapper(UserMapper.class);
mapper.deleteAll();
for (User user : users) {
mapper.add(user);
}
mapper.delete(mapper.lastId());
assertThat(users.size() - 1, is(mapper.count()));
} finally {
session.close();
}
}
}
}MyBatis (1) MyBatis 설정하기
목차.
- MyBatis 기본 설정
- CRUD 예제 만들기
- SQL mapper 파라메터 전달
MyBatis는 iBATIS의 새로운 버전으로 국내에서 가장 많이 사용되는 ORM Framework1 중 하나이다. MyBatis는 문서화가 잘 되어 있다고 하지만 막상 실전에 적용시키기에는 이전 버전인 iBATIS에 비해 예제가 부족한 듯하여 자료 정리겸 레퍼런스를 만들어 볼까 한다.
우선 MyBatis를 설정하고 간단한 예제를 만들어 본 다음, Spring 3로 Bean을 등록하는 예제로 확장시켜 볼 예정이다.
본 예제는 다음과 같은 개발환경하에서 작성되었다.
- Eclipse Java EE IDE (Juno)
- PropertiesEditor (by Sou Miyazaki)
- Java SE 6
- MyBatis-3.1.1
- JUnit 4
1. MySQL Table
CREATE TABLE user ( id INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(16) NOT NULL, password VARCHAR(16) NOT NULL, level INT(2) NOT NULL DEFAULT '0', reg_date DATE NOT NULL );
2. Configuration
development.properties
url=jdbc:mysql://localhost/development?characterEncoding=utf-8 driver=com.mysql.jdbc.Driver user=development pass=test123
mysql-config.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="exercise/mybatis3/persistence/development.properties"/> <settings> <setting name="defaultExecutorType" value="REUSE"/> <setting name="useGeneratedKeys" value="true"/> </settings> <typeAliases> <!-- Type Aliases List --> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="JNDI"> <property name="initial_context" value="java:comp/env" /> <property name="data_source" value="jdbc/insure"/> </dataSource> </environment> <environment id="testing"> <transactionManager type="MANAGED"> <property name="closeConnection" value="false"/> </transactionManager> <dataSource type="POOLED"> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> <property name="username" value="${user}" /> <property name="password" value="${pass}" /> </dataSource> </environment> </environments> <mappers> <!-- Mapper List --> </mappers> </configuration>
3. SQL Mapper (CRUD)
UserMapper.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="exercise.mybatis3.persistence.UserMapper">
<insert id="add" parameterType="User"
useGeneratedKeys="true" keyProperty="id">
INSERT INTO user (username, password, level, reg_date)
VALUES (#{username}, #{password}, 1, NOW())
</insert>
<select id="count" resultType="int">
SELECT COUNT(*) FROM user
</select>
</mapper>UserMapper.java
package exercise.mybatis3.persistence;
import exercise.mybatis3.domain.User;
public interface UserMapper {
public void add(User user);
public int count();
}4. Domain Object
User.java
package exercise.mybatis3.domain;
import java.io.Serializable;
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String username;
private String password;
private Integer level;
private String regDate;
public User() {
// TODO Auto-generated constructor stub
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getLevel() {
return level;
}
public void setLevel(Integer level) {
this.level = level;
}
public String getRegDate() {
return regDate;
}
public void setRegDate(String regDate) {
this.regDate = regDate;
}
}5. Unit Test (CRUD)
TestUserMapper.java
package exercise.mybatis3.test;
import static org.junit.Assert.assertThat;
import static org.hamcrest.CoreMatchers.is;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import exercise.mybatis3.domain.User;
import exercise.mybatis3.persistence.UserMapper;
public class TestUserMapper {
static SqlSessionFactory sf;
User user;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
String resource = "exercise/mybatis3/persistence/mybatis-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
sf = new SqlSessionFactoryBuilder().build(reader, "testing");
}
@Before
public void setUp() {
user = new User("user1", "1234");
}
@Test
public void testAdd() {
SqlSession session = sf.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
mapper.add(user);
assertThat(1, is(mapper.count()));
} finally {
session.close();
}
}
}- 데이터베이스와 객체와의 관계를 맵핑시켜 퍼시스턴스 로직처리를 도와주는 프레임워크 (Object Relational Mapping) [본문으로]