· 7 years ago · Oct 10, 2018, 04:38 PM
1import org.jetbrains.annotations.Nullable;
2
3import java.sql.*;
4
5public class JavaDatabase {
6 /**
7 * Jecsan Blanco
8 * 2018FA DATABASE SYSTEMS (CS-4340-01)
9 * Assignment #5
10 * 10/09/2018
11 */
12 private static Statement statement;
13 private static final String EXP_MSG = "Somewhere there’s a village missing its idiot.";
14
15 public static void main(String[] args) {
16 //no clue why this works! Magic?
17 new JavaDatabase();
18 String result[][];
19// Display a list of all instructors, showing their ID, name, and the number of sections that
20// they have taught
21
22 result = submitQuery(
23 "SELECT i.id,i.name,count(t.course_id) " +
24 "FROM instructor i, teaches t " +
25 "WHERE t.id=i.id " +
26 "Group by t.id");
27 String labels[] = new String[]{"", "", "#ofCoursesTaught"};
28 printTable(result, labels);
29
30 //Display the names of instructors who have not taught any section.
31 labels = new String[]{"Teach No Sections:"};
32 result = submitQuery(
33 "SELECT i.name FROM instructor i " +
34 "WHERE NOT EXISTS " +
35 "(SELECT 1 FROM teaches t WHERE t.id = i.id)");
36 printTable(result, labels);
37
38
39 // Display the list of all course sections offered in Spring 2010,
40 // along with the name of the instructors teaching the section.
41 // If a course has more than one section, it should appear as many times
42 //in the result as it has instructors.
43 result = submitQuery(
44 "SELECT c.course_id, i.name " +
45 "FROM course c,instructor i,teaches t " +
46 "WHERE t.id=i.id and t.course_id = c.course_id " +
47 "AND t.semester=\"Spring\" AND t.year=\"2010\"");
48 printTable(result);
49
50
51 //Display the list of all departments with the total number of instructors in
52 //each department.
53 result = submitQuery("select d.dept_name, count(i.id) " +
54 "FROM department d, instructor i " +
55 "where i.dept_name=d.dept_name " +
56 "group by i.dept_name ");
57
58 //Display the name, department and salary of the highest paid instructor.
59 result = submitQuery(
60 "select i.name, i.dept_name, max(i.salary)" +
61 "FROM instructor i " +
62 "WHERE i.salary >= (SELECT Max(i.salary) FROM instructor i) " +
63 "group by i.id "
64 );
65 printTable(result);
66
67 }
68
69 /**
70 * Prints a table
71 *
72 * @param table the table to display
73 */
74 private static void printTable(String[][] table) {
75 printTable(table, null);
76 }
77
78 /**
79 * Prints a table using the labels as the first row
80 *
81 * @param table the table to display
82 * @param labels the labels to use for each column 0-n
83 * labels length must equal the number of columns otherwise
84 * the default labels provided within the table are used.
85 * If some labels are omitted only the non empty will be used
86 * and the default labels used for the rest.
87 */
88 private static void printTable(@Nullable String[][] table, @Nullable String[] labels) {
89 if (table != null) {
90 int t = 0;
91 // add custom labels?
92 if (labels != null && labels.length == table[0].length) {
93 //put the label provided if any or put default table label
94 for (String label : labels) {
95 assert label != null;
96 System.out.printf("%-15s", (label.equals("")) ? table[0][t++] : label);
97 }
98 System.out.println();
99 t = 1;
100 }
101
102 //default table labels added or skipped
103 for (int i = t; i < table.length; ++i) {
104 for (int j = 0; j < table[0].length; j++) {
105 System.out.printf("%-15s", table[i][j]);
106 }
107 System.out.println();
108 }
109 System.out.println();
110 }
111 }
112
113 @Nullable
114 private static String[][] submitQuery(String sql) {
115 String[][] table = null;
116 try {
117 ResultSet result = statement.executeQuery(sql);
118 ResultSetMetaData md = result.getMetaData();
119 result.last();
120 //correction by 1 for the extra column name;
121 int rows = result.getRow() + 1;
122 result.first();
123
124 //get more info to create table
125 int columns = md.getColumnCount();
126 table = new String[rows][columns];
127
128 //gets the names of the columns on the first row
129 for (int column = 0; column < columns; ++column) {
130 table[0][column] = md.getColumnLabel(column + 1);
131 }
132 //populate the table with the data
133 // skipping the first row as it was filled by above.
134 for (int row = 1; row < rows; ++row) {
135 for (int column = 0; column < columns; ++column) {
136 table[row][column] = result.getString(column + 1);
137 }
138 //next row
139 result.next();
140 }
141 } catch (SQLException E) {
142 System.out.println(EXP_MSG);
143 E.printStackTrace();
144 }
145 return table;
146 }
147
148 private JavaDatabase() {
149 // This is a driver class used to establish a connection a submit query for this lab.
150 try {
151 // load the JDBC driver
152 // this command will register the driver with the driver manager and make it available to the program
153 // setup the connection to the db
154 Connection connection = DriverManager.getConnection("jdbc:mysql://10.0.6.1:3306/University_jb963962?autoReconnect=true&useSSL=false", "jb963962", "9PCX2vZS");
155 statement = connection.createStatement(); // Creates a Statement object for sending SQL statements to the database.
156 } catch (SQLException e) {
157 System.out.println(EXP_MSG);
158 e.printStackTrace();
159 }
160 }
161
162
163}