众所周知XML已经成不同应用程序之间数据交换的事实上的标准。在实际工作中,我们经常需要把JDBC返回的结果集(ResultSet)转化为XML表达形式,便于把数据传送到其他的应用程序。这里提供一个简单的例子,它可以把ResultSet转化为XML格式的文本,并存放在字符串(String)作为返回结果。
这个程序通用之处在于它与选用的数据库结构无关。就是说,如果数据库结构发生了变化,本文提供的程序也可以正确运行。如果你有相同的需要,希望本文能给您一点帮助和启发。
XMLWriter.Java
import java.sql.*;
import java.io.*;
public class XMLWriter {
/**
* @param ResultSet rs输入的结果集
* @return String 返回XML串
* @exception SQLException
*/
public String generateXML(final ResultSet rs) throws SQLException {
final StringBuffer buffer = new StringBuffer(1024 * 4);
if (rs == null) return "";
if (!rs.next()) return "";
buffer.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"); //XML的头部信息
buffer.append("<ResultSet>\n");
ResultSetMetaData rsmd = rs.getMetaData(); //得到结果集的定义结构
int colCount = rsmd.getColumnCount(); //得到列的总数
for (int id = 0; rs.next(); id++) { // 对放回的全部数据逐一处理
//格式为row id , col name, col context
buffer.append("\t<row id=\"").append(id).append("\">\n");
for (int i = 1; i <= colCount; i++) {
int type = rsmd.getColumnType(i); //获取字段类型
buffer.append("\t\t<col name=\"" + rsmd.getColumnName(i) + "\">");
buffer.append(getValue(rs, i, type));
buffer.append("</col>\n");
}
buffer.append("\t</row>\n");
}
buffer.append("</RowSet>");
rs.close();
return buffer.toString();
}
/**
* This method gets the value of the specified column
* 通用的读取结果集某一列的值并转化为String表达
* @param ResultSet rs 输入的纪录集
* @param int colNum 第几列
* @param int type 数据类型
*/
private String getValue(final ResultSet rs, int colNum, int type) throws SQLException {
switch (type) {
case Types.ARRAY:
case Types.BLOB:
case Types.CLOB:
case Types.DISTINCT:
case Types.LONGVARBINARY:
case Types.VARBINARY:
case Types.BINARY:
case Types.REF:
case Types.STRUCT:
return "undefined";
default: {
Object value = rs.getObject(colNum);
if (rs.wasNull() || (value == null))
return ("null");
else
return (value.toString());
}
}
}
//测试例程
public static void main (String args [])
throws SQLException, IOException
{
String user="odbc";
String password=null;
// Load the Mysql JDBC driver
DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
Connection conn =
DriverManager.getConnection ("jdbc:mysql://localhost/bank");
System.out.println ("connected.");
// Create a statement
Statement stmt = conn.createStatement ();
// Do the SQL "Hello World" thing
System.out.println("here is the table rows view");
ResultSet rset = stmt.executeQuery ("select * from userinfo");
while (rset.next ()) {
System.out.println (rset.getString (1)); //W?username
System.out.println (rset.getString (2)); //W?password
};
// call toxml function
System.out.println("here is the xml output");
rset.close();
rset = stmt.executeQuery ("select * from userinfo");
String xmlstring =(new Jdbcxml()).generateXML(rset);
System.out.println(xmlstring);
rset.close();
stmt.close();
conn.close();
}
}
好大功告成,如果一切顺利,便可以看看我们的执行效果了
connected.
here is the table rows view 数据库中的纪录
ariso
asdfjuojkhjkasdf9089sd89zxd9f
estoy
asdfjuojkhjkasdf9089sd89zxd9f
estoy
asdfjuojkhjkasdf9089sd89zxd9f
ariso
asdfjuojkhjkasdf9089sd89zxd9f
here is the xml output 可以看到XML输出效果如下
<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
<row id="0">
<col name="username">estoy</col>
<col name="password">asdfjuojkhjkasdf9089sd89zxd9f</col>
</row>
<row id="1">
<col name="username">estoy</col>
<col name="password">asdfjuojkhjkasdf9089sd89zxd9f</col>
</row>
<row id="2">
<col name="username">ariso</col>
<col name="password">asdfjuojkhjkasdf9089sd89zxd9f</col>
</row>
</RowSet>
这个程序通用之处在于它与选用的数据库结构无关。就是说,如果数据库结构发生了变化,本文提供的程序也可以正确运行。如果你有相同的需要,希望本文能给您一点帮助和启发。
XMLWriter.Java
import java.sql.*;
import java.io.*;
public class XMLWriter {
/**
* @param ResultSet rs输入的结果集
* @return String 返回XML串
* @exception SQLException
*/
public String generateXML(final ResultSet rs) throws SQLException {
final StringBuffer buffer = new StringBuffer(1024 * 4);
if (rs == null) return "";
if (!rs.next()) return "";
buffer.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"); //XML的头部信息
buffer.append("<ResultSet>\n");
ResultSetMetaData rsmd = rs.getMetaData(); //得到结果集的定义结构
int colCount = rsmd.getColumnCount(); //得到列的总数
for (int id = 0; rs.next(); id++) { // 对放回的全部数据逐一处理
//格式为row id , col name, col context
buffer.append("\t<row id=\"").append(id).append("\">\n");
for (int i = 1; i <= colCount; i++) {
int type = rsmd.getColumnType(i); //获取字段类型
buffer.append("\t\t<col name=\"" + rsmd.getColumnName(i) + "\">");
buffer.append(getValue(rs, i, type));
buffer.append("</col>\n");
}
buffer.append("\t</row>\n");
}
buffer.append("</RowSet>");
rs.close();
return buffer.toString();
}
/**
* This method gets the value of the specified column
* 通用的读取结果集某一列的值并转化为String表达
* @param ResultSet rs 输入的纪录集
* @param int colNum 第几列
* @param int type 数据类型
*/
private String getValue(final ResultSet rs, int colNum, int type) throws SQLException {
switch (type) {
case Types.ARRAY:
case Types.BLOB:
case Types.CLOB:
case Types.DISTINCT:
case Types.LONGVARBINARY:
case Types.VARBINARY:
case Types.BINARY:
case Types.REF:
case Types.STRUCT:
return "undefined";
default: {
Object value = rs.getObject(colNum);
if (rs.wasNull() || (value == null))
return ("null");
else
return (value.toString());
}
}
}
//测试例程
public static void main (String args [])
throws SQLException, IOException
{
String user="odbc";
String password=null;
// Load the Mysql JDBC driver
DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
Connection conn =
DriverManager.getConnection ("jdbc:mysql://localhost/bank");
System.out.println ("connected.");
// Create a statement
Statement stmt = conn.createStatement ();
// Do the SQL "Hello World" thing
System.out.println("here is the table rows view");
ResultSet rset = stmt.executeQuery ("select * from userinfo");
while (rset.next ()) {
System.out.println (rset.getString (1)); //W?username
System.out.println (rset.getString (2)); //W?password
};
// call toxml function
System.out.println("here is the xml output");
rset.close();
rset = stmt.executeQuery ("select * from userinfo");
String xmlstring =(new Jdbcxml()).generateXML(rset);
System.out.println(xmlstring);
rset.close();
stmt.close();
conn.close();
}
}
好大功告成,如果一切顺利,便可以看看我们的执行效果了
connected.
here is the table rows view 数据库中的纪录
ariso
asdfjuojkhjkasdf9089sd89zxd9f
estoy
asdfjuojkhjkasdf9089sd89zxd9f
estoy
asdfjuojkhjkasdf9089sd89zxd9f
ariso
asdfjuojkhjkasdf9089sd89zxd9f
here is the xml output 可以看到XML输出效果如下
<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
<row id="0">
<col name="username">estoy</col>
<col name="password">asdfjuojkhjkasdf9089sd89zxd9f</col>
</row>
<row id="1">
<col name="username">estoy</col>
<col name="password">asdfjuojkhjkasdf9089sd89zxd9f</col>
</row>
<row id="2">
<col name="username">ariso</col>
<col name="password">asdfjuojkhjkasdf9089sd89zxd9f</col>
</row>
</RowSet>