MyBatis使用

附送mybatis学习系列博客

逆向过程创建

mybatis官方提供逆向工程可以针对数据库的表生成java代码（mapper.java,mapper.xml、po..）

  1. 数据库的几个要素
  2. 表信息
  3. 生成文件的位置信息（包提前建好） 以上都写在配置文件里
    最后选中配置文件，右击，点击有个图标即可生成。

配置数据源

  1. 新建jdbc.properties文件，写数据库连接四大要素信息
  2. 在mybatis-config.xml或applicationContext.xml中加载jdbc.properties文件中的内容进行数据源定义。
<context:property-placeholder location="classpath:jdbc.properties" />
	<!-- 数据源 配置数据源 ，dbcp -->
	<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
		  p:driverClassName="${jdbc.driver}" p:url="${jdbc.url}"
		  p:username="${jdbc.username}" p:password="${jdbc.password}">

		<!-- 连接池启动时的初始值 -->
		<!--<property name="initialSize" value="5" />
		&lt;!&ndash;连接池的最大值&ndash;&gt;
        <property name="maxTotal" value="50"/>
		&lt;!&ndash;<property name="maxActive" value="50" />&ndash;&gt;
		&lt;!&ndash;最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止&ndash;&gt;
		<property name="maxIdle" value="10" />
		&lt;!&ndash;最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请&ndash;&gt;
		<property name="minIdle" value="5" />
        &lt;!&ndash;等待时间&ndash;&gt;
        <property name="maxWaitMillis" value="10000"/>-->
	</bean>

查询 sql

查出实体对象

  1. 先定义 resultMap，可以定义多个。
    list属性的映射使用 collection 标签。
 <resultMap id="ResultAllTasksMap" type="com.cashew.map.model.Alltasks">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="tname" property="tname" jdbcType="VARCHAR"/>
        <result column="tstartdate" property="tstartdate" jdbcType="TIMESTAMP"/>
        <result column="status" property="status" jdbcType="SMALLINT"/>
        <result column="gtxz" property="gtxz" jdbcType="DOUBLE" />
        <collection property="taskProcessList" ofType="com.cashew.map.model.TaskProcess">
            <id column="tpid" property="id" jdbcType="INTEGER"/>
            <result column="pdescn" property="pdescn" jdbcType="VARCHAR"/>
            <result column="pdate" property="pdate" jdbcType="TIMESTAMP"/>
            <result column="taskid" property="taskid" jdbcType="INTEGER"/>
            <result column="personid" property="personid" jdbcType="INTEGER"/>
            <result column="personname" property="personname" jdbcType="VARCHAR"/>
            <result column="tpstatus" property="status" jdbcType="SMALLINT"/>
            <result column="pstate" property="pstate" jdbcType="SMALLINT"/>
            <result column="ptstate" property="tstate" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
  1. 定义Base_Column_List 以备引用，主要是实体类对应表里的字段
    <sql id="Base_Column_List">
    id, tname, tstartdate, tenddate, psubid, status, tdealperson, tdealpersoname, finishtime
  </sql>
  1. 编写查询sql，参数使用 map 传递，也可以使用java.lang.Integer、java.lang.String等
