Home: www.vipan.com Vipan Singla e-mail: vipan@vipan.com
Accessing Databases through Java Programs

Road to JDBC

  • You WILL need access to a database to actually run your code. Popular ones to start with are Microsoft Access and MySQL.

    MySQL is available for free, is easy to install and hardly takes any disk space or computer memory at all. Moreover, it is a commercial grade database available for free and used through out the internet. It is not an industrial strength database, however. It does not do transactions and subqueries right out of the box, although there are ways around it. In addition, it locks the whole table whenever one user is trying to write to that table, not just the row(s) the user is trying to update or insert. Everybody else is free to read as much as they want though. It makes a great database which is mostly for reading, with sporadic writing. On the plus side, it is one of the fastest databases around, for the very reason that transactions, subqueries and row-level locking were intentionally left out. You should not need transactions unless you are trying to TRANSFER money around - playing with money in one place is fine.

    PostgreSQL is another free database program. You have to download the source code and compile it yourself for your operating system. It supports transactions and other things that MySQL does not.

    Microsoft Access of course is a desktop database program not meant to be accessed over the internet or any network for that matter. It crashes all the time if multiple users are trying to access it. It is good for getting your feet wet if you don't know nothing about no databases. It has a nice GUI and help system to make tables, queries and reports. A lot of small businesses use it as a standalone reporting system. It is of little practical use with JDBC although JDBC can access it.

    Oracle, SQL Server (from Microsoft, based on Sybase), DB2 (from IBM), Sybase and Informix are the big and popular commercial databases in use.

A Sample Java Program to Exercise a Database

// package dbtest;

import java.net.URL;
import java.sql.*;
import java.io.PrintWriter;

