insert 구문에서 session.commit() 적용

Framework & Platform/mybatis 2012. 5. 1. 17:15



session.commit() 적용 전

 : sqlSession created

 : Openning JDBC Connection

 : Checked out connection 31516585 from pool.

 : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e0e7a9]

 : ooo Using Connection [com.mysql.jdbc.JDBC4Connection@1e0e7a9]

 : ==>  Preparing: INSERT INTO SDK_SMS_SEND(USER_ID, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_COUNT, DEST_INFO, RESERVED1, SEND_COUNT) VALUES('myshuttle', ?, ?, sysdate()+0, ?, ?, ?, ?, ?, '3') 

 : ==> Parameters: 고객 전송 문자(String), 안녕하세요.(String), 20120401010101(String), 020000000(String), 2(Integer), sklee^00010001000|iu^00010002000(String), admin(String)

 : ooo Using Connection [com.mysql.jdbc.JDBC4Connection@1e0e7a9]

 : ==>  Preparing: SELECT LAST_INSERT_ID(); 

 : ==> Parameters: 

 : <==    Columns: LAST_INSERT_ID()

 : <==        Row: 21

 : Rolling back JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e0e7a9]

 : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e0e7a9]

 : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e0e7a9]

 : Returned connection 31516585 to pool.

 : sqlSession closed




session.commit() 적용 후

 : sqlSession created

 : Openning JDBC Connection

 : Checked out connection 6826369 from pool.

 : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@682981]

 : ooo Using Connection [com.mysql.jdbc.JDBC4Connection@682981]

 : ==>  Preparing: INSERT INTO SDK_SMS_SEND(USER_ID, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_COUNT, DEST_INFO, RESERVED1, SEND_COUNT) VALUES('myshuttle', ?, ?, sysdate()+0, ?, ?, ?, ?, ?, '3') 

 : ==> Parameters: 고객 전송 문자(String), 안녕하세요.(String), 20120401010101(String), 020000000(String), 2(Integer), sklee^00010001000|iu^00010002000(String), admin(String)

 : ooo Using Connection [com.mysql.jdbc.JDBC4Connection@682981]

 : ==>  Preparing: SELECT LAST_INSERT_ID(); 

 : ==> Parameters: 

 : <==    Columns: LAST_INSERT_ID()

 : <==        Row: 22

 : Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@682981]

 : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@682981]

 : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@682981]

 : Returned connection 6826369 to pool.

 : sqlSession closed

저작자표시

'Framework & Platform > mybatis' 카테고리의 다른 글

iBatis - DBCP  (0) 2012.12.07
Spring3 MVC에서 Mybatis 설정하기  (0) 2012.05.09
useGenerateKeys 사용하기 예제  (0) 2012.05.01
mybatis 3 settings 예제  (0) 2012.04.30
mybatis password 암호화/복호화  (0) 2012.04.28
Posted by linuxism
,

useGenerateKeys 사용하기 예제

Framework & Platform/mybatis 2012. 5. 1. 13:03

Inserting auto generated id using mybatis and return the generated id to java

In our previous tutorial of MyBatis, we see how to configure MyBatis in in your project and insert data to database using Mybatis proxy functionality. You have notice that, in real life projects the value of id columns are generated at runtime to identify the object uniquely, most of the time it is generated while the object is created in java. To generate such auto generated id values in java needs extra calculation of finding out a unique id in system and may cause a bottleneck while more data are saved.

So, what will be the solution?

The solution is that, we can get this facility from underlying database that saves data. We can use database specific facilities to generate unique id and then associate that id with the object.

We can use:

  1. AUTO_INCREMENT facility in MySQL Database
  2. IDENTITY facility in MS SQL Server.
  3. A combination of sequence and trigger to generate new unique id in Oracle server.

Each database server has such facility that can be used to generate unique id for new records.

In this tutorial, we will modify our previous example to include auto generated id and return that id to java.

