struts2连接mysql多表查询

下载地址：http://download.csdn.net/detail/qq_33599520/9786567

项目结构：

代码：

package com.mstf.action;

import java.util.List;

import com.mstf.entity.Emp;
import com.mstf.service.QueryEmpService;

public class QueryEmpAction {
	private List<Emp> posts;
	private int post_id;
	private String emp_name;

	public int getPost_id() {
		return post_id;
	}

	public void setPost_id(int post_id) {
		this.post_id = post_id;
	}

	public String getEmp_name() {
		return emp_name;
	}

	public void setEmp_name(String emp_name) {
		this.emp_name = emp_name;
	}

	public List<Emp> getPosts() {
		return posts;
	}

	public void setPosts(List<Emp> posts) {
		this.posts = posts;
	}

	/*
	 * 下拉列表控制
	 */
	public String selectEmp(){
		QueryEmpService qes = new QueryEmpService();
		posts = qes.selectEmp();
		System.out.println(posts);
		return "ok";
	}
	
	/*
	 * 查询控制
	 */
	public String selectEmployee(){
		QueryEmpService qes = new QueryEmpService();
		posts = qes.selectEmployee(emp_name, post_id);
		System.out.println(posts.size());
		return "ok";
	}
}

　　

package com.mstf.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mstf.db.DbHelper;
import com.mstf.entity.Emp;

public class EmpDao {
	
