December 19, 2014
Hot Topics:

Using More Advanced JDBC Features

  • January 26, 2006
  • By Matt Weisfeld
  • Send Email »
  • More Articles »

Inserting the Record

Now that the table is created, you can proceed to insert some individual records. Rather than hard code all of the information, I decided to pass a couple of parameters so that I can vary the information inserted into the record. In this case, hard-coding the information made testing more difficult. I use the following three lines of code to build the string containing the SQL information for the insertion of the record into the table.

String data = "("+ id + "," + "'" + name + "'" + ");";
String sqlRecord = "insert into tblCustomers values " + data;
System.out.println ("record = " + sqlRecord);

When the passed parameters are as follows, ID = 1 and name = "John Doe", the resulting string will look like this:

"insert into tblCustomers values   (1,'John Doe')"

When this statement is completed successfully, the table will contain a record with this information. The complete method is presented in Listing 7.

Listing 7

public void insertRecord(String id, String name){

   String data = "("+ id + "," + "'" +   name + "'" + ");";

   String sqlRecord = "insert into tblCustomers values " + data;
   System.out.println ("record = " + sqlRecord);

   try {
      DBStatement.executeUpdate (sqlRecord);
      System.out.println ("+++++++++++++++++++");
      System.out.println ("+ record inserted +");
      System.out.println ("+++++++++++++++++++");
   } catch (Exception excep) {
      System.out.println ("Unable to insert record: n" + excep);
      System.exit(0);
   }
}

When you execute these insertions, you see the output presented in Figure 11.

Figure 11

Committing the Changes

As I progressed with the code for this application, one of the more frustrating points was when the statements appeared to be working properly, yet the changes were not showing up in the database. At some point, I realized that I was not performing the basic task of committing the changes. In short, the transactions were being executed but they were not being committed to the database—thus whatever changes I was making were being lost. So, the moral of the story is to make sure that you place the following code into your application:

DBConnection.commit();

The complete method is presented in Listing 8.

Listing 8

public void commitChanges(){
   try {
      DBConnection.commit();
      System.out.println ("+++++++++++++++++++++");
      System.out.println ("+ changes committed +");
      System.out.println ("+++++++++++++++++++++");
   } catch (Exception excep) {
      System.out.println ("Unable to commit changes: n" + excep);
      System.exit(0);
   }
}

When the changes are committed, you can inspect the database again to see that the insertions were actually performed—see Figure 12.

Figure 12

Executing a Query

Perhaps the most complicated code in this example is the code to build and execute a query. The code for this method is presented in Listing 9. There is a detailed description of this code in last month's article. Rather than review the code here, please reference last month's article.

In a nutshell, in this example I hard-coded the following statement:

"select * from tblCustomers"

When executed, this SQL query will return all the records in the tblCustomers table into the result set.

Listing 9

public void executeQuery(){
   try {
      DBResultSet= DBStatement.executeQuery   (
                   "select * from tblCustomers");
      System.out.println ("Query Successful");

      while (DBResultSet.next()) {
         System.out.println ("ID : " + DBResultSet.getInt("CustomerID"));

         System.out.println ("Name : " + DBResultSet.getString("Name"));
         System.out.println ();
      }
   } catch (Exception excep) {
      System.out.println ("Unable to execute query: n" + excep);
      System.exit(0);
   }
}

When you execute the query, the application performs the select statement and returns that contents of the table, as can be seen in Figure 13.

Figure 13

Updating the Record

Now that you have inserted records into the database, one of the important tasks left to perform is to update a record that already exists. To accomplish this, you need to build an SQL transaction and then use the executeUpdate() method. Again, for simplicity you can hardcode an SQL statement that will change the name where the CustomerID = 2 to "Jane Jones". The string looks like this:

"UPDATE tblCustomers SET [Name] = 'Jane Jones' WHERE CustomerID = 2"

The executeUpdate() is used in the following code:

DBStatement.executeUpdate (sqlRecord);

The code for this method is presented in Listing 10.

Listing 10

public void updateRecord(){

   String sqlRecord = 
      "UPDATE tblCustomers SET [Name] = 'Jane Jones' WHERE CustomerID = 2";

   try {
      DBStatement.executeUpdate (sqlRecord);
      System.out.println ("++++++++++++++++++");
      System.out.println ("+ record updated +");
      System.out.println ("++++++++++++++++++");
   } catch (Exception excep) {
      System.out.println ("Unable to update record: n" + excep);
      System.exit(0);
   }
}

When the updates are executed, the output is shown in Figure 14.

Figure 14

And, of course, you can inspect the database to make sure that the change has been made and that Jane Smith is now Jane Doe—see Figure 15.

Figure 15





Page 3 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel