1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.yhb.jsxn.mapper.FinanceProductsUsersMapper"> 4 <!-- mybatis sql语句中的转义字符的书写： 5 1、在xml的sql语句中，不能直接用大于号、小于号要用转义字符 6 如果用小于号会报错误如下： 7 org.apache.ibatis.builder.BuilderException: Error creating document instance. 8 Cause: org.xml.sax.SAXParseException: The content of elements must consist of well-formed character data or markup. 9 转义字符 10 小于号 ＜ < 11 大于号 > > 12 和 & & 13 单引号 ' ' 14 双引号 " " 15 2、使用<![CDATA[ 你的sql语句 ]]>（sql语句中的<where><if>等标签不会被解析） 16 如： 17 <![CDATA[ 18 select * from 19 (select t.*, ROWNUM as rowno from tbl_user t where ROWNUM <= #{page.end,jdbcType=DECIMAL}) table_alias 20 where table_alias.rowno >#{page.start,jdbcType=DECIMAL} 21 ]]> 22 --> 23 24 <!-- Mybatis批量插入Oracle、MySQL 25 26 27 --> 28 29 <!-- mysql数据库的数据类型： --> 30 <!-- 31 32 --> 33 34 35 <!-- mysql修改字符编码 36 X:\%path%MySQLMySQL Server 5.0inMySQLInstanceConfig.exe 37 重新启动设置，将默认编码设置为utf8.这样就能达到我们所要的效果了。 38 39 1、修改数据库字符编码 40 41 mysql> alter database mydb character set utf8 ; 42 43 2、创建数据库时，指定数据库的字符编码 44 45 mysql> create database mydb character set utf8 ; 46 47 3、查看mysql数据库的字符编码 48 49 mysql> show variables like 'character%'; //查询当前mysql数据库的所有属性的字符编码 50 51 +--------------------------+----------------------------+ 52 | Variable_name | Value | 53 +--------------------------+----------------------------+ 54 | character_set_client | latin1 | 55 | character_set_connection | latin1 | 56 | character_set_database | utf8 | 57 | character_set_filesystem | binary | 58 | character_set_results | latin1 | 59 | character_set_server | utf8 | 60 | character_set_system | utf8 | 61 | character_sets_dir | /usr/share/mysql/charsets/ | 62 +--------------------------+----------------------------+ 63 64 4、修改mysql数据库的字符编码 65 66 修改字符编码必须要修改mysql的配置文件my.cnf,然后重启才能生效 67 68 通常需要修改my.cnf的如下几个地方： 69 70 【client】下面，加上default-character-set=utf8，或者character_set_client=utf8 71 72 【mysqld】下面，加上character_set_server = utf8 ； 73 74 因为以上配置，mysql默认是latin1，如果仅仅是通过命令行客户端，mysql重启之后就不起作用了。 75 76 如下是客户端命令行修改方式，不推荐使用 77 78 mysql> set character_set_client=utf8 ; 79 80 mysql> set character_set_connection=utf8 ; 81 82 mysql> set character_set_database=utf8 ; 83 84 mysql> set character_set_database=utf8 ; 85 86 mysql> set character_set_results=utf8 ; 87 88 mysql> set character_set_server=utf8 ; 89 90 mysql> set character_set_system=utf8 ; 91 92 mysql> show variables like 'character%'; 93 +--------------------------+----------------------------+ 94 | Variable_name | Value | 95 +--------------------------+----------------------------+ 96 | character_set_client | utf8 | 97 | character_set_connection | utf8 | 98 | character_set_database | utf8 | 99 | character_set_filesystem | binary | 100 | character_set_results | utf8 | 101 | character_set_server | utf8 | 102 | character_set_system | utf8 | 103 | character_sets_dir | /usr/share/mysql/charsets/ | 104 +--------------------------+----------------------------+ 105 106 -> ; 107 +--------------------------+---------------------------------------------------------------+ 108 | Variable_name | Value | 109 +--------------------------+---------------------------------------------------------------+ 110 | character_set_client | utf8 | 111 | character_set_connection | utf8 | 112 | character_set_database | utf8 | 113 | character_set_filesystem | binary | 114 | character_set_results | utf8 | 115 | character_set_server | utf8 | 116 | character_set_system | utf8 | 117 | character_sets_dir | C:Program Files (x86)MySQLMySQL Server 5.5sharecharsets | 118 +--------------------------+---------------------------------------------------------------+ 119 120 8 rows in set (0.00 sec) 121 8 rows in set (0.00 sec) 122 --> 123 <!-- 查询结果映射 --> 124 <!--解决数据库表字段列明和实体vo不匹配问题 --> 125 <resultMap id="BaseResultMap" type="com.yhb.jsxn.entity.FinanceProductsUsers"> 126 <!-- 主键映射 --> 127 <id column="FPUID" property="FPUID" jdbcType="INTEGER" /> 128 <result column="UserID" property="UserID" jdbcType="VARCHAR" /> 129 <result column="FProductsRates" property="FProductsRates" jdbcType="FLOAT" /> 130 <result column="FProductsBuyMoney" property="FProductsBuyMoney" jdbcType="DECIMAL" /> 131 <result column="FProductsBuyTime" property="FProductsBuyTime" jdbcType="TIMESTAMP" /> 132 <result column="FProductsRateInNum" property="FProductsRateInNum" jdbcType="INTEGER" /> 133 134 </resultMap> 135 136 <!-- select 语句 137 select 标签属性： 138 id： id编号 139 parameterType: 获取的参数值： 140 eg： 141 java.lang.Integer 142 map 143 resultMap： 144 eg： 145 返回的是一个映射结果集，对应一个实体vo类 146 想用ParameterType=Map传入多个参数构造SQL进行查询: 147 <select id="getBusList" resultMap="busListMap" parameterType="java.util.Map"> 148 select bs.bus_id as bus_id,bs.arrive_time as up_time,b.start_station 149 as start_station_id, 150 b.end_station as end_station_id 151 from bus b , bus_station bs where b.bus_id = bs.bus_id and 152 bs.station_id=#{upStationId} 153 and is_up=1 and b.up_station_line like 154 #{upStationLineLike} and b.down_station_line 155 like 156 #{downStationLineLike} 157 and (b.daily=1 or b.weekly like #{weeklyLike} or b.run_day like 158 #{runDayLike} ) 159 order by bs.arrive_time asc 160 </select> 161 调试时报 Parameter not found异常 162 解决方法，使用此方式传参，必须在对应的接口方法用@Param标签定义参数value才行: 163 164 public List<Bus> getBusList(@Param(value = "upStationId") long upStationId, 165 @Param(value = "upStationLineLike") String upStationLineLike, 166 @Param(value = "downStationLineLike") String downStationLineLike, 167 @Param(value = "weeklyLike") String weeklyLike, 168 @Param(value = "runDayLike") String runDayLike 169 ){} ; 170 resultType： 171 eg： 172 Integer 173 String 174 Decimal 175 int 176 --> 177 <select id="selectFinByFPUID" resultMap="BaseResultMap" parameterType="java.lang.Integer"> 178 select 179 * 180 from financeproducts_users 181 where FPUID = #{FPUID,jdbcType=INTEGER} 182 </select> 183 <select id="selectAllByFPid" resultMap="BaseResultMap" parameterType="map"> 184 select 185 a.TrueName,a.UserName,b.FProductsBuyMoney,b.FProductsBuyTime from 186 accounts_users a, financeproducts_users b where a.UserID = b.UserID 187 and b.FPid=#{fpid} and FProductsCountNum > FProductsRateInNum order 188 by b.FProductsBuyTime DESC limit 189 #{pageNo},#{size} 190 </select> 191 <select id="getFinancialUsers" resultType="String"> 192 select distinct userid from financeproducts_users 193 </select> 194 <select id="selectNewProduct" parameterType="String" resultType="Integer"> 195 select count(*) from financeproducts_users where UserID =#{userId} and 196 FPid in (140,141) 197 </select> 198 <select id="getUserFreeze" parameterType="map" resultType="Decimal"> 199 select sum(FproductsBuyMoney-FProductsRateMoney) userFrezz from 200 financeproducts_users where UserID =#{userId} and FProductsEm_k2=1 201 </select> 202 <!-- <if test="array.length > 0"> 203 <where> 204 <foreach collection="array" open="(" item="age" close=")" separator=","> 205 and age in (#{age}) 206 </foreach> 207 </where> 208 </if> --> 209 210 <select id="selectByUserIdById" resultMap="BaseResultMap" parameterType="Map"> 211 select * from ( 212 select 213 a.UserID,a.FProductsBuyMoney,a.FProductsName,b.ProfitMoney,b.UserId_Get,b.UserName_Give 214 from financeproducts_users a 215 left join 216 (SELECT 217 UserId_Give,UserId_Get,UserName_Give,ProfitMoney FROM 218 accounts_distributor_profit 219 where UserId_Get=#{Id} group by UserId_Give 220 )b 221 on a.UserId=b.UserId_Give 222 )a where a.UserId_Get is not null 223 </select> 224 <select id="getHistoricalBuyProductsByName" resultMap="BaseResultMap" 225 parameterType="hashMap"> 226 select 227 FProductsName,FProductsRateInNum,FProductsImgs,FProductsCountRateMoney,FProductsBuyMoney, 228 FProductsBuyTime,FProductsCountNum 229 from financeproducts_users 230 where 231 <if test="UserID !=null"> 232 UserID =#{UserID} 233 </if> 234 and FProductsCountNum<![CDATA[<=]]>FProductsRateInNum 235 <if test="startTime !=null"> 236 and DATE_FORMAT(FProductsBuyTime,'%Y-%m-%d')<![CDATA[>=]]>#{startTime} 237 </if> 238 <if test="dayNum !=null"> 239 and FProductsCountNum <![CDATA[<=]]>#{dayNum} 240 </if> 241 <if test="FProductsName !=null and FProductsName !=''"> 242 <!-- mysql> select concat_ws(',','11','22','33'); 243 244 +-------------------------------+ 245 | concat_ws(',','11','22','33') | 246 +-------------------------------+ 247 | 11,22,33 | 248 +-------------------------------+ 249 oracle 可以使用||来连接 250 --> 251 and FProductsName like CONCAT('%', #{FProductsName}, '%') 252 </if> 253 </select> 254 <select id="selectAlreadyByUserId" resultMap="BaseResultMap" 255 parameterType="Map"> 256 select 257 FProductsName,FProductsRateInNum,FProductsImgs,FProductsCountRateMoney,FProductsBuyMoney, 258 FProductsBuyTime,FProductsCountNum 259 from financeproducts_users 260 where 261 FProductsCountNum > FProductsRateInNum and UserID =#{UserID} 262 order 263 by FProductsBuyTime 264 </select> 265 <!--根据查询条件获取历史购买产品并分页 266 用 ISNULL(), NVL(), IFNULL() and COALESCE() 函数替换空值 267 在数据库操作中，往往要对一些查询出来的空值进行替换，如函数SUM(),这个函数如果没有值会返回NULL,这是我们不希望看到的， 268 在MySQL中我们可以这样来写： 269 select IFNULL(sum(data),0) ... 270 在SQLSERVER中我们可以这样写： 271 select ISNULL(sum(data),0) ... 272 在ORACLE中我们可以这样写： 273 select NVL(sum(data),0) ... 274 对于所有数据库适用的方法可以这样写： 275 select COALESCE(sum(data),0) ... 276 COALESCE()用法： 277 COALESCE(value,...) 278 返回第一个不是null的值，如果参数列表全是null,则返回null 279 SELECT COALESCE(NULL,1); 280 -> 1 281 SELECT COALESCE(NULL,NULL,NULL); 282 -> NULL 283 --> 284 <select id="selectHistoricalByUserIdByPageName" resultMap="BaseResultMap" 285 parameterType="Map"> 286 287 select b.UserID,coalesce(a.FProductsBuyMoney,b.FProductsRateMoney) as FProductsBuyMoney,coalesce(a.m,0) as sumProfit,b.FProductsRateMoney,b.FPUID,b.FProductsName,b.FProductsImgs from 288 ( select * from financeproducts_users where FProductsEm_k2 = 0 and userid =#{UserID}) b 289 left join 290 (select userid,FProductsBuyMoney,FProductsRateEm_k1,sum(FProductsAsRateMoney) as m from financeproducts_rates 291 where userid =#{UserID} 292 group by FProductsRateEm_k1 ) a 293 on a.FProductsRateEm_k1=b.fpuid 294 295 <if test="startTime !=null"> 296 and DATE_FORMAT(b.FProductsBuyTime,'%Y-%m-%d')<![CDATA[>=]]>#{startTime} 297 </if> 298 <if test="productName !=null"> 299 and b.FProductsName like CONCAT('%', #{productName}, '%') 300 </if> 301 </select> 302 303 <!-- insert --> 304 <insert id="insertSelective" useGeneratedKeys="true" keyProperty="FPUID" parameterType="com.yhb.jsxn.entity.FinanceProductsUsers"> 305 insert into financeproducts_users 306 <trim prefix="(" suffix=")" suffixOverrides=","> 307 <if test="UserID != null"> 308 UserID, 309 </if> 310 <if test="FPid != null"> 311 FPid, 312 </if> 313 </trim> 314 <trim prefix="values (" suffix=")" suffixOverrides=","> 315 <if test="UserID != null"> 316 #{UserID,jdbcType=VARCHAR}, 317 </if> 318 <if test="FPid != null"> 319 #{FPid,jdbcType=INTEGER}, 320 </if> 321 <if test="FProductsName != null"> 322 #{FProductsName,jdbcType=VARCHAR}, 323 </if> 324 </trim> 325 </insert> 326 327 <!-- update 语句 --> 328 <update id="updateByPrimaryKeySelective" parameterType="com.yhb.jsxn.entity.FinanceProductsUsers"> 329 update financeproducts_users 330 <set> 331 <if test="FProductsEm_k2 != null"> 332 FProductsEm_k2=#{FProductsEm_k2,jdbcType=VARCHAR}, 333 </if> 334 <if test="FProductsEm_k3 != null"> 335 FProductsEm_k3=#{FProductsEm_k3,jdbcType=VARCHAR}, 336 </if> 337 </set> 338 where FPUID =#{FPUID,jdbcType=INTEGER} 339 </update> 340 341 <!-- del 语句 --> 342 </mapper>