Mybatis基本增删改查操作及参数问题和返回值问题

Mybatis基本增删改查操作（以xml方式为例）

UserMapper接口

package com.xszx.dao;

import com.xszx.beans.Hobby;
import com.xszx.beans.Role;
import com.xszx.beans.User;
import org.apache.ibatis.annotations.Param;


import java.util.List;

public interface UserMapper {

    User getUserByUsernameAndPassword(User user);

    boolean getUserByUsername(String username);

    void adduser(User user);

    void adduser_role(@Param("uid") int uid,@Param("rid") int rid);

    List<User> getUsers(@Param("username") String username,@Param("gender") String gender,@Param("rid") int rid,@Param("currentPage") int currentPage,@Param("pageSize") int pageSize);

    int getUserSum(@Param("username") String username,@Param("gender") String gender,@Param("rid") int rid);

    List<Role> getRole();

    List<Hobby> gethobby();

    void deluser(int id);

    void deluser_role(int id);

    User getUserById(int id);

    void setUser(User user);
}

UserMapper.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">
<mapper namespace="com.xszx.dao.UserMapper">
    <resultMap id="userresult" type="com.xszx.beans.User">
        <id property="id" column="id"/>
        <result property="role.id" column="rid"/>
        <result property="role.name" column="rname"/>
    </resultMap>
    <select id="getUserByUsernameAndPassword" parameterType="com.xszx.beans.User" resultMap="userresult">
        select u.*,r.id rid,r.name rname  from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where u.username=#{username} and u.password=#{password}
    </select>
    <select id="getUserByUsername" parameterType="java.lang.String" resultType="java.lang.Boolean">
        select count(*) from user where username=#{username}
    </select>
    <insert id="adduser" parameterType="com.xszx.beans.User" useGeneratedKeys="true" keyProperty="id">
        insert into user(username,password,gender,age,hobby,address) values(#{username},#{password},#{gender},#{age},#{hobby},#{address})
    </insert>
    <insert id="adduser_role" parameterType="java.lang.Integer">
        insert into user_role(uid,rid) values(#{uid},#{rid})
    </insert>
    <select id="getUsers" parameterType="java.lang.Integer" resultMap="userresult">
        select u.*,r.id rid,r.name rname from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where 1=1
        <if test="username!=null and !username.equals('')">
            and u.username like concat('%',#{username},'%')
        </if>
        <if test="gender!=null and !gender.equals('')">
            and u.gender=#{gender}
        </if>
        <if test="rid!=0">
            and r.id=#{rid}
        </if>
        limit #{currentPage},#{pageSize}
    </select>
    <select id="getUserSum" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where 1=1
        <if test="username!=null and !username.equals('')">
            and u.username like concat('%',#{username},'%')
        </if>
        <if test="gender!=null and !gender.equals('')">
           and u.gender=#{gender}
        </if>
        <if test="rid!=0">
            and r.id=#{rid}
        </if>
    </select>
    <select id="getRole" resultType="com.xszx.beans.Role">
        select * from role
    </select>
    <select id="gethobby" resultType="com.xszx.beans.Hobby">
        select * from hobby
    </select>
    <delete id="deluser" parameterType="java.lang.Integer">
        delete from user where id=#{id}
    </delete>
    <delete id="deluser_role" parameterType="java.lang.Integer">
        delete from user_role where uid=#{id}
    </delete>
    <select id="getUserById" parameterType="java.lang.Integer" resultMap="userresult">
        select u.*,r.id rid,r.name rname from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where u.id=#{id}
    </select>
    <update id="setUser" parameterType="com.xszx.beans.User">
        update user set username=#{username},password=#{password},gender=#{gender},age=#{age},hobby=#{hobby},address=#{address} where id=#{id}
    </update>
</mapper>

参数问题

1. 如果方法中参数是自定义对象类型，那么#{}中的属性必须是实体类中的属性

<insert id="addUser" parameterType="com.xszx.beans.User">
    insert into user values (null,#{username},#{password})
</insert>

2. 如果方法中的参数是其他类型，那么#{}中的属性不一定是实体类中的属性

3. 如果方法中的参数是其他类型，并且有多个参数，

方式一：那么dao中的抽象方法中的形式参数前边需要标明注解@Param("参数名字")例如：

List getUserByUsernameandid(@Param("id") int id, @Param("username") String username);

<select id="getUserByUsernameandid" resultType="com.xszx.beans.User">
    select * from user where id = #{id} and username = #{username}
</select>

方式二：以param1、param2、....来替换(不推荐)

List getUserByUsernameandid( int id, String username);

<select id="getUserByUsernameandid" resultType="com.xszx.beans.User">
    select * from user where id = #{param1} and username = #{param2}
</select>

参数位置从 0 开始，引用参数语法 #{ arg 位置 } ，第一个参数是#{arg0}，第二个是#{arg1}

注：mybatis-3.3 版本和之前的版本使用#{0}，#{1}方式，从 mybatis3.4 开始使用#{arg0}方式

方式三：以map集合添加多个参数 #{}中填写map中的key值

HashMap map = new HashMap();
map.put("username","路飞");
map.put("age",23);
List<User> users = userDao.getUserByUsernameandAge(map);
<select id="getUserByUsernameandAge" resultType="com.xszx.beans.User" parameterType="map">
    select * from user where age = #{age} and username = #{username}
</select>

4. SQL注入  ${名称}（通常不用，容易产生sql注入）

<select id="getUserOrderby" resultType="com.xszx.beans.User" parameterType="String">
    select * from user order by ${str} desc
</select>

5. 关联对象传参

<select id="getUsersByGid01" resultType="com.xszx.beans.User" parameterType="com.xszx.beans.User"> 
 select * from user where gid = #{group.id}
 select * from user where gid = #{role.id}
</select>

返回值问题

1. 返回值为单个属性的时候

//接口中方法设计，返回值为String：
String getUser(int id);
//XML中语法：
<select id="getUser" parameterType="int" resultType="String">
    select username from user where id = #{id}
</select>
//测试类执行：
String username = userDao.getUser(2);
//返回int类型
int id  =  userMapper.getuserid(336);

<select id="getuserid"  resultType="int" parameterType="int">
    select id from user where id=#{id}
</select>

2. 返回值为多个属性的时候

方式一：采用对象形式

//接口中方法设计，返回值为User：
User getUser(int id);
//XML中语法：
<select id="getUser" parameterType="int" resultType="com.xszx.beans.User">
    select username,age from user where id = #{id}
</select>
//测试类执行：
User user = userDao.getUser(2);

方式二：采用map形式处理返回结果

//接口中方法设计，返回值为HashMap：
HashMap getUser02(int id);
//XML中语法：
<select id="getUser02" parameterType="int" resultType="map">
    select username,age from user where id = #{id}
</select>
//测试类执行：
HashMap map = userDao.getUser02(2);

返回map的时候，键是属性名称，值就是具体的值，只能返回一条数据，返回多条就会报错

resultType一般都是返回已有的类型，或者你写好的实体类型，可以直接对应的类型

若是字段或者类型，或者不是能很好的直接对应，就需要用自定义对应的类型