说来，mysql数据库是我们项目中用的比较多的库，ORM工具喜欢采用细粒度的MyBatis。这里面就这么引出了两者之间的故事！
首先，说改字段吧，将一个表中的varchar字段改为enum字段。如下：
mysql> desc ucc_purchase_status; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | status_type | varchar(64) | NO | | NULL | | | timestamp | datetime | NO | | NULL | | | purchase_id | int(11) | YES | MUL | NULL | | +-------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
这个表，主要是用来记录订单的状态。因为业务场景，这个订单的状态是有限的，目前只有"下单","付款","发货","收货","撤单","退货"。于是想将其修改成enum类型。
如下操作，得到错误了！！！
mysql> alter table ucc_purchase_status modify status_type enum("xd","fk","fh","sh","cd","th") not null default "xd"; ERROR 1265 (01000): Data truncated for column 'status_type' at row 1
这个错误，是什么意思呢？看到data truncated，应该想到什么呢？ 通常和数据记录的内容有关系！ 是不是因为我改类型后，默认值与表中当前的值有冲突呢？
带着这个疑问，看了下这个表中的内容：
mysql> select * from ucc_purchase_status; +----+-------------+---------------------+-------------+ | id | status_type | timestamp | purchase_id | +----+-------------+---------------------+-------------+ | 1 | 下单 | 2017-05-22 14:32:04 | 1 | | 2 | 发货 | 2017-05-23 17:32:44 | 2 | | 3 | 发货 | 2017-05-24 11:01:19 | 2 | | 4 | 收货 | 2017-05-22 14:18:23 | 3 | +----+-------------+---------------------+-------------+ 4 rows in set (0.00 sec)
的确，我这个表里面的status_type的值，的确和枚举的值，是不同的，真是这个原因造成的么？试试！
mysql> alter table ucc_purchase_status modify status_type enum("下单","付款","发货","收货","撤单","退货") not null default "下单"; Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0
呵呵，看来，这个是真的，这个分析是成立的！ 改后的表结构：
mysql> desc ucc_purchase_status; +-------------+-------------------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------------------------------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | status_type | enum('下单','付款','发货','收货','撤单','退货') | NO | | 下单 | | | timestamp | datetime | NO | | NULL | | | purchase_id | int(11) | YES | MUL | NULL | | +-------------+-------------------------------------------------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
这个数据类型的变化，对应的mapper文件也要修改！用mybatisGenerator工具生成mapper数据！
这个是字段改成enum之前的mapper文件部分:
<resultMap id="BaseResultMap" type="com.tg.ecs.ucc.model.UccPurchaseStatus" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="status_type" property="statusType" jdbcType="VARCHAR" /> <result column="timestamp" property="timestamp" jdbcType="TIMESTAMP" /> <result column="purchase_id" property="purchaseId" jdbcType="INTEGER" /> </resultMap>
下面这个，是status_type改成enum后的mapper文件部分：
<resultMap id="BaseResultMap" type="com.tg.ecs.ucc.model.UccPurchaseStatus"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="status_type" jdbcType="CHAR" property="statusType" /> <result column="timestamp" jdbcType="TIMESTAMP" property="timestamp" /> <result column="purchase_id" jdbcType="INTEGER" property="purchaseId" /> </resultMap>
好，到此，数据都改的差不多了，当然，dao里面的相关数据也都已经修正了。最后，就是mapper里面的sql查询，做相应的修改。之前对status_type的传值，都是hard code写死的，但是呢，这个业务场景，我希望查询数据，是基于状态的，比如付款的，未付款的，等等，逻辑都一样，就是状态传入的值不一样。于是，写一个通用的sql，通过参数进行过滤，是非常容易想到的方案！
这里，就涉及到mybatis增删改查中dao接口函数中传参数的问题了。
根据mybatis的官方文档，支持三种类型的传参模式：
1. 通过位置序号进行参数映射（序号从0开始，从函数参数列表中，自左向右依次递增，0,1,2,3）
2. 通过注解@Param("xxx")来进行参数名映射，其中的xxx字符串代表mapper中sql里面的传入参数。
3. 通过map对参数进行装载，通过KV的方式，K就是map的key，对应mapper文件里的sql中的传入参数的变量名。
对这三个方式，做一个简单的概括，也是mybatis的官方说法：任何参数传递，最终都将转化为map的形式，传入到mybatis的解析系统。默认是用key作为sql中的参数名，除非指定@Param进行重命名！
第一种模式，通过位置序号，比较不建议，因为在mapper的sql中不能顾名思义。
从这三个模式，你是不是觉得比较简单？mybatis里面也只是这么简单的描述！但是没有说这三个情况的组合情况！你有没有想过呢？至少我在我的应用场景，就上面的订单问题上，我有这个考虑！
下面请看我用的第2种模式：
dao的interface函数： List<PurchaseElement> findPElementByCustomerInfoAndStatus(@Param("uid") Integer id, @Param("start") Integer start, @Param("limit") Integer limit, @Param("list") List<String>stats);
这个其实比较简单，对应的mapper的sql如下（注意，红色部分名称的对应关系）：
<!-- 根据参数用户ID，订单状态两个参数，进行过滤 --> <select id="findPElementByCustomerInfoAndStatus" resultMap="purchaseElementResultMap"> select up.id as up_id, ups.status_type as ups_status_type, uua.id as uua_id, uua.username as uua_username, uua.address as uua_address, uua.mobile as uua_mobile, uua.zipcode as uua_zipcode, mp.name as mp_name, mp.price as mp_price, (select count(tupp.product_id) from ucc_purchase_product tupp where tupp.purchase_id = up.id and tupp.product_id = upp.product_id) as upp_quantity from ucc_purchase as up left join ucc_purchase_product as upp on upp.purchase_id = up.id left join mcc_product as mp on mp.id = upp.product_id left join ucc_purchase_status as ups on ups.purchase_id = upp.purchase_id left join ucc_user_address as uua on uua.id = up.address_id where ups.status_type is not null and ups.status_type != '' and (uua.id is not null and uua.id != '') and (uua.username is not null and uua.username != '') and (uua.address is not null and uua.address != '') and (uua.mobile is not null and uua.mobile != '') and (mp.name is not null and mp.name != '') and (mp.price is not null and mp.price !='') and up.customer_id = #{uid, jdbcType=INTEGER} and (ups.status_type in <foreach item="st" collection="list" index="idx" open="(" separator="," close=")"> #{st, jdbcType=CHAR} </foreach> ) order by up.timestamp desc <if test="start != null and limit!=null"> limit #{start}, #{limit} </if> </select>
对应的controller里面的业务逻辑代码如下：
@GET @Path("/load/paid") public String loadPaid(@Context HttpServletRequest req){ SysUser su = infos.getCurrentUser(); DataTablePager.generatePager(req, su); Integer id = su.getId().intValue(); Integer start = su.getStart(); Integer limit = su.getLimit(); List<PurchaseElement> list = pes.findPElementByCustomerInfoAndStatus(id, start, limit, pes.paidStatus()); int count = pes.findAllPElementCountByStatus(id, pes.paidStatus()); String sEcho = req.getParameter("sEcho"); return DataTablePager.getPageJson(list, count, sEcho); }
至于第三种，构建map这个就不用说了，将要传递的参数都写入一个map里面，用的时候通过map里面的key的名称取变量就可以了。
这里，我要说的是复杂类型，传递的参数，含有javaBean以及集合类型两个参数。当然，参数只有集合或者只有javaBean都比较简单，只有一个javaBean参数，mybatis会转化为map形式，但是对于既有javaBean，又有其他参数，例如我这里的List类型，会如何呢？
下面的经历，可以看出mybatis的强大！先看dao的接口：
List<PurchaseElement> findPElementByCustomerBeanAndStatus(@Param("su") SysUser su, @Param("list") List<String>stats);
再看看mapper文件：
<!-- 根据参数用户ID，订单状态两个参数，进行过滤 --> <select id="findPElementByCustomerBeanAndStatus" resultMap="purchaseElementResultMap"> select up.id as up_id, ups.status_type as ups_status_type, uua.id as uua_id, uua.username as uua_username, uua.address as uua_address, uua.mobile as uua_mobile, uua.zipcode as uua_zipcode, mp.name as mp_name, mp.price as mp_price, (select count(tupp.product_id) from ucc_purchase_product tupp where tupp.purchase_id = up.id and tupp.product_id = upp.product_id) as upp_quantity from ucc_purchase as up left join ucc_purchase_product as upp on upp.purchase_id = up.id left join mcc_product as mp on mp.id = upp.product_id left join ucc_purchase_status as ups on ups.purchase_id = upp.purchase_id left join ucc_user_address as uua on uua.id = up.address_id where ups.status_type is not null and ups.status_type != '' and (uua.id is not null and uua.id != '') and (uua.username is not null and uua.username != '') and (uua.address is not null and uua.address != '') and (uua.mobile is not null and uua.mobile != '') and (mp.name is not null and mp.name != '') and (mp.price is not null and mp.price !='') and up.customer_id = #{su.id, jdbcType=INTEGER} and (ups.status_type in <foreach item="st" collection="list" index="idx" open="(" separator="," close=")"> #{st, jdbcType=CHAR} </foreach> ) order by up.timestamp desc <if test="start != null and limit!=null"> limit #{su.start}, #{su.limit} </if> </select>
controller里面的代码如下：
@GET @Path("/load/paid") public String loadPaid(@Context HttpServletRequest req){ SysUser su = infos.getCurrentUser(); DataTablePager.generatePager(req, su); Integer id = su.getId().intValue(); List<PurchaseElement> list = pes.findPElementByCustomerBeanAndStatus(su, pes.paidStatus()); int count = pes.findAllPElementCountByStatus(id, pes.paidStatus()); String sEcho = req.getParameter("sEcho"); return DataTablePager.getPageJson(list, count, sEcho); }
可以看到，其中SysUser是一个用户数据的定义，以及后端分页信息的继承。
运行后，居然报错！！！！
五月 26, 2017 4:33:04 下午 org.apache.catalina.core.StandardWrapperValve invoke 严重: Servlet.service() for servlet [default] in context with path [/ecs] threw exception [org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'start' not found. Available parameters are [param1, param2, su, list]] with root cause org.apache.ibatis.binding.BindingException: Parameter 'start' not found. Available parameters are [param1, param2, su, list] at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:165) at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:123) at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:1657) at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:92)
仔细查询，涉及findPElementByCustomerBeanAndStatus函数调用逻辑的所有函数，以及mapper文件，最后，发现mapper文件中，的确有个start是独立的。就是那个动态sql if中判断分页数据的地方有问题。应该加上命名空间。
对比上面运行出错的mapper的内容，正确的应该是如下这个样子的：
<!-- 根据参数用户ID，订单状态两个参数，进行过滤 --> <select id="findPElementByCustomerBeanAndStatus" resultMap="purchaseElementResultMap"> select up.id as up_id, ups.status_type as ups_status_type, uua.id as uua_id, uua.username as uua_username, uua.address as uua_address, uua.mobile as uua_mobile, uua.zipcode as uua_zipcode, mp.name as mp_name, mp.price as mp_price, (select count(tupp.product_id) from ucc_purchase_product tupp where tupp.purchase_id = up.id and tupp.product_id = upp.product_id) as upp_quantity from ucc_purchase as up left join ucc_purchase_product as upp on upp.purchase_id = up.id left join mcc_product as mp on mp.id = upp.product_id left join ucc_purchase_status as ups on ups.purchase_id = upp.purchase_id left join ucc_user_address as uua on uua.id = up.address_id where ups.status_type is not null and ups.status_type != '' and (uua.id is not null and uua.id != '') and (uua.username is not null and uua.username != '') and (uua.address is not null and uua.address != '') and (uua.mobile is not null and uua.mobile != '') and (mp.name is not null and mp.name != '') and (mp.price is not null and mp.price !='') and up.customer_id = #{su.id, jdbcType=INTEGER} and (ups.status_type in <foreach item="st" collection="list" index="idx" open="(" separator="," close=")"> #{st, jdbcType=CHAR} </foreach> ) order by up.timestamp desc <if test="su.start != null and su.limit!=null"> limit #{su.start}, #{su.limit} </if> </select>
到此，有必要补充一下，上述resultMap的内容：
<mapper namespace="com.tg.ecs.ucc.dao.PurchaseElementMapper" > <resultMap id="purchaseElementResultMap" type="com.tg.ecs.ucc.model.PurchaseElement" > <id column="up_id" property="purchaseId" jdbcType="INTEGER"/> <result column="ups_status_type" property="statusType" jdbcType="VARCHAR"/> <!-- 特别注意：association以及collection这样子的标签，必须放在resultMap的最后面，且association在collection的前面 --> <association property="receivedBy" column="up_id" javaType="com.tg.ecs.ucc.model.ConsigneeElement"> <id column="uua_id" property="aid" /> <result column="uua_username" property="name" /> <result column="uua_address" property="address" /> <result column="uua_mobile" property="mobile" /> <result column="uua_zipcode" property="zipcode"/> </association> <!--一个订单号对应多个产品 --> <collection property="products" column="up_id" ofType="com.tg.ecs.ucc.model.PurchaseProduct"> <result column="mp_name" property="productName"/> <result column="mp_price" property="productPrice"/> <result column="upp_quantity" property="quantity"/> </collection> </resultMap>
注意，其中的红色部分，若不按照其规则配置，将会出现下面的错误：
The content of element type "resultMap" must match "(constructor?,id*,result*,association*,collection*,discriminator?)".
总结一下：
基于mybatis的参数传递功能，mybatis是支持任何参数的传递的，不仅是基础类型（String，Integer等）的参数传递，也支持javaBean类型的传递，同时，也支持各种类型的组合传递。只是使用的过程中，dao接口函数中参数基于@Param注解进行重命名，方便和mapper中sql语句的变量映射。最重要的一点，要注意：若参数中是javaBean的话，且有多个入口参数，就要注意变量的命名空间问题，我上面遇到的错误，找不到start变量（其实，start是SysUser里面的一个成员变量名），就是这个原因造成的！
另外，插曲一段：
在做订单状态传递进入sql的过程中，in 后面的foreach的使用，遇到了一点问题。故事是这样的，开始我的sql是这样的：
<foreach item="st", collection="list", index="idx", open="(", separator=",", close=")"> #{st, jdbcType=CHAR} </foreach>
这个时候，我的mapper文件中，总是报错：
Element type "foreach" must be followed by either attribute specifications, ">" or "/>".
仔细核对mybatis的技术手册，才发现，粗心大意造成了这个错误，因为属性字段之间不应该有逗号！是用“空格”，切记！
正确的写法应该是这样的：
<foreach item="st" collection="list" index="idx" open="(" separator="," close=")"> #{st, jdbcType=CHAR} </foreach>
好了，今天，这个博文，就写到这里吧！ 下一篇，将总结一下foreach中的collection以及index的使用，因为我看到好多人在纠结这两个字段的用法！
