【mybatis-记录】

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.yhb.jsxn.mapper.FinanceProductsUsersMapper">
<!-- mybatis sql语句中的转义字符的书写：
        1、在xml的sql语句中，不能直接用大于号、小于号要用转义字符
        如果用小于号会报错误如下：
        org.apache.ibatis.builder.BuilderException: Error creating document instance. 
        Cause: org.xml.sax.SAXParseException: The content of elements must consist of well-formed character data or markup.
        转义字符
                小于号        ＜            &lt;
                大于号        >        &gt;
                和            &         &amp;
                单引号        '        &apos;
                双引号        "       &quot;
        2、使用<![CDATA[  你的sql语句  ]]>（sql语句中的<where><if>等标签不会被解析）
         如：
         <![CDATA[
                select * from 
              (select t.*, ROWNUM as rowno from tbl_user t where ROWNUM <= #{page.end,jdbcType=DECIMAL}) table_alias
              where table_alias.rowno >#{page.start,jdbcType=DECIMAL}
             ]]>          
-->
 
<!--    Mybatis批量插入Oracle、MySQL 


 --> 
 
<!-- mysql数据库的数据类型： -->
<!--

-->


<!--  mysql修改字符编码
　　X:\%path%MySQLMySQL Server 5.0inMySQLInstanceConfig.exe
　　重新启动设置，将默认编码设置为utf8.这样就能达到我们所要的效果了。

1、修改数据库字符编码

mysql> alter database mydb character set utf8 ;

2、创建数据库时，指定数据库的字符编码

mysql> create database mydb character set utf8 ;

3、查看mysql数据库的字符编码

mysql> show variables like 'character%'; //查询当前mysql数据库的所有属性的字符编码

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

4、修改mysql数据库的字符编码

修改字符编码必须要修改mysql的配置文件my.cnf,然后重启才能生效

通常需要修改my.cnf的如下几个地方：

【client】下面，加上default-character-set=utf8，或者character_set_client=utf8

【mysqld】下面，加上character_set_server = utf8 ；

因为以上配置，mysql默认是latin1，如果仅仅是通过命令行客户端，mysql重启之后就不起作用了。

如下是客户端命令行修改方式，不推荐使用

mysql> set character_set_client=utf8 ;

mysql> set character_set_connection=utf8 ;

mysql> set character_set_database=utf8 ;

mysql> set character_set_database=utf8 ;

mysql> set character_set_results=utf8 ;

mysql> set character_set_server=utf8 ;

mysql> set character_set_system=utf8 ;

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

    -> ;
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | utf8                                                          |
| character_set_system     | utf8                                                          |
| character_sets_dir       | C:Program Files (x86)MySQLMySQL Server 5.5sharecharsets |
+--------------------------+---------------------------------------------------------------+

8 rows in set (0.00 sec)
8 rows in set (0.00 sec)
 -->
    <!-- 查询结果映射 -->
    <!--解决数据库表字段列明和实体vo不匹配问题  -->
    <resultMap id="BaseResultMap" type="com.yhb.jsxn.entity.FinanceProductsUsers">
        <!-- 主键映射 -->
        <id column="FPUID" property="FPUID"                                     jdbcType="INTEGER" />
        <result column="UserID" property="UserID"                                 jdbcType="VARCHAR" />
        <result column="FProductsRates" property="FProductsRates"                 jdbcType="FLOAT" />
        <result column="FProductsBuyMoney" property="FProductsBuyMoney"         jdbcType="DECIMAL" />
        <result column="FProductsBuyTime" property="FProductsBuyTime"             jdbcType="TIMESTAMP" />
        <result column="FProductsRateInNum" property="FProductsRateInNum"         jdbcType="INTEGER" />

    </resultMap>

    <!-- select 语句
        select 标签属性：
            id： id编号
            parameterType: 获取的参数值：
                eg：
                    java.lang.Integer
                    map
            resultMap：
                eg：
                    返回的是一个映射结果集，对应一个实体vo类
                            想用ParameterType=Map传入多个参数构造SQL进行查询:
                            <select id="getBusList" resultMap="busListMap" parameterType="java.util.Map">  
                                    select bs.bus_id as bus_id,bs.arrive_time as up_time,b.start_station  
                                    as start_station_id,  
                                    b.end_station as end_station_id  
                                    from bus b , bus_station bs where b.bus_id = bs.bus_id and  
                                    bs.station_id=#{upStationId}  
                                    and is_up=1 and b.up_station_line like  
                                    #{upStationLineLike} and b.down_station_line  
                                    like  
                                    #{downStationLineLike}  
                                    and (b.daily=1 or b.weekly like #{weeklyLike} or b.run_day like  
                                    #{runDayLike} )  
                                    order by bs.arrive_time asc  
                                </select>  
                            调试时报 Parameter not found异常
                            解决方法，使用此方式传参，必须在对应的接口方法用@Param标签定义参数value才行:
                            
                            public List<Bus> getBusList(@Param(value = "upStationId") long upStationId,   
                                        @Param(value = "upStationLineLike") String upStationLineLike,   
                                        @Param(value = "downStationLineLike") String downStationLineLike,  
                                        @Param(value = "weeklyLike") String weeklyLike,  
                                        @Param(value = "runDayLike") String runDayLike  
                                        ){} ;  
            resultType：
                eg：
                    Integer
                    String
                    Decimal
                    int
     -->
   <select id="selectFinByFPUID" resultMap="BaseResultMap" parameterType="java.lang.Integer">
   select 
    *
    from financeproducts_users
    where FPUID = #{FPUID,jdbcType=INTEGER}
  </select>
    <select id="selectAllByFPid" resultMap="BaseResultMap" parameterType="map">
        select
        a.TrueName,a.UserName,b.FProductsBuyMoney,b.FProductsBuyTime from
        accounts_users a, financeproducts_users b where a.UserID = b.UserID
        and b.FPid=#{fpid} and FProductsCountNum &gt; FProductsRateInNum order
        by b.FProductsBuyTime DESC limit
        #{pageNo},#{size}
    </select>
    <select id="getFinancialUsers" resultType="String">
        select distinct userid from financeproducts_users
    </select>
    <select id="selectNewProduct" parameterType="String" resultType="Integer">
        select count(*) from financeproducts_users where UserID =#{userId} and
        FPid in (140,141)
    </select>
    <select id="getUserFreeze"  parameterType="map" resultType="Decimal">
        select sum(FproductsBuyMoney-FProductsRateMoney) userFrezz from
        financeproducts_users where UserID =#{userId} and FProductsEm_k2=1
    </select>
    <!--     <if test="array.length > 0">
                    <where>
                        <foreach collection="array"  open="(" item="age" close=")" separator=",">
                            and age in (#{age})
                        </foreach>
                    </where>
                </if> -->
    
    <select id="selectByUserIdById" resultMap="BaseResultMap" parameterType="Map">
        select * from (
        select
        a.UserID,a.FProductsBuyMoney,a.FProductsName,b.ProfitMoney,b.UserId_Get,b.UserName_Give
        from financeproducts_users a
        left join
        (SELECT
        UserId_Give,UserId_Get,UserName_Give,ProfitMoney FROM
        accounts_distributor_profit
        where UserId_Get=#{Id} group by UserId_Give
        )b
        on a.UserId=b.UserId_Give
        )a where a.UserId_Get is not null
    </select> 
     <select id="getHistoricalBuyProductsByName" resultMap="BaseResultMap"
        parameterType="hashMap">
        select
        FProductsName,FProductsRateInNum,FProductsImgs,FProductsCountRateMoney,FProductsBuyMoney,
        FProductsBuyTime,FProductsCountNum
        from financeproducts_users
        where
        <if test="UserID !=null">
            UserID =#{UserID}
        </if>
        and FProductsCountNum<![CDATA[<=]]>FProductsRateInNum
        <if test="startTime !=null">
            and DATE_FORMAT(FProductsBuyTime,'%Y-%m-%d')<![CDATA[>=]]>#{startTime}
        </if>
        <if test="dayNum !=null">
            and FProductsCountNum <![CDATA[<=]]>#{dayNum}
        </if>
        <if test="FProductsName !=null and FProductsName !=''">
        <!-- mysql> select concat_ws(',','11','22','33');
                
                +-------------------------------+
                | concat_ws(',','11','22','33') |
                +-------------------------------+
                | 11,22,33 |
                +-------------------------------+ 
        oracle 可以使用||来连接
        -->
            and FProductsName like CONCAT('%', #{FProductsName}, '%')
        </if>
    </select> 
    <select id="selectAlreadyByUserId" resultMap="BaseResultMap"
        parameterType="Map">
        select
        FProductsName,FProductsRateInNum,FProductsImgs,FProductsCountRateMoney,FProductsBuyMoney,
        FProductsBuyTime,FProductsCountNum
        from financeproducts_users
        where
        FProductsCountNum &gt; FProductsRateInNum and UserID =#{UserID}
        order
        by FProductsBuyTime
    </select>
        <!--根据查询条件获取历史购买产品并分页 
            用 ISNULL(), NVL(), IFNULL() and COALESCE() 函数替换空值
            在数据库操作中，往往要对一些查询出来的空值进行替换，如函数SUM(),这个函数如果没有值会返回NULL,这是我们不希望看到的，
                    在MySQL中我们可以这样来写：
                        select IFNULL(sum(data),0) ...
                    在SQLSERVER中我们可以这样写：
                        select ISNULL(sum(data),0) ...
                    在ORACLE中我们可以这样写：
                        select NVL(sum(data),0) ...
            对于所有数据库适用的方法可以这样写：
                    select COALESCE(sum(data),0) ...
        COALESCE()用法：
             COALESCE(value,...)
            返回第一个不是null的值，如果参数列表全是null,则返回null
                SELECT COALESCE(NULL,1);
                    -> 1
                SELECT COALESCE(NULL,NULL,NULL);
                -> NULL
        -->
    <select id="selectHistoricalByUserIdByPageName" resultMap="BaseResultMap"
        parameterType="Map">

            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 
             ( select * from financeproducts_users where FProductsEm_k2 = 0 and userid =#{UserID}) b
            left join
                    (select userid,FProductsBuyMoney,FProductsRateEm_k1,sum(FProductsAsRateMoney) as m from financeproducts_rates 
                    where userid =#{UserID}
                     group by FProductsRateEm_k1 ) a
                    on a.FProductsRateEm_k1=b.fpuid 

        <if test="startTime !=null">
            and DATE_FORMAT(b.FProductsBuyTime,'%Y-%m-%d')<![CDATA[>=]]>#{startTime}
        </if>
        <if test="productName !=null">
            and b.FProductsName like CONCAT('%', #{productName}, '%')
        </if>
    </select>
  
      <!-- insert -->
      <insert id="insertSelective" useGeneratedKeys="true" keyProperty="FPUID" parameterType="com.yhb.jsxn.entity.FinanceProductsUsers">
        insert into financeproducts_users
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="UserID != null">
                UserID,
            </if>
            <if test="FPid != null">
                FPid,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="UserID != null">
                #{UserID,jdbcType=VARCHAR},
            </if>
            <if test="FPid != null">
                #{FPid,jdbcType=INTEGER},
            </if>
            <if test="FProductsName != null">
                #{FProductsName,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
      
    <!-- update 语句 -->
    <update id="updateByPrimaryKeySelective" parameterType="com.yhb.jsxn.entity.FinanceProductsUsers">
        update financeproducts_users
        <set>
            <if test="FProductsEm_k2 != null">
                FProductsEm_k2=#{FProductsEm_k2,jdbcType=VARCHAR},
            </if>
            <if test="FProductsEm_k3 != null">
                FProductsEm_k3=#{FProductsEm_k3,jdbcType=VARCHAR},
            </if>
        </set>
        where FPUID =#{FPUID,jdbcType=INTEGER}
    </update>
    
    <!-- del 语句 -->
</mapper>