mybatis使用总结：

批量修改：

public void insertManuyb() { PexContentBill bill=new PexContentBill(); bill.setBillId("c2643d40d80711e8847679c2a0768bfe"); bill.setCreator("李晓松"); PexContentBill bill2=new PexContentBill(); bill2.setBillId("73973460d80711e8847679c2a0768bfe"); bill2.setCreator("李孝王"); List<PexContentBill> list =new ArrayList<PexContentBill>(); list.add(bill); list.add(bill2); toDoDao.updateBatchAlla(list); } <update id="updateBatchAlla" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> update pex_bill <set> creator=#{item.creator} </set> where bill_id = #{item.billId} </foreach> </update>

注意#{item.creator}换成${item.creator}报错：Unknown column ‘c2643d40d80711e8847679c2a0768bfe’ in ‘where clause’

批量修改的第二种方式：

public void insertManuyc() { PexContentBill bill =new PexContentBill(); bill.setBillId("c2643d40d80711e8847679c2a0768bfe"); bill.setCreator("lxs"); bill.setCreatorName("李晓松"); PexContentBill bill2=new PexContentBill(); bill2.setBillId("73973460d80711e8847679c2a0768bfe"); bill2.setCreator("lxw"); bill2.setCreatorName("李孝王"); List<PexContentBill> list =new ArrayList<PexContentBill>(); list.add(bill); list.add(bill2); toDoDao.updateBatchAllb(list); } <update id="updateBatchAllb" parameterType="java.util.List"> update pex_bill <trim prefix="set" suffixOverrides=","> <trim prefix=" creator = case " suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.creator != null and item.creator != ''"> when bill_id = #{item.billId} then #{item.creator} </if> </foreach> </trim> <trim prefix=" creator_name = case " suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.creatorName != null and item.creatorName != ''"> when bill_id = #{item.billId} then #{item.creatorName} </if> </foreach> </trim> </trim> where bill_id in <foreach collection="list" open="(" separator="," close=")" item="item"> #{item.billId} </foreach> </update>

Mysql查询某字段值重复的数据

select bill_id,count(*) as count from pex_bill_relation group by bill_id having count>1; SELECT count(*) FROM pex_bill_relation GROUP BY bill_id

mysql返回map形式的数据：

List<Map<String, Object>> applyList = expenseTypeDao.listTop(rel); <select id="listTop" parameterType="com.pty.pex.entity.expense.BilltypeExpenseRelation" resultType="Map"> SELECT b.type_code billTypeCode, b.type_name billTypeName FROM pex_billtype_expense_relation rel LEFT JOIN pex_billtype b ON rel.bill_type_code = b.type_code LEFT JOIN pex_expense_type t ON rel.expense_type_code = t.expense_type_code WHERE rel.agy_code = #{agyCode} AND rel.fiscal = #{fiscal} AND rel.bill_func = #{billFunc} AND b.is_enabled = 1 GROUP BY b.type_code ORDER BY b.seq </select>