Java/Database SQL JDBC/ResultSet
Содержание
- 1 Concurrency in ResultSet
- 2 Convert a ResultSet to XML
- 3 Decorates a ResultSet with checks for a SQL NULL value on each getXXX method.
- 4 Demonstrate simple use of the CachedRowSet
- 5 Get available ResultSet types
- 6 Get BLOB data from resultset
- 7 Get Column Count In ResultSet
- 8 Metadata for ResultSet
- 9 Output data from table
- 10 Print ResultSet in HTML
- 11 Results Decorator SQL
- 12 Results Decorator Text
- 13 Results Decorator XML
- 14 ResultSet getter Methods
- 15 ResultSet Update
- 16 ResultSet Update Methods
- 17 Retrieve a rowcount from a ResultSet
- 18 Scrollable ResultSet
- 19 SQL statement: ResultSet and ResultSetMetaData
- 20 Wraps a ResultSet in an Iterator.
- 21 Wraps a ResultSet to trim strings returned by the getString() and getObject() methods.
Concurrency in ResultSet
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class TypeConcurrency {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT * FROM COFFEES");
int type = srs.getType();
System.out.println("srs is type " + type);
int concur = srs.getConcurrency();
System.out.println("srs has concurrency " + concur);
while (srs.next()) {
String name = srs.getString("COF_NAME");
int id = srs.getInt("SUP_ID");
float price = srs.getFloat("PRICE");
int sales = srs.getInt("SALES");
int total = srs.getInt("TOTAL");
System.out.print(name + " " + id + " " + price);
System.out.println(" " + sales + " " + total);
}
srs.close();
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("-----SQLException-----");
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}
Convert a ResultSet to XML
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class Main {
public static void main(String args[]) throws Exception {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();
Element results = doc.createElement("Results");
doc.appendChild(results);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager
.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/access.mdb");
ResultSet rs = con.createStatement().executeQuery("select * from product");
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
while (rs.next()) {
Element row = doc.createElement("Row");
results.appendChild(row);
for (int i = 1; i <= colCount; i++) {
String columnName = rsmd.getColumnName(i);
Object value = rs.getObject(i);
Element node = doc.createElement(columnName);
node.appendChild(doc.createTextNode(value.toString()));
row.appendChild(node);
}
}
DOMSource domSource = new DOMSource(doc);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer transformer = tf.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
transformer.setOutputProperty(OutputKeys.METHOD, "xml");
transformer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");
StringWriter sw = new StringWriter();
StreamResult sr = new StreamResult(sw);
transformer.transform(domSource, sr);
System.out.println(sw.toString());
con.close();
rs.close();
}
}
Decorates a ResultSet with checks for a SQL NULL value on each getXXX method.
import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Date;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;
/**
* Decorates a <code>ResultSet</code> with checks for a SQL NULL value on each
* <code>getXXX</code> method. If a column value obtained by a
* <code>getXXX</code> method is not SQL NULL, the column value is returned.
* If the column value is SQL null, an alternate value is returned. The
* alternate value defaults to the Java <code>null</code> value, which can be
* overridden for instances of the class.
*
* <p>
* Usage example: <blockquote>
*
* <pre>
* Connection conn = // somehow get a connection
* Statement stmt = conn.createStatement();
* ResultSet rs = stmt.executeQuery("SELECT col1, col2 FROM table1");
*
* // Wrap the result set for SQL NULL checking
* SqlNullCheckedResultSet wrapper = new SqlNullCheckedResultSet(rs);
* wrapper.setNullString("---N/A---"); // Set null string
* wrapper.setNullInt(-999); // Set null integer
* rs = ProxyFactory.instance().createResultSet(wrapper);
*
* while (rs.next()) {
* // If col1 is SQL NULL, value returned will be "---N/A---"
* String col1 = rs.getString("col1");
* // If col2 is SQL NULL, value returned will be -999
* int col2 = rs.getInt("col2");
* }
* rs.close();
* </pre>
*
* </blockquote>
* </p>
* <p>
* Unlike some other classes in DbUtils, this class is NOT thread-safe.
* </p>
*/
public class SqlNullCheckedResultSet implements InvocationHandler {
/**
* Maps normal method names (ie. "getBigDecimal") to the corresponding null
* Method object (ie. getNullBigDecimal).
*/
private static final Map nullMethods = new HashMap();
static {
Method[] methods = SqlNullCheckedResultSet.class.getMethods();
for (int i = 0; i < methods.length; i++) {
String methodName = methods[i].getName();
if (methodName.startsWith("getNull")) {
String normalName = "get" + methodName.substring(7);
nullMethods.put(normalName, methods[i]);
}
}
}
private InputStream nullAsciiStream = null;
private BigDecimal nullBigDecimal = null;
private InputStream nullBinaryStream = null;
private Blob nullBlob = null;
private boolean nullBoolean = false;
private byte nullByte = 0;
private byte[] nullBytes = null;
private Reader nullCharacterStream = null;
private Clob nullClob = null;
private Date nullDate = null;
private double nullDouble = 0.0;
private float nullFloat = 0.0f;
private int nullInt = 0;
private long nullLong = 0;
private Object nullObject = null;
private Ref nullRef = null;
private short nullShort = 0;
private String nullString = null;
private Time nullTime = null;
private Timestamp nullTimestamp = null;
private URL nullURL = null;
/**
* The wrapped result.
*/
private final ResultSet rs;
/**
* Constructs a new instance of <code>SqlNullCheckedResultSet</code> to wrap
* the specified <code>ResultSet</code>.
*
* @param rs
* ResultSet to wrap
*/
public SqlNullCheckedResultSet(ResultSet rs) {
super();
this.rs = rs;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getAsciiStream</code> method.
*
* @return the value
*/
public InputStream getNullAsciiStream() {
return this.nullAsciiStream;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getBigDecimal</code> method.
*
* @return the value
*/
public BigDecimal getNullBigDecimal() {
return this.nullBigDecimal;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getBinaryStream</code> method.
*
* @return the value
*/
public InputStream getNullBinaryStream() {
return this.nullBinaryStream;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getBlob</code> method.
*
* @return the value
*/
public Blob getNullBlob() {
return this.nullBlob;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getBoolean</code> method.
*
* @return the value
*/
public boolean getNullBoolean() {
return this.nullBoolean;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getByte</code> method.
*
* @return the value
*/
public byte getNullByte() {
return this.nullByte;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getBytes</code> method.
*
* @return the value
*/
public byte[] getNullBytes() {
return this.nullBytes;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getCharacterStream</code> method.
*
* @return the value
*/
public Reader getNullCharacterStream() {
return this.nullCharacterStream;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getClob</code> method.
*
* @return the value
*/
public Clob getNullClob() {
return this.nullClob;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getDate</code> method.
*
* @return the value
*/
public Date getNullDate() {
return this.nullDate;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getDouble</code> method.
*
* @return the value
*/
public double getNullDouble() {
return this.nullDouble;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getFloat</code> method.
*
* @return the value
*/
public float getNullFloat() {
return this.nullFloat;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getInt</code> method.
*
* @return the value
*/
public int getNullInt() {
return this.nullInt;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getLong</code> method.
*
* @return the value
*/
public long getNullLong() {
return this.nullLong;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getObject</code> method.
*
* @return the value
*/
public Object getNullObject() {
return this.nullObject;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getRef</code> method.
*
* @return the value
*/
public Ref getNullRef() {
return this.nullRef;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getShort</code> method.
*
* @return the value
*/
public short getNullShort() {
return this.nullShort;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getString</code> method.
*
* @return the value
*/
public String getNullString() {
return this.nullString;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getTime</code> method.
*
* @return the value
*/
public Time getNullTime() {
return this.nullTime;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getTimestamp</code> method.
*
* @return the value
*/
public Timestamp getNullTimestamp() {
return this.nullTimestamp;
}
/**
* Returns the value when a SQL null is encountered as the result of invoking
* a <code>getURL</code> method.
*
* @return the value
*/
public URL getNullURL() {
return this.nullURL;
}
/**
* Intercepts calls to <code>get*</code> methods and calls the appropriate
* <code>getNull*</code> method if the <code>ResultSet</code> returned
* <code>null</code>.
*
* @throws Throwable
* @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object,
* java.lang.reflect.Method, java.lang.Object[])
*/
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
Object result = method.invoke(this.rs, args);
Method nullMethod = (Method) nullMethods.get(method.getName());
// Check nullMethod != null first so that we don"t call wasNull()
// before a true getter method was invoked on the ResultSet.
return (nullMethod != null && this.rs.wasNull()) ? nullMethod.invoke(this, (Object[]) null)
: result;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getAsciiStream</code> method.
*
* @param nullAsciiStream
* the value
*/
public void setNullAsciiStream(InputStream nullAsciiStream) {
this.nullAsciiStream = nullAsciiStream;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getBigDecimal</code> method.
*
* @param nullBigDecimal
* the value
*/
public void setNullBigDecimal(BigDecimal nullBigDecimal) {
this.nullBigDecimal = nullBigDecimal;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getBinaryStream</code> method.
*
* @param nullBinaryStream
* the value
*/
public void setNullBinaryStream(InputStream nullBinaryStream) {
this.nullBinaryStream = nullBinaryStream;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getBlob</code> method.
*
* @param nullBlob
* the value
*/
public void setNullBlob(Blob nullBlob) {
this.nullBlob = nullBlob;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getBoolean</code> method.
*
* @param nullBoolean
* the value
*/
public void setNullBoolean(boolean nullBoolean) {
this.nullBoolean = nullBoolean;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getByte</code> method.
*
* @param nullByte
* the value
*/
public void setNullByte(byte nullByte) {
this.nullByte = nullByte;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getBytes</code> method.
*
* @param nullBytes
* the value
*/
public void setNullBytes(byte[] nullBytes) {
this.nullBytes = nullBytes;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getCharacterStream</code> method.
*
* @param nullCharacterStream
* the value
*/
public void setNullCharacterStream(Reader nullCharacterStream) {
this.nullCharacterStream = nullCharacterStream;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getClob</code> method.
*
* @param nullClob
* the value
*/
public void setNullClob(Clob nullClob) {
this.nullClob = nullClob;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getDate</code> method.
*
* @param nullDate
* the value
*/
public void setNullDate(Date nullDate) {
this.nullDate = nullDate;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getDouble</code> method.
*
* @param nullDouble
* the value
*/
public void setNullDouble(double nullDouble) {
this.nullDouble = nullDouble;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getFloat</code> method.
*
* @param nullFloat
* the value
*/
public void setNullFloat(float nullFloat) {
this.nullFloat = nullFloat;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getInt</code> method.
*
* @param nullInt
* the value
*/
public void setNullInt(int nullInt) {
this.nullInt = nullInt;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getLong</code> method.
*
* @param nullLong
* the value
*/
public void setNullLong(long nullLong) {
this.nullLong = nullLong;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getObject</code> method.
*
* @param nullObject
* the value
*/
public void setNullObject(Object nullObject) {
this.nullObject = nullObject;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getRef</code> method.
*
* @param nullRef
* the value
*/
public void setNullRef(Ref nullRef) {
this.nullRef = nullRef;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getShort</code> method.
*
* @param nullShort
* the value
*/
public void setNullShort(short nullShort) {
this.nullShort = nullShort;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getString</code> method.
*
* @param nullString
* the value
*/
public void setNullString(String nullString) {
this.nullString = nullString;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getTime</code> method.
*
* @param nullTime
* the value
*/
public void setNullTime(Time nullTime) {
this.nullTime = nullTime;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getTimestamp</code> method.
*
* @param nullTimestamp
* the value
*/
public void setNullTimestamp(Timestamp nullTimestamp) {
this.nullTimestamp = nullTimestamp;
}
/**
* Sets the value to return when a SQL null is encountered as the result of
* invoking a <code>getURL</code> method.
*
* @param nullURL
* the value
*/
public void setNullURL(URL nullURL) {
this.nullURL = nullURL;
}
}
Demonstrate simple use of the CachedRowSet
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import javax.sql.rowset.CachedRowSet;
/**
* Demonstrate simple use of the CachedRowSet. The RowSet family of interfaces
* is in JDK1.5, but the Implementation classes are (as of Beta 1) still in the
* unsupported "com.sun" package.
*/
public class CachedRowSetDemo {
public static final String ROWSET_IMPL_CLASS = "com.sun.rowset.CachedRowSetImpl";
public static void main(String[] args) throws Exception {
CachedRowSet rs;
// Create the class with class.forName to avoid importing
// from the unsupported com.sun packages.
Class c = Class.forName(ROWSET_IMPL_CLASS);
rs = (CachedRowSet) c.newInstance();
rs.setUrl("jdbc:postgresql:tmclub");
rs.setUsername("ian");
rs.setPassword("secret");
rs.setCommand("select * from members where name like ?");
rs.setString(1, "I%");
// This will cause the RowSet to connect, fetch its data, and
// disconnect
rs.execute();
// Some time later, the client tries to do something.
// Suppose we want to update data:
while (rs.next()) {
if (rs.getInt("id") == 42) {
rs.setString(1, "Marvin");
rs.updateRow(); // Normal JDBC
// This additional call tells the CachedRowSet to connect
// to its database and send the updated data back.
rs.acceptChanges();
}
}
// If we"re all done...
rs.close();
}
}
Get available ResultSet types
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getMySqlConnection();
System.out.println("Got Connection.");
Statement st = conn.createStatement();
st.executeUpdate("drop table survey;");
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
DatabaseMetaData meta = conn.getMetaData();
if (meta.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)) {
System.out.println("type name=TYPE_FORWARD_ONLY");
}
if (meta.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)) {
System.out.println("type name=TYPE_SCROLL_INSENSITIVE");
}
if (meta.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) {
System.out.println("type name=TYPE_SCROLL_SENSITIVE");
}
st.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:caspian";
String username = "mp";
String password = "mp2";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Get BLOB data from resultset
/*
Defining the Table: Oracle and MySql
create table MyPictures (
id INT PRIMARY KEY,
name VARCHAR(0),
photo BLOB
);
*/
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JPanel;
public class BlobSelect extends JPanel {
public static void main(String args[]) throws Exception {
Connection conn = null;
byte[] data = getBLOB(01, conn);
}
public static byte[] getBLOB(int id, Connection conn) throws Exception {
ResultSet rs = null;
PreparedStatement pstmt = null;
String query = "SELECT photo FROM MyPictures WHERE id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
rs.next();
Blob blob = rs.getBlob("photo");
// materialize BLOB onto client
return blob.getBytes(1, (int) blob.length());
} finally {
rs.close();
pstmt.close();
conn.close();
}
}
}
Get Column Count In ResultSet
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getMySqlConnection();
System.out.println("Got Connection.");
Statement st = conn.createStatement();
st.executeUpdate("drop table survey;");
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
System.out.println("resultSet MetaData column Count=" + numberOfColumns);
st.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:caspian";
String username = "mp";
String password = "mp2";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Metadata for ResultSet
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class RSMetaDataMethods {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from COFFEES");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
for (int i = 1; i <= numberOfColumns; i++) {
String colName = rsmd.getColumnName(i);
String tableName = rsmd.getTableName(i);
String name = rsmd.getColumnTypeName(i);
boolean caseSen = rsmd.isCaseSensitive(i);
boolean writable = rsmd.isWritable(i);
System.out.println("Information for column " + colName);
System.out.println(" Column is in table " + tableName);
System.out.println(" DBMS name for type is " + name);
System.out.println(" Is case sensitive: " + caseSen);
System.out.println(" Is possibly writable: " + writable);
System.out.println("");
}
while (rs.next()) {
for (int i = 1; i<=numberOfColumns; i++) {
String s = rs.getString(i);
System.out.print(s + " ");
}
System.out.println("");
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
Output data from table
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class PrintColumns {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
String query = "select * from COFFEES";
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
PrintColumnTypes.printColTypes(rsmd);
System.out.println("");
int numberOfColumns = rsmd.getColumnCount();
for (int i = 1; i <= numberOfColumns; i++) {
if (i > 1) System.out.print(", ");
String columnName = rsmd.getColumnName(i);
System.out.print(columnName);
}
System.out.println("");
while (rs.next()) {
for (int i = 1; i <= numberOfColumns; i++) {
if (i > 1) System.out.print(", ");
String columnValue = rs.getString(i);
System.out.print(columnValue);
}
System.out.println("");
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
}
}
class PrintColumnTypes {
public static void printColTypes(ResultSetMetaData rsmd)
throws SQLException {
int columns = rsmd.getColumnCount();
for (int i = 1; i <= columns; i++) {
int jdbcType = rsmd.getColumnType(i);
String name = rsmd.getColumnTypeName(i);
System.out.print("Column " + i + " is JDBC type " + jdbcType);
System.out.println(", which the DBMS calls " + name);
}
}
}
Print ResultSet in HTML
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/** Print ResultSet in HTML
*/
public class ResultsDecoratorHTML extends ResultsDecorator {
ResultsDecoratorHTML(ResultsDecoratorPrinter out) {
super(out);
}
public void write(ResultSet rs) throws IOException, SQLException {
ResultSetMetaData md = rs.getMetaData();
int count = md.getColumnCount();
println("<table border=1>");
print("<tr>");
for (int i=1; i<=count; i++) {
print("<th>");
print(md.getColumnLabel(i));
}
println("</tr>");
while (rs.next()) {
print("<tr>");
for (int i=1; i<=count; i++) {
print("<td>");
print(rs.getString(i));
}
println("</tr>");
}
println("</table>");
}
/* (non-Javadoc)
* @see ResultSetDecorator#write(int)
*/
void write(int updateCount) throws IOException {
println("<p>RowCount: updateCount = <b>" +
updateCount + "</p>");
}
/** Return a printable name for this decorator
* @see ResultsDecorator#getName()
*/
String getName() {
return "HTML";
}
}
/**
* Base class for a series of ResultSet printers.
*
* @version $Id: ResultsDecorator.java,v 1.2 2004/03/26 02:39:33 ian Exp $
*/
abstract class ResultsDecorator {
ResultsDecoratorPrinter parent;
ResultsDecorator(ResultsDecoratorPrinter wr) {
this.parent = wr;
}
/** Print the name of this Decorator"s output format */
abstract String getName();
/** Print the contents of a ResultSet */
abstract void write(ResultSet rs) throws IOException, SQLException;
/** Print the results of an operation as a Count */
abstract void write(int rowCount) throws IOException;
void println(String line) throws IOException {
parent.println(line);
}
void println() throws IOException {
parent.println();
}
void print(String lineSeg) throws IOException {
parent.print(lineSeg);
}
}
/**
* Callback so that ResultsDecorator can call invoker to handle redirections
* etc.
*
* @version $Id: ResultsDecoratorPrinter.java,v 1.1 2004/03/26 02:39:33 ian Exp $
*/
interface ResultsDecoratorPrinter {
void print(String line) throws IOException;
void println(String line) throws IOException;
void println() throws IOException;
PrintWriter getPrintWriter();
}
Results Decorator SQL
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
/**
* Print an SQL ResultSet in SQL-import format. TODO: check all escaped
* characters needed! Test on PGSQL and DB2 at least...
*
* @version $Id: ResultsDecoratorSQL.java,v 1.2 2004/03/26 02:39:33 ian Exp $
*/
public class ResultsDecoratorSQL extends ResultsDecorator {
ResultsDecoratorSQL(ResultsDecoratorPrinter out) {
super(out);
}
public void write(ResultSet rs) throws IOException, SQLException {
ResultSetMetaData md = rs.getMetaData();
// This assumes you"re not using a Join!!
String tableName = md.getTableName(1);
int cols = md.getColumnCount();
StringBuffer sb = new StringBuffer("insert into ").append(tableName)
.append("(");
for (int i = 1; i <= cols; i++) {
sb.append(md.getColumnName(i));
if (i != cols) {
sb.append(", ");
}
}
sb.append(") values (");
String insertCommand = sb.toString();
while (rs.next()) {
println(insertCommand);
for (int i = 1; i <= cols; i++) {
String tmp = rs.getString(i);
if (rs.wasNull()) {
print("null");
} else {
int type = md.getColumnType(i);
// Don"t quote numeric types; quote all others for now.
switch (type) {
case Types.BIGINT:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.INTEGER:
print(tmp);
break;
default:
tmp = tmp.replaceAll(""", """");
print(""" + tmp + """);
}
}
if (i != cols) {
print(", ");
}
}
println(");");
}
}
void write(int rowCount) throws IOException {
println("RowCount: " + rowCount);
}
/*
* (non-Javadoc)
*
* @see ResultsDecorator#getName()
*/
String getName() {
return "SQL";
}
}
/**
* Base class for a series of ResultSet printers.
*
* @version $Id: ResultsDecorator.java,v 1.2 2004/03/26 02:39:33 ian Exp $
*/
abstract class ResultsDecorator {
ResultsDecoratorPrinter parent;
ResultsDecorator(ResultsDecoratorPrinter wr) {
this.parent = wr;
}
/** Print the name of this Decorator"s output format */
abstract String getName();
/** Print the contents of a ResultSet */
abstract void write(ResultSet rs) throws IOException, SQLException;
/** Print the results of an operation as a Count */
abstract void write(int rowCount) throws IOException;
void println(String line) throws IOException {
parent.println(line);
}
void println() throws IOException {
parent.println();
}
void print(String lineSeg) throws IOException {
parent.print(lineSeg);
}
}
/**
* Callback so that ResultsDecorator can call invoker to handle redirections
* etc.
*
* @version $Id: ResultsDecoratorPrinter.java,v 1.1 2004/03/26 02:39:33 ian Exp $
*/
interface ResultsDecoratorPrinter {
void print(String line) throws IOException;
void println(String line) throws IOException;
void println() throws IOException;
PrintWriter getPrintWriter();
}
Results Decorator Text
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/**
* Print a ResultSet in plain text.
*
* @version $Id: ResultsDecoratorText.java,v 1.3 2004/03/26 02:39:33 ian Exp $
*/
public class ResultsDecoratorText extends ResultsDecorator {
ResultsDecoratorText(ResultsDecoratorPrinter pt) {
super(pt);
}
public void write(ResultSet rs) throws IOException, SQLException {
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
for (int i = 1; i <= cols; i++) {
print(md.getColumnName(i) + "\t");
}
println();
while (rs.next()) {
for (int i = 1; i <= cols; i++) {
print(rs.getString(i) + "\t");
}
println();
}
}
void write(int rowCount) throws IOException {
println("OK: " + rowCount);
}
/*
* (non-Javadoc)
*
* @see ResultsDecorator#getName()
*/
String getName() {
return "Plain text";
}
}
/**
* Base class for a series of ResultSet printers.
*
* @version $Id: ResultsDecorator.java,v 1.2 2004/03/26 02:39:33 ian Exp $
*/
abstract class ResultsDecorator {
ResultsDecoratorPrinter parent;
ResultsDecorator(ResultsDecoratorPrinter wr) {
this.parent = wr;
}
/** Print the name of this Decorator"s output format */
abstract String getName();
/** Print the contents of a ResultSet */
abstract void write(ResultSet rs) throws IOException, SQLException;
/** Print the results of an operation as a Count */
abstract void write(int rowCount) throws IOException;
void println(String line) throws IOException {
parent.println(line);
}
void println() throws IOException {
parent.println();
}
void print(String lineSeg) throws IOException {
parent.print(lineSeg);
}
}
/**
* Callback so that ResultsDecorator can call invoker to handle redirections
* etc.
*
* @version $Id: ResultsDecoratorPrinter.java,v 1.1 2004/03/26 02:39:33 ian Exp $
*/
interface ResultsDecoratorPrinter {
void print(String line) throws IOException;
void println(String line) throws IOException;
void println() throws IOException;
PrintWriter getPrintWriter();
}
Results Decorator XML
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.rowset.WebRowSet;
/**
* This guy"s primary raison d"etre is to generate an XML output file for use in
* JUnit testing of the ResultsDecoratorSQL!
*
* @version $Id: ResultsDecoratorXML.java,v 1.1 2004/03/26 02:39:33 ian Exp $
*/
public class ResultsDecoratorXML extends ResultsDecorator {
WebRowSet results;
ResultsDecoratorXML(ResultsDecoratorPrinter out) {
super(out);
try {
// The name is com.sun.rowset.WebRowSetImpl in rowset.jar,
// but sun.rowset.jar in J2SDK1.5. Go figure.
Class c = Class.forName("com.sun.rowset.WebRowSetImpl");
results = (WebRowSet) c.newInstance();
} catch (Exception ex) {
throw new IllegalArgumentException(
"can"t load WebRowSetImpl, check CLASSPATH");
}
}
public void write(ResultSet rs) throws SQLException {
results.writeXml(rs, parent.getPrintWriter());
}
void write(int rowCount) throws IOException {
println("RowCount: " + rowCount);
}
/*
* (non-Javadoc)
*
* @see ResultsDecorator#getName()
*/
String getName() {
return "XML";
}
}
/**
* Base class for a series of ResultSet printers.
*
* @version $Id: ResultsDecorator.java,v 1.2 2004/03/26 02:39:33 ian Exp $
*/
abstract class ResultsDecorator {
ResultsDecoratorPrinter parent;
ResultsDecorator(ResultsDecoratorPrinter wr) {
this.parent = wr;
}
/** Print the name of this Decorator"s output format */
abstract String getName();
/** Print the contents of a ResultSet */
abstract void write(ResultSet rs) throws IOException, SQLException;
/** Print the results of an operation as a Count */
abstract void write(int rowCount) throws IOException;
void println(String line) throws IOException {
parent.println(line);
}
void println() throws IOException {
parent.println();
}
void print(String lineSeg) throws IOException {
parent.print(lineSeg);
}
}
/**
* Callback so that ResultsDecorator can call invoker to handle redirections
* etc.
*
* @version $Id: ResultsDecoratorPrinter.java,v 1.1 2004/03/26 02:39:33 ian Exp $
*/
interface ResultsDecoratorPrinter {
void print(String line) throws IOException;
void println(String line) throws IOException;
void println() throws IOException;
PrintWriter getPrintWriter();
}
ResultSet getter Methods
Data Type Method
BigDecimal getBigDecimal(String columnName, int scale)
boolean getBoolean(String columnName)
byte getByte(String columnName)
byte[] getBytes(String columnName)
double getDouble(String columnName)
float getFloat(String columnName)
int getInt(String columnName)
java.io.InputStream getAsciiStream(String columnName)
java.io.InputStream getUnicodeStream(String columnName)
java.io.InputStream getBinaryStream(String columnName)
java.sql.Date getDate(String columnName)
java.sql.Time getTime(String columnName)
java.sql.Timestamp getTimestamp(String columnName)
long getLong(String columnName)
Object getObject(String columnName)
short getShort(String columnName)
String getString(String columnName)
ResultSet Update
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002. All rights
* reserved. Software written by Ian F. Darwin and others. $Id: LICENSE,v 1.8
* 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
* 1. Redistributions of source code must retain the above copyright notice,
* this list of conditions and the following disclaimer. 2. Redistributions in
* binary form must reproduce the above copyright notice, this list of
* conditions and the following disclaimer in the documentation and/or other
* materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS"" AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee cup"
* logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetUpdate {
public static void main(String args[]) {
String url;
url = "jdbc:odbc:UserDB";
String user, pass;
user = "ian";
pass = "stjklsq";
Connection con;
Statement stmt;
ResultSet rs;
try {
con = DriverManager.getConnection(url, user, pass);
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT * FROM Users where nick=\"ian\"");
// Get the resultset ready, update the passwd field, commit
rs.first();
rs.updateString("password", "unguessable");
rs.updateRow();
rs.close();
stmt.close();
con.close();
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
ResultSet Update Methods
Data Type Method
BigDecimal updateBigDecimal(String columnName, BigDecimal x)
boolean updateBoolean(String columnName, boolean x)
byte updateByte(String columnName, byte x)
byte[] updateBytes(String columnName, byte[] x)
double updateDouble(String columnName, double x)
float updateFloat(String columnName, float x)
int updateInt(String columnName, int x)
java.io.InputStream updateAsciiStream(String columnName, InputStream x, int length)
java.io.InputStream updateUnicodeStream(String columnName, InputStream x, int length)
java.io.InputStream updateBinaryStream(String columnName, InputStream x, int length)
java.sql.Date updateDate(String columnName, Date x)
java.sql.Time updateTime(String columnName, Time x)
java.sql.Timestamp updateTimestamp(String columnName, Timestamp x)
long updateLong(String columnName, long x)
Object updateObject(String columnName, Object x)
Object updateObject(String columnName, Object x, int scale)
short updateShort(String columnName, short x)
String updateString(String columnName, String x)
NULL updateNull(String columnName)
Retrieve a rowcount from a ResultSet
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] argv) throws Exception {
Connection conn = null;
Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet r = s
.executeQuery("SELECT * FROM employee");
r.last();
int count = r.getRow();
r.beforeFirst();
}
}
Scrollable ResultSet
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class ScrollableResultSet {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT * FROM COFFEES");
srs.absolute(4);
int rowNum = srs.getRow(); // rowNum should be 4
System.out.println("rowNum should be 4 " + rowNum);
srs.relative(-3);
rowNum = srs.getRow(); // rowNum should be 1
System.out.println("rowNum should be 1 " + rowNum);
srs.relative(2);
rowNum = srs.getRow(); // rowNum should be 3
System.out.println("rowNum should be 3 " + rowNum);
srs.absolute(1);
System.out.println("after last? " + srs.isAfterLast() );
if (!srs.isAfterLast()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
srs.close();
stmt.close();
con.close();
} catch(BatchUpdateException b) {
System.err.println("-----BatchUpdateException-----");
System.err.println("SQLState: " + b.getSQLState());
System.err.println("Message: " + b.getMessage());
System.err.println("Vendor: " + b.getErrorCode());
System.err.print("Update counts: ");
int [] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
System.out.println("");
} catch(SQLException ex) {
System.err.println("-----SQLException-----");
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}
SQL statement: ResultSet and ResultSetMetaData
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class SQLStatement {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from COFFEES, SUPPLIERS " +
"where SUPPLIERS.SUP_NAME like "Acme, Inc." and " +
"SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
int rowCount = 1;
while (rs.next()) {
System.out.println("Row " + rowCount + ": ");
for (int i = 1; i <= numberOfColumns; i++) {
System.out.print(" Column " + i + ": ");
System.out.println(rs.getString(i));
}
System.out.println("");
rowCount++;
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
}
}
Wraps a ResultSet in an Iterator.
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* <p>
* Wraps a <code>ResultSet</code> in an <code>Iterator</code>. This is useful
* when you want to present a non-database application layer with domain
* neutral data.
* </p>
*
* <p>
* This implementation requires the <code>ResultSet.isLast()</code> method
* to be implemented.
* </p>
*/
public class ResultSetIterator implements Iterator {
/**
* The wrapped <code>ResultSet</code>.
*/
private final ResultSet rs;
/**
* The processor to use when converting a row into an Object[].
*/
private final RowProcessor convert;
/**
* Constructor for ResultSetIterator.
* @param rs Wrap this <code>ResultSet</code> in an <code>Iterator</code>.
*/
public ResultSetIterator(ResultSet rs) {
this(rs , new BasicRowProcessor());
}
/**
* Constructor for ResultSetIterator.
* @param rs Wrap this <code>ResultSet</code> in an <code>Iterator</code>.
* @param convert The processor to use when converting a row into an
* <code>Object[]</code>. Defaults to a
* <code>BasicRowProcessor</code>.
*/
public ResultSetIterator(ResultSet rs, RowProcessor convert) {
this.rs = rs;
this.convert = convert;
}
/**
* Returns true if there are more rows in the ResultSet.
* @return boolean <code>true</code> if there are more rows
* @throws RuntimeException if an SQLException occurs.
*/
public boolean hasNext() {
try {
return !rs.isLast();
} catch (SQLException e) {
rethrow(e);
return false;
}
}
/**
* Returns the next row as an <code>Object[]</code>.
* @return An <code>Object[]</code> with the same number of elements as
* columns in the <code>ResultSet</code>.
* @see java.util.Iterator#next()
* @throws RuntimeException if an SQLException occurs.
*/
public Object next() {
try {
rs.next();
return this.convert.toArray(rs);
} catch (SQLException e) {
rethrow(e);
return null;
}
}
/**
* Deletes the current row from the <code>ResultSet</code>.
* @see java.util.Iterator#remove()
* @throws RuntimeException if an SQLException occurs.
*/
public void remove() {
try {
this.rs.deleteRow();
} catch (SQLException e) {
rethrow(e);
}
}
/**
* Rethrow the SQLException as a RuntimeException. This implementation
* creates a new RuntimeException with the SQLException"s error message.
* @param e SQLException to rethrow
* @since DbUtils 1.1
*/
protected void rethrow(SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
/**
* <code>RowProcessor</code> implementations convert
* <code>ResultSet</code> rows into various other objects. Implementations
* can extend <code>BasicRowProcessor</code> to protect themselves
* from changes to this interface.
*
* @see BasicRowProcessor
*/
interface RowProcessor {
/**
* Create an <code>Object[]</code> from the column values in one
* <code>ResultSet</code> row. The <code>ResultSet</code> should be
* positioned on a valid row before passing it to this method.
* Implementations of this method must not alter the row position of
* the <code>ResultSet</code>.
*
* @param rs ResultSet that supplies the array data
* @throws SQLException if a database access error occurs
* @return the newly created array
*/
public Object[] toArray(ResultSet rs) throws SQLException;
/**
* Create a JavaBean from the column values in one <code>ResultSet</code>
* row. The <code>ResultSet</code> should be positioned on a valid row before
* passing it to this method. Implementations of this method must not
* alter the row position of the <code>ResultSet</code>.
*
* @param rs ResultSet that supplies the bean data
* @param type Class from which to create the bean instance
* @throws SQLException if a database access error occurs
* @return the newly created bean
*/
public Object toBean(ResultSet rs, Class type) throws SQLException;
/**
* Create a <code>List</code> of JavaBeans from the column values in all
* <code>ResultSet</code> rows. <code>ResultSet.next()</code> should
* <strong>not</strong> be called before passing it to this method.
*
* @param rs ResultSet that supplies the bean data
* @param type Class from which to create the bean instance
* @throws SQLException if a database access error occurs
* @return A <code>List</code> of beans with the given type in the order
* they were returned by the <code>ResultSet</code>.
*/
public List toBeanList(ResultSet rs, Class type) throws SQLException;
/**
* Create a <code>Map</code> from the column values in one
* <code>ResultSet</code> row. The <code>ResultSet</code> should be
* positioned on a valid row before
* passing it to this method. Implementations of this method must not
* alter the row position of the <code>ResultSet</code>.
*
* @param rs ResultSet that supplies the map data
* @throws SQLException if a database access error occurs
* @return the newly created Map
*/
public Map toMap(ResultSet rs) throws SQLException;
}
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/**
* Basic implementation of the <code>RowProcessor</code> interface.
*
* <p>
* This class is thread-safe.
* </p>
*
* @see RowProcessor
*/
class BasicRowProcessor implements RowProcessor {
/**
* The default BeanProcessor instance to use if not supplied in the
* constructor.
*/
private static final BeanProcessor defaultConvert = new BeanProcessor();
/**
* The Singleton instance of this class.
*/
private static final BasicRowProcessor instance = new BasicRowProcessor();
/**
* Returns the Singleton instance of this class.
*
* @return The single instance of this class.
* @deprecated Create instances with the constructors instead. This will
* be removed after DbUtils 1.1.
*/
public static BasicRowProcessor instance() {
return instance;
}
/**
* Use this to process beans.
*/
private final BeanProcessor convert;
/**
* BasicRowProcessor constructor. Bean processing defaults to a
* BeanProcessor instance.
*/
public BasicRowProcessor() {
this(defaultConvert);
}
/**
* BasicRowProcessor constructor.
* @param convert The BeanProcessor to use when converting columns to
* bean properties.
* @since DbUtils 1.1
*/
public BasicRowProcessor(BeanProcessor convert) {
super();
this.convert = convert;
}
/**
* Convert a <code>ResultSet</code> row into an <code>Object[]</code>.
* This implementation copies column values into the array in the same
* order they"re returned from the <code>ResultSet</code>. Array elements
* will be set to <code>null</code> if the column was SQL NULL.
*
* @see org.apache.rumons.dbutils.RowProcessor#toArray(java.sql.ResultSet)
*/
public Object[] toArray(ResultSet rs) throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++) {
result[i] = rs.getObject(i + 1);
}
return result;
}
/**
* Convert a <code>ResultSet</code> row into a JavaBean. This
* implementation delegates to a BeanProcessor instance.
* @see org.apache.rumons.dbutils.RowProcessor#toBean(java.sql.ResultSet, java.lang.Class)
* @see org.apache.rumons.dbutils.BeanProcessor#toBean(java.sql.ResultSet, java.lang.Class)
*/
public Object toBean(ResultSet rs, Class type) throws SQLException {
return this.convert.toBean(rs, type);
}
/**
* Convert a <code>ResultSet</code> into a <code>List</code> of JavaBeans.
* This implementation delegates to a BeanProcessor instance.
* @see org.apache.rumons.dbutils.RowProcessor#toBeanList(java.sql.ResultSet, java.lang.Class)
* @see org.apache.rumons.dbutils.BeanProcessor#toBeanList(java.sql.ResultSet, java.lang.Class)
*/
public List toBeanList(ResultSet rs, Class type) throws SQLException {
return this.convert.toBeanList(rs, type);
}
/**
* Convert a <code>ResultSet</code> row into a <code>Map</code>. This
* implementation returns a <code>Map</code> with case insensitive column
* names as keys. Calls to <code>map.get("COL")</code> and
* <code>map.get("col")</code> return the same value.
* @see org.apache.rumons.dbutils.RowProcessor#toMap(java.sql.ResultSet)
*/
public Map toMap(ResultSet rs) throws SQLException {
Map result = new CaseInsensitiveHashMap();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
for (int i = 1; i <= cols; i++) {
result.put(rsmd.getColumnName(i), rs.getObject(i));
}
return result;
}
/**
* A Map that converts all keys to lowercase Strings for case insensitive
* lookups. This is needed for the toMap() implementation because
* databases don"t consistenly handle the casing of column names.
*
* <p>The keys are stored as they are given [BUG #DBUTILS-34], so we maintain
* an internal mapping from lowercase keys to the real keys in order to
* achieve the case insensitive lookup.
*
* <p>Note: This implementation does not allow <tt>null</tt>
* for key, whereas {@link HashMap} does, because of the code:
* <pre>
* key.toString().toLowerCase()
* </pre>
*/
private static class CaseInsensitiveHashMap extends HashMap {
/**
* The internal mapping from lowercase keys to the real keys.
*
* <p>
* Any query operation using the key
* ({@link #get(Object)}, {@link #containsKey(Object)})
* is done in three steps:
* <ul>
* <li>convert the parameter key to lower case</li>
* <li>get the actual key that corresponds to the lower case key</li>
* <li>query the map with the actual key</li>
* </ul>
* </p>
*/
private final Map lowerCaseMap = new HashMap();
/**
* Required for serialization support.
*
* @see java.io.Serializable
*/
private static final long serialVersionUID = 1841673097701957808L;
/**
* @see java.util.Map#containsKey(java.lang.Object)
*/
public boolean containsKey(Object key) {
Object realKey = lowerCaseMap.get(key.toString().toLowerCase());
return super.containsKey(realKey);
// Possible optimisation here:
// Since the lowerCaseMap contains a mapping for all the keys,
// we could just do this:
// return lowerCaseMap.containsKey(key.toString().toLowerCase());
}
/**
* @see java.util.Map#get(java.lang.Object)
*/
public Object get(Object key) {
Object realKey = lowerCaseMap.get(key.toString().toLowerCase());
return super.get(realKey);
}
/**
* @see java.util.Map#put(java.lang.Object, java.lang.Object)
*/
public Object put(Object key, Object value) {
/*
* In order to keep the map and lowerCaseMap synchronized,
* we have to remove the old mapping before putting the
* new one. Indeed, oldKey and key are not necessaliry equals.
* (That"s why we call super.remove(oldKey) and not just
* super.put(key, value))
*/
Object oldKey = lowerCaseMap.put(key.toString().toLowerCase(), key);
Object oldValue = super.remove(oldKey);
super.put(key, value);
return oldValue;
}
/**
* @see java.util.Map#putAll(java.util.Map)
*/
public void putAll(Map m) {
Iterator iter = m.entrySet().iterator();
while (iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next();
Object key = entry.getKey();
Object value = entry.getValue();
this.put(key, value);
}
}
/**
* @see java.util.Map#remove(java.lang.Object)
*/
public Object remove(Object key) {
Object realKey = lowerCaseMap.remove(key.toString().toLowerCase());
return super.remove(realKey);
}
}
}
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/**
* <p>
* <code>BeanProcessor</code> matches column names to bean property names
* and converts <code>ResultSet</code> columns into objects for those bean
* properties. Subclasses should override the methods in the processing chain
* to customize behavior.
* </p>
*
* <p>
* This class is thread-safe.
* </p>
*
* @see BasicRowProcessor
*
* @since DbUtils 1.1
*/
class BeanProcessor {
/**
* Special array value used by <code>mapColumnsToProperties</code> that
* indicates there is no bean property that matches a column from a
* <code>ResultSet</code>.
*/
protected static final int PROPERTY_NOT_FOUND = -1;
/**
* Set a bean"s primitive properties to these defaults when SQL NULL
* is returned. These are the same as the defaults that ResultSet get*
* methods return in the event of a NULL column.
*/
private static final Map primitiveDefaults = new HashMap();
static {
primitiveDefaults.put(Integer.TYPE, new Integer(0));
primitiveDefaults.put(Short.TYPE, new Short((short) 0));
primitiveDefaults.put(Byte.TYPE, new Byte((byte) 0));
primitiveDefaults.put(Float.TYPE, new Float(0));
primitiveDefaults.put(Double.TYPE, new Double(0));
primitiveDefaults.put(Long.TYPE, new Long(0));
primitiveDefaults.put(Boolean.TYPE, Boolean.FALSE);
primitiveDefaults.put(Character.TYPE, new Character("\u0000"));
}
/**
* Constructor for BeanProcessor.
*/
public BeanProcessor() {
super();
}
/**
* Convert a <code>ResultSet</code> row into a JavaBean. This
* implementation uses reflection and <code>BeanInfo</code> classes to
* match column names to bean property names. Properties are matched to
* columns based on several factors:
* <br/>
* <ol>
* <li>
* The class has a writable property with the same name as a column.
* The name comparison is case insensitive.
* </li>
*
* <li>
* The column type can be converted to the property"s set method
* parameter type with a ResultSet.get* method. If the conversion fails
* (ie. the property was an int and the column was a Timestamp) an
* SQLException is thrown.
* </li>
* </ol>
*
* <p>
* Primitive bean properties are set to their defaults when SQL NULL is
* returned from the <code>ResultSet</code>. Numeric fields are set to 0
* and booleans are set to false. Object bean properties are set to
* <code>null</code> when SQL NULL is returned. This is the same behavior
* as the <code>ResultSet</code> get* methods.
* </p>
*
* @param rs ResultSet that supplies the bean data
* @param type Class from which to create the bean instance
* @throws SQLException if a database access error occurs
* @return the newly created bean
*/
public Object toBean(ResultSet rs, Class type) throws SQLException {
PropertyDescriptor[] props = this.propertyDescriptors(type);
ResultSetMetaData rsmd = rs.getMetaData();
int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);
return this.createBean(rs, type, props, columnToProperty);
}
/**
* Convert a <code>ResultSet</code> into a <code>List</code> of JavaBeans.
* This implementation uses reflection and <code>BeanInfo</code> classes to
* match column names to bean property names. Properties are matched to
* columns based on several factors:
* <br/>
* <ol>
* <li>
* The class has a writable property with the same name as a column.
* The name comparison is case insensitive.
* </li>
*
* <li>
* The column type can be converted to the property"s set method
* parameter type with a ResultSet.get* method. If the conversion fails
* (ie. the property was an int and the column was a Timestamp) an
* SQLException is thrown.
* </li>
* </ol>
*
* <p>
* Primitive bean properties are set to their defaults when SQL NULL is
* returned from the <code>ResultSet</code>. Numeric fields are set to 0
* and booleans are set to false. Object bean properties are set to
* <code>null</code> when SQL NULL is returned. This is the same behavior
* as the <code>ResultSet</code> get* methods.
* </p>
*
* @param rs ResultSet that supplies the bean data
* @param type Class from which to create the bean instance
* @throws SQLException if a database access error occurs
* @return the newly created List of beans
*/
public List toBeanList(ResultSet rs, Class type) throws SQLException {
List results = new ArrayList();
if (!rs.next()) {
return results;
}
PropertyDescriptor[] props = this.propertyDescriptors(type);
ResultSetMetaData rsmd = rs.getMetaData();
int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);
do {
results.add(this.createBean(rs, type, props, columnToProperty));
} while (rs.next());
return results;
}
/**
* Creates a new object and initializes its fields from the ResultSet.
*
* @param rs The result set.
* @param type The bean type (the return type of the object).
* @param props The property descriptors.
* @param columnToProperty The column indices in the result set.
* @return An initialized object.
* @throws SQLException if a database error occurs.
*/
private Object createBean(ResultSet rs, Class type,
PropertyDescriptor[] props, int[] columnToProperty)
throws SQLException {
Object bean = this.newInstance(type);
for (int i = 1; i < columnToProperty.length; i++) {
if (columnToProperty[i] == PROPERTY_NOT_FOUND) {
continue;
}
PropertyDescriptor prop = props[columnToProperty[i]];
Class propType = prop.getPropertyType();
Object value = this.processColumn(rs, i, propType);
if (propType != null && value == null && propType.isPrimitive()) {
value = primitiveDefaults.get(propType);
}
this.callSetter(bean, prop, value);
}
return bean;
}
/**
* Calls the setter method on the target object for the given property.
* If no setter method exists for the property, this method does nothing.
* @param target The object to set the property on.
* @param prop The property to set.
* @param value The value to pass into the setter.
* @throws SQLException if an error occurs setting the property.
*/
private void callSetter(Object target, PropertyDescriptor prop, Object value)
throws SQLException {
Method setter = prop.getWriteMethod();
if (setter == null) {
return;
}
Class[] params = setter.getParameterTypes();
try {
// convert types for some popular ones
if (value != null) {
if (value instanceof java.util.Date) {
if (params[0].getName().equals("java.sql.Date")) {
value = new java.sql.Date(((java.util.Date) value).getTime());
} else
if (params[0].getName().equals("java.sql.Time")) {
value = new java.sql.Time(((java.util.Date) value).getTime());
} else
if (params[0].getName().equals("java.sql.Timestamp")) {
value = new java.sql.Timestamp(((java.util.Date) value).getTime());
}
}
}
// Don"t call setter if the value object isn"t the right type
if (this.isCompatibleType(value, params[0])) {
setter.invoke(target, new Object[] { value });
} else {
throw new SQLException(
"Cannot set " + prop.getName() + ": incompatible types.");
}
} catch (IllegalArgumentException e) {
throw new SQLException(
"Cannot set " + prop.getName() + ": " + e.getMessage());
} catch (IllegalAccessException e) {
throw new SQLException(
"Cannot set " + prop.getName() + ": " + e.getMessage());
} catch (InvocationTargetException e) {
throw new SQLException(
"Cannot set " + prop.getName() + ": " + e.getMessage());
}
}
/**
* ResultSet.getObject() returns an Integer object for an INT column. The
* setter method for the property might take an Integer or a primitive int.
* This method returns true if the value can be successfully passed into
* the setter method. Remember, Method.invoke() handles the unwrapping
* of Integer into an int.
*
* @param value The value to be passed into the setter method.
* @param type The setter"s parameter type.
* @return boolean True if the value is compatible.
*/
private boolean isCompatibleType(Object value, Class type) {
// Do object check first, then primitives
if (value == null || type.isInstance(value)) {
return true;
} else if (
type.equals(Integer.TYPE) && Integer.class.isInstance(value)) {
return true;
} else if (type.equals(Long.TYPE) && Long.class.isInstance(value)) {
return true;
} else if (
type.equals(Double.TYPE) && Double.class.isInstance(value)) {
return true;
} else if (type.equals(Float.TYPE) && Float.class.isInstance(value)) {
return true;
} else if (type.equals(Short.TYPE) && Short.class.isInstance(value)) {
return true;
} else if (type.equals(Byte.TYPE) && Byte.class.isInstance(value)) {
return true;
} else if (
type.equals(Character.TYPE) && Character.class.isInstance(value)) {
return true;
} else if (
type.equals(Boolean.TYPE) && Boolean.class.isInstance(value)) {
return true;
} else {
return false;
}
}
/**
* Factory method that returns a new instance of the given Class. This
* is called at the start of the bean creation process and may be
* overridden to provide custom behavior like returning a cached bean
* instance.
*
* @param c The Class to create an object from.
* @return A newly created object of the Class.
* @throws SQLException if creation failed.
*/
protected Object newInstance(Class c) throws SQLException {
try {
return c.newInstance();
} catch (InstantiationException e) {
throw new SQLException(
"Cannot create " + c.getName() + ": " + e.getMessage());
} catch (IllegalAccessException e) {
throw new SQLException(
"Cannot create " + c.getName() + ": " + e.getMessage());
}
}
/**
* Returns a PropertyDescriptor[] for the given Class.
*
* @param c The Class to retrieve PropertyDescriptors for.
* @return A PropertyDescriptor[] describing the Class.
* @throws SQLException if introspection failed.
*/
private PropertyDescriptor[] propertyDescriptors(Class c)
throws SQLException {
// Introspector caches BeanInfo classes for better performance
BeanInfo beanInfo = null;
try {
beanInfo = Introspector.getBeanInfo(c);
} catch (IntrospectionException e) {
throw new SQLException(
"Bean introspection failed: " + e.getMessage());
}
return beanInfo.getPropertyDescriptors();
}
/**
* The positions in the returned array represent column numbers. The
* values stored at each position represent the index in the
* <code>PropertyDescriptor[]</code> for the bean property that matches
* the column name. If no bean property was found for a column, the
* position is set to <code>PROPERTY_NOT_FOUND</code>.
*
* @param rsmd The <code>ResultSetMetaData</code> containing column
* information.
*
* @param props The bean property descriptors.
*
* @throws SQLException if a database access error occurs
*
* @return An int[] with column index to property index mappings. The 0th
* element is meaningless because JDBC column indexing starts at 1.
*/
protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
PropertyDescriptor[] props) throws SQLException {
int cols = rsmd.getColumnCount();
int columnToProperty[] = new int[cols + 1];
Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
for (int col = 1; col <= cols; col++) {
String columnName = rsmd.getColumnName(col);
for (int i = 0; i < props.length; i++) {
if (columnName.equalsIgnoreCase(props[i].getName())) {
columnToProperty[col] = i;
break;
}
}
}
return columnToProperty;
}
/**
* Convert a <code>ResultSet</code> column into an object. Simple
* implementations could just call <code>rs.getObject(index)</code> while
* more complex implementations could perform type manipulation to match
* the column"s type to the bean property type.
*
* <p>
* This implementation calls the appropriate <code>ResultSet</code> getter
* method for the given property type to perform the type conversion. If
* the property type doesn"t match one of the supported
* <code>ResultSet</code> types, <code>getObject</code> is called.
* </p>
*
* @param rs The <code>ResultSet</code> currently being processed. It is
* positioned on a valid row before being passed into this method.
*
* @param index The current column index being processed.
*
* @param propType The bean property type that this column needs to be
* converted into.
*
* @throws SQLException if a database access error occurs
*
* @return The object from the <code>ResultSet</code> at the given column
* index after optional type processing or <code>null</code> if the column
* value was SQL NULL.
*/
protected Object processColumn(ResultSet rs, int index, Class propType)
throws SQLException {
if ( !propType.isPrimitive() && rs.getObject(index) == null ) {
return null;
}
if (propType.equals(String.class)) {
return rs.getString(index);
} else if (
propType.equals(Integer.TYPE) || propType.equals(Integer.class)) {
return new Integer(rs.getInt(index));
} else if (
propType.equals(Boolean.TYPE) || propType.equals(Boolean.class)) {
return new Boolean(rs.getBoolean(index));
} else if (propType.equals(Long.TYPE) || propType.equals(Long.class)) {
return new Long(rs.getLong(index));
} else if (
propType.equals(Double.TYPE) || propType.equals(Double.class)) {
return new Double(rs.getDouble(index));
} else if (
propType.equals(Float.TYPE) || propType.equals(Float.class)) {
return new Float(rs.getFloat(index));
} else if (
propType.equals(Short.TYPE) || propType.equals(Short.class)) {
return new Short(rs.getShort(index));
} else if (propType.equals(Byte.TYPE) || propType.equals(Byte.class)) {
return new Byte(rs.getByte(index));
} else if (propType.equals(Timestamp.class)) {
return rs.getTimestamp(index);
} else {
return rs.getObject(index);
}
}
}
Wraps a ResultSet to trim strings returned by the getString() and getObject() methods.
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.ResultSet;
/**
* Wraps a <code>ResultSet</code> to trim strings returned by the
* <code>getString()</code> and <code>getObject()</code> methods.
*
* <p>
* Usage Example:
* This example shows how to decorate ResultSets so processing continues as
* normal but all Strings are trimmed before being returned from the
* <code>ResultSet</code>.
* </p>
*
* <pre>
* ResultSet rs = // somehow get a ResultSet;
*
* // Substitute wrapped ResultSet with additional behavior for real ResultSet
* rs = StringTrimmedResultSet.wrap(rs);
*
* // Pass wrapped ResultSet to processor
* List list = new BasicRowProcessor().toBeanList(rs);
* </pre>
*/
public class StringTrimmedResultSet implements InvocationHandler {
/**
* The wrapped result.
*/
private final ResultSet rs;
/**
* Constructs a new instance of <code>StringTrimmedResultSet</code>
* to wrap the specified <code>ResultSet</code>.
* @param rs ResultSet to wrap
*/
public StringTrimmedResultSet(ResultSet rs) {
super();
this.rs = rs;
}
/**
* Intercept calls to the <code>getString()</code> and
* <code>getObject()</code> methods and trim any Strings before they"re
* returned.
*
* @throws Throwable
* @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[])
*/
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
Object result = method.invoke(this.rs, args);
if (method.getName().equals("getObject")
|| method.getName().equals("getString")) {
if (result instanceof String) {
result = ((String) result).trim();
}
}
return result;
}
}
