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 fromdual</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">selectuser_id as "id", //在特别复杂的关联表查询中，可以进行重命名来初始化对象user_name as "userName",hashed_password as "hashedPassword"from some_tablewhere 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">selectB.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_biofrom Blog B left outer join Author A on B.author_id = A.idwhere 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