数据库嵌套查询 - JDBC && iBatis

笔记：使用JDBC，iBatis分别嵌套查询数据（在程序中嵌套）

CREATE TABLE T1 ( ID1 INT, ID2 INT, PRIMARY KEY (ID1, ID2));
CREATE TABLE T2 ( ID2 INT, ID3 INT, PRIMARY KEY (ID2, ID3));
CREATE TABLE T3 ( ID3 INT, ID4 INT, PRIMARY KEY (ID3, ID4));

INSERT INTO T1(ID1, ID2) VALUES( 1, 11);
INSERT INTO T1(ID1, ID2) VALUES( 2, 21);

INSERT INTO T2(ID2, ID3) VALUES( 11, 111);
INSERT INTO T2(ID2, ID3) VALUES( 11, 112);
INSERT INTO T2(ID2, ID3) VALUES( 21, 211);
INSERT INTO T2(ID2, ID3) VALUES( 21, 212);

INSERT INTO T3(ID3, ID4) VALUES( 111, 1111);
INSERT INTO T3(ID3, ID4) VALUES( 111, 1112);
INSERT INTO T3(ID3, ID4) VALUES( 112, 1121);
INSERT INTO T3(ID3, ID4) VALUES( 112, 1122);
INSERT INTO T3(ID3, ID4) VALUES( 211, 2111);
INSERT INTO T3(ID3, ID4) VALUES( 211, 2112);
INSERT INTO T3(ID3, ID4) VALUES( 212, 2121);
INSERT INTO T3(ID3, ID4) VALUES( 212, 2122);

package me.test;

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

/**
 * 根据一个未结束的SQL查询，再次进行子查询。
 *
 * @author [e-mail]
 */
public class TestJdbc {

    public static void main(String args[]) throws ClassNotFoundException,
            SQLException {

        // 加载驱动类
        Class.forName("org.gjt.mm.mysql.Driver");

        // 创建数据库连接
        String url = "jdbc:mysql://localhost:3306/test";
        String userName = "root";
        String password = "123456";
        Connection conn1 = DriverManager.getConnection(url, userName, password);

        Statement stmt1 = conn1.createStatement();
        ResultSet rs1 = stmt1.executeQuery("SELECT ID1, ID2 FROM T1");
        rs1.setFetchSize(1);
        while (rs1.next()) {
            System.out.println("==========================================");
            System.out.println("ID1 = " + rs1.getObject(1));
            System.out.println(" ID2 = " + rs1.getObject(2));
            Statement stmt2 = conn1.createStatement();
            ResultSet rs2 = stmt2.executeQuery("SELECT ID2, ID3 "
                    + "FROM T2 WHERE ID2 = " + rs1.getObject(2));
            rs2.setFetchSize(1);
            while (rs2.next()) {
                System.out.println(" ID3 = " + rs2.getObject(2));
                Statement stmt3 = conn1.createStatement();
                ResultSet rs3 = stmt3.executeQuery("SELECT ID3, ID4 "
                        + "FROM T3 WHERE ID3 = " + rs2.getObject(2));
                rs3.setFetchSize(1);
                while (rs3.next()) {
                    System.out.println(" ID4 = " + rs3.getObject(2));
                }
                rs3.close();
                stmt3.close();
            }
            rs2.close();
            stmt2.close();
        }
        rs1.close();
        stmt1.close();
        conn1.close();
        System.out.println("\nsuccess");
    }
}


==========================================
ID1 = 1
    ID2 = 11
       ID3 = 111
           ID4 = 1111
           ID4 = 1112
       ID3 = 112
           ID4 = 1121
           ID4 = 1122
==========================================
ID1 = 2
    ID2 = 21
       ID3 = 211
           ID4 = 2111
           ID4 = 2112
       ID3 = 212
           ID4 = 2121
           ID4 = 2122

success


package me.test;

import java.io.InputStream;
import java.sql.SQLException;
import java.util.Map;

import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import com.ibatis.sqlmap.client.event.RowHandler;

/**
 * 根据一个未结束的SQL查询，再次进行子查询。
 *
 * @author [e-mail]
 */
public class TestIBatis {

