Writing Data Using JDBC

In most applications in which you need to read data from the database, you often need to insert or update data in the database as well. You can do this using the same manner as before, except that the SQL statement must be changed to actually perform the insert or update. Using the CUSTOMER table for this, you can create a new customer record in the database. Then you can read it back out and update some fields.

As mentioned, the Java classes that you need to use to accomplish this were discussed in the last example. The primary classes that you will need are the Connection, Statement, and ResultSet classes.

Note

If you are using Oracle8i Lite to follow along, go into the ODBC configuration for the POLITE datasource and turn the AutoCommit selection to On. Otherwise, when you write your data, you will have to call commit on it before it will become persistent. You will use this type of feature later, but handle it with JDBC, not an ODBC configuration option. If you are using another database, you are probably already set the correct way.

Listing 26.8 shows the source code for the Customer.java class that is used in the NewCustomerExample.java in Listing 26.9. This example shows how to insert a new Customer record into the database using JDBC. Then it updates a field in the new Customer record and updates the record into the database. Listing 26.10 shows the output when the example is executed.

Listing 26.8 Source Code for Customer.java

 public class Customer {   // Default Constructor   public Customer()   {     super();   }   // Private Instance Variables   private String id;   private String name;   private String city;   private String state;   private String zip;   // Public Accessors   public String getId()   {     return id;   }   public void setId( String custId )   {     id = custId;   }   public void setName( String newName )   {     name = newName;   }   public String getName()   {     return name;   }   public void setCity( String city )   {     this.city = city;   }   public String getCity()   {     return city;   }   public void setState( String state )   {     this.state = state;   }   public String getState()   {     return state;   }   public void setZip( String zip )   {     this.zip = zip;   }   public String getZip()   {     return zip;   }   // Return a String that clients can use to insert an instance of this   // class into the database. public String getInsertSQL()   {     StringBuffer sqlBuf = new StringBuffer( "INSERT INTO CUSTOMER (" );     sqlBuf.append( "CUSTID, NAME, CITY, STATE, REPID, ZIP ) " );     sqlBuf.append( " VALUES ( " );     sqlBuf.append( getId() );     sqlBuf.append( "," );     sqlBuf.append( "'" );     sqlBuf.append( getName() );     sqlBuf.append( "'," );     sqlBuf.append( "'" );     sqlBuf.append( getCity() );     sqlBuf.append( "'," );     sqlBuf.append( "'" );     sqlBuf.append( getState() );     sqlBuf.append( "'," );     sqlBuf.append( 1 );     sqlBuf.append( "," );     sqlBuf.append( "'" );     sqlBuf.append( getZip() );     sqlBuf.append( "')" );     return sqlBuf.toString();   }   // Return a string that clients can use to get all the Customer records   public static String getReadAllSQL()   {     return "SELECT CUSTID, NAME, CITY, STATE, ZIP FROM CUSTOMER";   }  // Return a SQL update String for this instance. Notice that this is done   // very inefficiently because all the fields are being updated regardless of   // whether they have changed. There are better ways to do it   //. Because customer number was assigned, this field will not be updated   // . It would cause big referential integrity problems if   // it was updated.   public String getUpdateSQL()   {     StringBuffer sqlBuf = new StringBuffer( "UPDATE CUSTOMER SET " );     sqlBuf.append( "NAME='" );     sqlBuf.append( getName() );     sqlBuf.append( "', CITY='");     sqlBuf.append( getCity() );     sqlBuf.append( "', STATE='");     sqlBuf.append( getState() );     sqlBuf.append( "', ZIP='");     sqlBuf.append( getZip() );     sqlBuf.append( "'WHERE CUSTID = " );     sqlBuf.append( getId() );     return sqlBuf.toString();   }   // Override the default toString method to output something of your own   public String toString()   {     StringBuffer buf = new StringBuffer();     buf.append( "ID: " );     buf.append( getId() );     buf.append( " Name: " );     buf.append( getName() );     buf.append( " City: " );     buf.append( getCity() );     buf.append( " State: " );     buf.append( getState() );     buf.append( " Zip: " );     buf.append( getZip() );     return buf.toString();   } } 

Listing 26.9 is a class that is used to insert a Customer record into the database. The class gets a database connection and then creates a new instance of a Customer object. Listing 26.8 previously shows the Customer class that is used. After the Customer is inserted into the database, the ZIP code field of the Customer object is modified and an updateCustomer method is called causing the Customer record that inserted previously to be updated with the new ZIP code. Listing 26.10 shows all of the Customer records in the database, including the new one that was just inserted and updated.

Listing 26.9 Source Code for NewCustomerExample.java

 import java.sql.*; public class NewCustomerExample {   // Private Instance variable for the Connection   private Connection conn = null;   // Default Constructor   public NewCustomerExample( Connection connection )   {     super();     conn = connection;   }   public Customer insertNewCustomer( Customer aCustomer ) throws SQLException   {     // Local variable for the Statement object     Statement stmt = null;     ResultSet rs = null;     // Create a Statement object from the Connection     stmt = getConnection().createStatement();     // Get the highest customer id and increment it by 1 for the next id     String maxCustomerNbrSQL = "SELECT MAX(CUSTID) FROM CUSTOMER";     rs = stmt.executeQuery( maxCustomerNbrSQL );     rs.next();     int maxNbr = rs.getInt( 1 );     aCustomer.setId( String.valueOf( maxNbr + 1 ) );     // Ask the Customer for its insert sql statement     String sql = aCustomer.getInsertSQL();     stmt.executeUpdate( sql );     // return the customer instance with the customer number set     return aCustomer;   }   // A method to update an existing customer in the database   public void updateCustomer( Customer cust )   {     // Local variable for the Statement object     Statement stmt = null;     ResultSet rs = null;     try     {       String sql = cust.getUpdateSQL();       // Put a line here so that you can read the output better       System.out.println("");       System.out.println( sql );       // Create a Statement object from the Connection       stmt = getConnection().createStatement();       stmt.executeUpdate( sql );     }     catch( SQLException ex )     {       ex.printStackTrace();     }   }   public void printAllCustomers()   {     // Local variable for the Statement object     Statement stmt = null;     ResultSet rs = null;     try     {       // Create a Statement object from the Connection       stmt = getConnection().createStatement();       // Ask the Customer for its insert sql statement       String sql = Customer.getReadAllSQL();       rs = stmt.executeQuery( sql );       while( rs.next() )       {         Customer cust = new Customer();         String id = rs.getString( 1 );         String name = rs.getString( 2 );         String city = rs.getString( 3 );         // Had to do this because the database type was a char and         // there were too many spaces at the end for it to print out         // and look reasonable. This is a bad thing, but necessary for the         // example here in this book.         if ( city != null )           city = city.substring( 0, 15 );         String state = rs.getString( 4 );         String zip = rs.getString( 5 );         cust.setId( id );         cust.setName( name );         cust.setCity( city );         cust.setState( state );         cust.setZip( zip );         System.out.println( cust.toString() );       }     }     catch( SQLException ex )     {       ex.printStackTrace();     }   }   // Private accessor for the connection   private Connection getConnection()   {     return conn;   }   public static void main(String[] args)   {     try     {       // Use the previous DatabaseManager to acquire a connection       Connection conn = DatabaseManager.getConnection();       NewCustomerExample example = new NewCustomerExample( conn );       // Create a new Customer object to be inserted into the database       // Notice you don't have to populate the customer nbr field because the       // database is set up to do that for you       Customer cust = new Customer();       cust.setName( "Josh Alan" );       cust.setCity( "Snellville" );       cust.setState( "GA" );       cust.setZip( "12345" );       // Call the insert method so that the customer will be inserted       example.insertNewCustomer( cust );       // Print the customer records, including the one just inserted       example.printAllCustomers();       // Update a field in the customer       cust.setZip( "54321" );       example.updateCustomer( cust );       // Print the records out again. The zip for the one that was       // just added should be 54321, not 12345       example.printAllCustomers();       // Always be sure to close the connection when you are finished       conn.close();     }     catch( SQLException ex )     {       ex.printStackTrace();     }     catch( Exception ex )     {       ex.printStackTrace();     }   } } 

Listing 26.10 Output from NewCustomerExample.java

 C:\jdk1.3se_book\classes>java NewCustomerExample ID: 100 Name: JOCKSPORTS                                    City: BELMONT   State: CA Zip: 96711 ID: 101 Name: TKB SPORT SHOP                                City: REDWOOD   CITY    State: CA Zip: 94061 ID: 102 Name: VOLLYRITE                                     City:   BURLINGAME      State: CA Zip: 95133 ID: 103 Name: JUST TENNIS                                   City:   BURLINGAME      State: CA Zip: 97544 ID: 104 Name: EVERY MOUNTAIN                                City:   CUPERTINO       State: CA Zip: 93301 ID: 105 Name: K + T SPORTS                                  City: SANTA   CLARA     State: CA Zip: 91003 ID: 106 Name: SHAPE UP                                      City: PALO   ALTO       State: CA Zip: 94301 ID: 107 Name: WOMENS SPORTS                                 City:   SUNNYVALE       State: CA Zip: 93301 ID: 108 Name: NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER  City: HIBBING   State: MN Zip: 55649 ID: 109 Name: Josh Alan                                     City:   Snellville      State: GA Zip: 12345 UPDATE CUSTOMER SET NAME='Josh Alan', CITY='Snellville', STATE='GA',   ZIP='54321'WHERE CUSTID = 109 ID: 100 Name: JOCKSPORTS                                    City: BELMONT   State: CA Zip: 96711 ID: 101 Name: TKB SPORT SHOP                                City: REDWOOD   CITY    State: CA Zip: 94061 ID: 102 Name: VOLLYRITE                                     City:   BURLINGAME      State: CA Zip: 95133 ID: 103 Name: JUST TENNIS                                   City:   BURLINGAME      State: CA Zip: 97544 ID: 104 Name: EVERY MOUNTAIN                                City:   CUPERTINO       State: CA Zip: 93301 ID: 105 Name: K + T SPORTS                                  City: SANTA   CLARA     State: CA Zip: 91003 ID: 106 Name: SHAPE UP                                      City: PALO   ALTO       State: CA Zip: 94301 ID: 107 Name: WOMENS SPORTS                                 City:   SUNNYVALE       State: CA Zip: 93301 ID: 108 Name: NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER  City: HIBBING   State: MN Zip: 55649 ID: 109 Name: Josh Alan                                     City:   Snellville      State: GA Zip: 54321 

Notice that because an update was done, another "Josh Alan" record was not added, but the ZIP for the existing one was updated and written back out to the database.