Java/Database SQL JDBC/ResultSet

Concurrency in ResultSet

   <source lang="java">

/* 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());
   }
 }

}

 </source>

Convert a ResultSet to XML

   <source lang="java">

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();
 }

}

 </source>

Decorates a ResultSet with checks for a SQL NULL value on each getXXX method.

   <source lang="java">

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 ResultSet with checks for a SQL NULL value on each
* getXXX method. If a column value obtained by a
* getXXX 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 null value, which can be
* overridden for instances of the class.
*

*

* Usage example:

*

 *  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();
 *

*

* Unlike some other classes in DbUtils, this class is NOT thread-safe. *

*/

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 SqlNullCheckedResultSet to wrap
  * the specified ResultSet.
  * 
  * @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 getAsciiStream 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 getBigDecimal 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 getBinaryStream 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 getBlob 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 getBoolean 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 getByte 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 getBytes 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 getCharacterStream 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 getClob 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 getDate 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 getDouble 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 getFloat 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 getInt 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 getLong 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 getObject 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 getRef 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 getShort 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 getString 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 getTime 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 getTimestamp 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 getURL method.
  * 
  * @return the value
  */
 public URL getNullURL() {
   return this.nullURL;
 }
 /**
  * Intercepts calls to get* methods and calls the appropriate
  * getNull* method if the ResultSet returned
  * null.
  * 
  * @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 getAsciiStream 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 getBigDecimal 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 getBinaryStream 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 getBlob 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 getBoolean 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 getByte 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 getBytes 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 getCharacterStream 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 getClob 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 getDate 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 getDouble 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 getFloat 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 getInt 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 getLong 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 getObject 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 getRef 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 getShort 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 getString 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 getTime 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 getTimestamp 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 getURL method.
  * 
  * @param nullURL
  *          the value
  */
 public void setNullURL(URL nullURL) {
   this.nullURL = nullURL;
 }

}

 </source>

Demonstrate simple use of the CachedRowSet

   <source lang="java">