    public static void main(String args[]) throws ClassNotFoundException,
            SQLException {

        InputStream sqlMapCfgIn = TestIBatis.class.getResourceAsStream("sqlMapConfig.xml");
        SqlMapClient dao = SqlMapClientBuilder.buildSqlMapClient(sqlMapCfgIn);
        T1RowHandler t1RowHandler = new T1RowHandler();
        t1RowHandler.dao = dao;
        dao.queryWithRowHandler("s1", t1RowHandler);
    }

    public static class T1RowHandler implements RowHandler {
        private SqlMapClient dao = null;

        public void setDao(SqlMapClient dao) {
            this.dao = dao;
        }
        @Override
        public void handleRow(Object rec) {
            Map recMap = (Map) rec;
            Integer id1 = (Integer) recMap.get("ID1");
            Integer id2 = (Integer) recMap.get("ID2");
            System.out.println("ID1 = " + id1);
            System.out.println(" ID2 = " + id2);
            T2RowHandler t2RowHandler = new T2RowHandler();
            t2RowHandler.dao = dao;
            try {
                dao.queryWithRowHandler("s2", id2, t2RowHandler);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static class T2RowHandler implements RowHandler {
        private SqlMapClient dao = null;
        public void setDao(SqlMapClient dao) {
            this.dao = dao;
        }
        @Override
        public void handleRow(Object rec) {
            Map recMap = (Map) rec;
            Integer id3 = (Integer) recMap.get("ID3");
            System.out.println(" ID3 = " + id3);
            T3RowHandler t3RowHandler = new T3RowHandler();
            t3RowHandler.dao = dao;
            try {
                dao.queryWithRowHandler("s3", id3, t3RowHandler);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static class T3RowHandler implements RowHandler {
        private SqlMapClient dao = null;
        public void setDao(SqlMapClient dao) {
            this.dao = dao;
        }
        @Override
        public void handleRow(Object rec) {
            Map recMap = (Map) rec;
            Integer id4 = (Integer) recMap.get("ID4");
            System.out.println(" ID4 = " + id4);
        }
    }
}

结果：

ID1 = 1
    ID2 = 11
       ID3 = 111
           ID4 = 1111
           ID4 = 1112
       ID3 = 112
           ID4 = 1121
           ID4 = 1122
ID1 = 2
    ID2 = 21
       ID3 = 211
           ID4 = 2111
           ID4 = 2112
       ID3 = 212
           ID4 = 2121
           ID4 = 2122


sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
            PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
            "">
<sqlMapConfig>
  <settings cacheModelsEnabled="true"
            enhancementEnabled="true"
            lazyLoadingEnabled="true"
            maxRequests="32"
            maxSessions="10"
            maxTransactions="5"
            useStatementNamespaces="false" />

  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="org.gjt.mm.mysql.Driver" />
      <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/test" />
      <property name="JDBC.Username" value="root" />
      <property name="JDBC.Password" value="123456" />
      <property name="JDBC.DefaultAutoCommit" value="true" />
      <property name="Pool.MaximumActiveConnections" value="10"/>
      <property name="Pool.MaximumIdleConnections" value="5"/>
      <property name="Pool.MaximumCheckoutTime" value="120000"/>
      <property name="Pool.TimeToWait" value="500"/>
      <property name="Pool.PingQuery" value="select 1 from ACCOUNT"/>
      <property name="Pool.PingEnabled" value="false"/>
      <property name="Pool.PingConnectionsOlderThan" value="1"/>
      <property name="Pool.PingConnectionsNotUsedFor" value="1"/>
    </dataSource>
  </transactionManager>
  <sqlMap resource="me/test/sqlMap.xml" />
</sqlMapConfig>


sqlMap.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
            PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
            "">
<sqlMap>

  <select id="s1"
          resultClass="java.util.HashMap">
    <![CDATA[
      SELECT ID1, ID2 FROM T1
    ]]>
  </select>

  <select id="s2"
          parameterClass="java.lang.Integer"
          resultClass="java.util.HashMap">
    <![CDATA[
      SELECT ID2, ID3 FROM T2 WHERE ID2 = #VALUE#
    ]]>
  </select>

  <select id="s3"
          parameterClass="java.lang.Integer"
          resultClass="java.util.HashMap">
    <![CDATA[
      SELECT ID3, ID4 FROM T3 WHERE ID3 = #VALUE#
    ]]>
  </select>
</sqlMap>