class TestDatabase
{
   public static void main (String args[])
   {
      try
      {
         // Load driver's class, inilialize, register with DriverManager
         //Class.forName("oracle.jdbc.driver.OracleDriver");
         Class.forName("org.gjt.mm.mysql.Driver");
      }
      catch (ClassNotFoundException e)
      {
         System.out.println("Unable to load driver class");
         return;
      }

      ResultSet rs = null;

      try
      {
         //
         // Call DriverManager's methods (all are static)
         //

         // To print log on sysout
         //DriverManager.setLogStream(System.out); //JDBC 1.x driver
         DriverManager.setLogWriter(new PrintWriter(System.out) );

         // Connect to database.  DriverManager loads each registered driver
         // in turn until one can handle the database URL format
         //Connection con = DriverManager.getConnection(
         //                     "jdbc:oracle:thin:@host.domain.com:1521:db_name"
         //                     ,"scott","tiger");
         Connection con = DriverManager.getConnection(
                              "jdbc:mysql://localhost:3306/civil2","","");

         // Check for warnings generated during connect
         areWarnings(con.getWarnings() );

         // Get the DatabaseMetaData object to display database info
         DatabaseMetaData dmd = con.getMetaData();

         // Display overall database info
         System.out.println( "\nConnected to " + dmd.getURL()
            + " using driver = " + dmd.getDriverName() + ", version = "
            + dmd.getDriverVersion() );

         System.out.println(
            "\nVendor product name is " + dmd.getDatabaseProductName()
            + ".\n\tDatabase software version is "
            + dmd.getDatabaseProductVersion()
            + ".\n\tUser name is " + dmd.getUserName()
            + ".\n\tCatalog is called " + dmd.getCatalogTerm()
            + ".\n\tSchema is called " + dmd.getSchemaTerm()
            + ".\n\tProcedure is called " + dmd.getProcedureTerm()
            + "." );

         System.out.println(
            "\nNumeric functions are:\n" + dmd.getNumericFunctions()
            + "\nString functions are:\n" + dmd.getStringFunctions()
            + "\nDate and Time functions are:\n" + dmd.getTimeDateFunctions()
            + "\nSystem functions are:\n" + dmd.getSystemFunctions()
            + "\n" );

         System.out.println(
            "Use the escape string \"" + dmd.getSearchStringEscape()
            + "\" to escape wildcard characters." );

         System.out.println(
            "Is the database in read only mode? Answer: " + dmd.isReadOnly()
            + "" );

         // Create a Statement object for submitting SQL commands to driver
         Statement stmt = con.createStatement();

         String sql;

         //DROP TABLE statement
         sql = "DROP TABLE users";
         try
         {
            stmt.executeUpdate(sql);  //throws SQLExecption if fails
            System.out.println("Dropped table \"users\"!");
         }
         catch(SQLException ex)
         {
            System.out.println("Could not drop table \"users\"!");
            printException(ex);
         }
         areWarnings(stmt.getWarnings() );

         //CREATE TABLE statement
         sql = "CREATE TABLE users ("
               + "login VARCHAR(30) PRIMARY KEY NOT NULL,"
               + "password VARCHAR(100),"
               + "first VARCHAR(50),"
               + "middle VARCHAR(50),"
               + "last VARCHAR(50),"
               + "nickname VARCHAR(30),"
               + "email VARCHAR(100),"
               + "city VARCHAR(50),"
               + "state VARCHAR(50),"
               + "country VARCHAR(50),"
               + "zip VARCHAR(30)"
               + ")";

         try
         {
            stmt.executeUpdate(sql); //throws SQLExecption if fails
            System.out.println("Created table \"users\"!");
         }
         catch(SQLException ex)
         {
            printException(ex);
         }
         areWarnings(stmt.getWarnings() );

         //
         // More Database meta data
         //

         // What type of tables (tables, views etc.)?
         rs = dmd.getTableTypes();
         printResultSet(rs);
         rs.close();

         // Any catalogs?
         rs = dmd.getCatalogs();
         printResultSet(rs);
         rs.close();

         // What are the defined schemas?
         rs = dmd.getSchemas();
         printResultSet(rs);
         rs.close();

         // Good One:  What are the defined tables?
         // Last arg is an array of strings (table types to get)
         //String[] tp = {"TABLE"};
         //rs = dmd.getTables(null, null, "addr%", null);
         rs = dmd.getTables(null, null, null, null);
         printResultSet(rs);
         rs.close();

         // For a table, what are the defined indexes?
         rs = dmd.getIndexInfo(null, null, "users", false, true);
         printResultSet(rs);
         rs.close();

         // For a table, what columns nake up its primary key
         rs = dmd.getPrimaryKeys(null, null, "users");
         printResultSet(rs);
         rs.close();

         // Any stored procedures defined?
         rs = dmd.getProcedures(null, null, null);
         printResultSet(rs);
         rs.close();

         // The connected user has these table privileges
         rs = dmd.getTablePrivileges(null, null, null);
         printResultSet(rs);
         rs.close();

         // The connected user has these column privileges
         rs = dmd.getColumnPrivileges(null, null, null, null);
         printResultSet(rs);
         rs.close();

         // Get all the tables with their column definitions
         rs = dmd.getColumns(null, null, null, null);
         printResultSet(rs);
         rs.close();


         // INSERT statement (UPDATE is similar)
         sql = "INSERT INTO users ("
                  + "login, password, nickname, "
                  +  "last, first, middle, email,"
                  +  "city, state, country, zip"
               + ") VALUES ("
                  + "'superman', 'foo has a meaning', 'S',"
                  + "NULL, NULL, NULL, 'iam@your.place',"
                  + "NULL, NULL, NULL, '56735-6453'"
               + ")";

         stmt.executeUpdate(sql);   //throws SQLExecption if fails
         areWarnings(stmt.getWarnings() );

         // Submit a query, driver hands back a ResultSet object
         sql = "SELECT * FROM users WHERE login IS NOT NULL";
         rs = stmt.executeQuery(sql); //throws SQLExecption if fails
         areWarnings(stmt.getWarnings() );
         // Process query results.
         printResultSet(rs);
         // Tell Java you are done and Java may release memory
         rs.close();

         // DELETE statement
         sql = "DELETE FROM users WHERE login IS NOT NULL";
         stmt.executeUpdate(sql);   //throws SQLExecption if fails
         areWarnings(stmt.getWarnings() );

         // Good people close explicitly to release db resources
         stmt.close();
         con.close();
      }
      catch(SQLException ex){printException(ex); }
   }


   //
   // Work with ResultSetMetaData
   //

   private static void printResultSet(ResultSet rs) throws SQLException
   {
      ResultSetMetaData rsmd = rs.getMetaData();
      int numCols = rsmd.getColumnCount();

      // Display column titles
      System.out.println("-----------------------------------------");
      for(int i=1; i<=numCols; i++)
      {
         if(i > 1) System.out.print(",");
         System.out.print(rsmd.getColumnLabel(i));
      }
      System.out.println("");
      System.out.println("-----------------------------------------");

      // Display data, fetching until end of the result set
      // Calling next moves to first or next row and returns true if success
      while(rs.next() )
      {
         // Each rs after next() contains next rows data
         for(int i=1; i<=numCols; i++)
         {
            if(i > 1) System.out.print(",");
            // Almost all SQL types can be cast to a string by JDBC
            System.out.print(rs.getString(i));
            areWarnings(rs.getWarnings() );
         }
         System.out.println("");
      }
   }


