-
Notifications
You must be signed in to change notification settings - Fork 0
/
MYSQLDB.java
176 lines (155 loc) · 4.63 KB
/
MYSQLDB.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
/**
* @author Tania
* @date 31 oct. 2018
* @version 1.0
* @description
*
*/
package peval2;
import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;
/**
* Class MYSQLDB
*/
public class MYSQLDB {
SQLiteDB sqLite;
/**
* @variable_name connection
* @type Connection
*/
private Connection connection;
/**
* @variable_name statement
* @type Statement
*/
private Statement statement;
/**
* @variable_name resultSet
* @type ResultSet
*/
private ResultSet resultSet;
/**
* @variable_name dataBasePath
* @type String
*/
private String dataBasePath;
/**
* @variable_name answer
* @type String
*/
private String answer;
/**
* Class MYSQLDB Constructor
*/
public MYSQLDB() {
}
/**
* Method that connects to MYSQL database and asks for the dni checking if it is correct.
* If not, it asks again till it is found the database.
*
* @name connectMYSQL
*/
public void connectMYSQL() {
Scanner kb = new Scanner(System.in);
boolean foundDNI = false;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.err.print("Error al cargar el driver");
//e.printStackTrace();
}
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost/sanciones", "root", "");
statement = connection.createStatement();
System.out.println("Introduzca el DNI del conductor: \n");
do {
String dni = kb.nextLine();
String sql = "SELECT dni FROM conductores WHERE dni LIKE '" + dni + "'";
resultSet = statement.executeQuery(sql);
if (!resultSet.next()) {
System.err.println("\nNo existe un conductor con el dni introducido.\n");
System.err.flush();
System.out.println("Por favor, vuelva a introducir el DNI: \n");
} else {
foundDNI = true;
resultSet.close();
statement.close();
sql = "SELECT dni, vehiculo, sancion, nombre, fecha_sancion, importe\r\n"
+ "FROM sanciones, conductores, vehiculos\r\n" + "WHERE dni LIKE '" + dni + "'\r\n"
+ " AND matricula = vehiculo \r\n"
+ " AND dni = conductor";
this.dataBasePath = "./sanciones.db";
sqLite = new SQLiteDB(this, dataBasePath, DriverManager.getConnection("jdbc:sqlite:" + dataBasePath));
sqLite.connectSQLite();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
sqLite.insertRows(resultSet);
}
resultSet.close();
statement.close();
connection.close();
sqLite.queryData();
}
} while (!foundDNI);
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
System.err.print("No se ha podido conectar a la base de datos");
//e.printStackTrace();
}
}
/**
* Method that update MySQL database once you have finished deleting penalties
*
* @name updateDataBase
* @param penalties
*/
public void updateDataBase(ArrayList<Integer> penalties) {
String penalty = "";
Scanner kb = new Scanner(System.in);
if (penalties.size() != 0) {
for (int i = 0; i < penalties.size(); i++) {
if (i == (penalties.size() - 1)) {
penalty += penalties.get(i);
} else {
penalty += penalties.get(i) + ",";
}
}
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost/sanciones", "root", "");
statement = connection.createStatement();
String sql = "SELECT * FROM SANCIONES WHERE SANCION IN (" + penalty + ")";
resultSet = statement.executeQuery(sql);
String deleteSql = "DELETE FROM SANCIONES WHERE SANCION IN (" + penalty + ")";
if (resultSet.next()) {
statement.executeUpdate(deleteSql);
System.out.println("La base de datos MYSQL se ha actualizado correctamente.");
} else {
System.out.println("No se ha encontrado ningún registro con ese número de sanción");
}
} catch (SQLException e) {
System.err.print("\nNo se ha podido conectar a la base de datos");
//e.printStackTrace();
}
} else {
System.out.println("No se ha modificado la base de datos MySQL porque no se ha anulado ninguna sanción");
}
do {
System.out.println("\n¿Desea volver a consultar alguna sanción de algún conductor? SI/NO\n");
answer = kb.nextLine();
answer = answer.toUpperCase();
if ((!answer.equals("SI") && !answer.equals("SÍ")) && !answer.equals("NO")) {
System.err.print("\nError. Sólo puede introducir SI/NO\n");
System.err.flush();
}
} while ((!answer.equals("SI") && !answer.equals("SÍ")) && !answer.equals("NO"));
if (answer.equals("SI") || answer.equals("SÍ")) {
connectMYSQL();
} else {
System.out.println("\nFin del programa");
}
}
}