目标:在jsp页面上通过自定义标签,查询数据库。
package org.vv.taglib;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.jsp.JspTagException;
import javax.servlet.jsp.tagext.TagSupport;
public class QueryTag extends TagSupport {
private static final long serialVersionUID = 1L;
// 标签的属性
private String driver;
private String url;
private String user;
private String password;
private String sql;
// 执行数据库访问的对象
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
private ResultSetMetaData rsmd = null;
// 标签属性 driver url user password sql 的getter/setter方法
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
// 标签处理
public int doEndTag() throws JspTagException {
// 注册驱动
try {
Class.forName(driver);
// 获取数据库连接
conn = DriverManager.getConnection(url, user, password);
// 创建Statement对象
stmt = conn.createStatement();
// 执行查询
rs = stmt.executeQuery(sql);
rsmd = rs.getMetaData();
// 获取列数目
int columnCount = rsmd.getColumnCount();
// 获取页面输出流
Writer out = pageContext.getOut();
// 在页面输出表格
out.write("<table border='1' bgColor='9999cc'>");
// 遍历结果集
out.write("<tr>");
for (int i=0;i<rs.getMetaData().getColumnCount();i++){
out.write("<td>");
out.write(rs.getMetaData().getColumnName(i+1));
out.write("</td>");
}
out.write("</tr>");
while (rs.next()) {
out.write("<tr>");
// 逐列输出查询到的数据
for (int i = 1; i <= columnCount; i++) {
out.write("<td>");
out.write(rs.getString(i));
out.write("</td>");
}
out.write("</tr>");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
destroy();
}
return EVAL_PAGE;
}
// 销毁标签前调用的方法
public void destroy() {
// 关闭结果集
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭Statement
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭数据库连接
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
在 WebRoot\WEB-INF 目录下添加文件 query.tld
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE taglib PUBLIC "-//Sun Microsystems, Inc.//DTD JSP Tag Library 1.2//EN" "http://java.sun.com/dtd/web-jsptaglibrary_1_2.dtd" > <taglib> <tlib-version>1.0</tlib-version> <jsp-version>2.0</jsp-version> <short-name>query</short-name> <tag> <!-- 配置标签名 --> <name>query</name> <!-- 配置标签处理类 --> <tag-class>org.vv.taglib.QueryTag</tag-class> <!-- 配置标签体为空 --> <body-content>empty</body-content> <!-- 配置标签属性 driver --> <attribute> <name>driver</name> <required>true</required> <rtexprvalue>true</rtexprvalue> </attribute> <!-- 配置标签属性 url --> <attribute> <name>url</name> <required>true</required> <rtexprvalue>true</rtexprvalue> </attribute> <!-- 配置标签属性 user --> <attribute> <name>user</name> <required>true</required> <rtexprvalue>true</rtexprvalue> </attribute> <!-- 配置标签属性 password --> <attribute> <name>password</name> <required>true</required> <rtexprvalue>true</rtexprvalue> </attribute> <!-- 配置标签属性 sql --> <attribute> <name>sql</name> <required>true</required> <rtexprvalue>true</rtexprvalue> </attribute> </tag> </taglib>
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.4" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <jsp-config> <taglib> <taglib-uri>query</taglib-uri> <taglib-location>/WEB-INF/query.tld</taglib-location> </taglib> </jsp-config> </web-app>
jsp 页面中的调用方法
<myquery:query driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"
url="jdbc:microsoft:sqlserver://192.168.0.88:1433;DatabaseName=directorMail"
user="user" password="password" sql="select * from mailInfo" />