- 首先是mybatis-config.xml的配置(名字自己起,与加载的的文件名一致就行,一般就叫mybatis-config.xml)
<?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>
<properties resource="jdbc.properties"></properties>
<settings>
<!-- 开启驼峰匹配:经典的数据库列名(多个单词下划线连接)映射到经典的java属性名(多个单词驼峰连接) -->
<setting name="mapUnderscoreToCamelCase" value="true" />
<!-- 延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态 -->
<setting name="lazyLoadingEnabled" value="true" />
<!-- 当启用时,带有延迟加载属性的对象的加载与否完全取决于对任意延迟属性的调用;反之,每种属性将会按需加载 -->
<setting name="aggressiveLazyLoading" value="false" />
</settings>
<typeAliases>
<!-- 类型别名:type-pojo类的全路径,alias-别名名称(可随便写,推荐和类名一致) -->
<!-- <typeAlias type="cn.itcast.mybatis.pojo.User" alias="user" /> -->
<!-- 开启别名包扫描,name:包路径,扫描的别名就是类名,并且大小写不敏感 扫描的是实体类型 -->
<package name="cn.jaden.pojo" />
</typeAliases>
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 数据库方言 -->
<property name="dialect" value="mysql"/>
<!-- 设置为true时,使用RowBounds分页会进行count查询 会去查询出总数 -->
<property name="rowBoundsWithCount" value="true"/>
</plugin>
</plugins>
<!-- 环境:说明可以配置多个,default:指定生效的环境 -->
<environments default="development">
<!-- id:环境的唯一标识 -->
<environment id="development">
<!-- 事务管理器,type:类型 -->
<transactionManager type="JDBC" />
<!-- 数据源:type-池类型的数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClass}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 映射文件 -->
<mappers>
<mapper resource="commSQL.xml" />
<!-- <mapper resource="UserMapper.xml"/> -->
<!-- 扫描的是mapper 映射文件 -->
<package name="cn.jaden.mapper" />
</mappers>
</configuration>
2.第二步是XxxMapper.xml的配置(这个要和自己的XxxMapper接口要对应),根据自己的不同需求写不同的SQL既可。 这里提供两份demo 参考。
<?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="cn.jaden.mapper.OrderMapper">
<resultMap type="Order" id="orderDetailUserMap" autoMapping="true">
<id column="id" property="id" />
<association property="user" javaType="User" autoMapping="true">
<id column="user_id" property="id"/>
</association>
<collection property="detailList" javaType="list" ofType="Orderdetail" autoMapping="true">
<id column="detail_id" property="id"/>
<association property="item" javaType="Item" autoMapping="true">
<id column="item_id" property="id"/>
</association>
</collection>
</resultMap>
<select id="findUserByOrderId" resultType="User">
select * from tb_order o inner join tb_user u where o.user_id = u.id and o.order_number = #{number}
</select>
<select id="findOrderDetailByNumber" resultMap="orderDetailUserMap">
select * from tb_order o left join tb_user u on o.user_id = u.id left join tb_orderdetail d on o.id = d.order_id
where o.order_number = #{number}
</select>
<select id="findByOrderAndItemByNumber" resultMap="orderDetailUserMap">
select * from tb_order o
left join tb_user u on o.user_id = u.id
left join tb_orderdetail d on o.id = d.order_id
left join tb_item i on i.id = d.item_id
where order_number = #{number}
</select>
<resultMap type="Order" id="orderUserLazyMap" autoMapping="true">
<id column="id" property="id"/>
<association property="user" javaType="User" select="findUserByOrder" column="user_id" autoMapping="true"></association>
</resultMap>
<select id="findUserByOrder" resultMap="orderUserLazyMap">
select * from tb_order o
where o.order_number = #{number}
</select>
</mapper>
<?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="cn.jaden.mapper.UserMapper">
<resultMap type="User" id="userMap">
<id column="id" property="id"/>
</resultMap>
<select id="findByNamelike" resultMap="userMap">
select * from tb_user where user_name like '%' #{userName} '%'
</select>
<select id="findMaleAndName" resultMap="userMap" >
select <include refid="CommSQL.commSql"></include> from tb_user where sex = 1
<if test="userName != null || userName.trim()!=''">and user_name like '%' #{userName} '%'</if>
</select>
<select id="findMaleByNameOrAge" resultMap="userMap">
select <include refid="CommSQL.commSql"/> from tb_user where sex = 1
<choose>
<when test="userName != null and userName.trim()!=''">and user_name like '%' #{userName} '%'</when>
<when test="age != null">and age = #{age}</when>
<otherwise>and user_name = 'zhangsan'</otherwise>
</choose>
</select>
<select id="findMaleByNameAndAge" resultMap="userMap">
select <include refid="CommSQL.commSql"/> from tb_user
<where>
<if test="userName != null and userName.trim()!=''">and user_name like '%' #{userName} '%'</if>
<if test="age != null">and age = #{age}</if>
</where>
</select>
<select id="updateUser">
update tb_user
<set>
<if test="userName!=null and userName.trim()!=''">user_name = #{userName},</if>
<if test="password!=null and password.trim()!=''">password = #{password},</if>
<if test="name!=null and name.trim()!=''">name = #{name},</if>
<if test="age!=null">age = #{age},</if>
<if test="sex!=null">sex = #{sex}</if>
</set>
where id = #{id}
</select>
<select id="findUserByIds" resultMap="userMap">
select <include refid="CommSQL.commSql"/> from tb_user where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<select id="findUserById" resultType="User">
select * from tb_user where id = #{userId}
</select>
<select id="findAll" resultType="User">
select * from tb_user
</select>
</mapper>
- 将比较常用并且繁琐的SQL可以抽出到一个单独XML文件中,通过 include标签引入。
<?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">
<!-- namespace(命名空间):映射文件的唯一标识 -->
<mapper namespace="CommSQL">
<sql id="commSql">
id,
user_name,
password,
name,
age,
sex,
birthday,
created,
updated
</sql>
</mapper>
<!--在SQL中的使用 demo-->
select <include refid="CommSQL.commSql"/> from tb_user where sex = 1
4.这里是mybatis单独使用的配置值,mybatis与spring整合请看我相应的博客。