This tutorial is assuming that you have read previous tutorial of Configuring MyBatis 3. If you have not read it, it is recommended to read.

MyBatis version 3.0.4 has a bug in regarding auto generated id, you should use MyBatis version 3.1.0 instead.

Tools Used:

  1. MyBatis 3.1.0
  2. MySql Java Connector 5.1

First of all, we have to change the schema of table Product to make the id field auto generated. Following is the DDL command to make the Product table with auto generated id:

1CREATE TABLE Product(id BIGINT NOT NULL AUTO_INCREMENT , brand VARCHAR(20),
2model VARCHAR(20), name VARCHAR(30) , PRIMARY KEY (id));

Here, I have used AUTO_INCREMENT to generate unique id in MySQL database.

Now, to use auto generated id in our mapping file ProductServices.xml to modify our <insert/> command to use it:

1<?xml version="1.0" encoding="UTF-8"?>
2<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
3"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4 
5<mapper namespace="com.raistudies.services.ProductServices">
6 
7    <insert id="save" parameterType="product" useGeneratedKeys="true" keyProperty="id"�  keyColumn="id">
8        INSERT INTO Product (brand,model,name)
9        VALUE (#{brand}, #{model}, #{name} )
10        <selectKey keyProperty="id" resultType="long" order="AFTER">
11            SELECT LAST_INSERT_ID();
12        </selectKey>
13    </insert>
14 
15</mapper>
  • You can identify three changes that we have to made in <insert/> command tag to use auto generated id and then return the newly generated id to java.
  • We have added useGeneratedKeys=”true”, this specify mybatis that the id will be auto generated by database.We also have to specify keyProperty=”id” and keyColumn=”id” which tells the property name of POJO class Product that will be used as ID and also the column name of table product that is used as id.
  • We have to remove id field from insert DDL, so that database can generate a new id for the column and put it.
  • We have used : 
    <selectKey keyProperty=”id” resultType=”long” order=”AFTER”>
                SELECT LAST_INSERT_ID();
            </selectKey>
    That will return newly generated id and save it to the id property of POJO class Product. The query written in between <selectKey/> tags is database specific and it may be change according to database. In this example, we have used the query that will work for MySQL database server only.

To test the functionality, we have made a little modification in our runner class:

1package com.raistudies.runner;
2 
3import java.io.IOException;
4import java.io.Reader;
5 
6import org.apache.ibatis.io.Resources;
7import org.apache.ibatis.session.SqlSession;
8import org.apache.ibatis.session.SqlSessionFactory;
9import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 
11import com.raistudies.domain.Product;
12import com.raistudies.services.ProductServices;
13 
14public class AppTester {
15private static SqlSessionFactory sessionFac = null;
16private static Reader reader;
17private static String CONFIGURATION_FILE = "sqlmap-config.xml";
18 
19    static{
20        try {
21            reader = Resources.getResourceAsReader(CONFIGURATION_FILE);
22            sessionFac = new SqlSessionFactoryBuilder().build(reader);
23        } catch (IOException e) {
24            e.printStackTrace();
25        }
26    }
27 
28    public static void main(String[] args) {
29        SqlSession session = sessionFac.openSession();
30        try {
31        ProductServices productServiceObj = session.getMapper(ProductServices.class);
32        Product product = new Product();
33        product.setBrand("LG");
34        product.setModel("P500");
35        product.setName("Optimus One");
36        productServiceObj.save(product);
37        System.out.println("The new id is : " + product.getId());
38        session.commit();
39 
40        } finally {
41            session.close();
42        }
43    }
44 
45}

The new code will not specify the value of id field and also print the new generated id in console.

Run the code and you will get output like this:

Auto generated id in MyBatis and return the new id to java

Using Auto Generated ID in MyBatis and return the new ID to java

You can also try the example yourself. Just download the code from bellow links, import the project in Eclipse and create the product table using given DDL command:

Code + lib: Download

Code: Download


출처 - http://www.raistudies.com/mybatis/inserting-auto-generated-id-using-mybatis-return-id-to-java/




저작자표시

'Framework & Platform > mybatis' 카테고리의 다른 글

Spring3 MVC에서 Mybatis 설정하기  (0) 2012.05.09
insert 구문에서 session.commit() 적용  (0) 2012.05.01
mybatis 3 settings 예제  (0) 2012.04.30
mybatis password 암호화/복호화  (0) 2012.04.28
Mybatis selectList 예제  (0) 2012.03.28
Posted by linuxism
,

mybatis 3 settings 예제

Framework & Platform/mybatis 2012. 4. 30. 19:53


mybatis 3 settings


[MyBatis singleton class]
package meeton.web.util.mybatis;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatis {

    private static final MyBatis self = new MyBatis();

    public static MyBatis getInstance() {
        return self;
    }

    private SqlSessionFactory factory;

    private MyBatis() {
        reload();
    }

    public SqlSessionFactory getFactory() {
        return factory;
    }

    public void reload() {
        try {
            factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis.xml"));
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}





[mybatis.xml]
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="config.properties"/>

    <environments default="default">
        <environment id="default">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
                <property name="poolMaximumActiveConnections" value="20"/>
                <property name="poolMaximumIdleConnections" value="20"/>
                <property name="poolMaximumCheckoutTime" value="20000"/>
                <property name="poolPingEnabled" value="true"/>
                <property name="poolPingQuery" value="select 1"/>
                <property name="poolPingConnectionsNotUsedFor" value="43200"/>
                <property name="poolTimeToWait" value="30000"/>
                <property name="driver.encoding" value="UTF-8"/>
            </dataSource>
        </environment>
    </environments>

   <mappers>
        <mapper resource="root/sub1/sub2/sample.xml"/>
    </mappers>

</configuration>





[sample.xml]
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test.mysql">

    <select id="selectAllUsers" resultType="Map">
        select * from user
    </select>

    <select id="selectUser" parameterType="String" resultType="Map">
        select * from user where user = #{user}
    </select>

</mapper>


출처 - http://yousik.blogspot.com/2012/04/mybatis-singleton-class-package-meeton.html


==================================================================================

여기서 눈여겨 볼 사항이 <property name="poolPingQuery" value="select 1"/> 이다.

기존에 validationQuery 대신 poolPingQuery로 사용 하고 있다.


다음은 각 pool 과 관련된 속성 값 설명이다.

<property name="poolMaximumActiveConnections" value="20"/>

 : 동시 활성화 할 커넥션 수


<property name="poolMaximumIdleConnections" value="20"/>

: 유휴상태의 커넥션 수


<property name="poolMaximumCheckoutTime" value="20000"/>

: 커넥션 요청 후 획득까지 기다리는 시간


<property name="poolPingEnabled" value="true"/>

: 커넥션 ping 테스트


<property name="poolPingQuery" value="select 1"/>

: 커넥션이 살아 있는지 확인할 쿼리


<property name="poolPingConnectionsNotUsedFor" value="43200"/>
: 커넥션이 얼마 동안 유휴상태면 닫히는지 판단하는 시간


<property name="poolTimeToWait" value="30000"/>

: 사용 불가능한 커넥션 기다리는 시간


<property name="poolPingConnectionsOlderThan" value="43200"/>

: 어떤 커넥션이 닫힐 상태인지 판단하는 기준시간





저작자표시

'Framework & Platform > mybatis' 카테고리의 다른 글

insert 구문에서 session.commit() 적용  (0) 2012.05.01
useGenerateKeys 사용하기 예제  (0) 2012.05.01
mybatis password 암호화/복호화  (0) 2012.04.28
Mybatis selectList 예제  (0) 2012.03.28
mybatis 설치 예제  (0) 2012.03.18
Posted by linuxism
,

티스토리툴바