문제점

자바코드에서 SQL을 작성하는 작업은 자바 개발자를 가장 힘들게 하는 것중 하나이다. 대개는 동적 SQL을 작성해야 하지만 종종 파일이나 저장프로시저에 작성해야 할수도 있다. 이미 본것처럼 마이바티스는 XML매핑 기능으로 동적 SQL을 처리하는 강력한 기능을 제공한다. 하지만 종종 자바 코드를 사용해서 SQL구문을 만들어야 할수도 있다. 마이바티스는 이런 경우를 위한 기능도 제공한다. 더하기 기호나 따옴표, 개행처리, 포매팅 그리고 콤마나 문자열을 합치는 등의 다양한 조건을 처리하지 않아도 되도록 해준다. 정말 자바코드에서 SQL을 동적으로 만드는 것은 악몽과 같다. 예를들어보자.

String sql = "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, "
"P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON " +
"FROM PERSON P, ACCOUNT A " +
"INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID " +
"INNER JOIN COMPANY C on D.COMPANY_ID = C.ID " +
"WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) " +
"OR (P.LAST_NAME like ?) " +
"GROUP BY P.ID " +
"HAVING (P.LAST_NAME like ?) " +
"OR (P.FIRST_NAME like ?) " +
"ORDER BY P.ID, P.FULL_NAME";

해결방법

마이바티스 3은 이런 문제를 해결하기 위해 편리한 클래스를 제공한다. SQL클래스를 사용해서 인스턴스를 만들고 SQL구문을 만드는 메소드를 호출하자. 위 문제가 되는 예제를 SQL클래스를 사용해서 재작성하면 다음과 같다.

private String selectPersonSql() {
  return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
    FROM("PERSON P");
    FROM("ACCOUNT A");
    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
    WHERE("P.ID = A.ID");
    WHERE("P.FIRST_NAME like ?");
    OR();
    WHERE("P.LAST_NAME like ?");
    GROUP_BY("P.ID");
    HAVING("P.LAST_NAME like ?");
    OR();
    HAVING("P.FIRST_NAME like ?");
    ORDER_BY("P.ID");
    ORDER_BY("P.FULL_NAME");
  }}.toString();
}

이 예제가 특별한게 뭘까? 자 조금더 자세히 보자. "AND" 키워드가 중복으로 나오는 것이나 "WHERE"과 "AND"를 선택하는데 걱정할 필요가 없다. SQL클래스는 "WHERE"가 필요한 위치를 이해하고 처리한다. 그리고 "AND"를 사용하고 문자열을 합쳐야 하는 부분에 대해서도 알고 처리한다.

SQL 클래스

여기에 몇가지 예제가 있다.

// 익명 내부 클래스
public String deletePersonSql() {
  return new SQL() {{
    DELETE_FROM("PERSON");
    WHERE("ID = #{id}");
  }}.toString();
}

// 빌더 / 깔끔한 형태
public String insertPersonSql() {
  String sql = new SQL()
    .INSERT_INTO("PERSON");
    .VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
    .VALUES("LAST_NAME", "#{lastName}")
    .toString();
  return sql;
}

// 분기처리를 포함한 형태 (final 파라미터, 파라미터에 접근하는 익명 내부 클래스가 필요함)
public String selectPersonLike(final String id, final String firstName, final String lastName) {
  return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
    FROM("PERSON P");
    if (id != null) {
      WHERE("P.ID like #{id}");
    }
    if (firstName != null) {
      WHERE("P.FIRST_NAME like #{firstName}");
    }
    if (lastName != null) {
      WHERE("P.LAST_NAME like #{lastName}");
    }
    ORDER_BY("P.LAST_NAME");
  }}.toString();
}

public String deletePersonSql() {
  return new SQL() {{
    DELETE_FROM("PERSON");
    WHERE("ID = #{id}");
  }}.toString();
}

public String insertPersonSql() {
  return new SQL() {{
    INSERT_INTO("PERSON");
    VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
    VALUES("LAST_NAME", "#{lastName}");
  }}.toString();
}

public String updatePersonSql() {
  return new SQL() {{
    UPDATE("PERSON");
    SET("FIRST_NAME = #{firstName}");
    WHERE("ID = #{id}");
  }}.toString();
}

NOTE It is important to note that SQL class writes LIMIT, OFFSET, OFFSET n ROWS and FETCH FIRST n ROWS ONLY clauses into the generated statement as is. In other words, the library does not attempt to normalize those values for databases that don’t support these clauses directly. Therefore, it is very important for users to understand whether or not the target database supports these clauses. If the target database does not support these clauses, then it is likely that using this support will create SQL that has runtime errors.