<select id="selectByUserName" parameterType="java.util.Map" resultMap="ResultAllTasksMap">
    select
    <include refid="Base_Column_List" />
    from t_user_info
    <where>
      AND uname = #{uname,jdbcType=VARCHAR}
	  <if test="queryfield != null">
        AND belong_company LIKE concat('%', #{queryfield,jdbcType=VARCHAR}, '%') OR active_name LIKE concat('%', #{queryfield,jdbcType=VARCHAR}, '%')
      </if>
	  <if test="thisCarInTime != null">
        <![CDATA[ AND tstartdate < #{thisCarInTime} AND pdate > #{thisCarInTime} ]]>
      </if>
    </where>
</select>
  • 也可以从对象里读出参数
    List selectListByParam(Map<String, Object> map); 
     <select id="selectListByParam" resultMap="BaseResultMap" parameterType="java.util.Map">
    SELECT
    <include refid="Base_Column_List" />
    FROM t_announcement
    <where>
      <if test="obj != null">
        <if test="obj.title != null">
          AND title LIKE concat('%', #{obj.title}, '%')
        </if>
        <if test="obj.content != null">
          AND content LIKE concat('%', #{obj.content}, '%')
        </if>
      </if>
    </where>
    ORDER BY top DESC, createtime DESC
  </select>
    • 另外，关于时间类型的参赛，可直接写：
      startTime是Date类型
    	<select id="getHistoryPos" resultMap="BaseResultMap">
		select <include refid="Base_Column_List" />
		from roadschema.gis_apos
		<where>
			mposl!=0 and mposb!=0
			<if test="userId != null and userId !=''">
				and user_id = #{userId}
			</if>
			<if test="startTime != null and endTime !=null">
				and onlinetime between #{startTime} and #{endTime}
			</if>
			order by onlinetime
		</where>
	</select>
    • 带in的范围查询，不用自己写括号
    <select id="selectListByCondition" resultMap="BaseResultMap" parameterType="java.util.Map">
    SELECT
    <include refid="Base_Column_List" />
    FROM t_car_parking_record
    <where>
      <if test="feeRepeat != null">
        AND fee_repeat = #{feeRepeat}
      </if>
      <if test="carPlatenum != null">
        AND car_platenum LIKE concat('%', #{carPlatenum}, '%')
      </if>
      <if test="carPlatenumList != null and carPlatenumList.size > 0">
        AND car_platenum IN(
        <foreach collection="carPlatenumList" item="item" separator="," >
          #{item}
        </foreach>
        )
      </if>
    </where>
    <if test="isCarIn == 1">
      ORDER BY car_in_time ASC
    </if>
    <if test="isCarIn != 1">
      ORDER BY car_out_time DESC
    </if>
  </select>

      <select id="selectRoleAuthMenusByRolesId" resultMap="BaseResultMap" parameterType="java.util.HashMap" >
    select DISTINCT menu_id from shiro_role_auth where role_id in
    <foreach collection="rolesid" index="index" item="item" open="(" separator="," close=")">
          #{item}
    </foreach>
  </select>
    • 传入一个类型为String的参数：
      在 xml文件中应该使用_parameter来代替参数名，除非这么写：
    public Object getObjById(@Param("id)String id);
    • 数据库字段名与类属性名不一致，写个别名即可
     <result column="geom" property="geomDate" jdbcType="VARCHAR"/>
 ...
  <select id="selectByCondition" parameterType="com.cashew.parking.repository.system.entity.Parking"
            resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>, ST_AsText(geom) geom
        from t_parking
        <where>
            <if test="id != null and id != ''">
                and id = #{id,jdbcType=VARCHAR}
            </if>
        </where>
    </select>

    4.dao接口编写
    如果返回多个对象，用List接受

    List<Alltasks> selectByUserName(Map<String, Object> paramMap);

    5.使用

    paramMap.put('queryfield',stringParam);

    传入对象参数的使用，返回分页

        @Override
    public Object getAnnouncementList(PageInfo<Object> pageInfo, Announcement announcement) {
        Map<String, Object> paramMap = new HashMap<>(16);
        PageHelper.startPage(pageInfo.getPageNum(), pageInfo.getPageSize());
        paramMap.put("obj", announcement);
        List<Announcement> actives = announcementMapper.selectListByParam(paramMap);
        return new PageInfo<>(actives);
    }

    查出数量

      <select id="countCar" parameterType="java.util.Map" resultType="java.lang.Long">
    select COALESCE(count(car_platenum),0)
    from t_car_parking_record
    <where>
      <if test="record != null">
        and car_in_out = #{record}
      </if>
    </where>
    <choose>
      <when test="record != null and record == 0">
        order by car_out_time DESC
      </when>
      <otherwise>
        order by car_in_time DESC
      </otherwise>
    </choose>
    limit 30 offset 0
  </select>

    查询返回map

    List<Map> getCountGroupByDeptid();

      <select id="getCountGroupByDeptid" resultType="java.util.Map">
    select deptid,count(id)
    from p_user
    group by deptid
  </select>

    使用：

               List<Map> deptmap = userMapper.getCountGroupByDeptid();
            for (Dept d : deptList) {
                for (Map map : deptmap) {
                    if(d.getId().equals(map.get("deptid"))){
                        d.setUsernum(Integer.parseInt(map.get("count").toString()));
                    }
                }
            }

    mysql原生分页查询

    	<sql id="list_by_pagesql">
		LEFT JOIN shiro_role_info sr on sr.id=a.roles
		where 1=1
		<if test="tel != null and tel != ''" >
	    	and a.tel LIKE CONCAT(CONCAT('%',#{tel}),'%')
		</if>
		<if test="userName != null and userName != ''" >
	    	and a.username LIKE CONCAT(CONCAT('%',#{userName}),'%')
		</if>
        <if test="status != null">
            and a.status != #{status}
        </if>
    </sql>

    <select id="selectByCondition" resultMap="BaseResultMap" parameterType="java.util.Map">
        select a.id, a.username, a.password,a.tel,
		a.email, a.job, a.roles, a.registertime, a.updatetime, a.status, a.bgcode,
		a.bgcodename "bgcodeName", a.deptid,a.exprtime,a.token, a.type, a.companyid, 
		sr.role_name "roleName", pt.name "deptName", a.companyname, a.hycode, a.deptpid, a.headurl
		from shiro_user_info a
        <include refid="list_by_pagesql" />
        order by updatetime DESC limit #{pageSize} offset #{start}
    </select>
List<Map<String, Object>> selectByCondition(Map<String, Object> map);

    插入

    单个插入

    <insert id="insertSelective" parameterType="com.cashew.parking.repository.system.entity.UserInfo">
 insert into t_user_info
 <trim prefix="(" suffix=")" suffixOverrides=",">
   <if test="id != null">
     id,
   </if>
   <if test="uname != null">
     uname,
   </if>
   <if test="upwd != null">
     upwd,
   </if>
   <if test="usalt != null">
     usalt,
   </if>
   <if test="status != null">
     status,
   </if>
   <if test="createTime != null">
     create_time,
   </if>
   <if test="name != null">
     name,
   </if>
   <if test="gender != null">
     gender,
   </if>
   <if test="parkingId != null">
     parking_id,
   </if>
   <if test="stationId != null">
     station_id,
   </if>
   <if test="utel != null">
     utel,
   </if>
 </trim>
 <trim prefix="values (" suffix=")" suffixOverrides=",">
   <if test="id != null">
     #{id,jdbcType=VARCHAR},
   </if>
   <if test="uname != null">
     #{uname,jdbcType=VARCHAR},
   </if>
   <if test="upwd != null">
     #{upwd,jdbcType=VARCHAR},
   </if>
   <if test="usalt != null">
     #{usalt,jdbcType=VARCHAR},
   </if>
   <if test="status != null">
     #{status,jdbcType=SMALLINT},
   </if>
   <if test="createTime != null">
     #{createTime,jdbcType=TIMESTAMP},
   </if>
   <if test="name != null">
     #{name,jdbcType=VARCHAR},
   </if>
   <if test="gender != null">
     #{gender,jdbcType=INTEGER},
   </if>
   <if test="parkingId != null">
     #{parkingId,jdbcType=VARCHAR},
   </if>
   <if test="stationId != null">
     #{stationId,jdbcType=INTEGER},
   </if>
   <if test="utel != null">
     #{utel,jdbcType=VARCHAR},
   </if>
 </trim>
</insert>

    int insertSelective(UserInfo record);

    userMapper.insertSelective(userInfo);

    批量插入

         <insert id="insertList" parameterType="java.util.Map">
    INSERT INTO t_long_rent_car (car_platenum, rent_user_id, space_id, status) VALUES
    <foreach collection="carList" item="item" separator=",">
      (#{item.carPlatenum}, #{item.rentUserId}, #{item.spaceId}, #{item.status})
    </foreach>
  </insert>

    map定义：

     int insertList(Map<String, Object> paramMap);

    使用：

    List<LongRentCar> carList = new ArrayList<>(128);
 paramMap.put("carList", carList);

    更新

    <update id="updateByPrimaryKeySelective" parameterType="com.cashew.parking.repository.system.entity.UserInfo">
  update t_user_info
  <set>
    <if test="uname != null">
      uname = #{uname,jdbcType=VARCHAR},
    </if>
    <if test="upwd != null">
      upwd = #{upwd,jdbcType=VARCHAR},
    </if>
    <if test="usalt != null">
      usalt = #{usalt,jdbcType=VARCHAR},
    </if>
    <if test="status != null">
      status = #{status,jdbcType=SMALLINT},
    </if>
    <if test="createTime != null">
      create_time = #{createTime,jdbcType=TIMESTAMP},
    </if>
    <if test="name != null">
      name = #{name,jdbcType=VARCHAR},
    </if>
    <if test="gender != null">
      gender = #{gender,jdbcType=INTEGER},
    </if>
    <if test="parkingId != null">
      parking_id = #{parkingId,jdbcType=VARCHAR},
    </if>
    <if test="stationId != null">
      station_id = #{stationId,jdbcType=INTEGER},
    </if>
    <if test="utel != null">
      utel = #{utel,jdbcType=VARCHAR},
    </if>
  </set>
  where id = #{id,jdbcType=VARCHAR}
</update>

    map:

    int updateByPrimaryKeySelective(UserInfo record);

    使用：

        userInfo.setId(userInfoHave.getId());
    userInfo.setUsalt(newSalt);
    userInfo.setUpwd(newPwd);
    userMapper.updateByPrimaryKeySelective(userInfo);

    删除

      <delete id="deleteByRoleId" parameterType="java.util.Map" >
    delete from shiro_role
    <if test="roleid != null and roleid != ''">
      where role_id = #{roleid,jdbcType=VARCHAR}
    </if>
  </delete>

    int deleteByUserId(Map<String, Object> paramMap);

    分页列表查询

        @GetMapping("list")
    @ApiOperation(value = "停车场员工列表")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "pageNum", value = "当前页码", required = true),
            @ApiImplicitParam(name = "pageSize", value = "当前页数据条数", required = true),
            @ApiImplicitParam(name = "parkingId", value = "停车场id", required = true),
            @ApiImplicitParam(name = "uname", value = "登录名", required = true),
            @ApiImplicitParam(name = "upwd", value = "登录密码", required = true)
    })
    public RestResponse getUserListByPage(PageInfo<Object> pageInfo, UserInfo userInfo){
        return this.wrap(userService.getUserListByPage(pageInfo, userInfo));
    }

        @Override
    public Object getUserListByPage(PageInfo<Object> pageInfo, UserInfo userInfo) {
        Map<String, Object> paramMap = new HashMap<>(16);
        PageHelper.startPage(pageInfo.getPageNum(), pageInfo.getPageSize());
        paramMap.put("obj", userInfo);
        List<UserInfo> userInfos = userMapper.selectListByParam(paramMap);
        PageInfo<UserInfo> info = new PageInfo<>(userInfos);
        return info;
    }

    map:

    List<UserInfo> selectListByParam(Map<String, Object> paramMap);

    xml：

    <select id="selectListByParam" resultMap="BaseResultMap" parameterType="java.util.Map">
    SELECT
    <include refid="Base_Column_List" />
    FROM t_user_info
    <where>
      AND status != -1
      <if test="obj != null">
        <if test="obj.parkingId != null">
          AND parking_id = #{obj.parkingId}
        </if>
        <if test="obj.stationId != null">
          AND station_id = #{obj.stationId}
        </if>
        <if test="obj.name != null">
          AND name LIKE concat('%', #{obj.name}, '%')
        </if>
      </if>
    </where>
    ORDER BY create_time DESC
  </select>
posted @ 2018-03-12 20:39  cashew  阅读(287)  评论(0)    收藏  举报
​