Mybatis笔记(一)_工作中不推荐mybatisplus「建议收藏」

总配置文件sqlMapConfig.xml

<!DOCTYPE configuration

PUBLIC “-//mybatis.org//DTD Config 3.0//EN”

“http://mybatis.org/dtd/mybatis-3-config.dtd”>

<configuration>

<typeAliases>

<typeAlias type=”com.lrq.vo.User” alias=”User”/>

<typeAlias type=”com.lrq.vo.Order” alias=”Order”/>

<typeAlias type=”com.lrq.vo.Customer” alias=”Customer”/>

</typeAliases>

<environments default=”development”>

<environment id=”development”>

<transactionManager type=”JDBC” />

<dataSource type=”POOLED”>

<property name=”driver” value=”com.mysql.jdbc.Driver” />

<property name=”url” value=”jdbc:mysql://127.0.0.1:3306/mybatis” />

<property name=”username” value=”root” />

<property name=”password” value=”123456″ />

</dataSource>

</environment>

</environments>

<mappers>

<mapper resource=”sql/User.xml” />

<mapper resource=”sql/Order.xml” />

<mapper resource=”sql/Customer.xml” />

</mappers>

</configuration>

sql语句映射文件:User.xml

<?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=”com.lrq.vo.User”>

<!– 使用sql块而不是*,实际开发中不建议使用* –>

<sql id=”userField”>

uid,username,age,address

</sql>

<!– <include refid=”userField” />表示引用上面定义的sql块; –>

<select id=”selectUserById” parameterType=”int” resultType=”User”>

select

<include refid=”userField” />

from user where uid=#{uid}

</select>

<select id=”selectUserByIdForMap” parameterType=”int”

resultType=”map”>

select

<include refid=”userField” />

from user where uid=#{uid}

</select>

<select id=”selectAll” resultType=”User”>

select

<include refid=”userField” />

from user

</select>

<insert id=”insertUser” parameterType=”User”>

insert into

user(uid,username,address,age)

values(#{uid},#{username},#{address},#{age})

</insert>

<update id=”updateUserById” parameterType=”User”>

update user set

username=#{username},address=#{address},age=#{age}

where uid=#{uid}

</update>

<delete id=”deleteUserById” parameterType=”int”>

delete from user

where uid=#{uid}

</delete>

<!– 动态sql语句;成员变量的参数最好不设置成基本类型,最好换成包装类#号里面的是user对象的属性; :查询;

parameterType=”User”:参数类型来源于User表示参数就是一个User对象,因为定义了别名;

resultType=”User”返回值类型,也就是说resultType是从结果集中提取数据返回封装成对象的;

–>

<select id=”selectByCondition2″ parameterType=”User” resultType=”User”>

select uid,username,age,address from user where 1=1

<if test=”uid!=null”>

and uid=#{uid}

</if>

<if test=”username!=null”>

and username=#{username}

</if>

<if test=”address!=null”>

and address=#{address}

</if>

</select>

<select id=”selectByCondition” parameterType=”User” resultType=”User”>

select uid,username,age,address from user

<where>

<if test=”uid!=null”>

uid=#{uid}

</if>

<if test=”username!=null”>

and username=#{username}

</if>

<if test=”address!=null”>

and address=#{address}

</if>

</where>

</select>

<select id=”selectUserByFuzzy” parameterType=”User” resultType=”User”>

select <include refid=”userField”/> from user where username like ‘%${username}%’

</select>

<select id=”selectUserByFuzzy2″ parameterType=”User” resultType=”User”>

select <include refid=”userField”/> from user where username like ‘%${username}%’

</select>

<!– 动态sql更新

set:块,在拼接sql语句时会自动检测,号;并适时的增加或减少;

使用须知:第一个必须有,

最后一个不写,

请你按照规范或者说约定来走;

约定优于编码;COC:Convention Over Configuration

约定优于配置;

–>

<update id=”updateUserByCondition” parameterType=”User”>

update user

<set>

<if test=”username!=null”>

username=#{username},

</if>

<if test=”address!=null”>

address=#{address},

</if>

<if test=”age!=null”>

age=#{age}

</if>

</set>

where uid=#{uid}

</update>

</mapper>

sql映射文件:Order.xml

<?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=”com.lrq.vo.Order”>

<!– 这里的id是为resultMap这个项设置id –>

<resultMap type=”Order” id=”orderMap”>

<id property=”orderId” column=”oid” />

<result property=”price” column=”price” />

<result property=”orderNum” column=”orderNum” />

<association property=”customer” javaType=”Customer”>

<id column=”cid” property=”cid” />

<result property=”cname” column=”cname” />

<result property=”age” column=”age” />

<result property=”address” column=”address” />

</association>

</resultMap>

<select id=”selectOrderAndCustomerBy
Oid” resultMap=”orderMap” parameterType=”string”>

select o.*,c.*

from orders o,customer c

where
o.oid=c.cid and o.oid=#{oid}

</select>

</mapper>

Customer.xml文件;

<?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不可以使用别名
对应一个写在配置文件中的sql语句,必须是namespace+id

才能确定一条sql语句;

–>

<mapper namespace=”com.lrq.vo.Customer”>

<resultMap type=”Customer” id=”customerMap”>

<id column=”cid”
property=”cid”/>

<result property=”cname” column=”cname”/>

<result property=”age” column=”age”/>

<result property=”address” column=”address”/>

<!– property为类中的属性,column是表中的字段; –>

<collection property=”orders” ofType=”Order”>

<!–

private String orderId;

private Float price;

private String orderNum;

private Customer customer;

–>

<id property=”orderId” column=”oid”/>

<result property=”price” column=”price”/>

<result property=”orderNum” column=”orderNum”/>

</collection>

</resultMap>

<!– #cid –>

<select id=”selectCustomerForMap” resultMap=”customerMap” parameterType=”string”>

select c.*,o.* from customer c,orders o where c.cid=o.cid and c.cid=#{cid}

</select>

<insert id=”insertBatch” parameterType=”list”>

insert into customer values

<foreach collection=”list” item=”c” separator=”,”>

(#{c.cid},#{c.cname},#{c.age},#{c.address})

</foreach>

</insert>

</mapper>