从数据库提取数据通过jstl显示在jsp页面上
1.ConnectDB.java连接数据库，把数据转换成list
public class ConnectDB {
private final static String strDriver = "oracle.jdbc.driver.OracleDriver";
private final static String strConnect = "jdbc:oracle:thin:@localhost:1521:ORCL";
private final static String strDBUserName = "scott";
private final static String strDBPWD = "tiger";
private Connection conn = null;
private Statement stat = null;
private ResultSet rs = null;
//连接oracle数据库
private void getConnectFromOracle() throws ClassNotFoundException,
SQLException {
Class.forName(strDriver);// 实例化oracle.jdbc.driver.OracleDriver
conn = DriverManager.getConnection(strConnect, strDBUserName, strDBPWD);
stat = conn.createStatement();
}
//记录基每行数据存放到值对象，然后把值对象存放一个链表
public List<EMPVO> getListWithVOFromRS() {
String sql = "select * from emp";
List<EMPVO> rowList = new LinkedList<EMPVO>();
EMPVO emp = null;
try {
getConnectFromOracle();
rs = stat.executeQuery(sql);
while (rs.next()) {
emp = new EMPVO();
// 为了简单仅取2个值
emp.setEname(rs.getString("ename"));
emp.setHiredate(rs.getString("hiredate"));
rowList.add(emp);
}
closeConnection();
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
}
return rowList;
}
//记录基每行数据存放到1个链表，然后把这个链表存放另一个链表
public List<List<String>> getListWithListFromRS() {
String sql = "select * from emp";
List<List<String>> rowList = new LinkedList<List<String>>();
List<String> colList = null;
try {
getConnectFromOracle();
rs = stat.executeQuery(sql);
int columnCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
colList = new LinkedList<String>();
for (int i = 1; i <= columnCount; i++) {
colList.add(rs.getString(i));
}
rowList.add(colList);
}
closeConnection();
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
}
return rowList;
}
//关闭数据库
public void closeConnection() throws SQLException {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
}
}
2.EMPVO.java emp表的值对象
public class EMPVO {
private String empno;
private String ename;
private String job;
private String mgr;
private String hiredate;
private String sal;
private String comm;
private String deptno;
public String getComm() {
return comm;
}
public void setComm(String comm) {
this.comm = comm;
}
public String getDeptno() {
return deptno;
}
public void setDeptno(String deptno) {
this.deptno = deptno;
}
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 getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public String getMgr() {
return mgr;
}
public void setMgr(String mgr) {
this.mgr = mgr;
}
public String getSal() {
return sal;
}
public void setSal(String sal) {
this.sal = sal;
}
}
3.JstlServlet 调用ConnectDB，把List放入request对象，跳转到jstl.jsp
public class JstlServlet extends HttpServlet {
private static final long serialVersionUID = 7129164252442979467L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
ConnectDB db = new ConnectDB();
request.setAttribute("listinlist", db.getListWithListFromRS());
request.setAttribute("voinlist", db.getListWithVOFromRS());
request.getRequestDispatcher("jstl.jsp").forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
4.jstl.jsp
<%@ page language="java" pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<table>
<tr><h1>listinlist</h1></tr>
<c:forEach items="${listinlist}" var="rowlist">
<tr>
<c:forEach items="${rowlist}" var="collist">
<td>${collist}</td>
</c:forEach>
</tr>
</c:forEach>
</table>
<table>
<tr><h1>voinlist</h1></tr>
<c:forEach items="${voinlist}" var="empvo">
<tr>
<td>${empvo.ename}</td><td>${empvo.hiredate}</td>
</tr>
</c:forEach>
</table>
</body>
</html>