하늘을향해

다음은 MySQL JDBC INSERT 시 AUTO INCREMENT 값을 알아내는 예제이다.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLJdbcExample {

public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "apmsetup";
try {
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();

String sql = "INSERT INTO auto_increment_test_table (name) VALUE ('test')";
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
int autoIncrement = rs.getInt(1);
System.out.println("autoIncrement: " + autoIncrement);
}

}

위 예제는 JDBC의 기능을 활용한 버전이었고,

이번에는 MySQL의 SQL 문을 활용하는 버전이다.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLJdbcExample {

public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "apmsetup";
try {
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();

String sql = "INSERT INTO auto_increment_test_table (name) VALUE ('test')";
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
int autoIncrement = rs.getInt(1);
System.out.println("autoIncrement: " + autoIncrement);
}

sql = "SELECT LAST_INSERT_ID()";
rs = stmt.executeQuery(sql);
if (rs.next()) {
int autoIncrement = rs.getInt(1);
System.out.println("autoIncrement: " + autoIncrement);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}
http://devday.tistory.com/304 에서 펌

<?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="connector.project.mapper.LoginMapper">
 <cache />
 
 <select id="getIdAndPermission" parameterType="hashmap" resultType="org.apache.commons.collections.map.CaseInsensitiveMap" >
  <![CDATA[
  SELECT a.id as employee_id,b.permission_cd as permission
        FROM employee a,executive b
        WHERE 1=1 AND
        a.employee_no = b.employee_no AND
        a.id = #{id} and
        a.password = #{pw}
        ]]>
 </select>
</mapper>