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