Database operation in Java, part 2

By Darrin Koltow, for TopHost.gr, a Greek web hosting company specializing in shared and reseller hosting.

This is the second of two articles on Java database operations. We set up our development environment in the last article, and also sample data and a program to execute SQL queries on that data. We're now ready to try out that program.

Run the program

Return to the NetBeans IDE and press "Shift-F6" to run the program. Watch carefully for the output in the Output window. You should see "star wars" displayed in the window. That text is a result of the SQL "SELECT" statement in your Java code.

It's encouraging to see some evidence of database connectivity in your program, but we only printed one field from one row of the movies table. Let's print the whole table using the following code. Paste this code after the line "ResultSet rs = sm.executeQuery(strq);"

String content = "";
while (rs.next()) {
    content += rs.getString("title") + " ";
    content += rs.getString("director") + " ";
    content += Integer.toString(rs.getInt("year_released")) + "n";
}
System.out.println(content);

Delete the statement block that begins with "if ( rs.next() ) {" Then, re-run the program. Notice the output now displays the full contents of the movies table.

Run an UPDATE statement

SQL SELECT statements are only one of several kinds of statements you can execute on a database. Let's see how an UPDATE statement works on the movies table.

Replace the code inside the "try-catch" statement block, with this code:

try {
    Connection cx = DriverManager.getConnection("JDBC:ODBC:movies");
    Statement sm = cx.createStatement();
    String strq = "UPDATE movies SET title='Star Wars:a New Hope' where title = 'star wars';";
    int nr = sm.executeUpdate(strq);
    System.out.printf("%d rows were affected.%n", nr);
    cx.commit();
    cx.close();
} catch(SQLException e) {
    System.out.println(e);
}

Execute the code using the foregoing instructions (press "Shift-F6"). Notice the output in the NetBeans output window: "1 rows were affected." Open up the movies database in Access to verify that result. You'll notice the record with "star wars" in the title has changed.

Here's an important note about doing action queries--UPDATES, INSERTS and DELETES--on databases: remember to execute the connection object's commit() method. This method says to the database driver, "Yes, I really do want to do this update/delete/insert." If you omit commit(), the data changes you're looking for might not happen.

Do an INSERT

Try out an INSERT statement by replacing the UPDATE statement in the foregoing program, with this statement:

String strq = "insert into movies (title, director, year_released) VALUES ('Mymovie', 'me', '2010');";

After running the file (by pressing "Shift-F6") open up the movies database in Access and verify that the new table row was inserted.

References MundayWeb: JDBC-ODBC Tutorial