随着业务的发展，越来越多的应用系统都从一个大的系统分拆成多个小的系统，各个系统之间通过一定的通信协议进行数据交换。这样就会导致一些小的应用系统自己不用去进行数据库的操作，只需要进行一些rpc调用或者缓存就可以拿到数据进行展示。我之前参与的一个项目就是这样的情况，而我也是将近7个多月的时间没有写过一行SQL。
近期参与的一个项目的数据大多都市基于数据库来进行数据交互的，所以免不了的要写大量的 SQL，所以本篇就总结一下一些 SQL 的基本写法，以备后用。
建表 1 2 3 4 5 6 CREATE TABLE IF NOT EXISTS `user_test` ( `id` int (11 ) NOT NULL AUTO_INCREMENT COMMENT '自增长id' , `user_name` varchar (128 ) NOT NULL COMMENT '用户名' , PRIMARY KEY (`id`) ) ENGINE= InnoDB DEFAULT CHARSET= utf8mb4 COMMENT= '用户表' ;
查询
1 2 3 <select id ="queryUserByName" resultMap ="userMap" parameterType ="java.lang.String" > SELECT * FROM user_test WHERE user_name = #{userName} </select >
需要注意的是如果这里不指定parameterType，则默认会识别处理；如果指定了类型，则传入的值就需要和当前指定的类型保持一致，不然就会出现数据类型转换异常。
1 2 3 4 5 6 7 <select id ="queryUsersList" resultMap ="userMap" > SELECT * FROM user_test WHERE 1=1 <if test ="keyword != null and keyword != ''" > AND user_name LIKE concat('%',#{keyword},'%') </if > LIMIT #{currentPage},#{pageSize} </select >
app_info表和app_verion表分别存储的是应用信息和应用版本信息。现在要根据appId和versionId查出一个应用的具体信息【包括信息信息和版本信息】
1 2 3 4 5 6 7 8 <select id ="getAppDetail" resultMap ="appDeatilMap" > select m.id id, m.app_name appName, n.version version, from app_info m LEFT JOIN app_version n ON m.id = n.app_id where m.id = #{appId} and n.id = #{versionId} </select >
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <select id ="queryAppByAppNames" resultMap ="AppMap" parameterType ="java.util.List" > select a.app_name appName, b.version version from starter_info a,starter_version b where a.id = b.app_id and a.id in ( select id from app_info where app_name in <foreach collection ="list" item ="item" index ="index" open ="(" close =")" separator ="," > #{item} </foreach > ) </select >
更新
1 2 3 4 5 6 7 <update id ="updateApp" parameterType ="java.util.List" > UPDATE app_info SET app_name = #{appName} WHERE app_id = #{appId} </update >
有这样一个需求，把 app_info表中id 为1，2，3的app的app_name改为appName1，appName2，appName3;
使用 case ..when ..then 这样的语法结构来完成：
case 是当前的条件，when表示条件值，then后面是当前目前更新字段的值；
下面的说明：当前id=#{item.appId}时,app_name=#{item.appName}
1 2 3 4 5 6 7 8 9 10 11 <update id ="updateApps" parameterType ="java.util.List" > UPDATE app_info set app_name = <foreach collection ="applList" item ="item" index ="index" separator =" " open ="case ID" close ="end" > when #{item.appId,jdbcType=INTEGER} then #{item.appName,jdbcType=INTEGER} </foreach > where id in <foreach collection ="appList" index ="index" item ="item" separator ="," open ="(" close =")" > #{item.appId,jdbcType=INTEGER} </foreach > </update >
OK，现在于这样的需要：
根据应用类型的不同，更新不同的运行环境配置；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 { [ { "appType" : "applet" , "cpu" : 5 , "memory" : 4 , "card" : 3 , "nums" : 2 , "network" : 1 , "isInUse" : 1 } , { "appType" : "bs" , "cpu" : 5 , "memory" : 4 , "card" : 3 , "nums" : 2 , "network" : 1 , "isInUse" : 1 } , { "appType" : "cs" , "cpu" : 5 , "memory" : 4 , "card" : 3 , "nums" : 2 , "network" : 1 , "isInUse" : 1 } , ] }
trim 属性说明
1.prefix,suffix 表示在trim标签包裹的部分的前面或者后面添加内容
2.如果同时有prefixOverrides,suffixOverrides 表示会用prefix,suffix覆盖Overrides中的内容。
3.如果只有prefixOverrides,suffixOverrides 表示删除开头的或结尾的xxxOverides指定的内容。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 <update id ="updateBatchApp" parameterType ="java.util.List" > UPDATE app_info <trim prefix ="set" suffixOverrides ="," > <trim prefix ="cpu = case" suffix ="end," > <foreach collection ="modelList" item ="item" index ="index" > <if test ="item != null" > when app_type =#{item.appType} then #{item.cpu} </if > </foreach > </trim > <trim prefix ="memory = case" suffix ="end," > <foreach collection ="modelList" item ="item" index ="index" > <if test ="item != null" > when app_type =#{item.appType} then #{item.memory} </if > </foreach > </trim > <trim prefix ="card = case" suffix ="end," > <foreach collection ="modelList" item ="item" index ="index" > when app_type =#{item.appType} then #{item.card} </foreach > </trim > <trim prefix ="nums = case" suffix ="end," > <foreach collection ="modelList" item ="item" index ="index" > when app_type =#{item.appType} then #{item.nums} </foreach > </trim > <trim prefix ="network = case" suffix ="end," > <foreach collection ="modelList" item ="item" index ="index" > when app_type =#{item.appType} then #{item.network} </foreach > </trim > <trim prefix ="is_in_use = case" suffix ="end," > <foreach collection ="modelList" item ="item" index ="index" > when app_type =#{item.appType} then #{item.isInUse} </foreach > </trim > </trim > where app_id = #{appId} </update >
关于性能问题没做研究，之前看过关于不同更新语句写法的一篇性能的分析，大家有兴趣可以看下：批量更新数据两种方法效率对比
删除
1 DELETE FROM app_info where id = #{id}
1 2 3 4 5 6 < delete id= "deleteApps" parameterType= "java.util.List"> DELETE FROM app_info where app_id in < foreach item= "item" collection= "appIds" open = "(" separator= "," close = ")"> #{item} < / foreach> < / delete >
时间字符串 order by 不知道各位是否遇到过，之前的前辈们在项目中将时间用字符串的方式存在DB中，而不是使用DATE,然后有一天你的前辈走了，你的主管说查出来按时间来排序….；呵呵，好！！！
1 2 3 4 5 6 7 < select id= "querySysParamList" resultMap= "sysParamDO"> SELECT * FROM app_info WHERE 1 = 1 < if test= "keyword != null and keyword != ''" > AND app_name LIKE concat('%' ,#{keyword},'%' ) < / if> ORDER BY DATE_FORMAT(update_time,'%H %k %I %r %T %S %w' ) DESC < / select >
字符串转为日期格式 SELECT DATE_FORMAT(‘2011-09-20 08:30:45’, ‘%Y-%m-%d %H:%i:%S’);
把日期转为字符串格式 SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’);
附：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 %M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩写的月份名字(Jan……Dec) %j 一年中的天数(001……366) %H 小时(00……23) %k 小时(0……23) %h 小时(01……12) %I 小时(01……12) %l 小时(1……12) %i 分钟, 数字(00……59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一个星期中的天数(0=Sunday ……6=Saturday ） %U 星期(0……52), 这里星期天是星期的第一天 %u 星期(0……52), 这里星期一是星期的第一天 %% 一个文字“%”。
先记录这些，有坑再补！
参考
