· 6 years ago · May 22, 2019, 10:58 AM
1package org.sit322.azurestorage;
2
3import java.sql.*;
4import java.util.Properties;
5import java.io.IOException;
6import java.io.File;
7import java.util.Scanner;
8
9public class CreateTableInsertRows {
10 private Connection connection = null;
11
12 public static void main(String[] args) {
13 CreateTableInsertRows ctirObj = new CreateTableInsertRows();
14
15 try {
16 //Creates a connection then reads the text file
17 ctirObj.connection = ctirObj.initConnection();
18 ctirObj.readData();
19 ctirObj.readTable();
20 } catch (ClassNotFoundException e) {
21 e.printStackTrace();
22 } catch (SQLException e) {
23 e.printStackTrace();
24 }finally {
25 try {
26 ctirObj.connection.close();
27 } catch (SQLException e) {
28 e.printStackTrace();
29 }
30 }
31 }
32
33
34 private Connection initConnection() throws ClassNotFoundException, SQLException {
35 // Initialize connection variables.
36 String host = "dominicclustersql.mysql.database.azure.com";
37 String database = "clusterresults";
38 String user = "******@dominicclustersql";
39 String password = "********";
40
41 // check that the driver is installed
42 try {
43 Class.forName("com.mysql.cj.jdbc.Driver");
44 } catch (ClassNotFoundException e) {
45 throw new ClassNotFoundException("MySQL JDBC driver NOT detected in library path.", e);
46 }
47
48 System.out.println("MySQL JDBC driver detected in library path.");
49
50 Connection connection = null;
51
52 // Initialize connection object
53 try {
54 String url = String.format("jdbc:mysql://%s/%s", host, database);
55
56 // Set connection properties.
57 Properties properties = new Properties();
58 properties.setProperty("user", user);
59 properties.setProperty("password", password);
60 properties.setProperty("useSSL", "true");
61 properties.setProperty("verifyServerCertificate", "true");
62 properties.setProperty("requireSSL", "false");
63 properties.setProperty("useTimezone", "true");
64 properties.setProperty("useLegacyDatetimeCode", "false");
65 properties.setProperty("serverTimezone", "UTC");
66
67 // get connection
68 connection = DriverManager.getConnection(url, properties);
69
70 } catch (SQLException e) {
71 throw new SQLException("Failed to create connection to database.", e);
72 }
73
74 if (connection != null) {
75
76 // Perform some SQL queries over the connection.
77 try {
78 // Drop previous table of same name if one exists.
79 Statement statement = connection.createStatement();
80 statement.execute("DROP TABLE IF EXISTS wordcount;");
81 System.out.println("Finished dropping table (if existed).");
82
83 // Create table.
84 statement
85 .execute("CREATE TABLE wordcount (word VARCHAR(50), count INTEGER);");
86 System.out.println("Created table.");
87
88 } catch (SQLException e) {
89 throw new SQLException("Encountered an error when executing given sql statement.", e);
90 }
91 }
92
93 return connection;
94 }
95 public String word;
96 public int count;
97 // This function is called and reads the text file and strips out the white space and saves each word and the count
98 public void readData() {
99 //Counts how many times this while loop is used
100 int howmany = 0;
101 //uses the ouput text and reads it
102 try(Scanner input = new Scanner(new File("src/50littlemen.txt")))
103 {
104 while(input.hasNextLine()) {
105 word ="";
106 String line;
107
108 line = input.nextLine();
109
110 try(Scanner data = new Scanner(line))
111 {
112 //while ther is a word next it will read it
113 while(!data.hasNextInt()) {
114 word += data.next()+" ";
115 howmany++;
116 }
117 //trims out the white space
118 word = word.trim();
119
120 if(data.hasNextInt()) {
121 count = data.nextInt();
122
123 }
124
125 }
126 //calls the save to database fucntion
127 savaData();
128 }
129
130 }catch(IOException e)
131 {
132 System.out.println(e);
133 }
134 System.out.println(howmany);
135 }
136 private void savaData() {
137 if (this.connection != null) {
138
139 // Perform some SQL queries over the connection.
140 try {
141 // saves teh data saved from the text file into the sql database
142 PreparedStatement preparedStatement = connection
143 .prepareStatement("INSERT INTO wordcount VALUES(?, ?)");
144 preparedStatement.setString(1, word);
145 preparedStatement.setInt(2, count);
146 preparedStatement.executeUpdate();
147 // NOTE No need to commit all changes to database, as auto-commit is enabled by
148 // default.
149 } catch (SQLException e) {
150 System.out.println(e);
151 }
152 }
153 }
154
155
156 private void readTable() throws SQLException {
157 System.out.println("...Reading Table...");
158 if (this.connection != null) {
159 // Perform some SQL queries over the connection.
160 try {
161 //reads teh database and prints to conssole.
162 Statement statement = connection.createStatement();
163 boolean hasResulSet = statement.execute("SELECT * FROM wordcount");
164 if(hasResulSet)
165 {
166 ResultSet result = statement.getResultSet();
167 ResultSetMetaData metaData = result.getMetaData();
168
169 int columnCount = metaData.getColumnCount();
170
171 for(int i=1; i<=columnCount; i++) {
172 System.out.print(metaData.getColumnLabel(i)+"\t\t");
173
174 }
175 System.out.println();
176
177 while(result.next()) {
178 System.out.printf("%-20s%4d%4d%n", result.getString("word"), result.getInt("count"));
179 }
180 }
181 } catch (SQLException e) {
182 throw new SQLException("Encountered an error when executing given sql statement", e);
183 }
184 } else {
185 System.out.println("Failed to create connection to database.");
186 }
187 }
188 }