JDBC与EL实现数据库数据在前端页面查询(版本一)
进入JSP的EL表达式环节,结合JDBC,将数据库资料在前端页面查询。
为了不啰唆,就直接上代码了,也方便以后阅读。
第一部分:新建一个Dynamic Web Project,比如:
我就简单用一个MVC来命名了,你们随意!
第二部分:在lib文件夹导入jar包
归档里面都有:添加链接描述
第三部分:创建数据库配置文件(jdbc.properties)
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/factory
jdbc.username=root
jdbc.password=ps123456
第四部分:
/**
*
*/
package cn.pk.data;
import java.io.IOException;
import java.util.Properties;
import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;
public class MyDbUtils {
public static BasicDataSource bds=new BasicDataSource();
static {
Properties p=new Properties();
try {
p.load(MyDbUtils.class.getResourceAsStream("/jdbc.properties"));
bds.setDriverClassName(p.getProperty("jdbc.driver"));
bds.setUrl(p.getProperty("jdbc.url"));
bds.setUsername(p.getProperty("jdbc.username"));
bds.setPassword(p.getProperty("jdbc.password"));
} catch (IOException e) {
e.printStackTrace();
}
}
}
第五部分:要查询的数据表属性类(Emp.java)
package cn.pk.entity;
public class Emp {
private String empno;
private String ename;
private String deptno;
private int sal;
private String job;
public String getEmpno() {
return empno;
}
public void setEmpno(String empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getDeptno() {
return deptno;
}
public void setDeptno(String deptno) {
this.deptno = deptno;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
}
第六JSP部分:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!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>Insert title here</title>
<style type="text/css">
table tr td{
border:1px solid black;
}
</style>
</head>
<body>
<form action="${pageContext.request.contextPath}/listEmp">
雇员姓名:<input name="ename"/><input type='submit' value="查询"/>
</form>
<table style="width:80%">
<tr>
<td>雇员编号</td>
<td>雇员姓名</td>
<td>雇员薪水</td>
<td>雇员职位</td>
</tr>
<c:forEach var="tt" items="${requestScope.empList}">
<tr>
<td>${tt.empno}</td>
<td>${tt.ename}</td>
<td>${tt.sal}</td>
<td>${tt.job }</td>
</tr>
</c:forEach>
</table>
</body>
</html>
最后一部分:
package cn.pk.controller;
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 org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import cn.pk.data.MyDbUtils;
import cn.pk.entity.Emp;
/**
* Servlet implementation class EmpSerlvet
*/
@WebServlet(urlPatterns="/listEmp")
public class EmpSerlvet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String ename = request.getParameter("ename");
QueryRunner qr=new QueryRunner(MyDbUtils.bds);
try {
String sql="select * from newemp";
if(StringUtils.isNotEmpty(ename)) {
sql+=" where ename like '%"+ename+"%'";
}
List<Emp> empList = (List<Emp>)qr.query(sql,new BeanListHandler(Emp.class));
request.setAttribute("empList", empList);
request.getRequestDispatcher("/emp.jsp").forward(request, response);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
输出效果如图:
后续可能还会优化代码,不足之处,谅解~~~