mybatis简单实例

MyBatis简介

MyBatis 是一个可以自定义SQL、存储过程和高级映射的持久层框架。MyBatis 摒除了大部分的JDBC代码、手工设置参数和结果集重获。MyBatis 只使用简单的XML 和注解来配置和映射基本数据类型、Map 接口和POJO 到数据库记录。相对Hibernate和Apache OJB等“一站式”ORM解决方案而言，Mybatis 是一种“半自动化”的ORM实现。
需要使用的Jar包：mybatis-3.0.2.jar（mybatis核心包）。mybatis-spring-1.0.0.jar(与Spring结合包)。

下载地址：
http://ibatis.apache.org/tools/ibator
http://code.google.com/p/mybatis/

mybatis主配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<!-- 全局参数 -->
	<settings>
		<!-- 使全局的映射器启用或禁用缓存。 -->
		<setting name="cacheEnabled" value="true"/>
		
		<!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。 -->
		<setting name="lazyLoadingEnabled" value="true"/>
		
		<!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。 -->
		<setting name="aggressiveLazyLoading" value="true"/>
		
		<!-- 是否允许单条sql 返回多个数据集  (取决于驱动的兼容性) default:true -->
		<setting name="multipleResultSetsEnabled" value="true"/>
		
		<!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true -->
		<setting name="useColumnLabel" value="true"/>
		
		<!-- 允许JDBC 生成主键。需要驱动器支持。如果设为了true，这个设置将强制使用被生成的主键，有一些驱动器不兼容不过仍然可以执行。  default:false  -->
		<setting name="useGeneratedKeys" value="false"/>
		
		<!-- 指定 MyBatis 如何自动映射 数据基表的列 NONE：不隐射　PARTIAL:部分  FULL:全部  -->  
		<setting name="autoMappingBehavior" value="PARTIAL"/>
		
		<!-- 这是默认的执行类型  （SIMPLE: 简单； REUSE: 执行器可能重复使用prepared statements语句；BATCH: 执行器可以重复执行语句和批量更新）  -->
		<setting name="defaultExecutorType" value="SIMPLE"/>
		
		<!-- 使用驼峰命名法转换字段。 -->
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		
		<!-- 设置本地缓存范围 session:就会有数据的共享  statement:语句范围 (这样就不会有数据的共享 ) defalut:session -->
        <setting name="localCacheScope" value="SESSION"/>
		
        <!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER，插入空值时不需要指定类型 -->
        <setting name="jdbcTypeForNull" value="NULL"/>
		
	</settings>
	
	<!-- 类型别名 -->
	<typeAliases>
		<typeAlias alias="Page" type="com.eryansky.common.orm.Page" /><!--分页  -->
	</typeAliases>
	
	<!-- 插件配置 -->
	<plugins>
		<plugin interceptor="com.eryansky.common.orm.mybatis.interceptor.PaginationInterceptor" />
    </plugins>
	
</configuration>

spring-mybatis配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"
       default-lazy-init="true">

    <description>Spring Mybatis Configuration</description>

    <!-- 加载配置属性文件 -->
    <context:property-placeholder ignore-unresolvable="true" location="classpath*:/appconfig.properties" />

    <!-- MyBatis begin -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="defaultDataSource"/>
        <property name="typeAliasesPackage" value="com.jfit.modules.sys.mapper,com.jfit.modules.cms.mapper,com.jfit.modules.notice.mapper,
        com.jfit.modules.mobile.mapper,com.jfit.modules.form.mapper"/>
        <property name="typeAliasesSuperType" value="com.eryansky.common.persistence.AbstractBaseEntity"/>
        <property name="mapperLocations" value="classpath:/mappings/**/*.xml"/>
        <property name="configLocation" value="classpath:/mybatis-config.xml"></property>
    </bean>
    <!-- 扫描basePackage下所有以@MyBatisRepository注解的接口-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        <property name="basePackage" value="com.jfit.modules.sys.dao,com.jfit.modules.cms.dao,com.jfit.modules.notice.dao,
        com.jfit.modules.mobile.dao,com.jfit.modules.form.dao"/>
        <property name="annotationClass" value="com.eryansky.common.orm.mybatis.MyBatisDao"/>
    </bean>
    <!-- MyBatis end -->

    <bean class="com.eryansky.common.persistence.MapperLoader"></bean>

