Java package: java.sql

Example: connect to an Oracle Database

oraConnection.java

A simple class whose sole purpose is to use use the jdbc.oracle.oci driver to return a connection to a Oracle.

public class oraConnection {

   public static java.sql.Connection get() throws java.sql.SQLException {

      try {
         return java.sql.DriverManager.getConnection("jdbc:oracle:oci:rene/rene@ora19");
      }
      catch (Exception e) {
         System.out.println(e);
         System.exit(-1);
         return null; // Never reached
      }
   }
}

Github repository JavaClasses, path: /java/sql/oraConnection.java

selectTest.java

Select username (to verify with whose account I am connected) and sysdate.

//  & "$env:ORACLE_HOME\jdk\bin\javac"  -cp "$env:ORACLE_HOME\jdbc\lib\ojdbc8.jar;." ./oraConnection.java ./selectTest.java
//  & "$env:ORACLE_HOME\jdk\bin\java"   -cp "$env:ORACLE_HOME\jdbc\lib\ojdbc8.jar;."  selectTest

public class selectTest {

   public static void main(String[] args) {

      try {

         java.sql.Connection conn = oraConnection.get();

         java.sql.Statement sqlStatement=  conn.createStatement();
         String readRecordSQL  = "select user, sysdate from dual";
         java.sql.ResultSet myResultSet =  sqlStatement.executeQuery(readRecordSQL);

         while (myResultSet.next()) {
            System.out.println("User:    " + myResultSet.getString("USER"   ));
            System.out.println("Sysdate: " + myResultSet.getString("SYSDATE"));
         }

          myResultSet.close();
          conn.close();
      }
      catch (Exception e) {
          System.out.println(e);
      }
   }
}

Github repository JavaClasses, path: /java/sql/selectTest.java

Inserting a CLOB into an Oracle database

The following example inserts the content of files as CLOBs.

// Create destination table:
//
//    create table files (
//       name varchar(260) primary key,
//       text clob
//    );
//
// & "$env:ORACLE_HOME\jdk\bin\javac"  -cp "$env:ORACLE_HOME\jdbc\lib\ojdbc8.jar;." ./oraConnection.java ./insertClob.java
// & "$env:ORACLE_HOME\jdk\bin\java"   -cp "$env:ORACLE_HOME\jdbc\lib\ojdbc8.jar;."  insertClob  wordlist.10000.txt  insertClob.java

public class insertClob {

   public static void main(String[] args) {

      try {

         if (args.length < 1) {
            System.out.println("Specify filenames to load");
            System.exit(-1);
         }

         java.sql.Connection conn = oraConnection.get();

         String sql = "insert into files(name, text) values (?, ?)";
         java.sql.PreparedStatement stmt = conn.prepareStatement(sql);

         for (String fileName: args) {
            insertTextFileIntoClob(stmt, fileName);
         }

         conn.close();

        } catch (Exception e) {
            System.out.println(e);
        }
   }

   private static void insertTextFileIntoClob(java.sql.PreparedStatement stmt, String filename) {

      try {

         String fileContent = slurpFile(filename);

         java.io.Reader reader = new java.io.StringReader(fileContent);
         stmt.setString(1, filename);
         stmt.setCharacterStream(2, reader, fileContent.length());
         stmt.executeUpdate();
      }
      catch (java.sql.SQLException ex) {
         System.out.println("Problem: " + ex.toString());
      }
    }

   private static String slurpFile(String fileName) {
      byte a[] = null;
      try {
               java.io.InputStream fso = new java.io.FileInputStream(fileName);
               a = new byte[fso.available()];
               fso.read(a);
               fso.close();
      }
      catch(Exception e) {
         e.printStackTrace();
      }

      return (new String( a,0,a.length));
   }
}

Github repository JavaClasses, path: /java/sql/insertClob.java

Select CLOBs

The following example selects the CLOBS that were inserted with the previous example.

// & "$env:ORACLE_HOME\jdk\bin\javac"  -cp "$env:ORACLE_HOME\jdbc\lib\ojdbc8.jar;." ./oraConnection.java ./selectClob.java
// & "$env:ORACLE_HOME\jdk\bin\java"   -cp "$env:ORACLE_HOME\jdbc\lib\ojdbc8.jar;."  selectClob

public class selectClob {

   public static void main(String[] args) {

      try {

         java.sql.Connection        conn = oraConnection.get();
         java.sql.PreparedStatement stmt = conn.prepareStatement("select * from files");
         java.sql.ResultSet         rs   = stmt.executeQuery();

         while (rs.next()) {

            String        fileName = rs.getString("NAME");
            java.sql.Clob clob     = rs.getClob  ("TEXT");

            fillFile(fileName, clob.getSubString(1, (int) clob.length()));
         }


         conn.close();

        } catch (Exception e) {
            System.out.println(e);
        }
   }

   private static void fillFile(String fileName, String content) {

      try {

         java.io.FileWriter fwr = new java.io.FileWriter(fileName);
         fwr.write(content);
         fwr.close();
         System.out.println(fileName);
      }
      catch (java.io.IOException ex) {
         System.out.println("Exception: " + ex.toString());
      }
   }
}

Github repository JavaClasses, path: /java/sql/selectClob.java

Can't load AMD 64-bit .dll on a IA 32-bit platform

When I tried to execute the first example on this page, I received this error: *Exception in thread "main" java.lang.UnsatisfiedLinkError: C:\Oracle\19\bin\ocijdbc19.dll: Can't load AMD 64-bit .dll on a IA 32-bit platform

This was because my PATH variable pointed to an (apparently 32-bit version) JDK that was not located under %ORACLE_HOME%\jdk.

In order to fix this eror, I had to explicitly use the javac and java executables under %ORACLE_HOME%\jdk.