Mybatis中的Mapper模板总结
总结:
1.多表查询如果查询字段跨表，可不写实体类，用List<Map<String,Object>>接收即可，然后遍历list，用map的get方法取值，将object类型的数据，再根据数据库类型字段一个个转换。或用查询的字段新写一个实体类用List<新实体类>接收即可。
2.多表查询如果查询字段只有一个表，用List<实体类>接收即可,不查询全部，只查询部分字段，也可List<实体类>接收。
3.注意resultMap为com.wanli.entity.User实体类与java.util.HashMap的区别
假设实体类属性为 id，age，name，数据库字段为u_id,u_age,u_name。
那么当resultMap为实体类时，那么接口返回的数据即拥有这些id，age，name属性的值。
当resultMap为java.util.HashMap时，那么接口返回的数据则与数据库字段对应，即u_id,u_age,u_name。
之所以说这些，是因为一旦和freemarker和bootstraptable联合使用，它们对字段空值，字段一致，以及大小写要求十分严格，稍微不慎就会出错。
resultMap为java.util.HashMap时，接口用List<实体类>或List接收都可。
4.占位符可以参与运算 比如
--dao接口正常传参即可 select * from student where age=#{maxAge}/18
Mapper：
<?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.wanli.dao.UserDao"> <!-- 可根据自己的需求，是否要使用 ，property是实体类属性值，column是数据库字段值，用resultMap可以简化配置，假如数据库中字段名修改了，如果我们用的resultType的话，可能就要去改实体类了，而reusltMap我们只需要修改对应column属性--> <resultMap type="com.wanli.entity.User" id="UserMap"> <result column="USERNO" property="UserId"/> <result column="ADDRESS" property="address"/> <result column="NUMBER" property="number"/> </resultMap> <!--增--> <insert id="insertUser" parameterType="com.wanli.entity.User"> insert into user(NAME,PASSWORD) VALUES(#{name},#{password}) </insert> <!--删--> <delete id="deleteUser" parameterType="java.lang.Integer"> delete from user where ID=#{id} </delete> <!--改--> <update id="updateUser" parameterType="com.wanli.entity.User"> update user set NAME=#{name},PASSWORD=#{password} WHERE ID=#{id} </update> <!--查--> <select id="selectAddressByPersonId" parameterType="java.lang.String" resultMap="UserMap"> select * from user <if test="type != null and type != '' "> where type = #{type} </if> </select> <!--dao接口用List接收即可--> <select id="getProjectMapList" resultType="java.util.HashMap"> select * from project </select> <!--批量根据id查询--> <select id="getBrandNameById" resultType="com.wanli.entity.BrandEntity"> select * from brand where brandid in <foreach collection="Ids" item="brandId" open="(" close=")" separator=","> #{brandId} </foreach> </select> </mapper>
dao接口：
@Mapper public interface ShopDao extends BaseMapper<ShopEntity> { /** * 根据商品主键查找商品 * * @param shopId * @return */ public ShopEntity getShopEntityById(Integer shopId);
/** * 根据品牌主键查找品牌信息 * * @param brandId * @return */ public BrandEntity getBrandEntityById(Integer brandId);
/** * 添加商品 * * @param shopEntity */ public void insertShop(ShopEntity shopEntity);
/** * 添加品牌 * * @param brandEntity */ public void insertBrand(BrandEntity brandEntity);
/** * 根据id删除商品 * * @param shopId */ public void deleteShop(Integer shopId);
/** * 修改商品 * * @param shopEntity */ public void updateShop(ShopEntity shopEntity);
/** * 修改商品品牌信息 * @param brandName * @param oldBrandId * @param newBrandId */ public void updateBrand(String brandName,Integer oldBrandId,Integer newBrandId);
/** * 分页查询 * @param page * @return */ IPage getPageShopList (Page page);
/** * 根据品牌主键查找品牌名称 * * @param Ids * @return */ public List<BrandEntity> getBrandNameById(@Param("Ids") List<Integer> Ids); }
serivice接口：
public interface ShopService { /** * 根据商品主键查找商品 * * @param shopId * @return */ public ShopEntity getShopEntityById(Integer shopId); /** * 根据品牌主键查找品牌信息 * * @param brandId * @return */ public BrandEntity getBrandEntityById(Integer brandId); /** * 添加商品 * * @param shopEntity */ public void insertShop(ShopEntity shopEntity); /** * 添加品牌 * * @param brandEntity */ public void insertBrand(BrandEntity brandEntity); /** * 根据id删除商品 * * @param shopId */ public void deleteShop(Integer shopId); /** * 修改商品 * * @param shopEntity */ public void updateShop(ShopEntity shopEntity); /** * 修改商品品牌信息 * * @param brandName * @param oldBrandId * @param newBrandId */ public void updateBrand(String brandName, Integer oldBrandId, Integer newBrandId); /** * 分页查询 * * @param page * @return */ IPage getPageShopList(Page page); /** * 批量查询商品品牌名称 * * @param brandIds * @return */ public List<BrandEntity> getBrandNameBatch(List<Integer> brandIds); }
serviceImpl实现类接口：
@Service("shopService")
public class ShopServiceImpl implements ShopService {
@Autowired
private ShopDao shopDao;
@Override
public ShopEntity getShopEntityById(Integer shopId) {
ShopEntity s = shopDao.getShopEntityById(shopId);
return s;
}
@Override
public BrandEntity getBrandEntityById(Integer brandId) {
BrandEntity brandEntity = shopDao.getBrandEntityById(brandId);
return brandEntity;
}
@Override
public void insertShop(ShopEntity shopEntity) {
shopDao.insertShop(shopEntity);
}
@Override
public void insertBrand(BrandEntity brandEntity) {
shopDao.insertBrand(brandEntity);
}
@Override
public void deleteShop(Integer shopId) {
shopDao.deleteShop(shopId);
}
@Override
public void updateShop(ShopEntity shopEntity) {
shopDao.updateShop(shopEntity);
}
@Override
public void updateBrand(String brandName, Integer oldBrandId, Integer newBrandId) {
shopDao.updateBrand(brandName, oldBrandId, newBrandId);
}
@Override
public IPage getPageShopList(Page page) {
return shopDao.getPageShopList(page);
}
@Override
public List<BrandEntity> getBrandNameBatch(List<Integer> brandIds) {
List<BrandEntity> brandEntityList = new ArrayList<>();
if (brandIds.size() > 0) {
brandEntityList = shopDao.getBrandNameById(brandIds);
return brandEntityList;
} else {
return null;
}
}
}
Controller：
@RestController public class ShopController { @Autowired private ShopService shopService; @RequestMapping("/getPageShopList") @ResponseBody public Page getPageShopList(int pageNo, int pageSize) { Page page = new Page<>(pageNo, pageSize); Page shopList = (Page) shopService.getPageShopList(page); List<ShopBrandEntity> list = shopList.getRecords(); List<Integer> brandIds = new ArrayList<>(); for (int k = 0; k < list.size(); k++) { Integer brandId = list.get(k).getBrandId(); brandIds.add(brandId); } // 批量查询品牌信息 List<BrandEntity> brandList = shopService.getBrandNameBatch(brandIds); if (null != brandList && brandList.size() > 0) { // 通过品牌id，给品牌名称赋值 for (int i = 0; i < brandList.size(); i++) { Integer brandId = brandList.get(i).getBrandId(); String brandName = brandList.get(i).getBrandName(); for (int j = 0; j < list.size(); j++) { if (list.get(j).getBrandId() == brandId) { list.get(j).setBrandName(brandName); } } } } shopList.setRecords(list); return shopList; } /** * 根据商品主键查找商品 * * @param shopId * @return */ @RequestMapping("/getShopEntityById") public ShopEntity getShopEntityById(Integer shopId) { ShopEntity shopEntity = shopService.getShopEntityById(shopId); return shopEntity; } /** * 添加新商品 * * @param shopEntity * @param brandName * @return */ @RequestMapping("/insertShopEntity") @ResponseBody public String insertShopEntity(ShopEntity shopEntity, String brandName) { try { shopService.insertShop(shopEntity); Integer brandId = shopEntity.getBrandId(); if (null != brandId) { BrandEntity b = shopService.getBrandEntityById(brandId); if (null == b) { BrandEntity brandEntity = new BrandEntity(brandId, brandName); shopService.insertBrand(brandEntity); } } return "success"; } catch (Exception e) { e.printStackTrace(); return "errror"; } } /** * 根据id删除商品 * * @param shopId * @return */ @RequestMapping("/deleteShop") @ResponseBody public String deleteShop(Integer shopId) { try { shopService.deleteShop(shopId); return "success"; } catch (Exception e) { e.printStackTrace(); return "errror"; } } /** * 根据id更新商品 * * @return */ @RequestMapping("/updateShop") @ResponseBody public String updateShop(ShopEntity shopEntity, String brandName) { Integer shopId = shopEntity.getShopId(); // 旧商品信息 ShopEntity oldShopEntity = shopService.getShopEntityById(shopId); // 旧商品品牌id Integer oldBrandId = oldShopEntity.getBrandId(); // 新商品品牌id Integer newBrandId = null; if (null != shopEntity.getBrandId()) { newBrandId = shopEntity.getBrandId(); } try { if (null != oldShopEntity) { shopService.updateShop(shopEntity); if (null != newBrandId) { shopService.updateBrand(brandName, oldBrandId, newBrandId); } return "success"; } else { return "no product"; } } catch (Exception e) { e.printStackTrace(); return "error"; } } }
