Tijc5xrg

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