U've got a minute?

특정 row에 lock를 걸어서

다른 세션의 'select for update' 혹은 'select lock in share mode' 쿼리를 블락하고

싶은 경우 사용된다.

예)

select * from tableNm where column_name = '1' for update

출처 :

http://blog.daum.net/hazzling/17187062

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

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.

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:

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:

• 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:

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:

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

토드 6 Mysql alt + d + f 쿼리정렬... 자꾸 까먹네 ㅎㅎ;;

oracle에서 사용 했던 방식을 mysql로 적용 할려 하니 비슷하면서도 다른 부분이 많아 정리 하고자 한다.

1.문자열 합치기 

Oracle : '%'||A||'%'

Mysql  : CONCAT('%', A , '%')

2. null check

Oracle : SELECT NVL('컬럼명', '') FROM DUAL;

MySql : SELECT IFNULL('컬럼명', '') FROM DUAL;

3.현재 날짜시간

Oracle : SYSDATE

Mysql  : NOW() 

4.날짜 포맷

Oracle : TO_CHAR(sysdate,'MMDDYYYYHH24MISS')

Mysql  : DATE_FORMAT(now(),'%Y%m%d%H%i%s')  

-> 여기서 대문자Y는 4자리 년도, 소문자 y는 2자리 년도 20150713142107

DATE_FORMAT(now(),'%Y%m%d') -> 20150713

5.날짜 포멧 : 요일

Oracle : 요일이 1~7로 인식함  -> TO_CHAR(SYSDATE - 1, 'D') 

Mysql : 요일이 0~6으로 인식   -> DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%w')