Jdbc

// ----------------------------------------------------------------------------// ConnectionExample.java // ----------------------------------------------------------------------------/* * ============================================================================= * Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved. * * All source code and material located at the Internet address of * http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and * is protected under copyright laws of the United States. This source code may * not be hosted on any other site without my express, prior, written * permission. Application to host any of the material elsewhere can be made by * contacting me at [email protected]. * * I have made every effort and taken great care in making sure that the source * code and other content included on my web site is technically accurate, but I * disclaim any and all responsibility for any loss, damage or destruction of * data or any other property which may arise from relying on it. I will in no * case be liable for any monetary damages arising from such loss, damage or * destruction. * * As with any code, ensure to test this code in a development environment * before attempting to run it in production. * ============================================================================= */ import import import import import import

java.sql.DriverManager; java.sql.Connection; java.sql.Statement; java.sql.ResultSet; java.sql.SQLException; java.util.Properties;

/** * ----------------------------------------------------------------------------* The following class provides an example of using JDBC to connect to an * Oracle database. The one phase of JDBC that is the most difficult and * hard to achieve portability, is when connecting. This phase requires * that the Java database application specify driver-specific information that * JDBC requires in the form of a database URL. * * If you run into problems while trying to simply make a connection, check * if they match any of the following: * * Connection fails with the message "Class no found" * -------------------------------------------------* This message usually results from not having the JDBC driver in your * CLASSPATH. Ensure that if you are including *.zip and *.jar in your * CLASSPATH, that your enter them explicity. If you put all of your *.class * files and the ojdbc14.jar file containing the Oracle-JDBC driver into * /u02/lib, your CLASSPATH should read /u02/lib:/u02/lib/ojdbc14.jar. * * Connection fails with the message "Driver no found" * --------------------------------------------------* In this case, you did not register the JDBC driver with the DriverManager * class. This example application describes several ways to register a * JDBC driver. Sometimes developers using the Class.forName() method of

* registering a JDBC driver encounter an inconsistency between the JDBC * specification and some JVM implementations. You should thus use the * Class.forName().netInstance() method as a workaround. * * When attempting to make a database connection, your application must first * request a java.sql.Connection implementation from the DriverManager. You will * also use a database URL and whatever properties your JDBC driver requires * (generally a user ID and password). The DriverManager in turn will search * through all of the known java.sql.Driver implementations for the one that * connects with the URL you provided. If it exhausts all the implementations * without finding a match, it throws an exception back to your application. * * Once a Driver recognizes your URL, it creates a database connection using * the properties you specified. It then provides the DriverManager with a * java.sql.Connection implementation representing that database connection. The * DriverManager then passes that Connection object back to the application. * * At this point, you may be wondering how the JDBC DriverManager learns about * a new driver implementation. The DriverManager actually keeps a list of * classes that implement that java.sql.Driver interface. Something needs to * register the Driver implementation for any potential database drivers it * might require with the DriverManager. JDBC requires a Driver class to * register itself with the DriverManager when it is initiated. The act of * instantiating a Driver class thus enters it in the DriverManager's list. * * This class (ConnectionExample) provides three ways to register a driver. * ----------------------------------------------------------------------------* @version 1.0 * @author Jeffrey M. Hunter ([email protected]) * @author http://www.idevelopment.info * ----------------------------------------------------------------------------*/ public class ConnectionExample { final final final final final final

String String String String String String

driverClass connectionURLThin connectionURLOCI userID userPassword queryString

HH24:MI:SS') " +

= = = = = =

"oracle.jdbc.driver.OracleDriver"; "jdbc:oracle:thin:@jeffreyh3:1521:CUSTDB"; "jdbc:oracle:oci8:@CUSTDB_JEFFREYH3"; "scott"; "tiger"; "SELECT" + " user " + " , TO_CHAR(sysdate, 'DD-MON-YYYY "FROM dual";

/** * The following method provides an example of how to connect to a database * by registering the JDBC driver using the DriverManager class. This method * requires you to hardcode the loading of a Driver implementation in * your application. this alternative is the least desirable since it * requires a rewrite and recompile if your database or database driver * changes. */ public void driverManager() { Connection con = null; Statement stmt = null;

ResultSet rset = null; try { System.out.print("\n"); System.out.print("+-------------------------------+\n"); System.out.print("| USING DriverManager CLASS |\n"); System.out.print("+-------------------------------+\n"); System.out.print("\n"); System.out.print(" Loading JDBC Driver -> " + driverClass + "\n"); DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); "\n");

System.out.print("

Connecting to

-> " + connectionURLThin +

con = DriverManager.getConnection(connectionURLThin, userID, userPassword); System.out.print(" Connected as -> " + userID + "\n"); System.out.print(" Creating Statement...\n"); stmt = con.createStatement (); System.out.print(" Opening ResultsSet...\n"); rset = stmt.executeQuery(queryString); while (rset.next()) { System.out.println(" System.out.println(" rset.getString(1)); System.out.println(" rset.getString(2)); }

Results..."); User Sysdate

System.out.print(" rset.close();

Closing ResultSet...\n");

System.out.print(" stmt.close();

Closing Statement...\n");

-> " + -> " +

} catch (SQLException e) { e.printStackTrace(); if (con != null) { try { con.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } } finally { if (con != null) { try { System.out.print(" Closing down all connections...\n\n"); con.close(); } catch (SQLException e) {

e.printStackTrace(); }

}

} } /** * The following method provides an example of how to connect to a database * by registering the JDBC driver using the jdbc.drivers property. The * DriverManager will load all classes listed in this property * automatically. This alternative works well for applications with a * command-line interface, but might not be so useful in GUI applications * and applets. This is because you can specify properties at the command * line. */ public void jdbcDriversProperty() { Connection con = null; Statement stmt = null; ResultSet rset = null; try { System.out.print("\n"); System.out.print("+-------------------------------+\n"); System.out.print("| USING jdbc.drivers PROPERTY |\n"); System.out.print("+-------------------------------+\n"); System.out.print("\n"); System.out.print(" Loading JDBC Driver -> " + driverClass + "\n"); System.setProperty("jdbc.drivers", driverClass); "\n");

System.out.print("

Connecting to

-> " + connectionURLThin +

con = DriverManager.getConnection(connectionURLThin, userID, userPassword); System.out.print(" Connected as -> " + userID + "\n"); System.out.print(" Creating Statement...\n"); stmt = con.createStatement (); System.out.print(" Opening ResultsSet...\n"); rset = stmt.executeQuery(queryString); while (rset.next()) { System.out.println(" System.out.println(" rset.getString(1)); System.out.println(" rset.getString(2)); } System.out.print(" rset.close();

Results..."); User Sysdate

Closing ResultSet...\n");

-> " + -> " +

System.out.print(" stmt.close();

Closing Statement...\n");

} catch (SQLException e) { e.printStackTrace(); if (con != null) { try { con.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } } finally { if (con != null) { try { System.out.print(" Closing down all connections...\n\n"); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } /** * The following method provides an example of how to connect to a database * by registering the JDBC driver using the Class.forName() method. This * complex expression is a tool for dynamically creating an instance of * a class when you have some variable representing the class name. Because * a JDBC driver is required to register itself whenever its static * initializer is called, this expression has the net effect of registering * your driver for you. * * NOTE: When using Class.forName("classname"), the JVM is supposed to * be sufficient. Unfortunately, some Java virtual machines do * not actuall call the static intitializer until an instance of * a class is created. As a result, newInstance() should be * called to guarantee that the static initializer is run for * all virtual machines. * * This method is by far the BEST in that it does not require hardcoded * class names and it runs well in all Java environments. In real-world * applications, you should use this method along with a properties file * from which you load the name of the driver. * */ public void classForName() { Connection con = null; Statement stmt = null;

ResultSet rset = null; try { System.out.print("\n"); System.out.print("+-------------------------------+\n"); System.out.print("| USING Class.forName() |\n"); System.out.print("+-------------------------------+\n"); System.out.print("\n"); System.out.print(" Loading JDBC Driver -> " + driverClass + "\n"); Class.forName(driverClass).newInstance(); System.out.print("

Connecting to

-> " + connectionURLThin +

"\n");

con = DriverManager.getConnection(connectionURLThin, userID, userPassword); System.out.print(" Connected as -> " + userID + "\n"); System.out.print(" Creating Statement...\n"); stmt = con.createStatement (); System.out.print(" Opening ResultsSet...\n"); rset = stmt.executeQuery(queryString); while (rset.next()) { System.out.println(" System.out.println(" rset.getString(1)); System.out.println(" rset.getString(2)); }

Results..."); User Sysdate

System.out.print(" rset.close();

Closing ResultSet...\n");

System.out.print(" stmt.close();

Closing Statement...\n");

} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); if (con != null) { try {

-> " + -> " +

}

con.rollback(); } catch (SQLException e1) { e1.printStackTrace(); }

} finally { if (con != null) { try { System.out.print(" Closing down all connections...\n\n"); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } /** * The following method provides an example of how to connect to a database * using the OCI JDBC Driver. * */ public void jdbcOCIDriver() { Connection con = null; Statement stmt = null; ResultSet rset = null; try { System.out.print("\n"); System.out.print("+-------------------------------+\n"); System.out.print("| USING OCI Driver |\n"); System.out.print("+-------------------------------+\n"); System.out.print("\n"); System.out.print(" Loading JDBC Driver -> " + driverClass + "\n"); Class.forName(driverClass).newInstance(); "\n");

System.out.print("

Connecting to

-> " + connectionURLOCI +

con = DriverManager.getConnection(connectionURLOCI, userID, userPassword); System.out.print(" Connected as -> " + userID + "\n"); System.out.print(" Creating Statement...\n"); stmt = con.createStatement (); System.out.print(" Opening ResultsSet...\n"); rset = stmt.executeQuery(queryString); while (rset.next()) {

System.out.println(" System.out.println(" rset.getString(1)); System.out.println(" rset.getString(2)); }

Results..."); User Sysdate

System.out.print(" rset.close();

Closing ResultSet...\n");

System.out.print(" stmt.close();

Closing Statement...\n");

-> " + -> " +

} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); if (con != null) { try { con.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } } finally { if (con != null) { try { System.out.print(" Closing down all connections...\n\n"); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } /** * Sole entry point to the class and application. * @param args Array of String arguments. * @exception java.lang.InterruptedException * Thrown from the Thread class.

*/ public static void main(String[] args) throws java.lang.InterruptedException { ConnectionExample conExample = new ConnectionExample(); conExample.classForName(); Thread.sleep(5000); conExample.jdbcDriversProperty(); Thread.sleep(5000); conExample.driverManager(); Thread.sleep(5000); conExample.jdbcOCIDriver(); } } ========================= // ----------------------------------------------------------------------------// PasswordManagement.java // ----------------------------------------------------------------------------/* * ============================================================================= * Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved. * * All source code and material located at the Internet address of * http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and * is protected under copyright laws of the United States. This source code may * not be hosted on any other site without my express, prior, written * permission. Application to host any of the material elsewhere can be made by * contacting me at [email protected]. * * I have made every effort and taken great care in making sure that the source * code and other content included on my web site is technically accurate, but I * disclaim any and all responsibility for any loss, damage or destruction of * data or any other property which may arise from relying on it. I will in no * case be liable for any monetary damages arising from such loss, damage or * destruction. * * As with any code, ensure to test this code in a development environment * before attempting to run it in production. * ============================================================================= */ import import import import import import

java.sql.DriverManager; java.sql.Connection; java.sql.Statement; java.sql.ResultSet; java.sql.SQLException; java.sql.SQLWarning;

/** * ----------------------------------------------------------------------------* The following class provides an example of using the new PasswordManagement * and Password Aging features in JDBC for Oracle. * * Password Management first appeared in version 8.1.7 and at that time was * only available in the JDBC OCI driver. From what I can see, these features * will also work with the thin driver in Oracle9i. * * This example provides simple class that connects to the database and looks * for specfic warnings from the Connection object. In this case, we will be * looking for when a user's password is about to expire. Notice what happens * when this user logs into sqlplus with a password that is about to expire: * * % sqlplus scott/tiger * * SQL*Plus: Release 9.0.1.0.1 - Production on Tue Jun 3 23:26:46 2003 * * (c) Copyright 2001 Oracle Corporation. All rights reserved. * * ERROR: * ORA-28002: the password will expire within 2 days * * Connected to: * Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production * With the Partitioning, OLAP and Oracle Data Mining options * JServer Release 9.2.0.3.0 - Production * * SQL> * * Assume that, in this example, we create an Oracle profile named * DEV_PROFILE as follows: * * SQL> CREATE PROFILE dev_profile * 2 LIMIT * 3 PASSWORD_GRACE_TIME 2 PASSWORD_LIFE_TIME 1; * * Profile created. * * SQL> ALTER USER scott PROFILE dev_profile; * * User altered. * ----------------------------------------------------------------------------* @version 1.0 * @author Jeffrey M. Hunter ([email protected]) * @author http://www.idevelopment.info * ----------------------------------------------------------------------------*/ public class PasswordManagement { final static String driverClass = "oracle.jdbc.driver.OracleDriver"; final static String connectionURL = "jdbc:oracle:thin:@localhost:1521:CUSTDB"; final static String userID = "scott"; final static String userPassword = "tiger"; Connection con = null;

/** * Construct a PasswordManagement object. This constructor will create an Oracle * database connection. */ public PasswordManagement() { try { System.out.print(" Loading JDBC Driver -> " + driverClass + "\n"); Class.forName(driverClass).newInstance(); System.out.print(" Connecting to -> " + connectionURL + "\n"); this.con = DriverManager.getConnection(connectionURL, userID, userPassword); System.out.print(" Connected as -> " + userID + "\n"); System.out.print(" Looking for Warnings\n"); SQLWarning sqlw = con.getWarnings(); if (sqlw != null && sqlw.getErrorCode() == 28002) { System.out.println(" WARNING: ORA-28002: Your password will expire soon!"); System.out.println(" MESSAGE: " + sqlw.getMessage()); } System.out.print(" Warning check process completed\n");

} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }

/** * Close down Oracle connection. */ public void closeConnection() { try { System.out.print(" con.close();

Closing Connection...\n");

} catch (SQLException e) { e.printStackTrace();

} } /** * Sole entry point to the class and application. * @param args Array of String arguments. * @exception java.lang.InterruptedException * Thrown from the Thread class. */ public static void main(String[] args) throws java.lang.InterruptedException { PasswordManagement pm = new PasswordManagement(); pm.closeConnection(); } } =================================== // ----------------------------------------------------------------------------// WriteFileToTable.java // ----------------------------------------------------------------------------import import import import import

java.sql.DriverManager; java.sql.Connection; java.sql.Statement; java.sql.ResultSet; java.sql.SQLException;

import java.util.StringTokenizer; import java.io.FileReader; import java.io.BufferedReader; import java.io.IOException; import import import import

java.util.Date; java.text.SimpleDateFormat; java.text.NumberFormat; java.text.ParseException;

/** * ----------------------------------------------------------------------------* The following class provides an example of how to read a simple text file * of records and then insert them into a table in a database. A text file * named Employee.txt will contain employee records to be inserted into the * following table: * * SQL> desc emp * * Name Null? Type * ------------------- -------- -------------* EMP_ID NOT NULL NUMBER * DEPT_ID NUMBER

* NAME NOT NULL VARCHAR2(30) * DATE_OF_BIRTH NOT NULL DATE * DATE_OF_HIRE NOT NULL DATE * MONTHLY_SALARY NOT NULL NUMBER(15,2) * POSITION NOT NULL VARCHAR2(100) * EXTENSION NUMBER * OFFICE_LOCATION VARCHAR2(100) * * NOTE: This example will provide and call a method that creates the EMP * table. The name of the method is called createTable() and is called * from the main() method. * ----------------------------------------------------------------------------*/ public class WriteFileToTable { final static String driverClass = "oracle.jdbc.driver.OracleDriver"; final static String connectionURL = "jdbc:oracle:thin:@localhost:1521:CUSTDB"; final static String userID = "scott"; final static String userPassword = "tiger"; final static String inputFileName = "Employee.txt"; final static String TABLE_NAME = "EMP"; final static String DELIM = ","; Connection con = null; /** * Construct a WriteFileToTable object. This constructor will create an * Oracle database connection. */ public WriteFileToTable() { try { System.out.print(" Loading JDBC Driver -> " + driverClass + "\n"); Class.forName(driverClass).newInstance(); System.out.print(" Connecting to -> " + connectionURL + "\n"); this.con = DriverManager.getConnection(connectionURL, userID, userPassword); System.out.print(" Connected as -> " + userID + "\n"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } /** * Method used to create the initial EMP table. Before attempting to create

* the table, this method will first try to drop the table. */ public void createTable() { Statement stmt = null; try { stmt = con.createStatement(); System.out.print(" Dropping Table: " + TABLE_NAME + "\n"); stmt.executeUpdate("DROP TABLE " + TABLE_NAME); System.out.print("

- Dropped Table...\n");

System.out.print(" stmt.close();

Closing Statement...\n");

} catch (SQLException e) { System.out.print(" - Table " + TABLE_NAME + " did not exist.\n"); } try { stmt = con.createStatement(); System.out.print("

Creating Table: " + TABLE_NAME + "\n");

stmt.executeUpdate("CREATE TABLE emp (" + " emp_id NUMBER NOT NULL " " , dept_id NUMBER " + " , name VARCHAR2(30) NOT " , date_of_birth DATE NOT " , date_of_hire DATE NOT " , monthly_salary NUMBER(15,2) NOT " , position VARCHAR2(100) NOT " , extension NUMBER " + " , office_location VARCHAR2(100))"); System.out.print("

- Created Table...\n");

System.out.print(" stmt.close();

Closing Statement...\n");

+ NULL NULL NULL NULL NULL

" " " " "

+ + + + +

} catch (SQLException e) { e.printStackTrace(); } } /** * Method used to read records from Employee.txt file then write the records * to an Oracle table within the database named "EMP". */ public void performLoadWrite() { Statement stmt = null; int insertResults = 0;

StringTokenizer st = null; String String String String String String String String String

emp_id; dept_id; name; date_of_birth; date_of_hire; monthly_salary; position; extension; office_location;

try { System.out.print(" Creating Statement...\n"); stmt = con.createStatement (); System.out.print(" Create FileReader Object for file: " + inputFileName + "...\n"); FileReader inputFileReader = new FileReader(inputFileName); System.out.print(" Create BufferedReader Object for FileReader Object...\n"); BufferedReader inputStream = new BufferedReader(inputFileReader); String inLine = null; while ((inLine = inputStream.readLine()) != null) { st = new StringTokenizer(inLine, DELIM); emp_id = st.nextToken(); dept_id = st.nextToken(); name = st.nextToken(); date_of_birth = st.nextToken(); date_of_hire = st.nextToken(); monthly_salary = st.nextToken(); position = st.nextToken(); extension = st.nextToken(); office_location = st.nextToken(); System.out.print("

Inserting value for [" + name + "]\n");

insertResults = stmt.executeUpdate( "INSERT INTO " + TABLE_NAME + " VALUES (" + emp_id + " , " + dept_id + " , '" + name + "'" + " , '" + date_of_birth + "'" + " , '" + date_of_hire + "'" + " , " + monthly_salary + " , '" + position + "'" + " , " + extension + " , '" + office_location + "')"); }

System.out.print("

" + insertResults + " row created.\n");

System.out.print(" con.commit();

Commiting Transaction...\n");

System.out.print(" Closing inputString...\n"); inputStream.close(); System.out.print(" stmt.close();

Closing Statement...\n");

} catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * Method used to query records from the database table EMP. This method * can be used to verify all records have been correctly loaded from the * example text file "Employee.txt". */ public void queryRecords() { Statement ResultSet int int int int String String Date String Date float String int String

stmt rset deleteResults rowNumber

= = = =

null; null; 0; 0;

emp_id; dept_id; name; date_of_birth; date_of_birth_p; date_of_hire; date_of_hire_p; monthly_salary; position; extension; office_location;

try { SimpleDateFormat formatter NumberFormat defaultFormat NumberFormat.getCurrencyInstance();

= new SimpleDateFormat("yyyy-MM-dd"); =

System.out.print(" Creating Statement...\n"); stmt = con.createStatement (); "...\n");

System.out.print("

Opening query for table: " + TABLE_NAME +

rset = stmt.executeQuery ("SELECT * FROM emp ORDER BY emp_id"); while (rset.next ()) {

rowNumber = rset.getRow(); emp_id = rset.getInt(1); if ( rset.wasNull() ) {emp_id = -1;} dept_id = rset.getInt(2); if ( rset.wasNull() ) {dept_id = -1;} name = rset.getString(3); if ( rset.wasNull() ) {name = "";} date_of_birth = rset.getString(4); if ( rset.wasNull() ) {date_of_birth = "1900-01-01";} try { date_of_birth_p = formatter.parse(date_of_birth); } catch (ParseException e) { date_of_birth_p = new Date(0); } date_of_hire = rset.getString(5); if ( rset.wasNull() ) {date_of_hire = "1900-01-01";} try { date_of_hire_p = formatter.parse(date_of_hire); } catch (ParseException e) { date_of_hire_p = new Date(0); } monthly_salary = rset.getFloat(6); if ( rset.wasNull() ) {monthly_salary = 0;} position = rset.getString(7); if ( rset.wasNull() ) {position = "";} extension = rset.getInt(8); if ( rset.wasNull() ) {extension = -1;} office_location = rset.getString(9); if ( rset.wasNull() ) {office_location = "";} System.out.print( "\n" + " RESULTS -> [R" + rowNumber + "] " + "\n" + " Employee ID : " + emp_id + "\n" + " Department ID : " + dept_id + "\n" + " Employee Name : " + name + "\n" + " D.O.B. : " + date_of_birth_p + "\n" + " Date of Hire : " + date_of_hire_p + "\n" +

" Monthly Salary : " + defaultFormat.format(monthly_salary) + "\n" + " Position : " + position + "\n" + " Extension : x" + extension + "\n" + " Office Location : " + office_location + "\n"); } System.out.print(" rset.close();

Closing ResultSet...\n");

System.out.print(" stmt.close();

Closing Statement...\n");

} catch (SQLException e) { e.printStackTrace(); } }

/** * Close down Oracle connection. */ public void closeConnection() { try { System.out.print(" con.close();

Closing Connection...\n");

} catch (SQLException e) { e.printStackTrace(); } } /** * Sole entry point to the class and application. * @param args Array of String arguments. * @exception java.lang.InterruptedException * Thrown from the Thread class. */ public static void main(String[] args) throws java.lang.InterruptedException { WriteFileToTable runExample = new WriteFileToTable(); runExample.createTable(); runExample.performLoadWrite(); runExample.queryRecords(); runExample.closeConnection(); }

} =================