Commons-DbUtils
I. ¾îµð¼ ´Ù¿îÀ» ¹Þ³ª¿ä?
http://jakarta.apache.org/site/downloads/downloads_commons-dbutils.cgi
II. ¼³Ä¡´Â ¾î¶»°Ô Çϳª¿ä?
´Ù¿î ¹ÞÀº commons-beanutils.jar´Â ÀÚ½ÅÀÇ /WEB-INF/lib/ ¿¡ º¹»çÇÕ´Ï´Ù
III. DbUtils¶õ ¹«¾ùÀΰ¡¿ä?
DbUtils´Â JDBC ÀÛ¾÷À» Á»´õ ½±°Ô ÇÒ¼öÀÖµµ·Ï ÇØÁÖ´Â ÀÛÀº Ŭ·¡½º ÁýÇÕÀÔ´Ï´Ù.
IV. ¿Ö DbUtils¸¦ »ç¿ëÇØ¾ß Çϴ°¡¿ä?
¨ç resource ´©Ãâ¿¡ ´ëÇÑ ¾î¶°ÇÑ °¡´É¼ºµµ ¾ø½À´Ï´Ù
JDBCÄÚµùÀ» Çϴµ¥ ÀÖ¾î¼ ½±Áö¾Ê°í ¾çµµ ¸¸¸¸Ä¡ ¾ÊÀ¸¸ç Áö·çÇØ Áö±â ½±½À´Ï´Ù
ÀÌ·¯´Ù º¸¸é Àڱ⵵ ¸ð¸£°Ô Connection ´©¼ö¸¦ ¹ß»ý½Ãų¼ö Àִµ¥ ÀÌ·¯ÇÑ °¡´É¼ºÀ» ¹èÀçÇØ ÁÝ´Ï´Ù
¨è ÄÚµåÀÇ °¡µ¶¼ºÀÌ ³ô¾ÆÁý´Ï´Ù
µ¥ÀÌÅͺ£À̽º ó¸®Çϴµ¥ ÇÊ¿äÇÑ ÄÚµåÀÇ ¾çÀ» Àý´ëÀûÀ¸·Î ÁÙ¿©¾ß ÇÕ´Ï´Ù.
³²¾ÆÀÖ´Â ÄÚµå·Î ´ç½ÅÀÇ Àǵµ¸¦ Á¤È®ÇÏ°Ô ³ªÅ¸³»¾î¾ß ÇÕ´Ï´Ù.
¨é ResultSetÀ¸·Î ºÎÅÍ JavaBean property·Î ¼¼ÆÃÀ» ÇØÁÝ´Ï´Ù!
´õÀÌ»ó setter¸Þ¼Òµå¸¦ ÀÌ¿ëÇÏ¿© ResultSetÀ¸·ÎºÎÅÍ Ä÷³°ªÀ» °¡Á®¿À´Â ÄÚµùÀ» ÇÏÁö ¾Ê¾Æµµ µË´Ï´Ù
ResultSet °¢°¢ÀÇ row´Â bean instanceÀÇ ¿¡ ¿Ïº®ÇÏ°Ô Àü´ÞÇØ ÁÝ´Ï´Ù
V. ¾î¶»°Ô »ç¿ëÇϳª¿ä?
¨ç Connection, Statement, ResultSet ÀÇ close¸¦ °£´ÜÇÏ°Ô!
ÀÌ·²¶§´Â org.apache.commons.dbutils.DbUtils Ŭ·¡½º¸¦ ÀÌ¿ëÇÏÀÚ!
ÀÌ Å¬·¡½º´Â ¸ðµÎ static ¸Þ¼Òµåµé·Î ±¸¼ºµÇ¾îÀÖ½À´Ï´Ù
»ç¿ë¿¹)
DbUtils.close(conn);
DbUtils.close(stmt);
DbUtils.close(rs);
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(stmt);
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(conn, stmt, rs);
DbUtils.commitAndClose(conn);
DbUtils.commitAndCloseQuietly(conn);
DbUtils.loadDriver("com.mysql.jdbc.Driver");
DbUtils.rollback(conn);
closeQuietly ¸Þ¼Òµåó·³ µÚ¿¡ Quietly¶ó°í ºÙ¾î ÀÖ´Â ¸Þ¼Òµå´Â ÀͼÁ¼Ç 󸮴 ÀÚüÀûÀ¸·Î ó¸®ÇÕ´Ï´Ù,
Áï ÀÚ½ÅÀ» callÇÑ°÷À¸·Î throw ÇÏÁö ¾Ê½À´Ï´Ù
commitAndCloses´Â connectionÀ» commit ÈÄ close Çϸç rollback´Â connectionÀ» rollback ÇÕ´Ï´Ù
loadDriver ´Â JDBC µå¶óÀ̹ö¸¦ ·Îµù ÇÕ´Ï´Ù
¨è ÆÄÀÏ·Î ÀúÀåµÈ SQLÀ» »ç¿ëÇÏÀÚ!
ÀÌ·² ¶§´Â org.apache.commons.dbutils.QueryLoader Ŭ·¡½º¸¦ ÀÌ¿ëÇÕ´Ï´Ù
ÀÌ Å¬·¡½º´Â SingleTone ÆÐÅÏÀÇ Å¬·¡½ºÀÔ´Ï´Ù
Áï ÆÄÀÏ·Î ÀúÀåµÈ SQLÀ» Àоî HashMapÀ¸·Î ·ÎµåÇϴ Ŭ·¡½º ÀÔ´Ï´Ù
»ç¿ë¿¹)
QueryLoader queryloader = QueryLoader.getInstance(); //½Ì±ÛÅæ
HashMap hashmap = queryloader.load("sql");
queryloader.upload("sql");
queryloader´Â ½ÌŬÅæÀ̹ǷΠÀ§¿Í°°ÀÌ °´Ã¼¸¦ ¾ò¾î¿É´Ï´Ù
load ÇÔ¼ö´Â Properties Ŭ·¡½º¸¦ ÀÌ¿ëÇÏ¿© sql.properties ÆÄÀÏÀ» Àоî
HashMapÀ¸·Î ÀúÀåÇÏ¿© ¸®ÅÏÇÏ¿© ÁÝ´Ï´Ù
unload´Â load½Ã µû·Î ¸Þ¸ð¸®¿¡ ÀúÀåÇØ ³õ¾Ò´ø sql Á¤º¸¸¦ ÇØÁ¦ÇÕ´Ï´Ù
¨é SetterÇÔ¼ö·Î ´õÀÌ»ó ¸Ó¸® ¾ÆÇÁÁö ¸»ÀÚ!
ÀÌ·²¶§´Â org.apache.commons.dbutils.QueryRunner Ŭ·¡½º¸¦ ÀÌ¿ëÇÕ´Ï´Ù
»ç¿ë¿¹)
...
BoardVO boardVO = null;
ArrayList arraylist = new ArrayList();
resultset = statement.executeQuery("SELECT * FROM board_t");
while (resultset.next()) {
boardVO = new BoardVO();
boardVO.setTitle("title");
boardVO.setContent("content");
boardVO.setWriter("writer");
arraylist.add(boardVO);
}
..
¿Í °°Àº ÄÚµå´Â ´ÙÀ½°ú °°ÀÌ °£·«È µË´Ï´Ù
ResultSetHandler rsh= new BeanListHandler(BoardVO.class);
QueryRunner queryRunner = new QueryRunner();
List list = (List)queryRunner.query(conn, "SELECT * FROM board_t", rsh);
Á¤¸» °£´ÜÇØ Áý´Ï´Ù ¸¸¾à Å×ÀÌºí¿¡ Ä÷³ÀÌ 30~40°³°¡ µÈ´Ù¸é..
select Çѹ®Àå ÇÒ·Á¸é ÄÚµå¼ö°¡ ¸î½ÊÁÙ ÀÔ´Ï´Ù. ´õÀÌ»ó ³ë°¡´Ù ÇÏÁö ¸¿½Ã´Ù~
QueryRunner´Â ´ÙÀ½°ú °°Àº ÇÔ¼ö¸¦ Áö¿øÇÕ´Ï´Ù
»ç¿ë¿¹)
QueryRunner queryrunner = new QueryRunner();
QueryRunner queryrunner = new QueryRunner(DataSource ds); //datasource¸¦ ¹Ù·Î ÀÌ¿ëÇÒ ¼ö ÀÖ´Ù
queryRunner.query(Connection conn, String sql, ResultSetHandler rsh)
queryRunner.query(Connection conn, String sql, Object param, ResultSetHandler rsh)
queryRunner.query(Connection conn, String sql, Object[] params, ResultSetHandler rsh)
¿©±â¼ ¸»ÇÏ´Â Object paramÀº ÆĶó¹ÌÅÍ Àü´Þ½Ã »ç¿ëµË´Ï´Ù
ArrayList params = new ArrayList();
params.add("100");
params.add("200");
ResultSetHandler rsh = new BeanListHandler(BoardVO.class);
QueryRunner queryRunner = new QueryRunner();
List list = (List)queryRunner.query(conn, "SELECT * FROM board_t WHERE boardNo > ? and boardNo < ?", params.toArray(), rsh);
select »Ó¸¸ ¾Æ´Ï¶ó update, delete¿ª½Ã °¡´ÉÇÕ´Ï´Ù
»ç¿ë¿¹)
QueryRunner queryRunner = new QueryRunner();
queryRunner.update(Connection conn, String sql)
queryRunner.update(Connection conn, String sql, Object param)
queryRunner.update(Connection conn, String sql, Object params[])
ArrayList params = new ArrayList();
params.add(boardId);
queryRunner.update(connection, "UPDATE board_t SET read = read + 1 WHERE boardNo = ?", params.toArray());
¿Í °°ÀÌ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù
VI. »ùÇÃÄÚµå
public class DbUtilsExample() {
public static void main(String[] args) {
HashMap map = QueryLoader.getInstance().load("sql"); // (ÁÖÀÇ) loadÇÔ¼ö´Â ½ÇÇàÇÒ¶§¸¶´Ù ÆÄÀÏÀ» ÀнÀ´Ï´Ù
Connection conn = null;
try {
DbUtils.loadDriver("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/mysql", "root", "");
ArrayList params = new ArrayList(); params.add(args[0]);
ResultSetHandler rsh = new BeanListHandler(BoardVO.class);
QueryRunner qr = new QueryRunner();
List list = qr.query(conn, (String)map.get("select"), params.toArray(), rsh);
for (int i = 0; i < list.size(); i++) {
BoardVO board = (BoardV)list.get(i);
System.out.println(board.getTitle());
System.out.println(board.getContent());
System.out.println(board.getWriter());
)
} catch (Exception e) {
System.out.println(e);
} finally {
DbUtils.closeQuitely(conn);
}
}
}
|