· 5 years ago · Nov 03, 2020, 01:50 AM
1package randomdataset;
2
3import java.util.logging.Level;
4import java.util.logging.Logger;
5
6public class RandomDataset {
7
8 public static void main(String[] args) {
9
10 try {
11 TextFile txt = new TextFile();
12
13 txt.createFile();
14 txt.populateLists();
15 txt.displayLists();
16 txt.writeToFile();
17
18 MySQL sql = new MySQL("jdbc:mysql://localhost:3306/RandomDataset", "C:\\Users\\szoor\\OneDrive\\Documents\\NetBeansProjects\\RandomDataset\\RandomDataset.txt");
19
20 sql.dropTable();
21 sql.createTable();
22 sql.loadFileData();
23
24 } catch (Exception ex) {
25 Logger.getLogger(RandomDataset.class.getName()).log(Level.SEVERE, null, ex);
26 }
27
28 }
29
30}
31//========================================TEXT FILE CLASS=================================================================
32
33package randomdataset;
34
35import java.io.BufferedWriter;
36import java.io.File;
37import java.io.FileWriter;
38import java.io.IOException;
39import java.util.ArrayList;
40import java.util.concurrent.ThreadLocalRandom;
41import java.util.logging.Level;
42import java.util.logging.Logger;
43
44/**
45 *
46 * @author szoor
47 */
48public class TextFile {
49
50 private ArrayList<Integer> age = new ArrayList<>();
51 private ArrayList<Integer> ID = new ArrayList<>();
52 private ArrayList<String> sex = new ArrayList<>();
53 private File file;
54 private boolean running = true;
55
56 public void populateLists() {
57
58 while (age.size() <= 2000) {
59 int randomNum = ThreadLocalRandom.current().nextInt(18, 61);
60 int randomSex = ThreadLocalRandom.current().nextInt(1, 3);
61 age.add(randomNum);
62 int i = 10000;
63 while (i <= 12000) {
64 ID.add(i++);
65 }
66 if (randomSex == 1) {
67 sex.add("F");
68 } else {
69 sex.add("M");
70 }
71 }
72 }
73
74 public void displayLists() {
75 for (int i = 0; i < age.size(); i++) {
76 System.out.println(age.get(i) + " " + ID.get(i) + " " + sex.get(i));
77 }
78 }
79
80 public void createFile() {
81 try {
82 file = new File("RandomDataset.txt");
83 if (file.createNewFile()) {
84 System.out.println("File created: " + file.getName());
85 } else {
86 System.out.println("File name " + file.getName() + " already exists");
87 }
88 } catch (IOException e) {
89 System.out.println("Error creating file");
90 e.printStackTrace();
91 }
92 }
93
94 public void writeToFile() {
95
96 while (running) {
97 try {
98 FileWriter writer = new FileWriter("RandomDataset.txt", true);
99
100 for (int i = 0; i < age.size(); i++) {
101 writer.write(age.get(i) + ", " + ID.get(i) + ", " + sex.get(i) + "\n");
102 }
103 writer.close();
104 System.out.println("Successful write to file");
105
106 } catch (IOException e) {
107 System.out.println("Error writing to file");
108 Logger.getLogger(RandomDataset.class
109 .getName()).log(Level.SEVERE, null, e);
110 }
111 running = false;
112 }
113
114 }
115
116}
117//===========================================SQL CLASS============================================================
118
119package randomdataset;
120
121import java.io.BufferedReader;
122import java.io.File;
123import java.io.FileReader;
124import java.sql.*;
125import java.util.ArrayList;
126import java.util.logging.Level;
127import java.util.logging.Logger;
128
129public class MySQL {
130
131 private String url = "";
132 private String user = "root";
133 private String password = "2916";
134 private String filePath;
135 private boolean tableExists;
136
137 public MySQL(String url, String filePath) {
138 this.url = url;
139 this.filePath = filePath;
140 //pass database address (url) and text file path (filePath)
141 }
142
143 public void select() {
144
145 try {
146 Class.forName("com.mysql.cj.jdbc.Driver");
147 Connection con = DriverManager.getConnection(this.url, this.user, this.password);
148 Statement stmt = con.createStatement();
149
150 // SELECT query
151 String query = "SELECT * FROM patient";
152 ResultSet resultSet = stmt.executeQuery(query);
153 if (resultSet.next()) {
154 System.out.println("AGE: " + resultSet.getString(2));
155 System.out.println("ID: " + resultSet.getString(1));
156 System.out.println("SEX: " + resultSet.getString(3));
157 } else {
158 System.out.println("does not exist");
159 }
160 con.close();
161 } catch (ClassNotFoundException ex) {
162 Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
163 } catch (SQLException ex) {
164 Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
165 }
166
167 }
168
169
170 public void dropTable() {
171
172 //drop table before creating it again
173 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
174 String sqlDrop = "DROP TABLE patient";
175 PreparedStatement drop = con.prepareStatement(sqlDrop);
176 drop.executeUpdate();
177 //set table exists to false
178 tableExists = false;
179 System.out.println("Table 'patient' dropped");
180 con.close();
181 } catch (SQLException ex) {
182 Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
183 }
184
185 }
186
187
188 public void createTable() {
189
190 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
191 String createSQL = "CREATE TABLE patient(age varchar(10), id varchar(10), sex varchar(10))";
192 PreparedStatement create = con.prepareStatement(createSQL);
193 create.executeUpdate();
194 tableExists = true;
195 // create marker
196 System.out.println("Table 'patient' was created");
197 con.close();
198 } catch (SQLException ex) {
199 Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
200 }
201
202 }
203
204
205 public void insert(String age, String id, String sex) throws ClassNotFoundException {
206 if (tableExists) {
207 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
208 Class.forName("com.mysql.cj.jdbc.Driver");
209 if (con != null) {
210 // connect successful
211 }
212 String querySetLimit = "SET GLOBAL max_allowed_packet=104857600;"; //10 MB
213 Statement stSetLimit = con.createStatement();
214 stSetLimit.execute(querySetLimit);
215 Statement insertSQL = con.createStatement();
216 // Insert data
217 String insert = "INSERT INTO patient values('" + age + "', '" + id + "', '" + sex + "')";
218 insertSQL.executeUpdate(insert);
219
220 con.close();
221
222 } catch (SQLException ex) {
223 Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
224 }
225 } else {
226 System.out.println("No such table");
227 System.exit(0);
228 }
229
230 }
231
232
233 public void loadFileData() throws Exception {
234 ArrayList<String[]> dataSet = getFileData();
235 for (String[] data : dataSet) {
236 insert(data[0], data[1], data[2]);
237 System.out.println(data[0] + ", " + data[1] + ", " + data[2]);
238 }
239 }
240
241
242 public ArrayList<String[]> getFileData() throws Exception {
243 File file = new File(filePath);
244 BufferedReader br = new BufferedReader(new FileReader(file));
245
246 ArrayList<String[]> dataSet = new ArrayList<>();
247 String str;
248 while ((str = br.readLine()) != null) {
249 String[] data = str.replaceAll("\\s+", "").split(",");
250 dataSet.add(data);
251 }
252 return dataSet;
253 }
254
255}
256