D:\work\db\src\Example4.java
1     
2    import java.sql.*; 
3     
4    /** 
5     * 
6     */ 
7    public class Example4 { 
8        public static void main(String args[]) { 
9            Example4 example = new Example4(); 
10           example.run(); 
11       } 
12    
13       private void run() { 
14           Connection con = null; 
15           try { 
16               con = openConnection(); 
17    
18               publishData(con); 
19               showTableContent(con); 
20    
21               publishDataUsingPreparedStatement(con); 
22               showTableContent(con); 
23           } catch (SQLException e) { 
24               System.err.println("Errors occurs when communicating with the database server: " + e.getMessage()); 
25           } catch (ClassNotFoundException e) { 
26               System.err.println("Cannot find the database driver"); 
27           } finally { 
28               // Never forget to close database connection 
29               closeConnection(con); 
30           } 
31       } 
32    
33       /** 
34        * Using prepared statement that is faster than regular statement. 
35        * @param con 
36        * @throws SQLException 
37        */ 
38       private void publishDataUsingPreparedStatement(Connection con) throws SQLException { 
39           PreparedStatement stmt = con.prepareStatement("INSERT INTO info VALUES(?,?,?,?)"); 
40           stmt.setString(1, "Name 1"); 
41           stmt.setString(2, "00000000"); 
42           stmt.setString(3, "250000"); 
43           stmt.setString(4, "My Department"); 
44           stmt.executeUpdate(); 
45           stmt.setString(1, "Name 2"); 
46           stmt.setString(2, "111111"); 
47           stmt.setString(3, "887700"); 
48           stmt.setString(4, "My Department 2"); 
49           stmt.executeUpdate(); 
50           stmt.close(); 
51       } 
52    
53       /** 
54        * This method will first clean up the table and then 
55        * populate it with new data. 
56        * @param con 
57        * @throws java.sql.SQLException 
58        */ 
59       private void publishData(Connection con) throws SQLException { 
60           Statement stmt = con.createStatement(); 
61    
62           System.out.println("Deleting previous tuples ..."); 
63           stmt.executeUpdate("DELETE FROM info"); 
64    
65           System.out.println("Inserting Data ..."); 
66           stmt.executeUpdate("insert into info values ('Clinton, Bill', '9876543210', 400000, 'Ex. White House')"); 
67           stmt.executeUpdate("insert into info values ('Doll, Bob', '1234567890', 100000, 'House')"); 
68           stmt.executeUpdate("insert into info values ('Bush, George', '6543210987', 450000, 'White House')"); 
69           stmt.executeUpdate("insert into info values ('Gore, Al', '3210987654', 200000, 'donno')"); 
70           stmt.executeUpdate("insert into info values ('Bing, Chandler', '4321056789', 750000, 'Friends')"); 
71           stmt.close(); 
72       } 
73    
74       private void showTableContent(Connection con) throws SQLException { 
75           Statement stmt = con.createStatement(); 
76           ResultSet result = stmt.executeQuery("SELECT * FROM info"); 
77    
78           /* 
79           We use ResultSetMetaData.getColumnCount() to know the number columns 
80           that are contained. 
81           */ 
82           ResultSetMetaData meta = result.getMetaData(); 
83           for (int col = 1; col <= meta.getColumnCount(); col++) { 
84               System.out.println("Column" + col + ": " + meta.getColumnName(col) + 
85                                  "\t, Type: " + meta.getColumnTypeName(col)); 
86           } 
87    
88           /* 
89           Every time we call ResultSet.next(), its internal cursor will advance 
90           one tuple. By calling this method continuously, we can iterate through 
91           the whole ResultSet. 
92           */ 
93           while (result.next()) { 
94               for (int col = 1; col <= meta.getColumnCount(); col++) { 
95                   System.out.print("\"" + result.getString(col) + "\","); 
96               } 
97               System.out.println(); 
98           } 
99    
100          /* 
101          It is always a good practice to close a statement as soon as we 
102          no longer use it. 
103          */ 
104          stmt.close(); 
105      } 
106   
107      /** 
108       * 
109       * @return a database connection 
110       * @throws java.sql.SQLException when there is an error when trying to connect database 
111       * @throws ClassNotFoundException when the database driver is not found. 
112       */ 
113      private Connection openConnection() throws SQLException, ClassNotFoundException { 
114          // Load the Oracle database driver 
115          DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
116   
117          /* 
118          Here is the information needed when connecting to a database 
119          server. These values are now hard-coded in the program. In 
120          general, they should be stored in some configuration file and 
121          read at run time. 
122          */ 
123          String host = "shams.usc.edu"; 
124          String port = "1521"; 
125          String dbName = "cs585"; 
126          String userName = "temp"; 
127          String password = "temp585"; 
128   
129          // Construct the JDBC URL 
130          String dbURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName; 
131          return DriverManager.getConnection(dbURL, userName, password); 
132      } 
133   
134      /** 
135       * Close the database connection 
136       * @param con 
137       */ 
138      private void closeConnection(Connection con) { 
139          try { 
140              con.close(); 
141          } catch (SQLException e) { 
142              System.err.println("Cannot close connection: " + e.getMessage()); 
143          } 
144      } 
145  }