`
simafengyun
  • 浏览: 7355 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

just for reference

 
阅读更多
###SQL Server configuration
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jdbc.databaseURL=jdbc:sqlserver://10.102.0.183:1433;DatabaseName=resource
#jdbc.databaseURL=jdbc:sqlserver://180.168.4.198:1433;DatabaseName=resource
jdbc.databaseURL=jdbc:sqlserver://localhost:1433;DatabaseName=resource
jdbc.username=sa
jdbc.password=sa


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <!-- 直接注入映射器,DaoImpl就不用写了-->
    <!-- <bean id="userDao" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="com.resource.dao.UserDao" />
    </bean>-->
  
    <!-- 使用自动扫描映射器,这样无需写xml就能自动装配Dao了，而且DaoImpl也不用写-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.resource.dao" />
    </bean>

</beans>


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <!-- 数据源 -->
    <!--<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="/jdbc/fantasyDS" />-->
        <!-- 当 resourceRef属性为true时，jndiName会被加上前缀java:comp/env/-->
        <!-- <property name="resourceRef" value="true" />-->
    <!--</bean>-->

    <!-- 把JDBC属性配置移到外部文件 -->
    <context:property-placeholder location="classpath:properties/jdbc.properties" />
  
    <!-- 使用c3p0数据源连接池 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass" value="${jdbc.driverClassName}" />
        <property name="jdbcUrl" value="${jdbc.databaseURL}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="initialPoolSize" value="5" />
        <property name="minPoolSize" value="5" />
        <property name="maxPoolSize" value="5" />
        <property name="acquireIncrement" value="1" />
        <property name="maxIdleTime" value="5" />
    </bean>
  
    <!-- 使用DBCP数据源连接池 -->
    <!-- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="defaultAutoCommit" value="false" />
    </bean> -->

    <!-- 整合mybatis和spring的SqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath:/sql/*.xml" />
    </bean>

    <!-- 使用纯JDBC作为事务 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 使用注解方式来划分事务 -->
    <tx:annotation-driven />


</beans>


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <!-- 使用自动扫描装配bean -->
    <context:component-scan base-package="com.resource" />
  
    <!-- 将系统相关配置文件读入缓存 -->
    <bean id="sysConfig" class="org.apache.commons.configuration.PropertiesConfiguration">
        <constructor-arg value="properties/sys-config.properties" />
    </bean>
  
    <bean id="rtxConfig" init-method="init" class="com.resource.util.PropertyUtil">
        <constructor-arg value="properties/sys-config.properties" />
    </bean>
  
    <bean id="rtxListener" init-method="init" class="com.resource.rtx.RTXListener"/>
  

</beans>


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>

    <mappers>
        <mapper resource="mybatis/account.xml"/>
        <mapper resource="mybatis/shopprod.xml"/>
        <mapper resource="mybatis/autonum.xml"/>
        <mapper resource="mybatis/pltfmadjtype.xml"/>
        <mapper resource="mybatis/pltfmprod.xml"/>
        <mapper resource="mybatis/priceadjhistory.xml"/>
        <mapper resource="mybatis/profile.xml"/>
        <mapper resource="mybatis/shop.xml"/>
        <mapper resource="mybatis/user.xml"/>
      
    </mappers>
 
</configuration>

<?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.resource.dao.UpgradePackDao">

    <sql id="getAllColumn">
        SELECT
            p.ID as upgradePackID,
            p.Name as upgradePackName,
            p.Description as upgradePackDesc,
            p.FileName,
            p.CreateTime,
            p.PlanEndDate,
            p.ActualEndDate,
            p.StatusID,
            s.Name as StatusName,
            p.DelFlg
        FROM
            UpgradePack p inner join status s on p.StatusID = s.ID

    </sql>
   
    <!-- 待办事项-->
    <sql id="processingCondition">
        AND (
            (p.statusID IN (110, 115, 170) AND r.roleID = 1)
         OR (p.statusID IN (120) AND r.roleID = 2)
         OR (p.statusID IN (130) AND r.roleID = 3)
         OR (p.statusID IN (140) AND r.roleID = 4)
         OR (p.statusID IN (150) AND r.roleID = 5)
         OR (p.statusID IN (160) AND r.roleID = 6)
        )
    </sql>   
   
    <!-- 将办理事项-->
    <sql id="willProcessCondition">       
        AND (
            (p.statusID &lt; 120 AND r.roleID = 2)
         OR (p.statusID &lt; 130 AND r.roleID = 3)
         OR (p.statusID &lt; 140 AND r.roleID = 4)
         OR (p.statusID &lt; 150 AND r.roleID = 5)
         OR (p.statusID &lt; 160 AND r.roleID = 6)
        )
       
    </sql>
    <!-- 已办理事项-->
    <sql id="processedCondition">       
        AND (
            (p.statusID &gt;=  120 AND r.roleID = 1)
         OR (p.statusID &gt;=  130 AND r.roleID = 2)
         OR (p.statusID &gt;=  140 AND r.roleID = 3)
         OR (p.statusID &gt;=  150 AND r.roleID = 4)
         OR (p.statusID &gt;=  160 AND r.roleID = 5)
         OR (p.statusID &gt;=  170 AND r.roleID = 6)
        )
        AND p.statusID NOT IN (190, 200)
    </sql>
    <!-- 已完成事项-->
    <sql id="finishedCondition">
        AND p.statusID IN (190, 200)
    </sql>   
   
    <!-- 将办理事项 table-->       
    <sql id="willProcessTable">
        SELECT
            r1.UserID,
            r1.upgradePackID,
            min(r1.roleID) roleID
        FROM
            UserRoleUpgrade r1  inner join UpgradePack p1 on r1.UpgradePackID = p1.ID
        WHERE
            r1.UserID = #{userID, jdbcType=NUMERIC} AND
             ((p1.statusID &lt; 120  AND r1.roleID &gt; 1) OR
            (120 &lt;= p1.statusID AND p1.statusID &lt; 130 AND r1.roleID &gt;= 2) OR
            (130 &lt;= p1.statusID AND p1.statusID &lt; 140 AND r1.roleID &gt;= 3) OR
            (140 &lt;= p1.statusID AND p1.statusID &lt; 150 AND r1.roleID &gt;= 4) OR
            (150 &lt;= p1.statusID AND p1.statusID &lt; 160 AND r1.roleID &gt;= 5) OR
            (160 &lt;= p1.statusID AND p1.statusID &lt; 170 AND r1.roleID &gt;= 6))
        GROUP BY r1.UserID, r1.upgradePackID
    </sql>                               
    <!-- 已办理事项table-->
    <sql id="processedTable">

        SELECT
            r1.UserID,
            r1.upgradePackID,
            max(r1.roleID) roleID
        FROM
            UserRoleUpgrade r1  inner join UpgradePack p1 on r1.UpgradePackID = p1.ID
        WHERE
            r1.UserID = #{userID, jdbcType=NUMERIC} AND
             ((p1.statusID &lt;= 120  AND r1.roleID &lt;= 1) OR
            (120 &lt; p1.statusID AND p1.statusID &lt;= 130 AND r1.roleID &lt;= 2) OR
            (130 &lt; p1.statusID AND p1.statusID &lt;= 140 AND r1.roleID &lt;= 3) OR
            (140 &lt; p1.statusID AND p1.statusID &lt;= 150 AND r1.roleID &lt;= 4) OR
            (150 &lt; p1.statusID AND p1.statusID &lt;= 160 AND r1.roleID &lt;= 5) OR
            (160 &lt; p1.statusID AND p1.statusID &lt;= 170 AND r1.roleID &lt;= 6))
        GROUP BY r1.UserID, r1.upgradePackID

    </sql>
           
               
               
    <select id="findUpgradePackByIDs" parameterType="com.resource.dto.PageDto" resultType="com.resource.dto.UpgradePackDto">
        <include refid="getAllColumn" />
        Where p.ID in
        <foreach collection="list" item="upgradePackIDs"  open="(" separator="," close=")">
            #{upgradePackIDs}
        </foreach>   
    </select>
   
    <select id="findUpgradePackByUserID" parameterType="com.resource.dto.PageDto" resultType="Integer">
        SELECT
            p.ID as upgradePackID
        FROM
            UpgradePack p
        WHERE
            p.DelFlg = 'Y' AND
            exists (
                SELECT
                    roleID
                FROM
               
                <!-- 待办事项 -->
                <if test="sType != 3 and sType != 4">
                    UserRoleUpgrade r
                </if>
               
                <!-- 将办理事项-->
                <if test="sType == 3">
                    (
                   
                        <include refid="willProcessTable" />
                   
                    ) r
                </if>
                <!-- 已办理事项-->
                <if test="sType == 4">
                    (
                   
                    <include refid="processedTable" />
                   
                    ) r
                </if>
                       
                   
                   
                WHERE
                    p.ID = r.UpgradePackID AND
                    r.UserID = #{userID, jdbcType=NUMERIC}
               
                <!-- 待办事项-->
                <if test="sType == 2">               
                    <include refid="processingCondition" />
                </if>
               
                <!-- 将办理事项-->
                <if test="sType == 3">                   
                    <include refid="willProcessCondition" />
                </if>
                <!-- 已办理事项-->
                <if test="sType == 4">
                    <include refid="processedCondition" />
                </if>
               
                <!-- 已完成事项-->
                <if test="sType == 5">
                    <include refid="finishedCondition" />
                </if>                                   

            )order by p.ID;
    </select>
   
   
    <select id="findUpgradePackCountByUserID" parameterType="com.resource.dto.UserDto" resultType="com.resource.dto.CountDto">
   
    <!-- 待办事项-->
           SELECT
               2 as typ,
            count(p.ID)  as cnt
        FROM
            UpgradePack p inner join status s on p.StatusID = s.ID
        WHERE
            p.DelFlg = 'Y'
            AND exists (
                SELECT
                    roleID
                FROM
                    UserRoleUpgrade r
                WHERE
                    p.ID = r.UpgradePackID AND
                    r.UserID = #{ID, jdbcType=NUMERIC}    
                    <include refid="processingCondition" />
            )
           
        Union
        <!-- 将办事项-->   
        SELECT
               3 as typ,
            count(p.ID)  as cnt
        FROM
            UpgradePack p inner join status s on p.StatusID = s.ID
        WHERE
            p.DelFlg = 'Y'
            AND exists (
                SELECT
                    roleID
                FROM
               
                (
                            <include refid="willProcessTable" />   
                   
   
                   
                    ) r
                               
                WHERE
                    p.ID = r.UpgradePackID AND
                    r.UserID = #{ID, jdbcType=NUMERIC}
                    <!-- 将办理事项-->               
                    <include refid="willProcessCondition" />
            )
           
            union       
        <!-- 已办理事项-->
        SELECT
               4 as typ,
            count(p.ID)  as cnt
        FROM
            UpgradePack p inner join status s on p.StatusID = s.ID
        WHERE
            p.DelFlg = 'Y'
            AND exists (
                SELECT
                    roleID
                FROM
               
                (<include refid="processedTable" />) r
                               
                WHERE
                    p.ID = r.UpgradePackID AND
                    r.UserID = #{ID, jdbcType=NUMERIC}        
                    <!-- 已办理事项-->
                    <include refid="processedCondition" />
            )
           
        union
        <!-- 已完成事项-->   
         SELECT
               5 as typ,
            count(p.ID)  as cnt
        FROM
            UpgradePack p inner join status s on p.StatusID = s.ID
        WHERE
            p.DelFlg = 'Y'
            AND exists (
                SELECT
                    roleID
                FROM
                    UserRoleUpgrade r
                WHERE
                    p.ID = r.UpgradePackID AND
                    r.UserID = #{ID, jdbcType=NUMERIC}    
                    <!-- 已完成事项-->                       
                    <include refid="finishedCondition" />    
            )           
    </select>
   
    <select id="findUpgradePackByID" parameterType="Integer" resultType="com.resource.dto.UpgradePackDto">
        <include refid="getAllColumn" />
        WHERE
            p.DelFlg = 'Y'
            AND p.ID = #{upgradePackID};
    </select>
           
    <insert id="insert" parameterType="com.resource.dto.UpgradePackDto">
       
        <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="upgradePackID">
            SELECT @@IDENTITY AS upgradePackID   
        </selectKey>
       
        INSERT INTO UpgradePack (        
              Name,
              Description,
              FileName,
              CreateTime,
              PlanEndDate,
              StatusID
        )VALUES(
                #{upgradePackName, jdbcType=VARCHAR},
                #{upgradePackDesc, jdbcType=VARCHAR},  
                #{fileName, jdbcType=VARCHAR},        
                getdate(),
                #{planEndDate, jdbcType=DATE},
                #{statusID, jdbcType=NUMERIC}
        )

    </insert>
   
   
    <update id="updateStatus" parameterType="com.resource.dto.UpgradePackDto">
        UPDATE
            UpgradePack
        SET
            StatusID = #{statusID, jdbcType=NUMERIC}
            <if test="actualEndDate != null and actualEndDate != ''">
            , actualEndDate = getdate()
            </if>
        where
       
              ID = #{upgradePackID, jdbcType=NUMERIC}             
            <if test="statusID == '160'">
            AND not exists
           
            (
                SELECT
                    *
                FROM
                    UserRoleUpgrade r
                WHERE
                    RoleID = 5
                    AND UpgradePackID =  #{upgradePackID, jdbcType=NUMERIC}
                    AND ( ApproveFlg &lt;&gt; 'Y' or ApproveFlg is null)     
            )           
            </if>

    </update>
   
   
       <update id="update" parameterType="com.resource.dto.UpgradePackDto">
        UPDATE
            UpgradePack
        SET


<!--             <if test="upgradePackName != null and upgradePackName != ''"> -->
            Name = #{upgradePackName, jdbcType=VARCHAR}
        <!--     </if>         -->   
            <if test="upgradePackDesc != null and upgradePackDesc != ''">
            , Description = #{upgradePackDesc, jdbcType=VARCHAR}
            </if>   
            <if test="planEndDate != null and planEndDate != ''">
            , planEndDate = #{planEndDate, jdbcType=DATE}
            </if>
            <if test="fileName != null and fileName != ''">
            , FileName = #{fileName, jdbcType=VARCHAR}
            </if>
        where
       
              ID = #{upgradePackID, jdbcType=NUMERIC}

    </update>
   
    <delete id="deleteByID" parameterType="Integer">
        DELETE FROM
            UpgradePack
        WHERE
            ID = #{upgradePackID}
    </delete>

</mapper>




分享到：
| Jquery操作select
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    51踩踩软件haobig

    8) The price is just for reference. Please contact us to get more information to the product. http://www.cardior.cn E-mail:cardior168@hotmail.com Fax: 0086-755-83841986 Mobile: 0086-13631539986 ...

    wifi_hack PDF

    is a live-linux cd that is compatible with most laptops (Just for reference sake, I am using my Eee PC with an external DVDRom drive to boot it). All you have to do is go to www.remote- exploit.org ...

    (NIO)just for my own reference

    标题中的"NIO"指的是Java的非阻塞I/O（Non-blocking Input/Output）模型，它是一种高效处理网络I/O操作的技术。NIO在Java中主要由java.nio包提供，包括了通道（Channels）、缓冲区（Buffers）和选择器（Selectors）...

    SCE 11.0x-WMM-Shipping

    4. **Just for reference only**：该文档仅作参考用途，意味着它不是正式的操作协议或合同，而是为了辅助用户理解和应用Infor SCE 11.0.x的运输管理功能。 **版权信息**：Infor拥有此出版物（包括任何附加信息）的...

    C++结束系统进程实例

    非常简单的一个结束进程实例 Just for reference...

    SCE 11.0x-Small Partcel Shipping

    1. INFOR SCE 11.0.X - 2017V; 2. Chn vs Eng in manual; 3. Ref to Word Specification if any misunderstanding met; 4. Just for reference only.

    Ebooks For Dummies Collection

    Access 2007 AIO Desk Reference For Dummies.pdf Access 2007 Vba Programming For Dummies Feb 2007.pdf Access Forms & Reports For Dummies.pdf Acne For Dummies.pdf ACT 2007 For Dummies.pdf ACT ! 2005 For ...

    Apache Velocity - VTL Reference

    Apache Velocity - VTL Reference just for convenience, it's from official site documentation

    Find Reference 2

    Just select one or more asset in Project panel and FR2 will show you: - Exactly what's being included in each scene or in each prefab. Remember that one redundant reference may resulted in asset ...

    Web Developer's Reference Guide

    This section will not just introduce functions, but will provide you with an entire reference for the language and paradigms. You will discover more about three of the most popular frameworks today-...

    Find Reference 2 1.2.10

    Just select one or more asset in Project panel and FR2 will show you: - Exactly what's being included in each scene or in each prefab. Remember that one redundant reference may resulted in asset ...

    Solaris.10-The.Complete.Reference

    Solaris 10 is now free for all users, making it just as accessible as competing, “free” UNIX-style systems, such as Linux, and pay-per-seat systems such as Microsoft Windows. Each chapter provides a...

    FLEXlm_Reference_Manual

    • Trivial API, used with single-process applications that checkout just one feature. It is provided in two formats: macro-based and function-based. • Simple API, used with applications that require ...

    Just-In-Time Math for Engineers

    ust-In-Time Math is a concise review and summary of the mathematical ...Includes conversion tables, quick reference guides, and hundreds of solved example problems based on common engineering situations

    Samsung.ARTIK.Reference.The.Definitive.Developers.Guide

    It is also a perfect follow-up resource for new-to-the-field developers who are just getting past the beginning stages of learning the ARTIK. Samsung ARTIK Reference begins with a concise overview ...

    XSLT 2.0 Programmer's Reference, Third Edition

    This book, as the title implies, is primarily a practical reference book for professional XSLT developers. It assumes no previous knowledge of the language, and many developers have used it as their ...

    SI100B_2020:ShanghaiTech SI100B 2020秋季参考数据

    SI100B_2020Archives of ShanghaiTech 2020 Fall SI 100B class data.Notice that homework and quiz are just for reference. DO NOT use them for illegal aims.For SP project, turn to our team's repository:[]...

Magicbox
Global site tag (gtag.js) - Google Analytics