JDBC 한글 Query Transaction 문제

JDBC로 한글 쿼리를 처리할 때 다양한 문제가 발생할 수 있다. 다음의 예를 살펴보자.

checkName.java

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	request.setCharacterEncoding("euc-kr");
	response.setContentType("text/html;charset=euc-kr");
	
	ResultSet rs = null;
	String name = request.getParameter("name");
	
	try {
		Context init = new InitialContext();
		DataSource ds = (DataSource)init.lookup("java:comp/env/jdbc/ProjectName");
		Connection conn = ds.getConnection();
		Statement stmt = conn.createStatement();
		
		String query = "SELECT * FROM people WHERE NAME='" + name + "'";
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			System.out.println(e);
		}
		
		if(rs.next()) {
			request.setAttribute("errmsg", "The name already exists.");
			RequestDispatcher dispatcher = request.getRequestDispatcher("searchName.jsp");
			dispatcher.forward(request, response);
		}
		
		rs.close();
		stmt.close();
	} catch (SQLException e) {
	} catch (Exception e) {
	}
}

여기서 디버깅해보면 ResultSet rs = null 이라고 나온다. 그런데 executeQuery()함수는 null을 return하지 않는다.

executeQuery

ResultSet executeQuery(String sql) throws SQLException

Executes the given SQL statement, which returns a single ResultSet object. 

Note:This method cannot be called on a PreparedStatement or CallableStatement.

Parameters:

sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement 

Returns:

a ResultSet object that contains the data produced by the given query; never null 

Throws: 

SQLException - if a database access error occurs, this method is called on a closed Statement, the given SQL statement produces anything other than a single ResultSet object, the method is called on a PreparedStatement or CallableStatement 

SQLTimeoutException - when the driver has determined that the timeout value that was specified by the setQueryTimeout method has been exceeded and has at least attempted to cancel the currently running Statement

무엇이 문제일까?

SQLException을 체크해보니, 문제는 name 변수가 한글이라서 쿼리처리 시 데이터 및 테이블, Database간의 charset차이로 인한 오류였다.

Illegal mix of collations (euckr_korean_ci, IMPLICIT) and (latin1_swedish_ci, COERCIBLE) for operation '='

시간이 없어서 해결방법은 내일 Posting...