Java/Database SQL JDBC/Connection

boolean java.sql.DriverPropertyInfo.required (Is property value required?)

   <source lang="java">
  

import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "org.gjt.mm.mysql.Driver";
   Class.forName(driverName);
   String url = "jdbc:mysql://a/b";
   Driver driver = DriverManager.getDriver(url);
   DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);
   for (int i = 0; i < info.length; i++) {
     String name = info[i].name;
     boolean isRequired = info[i].required;
     String value = info[i].value;
     String desc = info[i].description;
     String[] choices = info[i].choices;
   }
 }

}

 </source>
   
  
 
  

Committing and Rolling Back Updates to a Database

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   // Disable auto commit
   connection.setAutoCommit(false);
   // Do SQL updates...
   // Commit updates
   connection.rumit();
 }

}

 </source>
   
  
 
  

Connect to more than one database

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class TestConnectToMoreThanOneDatabase {

 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:scorpian";
   String username = "userName";
   String password = "pass";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/tiger";
   String username = "root";
   String password = "root";
   Class.forName(driver); // load MySQL driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void main(String[] args) {
   Connection oracleConn = null;
   Connection mysqlConn = null;
   try {
     oracleConn = getOracleConnection();
     mysqlConn = getMySqlConnection();
     System.out.println("oracleConn=" + oracleConn);
     System.out.println("mysqlConn=" + mysqlConn);
   } catch (Exception e) {
     // handle the exception
     e.printStackTrace();
     System.exit(1);
   } finally {
     // release database resources
     try {
       oracleConn.close();
       mysqlConn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }

}

 </source>
   
  
 
  

Create Connection With Properties

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; public class TestCreateConnectionWithProperties_MySQL {

 public static final String DATABASE_USER = "user";
 public static final String DATABASE_PASSWORD = "password";
 public static final String MYSQL_AUTO_RECONNECT = "autoReconnect";
 public static final String MYSQL_MAX_RECONNECTS = "maxReconnects";
 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   // load the driver
   Class.forName(driver);
   String dbURL = "jdbc:mysql://localhost/databaseName";
   String dbUsername = "root";
   String dbPassword = "root";
   java.util.Properties connProperties = new java.util.Properties();
   connProperties.put(DATABASE_USER, dbUsername);
   connProperties.put(DATABASE_PASSWORD, dbPassword);
   // set additional connection properties:
   // if connection stales, then make automatically
   // reconnect; make it alive again;
   // if connection stales, then try for reconnection;
   connProperties.put(MYSQL_AUTO_RECONNECT, "true");
   connProperties.put(MYSQL_MAX_RECONNECTS, "4");
   Connection conn = DriverManager.getConnection(dbURL, connProperties);
   return conn;
 }
 public static void main(String[] args) {
   Connection conn = null;
   try {
     // get connection to an Oracle database
     conn = getConnection();
     System.out.println("conn=" + conn);
   } catch (Exception e) {
     // handle the exception
     e.printStackTrace();
     System.exit(1);
   } finally {
     // release database resources
     try {
       conn.close();
     } catch (Exception ignore) {
     }
   }
 }

}

 </source>
   
  
 
  

Debug Database connection

   <source lang="java">
 

import java.io.FileOutputStream; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestDebug_MySQL {

 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/octopus";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   return DriverManager.getConnection(url, username, password);
 }
 public static int countRows(Connection conn, String tableName) throws SQLException {
   Statement stmt = null;
   ResultSet rs = null;
   int rowCount = -1;
   try {
     stmt = conn.createStatement();
     rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
     // get the number of rows from the result set
     rs.next();
     rowCount = rs.getInt(1);
   } finally {
     rs.close();
     stmt.close();
   }
   return rowCount;
 }
 public static void main(String[] args) {
   Connection conn = null;
   try {
     PrintWriter pw = new PrintWriter(new FileOutputStream("mysql_debug.txt"));
     DriverManager.setLogWriter(pw);
     conn = getConnection();
     String tableName = "myTable";
     System.out.println("tableName=" + tableName);
     System.out.println("conn=" + conn);
     System.out.println("rowCount=" + countRows(conn, tableName));
   } catch (Exception e) {
     e.printStackTrace();
     System.exit(1);
   } finally {
     try {
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }

}

 </source>
   
  
 
  

Determining If a Database Supports Transactions

   <source lang="java">
  

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   DatabaseMetaData dmd = connection.getMetaData();
   if (dmd.supportsTransactions()) {
     // Transactions are supported
   } else {
     // Transactions are not supported
   }
 }

}

 </source>
   
  
 
  

