年末了，停下来歇歇，顺便把自己对mybatis的理解来说一说。
本次开发用的是spring mvc 3.0+mybatis, spring mvc就不多说了，接下来，重点说一下mybatis,本人也第一次使用mybatis,
mybatis其实就是对jdbc的封装，对程序员的编写sql能力的要求比较高。
下面来说说在实际应用中的mybatis
首先要构建一个SqlSessionFactory
1.在spring 配置文件中，先建一个SqlSessionFactory，然后配置dataSource.
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath*:mybatis-*.xml" />
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" abstract="false">
<property name="driverClassName">
<value>${database.driverClassName}</value>
</property>
<property name="url">
<value>${database.url}</value>
</property>
<property name="username">
<value>${database.username}</value>
</property>
<property name="password">
<value>${database.password}</value>
</property>
</bean>
2.dataInit.properties文件配置,当然还有好多的数据库的配置没加，这只是一个总结，有需要的童鞋，自己加一下就ok了。针对数据库配置这方面的配置，在后来的blog中会进行详细阐述，敬请期待。
database.driverClassName=oracle.jdbc.driver.OracleDriver
database.url=jdbc:oracle:thin:@10.21.2.89:1521:portal
database.dialect=org.hibernate.dialect.Oracle10gDialect
database.username=username
database.password=password
3.mybatis-xxx.xml文件配置
mybatis的映射文件，其实mybatis框架的核心就是对mybatis映射文件的操作，mybatis的文件配置就是sql的映射，所以要想特别熟练的应用mybatis，必须能熟练地操作sql。
mybatis其实是在jstl的基础上进行的改进，主要有9个标签
<select> 查询标签，
<insert>新增标签，
<update>修改标签，
<delete>删除标签，
<sql id=""></sql>标签（主要是用来写一些通用的sql语句的，在其他标签中可以使用<include refid=""></include>来引入标签）,
<parameterMap type="" id=""></parameterMap>参数集合，
<resultMap type="" id=""></resultMap>结果集，
<cache></cache>，
<cache-ref namespace=""/> 这两个标签都是缓存标签，
对查询结果集做缓存并不是MyBatis框架擅长的，它专心做的应该是sql mapper,所以在这我就不研究这两个标签了。
<mapper namespace="test" >
<sql id="table_name">
CONTAINER
</sql>
<sql id="table_Allcolumn">
container_id as containerId,
parent_id as parentId,
container_name as containerName,
total_well_count as totalWellCount,
empty_well_count as emptyWellCount,
sort as sort,
is_box as isBox,
xNumber as xNumber,
yNumber as yNumber,
remarks as remarks,
creat_time as createTime,
creator as creator,
last_modify_person as lastModifyPerson,
last_modify_time as lastModifyTime,
domain as domain
</sql>
<sql id="order_sql">
order by sort asc
</sql>
<sql id="select_Where">
<where>
1=1
</where>
</sql>
<!-- 查询集合 -->
<select id="queryList" resultType="com.ronglian.entity.Container">
SELECT
<include refid="table_Allcolumn" />
from
<include refid="table_name" />
<include refid="select_Where" />
<if test="parentId != null">
AND parent_id=#{parentId}
</if>
<if test="containerName != null">
AND container_name=#{containerName}
</if>
<if test="isBox != null">
AND is_box=#{isBox}
</if>
<if test="domain != null">
AND domain=#{domain}
</if>
<include refid="order_sql" />
</select>
<!-- 按Id查询 -->
<select id="queryById" resultType="com.ronglian.entity.Container">
SELECT
<include refid="table_Allcolumn" />
from
<include refid="table_name" />
where container_id = #{containerId}
</select>
<!-- 查询总数 -->
<select id="queryCount" resultType="Integer">
SELECT
COUNT(container_id)
from
<include refid="table_name" />
<where>
<if test="parentId != null">
parent_id = #{parentId}
</if>
<if test="domain != null">
AND domain=#{domain}
</if>
</where>
</select>
<!-- 新增(使用序列) -->
<insert id="save" parameterType="com.ronglian.entity.Container">
<selectKey resultType="Integer" keyProperty="containerId"
order="BEFORE">
select seq_container.nextVal
from dual
</selectKey>
insert into
<include refid="table_name" />
(
container_id,
parent_id,
container_name,
total_well_count,
empty_well_count,
sort,
is_box,
xNumber,
yNumber,
remarks,
creat_time,
creator,
last_modify_person,
last_modify_time,
domain
)
values
(
#{containerId,jdbcType=NUMERIC},
#{parentId,jdbcType=NUMERIC},
#{containerName,jdbcType=VARCHAR},
#{totalWellCount,jdbcType=NUMERIC},
#{emptyWellCount,jdbcType=NUMERIC},
#{sort,jdbcType=VARCHAR},
#{isBox,jdbcType=NUMERIC},
#{xNumber,jdbcType=NUMERIC},
#{yNumber,jdbcType=NUMERIC},
#{remarks,jdbcType=VARCHAR},
sysdate,
#{creator,jdbcType=VARCHAR},
#{lastModifyPerson,jdbcType=VARCHAR},
sysdate,
#{domain,jdbcType=VARCHAR}
)
</insert>
<!-- 按id删除 -->
<delete id="deleteById" parameterType="java.lang.Integer">
delete container where container_id in(
select container_id from container c start with c.container_id=#{containerId} connect by c.parent_id = prior c.container_id
)
</delete>
<!-- 更新 -->
<update id="update" parameterType="com.ronglian.entity.Container">
update
<include refid="table_name" />
<set>
container_name = #{containerName},
parent_id = #{parentId}
</set>
where container_id = #{containerId}
</update>
</mapper>
以上是对我们常用的操作的一些总结，主要就是查询，新增，修改，删除等操作。
以下，我写一些在项目上的经验之谈。我在项目中，主要做的是对easyui tree的操作，之前，由于是半路被拉进项目中，不知道oracle有操作tree的语句，再加上项目一直没定下来批量操作，所以，后来才加上的批量操作，希望对有需要的童鞋有帮助，也希望以此来警戒自己，在项目开始之前，一定要做好功课，确定好最佳方案，在遇到问题时寻求最优的解决方案解决问题。
下面是批量操作的映射文件代码
<!-- 批量插入 -->
<insert id="insertBatch" parameterType="java.util.List">
insert into
<include refid="table_name"/>
select seq_container.nextval, c.* from (
<foreach collection="list" item="item" index="index" separator="union all">
select
#{item.parentId,jdbcType=NUMERIC},
#{item.containerName,jdbcType=VARCHAR},
#{item.totalWellCount,jdbcType=NUMERIC},
#{item.emptyWellCount,jdbcType=NUMERIC},
#{item.sort,jdbcType=VARCHAR},
#{item.remarks,jdbcType=VARCHAR},
sysdate createTime,
#{item.creator,jdbcType=VARCHAR},
#{item.lastModifyPerson,jdbcType=VARCHAR},
sysdate lastModifyTime,
#{item.domain,jdbcType=VARCHAR},
#{item.isBox,jdbcType=NUMERIC},
#{item.xNumber,jdbcType=NUMERIC},
#{item.yNumber,jdbcType=NUMERIC}
from dual
</foreach>
) c
</insert>
<!-- 批量查询 -->
<select id="queryToList" resultType="com.ronglian.entity.Container">
SELECT
<include refid="table_Allcolumn" />
from
<include refid="table_name" />
<include refid="select_Where" />
and parent_id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item.parentId}
</foreach>
and container_name in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item.containerName}
</foreach>
<if test="domain != null">
and domain=#{domain}
</if>
<include refid="order_sql" />
</select>
<delete id="deleteBatch" parameterType="java.util.List">
delete from
<include refid="table_name" />
where container_id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item.containerId}
</foreach>
</delete>
<!-- 通过id查询子树 -->
<select id="queryTree" resultType="com.ronglian.entity.Container">
select
<include refid="table_Allcolumn"/>
from container c start with c.container_id=#{containerId} connect by c.parent_id = prior c.container_id
</select>
<!-- 通过id查询所有父节点 -->
<select id="queryFathers" resultType="com.ronglian.entity.Container">
select
<include refid="table_Allcolumn"/>
from container m start with m.container_id=#{containerId} connect by prior m.parent_id=m.container_id
</select>
<!-- 批量更新 -->
<update id="updateBatch" parameterType="com.ronglian.entity.Container">
update container set
<!-- 如果reduce=+1，则容量增加 -->
<if test="reduce == +1">
total_well_count = total_well_count + #{totalWellCount},
empty_well_count = empty_well_count + #{emptyWellCount},
last_modify_person = #{lastModifyPerson},
last_modify_time = sysdate
</if>
<!-- 如果reduce=-1,则容量减少 -->
<if test="reduce == -1">
total_well_count = total_well_count - #{totalWellCount},
empty_well_count = empty_well_count - #{emptyWellCount},
last_modify_person = #{lastModifyPerson},
last_modify_time = sysdate
</if>
where container_id in (
select m.container_id from container m start with m.container_id=#{containerId} connect by prior m.parent_id=m.container_id
)
</update>
<!-- 通过id查询所有父节点 -->
<select id="getBoxChildren" resultType="java.lang.Integer" parameterType="java.util.List">
select container_id from (select * from container c start with c.container_id in
<foreach collection="list" open="(" separator="," close=")" item="item">
#{item}
</foreach>
connect by c.parent_id = prior c.container_id) s where s.is_box=1
</select>
也提一点注意：在进行mybatis的操作时，最好先将自己的sql提出来，拿到数据库进行格式化和执行，这样的话，能 更快的提高效率，当然，如果sql的功力特深的话，更好了。
本人在数据库方面的了解还甚少，有童鞋有更优的方案，敬请提出，共同努力，如果以上有啥问题，希望大神提出，共同来进行探讨。
459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
