D:\work\db\src\Example3.java
1     
2    import java.sql.*; 
3     
4    /** 
5     * This example is based on Example2 
6     */ 
7    public class Example3 { 
8        public static void main(String args[]) { 
9            Example3 example = new Example3(); 
10           example.run(); 
11       } 
12    
13       private void run() { 
14           Connection con = null; 
15           ResultSet result = null; 
16           try { 
17               con = openConnection(); 
18               publishData(con); 
19               result = searchAllTuples(con); 
20               showMetaDataOfResultSet(result); 
21               showResultSet(result); 
22           } catch (SQLException e) { 
23               System.err.println("Errors occurs when communicating with the database server: " + e.getMessage()); 
24           } catch (ClassNotFoundException e) { 
25               System.err.println("Cannot find the database driver"); 
26           } finally { 
27               // Never forget to close database connection 
28               closeConnection(con); 
29           } 
30       } 
31    
32       /** 
33        * This method will first clean up the table and then 
34        * populate it with new data. 
35        * @param con 
36        * @throws SQLException 
37        */ 
38       private void publishData(Connection con) throws SQLException { 
39           Statement stmt = con.createStatement(); 
40    
41           System.out.println("Deleting previous tuples ..."); 
42           stmt.executeUpdate("delete from info"); 
43    
44           System.out.println("Inserting Data ..."); 
45           stmt.executeUpdate("insert into info values ('Clinton, Bill', '9876543210', 400000, 'Ex. White House')"); 
46           stmt.executeUpdate("insert into info values ('Doll, Bob', '1234567890', 100000, 'House')"); 
47           stmt.executeUpdate("insert into info values ('Bush, George', '6543210987', 450000, 'White House')"); 
48           stmt.executeUpdate("insert into info values ('Gore, Al', '3210987654', 200000, 'donno')"); 
49           stmt.executeUpdate("insert into info values ('Bing, Chandler', '4321056789', 750000, 'Friends')"); 
50           stmt.close(); 
51       } 
52    
53       private ResultSet searchAllTuples(Connection con) throws SQLException { 
54           Statement stmt = con.createStatement(); 
55           return stmt.executeQuery("SELECT * FROM info"); 
56       } 
57    
58       private void showMetaDataOfResultSet(ResultSet result) throws SQLException { 
59           ResultSetMetaData meta = result.getMetaData(); 
60           for (int col = 1; col <= meta.getColumnCount(); col++) { 
61               System.out.println("Column: " + meta.getColumnName(col) + 
62                                  ",\t Type: " + meta.getColumnTypeName(col)); 
63           } 
64       } 
65    
66       private void showResultSet(ResultSet result) throws SQLException { 
67           ResultSetMetaData meta = result.getMetaData(); 
68           int tupleCount = 1; 
69           while (result.next()) { 
70               System.out.print("Tuple " + tupleCount++ + ": "); 
71               for (int col = 1; col <= meta.getColumnCount(); col++) { 
72                   System.out.print("\"" + result.getString(col) + "\",\t"); 
73               } 
74               System.out.println(); 
75           } 
76       } 
77    
78       /** 
79        * 
80        * @return a database connection 
81        * @throws java.sql.SQLException when there is an error when trying to connect database 
82        * @throws ClassNotFoundException when the database driver is not found. 
83        */ 
84       private Connection openConnection() throws SQLException, ClassNotFoundException { 
85           // Load the Oracle database driver 
86           DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
87    
88           /* 
89           Here is the information needed when connecting to a database 
90           server. These values are now hard-coded in the program. In 
91           general, they should be stored in some configuration file and 
92           read at run time. 
93           */ 
94           String host = "shams.usc.edu"; 
95           String port = "1521"; 
96           String dbName = "cs585"; 
97           String userName = "temp"; 
98           String password = "temp585"; 
99    
100          // Construct the JDBC URL 
101          String dbURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName; 
102          return DriverManager.getConnection(dbURL, userName, password); 
103      } 
104   
105      /** 
106       * Close the database connection 
107       * @param con 
108       */ 
109      private void closeConnection(Connection con) { 
110          try { 
111              con.close(); 
112          } catch (SQLException e) { 
113              System.err.println("Cannot close connection: " + e.getMessage()); 
114          } 
115      } 
116  }