/*

* 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();
 }

}

 </source>

Get available ResultSet types

   <source lang="java">

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;
 }

}

 </source>

Get BLOB data from resultset

   <source lang="java">

/* 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();
   }
 }

}

 </source>

Get Column Count In ResultSet

   <source lang="java">

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;
 }

}

 </source>

Metadata for ResultSet

   <source lang="java">

/* 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());
   }
 }

}

 </source>

Output data from table

   <source lang="java">

/* 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);
   }
 }

}

 </source>

Print ResultSet in HTML

   <source lang="java">

/*

* 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(""); print(""); for (int i=1; i<=count; i++) { print(""); while (rs.next()) { print(""); for (int i=1; i<=count; i++) { print(""); } println("

"); print(md.getColumnLabel(i)); } println("
"); print(rs.getString(i)); } println("

");

 }
 /* (non-Javadoc)
  * @see ResultSetDecorator#write(int)
  */
 void write(int updateCount) throws IOException {

println("

RowCount: updateCount = " + updateCount + ""); } /** 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(); } </source>

Results Decorator SQL

<source lang="java">

/*

* 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();

}

</source>

Results Decorator Text

<source lang="java">

/*

* 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();

}

</source>

Results Decorator XML

<source lang="java">

/*

* 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();

}

</source>

ResultSet getter Methods

<source lang="java">

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)

</source>

ResultSet Update

<source lang="java">

/*

* 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()); } }

}

</source>

ResultSet Update Methods

<source lang="java">

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)

</source>

Retrieve a rowcount from a ResultSet

<source lang="java">

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(); }

}

</source>

Scrollable ResultSet

<source lang="java">

/* 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()); } }

}

</source>

SQL statement: ResultSet and ResultSetMetaData

<source lang="java">

/* 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()); } }

}

</source>

Wraps a ResultSet in an Iterator.

<source lang="java">

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; /**

* * Wraps a ResultSet in an Iterator. This is useful * when you want to present a non-database application layer with domain * neutral data. * * * * This implementation requires the ResultSet.isLast() method * to be implemented. * */

public class ResultSetIterator implements Iterator {

/** * The wrapped ResultSet. */ 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 ResultSet in an Iterator. */ public ResultSetIterator(ResultSet rs) { this(rs , new BasicRowProcessor()); } /** * Constructor for ResultSetIterator. * @param rs Wrap this ResultSet in an Iterator. * @param convert The processor to use when converting a row into an * Object[]. Defaults to a * BasicRowProcessor. */ public ResultSetIterator(ResultSet rs, RowProcessor convert) { this.rs = rs; this.convert = convert; } /** * Returns true if there are more rows in the ResultSet. * @return boolean true 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 Object[]. * @return An Object[] with the same number of elements as * columns in the ResultSet. * @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 ResultSet. * @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()); }

} /**

* RowProcessor implementations convert * ResultSet rows into various other objects. Implementations * can extend BasicRowProcessor to protect themselves * from changes to this interface. * * @see BasicRowProcessor */ interface RowProcessor { /** * Create an Object[] from the column values in one * ResultSet row. The ResultSet 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 ResultSet. * * @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 ResultSet * row. The ResultSet 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 ResultSet. * * @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 List of JavaBeans from the column values in all * ResultSet rows. ResultSet.next() should * not 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 List of beans with the given type in the order * they were returned by the ResultSet. */ public List toBeanList(ResultSet rs, Class type) throws SQLException; /** * Create a Map from the column values in one * ResultSet row. The ResultSet 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 ResultSet. * * @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 RowProcessor interface. * * * This class is thread-safe. * * * @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 ResultSet row into an Object[]. * This implementation copies column values into the array in the same * order they"re returned from the ResultSet. Array elements * will be set to null 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 ResultSet 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 ResultSet into a List 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 ResultSet row into a Map. This * implementation returns a Map with case insensitive column * names as keys. Calls to map.get("COL") and * map.get("col") 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. * * 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. * * Note: This implementation does not allow null * for key, whereas {@link HashMap} does, because of the code:
*
* key.toString().toLowerCase() *

*/ private static class CaseInsensitiveHashMap extends HashMap { /** * The internal mapping from lowercase keys to the real keys. * * * Any query operation using the key * ({@link #get(Object)}, {@link #containsKey(Object)}) * is done in three steps:
*
*
convert the parameter key to lower case
*
get the actual key that corresponds to the lower case key
*
query the map with the actual key
*

* */ 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. */

/** * * BeanProcessor matches column names to bean property names * and converts ResultSet columns into objects for those bean * properties. Subclasses should override the methods in the processing chain * to customize behavior. * * * * This class is thread-safe. * * * @see BasicRowProcessor * * @since DbUtils 1.1 */ class BeanProcessor { /** * Special array value used by mapColumnsToProperties that * indicates there is no bean property that matches a column from a * ResultSet. */ 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 ResultSet row into a JavaBean. This * implementation uses reflection and BeanInfo classes to * match column names to bean property names. Properties are matched to * columns based on several factors: *

*
*
* The class has a writable property with the same name as a column. * The name comparison is case insensitive. *

*
*
* 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. *
*

* * * Primitive bean properties are set to their defaults when SQL NULL is * returned from the ResultSet. Numeric fields are set to 0 * and booleans are set to false. Object bean properties are set to * null when SQL NULL is returned. This is the same behavior * as the ResultSet get* methods. * * * @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 ResultSet into a List of JavaBeans. * This implementation uses reflection and BeanInfo classes to * match column names to bean property names. Properties are matched to * columns based on several factors: *

*
*
* The class has a writable property with the same name as a column. * The name comparison is case insensitive. *

*
*
* 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. *
*

* * * Primitive bean properties are set to their defaults when SQL NULL is * returned from the ResultSet. Numeric fields are set to 0 * and booleans are set to false. Object bean properties are set to * null when SQL NULL is returned. This is the same behavior * as the ResultSet get* methods. * * * @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 * PropertyDescriptor[] for the bean property that matches * the column name. If no bean property was found for a column, the * position is set to PROPERTY_NOT_FOUND. * * @param rsmd The ResultSetMetaData 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 ResultSet column into an object. Simple * implementations could just call rs.getObject(index) while * more complex implementations could perform type manipulation to match * the column"s type to the bean property type. * * * This implementation calls the appropriate ResultSet getter * method for the given property type to perform the type conversion. If * the property type doesn"t match one of the supported * ResultSet types, getObject is called. * * * @param rs The ResultSet 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 ResultSet at the given column * index after optional type processing or null 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); } } } </source>

Wraps a ResultSet to trim strings returned by the getString() and getObject() methods.

<source lang="java">

/*

* 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 ResultSet to trim strings returned by the * getString() and getObject() methods. * * * Usage Example: * This example shows how to decorate ResultSets so processing continues as * normal but all Strings are trimmed before being returned from the * ResultSet. * *
*
* 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); *

*/

public class StringTrimmedResultSet implements InvocationHandler {

/** * The wrapped result. */ private final ResultSet rs; /** * Constructs a new instance of StringTrimmedResultSet * to wrap the specified ResultSet. * @param rs ResultSet to wrap */ public StringTrimmedResultSet(ResultSet rs) { super(); this.rs = rs; } /** * Intercept calls to the getString() and * getObject() 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; }

}

 </source>

Java/Database SQL JDBC/ResultSet

Содержание

Concurrency in ResultSet

Convert a ResultSet to XML

Decorates a ResultSet with checks for a SQL NULL value on each getXXX method.

Demonstrate simple use of the CachedRowSet

Get available ResultSet types

Get BLOB data from resultset

Get Column Count In ResultSet

Metadata for ResultSet

Output data from table

Print ResultSet in HTML

Results Decorator SQL

Results Decorator Text

Results Decorator XML

ResultSet getter Methods

ResultSet Update

ResultSet Update Methods

Retrieve a rowcount from a ResultSet

Scrollable ResultSet

SQL statement: ResultSet and ResultSetMetaData

Wraps a ResultSet in an Iterator.

Wraps a ResultSet to trim strings returned by the getString() and getObject() methods.

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты