/*
 *    Copyright 2009-2012 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 org.apache.ibatis.submitted.sptests;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;

import java.io.Reader;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.BeforeClass;
import org.junit.Test;

public class SPTest {
  private static SqlSessionFactory sqlSessionFactory;

  @BeforeClass
  public static void initDatabase() throws Exception {
    Connection conn = null;

    try {
      Class.forName("org.hsqldb.jdbcDriver");
      conn = DriverManager.getConnection("jdbc:hsqldb:mem:sptest", "sa", "");

      Reader reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/sptests/CreateDB.sql");

      ScriptRunner runner = new ScriptRunner(conn);
      runner.setDelimiter("go");
      runner.setLogWriter(null);
      runner.setErrorLogWriter(null);
      runner.runScript(reader);
      conn.commit();
      reader.close();

      reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/sptests/MapperConfig.xml");
      sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
      reader.close();
    } finally {
      if (conn != null) {
        conn.close();
      }
    }
  }

  /*
   * This test shows how to use input and output parameters in a stored
   * procedure. This procedure does not return a result set.
   * 
   * This test shows using a multi-property parameter.
   */
  @Test
  public void testAdderAsSelect() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);
      spMapper.adderAsSelect(parameter);

      assertEquals((Integer) 5, parameter.getSum());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use input and output parameters in a stored
   * procedure. This procedure does not return a result set.
   * 
   * This test shows using a multi-property parameter.
   */
  @Test
  public void testAdderAsSelectDoubleCall1() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderAsSelect(parameter);
      assertEquals((Integer) 5, parameter.getSum());

      parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);
      spMapper.adderAsSelect(parameter);
      assertEquals((Integer) 5, parameter.getSum());

    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use input and output parameters in a stored
   * procedure. This procedure does not return a result set.
   * 
   * This test also demonstrates session level cache for output parameters.
   * 
   * This test shows using a multi-property parameter.
   */
  @Test
  public void testAdderAsSelectDoubleCall2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderAsSelect(parameter);
      assertEquals((Integer) 5, parameter.getSum());

      parameter = new Parameter();
      parameter.setAddend1(4);
      parameter.setAddend2(5);
      spMapper.adderAsSelect(parameter);
      assertEquals((Integer) 9, parameter.getSum());

    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to call a stored procedure defined as <update> rather
   * then <select>. Of course, this only works if you are not returning a result
   * set.
   * 
   * This test shows using a multi-property parameter.
   */
  @Test
  public void testAdderAsUpdate() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderAsUpdate(parameter);
      assertEquals((Integer) 5, parameter.getSum());

      parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);
      spMapper.adderAsUpdate(parameter);
      assertEquals((Integer) 5, parameter.getSum());

    } finally {
      sqlSession.close();
    }
  }

  // issue #145  
  @Test
  public void testEchoDate() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      HashMap<String, Object> parameter = new HashMap<String, Object>();
      Date now = new Date();
      parameter.put("input date", now);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);
      spMapper.echoDate(parameter);

      java.sql.Date outDate = new java.sql.Date(now.getTime());      
      assertEquals(outDate.toString(), parameter.get("output date").toString());
    } finally {
      sqlSession.close();
    }
  }
  
  /*
   * This test shows the use of a declared parameter map. We generally prefer
   * inline parameters, because the syntax is more intuitive (no pesky question
   * marks), but a parameter map will work.
   */
  @Test
  public void testAdderAsUpdateWithParameterMap() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("addend1", 3);
      parms.put("addend2", 4);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderWithParameterMap(parms);
      assertEquals(7, parms.get("sum"));

      parms = new HashMap<String, Object>();
      parms.put("addend1", 2);
      parms.put("addend2", 3);
      spMapper.adderWithParameterMap(parms);
      assertEquals(5, parms.get("sum"));

    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use an input parameter and return a result set from
   * a stored procedure.
   * 
   * This test shows using a single value parameter.
   */
  @Test
  public void testCallWithResultSet1() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Name name = spMapper.getName(1);
      assertNotNull(name);
      assertEquals("Wilma", name.getFirstName());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a single value parameter.
   */
  @Test
  public void testCallWithResultSet2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 1);
      List<Name> names = spMapper.getNames(parms);
      assertEquals(3, names.size());
      assertEquals(3, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a Map parameter.
   */
  @Test
  public void testCallWithResultSet3() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      List<Name> names = spMapper.getNames(parms);
      assertEquals(2, parms.get("totalRows"));
      assertEquals(2, names.size());

      parms = new HashMap<String, Object>();
      parms.put("lowestId", 3);
      names = spMapper.getNames(parms);
      assertEquals(1, names.size());
      assertEquals(1, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a Map parameter.
   */
  @Test
  public void testCallWithResultSet4() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      List<Name> names = spMapper.getNames(parms);
      assertEquals(2, parms.get("totalRows"));
      assertEquals(2, names.size());

      parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      names = spMapper.getNames(parms);
      assertEquals(2, names.size());
      assertEquals(2, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use the ARRAY JDBC type with MyBatis.
   * 
   * @throws SQLException
   */
  @Test
  public void testGetNamesWithArray() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Array array = sqlSession.getConnection().createArrayOf("int", new Integer[] { 1, 2, 5 });

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("ids", array);
      List<Name> names = spMapper.getNamesWithArray(parms);
      Object[] returnedIds = (Object[]) parms.get("returnedIds");
      assertEquals(4, returnedIds.length);
      assertEquals(3, parms.get("requestedRows"));
      assertEquals(2, names.size());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to call procedures that return multiple result sets
   * 
   * @throws SQLException
   */
  @Test
  public void testGetNamesAndItems() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      List<List<?>> results = spMapper.getNamesAndItems();
      assertEquals(2, results.size());
      assertEquals(4, results.get(0).size());
      assertEquals(3, results.get(1).size());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use input and output parameters in a stored
   * procedure. This procedure does not return a result set.
   * 
   * This test shows using a multi-property parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testAdderAsSelectAnnotated() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);
      spMapper.adderAsSelectAnnotated(parameter);

      assertEquals((Integer) 5, parameter.getSum());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use input and output parameters in a stored
   * procedure. This procedure does not return a result set.
   * 
   * This test shows using a multi-property parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testAdderAsSelectDoubleCallAnnotated1() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderAsSelectAnnotated(parameter);
      assertEquals((Integer) 5, parameter.getSum());

      parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);
      spMapper.adderAsSelectAnnotated(parameter);
      assertEquals((Integer) 5, parameter.getSum());

    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use input and output parameters in a stored
   * procedure. This procedure does not return a result set.
   * 
   * This test also demonstrates session level cache for output parameters.
   * 
   * This test shows using a multi-property parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testAdderAsSelectDoubleCallAnnotated2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderAsSelectAnnotated(parameter);
      assertEquals((Integer) 5, parameter.getSum());

      parameter = new Parameter();
      parameter.setAddend1(4);
      parameter.setAddend2(5);
      spMapper.adderAsSelectAnnotated(parameter);
      assertEquals((Integer) 9, parameter.getSum());

    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to call a stored procedure defined as <update> rather
   * then <select>. Of course, this only works if you are not returning a result
   * set.
   * 
   * This test shows using a multi-property parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testAdderAsUpdateAnnotated() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      Parameter parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);

      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      spMapper.adderAsUpdateAnnotated(parameter);
      assertEquals((Integer) 5, parameter.getSum());

      parameter = new Parameter();
      parameter.setAddend1(2);
      parameter.setAddend2(3);
      spMapper.adderAsUpdateAnnotated(parameter);
      assertEquals((Integer) 5, parameter.getSum());

    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use an input parameter and return a result set from
   * a stored procedure.
   * 
   * This test shows using a single value parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testCallWithResultSet1Annotated() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Name name = spMapper.getNameAnnotated(1);
      assertNotNull(name);
      assertEquals("Wilma", name.getFirstName());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use an input parameter and return a result set from
   * a stored procedure.
   * 
   * This test shows using a single value parameter.
   * 
   * This test shows using annotations for stored procedures and using a
   * resultMap in XML
   */
  @Test
  public void testCallWithResultSet1_a2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Name name = spMapper.getNameAnnotatedWithXMLResultMap(1);
      assertNotNull(name);
      assertEquals("Wilma", name.getFirstName());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a single value parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testCallWithResultSet2_a1() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 1);
      List<Name> names = spMapper.getNamesAnnotated(parms);
      assertEquals(3, names.size());
      assertEquals(3, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a single value parameter.
   * 
   * This test shows using annotations for stored procedures and using a
   * resultMap in XML
   */
  @Test
  public void testCallWithResultSet2_a2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 1);
      List<Name> names = spMapper.getNamesAnnotatedWithXMLResultMap(parms);
      assertEquals(3, names.size());
      assertEquals(3, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a Map parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testCallWithResultSet3_a1() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      List<Name> names = spMapper.getNamesAnnotated(parms);
      assertEquals(2, parms.get("totalRows"));
      assertEquals(2, names.size());

      parms = new HashMap<String, Object>();
      parms.put("lowestId", 3);
      names = spMapper.getNamesAnnotated(parms);
      assertEquals(1, names.size());
      assertEquals(1, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a Map parameter.
   * 
   * This test shows using annotations for stored procedures and using a
   * resultMap in XML
   */
  @Test
  public void testCallWithResultSet3_a2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      List<Name> names = spMapper.getNamesAnnotatedWithXMLResultMap(parms);
      assertEquals(2, parms.get("totalRows"));
      assertEquals(2, names.size());

      parms = new HashMap<String, Object>();
      parms.put("lowestId", 3);
      names = spMapper.getNamesAnnotatedWithXMLResultMap(parms);
      assertEquals(1, names.size());
      assertEquals(1, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a Map parameter.
   * 
   * This test shows using annotations for stored procedures
   */
  @Test
  public void testCallWithResultSet4_a1() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      List<Name> names = spMapper.getNamesAnnotated(parms);
      assertEquals(2, parms.get("totalRows"));
      assertEquals(2, names.size());

      parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      names = spMapper.getNamesAnnotated(parms);
      assertEquals(2, names.size());
      assertEquals(2, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use a input and output parameters and return a
   * result set from a stored procedure.
   * 
   * This test shows using a Map parameter.
   * 
   * This test shows using annotations for stored procedures and using a
   * resultMap in XML
   */
  @Test
  public void testCallWithResultSet4_a2() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      List<Name> names = spMapper.getNamesAnnotatedWithXMLResultMap(parms);
      assertEquals(2, parms.get("totalRows"));
      assertEquals(2, names.size());

      parms = new HashMap<String, Object>();
      parms.put("lowestId", 2);
      names = spMapper.getNamesAnnotatedWithXMLResultMap(parms);
      assertEquals(2, names.size());
      assertEquals(2, parms.get("totalRows"));
    } finally {
      sqlSession.close();
    }
  }

  /*
   * 
   * This test shows using a two named parameters.
   * 
   * This test shows using annotations for stored procedures and using a
   * resultMap in XML
   */
  @Test
  public void testCallLowHighWithResultSet() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);
      List<Name> names = spMapper.getNamesAnnotatedLowHighWithXMLResultMap(1, 1);
      assertEquals(1, names.size());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use the ARRAY JDBC type with MyBatis.
   * 
   * This test shows using annotations for stored procedures
   * 
   * @throws SQLException
   */
  @Test
  public void testGetNamesWithArray_a1() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Array array = sqlSession.getConnection().createArrayOf("int", new Integer[] { 1, 2, 5 });

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("ids", array);
      List<Name> names = spMapper.getNamesWithArrayAnnotated(parms);
      Object[] returnedIds = (Object[]) parms.get("returnedIds");
      assertEquals(4, returnedIds.length);
      assertEquals(3, parms.get("requestedRows"));
      assertEquals(2, names.size());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to use the ARRAY JDBC type with MyBatis.
   * 
   * This test shows using annotations for stored procedures and using a
   * resultMap in XML
   * 
   * @throws SQLException
   */
  @Test
  public void testGetNamesWithArray_a2() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      Array array = sqlSession.getConnection().createArrayOf("int", new Integer[] { 1, 2, 5 });

      Map<String, Object> parms = new HashMap<String, Object>();
      parms.put("ids", array);
      List<Name> names = spMapper.getNamesWithArrayAnnotatedWithXMLResultMap(parms);
      Object[] returnedIds = (Object[]) parms.get("returnedIds");
      assertEquals(4, returnedIds.length);
      assertEquals(3, parms.get("requestedRows"));
      assertEquals(2, names.size());
    } finally {
      sqlSession.close();
    }
  }

  /*
   * This test shows how to call procedures that return multiple result sets
   * 
   * This test shows using annotations for stored procedures and referring to
   * multiple resultMaps in XML
   * 
   * @throws SQLException
   */
  @Test
  public void testGetNamesAndItems_a2() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      List<List<?>> results = spMapper.getNamesAndItemsAnnotatedWithXMLResultMap();
      assertEquals(2, results.size());
      assertEquals(4, results.get(0).size());
      assertEquals(3, results.get(1).size());
    } finally {
      sqlSession.close();
    }
  }

  @Test
  public void testGetNamesAndItems_a3() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      List<List<?>> results = spMapper.getNamesAndItemsAnnotatedWithXMLResultMapArray();
      assertEquals(2, results.size());
      assertEquals(4, results.get(0).size());
      assertEquals(3, results.get(1).size());
    } finally {
      sqlSession.close();
    }
  }
  
  @Test
  public void testGetNamesAndItemsLinked() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      List<Name> names = spMapper.getNamesAndItemsLinked();
      assertEquals(4, names.size());
      assertEquals(2, names.get(0).getItems().size());
      assertEquals(1, names.get(1).getItems().size());
      assertNull(names.get(2).getItems());
      assertNull(names.get(3).getItems());
    } finally {
      sqlSession.close();
    }
  }

  @Test
  public void testGetNamesAndItemsLinkedWithNoMatchingInfo() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
      SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

      List<Name> names = spMapper.getNamesAndItemsLinkedById(0);
      assertEquals(1, names.size());
      assertEquals(2, names.get(0).getItems().size());
    } finally {
      sqlSession.close();
    }
  }

}