D:\work\db\src\Example5.java
1     
2    import java.sql.*; 
3     
4    /** 
5     */ 
6    public class Example5 { 
7        public static void main(String args[]) { 
8            Example5 example = new Example5(); 
9            example.run(); 
10       } 
11    
12       private void run() { 
13           Connection con = null; 
14           ResultSet result = null; 
15           try { 
16               con = openConnection(); 
17    
18               publishData(con); 
19               result = searchAllTuples(con); 
20               showMetaDataOfResultSet(result); 
21               showResultSet(result); 
22               navigateResult(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       private void navigateResult(Connection c) throws SQLException { 
34           /* 
35           This will create a Statement that return ResultSets which is: 
36           1. Scrollablle (can use ResultSet.previous() or ResultSet.absolute()) 
37           2. Read-only (cannot call ResultSet.updateXXX() to change the content) 
38           */ 
39           Statement stmt = c.createStatement( 
40                   ResultSet.TYPE_SCROLL_INSENSITIVE, 
41                   ResultSet.CONCUR_READ_ONLY); 
42    
43           ResultSet r = stmt.executeQuery("SELECT * FROM info"); 
44           ResultSetMetaData meta = r.getMetaData(); 
45    
46           System.out.print("\nFirst Tuple: "); 
47           r.next(); 
48           for (int col = 1; col <= meta.getColumnCount(); col++) { 
49               System.out.print("\"" + r.getString(col) + "\","); 
50           } 
51    
52           System.out.print("\nNext Tuple: "); 
53           r.next(); 
54           for (int col = 1; col <= meta.getColumnCount(); col++) { 
55               System.out.print("\"" + r.getString(col) + "\","); 
56           } 
57    
58           System.out.print("\nPrev. Tuple: "); 
59           r.previous(); 
60           for (int col = 1; col <= meta.getColumnCount(); col++) { 
61               System.out.print("\"" + r.getString(col) + "\","); 
62           } 
63    
64           System.out.print("\n3rd Tuple: "); 
65           r.absolute(3); 
66           for (int col = 1; col <= meta.getColumnCount(); col++) { 
67               System.out.print("\"" + r.getString(col) + "\","); 
68           } 
69       } 
70    
71       /** 
72        * This method will first clean up the table and then 
73        * populate it with new data. 
74        * @param con 
75        * @throws java.sql.SQLException 
76        */ 
77       private void publishData(Connection con) throws SQLException { 
78           Statement stmt = con.createStatement(); 
79    
80           System.out.print("Deleting previous tuples ..."); 
81           stmt.executeUpdate("delete from info"); 
82    
83           System.out.print("Inserting Data ..."); 
84           stmt.executeUpdate("insert into info values ('Clinton, Bill', '9876543210', 400000, 'Ex. White House')"); 
85           stmt.executeUpdate("insert into info values ('Doll, Bob', '1234567890', 100000, 'House')"); 
86           stmt.executeUpdate("insert into info values ('Bush, George', '6543210987', 450000, 'White House')"); 
87           stmt.executeUpdate("insert into info values ('Gore, Al', '3210987654', 200000, 'donno')"); 
88           stmt.executeUpdate("insert into info values ('Bing, Chandler', '4321056789', 750000, 'Friends')"); 
89           stmt.close(); 
90       } 
91    
92       private ResultSet searchAllTuples(Connection con) throws SQLException { 
93           Statement stmt = con.createStatement(); 
94           return stmt.executeQuery("SELECT * FROM info"); 
95       } 
96    
97       private void showMetaDataOfResultSet(ResultSet result) throws SQLException { 
98           ResultSetMetaData meta = result.getMetaData(); 
99           for (int col = 1; col <= meta.getColumnCount(); col++) { 
100              System.out.println("Column: " + meta.getColumnName(col) + 
101                                 "\t, Type: " + meta.getColumnTypeName(col)); 
102          } 
103      } 
104   
105      private void showResultSet(ResultSet result) throws SQLException { 
106          ResultSetMetaData meta = result.getMetaData(); 
107          int tupleCount = 1; 
108          while (result.next()) { 
109              System.out.print("Tuple " + tupleCount++ + " : "); 
110              for (int col = 1; col <= meta.getColumnCount(); col++) { 
111                  System.out.print("\"" + result.getString(col) + "\","); 
112              } 
113              System.out.println(); 
114          } 
115      } 
116   
117      /** 
118       * 
119       * @return a database connection 
120       * @throws java.sql.SQLException when there is an error when trying to connect database 
121       * @throws ClassNotFoundException when the database driver is not found. 
122       */ 
123      private Connection openConnection() throws SQLException, ClassNotFoundException { 
124          // Load the Oracle database driver 
125          DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
126   
127          /* 
128          Here is the information needed when connecting to a database 
129          server. These values are now hard-coded in the program. In 
130          general, they should be stored in some configuration file and 
131          read at run time. 
132          */ 
133          String host = "shams.usc.edu"; 
134          String port = "1521"; 
135          String dbName = "cs585"; 
136          String userName = "temp"; 
137          String password = "temp585"; 
138   
139          // Construct the JDBC URL 
140          String dbURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName; 
141          return DriverManager.getConnection(dbURL, userName, password); 
142      } 
143   
144      /** 
145       * Close the database connection 
146       * @param con 
147       */ 
148      private void closeConnection(Connection con) { 
149          try { 
150              con.close(); 
151          } catch (SQLException e) { 
152              System.err.println("Cannot close connection: " + e.getMessage()); 
153          } 
154      } 
155  } 
156