JDBC与EL实现数据库数据在前端页面查询（版本一）

骑猪看日落 2022-05-11 12:08 197阅读 0赞

进入JSP的EL表达式环节，结合JDBC,将数据库资料在前端页面查询。  
为了不啰唆，就直接上代码了，也方便以后阅读。  
**第一部分：新建一个Dynamic Web Project，比如：**![在这里插入图片描述][70]  
我就简单用一个MVC来命名了，你们随意！

**第二部分：在lib文件夹导入jar包**  
归档里面都有：[添加链接描述][Link 1]  
**第三部分：创建数据库配置文件**（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();
    		}
    
    	}
    }

输出效果如图：  
![在这里插入图片描述][70 1]  
后续可能还会优化代码，不足之处，谅解~~~

[70]: /images/20220505/039621ef94bb44d1b751ea08d0b89856.png
[Link 1]: http://archive.apache.org/dist/
[70 1]: /images/20220505/d134d209085f496d9bdbdfd52ea28d14.png