Summary of SQL notes commonly used in Mybatis project development
parameterType 和 resultType
parameterType：单个参数用String，多个参数用map
resultType：可以是 Integer、String、Object
SELECT COUNT(id) FROM t_mc_store_group_master WHERE u_id = #{uid} SELECT COUNT(1) FROM t_mc_store_group_master gm, t_mc_store_group g WHERE gm.id = g.master_id AND gm.u_id = #{uid} AND g.id = #{groupId} SELECT id FROM t_mc_store_group_master WHERE u_id = #{uid} SELECT gm.u_id mid, gm.u_name mName, 1 mTag, 1 pb_list, 1 pb_view, 1 pb_down, 1 pb_upload, 1 pb_delete, 1 pb_rename, 1 pb_share, 2 isAdmin, 1 isMaster FROM t_mc_store_group g, t_mc_store_group_master gm WHERE gm.id = g.master_id AND g.id = #{groupId}
对应的Java实现接口解析
我们可以基于org.mybatis.spring.SqlSessionTemplate提供的模板类来进行数据库操作的具体实现，例如以下是经过对模板的再封装进行实现的，封装类的过程做了日志记录操作等，在此具体不在细说：
@Override public int countGroupMasterByUid(String uid) { return getSqlSessionTemplate().selectOne("countGroupMasterByUid", uid); } @Override public int countGroupMaster(String uid, String groupId) { Map map = new HashMap(); map.put("uid", uid); map.put("groupId", groupId); return getSqlSessionTemplate().selectOne("countGroupMaster", map); } @Override public GetGroupMember queryMasterByGroupId(String groupId) { return getSqlSessionTemplate().selectOne("queryMasterByGroupId", groupId); } @Override public String queryMasterIdByUid(String uid) { return getSqlSessionTemplate().selectOne("queryMasterIdByUid", uid); } @Override public List queryMasterList(int index, int pageSize) { Map map = new HashMap(); map.put("index", (index-1)*pageSize); map.put("pageSize", pageSize); return getSqlSessionTemplate().selectList("queryMasterList",map); }
亦可以参考：http://lydia-fly.iteye.com/blog/2153102
动态SQL参考：http://www.cnblogs.com/ywqbj/p/5707652.html
selectKey的使用
注意：插入的时候id可以省略，但masterId必须对应的上AddMaster这个JavaBean的masterId属性。
表结构如下：
MySQL [mcloud]> desc t_mc_store_group_master;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| id | bigint(16) | NO | PRI | NULL | auto_increment |
| u_id | varchar(128) | NO | MUL | NULL | |
| u_name | varchar(128) | YES | | NULL | |
| dept_name | varchar(128) | YES | | NULL | |
| create_time | timestamp | YES | | CURRENT_TIMESTAMP | |
| update_time | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+--------------+------+-----+-------------------+-----------------------------+
INSERT INTO t_mc_store_group_master (id,u_id,u_name,dept_name) VALUES (#{masterId},#{uid},#{uName},#{deptName}) SELECT LAST_INSERT_ID() AS masterId
组合查询设置不存在的列
SELECT tu.id casualId, tu.name name, tu.expire_time validdate, tu.pb_upload, tu.pb_down, tu.pb_share, tu.pb_rename, tu.pb_view, tu.pb_delete, tu.account, IF(ul.id>0,1,0) status FROM t_mc_store_casual_user tu JOIN t_mc_store_manage_department md ON tu.dept_id = md.dept_id LEFT JOIN t_mc_store_user_lock ul ON tu.account = ul.account WHERE md.u_id = #{uid} AND tu.is_delete = 0 LIMIT #{index},#{pageSize}
原生SQL语句
#{beginTime} AND is_delete = 0 ]]>
