[Spring] MyBatis Batch + Transaction À» ÀÌ¿ëÇÑ ´ë¿ë·® SQLÀÛ¾÷
[Spring] MyBatis Batch + Transaction À» ÀÌ¿ëÇÑ ´ë¿ë·® SQLÀÛ¾÷
¸¶À»¿¡¼ ¿ÀÅ©Àâ´Â Äù½ºÆ® ÇÏ°í Àִµ¥ °©ÀÚ±â
Áß°£º¸½º¸¦ Àâ¾Æ¿À¶ó´Â Äù½ºÆ®°¡ ¶³¾îÁ³´Ù...
ÀÏÁ¤½Ã°£¸¶´Ù ¶óÁ¸®ÆÄÀÌ¿¡¼ ¹Þ¾Æ¿Â ¿ø½Ãµ¥ÀÌÅ͸¦ Àç°¡°øÇÏ¿©
DB¿¡ insert ÇØÁÖ¾î¾ß ÇÏ´Â ÀÛ¾÷
·Î¿ì¼ö°¡ Àû´Ù¸é ±×³É ¸¸µé°ÚÁö¸¸ ´ë¿ë·® ÀÛ¾÷ÀÏ °æ¿ì ´äÀÌ ¾È³ª¿Â´Ù.
¾à 1¸¸°Ç~10¸¸°Ç Á¤µµÀÇ µ¥ÀÌÅ͸¦ ³¯·ÁÁà¾ß Çϴµ¥ ¤²¤§¤²¤§¤²¤§
±×·¡¼ ã¾Æº» ¹æ¹ýÀº Batch¿Í Transaction À» ÀÌ¿ëÇÑ ´ë¿ë·® sql ÀÛ¾÷
³ªµµ Á¤È®È÷ ³»°¡ ¹» ÇÑ°ÇÁöµµ ¸ð¸£°í ±×³É ½ºÅÿÀ¹ö Ç÷οì, ¿ÀÅ°, ÀüÀÚÁ¤ºÎ µÚÀû°Å¸®¸é¼ ÀÌ°Í Àú°Í ´Ù ¶§·Á ¹Ú´À¶ó
ÇÊ¿ä ¾ø´Â ¼³Á¤ÀÌ ÀÖÀ» ¼öµµ ÀÖÀ¸´Ï ¾Æ´Â »ç¶÷Àº ´ñ±ÛÁ» ´Þ¾ÆÁÖ½Ã±æ ¹Ù¶ø´Ï´Ù.
1. XML ¼³Á¤
1) mapper ¼³Á¤
context-mapper.xml ÆÄÀÏÀ̳ª context-sqlMap.xml ÆÄÀÏ¿¡
¸¶À̹ÙƼ½º ¿¬µ¿À» À§ÇØ ¸¸µé¾î³ù´ø ¼³Á¤À» ¾Æ·¡¿Í °°ÀÌ ¹Ù²ãÁØ´Ù.
9¹ø ¶óÀÎÀÇ batch¼³Á¤À¸·Î batch »ç¿ëÀÌ °¡´É Çϵµ·Ï ÇÏ´Â µí ÇÏ´Ù.
1 2 3 4 5 6 7 8 9 10 | <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:/egovframework/sqlmap/example/sql-mapper-config.xml" /> <property name="mapperLocations" value="classpath:/egovframework/sqlmap/example/mappers/mssql/*.xml" /> </bean> <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="clearCache"> <constructor-arg index="0" ref="sqlSession" /> <constructor-arg index="1" value="BATCH" /> </bean> | cs |
2) datasource ¼³Á¤
DBÁ¢¼Ó Á¤º¸ ÀÛ¼ºÇÏ´Â °÷¿¡ Æ®·»Á§¼Ç¸Þ´ÏÀú ¼³Á¤À» Çϴµ¥ ÀÌ°É ÇØ¾ß ÇÏ´Â °ÇÁø Àß ¸ð¸£°Ú´Ù.
1 2 3 | <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> | cs |
3) dispatcher-servlet ¼³Á¤
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?xml version="1.0" encoding="UTF-8"?> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:task="http://www.springframework.org/schema/task" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/context http://www.springframework.org/schema/task http://www.springframework.org/schema/mvc http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"> <tx:annotation-driven proxy-target-class="true"/> | cs |
7¹ø ¶óÀΰú 17¹ø ¶óÀÎÀ» Ãß°¡ÇØÁÖ°í 20¹ø¶óÀÎÀÇ ³»¿ëÀ» ³Ö¾î ÁÖµµ·Ï ÇÑ´Ù.
4) pom.xml
1 2 3 4 5 6 7 8 | <!-- Æ®·£Á§¼Ç 󸮸¦ À§ÇÔ --> <dependency> <groupId>cglib</groupId> <artifactId>cglib</artifactId> <version>2.2</version> <type>jar</type> <scope>compile</scope> </dependency> | cs |
µðÆæ´ø½Ã¿¡ Ãß°¡¸¦ ÇØÁà¾ß Æ®·£Á§¼Ç 󸮰¡ °¡´ÉÇÑ°ÇÁø ¸ð¸£°ÚÀ½. ÀüÀÚÁ¤ºÎ ÀÚü¿¡ °ü·ÃµÈ°Ô ÀÌ¹Ì Ãß°¡ µÇ¾î ÀÖÀ» ¼öµµ ÀÖ°í
Á¤È®È÷ ¸ð¸£°ÚÀ½ Àú°Ç
2. DAO or impl ÀÛ¼º
³ª´Â DAO¸¦ »ç¿ëÇÏÁö ¾Ê°í °øÅëDAOÇϳª ¸¸µé¾î ³õ°í impl¿¡¼ ¹Ù·Î Äõ¸®¸¦ ³¯·ÁÁÖ´Â ¹æ½ÄÀ»
ÁÁ¾Æ ÇÑ´Ù.
¾îÂ÷ÇÇ DAO¿¡¼ Ưº°È÷ ÇØÁٰ͵µ ¾ø°í.. ÇØÁà¾ß Çϴ°ÍÀÌ À־ impl¿¡¼ ÇØÁÖ¸é µÇ´Ï±î
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | @Override public void updateSchedulerHistoryRow(List<MinHistoryVO> historyList) { // TODO Auto-generated method stub // Æ®·»Á§¼Ç ½ÃÀÛ SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH); long startTime = System.currentTimeMillis(); try { for (MinHistoryVO list : historyList) { sqlSession.update("scheduler.updateSchedulerHistoryRow", list); } } finally { sqlSession.flushStatements(); sqlSession.close(); } long endTime = System.currentTimeMillis(); long resutTime = endTime - startTime; System.out.println("Æ®·£Á§¼Ç ¹èÄ¡" + " ¼Ò¿ä½Ã°£ : " + resutTime/1000 + "(ms)"); } | cs |
³ ÀÌ·±½ÄÀ¸·Î ÀÛ¼º Çß´Ù.
°¡²û 6¹ø ¶óÀο¡ ExecutorType.BATCH ÀÌ ¸Å°³º¯¼ö¸¦ ¾È³Ö°í ½ÃÀÛ ÇÒ ¼ö Àִµ¥
±×·¯¸é Æ®·»Á§¼Ç ¾Èµ¹°í Ä¿³Ø¼Ç ´Ù ÂïÀ¸¸é¼ µ¹°Ô µÈ´Ù.
Àú·¸°Ô Çصµ ¼ö¸¸°ÇÀÌ µ¹°Ô µÇ¸é ¼¼¼Ç¿¡ÀúÀåµÉ µ¥ÀÌÅ͵éÀÌ ³ÑÃÄÈê·¯¼ ±×·±Áö ¹ö¹÷°Å¸±¶§°¡ Àִµ¥
±×·²¶© ÄÁÆ®·Ñ·¯¿¡¼ Á¶±Ý¾¿ ²÷¾î¼ ³¯·Á ÁÖµµ·Ï ÇÏÀÚ
3. controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | //ÄÁÆ®·Ñ·¯¿¡¼ ÀÏÁ¤ °³¼ö ´ÜÀ§·Î ²÷¾î¼ ³¯·Á ÁØ´Ù. int insertCount = 0; List<MinHistoryVO> divHisList = new ArrayList<MinHistoryVO>(); for(int hisCount = 0, hisSize = historyList.size(); hisCount < hisSize; hisCount++){ MinHistoryVO _tempData = new MinHistoryVO(); _tempData = historyList.get(hisCount); divHisList.add(_tempData); if(insertCount == 1000 || hisSize-1 == hisCount){ schedulerService.updateSchedulerHistoryRow(divHisList); // Æ®·»Á§¼Ç divHisList = new ArrayList<MinHistoryVO>(); insertCount = 0; } else{ insertCount++; } } | cs |
while¹® »ç¿ëÇÏ´Â°Ô Àͼ÷ÇÏÁö ¾Ê¾Æ¼ ³ª´Â for¹®À» ÁÖ·Î »ç¿ë ÇÑ´Ù.
¸®½ºÆ®¿¡ Àܶà ÀÖ´Â µ¥ÀÌÅ͵éÀ» »õ·Î¿î ÀÛÀº ¹Ù±¸´Ï¿¡ ´ã¾Æ¼ ²÷¾î¼ ³¯·ÁÁØ´Ù.
ÀÌ·¸°Ô Çϸé MsSql Server 2005¹öÀü ±âÁØ 1¸¸°Ç insertÇϴµ¥ 5ÃÊÁ¤µµ °É¸°´Ù.
Àú±â¿¡ ¸¶À̹ÙƼ½º foreach±îÁö ½á¼ ¹úÅ©ÀμƮ ÇÏ¸é ½Ã°£Àº ´õ ´ÜÃà µÈ´Ù.
¿ø½Ãµ¥ÀÌÅ͸¦ ÂÉ°³¼ 60°³Ä÷³¿¡ µû·Î ¹Ú¾Æ¾ß Çϱ⠶§¹®¿¡
³Ñ°ÜÁÖ´Â ÆĶó¹ÌÅÍ °³¼ö 2100°³ Á¦ÇÑÀÌ ÀÖ¾î¼ Á¦´ë·Î »ç¿ë ¸øÇߴµ¥
¿©·¯°³·Î ÂÉ°µ´ÙÀ½¿¡ Mybatis foreach µ¹·Á¼ Çѹø¿¡ ³¯·ÁÁÖ¸é ½Ã°£Àº
ÈξÀ ´õ´õ´õ´õ´õ ´ÜÃà µÈ´Ù.
Ãâó: http://yamea-guide.tistory.com/category/Yame Programmer/ÀüÀÚÁ¤ºÎÇÁ·¹ÀÓ¿öÅ©?page=1 [±âŸġ´Â °³¹ßÀÚÀÇ ¾ß¸Å °¡À̵å]