3.4.2 버전부터, variable-length 매개변수를 아래와 같이 사용할 수 있습니다.

public String selectPersonSql() {
  return new SQL()
    .SELECT("P.ID", "A.USERNAME", "A.PASSWORD", "P.FULL_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME")
    .FROM("PERSON P", "ACCOUNT A")
    .INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID", "COMPANY C on D.COMPANY_ID = C.ID")
    .WHERE("P.ID = A.ID", "P.FULL_NAME like #{name}")
    .ORDER_BY("P.ID", "P.FULL_NAME")
    .toString();
}

public String insertPersonSql() {
  return new SQL()
    .INSERT_INTO("PERSON")
    .INTO_COLUMNS("ID", "FULL_NAME")
    .INTO_VALUES("#{id}", "#{fullName}")
    .toString();
}

public String updatePersonSql() {
  return new SQL()
    .UPDATE("PERSON")
    .SET("FULL_NAME = #{fullName}", "DATE_OF_BIRTH = #{dateOfBirth}")
    .WHERE("ID = #{id}")
    .toString();
}

Since version 3.5.2, you can create insert statement for bulk insert as follow:

public String insertPersonsSql() {
  // INSERT INTO PERSON (ID, FULL_NAME)
  //     VALUES (#{mainPerson.id}, #{mainPerson.fullName}) , (#{subPerson.id}, #{subPerson.fullName})
  return new SQL()
    .INSERT_INTO("PERSON")
    .INTO_COLUMNS("ID", "FULL_NAME")
    .INTO_VALUES("#{mainPerson.id}", "#{mainPerson.fullName}")
    .ADD_ROW()
    .INTO_VALUES("#{subPerson.id}", "#{subPerson.fullName}")
    .toString();
}

Since version 3.5.2, you can create select statement for limiting search result rows clause as follow:

public String selectPersonsWithOffsetLimitSql() {
  // SELECT id, name FROM PERSON
  //     LIMIT #{limit} OFFSET #{offset}
  return new SQL()
    .SELECT("id", "name")
    .FROM("PERSON")
    .LIMIT("#{limit}")
    .OFFSET("#{offset}")
    .toString();
}

public String selectPersonsWithFetchFirstSql() {
  // SELECT id, name FROM PERSON
  //     OFFSET #{offset} ROWS FETCH FIRST #{limit} ROWS ONLY
  return new SQL()
    .SELECT("id", "name")
    .FROM("PERSON")
    .OFFSET_ROWS("#{offset}")
    .FETCH_FIRST_ROWS_ONLY("#{limit}")
    .toString();
}

SqlBuilder 와 SelectBuilder (향후 제거예정)

3.2버전 이전에는 다른 방법을 사용했다. 자바 DSL을 다루기 힘든 언어의 제한점을 가리기 위해 ThreadLocal변수를 사용했다. 하지만 이 방법은 이제 사용하지 않길 바란다. 최근에는 빌더 타입의 패턴과 익명 내부 클래스를 사용하는 추세이다. 그러므로 SelectBuilder 와 SqlBuilder는 향후 제거할 예정이다.

다음에 나열된 메소드는 SqlBuilder 와 SelectBuilder클래스에서만 사용할 수 있다.

SelectBuilder와 SqlBuilder클래스가 신기하지는 않지만 이 클래스가 동작하는 방식을 아는게 중요하다. SelectBuilder와 SqlBuilder는 문법을 명확히 처리하기 위해 Static Imports와 ThreadLocal변수의 조합을 사용한다. 이 두가지 클래스를 사용하기 위해 다음처럼 정적인 방법으로 import할 필요가 있다.

import static org.apache.ibatis.jdbc.SelectBuilder.*;

import static org.apache.ibatis.jdbc.SqlBuilder.*;

다음처럼 메소드를 생성하도록 해준다.

/* 향후 제거예정 */
public String selectBlogsSql() {
  BEGIN(); // ThreadLocal 변수 제거
  SELECT("*");
  FROM("BLOG");
  return SQL();
}
        

/* 향후 제거예정 */
private String selectPersonSql() {
  BEGIN(); // ThreadLocal 변수 제거
  SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
  SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
  FROM("PERSON P");
  FROM("ACCOUNT A");
  INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
  INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
  WHERE("P.ID = A.ID");
  WHERE("P.FIRST_NAME like ?");
  OR();
  WHERE("P.LAST_NAME like ?");
  GROUP_BY("P.ID");
  HAVING("P.LAST_NAME like ?");
  OR();
  HAVING("P.FIRST_NAME like ?");
  ORDER_BY("P.ID");
  ORDER_BY("P.FULL_NAME");
  return SQL();
}