Ibiats 的sql配置文件

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
   "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap>

 <select id="getAllUsers" resultClass="com.air.Account">
  SELECT * FROM USER_ACCOUNT order by USERID
 </select>

 <!-- 无映射  -->
 <select id="getAllUsers1" resultClass="com.air.Account"
  parameterClass="string">
  SELECT * FROM USER_ACCOUNT WHERE GROUPNAME=#groupName#
 </select>

 <!-- 内联映射->实体类 -->
 <select id="getAllUsers2" resultClass="com.air.Account"
  parameterClass="string">
  SELECT USERID as userid, USERNAME as username, PASSWORD as
  password, GROUPNAME as groupname FROM USER_ACCOUNT WHERE
  GROUPNAME=#groupName#
 </select>

 <!-- 内联映射->MAP类 -->
 <select id="getAllUsers3" resultClass="hashmap"
  parameterClass="string">
  SELECT * FROM USER_ACCOUNT WHERE GROUPNAME=#groupName#
 </select>

 <!-- 显示映射->实体类 -->
 <resultMap id="accoutResult" class="com.air.Account">
  <result property="userid" column="USERID" />
  <result property="username" column="USERNAME" />
  <result property="password" column="PASSWORD" />
  <result property="groupname" column="GROUPNAME" />
 </resultMap>
 <select id="getAllUsers4" resultMap="accoutResult"
  parameterClass="string">
  SELECT * FROM USER_ACCOUNT WHERE GROUPNAME=#groupName#
 </select>

 <!-- 显示映射->MAP类 -->
 <resultMap id="mapResult" class="hashmap">
  <result property="userid" column="USERID" />
  <result property="username" column="USERNAME" />
  <result property="password" column="PASSWORD" />
  <result property="groupname" column="GROUPNAME" />
 </resultMap>
 <select id="getAllUsers5" resultMap="mapResult"
  parameterClass="string">
  SELECT * FROM USER_ACCOUNT WHERE GROUPNAME=#groupName#
 </select>

 <!-- XML -->
 <select id="selectXML" parameterClass="string" resultClass="xml"
  xmlResultName="log">
  SELECT * FROM USER_ACCOUNT WHERE GROUPNAME=#groupName#
 </select>

 <select id="getOneUser" resultClass="com.air.Account"
  parameterClass="string">
  SELECT * FROM USER_ACCOUNT WHERE GROUPNAME=#groupName#
 </select>

 <!-- 自动参数映射 --> 
 <insert id="insertOneUser1" parameterClass="com.air.Account">
  INSERT INTO USER_ACCOUNT
  (
   USERNAME, PASSWORD, GROUPNAME
  )VALUES(
   #username#,#password#,#groupname#
  )
 </insert>

 <!-- 内联参数映射 --> 
 <insert id="insertOneUser2" parameterClass="com.air.Account">
  INSERT INTO USER_ACCOUNT
  (
   USERNAME, PASSWORD, GROUPNAME
  )VALUES(
   #username:VARCHAR#,
   #password:VARCHAR#,
   #groupname:VARCHAR#
  )
 </insert>

 <!-- 外联参数映射 --> 
 <parameterMap  id="parameterMapEx" class="com.air.Account">
  <parameter property="username" jdbcType="VARCHAR"/>
  <parameter property="password" jdbcType="VARCHAR"/>
  <parameter property="groupname" jdbcType="VARCHAR"/>
 </parameterMap>
 <insert id="insertOneUser3" parameterMap="parameterMapEx">
  INSERT INTO USER_ACCOUNT
  (
   USERNAME, PASSWORD, GROUPNAME
  )VALUES(
   ?,?,?
  )
 </insert>
 
 <!-- 自动生成的键 --> 
 <insert id="insertOneUser4" parameterClass="com.air.Account">
  INSERT INTO USER_ACCOUNT
  (
   USERID,USERNAME, PASSWORD, GROUPNAME
  )VALUES(
   #userid#,
   #username#,
   #password#,
   #groupname# 
  )
  <selectKey 
   keyProperty="userid"
   resultClass="int">
   SELECT LAST_INSERT_ID()   
  </selectKey>
 </insert> 

 <!-- 存储过程 -->  
 <parameterMap id="pro" class="java.util.Map">
  <parameter property="name" jdbcType="VARCHAR"
       javaType="string" mode="IN"/>
 </parameterMap>
 <procedure id="pro_insert" parameterMap="pro">
  {call new_proc(?)}
 </procedure>
 