Disable auto commit mode in JDBC

   <source lang="java">
  

import java.sql.Connection; public class Main {

 public static void main(String[] args) throws Exception{
   Connection connection = null;
   connection.setAutoCommit(false);
   connection.rumit();
 }

}

 </source>
   
  
 
  

Encapsulate the Connection-related operations that every JDBC program seems to use

   <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.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; /** Encapsulate the Connection-related operations that every

* JDBC program seems to use.
*/

public class ConnectionUtil {

 /** The default config filename, relative to ${user.home} */
 public static final String DEFAULT_NAME = ".db.properties";
 
 /** The current config filename */
 private static String configFileName =
   System.getProperty("user.home") + File.separator + DEFAULT_NAME;
 /** Get a Connection for the given config using the default or set property file name */
 public static Connection getConnection(String config) throws Exception {
   try {
     Properties p = new Properties();
     p.load(new FileInputStream(configFileName));
     return getConnection(p, config);
   } catch (IOException ex) {
     throw new Exception(ex.toString());
   }
 }
 
 /** Get a Connection for the given config name from a provided Properties */
 public static Connection getConnection(Properties p,  String config) throws Exception {
   try {
     String db_driver = p.getProperty(config  + "." + "DBDriver");
     String db_url = p.getProperty(config  + "." + "DBURL");
     String db_user = p.getProperty(config  + "." + "DBUser");
     String db_password = p.getProperty(config  + "." + "DBPassword");
     if (db_driver == null || db_url == null) {
       throw new IllegalStateException("Driver or URL null: " + config);
     }
     return createConnection(db_driver, db_url, db_user, db_password);
   } catch (ClassNotFoundException ex) {
     throw new Exception(ex.toString());
 
   } catch (SQLException ex) {
     throw new Exception(ex.toString());
   }
 }
 public static Connection createConnection(String db_driver, String db_url, 
         String db_user, String db_password)
     throws ClassNotFoundException, SQLException {

   // Load the database driver
   System.out.println("Loading driver " + db_driver);
   Class.forName(db_driver);
   System.out.println("Connecting to DB " + db_url);
   return DriverManager.getConnection(
     db_url, db_user, db_password);
 }
 
 /** Returns the full path of the configuration file being used.
  * @return Returns the configFileName.
  */
 public static String getConfigFileName() {
   return configFileName;
 }
 
 /** Sets the full path of the config file to read.
  * @param configFileNam The FileName of the configuration file to use.
  */
 public static void setConfigFileName(String configFileNam) {
   configFileName = configFileNam;
   File file = new File(configFileName);
   if (!file.canRead()) {
     throw new IllegalArgumentException("Unreadable: " + configFileName);
   }
   try {
     ConnectionUtil.configFileName = file.getCanonicalPath();
   } catch (IOException ex) {
     System.err.println("Warning: IO error checking path: " + configFileName);
     ConnectionUtil.configFileName = configFileName;
   }
 }

}

 </source>
   
  
 
  

Install Oracle Driver and Execute Resultset

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestThinApp {

 public static void main(String args[]) throws ClassNotFoundException,
     SQLException {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery("select "Hello Thin driver tester "||USER||"!" result from dual");
   while (rset.next())
     System.out.println(rset.getString(1));
   rset.close();
   stmt.close();
   conn.close();
 }

}

 </source>
   
  
 
  

JDBC Simple Connection

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SimpleConnection {

 static public void main(String args[]) {
   Connection connection = null;
   if (args.length != 4) {
     System.out.println("Syntax: java SimpleConnection "
         + "DRIVER URL UID PASSWORD");
     return;
   }
   try { 
     Class.forName(args[0]).newInstance();
   } catch (Exception e) {
     e.printStackTrace();
     return;
   }
   try {
     connection = DriverManager.getConnection(args[1], args[2], args[3]);
     System.out.println("Connection successful!");
   } catch (SQLException e) {
     e.printStackTrace();
   } finally {
     if (connection != null) {
       try {
         connection.close();
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }
 }

}

 </source>
   
  
 
  

Listing All Available Parameters for Creating a JDBC Connection

   <source lang="java">
  

import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "org.gjt.mm.mysql.Driver";
   Class.forName(driverName);
   String url = "jdbc:mysql://a/b";
   Driver driver = DriverManager.getDriver(url);
   DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);
   for (int i = 0; i < info.length; i++) {
     String name = info[i].name;
     boolean isRequired = info[i].required;
     String value = info[i].value;
     String desc = info[i].description;
     String[] choices = info[i].choices;
   }
 }

}

 </source>
   
  
 
  

