逆向过程创建
mybatis官方提供逆向工程可以针对数据库的表生成java代码（mapper.java,mapper.xml、po..）
- eclipse插件： https://pan.baidu.com/s/1i6jxEqp
配置文件generatorConfig.xml添加到java工程，并修改几个信息：
- 数据库的几个要素
- 表信息
- 生成文件的位置信息（包提前建好） 以上都写在配置文件里
最后选中配置文件，右击，点击有个图标即可生成。
-
idea，可引入一个开源工具jar包
不仅可逆向生成java代码，还集成了mybatisPlus等 -
发现，用idea连数据库后，也可以自动生成
配置数据源
- 新建jdbc.properties文件，写数据库连接四大要素信息
- 在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" />
<!–连接池的最大值–>
<property name="maxTotal" value="50"/>
<!–<property name="maxActive" value="50" />–>
<!–最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止–>
<property name="maxIdle" value="10" />
<!–最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请–>
<property name="minIdle" value="5" />
<!–等待时间–>
<property name="maxWaitMillis" value="10000"/>-->
</bean>
查询 sql
查出实体对象
- 先定义 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>
- 定义Base_Column_List 以备引用，主要是实体类对应表里的字段
<sql id="Base_Column_List">
id, tname, tstartdate, tenddate, psubid, status, tdealperson, tdealpersoname, finishtime
</sql>
- 编写查询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
<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>