Recipe 20.11 Changing Data Using SQL

Problem

You wish to insert or update data, create a new table, delete a table, or otherwise change the database.

Solution

Instead of using the Statement method executeQuery( ), use executeUpdate( ) with SQL commands to make the change.

Discussion

The executeUpdate( ) method is used when you want to make a change to the database as opposed to getting a list of rows with a query. You can implement either data changes like insert or update, data structure changes like create table, or almost anything that you can do by sending SQL directly to the database through its own update command interface or GUI.

The program listed in Example 20-12 converts the User database from the text file format of Recipe 20.2 into a relational database. Note that I destroy the table before creating it, just in case an older version was in place. If there was not, executeUpdate( ) simply indicates this line in its return code; it doesn't throw an exception. Then the program creates the table and its index. Finally, it goes into a loop reading the lines from the text file; for each, a prepared statement is used to insert the user's information into the database.

Example 20-12. TextToJDBC.java

package jabadot; import java.sql.*; import java.io.*; import java.util.*; /** Convert the database from text form to JDBC form.  */ public class TextToJDBC {     protected final static String TEXT_NAME = "userdb.txt";     protected final static String DB_URL = "jdbc:idb:userdb.prp";     public static void main(String[] fn)     throws ClassNotFoundException, SQLException, IOException {         BufferedReader is = new BufferedReader(new FileReader(TEXT_NAME));         // Load the database driver         Class.forName("jdbc.idbDriver");         System.out.println("Getting Connection");         Connection conn = DriverManager.getConnection(             DB_URL, "ian", "");    // user, password         System.out.println("Creating Statement");         Statement stmt = conn.createStatement( );         System.out.println("Creating table and index");         stmt.executeUpdate("DROP TABLE userdb");         stmt.executeUpdate("CREATE TABLE userdb (\n" +             "name     char(12) PRIMARY KEY,\n" +             "password char(20),\n" +             "fullName char(30),\n" +             "email    char(60),\n" +             "city     char(20),\n" +             "prov     char(20),\n" +             "country  char(20),\n" +             "privs    int\n" +             ")");         stmt.executeUpdate("CREATE INDEX nickIndex ON userdb (name)");         stmt.close( );         // put the data in the table         PreparedStatement ps = conn.prepareStatement(             "INSERT INTO userdb VALUES (?,?,?,?,?,?,?,?)");         String line;         while ((line = is.readLine( )) != null) {             //name:password:fullname:City:Prov:Country:privs             if (line.startsWith("#")) {        // comment                 continue;             }             StringTokenizer st =                 new StringTokenizer(line, ":");             String nick = st.nextToken( );             String pass = st.nextToken( );             String full = st.nextToken( );             String email = st.nextToken( );             String city = st.nextToken( );             String prov = st.nextToken( );             String ctry = st.nextToken( );             // User u = new User(nick, pass, full, email,             //    city, prov, ctry);             String privs = st.nextToken( );             int iprivs = 0;             if (privs.indexOf("A") != -1) {                 iprivs |= User.P_ADMIN;             }             if (privs.indexOf("E") != -1) {                 iprivs |= User.P_EDIT;             }             ps.setString(1, nick);             ps.setString(2, pass);             ps.setString(3, full);             ps.setString(4, email);             ps.setString(5, city);             ps.setString(6, prov);             ps.setString(7, ctry);             ps.setInt(8, iprivs);             ps.executeUpdate( );         }         ps.close( );           // All done with that statement         conn.close( );     // All done with that DB connection         return;           // All done with this program.     } }

Once the program has run, the database is populated and ready for use by the UserDBJDBC data accessor shown in Recipe Recipe 20.7.