	/*
	 * 查询职位编号，职位名称
	 */
	public List<Emp> selectPost() {
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		List<Emp> list = new ArrayList<Emp>();
		try {
			conn = DbHelper.getConnection();
			ps = conn.prepareStatement("select post_id,post_name from post");
			rs = ps.executeQuery();
			while(rs.next()){
				Emp emp = new Emp();
				emp.setPost_id(rs.getInt("post_id"));
				emp.setPost_name(rs.getString("post_name"));
				list.add(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/*
	 * 根据雇员姓名和职位编号链表查询
	 * 列出：雇员编号,职位名称,雇员姓名,雇员性别,雇员年龄,所属部门,雇员工龄
	 */
	public List<Emp> selectEmployee(String emp_name, int post_id) {
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		List<Emp> list = new ArrayList<Emp>();
		try {
			conn = DbHelper.getConnection();
			ps = conn.prepareStatement("select emp_id,post_name,emp_name,emp_sex,emp_age,emp_depart,emp_year from employee e,post p where e.post_id=p.post_id and emp_name like ? and p.post_id like ?");
			ps.setString(1, emp_name);
			if(post_id==0){
				ps.setString(2, "%");
			}else{
				ps.setInt(2, post_id);
			}
			rs = ps.executeQuery();
			while(rs.next()){
				Emp emp = new Emp();
				emp.setPost_name(rs.getString("post_name"));
				emp.setEmp_id(rs.getInt("emp_id"));
				emp.setEmp_name(rs.getString("emp_name"));
				emp.setEmp_sex(rs.getString("emp_sex"));
				emp.setEmp_age(rs.getInt("emp_age"));
				emp.setEmp_depart(rs.getString("emp_depart"));
				emp.setEmp_year(rs.getInt("emp_year"));
				list.add(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}

　　

package com.mstf.db;

import java.sql.Connection;
import java.sql.DriverManager;

public class DbHelper {
	private static String url = "jdbc:mysql://127.0.0.1:3306/db_emp";  // 数据库地址
	private static String userName = "root";  // 数据库用户名
	private static String passWord = "root";  // 数据库密码
	private static Connection conn;
	
	private DbHelper(){
		
	}
	
	public static Connection getConnection(){
		if(null == conn){
			try {
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection(url, userName, passWord);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return conn;
	}
	
	public static void main(String[] args) {  // 测试数据库是否连通
		System.err.println(getConnection());
	}
}

　　

package com.mstf.entity;

public class Emp { // 实体类
	private int post_id;
	private String post_name;
	private String post_desc;
	private int emp_id;
	private String emp_name;
	private String emp_sex;
	private int emp_age;
	private String emp_depart;
	private int emp_year;
	
	public int getPost_id() {
		return post_id;
	}
	public void setPost_id(int post_id) {
		this.post_id = post_id;
	}
	public String getPost_name() {
		return post_name;
	}
	public void setPost_name(String post_name) {
		this.post_name = post_name;
	}
	public String getPost_desc() {
		return post_desc;
	}
	public void setPost_desc(String post_desc) {
		this.post_desc = post_desc;
	}
	public int getEmp_id() {
		return emp_id;
	}
	public void setEmp_id(int emp_id) {
		this.emp_id = emp_id;
	}
	public String getEmp_name() {
		return emp_name;
	}
	public void setEmp_name(String emp_name) {
		this.emp_name = emp_name;
	}
	public String getEmp_sex() {
		return emp_sex;
	}
	public void setEmp_sex(String emp_sex) {
		this.emp_sex = emp_sex;
	}
	public int getEmp_age() {
		return emp_age;
	}
	public void setEmp_age(int emp_age) {
		this.emp_age = emp_age;
	}
	public String getEmp_depart() {
		return emp_depart;
	}
	public void setEmp_depart(String emp_depart) {
		this.emp_depart = emp_depart;
	}
	public int getEmp_year() {
		return emp_year;
	}
	public void setEmp_year(int emp_year) {
		this.emp_year = emp_year;
	}
}

　　

package com.mstf.service;

import java.util.List;

import com.mstf.dao.EmpDao;
import com.mstf.entity.Emp;

public class QueryEmpService {
	
	/*
	 * 下拉列表业务层
	 */
	public List<Emp> selectEmp(){
		EmpDao empdao = new EmpDao();
		List<Emp> list = empdao.selectPost();
		return list;
	}
	
	/*
	 * 链接查询业务层
	 */
	public List<Emp> selectEmployee(String emp_name,int post_id){
		if(emp_name.equals("")){
			emp_name = "%";
		}
		EmpDao empdao = new EmpDao();
		List<Emp> list = empdao.selectEmployee(emp_name, post_id);
		return list;
	}
}

　　

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC
        "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
        "http://struts.apache.org/dtds/struts-2.3.dtd">

<struts>
	<!-- 热部署 -->
	<constant name="struts.devMode" value="true"></constant>
	<!-- 指定语言 -->
	<constant name="struts.locale" value="zh_CN"></constant>
	<!-- 指定编码 -->
	<constant name="struts.i18n.encoding" value="utf-8"></constant>

    <constant name="struts.enable.DynamicMethodInvocation" value="true"/>
	<package name="mstf" extends="struts-default" namespace="/">
		<action name="show" class="com.mstf.action.QueryEmpAction" method="selectEmp">
			<result name="ok">index.jsp</result>
		</action>
		<action name="showEmp" class="com.mstf.action.QueryEmpAction" method="selectEmployee">
			<result name="ok">show.jsp</result>
		</action>
	</package>

</struts>

　　

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" 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_3_0.xsd">

    <!-- 过滤器 用于初始化struts2 -->
    <filter>
        <filter-name>struts2</filter-name>
        <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
    </filter>

    <!-- 用于struts2 的过滤器映射 -->
    <filter-mapping>
        <filter-name>struts2</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

</web-app>

　　

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>雇员查询</title>
</head>
<body>
	<h1 align="center">雇员查询</h1>
	<form action="showEmp" method="post">
		<table align="center" border="1">
			<tr>
				<td>
					雇员姓名：
					<input type="text" name="emp_name">
				</td>
			</tr>
			<tr>
				<td>
					公司职位：
					<select name="post_id">
						<option value="0">请选择：</option>
							<c:forEach items="${posts}" var="emp">
								<option value="${emp.post_id}">${emp.post_name}</option>
							</c:forEach>
					</select>
				</td>
			</tr>
			<tr>
				<td>
					查询雇员明细：
					<input type="submit" name="submit" value="查询雇员明细">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

　　

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询信息显示</title>
</head>
<body>
	<table align="center" border="1">
		<tr>
			<td>雇员编号</td>
			<td>雇员职位</td>
			<td>雇员姓名</td>
			<td>雇员年龄</td>
			<td>所属部门</td>
			<td>雇员工龄</td>
		</tr>
		<c:forEach items="${posts}" var="emp">
			<tr>
				<td>${emp.emp_id}</td>
				<td>${emp.post_name}</td>
				<td>${emp.emp_name}</td>
				<td>${emp.emp_age}</td>
				<td>${emp.emp_depart}</td>
				<td>${emp.emp_year}</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>