   //
   // Check for warnings
   //
   private static boolean areWarnings(SQLWarning warn) throws SQLException
   {
      boolean rc = false;

      // Multiple warning smay have been chained to the passed SQLWarning object
      // Or, null may have been passed if there were no warnings
      if(warn != null)
      {
         System.out.println("\n *** Warning ***\n");
         rc = true;
         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 ();
         }
      }
      return rc;
   }

   //
   //  Print all chained exceptions
   //
   public static void printException(SQLException ex)
   {
      // Multiple error objects may be chained together
      System.out.println("\n*** SQLException caught ***\n");
      while(ex != null)
      {
         System.out.println("SQLState: " + ex.getSQLState() );
         System.out.println("Message:  " + ex.getMessage() );
         System.out.println("Vendor:   " + ex.getErrorCode ());
         ex.printStackTrace(System.out);
         System.out.println ("");
         // Get next error object in chain. null if none.
         ex = ex.getNextException();
      }
   }

}

Step-By-Step

  1. Load the database driver using
    Class.forName("Vendor-driver's class name here");
  2. Connect to the database using vendor-specified connection string:
    Connection con = DriverManager.getConnection("jdbc:dbVendorHere:dbNameHere");
  3. Instantiate a "Statement" object.
    For simple and infrequent SELECT, INSERT, UPDATE, DELETE:
    Statement stm =con.createStatement();
    For complex, variable-rich and frequent SELECT, INSERT, UPDATE, DELETE:
    PreparedStatement stm = con.prepareStatement("some sql command here");
    For stored procedures (or use SQL escape sequences in PreparedStatement:
    CallableStatement stm = con.prepareCall("some vendor-specific sql here");
  4. Execute the SQL command.
    For SELECT:
    ResultSet rs = stm.executeQuery(...); Then work with the "ResultSet" object to retrieve data using:
    while rs.next()
    {
       String blah = rs.getString("column_name"|column_position);
       int cu = rs.getInt(...);
    }
    For INSERT, UPDATE and DELETE (sometimes CREATE|DROP|ALTER TABLE|INDEX|VIEW):
    int returnVal = stm.executeUpdate(...); Returned integer contains number of rows affected.
  5. Check for any warnings using getWarnings() on "con", "stm" and "rs" objects.
  6. Release your hold on the database resources by calling rs.close(); stm.close(); con.close();

Detailed Look at Database Access

Java Equivalents of SQL
SQL TYPE Java Class ResultSet "getXXX" Method Remarks
CHAR String rs.getString() Text (Fixed length storage upto 254 characters)
VARCHAR String rs.getString() Text (Variable length storage upto 254 characters)
SMALLINT short rs.getShort() -32767 to +32767. Don't use TINYINT or BIT.
INTEGER or INT int rs.getInt() -2147483647 to +2147483647
BIGINT long rs.getLong() Use only if already in database
REAL float rs.getFloat() Upto 7 decimal places
FLOAT, DOUBLE PRECISION or DOUBLE double rs.getDouble() Upto 15 decimal places
NUMERIC, DECIMAL java.math.BigDecimal rs.getBigDecimal() e.g. 92375.456 (Precision = 8; Scale = 3). NUMERIC always has fixed scale, DECIMAL has variable scale beyond specified
DATE java.sql.Date rs.getDate() yyyy-mm-dd, support varies
TIME java.sql.Time rs.getTime() hh:mm:ss, support varies
TIMESTAMP java.sql.Timestamp rs.getTimestamp() Allows nano-seconds. Maps to DATE in Oracle, DATETIME in SQL Server and Informix, TIMESTAMP in DB2 and Sybase
BOOLEAN, BIT boolean rs.getBoolean() True or False
BINARY, VARBINARY byte[] getBytes() Raw binary data upto 254 bytes
BLOB, LONGVARBINARY Blob, byte[] getBinaryStream(), getBlob() Raw binary data upto 2GB (images, serialized objects)
CLOB, LONGVARCHAR Clob, String rs.AsciiStream(), getCharacterStream(), getClob() Text up to 2GB. getUnicodeStream() is deprecated.

Using Middle Tier and Type 3 JDBC Drivers

How to do Transactions

try
{
   con.setAutoCommit(false);
   Statement stm = con.createStatement();
   stm.executeUpdate("UPDATE ...");
   stm.executeUpdate("UPDATE ...");
   stm.executeUpdate("UPDATE ...");
   ...
   con.commit();
}
catch(Exception e)
{
   con.rollback();
}

Weblogic Specific Information

SQL Examples

Generic SQL Functions
Example usage:INSERT INTO test VALUES ({fn function_name(function paramaters)});
Function Purpose
Date and Time Functions
CURDATE Returns the current date
CURTIME Returns the current time
CURRENT_DATE Returns the current date
CURRENT_TIME(time_precision) Returns the current time
CURRENT_TIMESTAMP(timestamp-precision) Returns the local date and local time as a timestamp
DAYNAME(date) Returns a string with the day of the week
DAYOFMONTH(date) Returns the day of the month, between 1 and 31
DAYOFWEEK(date) Returns the day of the week as an integer between 1 and 7
DAYOFYEAR(date) Returns the day of the year between 1 and 366
EXTRACT(extract-field FROM extract-source) Returns YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND field of an extract-source (datetime or interval expression)
HOUR(time_exp) Returns the hour as an integer between 0 and 23
MINUTE(time_exp) Returns the minute as an integer between 0 and 59
MONTH(date) Returns the month as an integer between 1 and 12
MONTHNAME(date) Returns the month name as a string
NOW Returns the current date and time as a timestamp
QUARTER(date) Returns the quarter as an integer between 1 and 4
SECOND(time_exp) Returns the second as an integer between 0 and 59
TIMESTAMPADD (interval,integer_exp,timestamp_exp) Calculates a timestamp by adding integer_exp
intervals of type interval to timestamp_exp.
TIMESTAMPDIFF (interval,timestamp_exp1,timestamp_exp2) Returns the integer number of intervals by which timestamp_exp2 is greater than timestamp_exp1.
WEEK(date) Returns the week of the year as an integer between 1 and 53
YEAR(date) Returns the year as an integer in a data source-dependent range
String Functions (str = string expression)
ASCII(str) Returns the integer equivalent of string str
CHAR(code) Returns the character equivalent of code
BIT_LENGTH(str) Returns the length in bits of a string
CHAR_LENGTH(str) Returns the length of a string expression
CHARACTER_LENGTH(str) Returns the length of a string expression
CONCAT(str1,str2) Concatenates string 2 to string 1
DIFFERENCE(str1,str2) Returns the SOUNDEX difference between strings
INSERT(str1,start,length,str2) Merges strings by deleting length characters from str1 and inserting str2
LCASE(str) Converts string str to lowercase
LEFT(str,count) Returns leftmost count characters of string str
LENGTH(str) Returns the number of characters in str
LOCATE(str1,str2[,start]) Returns starting position of str1 within str2
LTRIM(str) Removes the leading spaces of str
OCTET_LENGTH(str) Returns smallest integer byte length not less than the bit length divided by 8
POSITION(char_exp IN char_exp) Returns the position of the first character expression in the second
REPEAT(str,count) Returns characters of str repeated count times
REPLACE(str1,str2,str3) Replaces occurences of str2 in str1 with str3
RIGHT(str,count) Replaces the rightmost count characters of string str
RTRIM(str) Removes trailing spaces in str
SOUNDEX(str) Returns phonetic equivalent of str
SPACE(count) Returns a string str of count spaces
SUBSTRING(str,start,length) Derives substring from str beginning at start
UCASE(str) Converts str to upper case
Number Functions
ABS(numeric) Returns the absolute value of numeric
ACOS(float) Returns the arccosine of float as an angle expressed in radians.
ASIN(float) Returns the arcsine of float
ATAN(float) Returns the arctangent of float
ATAN2(float1, float2) Returns the arctangent of float1 and float2 as an angle expressed in radians
CEILING (numeric) Returns the smallest integer greater than or equal to numeric
COS(float) Returns the cosine of float
COT(float) Returns the cotangent of float
DEGREES(numeric) Converts numeric radians to degrees
EXP(float) Returns the exponential value of float
FLOOR(numeric) Returns the largest integer less than or equal to numeric
LOG(float) Returns the natural logarithm of float
LOG10(float) Returns the base 10 log of float
MOD(integer1,integer2) Returns the modulus of integer1 divided by integer2
PI Returns pi as a floating point number
POWER(numeric,integer) Returns the value of numeric raised to the power of integer
RADIANS(numeric) Returns the number of radians converted from numeric
RAND(integer) Returns a random floating number using integer as the optional seed value
ROUND(numeric,integer) Returns numeric rounded to integer
SIGN(numeric) Returns the sign of numeric
SIN(float) Returns the sine of float
SQRT(float) Returns the square root of float
TAN(float) Returns the tangent of float
TRUNCATE(numeric,integer) Returns numeric truncated to integer

Keywords to express valid TIMESTAMPADD interval or TIMESTAMPDIFF interval values; fractional seconds are expressed in billionths of a second. 

SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

How to Cache data

© Vipan Singla 2000

The material in this document is my notes on using JDBC. It is not meant to be a tutorial, introduction or cover-all. It is intended to provide some boiler-plate code-snippets or refresh my memory on some concepts.