JDBC

Last Updated:.

  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);