Load MySQL JDBC Driver

   <source lang="java">
 

public class MySQLJDBCDriverTest {

 public static void main(String[] args) {
   try {
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     System.out.println("Good to go");
   } catch (Exception E) {
     System.out.println("JDBC Driver error");
   }
 }

}

 </source>
   
  
 
  

Load some drivers

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

/**

• Load some drivers.
• /

public class LoadDriver {

 public static void main(String[] av) {
   try {
     // Try to load the jdbc-odbc bridge driver
     // Should be present on Sun JDK implementations.
     Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     System.out.println("Loaded " + c.getName());
     // Try to load an Oracle driver.
     Class d = Class.forName("oracle.jdbc.driver.OracleDriver");
     System.out.println("Loaded " + d.getName());
   } catch (ClassNotFoundException ex) {
     System.err.println(ex);
   }
 }

}

 </source>
   
  
 
  

Oracle JDBC Driver load

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestClassForNameApp {

 public static void main(String args[]) {
   try {
     Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch (ClassNotFoundException e) {
     System.out.println("Oops! Can"t find class oracle.jdbc.driver.OracleDriver");
     System.exit(1);
   }
   Connection conn = null;
   Statement stmt = null;
   ResultSet rset = null;
   try {
     conn = DriverManager.getConnection("jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
     stmt = conn.createStatement();
     rset = stmt
         .executeQuery("select "Hello "||USER||"!" result from dual");
     while (rset.next())
       System.out.println(rset.getString(1));
     rset.close();
     rset = null;
     stmt.close();
     stmt = null;
     conn.close();
     conn = null;
   } catch (SQLException e) {
     System.out.println("Darn! A SQL error: " + e.getMessage());
   } finally {
     if (rset != null)
       try {
         rset.close();
       } catch (SQLException ignore) {
       }
     if (stmt != null)
       try {
         stmt.close();
       } catch (SQLException ignore) {
       }
     if (conn != null)
       try {
         conn.close();
       } catch (SQLException ignore) {
       }
   }
 }

}

 </source>
   
  
 
  

Oracle JDBC Driver load test: NewInstance

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestClassForNameNewInstanceApp {

 public static void main(String args[]) {
   try {
     Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
   } catch (ClassNotFoundException e) {
     System.out.println("Oops! Can"t find class oracle.jdbc.driver.OracleDriver");
     System.exit(1);
   } catch (IllegalAccessException e) {
     System.out
         .println("Uh Oh! You can"t load oracle.jdbc.driver.OracleDriver");
     System.exit(2);
   } catch (InstantiationException e) {
     System.out
         .println("Geez! Can"t instantiate oracle.jdbc.driver.OracleDriver");
     System.exit(3);
   }
   Connection conn = null;
   Statement stmt = null;
   ResultSet rset = null;
   try {
     conn = DriverManager.getConnection(
         "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
     stmt = conn.createStatement();
     rset = stmt
         .executeQuery("select "Hello "||USER||"!" result from dual");
     while (rset.next())
       System.out.println(rset.getString(1));
     rset.close();
     rset = null;
     stmt.close();
     stmt = null;
     conn.close();
     conn = null;
   } catch (SQLException e) {
     System.out.println("Darn! A SQL error: " + e.getMessage());
   } finally {
     if (rset != null)
       try {
         rset.close();
       } catch (SQLException ignore) {
       }
     if (stmt != null)
       try {
         stmt.close();
       } catch (SQLException ignore) {
       }
     if (conn != null)
       try {
         conn.close();
       } catch (SQLException ignore) {
       }
   }
 }

}

 </source>
   
  
 
  

Print warnings on a Connection to a specified PrintWriter.

   <source lang="java">

import java.io.PrintWriter; import java.sql.Connection; import java.sql.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.
*
*
*/

public class Main {