</sqlMap>

 

 

类的使用：

mport com.ibatis.sqlmap.client.*;
import com.ibatis.common.resources.*;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.LogRecord;
import java.util.logging.XMLFormatter;

public class Demo1 {

 /**
  * @param args
  * @throws SQLException
  */
 @SuppressWarnings({ "unchecked", "unchecked" })
 public static void main(String[] args) throws SQLException {
  // TODO Auto-generated method stub
  String resource = "./SqlMapConfig.xml";
  Reader reader;
  try {
   reader = Resources.getResourceAsReader(resource);
   SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

   //List list = sqlMap.queryForList("selectXML", "NBA");

//   for (int i = 0; i < list.size(); i++) {
//    Account cc = (Account) list.get(i);
//    System.out.println(cc.getUserid() + " " + cc.getUsername()
//      + " " + cc.getPassword() + " " + cc.getGroupname());
//   }

//    for (int i=0;i<list.size();i++){
//    Map m=(Map)list.get(i);
//    System.out.print((String)m.get("userid").toString()+" ");
//    System.out.print((String)m.get("username")+" ");
//    System.out.print((String)m.get("password")+" ");
//    System.out.print((String)m.get("groupname")+" ");
//    System.out.println();
//    }

//    for (int i = 0; i < list.size(); i++) {
//    String s = (String) list.get(i);
//    System.out.println(s);
//    }

//    Account ac=(Account)sqlMap.queryForObject("getOneUser","IBM");
//    System.out.println(ac.getUserid());
   
   
   //selectkey
//   Account iac=new Account();
//   iac.setUsername("ALEXhhhh");
//   iac.setPassword("DALIAN");
//   iac.setGroupname("MS");
//   
//   Integer returnValue=(Integer)sqlMap.insert("insertOneUser4",iac);
//   
//   System.out.println(returnValue.intValue());
//   //显示插入后的结果
//   @SuppressWarnings("unchecked")
//   List list = sqlMap.queryForList("getAllUsers1","MS");
//   
//   for (int i = 0; i < list.size(); i++) {
//    Account cc = (Account) list.get(i);
//    System.out.println(cc.getUserid() + " " + cc.getUsername()
//     + " " + cc.getPassword() + " " + cc.getGroupname());
//   } 
   
   
   //调用存储过程
//   @SuppressWarnings("unchecked")
//   Map m=new HashMap(1);
//
//   m.put("name", new String("LISI"));
//   sqlMap.update("pro_insert",m);
//   //Integer i=(Integer)sqlMap.queryForObject("pro_insert",m);
//   
//   System.out.println("call procedure ok!");
   
   //批处理
//   Account c1=new Account();
//   c1.setUsername("ZHOUQI");
//   c1.setPassword("QINGDAO");
//   c1.setGroupname("CN");
//   
//   Account c2=new Account();
//   c2.setUsername("WANGWU");
//   c2.setPassword("NANJING");
//   c2.setGroupname("CN");
//   
//   sqlMap.startBatch();
//   
//   sqlMap.insert("insertOneUser3",c1);
//   sqlMap.insert("insertOneUser3",c2);
//   
//   sqlMap.executeBatch();
   
   //显示插入后的结果
//   @SuppressWarnings("unchecked")
//   List ls = sqlMap.queryForList("getAllUsers1","CN");
//   
//   for (int i = 0; i < ls.size(); i++) {
//    Account cc = (Account) ls.get(i);
//    System.out.println(cc.getUserid() + " " + cc.getUsername()
//     + " " + cc.getPassword() + " " + cc.getGroupname());
//   }    
   
   
   //RowHandle生产XML
//   XmlRowHandler rh=new XmlRowHandler();
//   sqlMap.queryWithRowHandler("getAllUsers1", "CN", rh);
//   String xmlDoc=rh.getXmlDocument();   
//  
//   System.out.println(xmlDoc);
   
   
   //RowHandler过滤数据
   FilterRowHandler fh=new FilterRowHandler();
   sqlMap.queryWithRowHandler("getAllUsers", null, fh);
   List<Account> ls=fh.getList();   
   
   for (int i=0;i<ls.size();i++){
    System.out.println(ls.get(i).getUserid()+":"+
      ls.get(i).getGroupname()); 
   }
     
   
   

  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

 }
}