iBtaits最简单使用范例
1、准备包，搭建工程
jdbc驱动
ibatis2.3的包
2、建表
CREATE TABLE `cfg_code` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`code` varchar(24) NOT NULL COMMENT '代码',
`parent_code` varchar(24) DEFAULT NULL COMMENT '父代码',
`name` varchar(32) NOT NULL COMMENT '名称',
`type` varchar(24) NOT NULL COMMENT '类型',
`alias` varchar(24) DEFAULT NULL COMMENT '别名',
`ind_order` varchar(16) DEFAULT NULL COMMENT '排序号',
`valid` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效',
`remark` varchar(64) DEFAULT NULL COMMENT '备注',
`createtime` datetime NOT NULL COMMENT '创建时间',
`updatetime` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=gbk COMMENT='代码表'
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`code` varchar(24) NOT NULL COMMENT '代码',
`parent_code` varchar(24) DEFAULT NULL COMMENT '父代码',
`name` varchar(32) NOT NULL COMMENT '名称',
`type` varchar(24) NOT NULL COMMENT '类型',
`alias` varchar(24) DEFAULT NULL COMMENT '别名',
`ind_order` varchar(16) DEFAULT NULL COMMENT '排序号',
`valid` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效',
`remark` varchar(64) DEFAULT NULL COMMENT '备注',
`createtime` datetime NOT NULL COMMENT '创建时间',
`updatetime` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=gbk COMMENT='代码表'
3、写实体类
public class Cfg_code extends GenericEntity implements Serializable{
private String code; //代码
private String parent_code; //父代码
private String name; //名称
private String type; //类型
private String alias; //别名
private String ind_order; //排序号
private int valid; //是否有效
private String remark; //备注
private java.sql.Timestamp createtime; //创建时间
private java.sql.Timestamp updatetime; //更新时间
private String code; //代码
private String parent_code; //父代码
private String name; //名称
private String type; //类型
private String alias; //别名
private String ind_order; //排序号
private int valid; //是否有效
private String remark; //备注
private java.sql.Timestamp createtime; //创建时间
private java.sql.Timestamp updatetime; //更新时间
4、写SQLMap文件
<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<!-- cfg_code: 代码表-->
<sqlMap namespace="Cfg_code">
<typeAlias alias="Cfg_code" type="com.asiainfo.tdmc.entity.Cfg_code"/>
<resultMap id="result_base" class="Cfg_code">
<result property="id" column="id"/>
<result property="code" column="code"/>
<result property="parent_code" column="parent_code"/>
<result property="name" column="name"/>
<result property="type" column="type"/>
<result property="alias" column="alias"/>
<result property="ind_order" column="ind_order"/>
<result property="valid" column="valid"/>
<result property="remark" column="remark"/>
<result property="createtime" column="createtime"/>
<result property="updatetime" column="updatetime"/>
</resultMap>
<resultMap id="result" class="Cfg_code" extends="result_base">
<result property="childList" column="code" select="Cfg_code.queryChildren"/>
</resultMap>
<insert id="insert" parameterClass="Cfg_code">
insert into cfg_code(
code,
parent_code,
name,
type,
alias,
ind_order,
valid,
remark,
createtime,
updatetime
) values(
#code#,
#parent_code#,
#name#,
#type#,
#alias#,
#ind_order#,
#valid#,
#remark#,
now(),
now()
)
<selectKey keyProperty="id" resultClass="long">
select LAST_INSERT_ID()
</selectKey>
</insert>
<update id="update" parameterClass="Cfg_code">
update cfg_code set
code=#code#,
parent_code=#parent_code#,
name=#name#,
type=#type#,
alias=#alias#,
ind_order=#ind_order#,
valid=#valid#,
remark=#remark#,
updatetime=now()
where id = #id#
</update>
<delete id="delete" parameterClass="long">
delete from cfg_code where id=#value#
</delete>
<select id="load" parameterClass="long" resultClass="Cfg_code" resultMap="Cfg_code.result_base">
select * from cfg_code where id=#value#
</select>
<select id="queryChildren" parameterClass="string" resultClass="Cfg_code" resultMap="Cfg_code.result_base">
select * from cfg_code where parent_code=#value#
</select>
<sql id="sql_query_where">
<dynamic prepend="where">
<isNotEmpty prepend="and" property="code">
code=#code#
</isNotEmpty>
<isNotEmpty prepend="and" property="parent_code">
parent_code=#parent_code#
</isNotEmpty>
<isNotEmpty prepend="and" property="name">
name like '%$name$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="type">
type=#type#
</isNotEmpty>
<isNotEmpty prepend="and" property="alias">
alias like '%$alias$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="valid">
valid=#valid#
</isNotEmpty>
<isNotEmpty prepend="and" property="_startDate">
<isNotEmpty property="_endDate">
<![CDATA[
updatetime >= #_startDate# and updatetime <= #_endDate#
]]>
</isNotEmpty>
</isNotEmpty>
</dynamic>
</sql>
<select id="query" parameterClass="map" resultMap="Cfg_code.result">
select * from cfg_code
<include refid="sql_query_where"/>
<dynamic prepend="">
<isNotEmpty property="sortColumns">
order by #sortColumns#
</isNotEmpty>
<isEmpty property="sortColumns">
order by id desc
</isEmpty>
</dynamic>
</select>
<select id="count" parameterClass="map" resultClass="int">
select count(1) from cfg_code
<include refid="sql_query_where"/>
</select>
</sqlMap>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<!-- cfg_code: 代码表-->
<sqlMap namespace="Cfg_code">
<typeAlias alias="Cfg_code" type="com.asiainfo.tdmc.entity.Cfg_code"/>
<resultMap id="result_base" class="Cfg_code">
<result property="id" column="id"/>
<result property="code" column="code"/>
<result property="parent_code" column="parent_code"/>
<result property="name" column="name"/>
<result property="type" column="type"/>
<result property="alias" column="alias"/>
<result property="ind_order" column="ind_order"/>
<result property="valid" column="valid"/>
<result property="remark" column="remark"/>
<result property="createtime" column="createtime"/>
<result property="updatetime" column="updatetime"/>
</resultMap>
<resultMap id="result" class="Cfg_code" extends="result_base">
<result property="childList" column="code" select="Cfg_code.queryChildren"/>
</resultMap>
<insert id="insert" parameterClass="Cfg_code">
insert into cfg_code(
code,
parent_code,
name,
type,
alias,
ind_order,
valid,
remark,
createtime,
updatetime
) values(
#code#,
#parent_code#,
#name#,
#type#,
#alias#,
#ind_order#,
#valid#,
#remark#,
now(),
now()
)
<selectKey keyProperty="id" resultClass="long">
select LAST_INSERT_ID()
</selectKey>
</insert>
<update id="update" parameterClass="Cfg_code">
update cfg_code set
code=#code#,
parent_code=#parent_code#,
name=#name#,
type=#type#,
alias=#alias#,
ind_order=#ind_order#,
valid=#valid#,
remark=#remark#,
updatetime=now()
where id = #id#
</update>
<delete id="delete" parameterClass="long">
delete from cfg_code where id=#value#
</delete>
<select id="load" parameterClass="long" resultClass="Cfg_code" resultMap="Cfg_code.result_base">
select * from cfg_code where id=#value#
</select>
<select id="queryChildren" parameterClass="string" resultClass="Cfg_code" resultMap="Cfg_code.result_base">
select * from cfg_code where parent_code=#value#
</select>
<sql id="sql_query_where">
<dynamic prepend="where">
<isNotEmpty prepend="and" property="code">
code=#code#
</isNotEmpty>
<isNotEmpty prepend="and" property="parent_code">
parent_code=#parent_code#
</isNotEmpty>
<isNotEmpty prepend="and" property="name">
name like '%$name$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="type">
type=#type#
</isNotEmpty>
<isNotEmpty prepend="and" property="alias">
alias like '%$alias$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="valid">
valid=#valid#
</isNotEmpty>
<isNotEmpty prepend="and" property="_startDate">
<isNotEmpty property="_endDate">
<![CDATA[
updatetime >= #_startDate# and updatetime <= #_endDate#
]]>
</isNotEmpty>
</isNotEmpty>
</dynamic>
</sql>
<select id="query" parameterClass="map" resultMap="Cfg_code.result">
select * from cfg_code
<include refid="sql_query_where"/>
<dynamic prepend="">
<isNotEmpty property="sortColumns">
order by #sortColumns#
</isNotEmpty>
<isEmpty property="sortColumns">
order by id desc
</isEmpty>
</dynamic>
</select>
<select id="count" parameterClass="map" resultClass="int">
select count(1) from cfg_code
<include refid="sql_query_where"/>
</select>
</sqlMap>
5、配置iBatis
<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="true"
lazyLoadingEnabled="true" maxRequests="512" maxSessions="128"
maxTransactions="32" useStatementNamespaces="true"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL" value="jdbc:mysql://10.87.30.32:3306/tdmc_leizm"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value="123456"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/asiainfo/tdmc/entity/sqlmap/Cfg_code.xml"/>
</sqlMapConfig>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="true"
lazyLoadingEnabled="true" maxRequests="512" maxSessions="128"
maxTransactions="32" useStatementNamespaces="true"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL" value="jdbc:mysql://10.87.30.32:3306/tdmc_leizm"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value="123456"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/asiainfo/tdmc/entity/sqlmap/Cfg_code.xml"/>
</sqlMapConfig>
6、编写主程序
import com.asiainfo.tdmc.entity.Cfg_code;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
/**
* iBtaits最简单使用范例
*
* @author leizhimin 2012-03-17 15:22
*/
public class StartIBatis {
public static void main(String[] args) throws IOException, SQLException {
Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
List<Cfg_code> codeList = sqlMapClient.queryForList("Cfg_code.query", new HashMap());
for (Cfg_code code : codeList) {
System.out.println(code.getName());
}
}
}
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
/**
* iBtaits最简单使用范例
*
* @author leizhimin 2012-03-17 15:22
*/
public class StartIBatis {
public static void main(String[] args) throws IOException, SQLException {
Reader reader = Resources.getResourceAsReader("sqlMapConfig.xml");
SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
List<Cfg_code> codeList = sqlMapClient.queryForList("Cfg_code.query", new HashMap());
for (Cfg_code code : codeList) {
System.out.println(code.getName());
}
}
}
运行：
Created connection 21830977. 2012-03-17 15:38:53,687 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] -
{conn-100000} Connection 2012-03-17 15:38:53,687 DEBUG [java.sql.Connection] -
{conn-100000} Preparing Statement: select * from cfg_code order by id desc 2012-03-17 15:38:53,687 DEBUG [java.sql.Connection] -
{pstm-100001} Executing Statement: select * from cfg_code order by id desc 2012-03-17 15:38:53,718 DEBUG [java.sql.PreparedStatement] -
{pstm-100001} Parameters: [] 2012-03-17 15:38:53,718 DEBUG [java.sql.PreparedStatement] -
{pstm-100001} Types: [] 2012-03-17 15:38:53,718 DEBUG [java.sql.PreparedStatement] -
log4j:WARN No appenders could be found for logger (java.sql.ResultSet).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Returned connection 21830977 to pool. 2012-03-17 15:38:53,781 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] -
保留值类型(日)
保留值类型(月)
保留值类型(年)
保留值类型
操作对象
更新对象
删处对象
创建对象
脚本类型
SQL脚本
建表脚本
脚本关联类型
不限制
定时
立即
{conn-100000} Connection 2012-03-17 15:38:53,687 DEBUG [java.sql.Connection] -
{conn-100000} Preparing Statement: select * from cfg_code order by id desc 2012-03-17 15:38:53,687 DEBUG [java.sql.Connection] -
{pstm-100001} Executing Statement: select * from cfg_code order by id desc 2012-03-17 15:38:53,718 DEBUG [java.sql.PreparedStatement] -
{pstm-100001} Parameters: [] 2012-03-17 15:38:53,718 DEBUG [java.sql.PreparedStatement] -
{pstm-100001} Types: [] 2012-03-17 15:38:53,718 DEBUG [java.sql.PreparedStatement] -
log4j:WARN No appenders could be found for logger (java.sql.ResultSet).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Returned connection 21830977 to pool. 2012-03-17 15:38:53,781 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] -
保留值类型(日)
保留值类型(月)
保留值类型(年)
保留值类型
操作对象
更新对象
删处对象
创建对象
脚本类型
SQL脚本
建表脚本
脚本关联类型
不限制
定时
立即
