简单的查询

##简单的查询
项目结构图

##index页面
`<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8”%>

<h1>XXXX点餐系统</h1>
<form action="${pageContext.request.contextPath }/LoginServlet" method="post" onsubmit=" return login(this)">
     <p>用户名：<input  type="text" name="username"> 
     <p>密码：<input  type="password" name="password" >
     <p><input type="reset"  value="重置">
    	<input type="submit"  value="登录">
 </form>

`

##home页面

<%@ page language=“java” contentType=“text/html; charset=UTF-8” pageEncoding=“UTF-8”%>
<%@ taglib uri=“http://java.sun.com/jsp/jstl/core” prefix=“c” %>
<%@ taglib uri=“http://java.sun.com/jsp/jstl/functions” prefix=“fn” %>

添加菜品

<form action="${pageContext.request.contextPath }/FindByFoodNameServlet" method="post">
	<input type="text" name="foodname">
	<input type="submit" value="查询">
</form>
<table class="table table-striped" >
	<tr>
		<td>序号</td>
		<td>菜名</td>
		<td>菜品说明</td>		
	</tr>
	<c:forEach items="${requestScope.menuList}" var="m" varStatus="status">
	<tr>
		<td>${status.count}</td>
		<td>${m.foodname}</td>
		<td>${m.explain}</td>
	</tr>
	</c:forEach>
</table>

##dao 包中的MenumerationDao.java
package dao;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import domain.Menu;
import utils.DataSourceUtils;

public class MenuDao {
/**
* 查询所有数据
* @throws SQLException
*/
public List

findAllMenuInFo() throws SQLException{
String sql = “SELECT * FROM MENU”;
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
List menuList = runner.query(sql, new BeanListHandler (Menu.class));
return menuList;
}

/**
 * 通过菜品名来查询数据
 */
public List<Menu> findByFoodName(String foodname) throws SQLException{
	String sql = "SELECT * FROM MENU WHERE foodname = ?";
	QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
	List<Menu> findByFoodNameMenu = runner.query(sql, new BeanListHandler<Menu>(Menu.class),foodname);
	return findByFoodNameMenu;
}

}

##domain包中的Menu.java
package domain;

/**

• 菜单表的实例化
• @author

*/

public class Menu {
private int id;
private String foodname;
private String explain;

@Override
public String toString() {
	return "Menu [id=" + id + ", foodname=" + foodname + ", explain=" + explain + "]";
}
public int getId() {
	return id;
}
public void setId(int id) {
	this.id = id;
}
public String getFoodname() {
	return foodname;
}
public void setFoodname(String foodname) {
	this.foodname = foodname;
}
public String getExplain() {
	return explain;
}
public void setExplain(String explain) {
	this.explain = explain;
}

}

##servlet 包中的servlet文件
##FindByFoodNameServlet.java
package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.MenuDao;
import domain.Menu;

/**

• Servlet implementation class FindByFoodNameServlet
  */
  @WebServlet("/FindByFoodNameServlet")
  public class FindByFoodNameServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  /**

  • @see HttpServlet#HttpServlet()
    */
    public FindByFoodNameServlet() {
    super();
    // TODO Auto-generated constructor stub
    }

  /**

  • @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    //设置编码类型
    request.setCharacterEncoding(“UTF-8”);
    String foodname = request.getParameter(“foodname”);
    MenuDao menuDao = new MenuDao();
    List

    menuList = null;
    try {
    menuList = menuDao.findByFoodName(foodname);
    request.setAttribute(“menuList”, menuList);
    request.getRequestDispatcher(“home.jsp”).forward(request, response);
    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    // TODO Auto-generated method stub
    response.getWriter().append("Served at: ").append(request.getContextPath());
    }

  /**

  • @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
    }

}

##LoginServlet.java
package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.MenuDao;
import domain.Menu;

/**

• Servlet implementation class LoginServlet
  */
  @WebServlet("/LoginServlet")
  public class LoginServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  /**

  • @see HttpServlet#HttpServlet()
    */
    public LoginServlet() {
    super();
    // TODO Auto-generated constructor stub
    }

  /**

  • @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    MenuDao menuDao = new MenuDao();

     List<Menu> menuList = null;
     try {
     	menuList = menuDao.findAllMenuInFo();
     } catch (SQLException e) {
     	// TODO Auto-generated catch block
     	e.printStackTrace();
     }
     request.setAttribute("menuList", menuList);
     request.getRequestDispatcher("home.jsp").forward(request, response);;
    

    // TODO Auto-generated method stub
    response.getWriter().append("Served at: ").append(request.getContextPath());
    }

  /**

  • @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
    }

}

##utils包中的文件
##DataSourceUtils.java
package utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceUtils {

private static DataSource dataSource = new ComboPooledDataSource();

private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

// 直接可以获取一个连接池
public static DataSource getDataSource() {
	return dataSource;
}

// 获取连接对象
public static Connection getConnection() throws SQLException {

	Connection con = tl.get();
	if (con == null) {
		con = dataSource.getConnection();
		tl.set(con);
	}
	return con;
}

// 开启事务
public static void startTransaction() throws SQLException {
	Connection con = getConnection();
	if (con != null) {
		con.setAutoCommit(false);
	}
}

// 事务回滚
public static void rollback() throws SQLException {
	Connection con = getConnection();
	if (con != null) {
		con.rollback();
	}
}

// 提交并且 关闭资源及从ThreadLocall中释放
public static void commitAndRelease() throws SQLException {
	Connection con = getConnection();
	if (con != null) {
		con.commit(); // 事务提交
		con.close();// 关闭资源
		tl.remove();// 从线程绑定中移除
	}
}

// 关闭资源方法
public static void closeConnection() throws SQLException {
	Connection con = getConnection();
	if (con != null) {
		con.close();
	}
}

public static void closeStatement(Statement st) throws SQLException {
	if (st != null) {
		st.close();
	}
}

public static void closeResultSet(ResultSet rs) throws SQLException {
	if (rs != null) {
		rs.close();
	}
}

}

##JDBCUtils.java
package utils;
/*

• 实现JDBC的工具类
• 定义方法，直接返回数据库的连接对象
• 关闭方法
  */
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;

import javax.management.RuntimeErrorException;
public class JDBCUtils {
private JDBCUtils(){}
private static Connection con ;

static{
	try{
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/demo";
		String username="root";
		String password="root";
		con = DriverManager.getConnection(url, username, password);
	}catch(Exception ex){
		throw new RuntimeException(ex+"数据库连接失败");
	}
}

/*
 * 定义静态方法,返回数据库的连接对象
 */
public static Connection getConnection(){
	return con;
}

public static void close(Connection con,Statement stat,ResultSet rs){
	if(rs!=null){
		try{
			rs.close();
		}catch(SQLException ex){}
	}
	
	if(stat!=null){
		try{
			stat.close();
		}catch(SQLException ex){}
	}
	
	if(con!=null){
		try{
			con.close();
		}catch(SQLException ex){}
	}
}
public static void close(Connection con,Statement stat){
	
	if(stat!=null){
		try{
			stat.close();
		}catch(SQLException ex){}
	}
	
	if(con!=null){
		try{
			con.close();
		}catch(SQLException ex){}
	}
}

}

##c3p0-comfig.xml

<?xml version="1.0" encoding="UTF-8"?> root aisino123 com.mysql.jdbc.Driver jdbc:mysql:///demo

##lib里的包