采购系统(二)

一、用户列表

1.实体分析

(1)数据库表分析

系统用户表: SYSUSER

记录系统所有的用户。

Id: 主键

USERID: 账号

USERNAME: 名称

GROUPID: 用户类别 0:系统管理员 1:卫生局 2:卫生院 3:卫生室 4:供货商

SYSID: 系统用户所属的单位

监督单位表: USERJD

Id: 主键

MC: 单位名称

DQ: 管理区域

医院单位表: USERYY

Id: 主键

MC: 单位名称

DQ: 所属区域

供货商单位表: USERGYS

Id: 主键

Mc: 单位名称

2.需求

(1)自定义查询，查询用户信息

SQL语句:

第一种查询

select *
  from (select SYSUSER.id,
               SYSUSER.userid,
               SYSUSER.username,
               SYSUSER.groupid,
               SYSUSER.sysid,
               
               nvl(userjd.mc, nvl(useryy.mc, usergys.mc)) sysmc
        
          from SYSUSER
        
          left join userjd
            on SYSUSER.Sysid = userjd.id
        
          left join useryy
            on SYSUSER.Sysid = useryy.id
        
          left join usergys
            on SYSUSER.Sysid = usergys.id
        
        ) u
 where u.sysmc like ‘%高村%‘;

NVL函数

格式: NVL(E1,E2)

解释: 如果E1为NULL，则函数返回E2，否则就返回E1。

NVL2函数

格式: NVL2(E1,E2,E3)

解释: 如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

第二种查询

select *
  from (select SYSUSER.id,
               SYSUSER.userid,
               SYSUSER.username,
               SYSUSER.groupid,
               SYSUSER.sysid,
               decode(SYSUSER.Groupid,
                      ‘1‘,
                      (select mc from userjd where id = sysuser.sysid),
                      ‘2‘,
                      (select mc from userjd where id = sysuser.sysid),
                      ‘3‘,
                      (select mc from useryy where id = sysuser.sysid),
                      ‘4‘,
                      (select mc from usergys where id = sysuser.sysid)) sysmc
        
          from SYSUSER)u
 where u.sysmc like ‘%高村%‘

DECODE函数

格式: DECODE(value, if1, then1, if2,then2, if3,then3, . . . else)

解释: 如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2。

2.后台代码

(1)实体类

查询结果的包装类

SysuserCustom.java

查询条件的包装类

SysuserQueryVo.java

由于页面是DataGrid所以查询结果还要封装一个对象

(2)Mapper相关

SysuserMapperCustom.xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="yycg.base.dao.mapper.SysuserMapperCustom">

	<!-- sql片段 -->
	<!-- 用户查询条件 -->
	<sql id="quey_sysuser_where">
		<if test="sysuserCustom != null">
		
        		<if test="sysuserCustom.sysmc != null and sysuserCustom.sysmc !=‘‘">
        			and  sysuser.sysmc like ‘%${sysuser.sysmc}%‘
        		</if>
        		
        		<if test="sysuserCustom.userid != null and sysuserCustom.userid !=‘‘">
        			and  sysuser.userid = #{sysuserCustom.userid}
        		</if>
        		
        		<if test="sysuserCustom.username != null and sysuserCustom.username !=‘‘">
        			and  sysuser.username = #{sysuserCustom.username}
        		</if>
        		
        		<if test="sysuserCustom.groupid != null and sysuserCustom.groupid !=‘‘">
        			and  sysuser.groupid = #{sysuserCustom.groupid}
        		</if>
        		
          </if>
	</sql>
	
	<!-- 用户查询 -->
	<select id="findSysuserList" parameterType="yycg.base.pojo.vo.SysuserQueryVo" resultType="yycg.base.pojo.vo.SysuserCustom">
	select * from (
 		 select SYSUSER.id,
         SYSUSER.userid,
         SYSUSER.username,
         SYSUSER.groupid,
         SYSUSER.userstate,
         SYSUSER.sysid,
         decode(SYSUSER.Groupid,
                ‘1‘,
                (select mc from userjd where id = sysuser.sysid),
                ‘2‘,
                (select mc from userjd where id = sysuser.sysid),
                ‘3‘,
                (select mc from useryy where id = sysuser.sysid),
                ‘4‘,
                (select mc from usergys where id = sysuser.sysid)
                ) sysmc
        
          from SYSUSER
          )sysuser 
          
          <where>
          	<include refid="quey_sysuser_where"/>
          </where>
          
        
	</select>

</mapper>

SysuserMapperCustom.java

(3)Service/Action

SqlMapconfig.xml

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

	<!-- 配置分页插件 -->
	<plugins>
		<plugin interceptor="com.github.pagehelper.PageHelper">
			<!-- 指定使用的数据库 -->
			<property name="dialect" value="oracle" />
		</plugin>
	</plugins>
</configuration>

SysuserAction.java

采购系统(二)

原文：http://www.cnblogs.com/yangang2013/p/5667976.html