 /**
  * Print warnings on a Connection to STDERR.
  *
  * @param conn Connection to print warnings from
  */
 public static void printWarnings(Connection conn) {
     printWarnings(conn, new PrintWriter(System.err));
 }
 /**
  * Print warnings on a Connection to a specified PrintWriter. 
  *
  * @param conn Connection to print warnings from
  * @param pw PrintWriter to print to
  */
 public static void printWarnings(Connection conn, PrintWriter pw) {
     if (conn != null) {
         try {
             printStackTrace(conn.getWarnings(), pw);
         } catch (SQLException e) {
             printStackTrace(e, pw);
         }
     }
 }

 /**
  * Print the stack trace for a SQLException to a 
  * specified PrintWriter. 
  *
  * @param e SQLException to print stack trace of
  * @param pw PrintWriter to print to
  */
 public static void printStackTrace(SQLException e, PrintWriter pw) {
     SQLException next = e;
     while (next != null) {
         next.printStackTrace(pw);
         next = next.getNextException();
         if (next != null) {
             pw.println("Next SQLException:");
         }
     }
 }

}

 </source>
   
  
 
  

Print warnings on a Connection to STDERR.

   <source lang="java">

import java.io.PrintWriter; import java.sql.Connection; import java.sql.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.
*
*
*/

public class Main {

 /**
  * Print warnings on a Connection to STDERR.
  *
  * @param conn Connection to print warnings from
  */
 public static void printWarnings(Connection conn) {
     printWarnings(conn, new PrintWriter(System.err));
 }
 /**
  * Print warnings on a Connection to a specified PrintWriter. 
  *
  * @param conn Connection to print warnings from
  * @param pw PrintWriter to print to
  */
 public static void printWarnings(Connection conn, PrintWriter pw) {
     if (conn != null) {
         try {
             printStackTrace(conn.getWarnings(), pw);
         } catch (SQLException e) {
             printStackTrace(e, pw);
         }
     }
 }

 /**
  * Print the stack trace for a SQLException to a 
  * specified PrintWriter. 
  *
  * @param e SQLException to print stack trace of
  * @param pw PrintWriter to print to
  */
 public static void printStackTrace(SQLException e, PrintWriter pw) {
     SQLException next = e;
     while (next != null) {
         next.printStackTrace(pw);
         next = next.getNextException();
         if (next != null) {
             pw.println("Next SQLException:");
         }
     }
 }

}

 </source>
   
  
 
  

Set save point

   <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 SetSavepoint {

   public static void main(String args[]) {
   
 String url = "jdbc:mySubprotocol:myDataSource";
 try {
   Class.forName("myDriver.className");
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: ");
     System.err.println(e.getMessage());
     }
 try {
     Connection con = DriverManager.getConnection(url,
             "myLogin", "myPassword");
   con.setAutoCommit(false);
   String query = "SELECT COF_NAME, PRICE FROM COFFEES " +
                   "WHERE TOTAL > ?";
   String update = "UPDATE COFFEES SET PRICE = ? " +
                   "WHERE COF_NAME = ?";
   PreparedStatement getPrice = con.prepareStatement(query);
   PreparedStatement updatePrice = con.prepareStatement(
                           update);
   getPrice.setInt(1, 7000);
   ResultSet rs = getPrice.executeQuery();
   Savepoint save1 = con.setSavepoint();
   while (rs.next())  {
     String cof = rs.getString("COF_NAME");
     float oldPrice = rs.getFloat("PRICE");
     float newPrice = oldPrice + (oldPrice * .05f);
     updatePrice.setFloat(1, newPrice);
     updatePrice.setString(2, cof);
     updatePrice.executeUpdate();
     System.out.println("New price of " + cof + " is " +
                         newPrice);
     if (newPrice > 11.99) {
       con.rollback(save1);
     }
   }
   getPrice = con.prepareStatement(query);
   updatePrice = con.prepareStatement(update);
   getPrice.setInt(1, 8000);
   rs = getPrice.executeQuery();
   System.out.println();
   Savepoint save2 = con.setSavepoint();
   while (rs.next())  {
     String cof = rs.getString("COF_NAME");
     float oldPrice = rs.getFloat("PRICE");
     float newPrice = oldPrice + (oldPrice * .05f);
     updatePrice.setFloat(1, newPrice);
     updatePrice.setString(2, cof);
     updatePrice.executeUpdate();
     System.out.println("New price of " + cof + " is " + 
                         newPrice);
     if (newPrice > 11.99) {
       con.rollback(save2);
     }
   }
   con.rumit();
   Statement stmt = con.createStatement();
   rs = stmt.executeQuery("SELECT COF_NAME, " +
       "PRICE FROM COFFEES");
   System.out.println();
   while (rs.next()) {
     String name = rs.getString("COF_NAME");
     float price = rs.getFloat("PRICE");
     System.out.println("Current price of " + name + 
                 " is " + price);
   }
     con.close();
 } catch (Exception e) {
     e.printStackTrace();
   }
   }

}

 </source>
   
  
 
  

