Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.util.*;
- import java.sql.*;
- import java.io.*;
- public class myjdbc {
- public static void main(String[] args) {
- try {
- System.out.println("Loading the driver...");
- Class.forName("org.postgresql.Driver");
- System.out.println("Connecting...");
- Connection db = DriverManager.getConnection("jdbc:postgresql://laomedon.cs
- .brandeis.edu/jsheenaPA2", "jsheena", "hwul2g@cs4m");
- System.out.println("Creating statement...");
- Statement statement = db.createStatement();
- while (true) {
- // Interact with the user
- System.out.print("Enter 1 for query 1, 2 for query 2, or x to quit: ")
- ;
- String s = getStringFromUser();
- if (s.equals("x")) {
- // Close all connections
- statement.close();
- db.close();
- System.exit(0);
- } else if (s.equals("1")) {
- // Run the query
- String query = "SELECT n_name, SUM(l_extendedprice * (1 - l_discount)) AS revenue "+
- "FROM customer, orders, lineitem, supplier, nation, region "+
- "WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey "+
- "AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA' AND o_orderdate >= date '1995-01-01' "+
- "AND o_orderdate < date '1997-01-01' + interval '1' year "+
- "GROUP BY n_name "+
- "ORDER BY revenue DESC;";
- ResultSet rs = statement.executeQuery(query);
- // Show the results
- printResult(rs);
- } else if (s.equals("2")) {
- String query1 = "Select n_nationkey,n_name from nation;";
- ResultSet rs1 = statement.executeQuery(query1);
- printResult(rs1);
- System.out.print("Select nation to use for query:");
- String s2 = getStringFromUser();
- String query2 = "select n_name, count(*) as numwait "+
- "from supplier, lineitem l1, orders, nation " +
- "where s_suppkey=l1.l_suppkey and o_orderkey and o_orderstatus='F' and n_nationkey='"+
- s2+ "' and exists (select * from lineitem l2 "+
- "where l2.l_orderkey=l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) "+
- "and not exists (select * from lineitem l3 "+
- "where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey "+
- "and l3.l_recieptdate > l3.l_commitdate) "+
- "group by s_name order by numwait desc, s_name;";
- ResultSet rs2 = statement.executeQuery(query2);
- printResult(rs2);
- }
- }
- } catch (Exception e) {
- System.out.println("SQLException: " + e.getMessage());
- e.printStackTrace();
- }
- }
- // Call this to print a result
- public static void printResult(ResultSet rs) {
- try {
- // Retrieve the column information from the result
- ResultSetMetaData rsmd = rs.getMetaData();
- int numberOfColumns = rsmd.getColumnCount();
- int rowCount = 1;
- // Standard way to loop through returned rows
- while (rs.next()) {
- System.out.print("Row " + rowCount + ": ");
- for (int i = 1; i <= numberOfColumns; i++) {
- System.out.print(rs.getString(i)+" ");
- }
- System.out.println("");
- rowCount++;
- }
- } catch (SQLException e) {
- System.out.println("SQLException: " + e.getMessage());
- e.printStackTrace();
- System.exit(-1);
- }
- }
- // Use this to get input from the user at the console
- public static String getStringFromUser()
- {
- BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
- String cmd = "";
- try
- {
- while (((cmd = in.readLine()) != null)) {
- return cmd;
- }
- }
- catch(IOException aIOException)
- {
- System.out.println("Exception trying to get string from user: " + aIOE
- xception.getMessage());
- aIOException.printStackTrace();
- }
- return null;
- }
- }
Add Comment
Please, Sign In to add comment