Sustainability Solu 작성자 : 박찬연 Ibatis (2 nd ) 박찬연 () 2008. 04. 29
목차 소개 datasource sqlmap 트렌젝션 배치 SqlMapClient 로깅 SimpleDataSource ScriptRunner 2
소개 SQL Maps 프레임워크는관계형데이터베이스에접근할때필요한자바코드를현저하게줄일수있도록도와줍니다. SQL Maps 는간단한 XML 서술자를사용해서간단하게자바빈즈를 SQL statement 에맵핑시킵니다. 간단함이란다른프레임워크와객체관계맵핑툴에비해 SQL Maps 의가장큰장점입니다. SQL Maps 를사용하기위해서여러분은자바빈즈와 XML 그리고 SQL 에친숙할필요가있습니다. 여러분은배워야할것도거의없고테이블을조인하거나복잡한쿼리문을수행하기위해필요한복잡한스키마도없습니다. SQL Maps 를사용하면당신은실제 SQL 문의모든기능을가질수있습니다. 3
소개 - 다운로드 http://ibatis.apache.org/ 4
datasource <datasource> 요소 database 에연결하기위해 datasource 를설정합니다. 프레임워크에서제공되는 3 가지데이터소스타입이있지만여러분만의데이터소스를사용할수도있습니다. SimpleDataSourceFactory SimpleDataSource 는데이터소스를제공하는컨테이너가없는경우에 connection 을제공하기위해기본적으로풀링 (pooling) 데이터소스구현을제공합니다. 이것은 ibatis SimpleDataSource connection 풀링을기초로합니다. sql-map-config.xml 코드참조 5
datasource DbcpDataSourceFactory 이구현물은 DataSource API 를통해 connection 풀링서비스를제공하기위해 Jakarta DBCP (Database Connection Pool) 을사용합니다. 이 DataSource 는애플리케이션 / 웹컨테이너가 DataSource 구현물을제공하지못하거나직접 standalone 애플리케이션을구동할때이상적입니다. sql-map-config-dbcp.xml 코드참조 6
datasource JndiDataSourceFactory 이구현물은애플리케이션컨테이너내 JNDI 컨텍스트로부터 DataSource 구현물을가져와야할것입니다. 이것은전형적으로애플리케이션서버를사용중이고컨테이너관리 connection 풀그리고제공되는 DataSource 구현물이있을때사용합니다. JDBC DataSource 구현물에접근하기위한표준적인방법은 JNDI 컨텍스트를통하는것입니다. JndiDataSourceFactory 는 JNDI 를통해 DataSource 에접근하는기능을제공합니다. <transactionmanager type="jdbc" > 1 <datasource type="jndi"> 2 <property name="datasource" 3 value="java:comp/env/jdbc/jpetstore"/> 4 </datasource> </transactionmanager> 7
datasource 전역 (global) 트랜잭션을설정 <transactionmanager type="jta" > 1 <property name="usertransaction" value="java:/ctx/con/usertransaction"/> 2 <datasource type="jndi"> 3 <property name="datasource" value="java:comp/env/jdbc/jpetstore"/> </datasource> </transactionmanager> 8
sqlmap <sqlmap> 요소 <!--CLASSPATH RESOURCES --> <sqlmap resource="com/ibatis/examples/sql/customer.xml" /> <sqlmap resource="com/ibatis/examples/sql/account.xml" /> <sqlmap resource="com/ibatis/examples/sql/product.xml" /> <!--URL RESOURCES --> <sqlmap url="file:///c:/config/customer.xml " /> <sqlmap url="file:///c:/config/account.xml " /> <sqlmap url="file:///c:/config/product.xml" /> 9
sqlmap statement 10
sqlmap 쿼리의 < 와 > 문제해결법 <statement id="getpersonsbyage" parameterclass= int resultclass="examples.domain.person"> 1 <![CDATA[ SELECT * FROM PERSON WHERE AGE > #value# 2 ]]> </statement> 11
sqlmap 저장프로시저 파라미터객체 (map) 내에서두개의칼럼사이에두개의이메일주소를교체하는예제 저장프로시저는 <procedure> statement 요소를통해지원됩니다. <parametermap id="swapparameters" class="map" > 1 <parameter property="email1" jdbctype="varchar" javatype="java.lang.string" mode="inout"/> 2 <parameter property="email2" jdbctype="varchar" javatype="java.lang.string" 3 mode="inout"/> </parametermap> <procedure id="swapemailaddresses" parametermap="swapparameters" > 1 {call swap_email_address (?,?)} </procedure> 12
sqlmap parameterclass <statement id= statementname parameterclass= examples.domain.product > 1 insert into PRODUCT values (#id#, #description#, #price#) </statement> 13
sqlmap parametermap <parametermap id= insert-product-param class= com.domain.product > 1 <parameter property= id /> 2 <parameter property= description /> </parametermap> <statement id= insertproduct parametermap= insert-productparam > 1 insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?); </statement> 좀더세부적인설정이필요하다면다음과같이합니다. <parametermap id= insert-product-param class= com.domain.product > 1 2 <parameter property= id jdbctype= NUMERIC javatype= int nullvalue= -9999999 /> <parameter property= description jdbctype= VARCHAR nullvalue= NO_ENTRY /> </parametermap> 14
sqlmap parametermap(inline) <statement id= insertproduct parameterclass= com.domain.product > 1 </statement> insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (#id#, #description#); 타입을선언하는것은다음의문법을사용함으로써인라인파라미터로할수있습니다. <statement id= insertproduct parameterclass= com.domain.product > insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (#id:numeric#, #description:varchar#); </statement> 타입을선언하는것과 null 값대체는다음문법을사용함으로써인라인파라미터로할수있습니다. <statement id= insertproduct parameterclass= com.domain.product > insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (#id:numeric:-999999#, #description:varchar:no_entry#); </statement> 15
sqlmap resultclass <statement id="getperson" parameterclass= int resultclass="examples.domain.person"> 1 SELECT 2 PER_ID as id, 3 PER_FIRST_NAME as firstname, 4 PER_LAST_NAME as lastname, 5 PER_BIRTH_DATE as birthdate, 6 PER_WEIGHT_KG as weightinkilograms, 7 PER_HEIGHT_M as heightinmeters 8 FROM PERSON 9 WHERE PER_ID = #value# </statement> 16
sqlmap cachemodel 24 시간마다또는관련된 update 문이수행될때마다지워집니다.(flush) 1 LRU 캐쉬는객체가자동으로캐시로부터어떻게삭제되는지결정하기위해 Least Recently Used( 가장최근에사용된 ) 알고리즘을사용합니다. 캐쉬가가득찼을때가장최근에접근된객체는캐쉬로부터삭제됩니다. 2 FIFO = First In First Out( 먼저들어온것을먼저보낸다.) 3 OSCACHE = http://www.opensymphony.com/oscache/ <cachemodel id="product-cache" imlementation="lru"> 1 2 3 4 <flushinterval hours="24"/> <flushonexecute statement="insertproduct"/> <flushonexecute statement="updateproduct"/> <flushonexecute statement="deleteproduct"/> 5 <property name= size value= 1000 /> </cachemodel> <statement id= getproductlist parameterclass= int cachemodel= product-cache > 1 select * from PRODUCT where PRD_CAT_ID = #value# </statement> 17
sqlmap 캐쉬타입들 <cachemodel id="product-cache" type="memory"> <flushinterval hours="24"/> <flushonexecute statement="insertproduct"/> <flushonexecute statement="updateproduct"/> <flushonexecute statement="deleteproduct"/> <property name= reference-type value= WEAK /> </cachemodel> WEAK (default) 이참조타입은대부분의경우에가장좋은선택이고참조타입을정의하지않는다면디폴트로설정되는값입니다. 이것은대개의결과에성능을향상시킬것입니다. 하지만할당된다른객체내에서사용되는메모리를완전히제거할것입니다. 그결과는현재사용중이지않다는것을가정합니다. 18
sqlmap 캐쉬타입들 <cachemodel id="product-cache" type="memory"> <flushinterval hours="24"/> <flushonexecute statement="insertproduct"/> <flushonexecute statement="updateproduct"/> <flushonexecute statement="deleteproduct"/> <property name= reference-type value= SOFT /> </cachemodel> SOFT 이참조타입은결과물이현재사용중이지않고메모리가다른객체를위해필요한경우에메모리가바닥나는가능성을제거할것입니다. 어쨌든이것은할당되고좀더중요한객체에유효하지않는메모리에대해대부분공격적인참조타입이아닙니다. 19
sqlmap 캐쉬타입들 <cachemodel id="product-cache" type="memory"> <flushinterval hours="24"/> <flushonexecute statement="insertproduct"/> <flushonexecute statement="updateproduct"/> <flushonexecute statement="deleteproduct"/> <property name= reference-type value= STRONG /> </cachemodel> STRONG 이참조타입은명시적을캐쉬가삭제될때까지메모리내에저장된결과물을보증합니다. 이것은 1) 매우작음, 2) 절대적으로정적, and 3) 매우종종사용되는결과에좋습니다. 장점은특수한쿼리를위해매우좋은성능을보입니다. 단점은결과물에의해사용되는메모리가필요할때다른객체를위해메모리를반환하지않습니다. 20
sqlmap xmlresultname <select id="getperson" parameterclass= int resultclass="xml" xmlresultname= person > 1 2 3 4 5 6 7 8 </select> SELECT PER_ID as id, PER_FIRST_NAME as firstname, PER_LAST_NAME as lastname, PER_BIRTH_DATE as birthdate, PER_WEIGHT_KG as weightinkilograms, PER_HEIGHT_M as heightinmeters FROM PERSON WHERE PER_ID = #value# 위 select statement 는다음구조의 XML 객체를생성합니다. <person> 1 2 3 4 5 6 </person> <id>1</id> <firstname>clinton</firstname> <lastname>begin</lastname> <birthdate>1900-01-01</birthdate> <weightinkilograms>89</weightinkilograms> <heightinmeters>1.77</heightinmeters> 21
sqlmap 원시타입파라미터 원시타입래퍼객체 (String, Integer, Date 등등 ) 를파라미터로사용할수있습니다. <statement id= insertproduct parameter= java.lang.integer > 1 select * from PRODUCT where PRD_ID = #value# </statement> 22
sqlmap Map 타입파라미터 <statement id= insertproduct parameterclass= java.util.map > select * from PRODUCT where PRD_CAT_ID = #catid# and PRD_CODE = #code# </statement> 23
sqlmap result maps <resultmap id= resultmapname class= some.domain.class [extends= parent-resultmap ]> 1 <result property= propertyname column= COLUMN_NAME [columnindex= 1 ] [javatype= int ] [jdbctype= NUMERIC ] [nullvalue= -999999 ] [select= someotherstatement ] 2 /> <result /> 3 4 5 <result /> <result /> </resultmap> 24
sqlmap 내포하는 Result Maps <statement id= getproduct resultclass= com.ibatis.example.product > 1 select 2 PRD_ID as id, 3 PRD_DESCRIPTION as description 4 from PRODUCT 5 where PRD_ID = #value# </statement> 25
sqlmap 원시타입의 Results ( 이를테면 String, Integer, Boolean) <resultmap id= get-product-result class= java.lang.string > 1 <result property= value column= PRD_DESCRIPTION /> </resultmap> 좀더간단한접근법은맵핑된 statement 안에서간단하게 result class 를사용하는것입니다.( as 키워드를사용해서 value 라는칼럼별칭을사용하는것을주의깊게보세요.) <statement id= getproductcount resultclass= java.lang.integer > 1 select count(1) as value 2 from PRODUCT </statement> 26
sqlmap Map Results <resultmap id= get-product-result class= java.util.hashmap > 1 <result property= id column= PRD_ID /> 2 <result property= code column= PRD_CODE /> 3 <result property= description column= PRD_DESCRIPTION /> 4 <result property= suggestedprice column= PRD_SUGGESTED_PRICE /> </resultmap> 간단하게사용하는법 <statement id= getproductcount resultclass= java.util.hashmap > 1 select * from PRODUCT </statement> 27
sqlmap 복합 (Complex) Properties ( 이를테면사용자에의해정의된클래스의프라퍼티 ) <resultmap id= get-product-result class= com.ibatis.example.product > 1 2 3 </resultmap> <result property= id column= PRD_ID /> <result property= description column= PRD_DESCRIPTION /> <result property= category column= PRD_CAT_ID select= getcategory /> <resultmap id= get-category-result class= com.ibatis.example.category > 1 <result property= id column= CAT_ID /> 2 <result property= description column= CAT_DESCRIPTION /> </resultmap> <statement id= getproduct parameterclass= int resultmap= get-product-result > 1 select * from PRODUCT where PRD_ID = #value# </statement> <statement id= getcategory parameterclass= int resultmap= get-categoryresult > 1 select * from CATEGORY where CAT_ID = #value# </statement> 28
sqlmap N+1 Selects (1:1) 피하기 복합프로퍼티사용시퍼포먼스에악영향이있을수있습니다. 해결방법 ( 조인사용 ) <resultmap id= get-product-result class= com.ibatis.example.product > 1 <result property= id column= PRD_ID /> 2 <result property= description column= PRD_DESCRIPTION /> 3 <result property= category.id column= CAT_ID /> 4 <result property= category.description column= CAT_DESCRIPTION /> </resultmap> <statement id= getproduct parameterclass= int resultmap= getproduct-result > 1 select * from PRODUCT, CATEGORY where PRD_CAT_ID=CAT_ID and PRD_ID = #value# </statement> 하지만위의조인이항상좋은것만은아니니주의해서사용해야합니다. 29
sqlmap 복합키또는다중복합파라미터프라퍼티 <resultmap id= get-order-result class= com.ibatis.example.order > 1 <result property= id column= ORD_ID /> 2 <result property= customerid column= ORD_CST_ID /> 3 4 <result property= payments column= {itemid=ord_id, custid=ord_cst_id} select= getorderpayments /> </resultmap> <statement id= getorderpayments resultmap= get-payment-result > 1 select * from PAYMENT 2 where PAY_ORD_ID = #itemid# 3 and PAY_CST_ID = #custid# </statement> 30
sqlmap 동적으로맵핑되는 Statements <select id="dynamicgetaccountlist cachemodel="account-cache resultmap="account-result" > 1 select * from ACCOUNT 2 <isgreaterthan prepend="and" property="id" comparevalue="0"> 3 4 where ACC_ID = #id# </select> </isgreaterthan> order by ACC_LAST_NAME 31
sqlmap 바이너리조건적인요소 <isequal> 1 프라퍼티와값또는다른프라퍼티가같은지체크. <isnotequal> 1 프라퍼티와값또는다른프라퍼티가같지않은지체크. <isgreaterthan> 1 프라퍼티가값또는다른프라퍼티보다큰지체크. <isgreaterequal> 1 프라퍼티가값또는다른프라퍼티보다크거나같은지체크. <islessthan> 1 프라퍼티가값또는다른프라퍼티보다작은지체크. <islessequal> 프라퍼티가값또는다른프라퍼티보다작거나같은지체크. 사용법예제 : 1 <islessequal prepend= AND property= age comparevalue= 18 > ADOLESCENT = TRUE 2 </islessequal> 32
sqlmap 단일조건적인요소 <ispropertyavailable> 1 프라퍼티가유효한지체크 ( 이를테면파라미터빈의프라퍼티이다.) <isnotpropertyavailable> 1 프라퍼티가유효하지않은지체크 ( 이를테면파라미터의프라퍼티가아니다.) <isnull> 1 <isnotnull> 1 <isempty> 1 프라퍼티가 null 인지체크 프라퍼티가 null 이아닌지체크 Collection, 문자열또는 String.valueOf() 프라퍼티가 null 이거나 empty( or size() < 1) 인지체크 <isnotempty> 1 Collection, 문자열또는 String.valueOf() 프라퍼티가 null 이아니거나 empty( or size() < 1) 가아닌지체크. 2 Example Usage: 3 <isnotempty prepend= AND property= firstname > FIRST_NAME=#firstName# 4 </isnotempty> 33
sqlmap 다른요소들 <isparameterpresent> 1 파라미터객체가존재 (not null) 하는지보기위해체크. <isnotparameterpresent> 1 파라미터객체가존재하지 (null) 않는지보기위해체크. 2 3 4 Example Usage: <isnotparameterpresent prepend= AND > EMPLOYEE_TYPE = DEFAULT </isnotparameterpresent> 34
sqlmap 다른요소들 <iterate> 1 java.util.list 타입의프라퍼티반복 사용법예제 : <iterate prepend= AND property= usernamelist open= ( close= ) conjunction= OR > 1 username=#usernamelist[]# </iterate> 주의 : iterator 요소를사용할때리스트프라퍼티의끝에중괄호 [] 를포함하는것은중요합니다. 중괄호는문자열처럼리스트를간단하게출력함으로부터파서를유지하기위해리스트처럼객체를구별합니다. 35
트랜잭션 트랜잭션 private Reader reader = new Resources.getResourceAsReader ("com/ibatis/example/sqlmap-config.xml"); private SqlMapClient sqlmap = XmlSqlMapBuilder.buildSqlMap(reader); public updateitemdescription (String itemid, String newdescription) throws SQLException { 1 try { sqlmap.starttransaction (); Item item = (Item) sqlmap.queryforobject ("getitem", itemid); item.setdescription (newdescription); sqlmap.update ("updateitem", item); sqlmap.committransaction (); 2 } finally { sqlmap.endtransaction ();// 반드시해줘야합니다. 3 } } 36
트랜잭션 트랜잭션 ( 두개의트랜젝션 ) SQL Map 설정파일에 <transactionmanager> type 속성을 JTA 로설정해야만하고 UserTransaction 을 SqlMapClient 인스턴스가 UserTransaction 인스턴스를찾는곳에전체 JNDI 이름에셋팅해야합니다. try { 1 ordersqlmap.starttransaction(); 2 storesqlmap.starttransaction(); 3 ordersqlmap.insertorder( ); 4 ordersqlmap.updatequantity( ); 5 storesqlmap.committransaction(); 6 ordersqlmap.committransaction(); } finally { 1 try { storesqlmap.endtransaction(); 2 } finally { ordersqlmap.endtransaction(); 3 } } 37
배치 배치 (Batches) sqlmap.startbatch(); // execute statements in between sqlmap.executebatch(); 38
SqlMapClient SqlMapClient 주요메소드 public int insert(string statementname, Object parameterobject) throws SQLException public int update(string statementname, Object parameterobject) throws SQLException public int delete(string statementname, Object parameterobject) throws SQLException public Object queryforobject(string statementname,object parameterobject) throws SQLException public Object queryforobject(string statementname,object parameterobject, Object resultobject) throws SQLException public List queryforlist(string statementname, Object parameterobject) throws SQLException 39
SqlMapClient SqlMapClient 주요메소드 public List queryforlist(string statementname, Object parameterobject, int skipresults, int maxresults) throws SQLException public List queryforlist (String statementname,object parameterobject, RowHandler rowhandler) throws SQLException public PaginatedList queryforpaginatedlist(string statementname,object parameterobject, int pagesize) throws SQLException public Map queryformap (String statementname, Object parameterobject,string keyproperty) throws SQLException public Map queryformap (String statementname, Object parameterobject,string keyproperty, String valueproperty) throws SQLException 40
SqlMapClient SqlMapClient 예제 Example 1: Update (insert, update, delete) 수행하기. sqlmap.starttransaction(); Product product = new Product(); product.setid (1); product.setdescription ( Shih Tzu ); int rows = sqlmap.insert ( insertproduct, product); sqlmap.committransaction(); 41
SqlMapClient SqlMapClient 예제 Example 2: 객체를위한쿼리수행하기. (select) sqlmap.starttransaction(); Integer key = new Integer (1); Product product = (Product)sqlMap.queryForObject ( getproduct, key); sqlmap.committransaction(); 42
SqlMapClient SqlMapClient 예제 Example 3: 미리할당된 result 객체를가지고객체를위한쿼리수행하기. sqlmap.starttransaction(); Customer customer = new Customer(); sqlmap.queryforobject( getcust, parameterobject, customer); sqlmap.queryforobject( getaddr, parameterobject, customer); sqlmap.committransaction(); 43
SqlMapClient SqlMapClient 예제 Example 4: 리스트를위한뭐리수행하기. (select) sqlmap.starttransaction(); List list = sqlmap.queryforlist ( getproductlist, null); sqlmap.committransaction(); 44
SqlMapClient SqlMapClient 예제 Example 5: 자동커밋 // When starttransaction is not called, the statements will // auto-commit. Calling commit/rollback is not needed. int rows = sqlmap.insert ( insertproduct, product); 45
SqlMapClient SqlMapClient 예제 Example 6: 결과경계를가지고리스트를위한쿼리수행하기. sqlmap.starttransaction(); List list = sqlmap.queryforlist ( getproductlist, null, 0, 40); sqlmap.committransaction(); 46
SqlMapClient SqlMapClient 예제 Example 7: RowHandler 를가지고쿼리수행하기. (select) public class MyRowHandler implements RowHandler { 1 public void handlerow (Object object, List list) throws SQLException { Product product = (Product) object; product.setquantity (10000); sqlmap.update ( updateproduct, product); // Optionally you could add the result object to the list. // The list is returned from the queryforlist() method. 2 } } sqlmap.starttransaction(); RowHandler rowhandler = new MyRowHandler(); List list = sqlmap.queryforlist ( getproductlist, null, rowhandler); sqlmap.committransaction(); 47
SqlMapClient SqlMapClient 예제 Example 8: 페이지처리된리스트를위한쿼리수행하기. (select) PaginatedList list = sqlmap.queryforpaginatedlist ( getproductlist, null, 10); list.nextpage(); list.previouspage(); 48
SqlMapClient SqlMapClient 예제 Example 9: map 을위한쿼리수행하기. sqlmap.starttransaction(); Map map = sqlmap.queryformap ( getproductlist, null, productcode ); sqlmap.committransaction(); Product p = (Product) map.get( EST-93 ); 49
Logging Log4j log4j.properties # Global logging configuration log4j.rootlogger=error, stdout # SqlMap logging configuration... #log4j.logger.com.ibatis=debug #log4j.logger.com.ibatis.common.jdbc.simpledatasource=debug #log4j.logger.com.ibatis.common.jdbc.scriptrunner=debug #log4j.logger.com.ibatis.sqlmap.engine.impl.sqlmapclientdelegate=debug #log4j.logger.java.sql.connection=debug #log4j.logger.java.sql.statement=debug #log4j.logger.java.sql.preparedstatement=debug #log4j.logger.java.sql.resultset=debug # Console output... log4j.appender.stdout=org.apache.log4j.consoleappender log4j.appender.stdout.layout=org.apache.log4j.patternlayout log4j.appender.stdout.layout.conversionpattern=%5p [%t] -%m%n 50
SimpleDataSource (com.ibatis.common.jdbc.*) SimpleDataSource 예제 : SimpleDataSource 사용하기 1 properties 셋팅 JDBC.Driver,Yes,JDBC 드라이버클래스명 JDBC.ConnectionURL,Yes, JDBC connection URL. JDBC.Username,Yes, 데이터베이스에로그인하기위한유저명 JDBC.Password,Yes, 데이터베이스에로그인하기위한패스워드 DataSource datasource = new SimpleDataSource(props); //properties usually loaded from a file Connection conn = datasource.getconnection(); //..database queries and updates conn.commit(); conn.close(); //connections retrieved from SimpleDataSource will return to the pool when closed 51
ScriptRunner (com.ibatis.common.jdbc.*) ScriptRunner 예제사용법 1: 현재존재하는 connection 을사용하기 Connection conn = getconnection(); //some method to get a Connection ScriptRunner runner = new ScriptRunner (); runner.runscript(conn, Resources.getResourceAsReader("com/some/resource/path/initializ e.sql")); conn.close(); 52
ScriptRunner (com.ibatis.common.jdbc.*) ScriptRunner 예제사용법 2: 새로운 connection 을사용하기 ScriptRunner runner = new ScriptRunner ( com.some.driver, jdbc:url://db, login, password ); runner.runscript(conn, new FileReader("/usr/local/db/scripts/ initialize-db.sql")); 53
ScriptRunner (com.ibatis.common.jdbc.*) ScriptRunner 프로퍼티 1 2 3 4 5 driver=org.hsqldb.jdbcdriver url=jdbc:hsqldb:. username=dba password=whatever stoponerror=true 예제사용법 3: 프라퍼티로부터새로운 connection 을사용하기 Properties props = getproperties (); // some properties from somewhere ScriptRunner runner = new ScriptRunner (props); runner.runscript(conn, new FileReader("/usr/local/db/scripts/ initialize-db.sql")); 54