Specify a CharSet when connecting to a DBMS

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.util.Properties; public class Main {

 public static void main(String[] argv) throws Exception {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Properties prop = new Properties();
   prop.put("charSet", "iso-8859-7"); 
   prop.put("user", "your username");
   prop.put("password", "your password");
   // Connect to the database
   Connection con = DriverManager.getConnection("url", prop);
 }

}

 </source>
   
  
 
  

String[] java.sql.DriverPropertyInfo.choices (Get possible choices for property; if null, value can be any string)

   <source lang="java">
  

import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "org.gjt.mm.mysql.Driver";
   Class.forName(driverName);
   String url = "jdbc:mysql://a/b";
   Driver driver = DriverManager.getDriver(url);
   DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);
   for (int i = 0; i < info.length; i++) {
     String name = info[i].name;
     boolean isRequired = info[i].required;
     String value = info[i].value;
     String desc = info[i].description;
     String[] choices = info[i].choices;
   }
 }

}

 </source>
   
  
 
  

String java.sql.DriverPropertyInfo.description (Get description of property)

   <source lang="java">
  

import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "org.gjt.mm.mysql.Driver";
   Class.forName(driverName);
   String url = "jdbc:mysql://a/b";
   Driver driver = DriverManager.getDriver(url);
   DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);
   for (int i = 0; i < info.length; i++) {
     String name = info[i].name;
     boolean isRequired = info[i].required;
     String value = info[i].value;
     String desc = info[i].description;
     String[] choices = info[i].choices;
   }
 }

}

 </source>
   
  
 
  

String java.sql.DriverPropertyInfo.name (Get name of property)

   <source lang="java">
  

import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "org.gjt.mm.mysql.Driver";
   Class.forName(driverName);
   String url = "jdbc:mysql://a/b";
   Driver driver = DriverManager.getDriver(url);
   DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);
   for (int i = 0; i < info.length; i++) {
     String name = info[i].name;
     boolean isRequired = info[i].required;
     String value = info[i].value;
     String desc = info[i].description;
     String[] choices = info[i].choices;
   }
 }

}

 </source>
   
  
 
  

String java.sql.DriverPropertyInfo.value (Get current value)

   <source lang="java">
  

import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "org.gjt.mm.mysql.Driver";
   Class.forName(driverName);
   String url = "jdbc:mysql://a/b";
   Driver driver = DriverManager.getDriver(url);
   DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);
   for (int i = 0; i < info.length; i++) {
     String name = info[i].name;
     boolean isRequired = info[i].required;
     String value = info[i].value;
     String desc = info[i].description;
     String[] choices = info[i].choices;
   }
 }

}

 </source>
   
  
 
  

Test of loading a driver and connecting to a database

   <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.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.SQLWarning; /**

* Test of loading a driver and connecting to a database. The URL assumes you
* have the M$ Example "Companies" database configured as a System DSN (or user
* DSN for your user) in the ODBC control panel.
*/

public class Connect {

 public static void main(String[] av) {
   String dbURL = "jdbc:odbc:Companies";
   try {
     // Load the jdbc-odbc bridge driver
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     // Enable logging
     DriverManager.setLogWriter(new PrintWriter((System.err)));
     System.out.println("Getting Connection");
     Connection conn = DriverManager.getConnection(dbURL, "ian", ""); // user,
                                      // passwd
     // If a SQLWarning object is available, print its
     // warning(s). There may be multiple warnings chained.
     SQLWarning warn = conn.getWarnings();
     while (warn != null) {
       System.out.println("SQLState: " + warn.getSQLState());
       System.out.println("Message:  " + warn.getMessage());
       System.out.println("Vendor:   " + warn.getErrorCode());
       System.out.println("");
       warn = warn.getNextWarning();
     }
     // Do something with the connection here...
     conn.close(); // All done with that DB connection
   } catch (ClassNotFoundException e) {
     System.out.println("Can"t load driver " + e);
   } catch (SQLException e) {
     System.out.println("Database access failed " + e);
   }
 }

}

