联合查询jjjj

（利用spring、springMVC、hibernate实现对员工表的联合查询）

• 联合查询思路

通过从客户端传回所需查询数据库字段条件，然后通过服务端根据客户端传过来的条件对数据库进行查询

客户端联合查询传值方式代码：

html代码
输入区

后台联合查询逻辑代码：

package controller;

import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import bean.EmpBean;
import dao.EmpDao;

@Controller
public class EmpController {
    private final static Log log = LogFactory.getLog(EmpController.class);
    
    @Resource
    private EmpDao empDao;
    
    @RequestMapping(value="/findAllEmp.do")
    public ModelAndView findAllEmp(ModelAndView mv,String empno, String ename, String job, String deptno) {
        StringBuffer hql = new StringBuffer("from EmpBean t where 1=1 ");
        if(StringUtils.isNotBlank(job)) {
            hql.append(" and job like upper('%"+ job +"%')");       //模糊查询这个字段中有客户端传过来的字符数据
            mv.addObject("job",job);                    //将传入服务端的数据传回客户端，进行条件回显
        }
        if(StringUtils.isNotBlank(empno)) {
            hql.append(" and empno like upper('%"+ empno +"%')");
            mv.addObject("empno",empno);
        }
        if(StringUtils.isNotBlank(ename)) {
            hql.append(" and ename like upper('%"+ ename +"%')");
            mv.addObject("ename",ename);
        }
        if(StringUtils.isNotBlank(deptno)) {
            hql.append(" and deptno like upper('%"+ deptno +"%')");
            mv.addObject("deptno",deptno);
        }
        hql.append(" order by empno");      //排序
        List<EmpBean> listEmp = empDao.findListByHql(hql.toString());
        mv.addObject("listEmp",listEmp);
        mv.setViewName("emp_manage");
        return mv;
    }
    
}

Dao层查询方法代码：

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import bean.EmpBean;

@Repository
@Transactional
public class EmpDao {
    
    @Resource 
    private SessionFactory sessionFactory;
    
    //获取会话
    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }
    
    //查询所有
    public List<EmpBean> findListByHql(String hql){
        List<EmpBean> list = new ArrayList<EmpBean>();
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            list = query.list();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
}

（bean层省略）springMVC配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:mvc="http://www.springframework.org/schema/mvc"   
    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.2.xsd
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-2.5.xsd
    http://www.springframework.org/schema/mvc  
    http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">
    <!-- 注解扫描 -->
    <context:component-scan base-package="bean"></context:component-scan>
    <context:component-scan base-package="controller"></context:component-scan>
    <context:component-scan base-package="dao"></context:component-scan>
    <!-- 默认的注解映射支持(所有mvc传值都是经过这里) -->
    <mvc:annotation-driven/>
    <mvc:default-servlet-handler/>
        <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
            <property name="prefix" value="/WEB-INF/views/"></property>     <!-- 前缀 -->
            <property name="suffix" value=".jsp"></property>                <!-- 后缀 -->
            <property name="viewClass" value="org.springframework.web.servlet.view.JstlView"></property><!-- 显示标签库 -->
        </bean>
</beans>

spring配置：

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

    <context:property-placeholder location="classpath:jdbc.properties"/>
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName">
            <value>${jdbc.driverClassName}</value>
        </property>
        <property name="url">
            <value>${jdbc.url}</value>
        </property>
        <property name="username">
            <value>${jdbc.username}</value>
        </property>
        <property name="password">
            <value>${jdbc.password}</value>
        </property>
        <property name="maxActive"><!--最大存活连接数-->
            <value>50</value>
        </property>
        <property name="initialSize"><!--初始化连接数-->
            <value>5</value>
        </property>
        <property name="maxIdle"><!--最大空闲连接-->
            <value>50</value>
        </property>
        <property name="maxWait"><!--最大等待时间-->
            <value>-1</value>
        </property>
        <property name="defaultAutoCommit"><!--自动提交事务-->
            <value>false</value>
        </property>
        <property name="testWhileIdle" value="true" /> <!-- 打开检查,用异步线程evict进行检查 -->
        <property name="testOnBorrow" value="false" /> <!-- connection进行validateObject校验 BorrowObject时 -->
        <property name="testOnReturn" value="false" /> <!-- connection进行validateObject校验 ReturnObject时 -->
        <property name="validationQuery" value="select 1 from dual" /> <!-- 检查sql -->
        <!-- property name="validationQueryTimeout" value="1000" /> --> <!-- 检查sql执行超时时长 单位ms -->
        <property name="timeBetweenEvictionRunsMillis" value="30000" />  <!-- 设置的Evict线程的时间，单位ms -->
        <property name="numTestsPerEvictionRun" value="50" /> <!-- 打开检查,检查链接个数 建议是和maxActive属性值一致 -->
    </bean>

    <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" lazy-init="true" />
    <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true">
        <property name="nativeJdbcExtractor">
            <ref bean="nativeJdbcExtractor" />
        </property>
    </bean>

    <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="hibernateProperties">
            <props>
                <!-- <prop key="hibernate.hbm2ddl.auto">update</prop> 不需要创建数据库表 -->
                <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                <prop key="hibernate.show_sql">true </prop>
                <prop key="hibernate.format_sql">true</prop>
                <!-- 二级缓存 -->
                <prop key="hibernate.jdbc.batch_size">500</prop><!-- 批量删除，批量更新和批量插入的时候的批次大小 -->  
                <prop key="hibernate.cache.use_second_level_cache">false</prop>
                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
                <!--获取当前session并开启事务 -->
                <prop key="hibernate.current_session_context_class">org.springframework.orm.hibernate4.SpringSessionContext</prop>
            </props>
        </property>
        <property name="dataSource">
            <ref bean="dataSource" />
        </property>
        <property name="mappingDirectoryLocations">
            <list>
                <value>classpath:/hbm/</value>
            </list>
        </property>
        
    </bean>
    <!--  配置事务处理 -->
    <bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory"/>
    </bean>
    <tx:annotation-driven transaction-manager="txManager"/> 
 
 </beans>

jdbc.properties文件配置

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc\:oracle\:thin\:@127.0.0.1\:1521\:oracle10
jdbc.username=emp
jdbc.password=workit

web.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 
    xmlns="http://java.sun.com/xml/ns/javaee" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
  <welcome-file-list>
    <welcome-file>entrance.jsp</welcome-file>
  </welcome-file-list>
   <!-- 配置字符编码 -->
   <filter>
    <filter-name>characterEncodingFilter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
      <param-name>encoding</param-name>
      <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
      <param-name>forceencoding</param-name>
      <param-value>true</param-value>
    </init-param>
  </filter>
  
   <filter-mapping>
    <filter-name>characterEncodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>

  
  <!-- 配置springMVC -->
  <servlet>
    <servlet-name>springMVC</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/spring-conf/*</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <!-- 配置springMVC映射 -->
  <servlet-mapping>
    <servlet-name>springMVC</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
</web-app>

大概文件架构（省略了大部分项目搭建思想）