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%\MySQL\MySQL Server 5.0\bin\MySQLInstanceConfig.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)\MySQL\MySQL Server 5.5\share\charsets\ |
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>
