You wish to insert or update data, create a new table, delete a table, or otherwise change the database.
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-10 converts the
User
database from
the text file format of Section 20.2 into a
relational database. Note that I destroy the table before creating
it, just in case there was an older version in place. If there was
not, executeUpdate( )
simply indicates this 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-10. 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 ( " + "name char(12) PRIMARY KEY, " + "password char(20), " + "fullName char(30), " + "email char(60), " + "city char(20), " + "prov char(20), " + "country char(20), " + "privs int " + ")"); 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 Section 20.6.
3.137.212.124