 </source>
   
  
 
  

Test Register Oracle JDBC Driver

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestRegisterDriverApp {

 public static void main(String args[]) {
   try {
     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
   } catch (SQLException e) {
     System.out.println("Oops! Got a SQL error: " + e.getMessage());
     System.exit(1);
   }
   Connection conn = null;
   Statement stmt = null;
   ResultSet rset = null;
   try {
     conn = DriverManager.getConnection("jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
     stmt = conn.createStatement();
     rset = stmt
         .executeQuery("select "Hello "||USER||"!" result from dual");
     while (rset.next())
       System.out.println(rset.getString(1));
     rset.close();
     rset = null;
     stmt.close();
     stmt = null;
     conn.close();
     conn = null;
   } catch (SQLException e) {
     System.out.println("Darn! A SQL error: " + e.getMessage());
   } finally {
     if (rset != null)
       try {
         rset.close();
       } catch (SQLException ignore) {
       }
     if (stmt != null)
       try {
         stmt.close();
       } catch (SQLException ignore) {
       }
     if (conn != null)
       try {
         conn.close();
       } catch (SQLException ignore) {
       }
   }
 }

}

 </source>
   
  
 
  

Test Thin Net8 App

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestThinNet8App {

 public static void main(String args[]) throws ClassNotFoundException,
     SQLException {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
   Connection conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@(DESCRIPTION = "
           + "(ADDRESS_LIST = (ADDRESS = "
           + "(PROTOCOL = TCP)(HOST = dssw2k01)(PORT = 1521)))"
           + "(CONNECT_DATA = (SERVICE_NAME = dssw2k01)))",
       "scott", "tiger");
   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery("select "Hello Thin driver tester "||USER||"!" result from dual");
   while (rset.next())
     System.out.println(rset.getString(1));
   rset.close();
   stmt.close();
   conn.close();
 }

}

 </source>
   
  
 
  

Verify database setup

   <source lang="java">
 

import java.sql.*; public class CheckJDBCInstallation {

 /**
  * Test Validity of JDBC Installation
  * 
  * @param conn
  *          a JDBC connection object
  * @param dbVendor
  *          db vendor {"oracle", "mysql" }
  * @return true if a given connection object is a valid one; otherwise return
  *         false.
  * @throws Exception
  *           Failed to determine if a given connection is valid.
  */
 public static boolean isValidConnection(Connection conn, String dbVendor) throws Exception {
   if (conn == null) {
     return false;
   }
   if (conn.isClosed()) {
     return false;
   }
   // depends on the vendor of the database:
   //
   // for MySQL database:
   // you may use the connection object
   // with query of "select 1"; if the
   // query returns the result, then it
   // is a valid Connection object.
   //
   // for Oracle database:
   // you may use the Connection object
   // with query of "select 1 from dual"; if
   // the query returns the result, then it
   // is a valid Connection object.
   if (dbVendor.equalsIgnoreCase("mysql")) {
     return testConnection(conn, "select 1");
   } else if (dbVendor.equalsIgnoreCase("oracle")) {
     return testConnection(conn, "select 1 from dual");
   } else {
     return false;
   }
 }
 /**
  * Test Validity of a Connection
  * 
  * @param conn
  *          a JDBC connection object
  * @param query
  *          a sql query to test against db connection
  * @return true if a given connection object is a valid one; otherwise return
  *         false.
  */
 public static boolean testConnection(Connection conn, String query) {
   ResultSet rs = null;
   Statement stmt = null;
   try {
     stmt = conn.createStatement();
     if (stmt == null) {
       return false;
     }
     rs = stmt.executeQuery(query);
     if (rs == null) {
       return false;
     }
     // connection object is valid: you were able to
     // connect to the database and return something useful.
     if (rs.next()) {
       return true;
     }
     // there is no hope any more for the validity
     // of the Connection object
     return false;
   } catch (Exception e) {
     // something went wrong: connection is bad
     return false;
   } finally {
     // close database resources
     try {
       rs.close();
       stmt.close();        
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }
 public static void main(String[] args) {
   Connection conn = null;
   try {
     String dbVendor = args[0];
     // get connection to a database
     System.out.println("dbVendor=" + dbVendor);
     System.out.println("conn=" + conn);
     System.out.println("valid connection = " + isValidConnection(conn, dbVendor));
   } catch (Exception e) {
     // handle the exception
     e.printStackTrace();
     System.exit(1);
   } finally {
     // release database resources
     try {
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }

}

 </source>