/*
 * Copyright 2002-2008 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
*/
package integration.anyframe.services.query;

import integration.anyframe.services.AbstractTest;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import anyframe.core.query.IQueryService;

public class QueryServiceTestCallableStatement extends AbstractTest {
	/**
	 * 테스트 수행을 위한 main
	 */
	public static void main(String[] args) throws Exception {
		QueryServiceTestCallableStatement queryTest = new QueryServiceTestCallableStatement();
		// 1. initialize context
		queryTest.setup();
		// 2. testCallableStatementProcedure
		queryTest.testCallableStatementFunction();
		// 3.
		queryTest.testCallableStatementBySQL();
		// 4.
		queryTest.testCallableStatementBySQLWithPaging();
		// 5. close context
		queryTest.teardown();

		System.out.println("Successful!!!!!");
	}

	protected void setup() {
		super.setup();
		try {
			testInit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 테스트 수행을 위한 Function 초기화
	 */
	public void testInit() throws Exception {
		try {
			DataSource dataSource = (DataSource) context
					.getBean("oracle_datasource");
			Connection conn = dataSource.getConnection();
			try {
				Statement statement = conn.createStatement();
				try {
					statement.executeUpdate("DROP FUNCTION test_function");
				} catch (SQLException e) {
				}
				statement
						.executeUpdate("CREATE OR REPLACE FUNCTION test_function "
								+ "(v_in1 IN number) RETURN number "
								+ "IS BEGIN return 1; END;");
			} catch (Exception e) {
			} finally {
				conn.close();
			}
		} catch (SQLException e) {
			throw new Exception("Unable to initialize database for test." + e);
		}
	}

	/**
	 * 매핑 XML 파일에 정의되어 있는 query id를 이용해 Function을 호출한다.
	 */
	public void testCallableStatementFunction() throws Exception {
		IQueryService queryService = (IQueryService) context
				.getBean("oracle_queryservice");

		HashMap inVal = new HashMap();
		inVal.put("inVal", new Integer(10));
		
		Map results = queryService.execute("callFunction", inVal);
		BigDecimal rtVal = (BigDecimal) results.get("outVal");

		if (rtVal.intValue() != 1) {
			throw new Exception("testCallableStatementProcedure failed");
		}
	}

	/**
	 * 매핑 XML 파일에 정의되어 있지 않은 경우에도 특정 Function을 호출할 수 있다.
	 * Map executeBySQL(String sql, String[] types, String[] names, String[] bindings, Map values) 
	 */
	public void testCallableStatementBySQL() throws Exception {
		IQueryService queryService = (IQueryService) context
				.getBean("oracle_queryservice");

		String sql = "{? = call test_function(?)}";
		String[] types = { "NUMERIC", "NUMERIC" };
		String[] bindings = { "OUT", "IN" };
		String[] names = { "outVal", "inVal" };
		
		HashMap inVal = new HashMap();
		inVal.put("inVal", new Integer(10));

		Map results = queryService.executeBySQL(sql, types, names, bindings,
				inVal);
		BigDecimal rtVal = (BigDecimal) results.get("outVal");

		if (rtVal.intValue() != 1) {
			throw new Exception("testCallableStatementBySQL failed");
		}
	}

	/**
	 * 매핑 XML 파일에 정의되어 있지 않은 경우에도 특정 Function을 호출할 수 있다.
	 * 이 메소드에서는 페이징 처리된 조회 결과를 얻고자 한다.
	 * Map executeBySQL(String sql, String[] types, String[] names, 
	 * 					String[] bindings, Map values, int pageIndex, int pageSize) 
	 */
	public void testCallableStatementBySQLWithPaging() throws Exception {
		IQueryService queryService = (IQueryService) context
				.getBean("oracle_queryservice");

		String sql = "{? = call test_function(?)}";
		String[] types = { "NUMERIC", "NUMERIC" };
		String[] bindings = { "OUT", "IN" };
		String[] names = { "outVal", "inVal" };
		
		HashMap inVal = new HashMap();
		inVal.put("inVal", new Integer(10));

		Map results = queryService.executeBySQL(sql, types, names, bindings,
				inVal, 1, 1);
		BigDecimal rtVal = (BigDecimal) results.get("outVal");

		if (rtVal.intValue() != 1) {
			throw new Exception("testCallableStatementBySQLWithPaging failed");
		}
	}

	protected String[] getConfigLocations() {
		return new String[] {
				"classpath*:/common/applicationContext-*.xml",
				"classpath*:/services/datasource/applicationContext-datasource-oracle.xml",
				"classpath*:/services/query/applicationContext-query-oracle.xml",
				"classpath*:/services/query/applicationContext-query-sqlloader.xml" };
	}
}