</beans>

实体Bean

public class TableModel extends DataEntity<TableModel> {
    /**
     * 分类 数据字典 TABLE_MODEl_CATEGORY
     */
    private String category;
    /**
     * 名称
     */
    private String name;
    /**
     * 表名称
     */
    private String code;
    /**
     * 主键字段（@Transient）
     */
    private String primaryCode;
    /**
     * 类型 {@link TableModelType}
     */
    private String type = TableModelType.Single.getValue();

    /**
     * 关联类型 {@link com.jfit.modules.form._enum.TableJoinType}
     */
    private String joinType;
    /**
     * 父表模型ID
     */
    private String parentTableModelId;
    /**
     * 备注
     */
    private String remark;

    /**
     * 版本号
     */
    private Integer versionNo;
    /**
     * 是否同步数据库
     */
    private Integer isSync;    
    
    private List<TableModelColumn>  columnList;

	public TableModel() {}

    public TableModel(String id) {
        super(id);
    }


	public List<TableModelColumn> getColumnList() {
		return columnList;
	}

	public void setColumnList(List<TableModelColumn> columnList) {
		this.columnList = columnList;
	}
	
    public Integer getIsSync() {
		return isSync;
	}

	public void setIsSync(Integer isSync) {
		this.isSync = isSync;
	}

