<?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();
}
}
}