Guest User

Untitled

a guest
May 25th, 2018
109
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.74 KB | None | 0 0
  1. import java.util.*;
  2. import java.sql.*;
  3. import java.io.*;
  4.  
  5. public 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, or x to quit: ")
  19. ;
  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_discount)) AS revenue "+
  29. "FROM customer, orders, lineitem, supplier, nation, region "+
  30. "WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey "+
  31. "AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA' AND o_orderdate >= date '1995-01-01' "+
  32. "AND o_orderdate < date '1997-01-01' + interval '1' year "+
  33. "GROUP BY n_name "+
  34. "ORDER BY revenue DESC;";
  35.  
  36. ResultSet rs = statement.executeQuery(query);
  37. // Show the results
  38. printResult(rs);
  39. } else if (s.equals("2")) {
  40. String query1 = "Select n_nationkey,n_name from nation;";
  41. ResultSet rs1 = statement.executeQuery(query1);
  42. printResult(rs1);
  43. System.out.print("Select nation to use for query:");
  44. String s2 = getStringFromUser();
  45. String query2 = "select n_name, count(*) as numwait "+
  46. "from supplier, lineitem l1, orders, nation " +
  47. "where s_suppkey=l1.l_suppkey and o_orderkey and o_orderstatus='F' and n_nationkey='"+
  48. s2+ "' and exists (select * from lineitem l2 "+
  49. "where l2.l_orderkey=l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) "+
  50. "and not exists (select * from lineitem l3 "+
  51. "where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey "+
  52. "and l3.l_recieptdate > l3.l_commitdate) "+
  53. "group by s_name order by numwait desc, s_name;";
  54. ResultSet rs2 = statement.executeQuery(query2);
  55. printResult(rs2);
  56.  
  57. }
  58. }
  59.  
  60. } catch (Exception e) {
  61. System.out.println("SQLException: " + e.getMessage());
  62. e.printStackTrace();
  63. }
  64. }
  65.  
  66. // Call this to print a result
  67. public static void printResult(ResultSet rs) {
  68. try {
  69. // Retrieve the column information from the result
  70. ResultSetMetaData rsmd = rs.getMetaData();
  71. int numberOfColumns = rsmd.getColumnCount();
  72. int rowCount = 1;
  73.  
  74. // Standard way to loop through returned rows
  75. while (rs.next()) {
  76. System.out.print("Row " + rowCount + ": ");
  77. for (int i = 1; i <= numberOfColumns; i++) {
  78. System.out.print(rs.getString(i)+" ");
  79. }
  80. System.out.println("");
  81. rowCount++;
  82. }
  83. } catch (SQLException e) {
  84. System.out.println("SQLException: " + e.getMessage());
  85. e.printStackTrace();
  86. System.exit(-1);
  87. }
  88. }
  89.  
  90. // Use this to get input from the user at the console
  91. public static String getStringFromUser()
  92. {
  93. BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
  94. String cmd = "";
  95. try
  96. {
  97. while (((cmd = in.readLine()) != null)) {
  98. return cmd;
  99. }
  100. }
  101. catch(IOException aIOException)
  102. {
  103. System.out.println("Exception trying to get string from user: " + aIOE
  104. xception.getMessage());
  105. aIOException.printStackTrace();
  106. }
  107. return null;
  108. }
  109.  
  110. }
Add Comment
Please, Sign In to add comment