一、建一个生成XML的类,参数为表名
//
/得到XML,格式如下
// <?xml version="1.0" encoding="GBK" ?>
// <NewDataSet>
// <record>
// <TITLE>The Adventures of Huckleberry Finn</TITLE>
// <AUTHOR>Mark Twain</AUTHOR>
// <BINDING>mass market paperback</BINDING>
// <ID>298</ID>
// <PRICE>$5.49</PRICE>
// </record>
// </NewDataSet>
public static String GetXML(String strTableName)
{
Connection conn = null;
StringBuffer sbtable = new StringBuffer(); //表格
StringBuffer sColumn =new StringBuffer();
try {
conn = DBConnection.getConnection();
//得到字段
PreparedStatement pColumn = conn.prepareStatement(
"select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'");
ResultSet rsColumn = pColumn.executeQuery();
//记录所显示字段的数量
int Count=0;
while(rsColumn.next())
{
sColumn.append(rsColumn.getString(1));
sColumn.append(',');
Count++;
}
//去掉最后一个,号 str为表的字段
String str=sColumn.toString().substring(0,sColumn.length()-1);
///得到数据
PreparedStatement pStat = conn.prepareStatement(
"select "+str+" from "+strTableName+" where rownum<15");
ResultSet rs = pStat.executeQuery();
StringBuffer sbTD = new StringBuffer();
//给XML加头
sbTD.append("<?xml version=/"1.0/" encoding=/"GB2312/"?>");
sbTD.append("<NewDataSet>");
while (rs.next()) {
//将每条记录的每个字段分别取出来,放到TD里
sbTD.append("<record>");
//得到字段 lsa_column是一个专门存数据列的表,自己建的
PreparedStatement pColumnXML = conn.prepareStatement(
"select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'");
ResultSet rsColumnXML = pColumnXML.executeQuery();
int n=1;
//以各字段为节点
while(rsColumnXML.next())
{
sbTD.append("<"+rsColumnXML.getString(1)+">"+rs.getString(n)+"</"+rsColumnXML.getString(1)+">");
n++;
}
sbTD.append("</record>");
}
sbTD.append("</NewDataSet>");
return sbTD.toString();
} catch (SQLException ex) {
ex.printStackTrace();
return "";
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex1) {
}
}
}
//
// <?xml version="1.0" encoding="GBK" ?>
// <NewDataSet>
// <record>
// <TITLE>The Adventures of Huckleberry Finn</TITLE>
// <AUTHOR>Mark Twain</AUTHOR>
// <BINDING>mass market paperback</BINDING>
// <ID>298</ID>
// <PRICE>$5.49</PRICE>
// </record>
// </NewDataSet>
public static String GetXML(String strTableName)
{
Connection conn = null;
StringBuffer sbtable = new StringBuffer(); //表格
StringBuffer sColumn =new StringBuffer();
try {
conn = DBConnection.getConnection();
//得到字段
PreparedStatement pColumn = conn.prepareStatement(
"select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'");
ResultSet rsColumn = pColumn.executeQuery();
//记录所显示字段的数量
int Count=0;
while(rsColumn.next())
{
sColumn.append(rsColumn.getString(1));
sColumn.append(',');
Count++;
}
//去掉最后一个,号 str为表的字段
String str=sColumn.toString().substring(0,sColumn.length()-1);
///得到数据
PreparedStatement pStat = conn.prepareStatement(
"select "+str+" from "+strTableName+" where rownum<15");
ResultSet rs = pStat.executeQuery();
StringBuffer sbTD = new StringBuffer();
//给XML加头
sbTD.append("<?xml version=/"1.0/" encoding=/"GB2312/"?>");
sbTD.append("<NewDataSet>");
while (rs.next()) {
//将每条记录的每个字段分别取出来,放到TD里
sbTD.append("<record>");
//得到字段 lsa_column是一个专门存数据列的表,自己建的
PreparedStatement pColumnXML = conn.prepareStatement(
"select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'");
ResultSet rsColumnXML = pColumnXML.executeQuery();
int n=1;
//以各字段为节点
while(rsColumnXML.next())
{
sbTD.append("<"+rsColumnXML.getString(1)+">"+rs.getString(n)+"</"+rsColumnXML.getString(1)+">");
n++;
}
sbTD.append("</record>");
}
sbTD.append("</NewDataSet>");
return sbTD.toString();
} catch (SQLException ex) {
ex.printStackTrace();
return "";
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex1) {
}
}
}
//
二、在一个JSP的页面(getXML.jsp)直接就可以调用此方法生成XML,只需要传入一个表名就可得到该表的XML
<%
@ page contentType="text/html; charset=GBK" import="bookstore.UserList"
%>
<% = UserList.GetXML(request.getParameter( " TableName " ))
%>
<% = UserList.GetXML(request.getParameter( " TableName " ))
%>
三、建一个生成表格的类,用以绑定XML
动态生成一个绑定XML数据的TABLE
//
public static String GetTableXML(String strTableName)
{
Connection conn = null;
StringBuffer sbtable = new StringBuffer(); //表格
StringBuffer sbHeader=new StringBuffer(); //表头
try
{
conn = DBConnection.getConnection();
//得到字段
String strSql="select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'";
PreparedStatement pColumn=conn.prepareStatement(strSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rsColumn = pColumn.executeQuery();
// 两种执行SQL都可以,后面的参数表示游标可以重新移动
// Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
// String sql="select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'";
// ResultSet rsColumn=stmt.executeQuery(sql);
sbtable.append("<tr>");
while(rsColumn.next())
{
sbtable.append("<td>");
sbtable.append("<div dataFld='"+rsColumn.getString(1)+"'>");
sbtable.append("</div>");
sbtable.append("</td>");
}
sbtable.append("</tr>");
//将结果集移动到第一行的前一行
rsColumn.beforeFirst();
sbHeader.append("<thead>");
sbHeader.append("<tr>");
while(rsColumn.next())
{
sbHeader.append("<td>");
sbHeader.append(rsColumn.getString(1));
sbHeader.append("</td>");
}
sbHeader.append("</tr>");
sbHeader.append("</thead>");
return sbHeader.toString()+sbtable.toString();
}
catch (SQLException ex) {
ex.printStackTrace();
return "";
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex1) {
}
}
}
/
public static String GetTableXML(String strTableName)
{
Connection conn = null;
StringBuffer sbtable = new StringBuffer(); //表格
StringBuffer sbHeader=new StringBuffer(); //表头
try
{
conn = DBConnection.getConnection();
//得到字段
String strSql="select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'";
PreparedStatement pColumn=conn.prepareStatement(strSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rsColumn = pColumn.executeQuery();
// 两种执行SQL都可以,后面的参数表示游标可以重新移动
// Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
// String sql="select columnname from lsa_column where tableename='"+strTableName+"' and isdisplay_grid='1'";
// ResultSet rsColumn=stmt.executeQuery(sql);
sbtable.append("<tr>");
while(rsColumn.next())
{
sbtable.append("<td>");
sbtable.append("<div dataFld='"+rsColumn.getString(1)+"'>");
sbtable.append("</div>");
sbtable.append("</td>");
}
sbtable.append("</tr>");
//将结果集移动到第一行的前一行
rsColumn.beforeFirst();
sbHeader.append("<thead>");
sbHeader.append("<tr>");
while(rsColumn.next())
{
sbHeader.append("<td>");
sbHeader.append(rsColumn.getString(1));
sbHeader.append("</td>");
}
sbHeader.append("</tr>");
sbHeader.append("</thead>");
return sbHeader.toString()+sbtable.toString();
}
catch (SQLException ex) {
ex.printStackTrace();
return "";
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex1) {
}
}
}
/
在testXML.jsp页面来测试
<%
@ page contentType="text/html; charset=GBK" import="bookstore.UserList"
%>
< html >
< head >
< title >
testXML
</ title >
</ head >
< body bgcolor ="#ffffff" >
<% String strTableName=request.getParameter("TableName");
%>
< XML id ="xmldso" src ="getXML.jsp?TableName=<%=strTableName%>" ></ XML >
< TABLE cellSpacing ="1" cellPadding ="1" border ="1" dataSrc ="#xmldso" >
<% = UserList.GetTableXML(request.getParameter( " TableName " )) %>
</ TABLE >
</ body >
</ html >
< html >
< head >
< title >
testXML
</ title >
</ head >
< body bgcolor ="#ffffff" >
<% String strTableName=request.getParameter("TableName");
%>
< XML id ="xmldso" src ="getXML.jsp?TableName=<%=strTableName%>" ></ XML >
< TABLE cellSpacing ="1" cellPadding ="1" border ="1" dataSrc ="#xmldso" >
<% = UserList.GetTableXML(request.getParameter( " TableName " )) %>
</ TABLE >
</ body >
</ html >