MyBatis (2) CRUD 예제 만들기

목차.

1. MyBatis 기본 설정
2. CRUD 예제 만들기
3. 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();
			}
		}
	}
}