세상에서 가장 평범한 사람이 되자. RSS FEED

full-dataset.xml
<?xml version='1.0' encoding='UTF-8'?> <dataset> <GUESTBOOK SEQ="19" NAME="1" SUBJECT="1" CONTENT="1" PASSWORD="1" LOGTIME="2008-05-07 06:18:06.0"/> <GUESTBOOK SEQ="22" NAME="이나영" EMAIL="nayoung@naver.com" HOMEPAGE="nayoung.com" SUBJECT="utsman님에게" CONTENT="잘지내요?" PASSWORD="0000" LOGTIME="2008-05-07 13:15:54.0"/> <GUESTBOOK SEQ="23" NAME="김태희" EMAIL="taehye@naver.com" HOMEPAGE="taehye.com" SUBJECT="utsman님에게 태희가요" CONTENT="좋아해요~" PASSWORD="1111" LOGTIME="2008-05-07 13:16:59.0"/> </dataset>

ListHandlerTest.Java import static org.junit.Assert.assertEquals; import static org.junit.Assert.fail; import java.io.FileInputStream; import java.sql.Connection; import java.util.List; import org.dbunit.database.IDatabaseConnection; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.xml.FlatXmlDataSet; import org.dbunit.ext.oracle.OracleConnection; import org.dbunit.operation.DatabaseOperation; import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Test; import com.jsp.test.DBTestJNDIHelper; public class ListHandlerTest { final static String XML_PATH = "C:\\4_PROJECTs\\workspace\\zeehastory\\test_data\\full-dataset.xml"; final static String SCHEMA_NAME = "zeehastory"; @BeforeClass public static void setUpBeforeClass () throws Exception { // JNDI 데이타 소스를 사용하고 있기에, Junit 테스트를 위해 헬퍼 클래스를 통해 컨텍스트 초기화 DBTestJNDIHelper.initialized(); cleanInsert(); } @AfterClass public static void tearDownAfterClass () throws Exception { delete(); // 컨텍스트 종료 DBTestJNDIHelper.shutDown(); } /** * XML 파일내에 존재하는 테이블을 기준으로 디비 테이블을 truncate 하고, XML 내의 데이타를 insert 한다. * @throws Exception */ public static void cleanInsert () throws Exception { IDatabaseConnection conn = getIDataBaseConnection(); try { DatabaseOperation.CLEAN_INSERT.execute(conn, getDataSet()); } finally { if (conn != null) { conn.close(); } } } /** * XML 파일내에 존재하는 테이블을 기준으로 디비 테이블을 truncate * @throws Exception */ public static void delete () throws Exception { IDatabaseConnection conn = getIDataBaseConnection(); try { DatabaseOperation.DELETE.execute(conn, getDataSet()); } finally { if (conn != null) { conn.close(); } } } /** * DBUnit 을 위한 IDatabaseConnection 리턴 * @return * @throws Exception */ public static IDatabaseConnection getIDataBaseConnection () throws Exception { return new OracleConnection(getJDBCConnection(), SCHEMA_NAME); } /** * JDBC 커넥션을 반환 * @return * @throws Exception */ public static Connection getJDBCConnection () throws Exception { return DBTestJNDIHelper.getConnection(); } /** * 테스트 데이터를 가져온다. * @return * @throws Exception */ public static IDataSet getDataSet () throws Exception { return new FlatXmlDataSet(new FileInputStream(XML_PATH)); } @Test public void testGetList() throws Exception { Connection conn = null; try { conn = DBTestJNDIHelper.getConnection(); ListHandler lh = new ListHandler(); List<ArticleBean> list = lh.getList(conn); if (list == null) { fail("Empty List"); } assertEquals(3, list.size()); assertEquals("김태희", list.get(0).getName()); assertEquals("이나영", list.get(1).getName()); assertEquals("1", list.get(2).getName()); } finally { if (conn != null) { conn.close(); } } } }

ListHandler.Java import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import com.jsp.chapter10.util.DSConn; public class ListHandler { /** * Logger for this class */ private static final Log logger = LogFactory.getLog(ListHandler.class); static final String QUERY_COUNT = "SELECT COUNT(*) FROM GUESTBOOK"; static final String QUERY_LIST = "SELECT * FROM ("+ " SELECT /*+ INDEX_DESC(GUESTBOOK PK_GUESTBOOK) */"+ " ROWNUM RSEQ, SEQ, NAME, EMAIL, HOMEPAGE, SUBJECT, CONTENT, LOGTIME"+ " FROM GUESTBOOK WHERE ROWNUM <= ?"+ ") A WHERE A.RSEQ > ?"; int pageSzie = 5; // int cPage = 1; //현재 페이지 번호 int total = 0; //게시물 총수 /** * * @param conn * @return * @throws SQLException */ protected List<ArticleBean> getList (Connection conn) throws SQLException { PreparedStatement pstmt = null; ResultSet rs = null; try { //전체 게시물 갯수 pstmt = conn.prepareStatement(QUERY_COUNT); rs = pstmt.executeQuery(); if (rs.next()) { this.total = rs.getInt(1); } rs.close(); pstmt.close(); //리스트 가져오기 int endNo = pageSzie * cPage; pstmt = conn.prepareStatement(QUERY_LIST); pstmt.setInt(1, endNo); pstmt.setInt(2, endNo - pageSzie); rs = pstmt.executeQuery(); List<ArticleBean> list = new ArrayList<ArticleBean>(); while (rs.next()) { if (logger.isDebugEnabled()) { logger.debug("getList(Connection) -name =" + rs.getString("name")); } ArticleBean ab = new ArticleBean(); ab.setSeq(rs.getInt("seq")); ab.setName(rs.getString("name")); ab.setEmail(rs.getString("email")); ab.setHomepage(rs.getString("homepage")); ab.setSubject(rs.getString("subject")); ab.setContent(rs.getString("content")); ab.setLogtime(rs.getTimestamp("logtime")); list.add(ab); } return list; } catch (SQLException e) { logger.error("getList()", e); throw e; } finally { DbUtils.closeQuietly(conn, pstmt, rs); } } /** * 페이지에 해당하는 게시물 목록을 List 에 저장해서 리턴 * @return * @throws SQLException */ public List<ArticleBean> getList () throws SQLException { return getList(DSConn.getConnection()); } }