myBatis sql配置高级用法

1.不仅可以在mapper中配置sql,也可以用注解的方式来配置，但不建议这么用，因为代码与注解混在一起，不易维护，如下用法:

@Select("select id,name from userinfo where id=#{id}")
    // 也可以用注解方式，但实际开发不建议用，应该将sql与代码分离，便于后期维护
    public User getuser(int id); // 方法名与mapper文件的sql id一样,参数就是要传给sql的参数,返回值就是sql的返回值，它们是一一对应的

    @Insert("insert into userinfo(id,name) values(#{id},#{name})")
    public void addUser(User user);// 添加用户

    @Select("select id,name from userinfo")
    public List<User> getUserList();

    @Update("update userinfo set name=#{name} where id=#{id}")
    public void modifyuser(User user);// 修改用户

2.mybatis中支持mysql ,sql server自动产生主键，但对于oracle如何产生主键呢，用随机函数，如下：

<insert id="addUser" parameterType="user"><!-- mysql and sqlserver can add  useGeneratedKeys="true" keyProperty="id" 来产么主键，oracle用selectKey-->
		<selectKey keyProperty="id" resultType="int" order="BEFORE">
			<!-- 用于oracle来产生随机主键 -->
			select cast(dbms_random.value*1000000 as integer) a from
			dual

		</selectKey>
		insert into userinfo(id,name) values(#{id},#{name})
	</insert>

3.使用sql片断来重用列名,如下用法:

<select id="getUserList" resultMap="getUserLists">
		select
		<include refid="useridNames" /><!-- 引用sql id -->
		from userinfo
	</select>

	<!-- 定义sql片断，用于重用 -->
	<sql id="useridNames">id,name</sql>

4.用resultMap来代替字段的重命名

<select id="selectUsers" resultType="User">
select
user_id as "id", //在特别复杂的关联表查询中，可以进行重命名来初始化对象
user_name as "userName",
hashed_password as "hashedPassword"
from some_table
where id = #{id}
</select>

替换为:

<resultMap id="userResultMap" type="User">
<id property="id" column="user_id" />
<result property="username" column="user_name"/>
<result property="password" column="hashed_password"/>
</resultMap>

4.通过构造器来封装查到的值到javaBean

<resultMap id="userResultMap" type="User">
<constructor>
<idArg column="id" javaType="int"/>
<arg column="username" javaType="String"/>
</constructor>
</resultMap>

5.通过关联查询给对象中的对象的属性赋值(A.B),将查到的封装到a对象中的b对象中

<select id="selectBlog" resultMap="blogResult">
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio
from Blog B left outer join Author A on B.author_id = A.id
where B.id = #{id}
</select>

<resultMap id="blogResult" type="Blog"> <span style="color:#FF0000;"> 1</span>
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author" resultMap="authorResult" />//引用下面封装的Author对象
</resultMap>

 <resultMap id="authorResult" type="Author"><span style="color:#FF0000;"> 2</span>
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</resultMap>

1,2替换为下面也是可以的

<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</association>
</resultMap>

6.如果再在上面的基础中添加一个新的author2，怎么办呢，列名仅仅加了一个c_前缀,怎么复用呢?

直接在1里面加

<association property="cAuthor"
resultMap="authorResult"
columnPrefix="c_" /> //引用的是同一个resultMap，就是最后加了c_,前提是查询的结果列中有与1中一样的列名，只不过前面加了c_,如:
<pre name="code" class="html">CA.id as c_author_id,
CA.username as c_author_username,
CA.password as c_author_password,
CA.email as c_author_email,
CA.bio as c_author_bio