	public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return this.name;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getCode() {
        return this.code;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getType() {
        return this.type;
    }

    public String getTypeView() {
        TableModelType s = TableModelType.getTableModelTypeByValue(type);
        String str = "";
        if (s != null) {
            str = s.getDescription();
        }
        return str;
    }

    public String getJoinType() {
        return joinType;
    }

    public void setJoinType(String joinType) {
        this.joinType = joinType;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getRemark() {
        return this.remark;
    }

    public Integer getVersionNo() {
        return versionNo;
    }

    public void setVersionNo(Integer versionNo) {
        this.versionNo = versionNo;
    }

    public String  getParentTableModelId() {
        return parentTableModelId;
    }

    public void setParentTableModelId(String parentTableModelId) {
        this.parentTableModelId = parentTableModelId;
    }

    public String getPrimaryCode() {
        return primaryCode;
    }

    public void setPrimaryCode(String primaryCode) {
        this.primaryCode = primaryCode;
    }
}

有mybatis注解的DAO层

@MyBatisDao
public interface TableModelDao extends CrudDao<TableModel> {
	/**
	 * 删除所有 清空
	 *
	 * @return
	 */
	public int removeAll();

	/**
	 * 判断是否存在
	 *
	 * @return
	 */
	public TableModel findUnique(String code);

	/**
	 * 保存或更新
	 *
	 * @return
	 */
	public int saveOrUpdate(List<TableModelColumn> mColumnList);

	/**
	 *
	 * 根据父ID得到 Model. <br>
	 * 默认按 id asc排序.
	 *
	 * @param parentId 父节点ID(当该参数为null的时候查询顶级资源列表)
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<TableModel> getByParentId(String parentId);
	
    /**
     * 删除表
     */
    public void dropTable(String code);
	
    /**
     * 创建表
     */
    public void createMysqlTable(Map<String, Object> params);
    
    /**
     * 更新表
     */
    public void alterMysqlTable(Map<String, Object> params);
    
    /**
     * 设置同步状态
     */
    public void syncStatus(String id);
}

SQL映射语句文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jfit.modules.form.dao.TableModelDao">
/*  使用sql别名转换字段和属性名 */
	<sql id="formTableModelColumns">
		a.id AS "id",
		a.create_user AS "createUser",
		a.create_time AS "createTime",
		a.status AS "status",
		a.update_user AS "updateUser",
		a.update_time AS "updateTime",
		a.version AS "version",
		a.name AS "name",
		a.code AS "code",
		a.type AS "type",
		a.join_type AS "joinType",
		a.parent_table_model_id AS "parentTableModelId",
		a.remark AS "remark",
		a.category AS "category",
		a.version_no AS "versionNo",
		a.is_sync AS "isSync"
	</sql>
/* 或者用resultMap字段属性名转换
   <resultMap type="TableModel" id="tableModelMap">  
       <id property="id" column="id"/>  
       <result property="createUser" column="create_user"/>  
       <result property="createTime" column="create_time"/>  
       //....省略
       <result property="isSync" column="is_sync"/>  
    </resultMap>  
	在<select>标签加上resultMap="tableModelMap"也能进行字段名和属性名 转换
 */

	<select id="get" resultType="TableModel">
		SELECT
		<include refid="formTableModelColumns"/>
		FROM t_form_table_model a
		WHERE a.id = #{id}
	</select>

	<select id="findList" resultType="TableModel">
		SELECT
		<include refid="formTableModelColumns"/>
		FROM t_form_table_model a
		<where>
			a.status = #{STATUS_NORMAL}
		</where>
		<choose>
			<when test="entityPage !=null and entityPage.orderBy != null and entityPage.orderBy != ''">
				ORDER BY ${entityPage.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_time DESC
			</otherwise>
		</choose>
	</select>

	<select id="findByParentId" resultType="TableModel">
		SELECT
		<include refid="formTableModelColumns"/>
		FROM t_form_table_model a
		<where>
			a.status = #{STATUS_NORMAL}
			<choose>
				<when test="parentId !=null and parentId!= ''">
					AND
				</when>
				<otherwise>
					ORDER BY a.update_time DESC
				</otherwise>
			</choose>
		</where>
		<choose>
			<when test="entityPage !=null and entityPage.orderBy != null and entityPage.orderBy != ''">
				ORDER BY ${entityPage.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_time DESC
			</otherwise>
		</choose>
	</select>

	<select id="findUnique" resultType="TableModel">
		SELECT
		<include refid="formTableModelColumns"/>
		FROM t_form_table_model a
		WHERE a.code = #{code}
	</select>

	<insert id="insert">
		INSERT INTO t_form_table_model(
		id,
		status,
		version,
		create_user,
		create_time,
		update_user,
		update_time,
		name,
		code,
		type,
		join_type,
		parent_table_model_id,
		remark,
		category,
		version_no,
		is_sync
		) VALUES (
		#{id},
		#{status},
		#{version},
		#{createUser},
		#{createTime},
		#{updateUser},
		#{updateTime},
		#{name},
		#{code},
		#{type},
		#{joinType},
		#{parentTableModelId},
		#{remark},
		#{category},
		#{versionNo},
		#{isSync}
		)
	</insert>

	<update id="update">
		UPDATE t_form_table_model SET
		status = #{status},
		version = version +1,
		update_user = #{updateUser},
		update_time = #{updateTime},
		name = #{name},
		code = #{code},
		type = #{type},
		join_type = #{joinType},
		parent_table_model_id = #{parentTableModelId},
		remark = #{remark},
		category=#{category},
		version_no=#{versionNo},
		is_sync=#{isSync}
		WHERE id = #{id}
	</update>

	<delete id="delete">
		update  t_form_table_model
		set status='1'
		WHERE id = #{id}
	</delete>
	
	<update id="createMysqlTable">
	create table ${table} 
    <foreach  collection="list" index="index" item="column" open="(" separator="," close=")">
	${column.code}  ${column.dataType}(${column.columnSize}<if test="column.scale != null and column.dataType=='DOUBLE'">,${column.scale}</if>) 
	<if test="column.defaultValue!='' and (column.dataType == 'int' or column.dataType == 'double')">DEFAULT ${column.defaultValue} </if>
	<if test="column.defaultValue!='' and column.dataType == 'varchar'">DEFAULT '${column.defaultValue}' </if>
	<if test="column.isNull == 0">not null</if>
    <if test="column.comment!= null and column.comment !=''">comment '${column.comment}'</if> 
    <if test="column.isPrimary == 1">,primary key (${column.code})</if> 
    <if test="column.isUnique == 1">,unique key ${column.code}(${column.code})</if> 
    </foreach>
    comment '${comment}'
	</update>
	
	<update id="alterMysqlTable">
	${sql}
	</update>
    
    <update id="syncStatus">
		update  t_form_table_model
		set is_sync='1'
		WHERE id = #{id}
    </update>
</mapper>

下一篇讲解mybatis配置文件标签语法及其参数传递