unL44DXU

1import java.util.*;
2import java.sql.*;
3import java.io.*;
4
5public class myjdbc {
6    public static void main(String[] args) {
7        try {
8            System.out.println("Loading the driver...");
9            Class.forName("org.postgresql.Driver");
10            System.out.println("Connecting...");
11            Connection db = DriverManager.getConnection("jdbc:postgresql://laomedon.cs
12.brandeis.edu/jsheenaPA2", "jsheena", "hwul2g@cs4m");
13            System.out.println("Creating statement...");
14            Statement statement = db.createStatement();
15
16            while (true) {
17                // Interact with the user
18                System.out.print("Enter 1 for query 1, 2 for query 2,3 for all regions
19, or x to quit: ");
20                String s = getStringFromUser();
21                if (s.equals("x")) {
22                    // Close all connections
23                    statement.close();
24                    db.close();
25                    System.exit(0);
26                } else if (s.equals("1")) {
27                    // Run the query
28                    String query = "SELECT n_name, SUM(l_extendedprice * (1 - l_discou
29nt)) AS revenue "+
30                        "FROM customer, orders, lineitem, supplier, nation, region "+
31                        "WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l
32_suppkey = s_suppkey AND c_nationkey = s_nationkey "+
33                        "AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey A
34ND r_name = 'ASIA' AND o_orderdate >= date '1995-01-01' "+
35                        "AND o_orderdate < date '1997-01-01' + interval '1' year "+
36                        "GROUP BY n_name "+
37                        "ORDER BY revenue DESC;";
38
39                    ResultSet rs = statement.executeQuery(query);
40                    // Show the results
41                    printResult(rs);
42                } else if (s.equals("2")) {
43                        String query1 = "Select n_nationkey,n_name from nation;";
44                        ResultSet rs1 = statement.executeQuery(query1);
45                        printResult(rs1);
46                        System.out.print("Select nation to use for query:");
47                        String s2 = getStringFromUser();
48                        String query2 = "select s_name, count(*) as numwait "+
49                        "from supplier, lineitem l1, orders, nation " +
50                        "where s_suppkey=l1.l_suppkey and o_orderkey = l1.l_orderkey a
51nd o_orderstatus='F' and n_nationkey='"+
52                        s2+"' and exists (select * from lineitem l2 "+
53                        "where l2.l_orderkey=l1.l_orderkey and l2.l_suppkey <> l1.l_su
54ppkey) "+
55                        "and not exists (select * from lineitem l3 "+
56                        "where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_
57suppkey "+ 
58                        "and l3.l_receiptdate > l3.l_commitdate) "+
59                        "group by s_name order by numwait desc, s_name;";
60                        ResultSet rs2 = statement.executeQuery(query2);
61                        printResult(rs2);
62                } else if (s.equals("3")) {
63                        String query = "create table tmp (name text, contains text);";
64                        statement.executeQuery(query);
65                        boolean notdone = true;
66                        query = "insert into tmp (select * from allregions);";
67                        statement.executeQuery(query);
68                        query = "select * from tmp;";
69                        ResultSet base = statement.executeQuery(query);
70                        while (notdone) {
71                                query = "insert into tmp ( select b.name,a.contains fr
72om tmp as a cross join tmp as b where b.contains=a.name union select * from tmp;";
73                                ResultSet rs = statement.executeQuery(query);
74                                if (rs == base) {
75                                        printResult(rs);
76                                        notdone = false;
77                                }else {
78                                        base = rs;
79                                }
80                        }
81                }
82        } catch (Exception e) {
83            System.out.println("SQLException: " + e.getMessage());
84            e.printStackTrace();
85        }
86    }
87
88    // Call this to print a result
89    public static void printResult(ResultSet rs) {
90        try {
91            // Retrieve the column information from the result
92            ResultSetMetaData rsmd = rs.getMetaData();
93            int numberOfColumns = rsmd.getColumnCount();
94            
95            int rowCount = 1;
96            
97            // Standard way to loop through returned rows
98            while (rs.next()) { 
99                System.out.print("Row " + rowCount + ":  ");
100                for (int i = 1; i <= numberOfColumns; i++) {
101                    System.out.print(rs.getString(i)+" ");
102                }
103                System.out.println("");
104                rowCount++;
105            }
106        } catch (SQLException e) {
107            System.out.println("SQLException: " + e.getMessage());
108            e.printStackTrace();
109            System.exit(-1);
110        }
111    }
112    
113    // Use this to get input from the user at the console
114    public static String getStringFromUser()
115    {
116        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
117        String cmd = "";
118        try
119            {
120                while (((cmd = in.readLine()) != null)) {
121                    return cmd;
122                }
123            }
124        catch(IOException aIOException)
125            {
126                System.out.println("Exception trying to get string from user: " + aIOE
127xception.getMessage());
128                aIOException.printStackTrace();
129            }
130        return null;
131    }
132    
133}