JDBC

Last updated April 29, 2017

Last Updated: April 29, 2017.

1. A small example program using JDBC to connect to a database.

   import java.io.*;                   // I/O classes and exceptions
   import java.net.*;                  // TCP/IP libraries
   import java.sql.*;
   
   /***
    * A tiny JDBC example.
    * @author mitch fincher,
   **/
   public class SimpleJDBCExample {
     static Connection connection = null;
     static Statement stmt = null;
     
   /***
    * 
    * @param login a valid login for the database
    * @param password the valid password for the login
   **/
   public SimpleJDBCExample (String login, String password)
     {
       // executeUpdates are used for sql which return no rows
       String url = "jdbc:ff-microsoft://";     
       String DatabaseName = "test1";
       String DatabaseHost = "mfincher";
       try 
         {
   	Class.forName("connect.microsoft.MicrosoftDriver");
   	connection = DriverManager.getConnection("jdbc:ff-microsoft://" + 
   			DatabaseHost + ":1433/" + 
   			DatabaseName, login, password);
   	stmt = connection.createStatement();
   	DatabaseMetaData dbmd = connection.getMetaData();
         }
       catch(Exception ex)
         {
   	System.out.println("SimpleJDBCExample: " + ex);
   	ex.printStackTrace();
         }
     }
   /***
    * Example to show JDBC
    * @param argv - login, password
   **/
   public static void main(String argv[])
     {
       if(argv.length < 2)
         {
   	System.out.println("usage: SimpleJDBCExample loginname, password");
   	System.exit(1);
         }
       
       SimpleJDBCExample app = new SimpleJDBCExample(argv[0],argv[1]);
       try 
         {
   	stmt.executeUpdate("Drop TABLE names");
   	stmt.executeUpdate("CREATE TABLE names (id INT IDENTITY, FirstName CHAR(15), LastName CHAR(15))");
   	stmt.executeUpdate("INSERT INTO  names (FirstName, LastName) VALUES('Jane','Smith')");
   	stmt.executeUpdate("INSERT INTO  names (FirstName, LastName) VALUES('Hannah','Kawai')");
   	ResultSet results = stmt.executeQuery("SELECT FirstName,LastName,id FROM names ORDER BY LastName");
   	if(results != null)
   	  {
   	    String FirstName,LastName;
   	    int id;
   	    while(results.next())
   	      {
   		FirstName = results.getString(1);
   		LastName = results.getString(2);
   		id = results.getInt(3);
   		System.out.println(id+", "+LastName+", "+FirstName);
   	      }
   	  }
   	stmt.close();
   	connection.close();
         }
       catch(Exception ex)
         {
   	System.out.println("SimpleJDBCExample: " + ex);
   	ex.printStackTrace();
         }
     } // function main()
   } // SimpleJDBCExample
   
   
   

2. Example of Printing all the contents of any table:

   import java.io.*;                   // I/O classes and exceptions
   import java.net.*;                  // TCP/IP libraries
   import java.sql.*;
   
   /***
    * A tiny JDBC example.
    * @author mitch fincher, 
   **/
   public class SimpleJDBCExample2 {
     static Connection connection = null;
     static Statement stmt = null;
     
   /***
    * 
    * @param login a valid login for the database
    * @param password the valid password for the login
   **/
   public SimpleJDBCExample2 (String login, String password)
     {
       // executeUpdates are used for sql which return no rows
       String url = "jdbc:ff-microsoft://";     
       String DatabaseName = "test1";
       String DatabaseHost = "mfincher";
       try 
         {
   	Class.forName("connect.microsoft.MicrosoftDriver");
   	connection = DriverManager.getConnection("jdbc:ff-microsoft://" + 
                 DatabaseHost + ":1433/" + DatabaseName, login, password);
   	stmt = connection.createStatement();
   	DatabaseMetaData dbmd = connection.getMetaData();
         }
       catch(Exception ex)
         {
   	System.out.println("SimpleJDBCExample2: " + ex);
   	ex.printStackTrace();
         }
     }
   /***
    * Example to show JDBC
    * @param argv - login, password
   **/
   public static void main(String argv[])
     {
       if(argv.length < 2)
         {
   	System.out.println("usage: SimpleJDBCExample2 loginname, password");
   	System.exit(1);
         }
       
       SimpleJDBCExample2 app = new SimpleJDBCExample2(argv[0],argv[1]);
       try 
         {
   	String myString="";
   	String sqlQuery = "SELECT * FROM names";
   	ResultSetMetaData rsmd = null;
   	ResultSet result = stmt.executeQuery(sqlQuery);
   	int NumberOfColumns=0;
   System.out.println("result: " + result);
   
   	if(result != null)
   	  {
   	    rsmd = result.getMetaData();
   	    NumberOfColumns  = rsmd.getColumnCount();
   	  }
   	else
   	  myString += "No Rows in table";
   
   	int row_num=1;	
   	while( (result != null) && result.next())
   	  {
   	    myString += "\n******* Row " + row_num++ + " *********";
   	    for(int column = 1; column<=NumberOfColumns; column++)
   	      {
   		myString += "\n   " + rsmd.getColumnName(column);
   		myString += " = " + result.getString(column);
   	      }
   	  }
   	System.out.println("myString: " + myString);
   	result.close();
         }
       catch(Exception ex)
         {
   	System.out.println("SimpleJDBCExample2: " + ex);
   	ex.printStackTrace();
         }
     } // function main()
   } // SimpleJDBCExample2
   
   
   
   

   Results:
   ******* Row 1 *********
      id = 1
      FirstName = Jane
      LastName = Smith
   ******* Row 2 *********
      id = 2
      FirstName = Hannah
      LastName = Kawai
   

3. a very simple jdbc resultset code fragment

   Vector vector = new Vector();
   String sqlQuery = "SELECT * FROM myTable WHERE myKey = " + myKey;
   Statement stmt = connection.createStatement();
   ResultSet result = stmt.executeQuery(sqlQuery);
   try {
     while( (result != null) && result.next())
       vector.addElement(result.getString(1));
      }
   catch(Exception ex){ System.err.println("" + ex);}      
   

4. Catch chained SQLExceptions.

   catch  (SQLException ex)
    {
      int i=0;
      do 
        {
          System.out.println("**********\nSQLException Number: " + i++);
          System.out.println("ex.getMessage():" + ex.getMessage());
          System.out.println("ex.getSQLState(): " + ex.getSQLState() );
          System.out.println("ex.getErrorCode(): " + ex.getErrorCode() );
          ex.printStackTrace();
        } while ( (ex = ex.getNextException()) != null);
   

5. to access stored procedures

   String queryString = "{call sp_mystored_procedure(?,?,?)}";
   CallableStatement statement = connection.prepareCall(queryString);
   statement.setString(1, <first argument> );
   statement.setString(2, <second argument> );
   statement.setString(3, <third argument> );
   
   boolean resultP = statement.execute();
   if(resultP)
       {
          ResultSet result = statement.getResultSet();
       }
   else
      ...
   

6. example how to get values returned from a stored procedure with output parameters (From Ken Beckett)

   
   CallableStatement cs = c.prepareCall("{call sp_insert_testitem_all(?,?,?,?,?,?)}");
   
   cs.setString(1, which);
   cs.setString(2, name);
   cs.setString(3, path);
   cs.setInt(4, 1);
   cs.setInt(5, 0);
   cs.registerOutParameter(6, java.sql.Types.INTEGER);
   
   cs.execute();
   iid = cs.getInt(6);