-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLiteDB.java
234 lines (211 loc) · 6.45 KB
/
SQLiteDB.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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
/**
* @author Tania
* @date 31 oct. 2018
* @version 1.0
* @description
*
*/
package peval2;
import java.sql.*;
import java.util.ArrayList;
import java.util.InputMismatchException;
import java.util.Scanner;
import javax.xml.parsers.ParserConfigurationException;
import java.io.*;
/**
* Class SQLiteDB
*/
public class SQLiteDB {
/**
* @variable_name mySQL
* @type MYSQLDB
*/
private MYSQLDB mySQL;
/**
* @variable_name sql
* @type String
*/
private String sql;
/**
* @variable_name penalties
* @type ArrayList<Integer>
*/
private ArrayList<Integer> penalties;
/**
* @variable_name connection
* @type Connection
*/
private Connection connection;
/**
* @variable_name dataBasePath
* @type String
*/
private String dataBasePath;
/**
* @variable_name statement
* @type Statement
*/
private Statement statement;
/**
* Class SQLiteDB Constructor
*/
public SQLiteDB(MYSQLDB mySQL, String dataBasePath, Connection connection) {
this.connection = connection;
this.mySQL = mySQL;
this.dataBasePath = dataBasePath;
penalties = new ArrayList<Integer>();
}
/**
* Method that creates (if not exist) and connects to a local SQLite database
* and creates SANCION_DETALLES table
*
* @name connectSQLite
*/
public void connectSQLite() {
ResultSet resultSet;
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e1) {
System.err.println("Error al cargar el driver");
//e1.printStackTrace();
}
try {
statement = connection.createStatement();
System.out.println("\n---Base de datos local creada correctamente---");
statement.execute("CREATE TABLE IF NOT EXISTS SANCION_DETALLES " + "(dni varchar(9) NOT NULL,"
+ " vehiculo varchar(7) NOT NULL," + " sancion tinyint PRIMARY KEY NOT NULL,"
+ " nombre varchar(40)," + " fecha_sancion NOT NULL," + " importe tinyint)");
statement.close();
} catch (SQLException e) {
System.err.print("\nNo se ha podido conectar a la base de datos");
System.err.flush();
//e.printStackTrace();
}
}
/**
* Method that inserts into table SANCION_DETALLES all penalties found for the
* dni you entered at the start of the program
*
* @name insertRows
* @param resultSet
*/
public void insertRows(ResultSet resultSet) {
try {
statement = connection.createStatement();
statement.executeUpdate("INSERT INTO SANCION_DETALLES values" + "('" + resultSet.getString(1) + "', '"
+ resultSet.getString(2) + "', '" + resultSet.getInt(3) + "', '" + resultSet.getString(4) + "', '"
+ resultSet.getString(5) + "', '" + resultSet.getInt(6) + "')");
statement.close();
} catch (SQLException e) {
System.err.print("\nNo se ha podido conectar a la base de datos");
System.err.flush();
//e.printStackTrace();
}
}
/**
* Method that shows all the penalties found for the dni you entered at the
* start of the program and the total amount of money of all penalties
*
* @name queryData
*/
public void queryData() {
try {
int totalMultas = 0;
statement = connection.createStatement();
String sql = "SELECT * FROM SANCION_DETALLES";
ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next()) {
System.out.println("\nDNI\t VEHÍCULO SANCIÓN NOMBRE\t FECHA IMPORTE");
totalMultas += resultSet.getInt(6);
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + "\t" + resultSet.getInt(3)
+ " " + resultSet.getString(4) + " " + resultSet.getString(5) + " " + resultSet.getInt(6)
+ "\n");
} else {
System.out.println("\n El usuario no tiene ninguna sanción");
}
while (resultSet.next()) {
totalMultas += resultSet.getInt(6);
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + "\t" + resultSet.getInt(3)
+ " " + resultSet.getString(4) + " " + resultSet.getString(5) + " " + resultSet.getInt(6)
+ "\n");
}
if(totalMultas != 0) {
System.out.println("TOTAL MULTAS.........................." + totalMultas);
}
resultSet.close();
statement.close();
deletePenalties();
} catch (SQLException e) {
System.err.print("\nNo se ha podido conectar a la base de datos");
System.err.flush();
//e.printStackTrace();
}
}
/**
* Method that allows to delete penalties
* @name deletePenalties
*/
public void deletePenalties() {
String answer;
int penalty;
String deleteSql;
Scanner kb = new Scanner(System.in);
ResultSet resultSet;
do {
System.out.println("\n¿Desea anular alguna sanción? SI/NO\n");
answer = kb.nextLine();
answer = answer.toUpperCase();
if ((!answer.equals("SI") && !answer.equals("SÍ")) && !answer.equals("NO")) {
System.err.println("\nError. Sólo puede introducir SI/NO");
System.err.flush();
}
} while ((!answer.equals("SI") && !answer.equals("SÍ")) && !answer.equals("NO"));
try {
if (answer.equals("SI") || answer.equals("SÍ")) {
do {
try {
System.out.println("\nIntroduzca número de sanción \n(El número debe ser mayor que 0)\n");
penalty = Integer.parseInt((kb.nextLine()));
} catch (NumberFormatException nf) {
System.err.println("\nError. No ha introducido un valor válido");
System.err.flush();
penalty = 0;
//nf.printStackTrace();
} catch (InputMismatchException im) {
System.err.println("\nError. No ha introducido un valor válido");
System.err.flush();
penalty = 0;
//im.printStackTrace();
}
} while (penalty <= 0);
sql = "SELECT * FROM SANCION_DETALLES WHERE sancion LIKE '" + penalty + "'";
deleteSql = "DELETE FROM SANCION_DETALLES WHERE sancion LIKE '" + penalty + "'";
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
penalties.add(penalty);
statement.executeUpdate(deleteSql);
System.out.println("\n---La sanción ha sido eliminada correctamente---");
} else {
System.out.println("\n---La sanción no existe o ha sido eliminada---");
}
resultSet.close();
statement.close();
queryData();
} else {
connection.close();
File file = new File(dataBasePath);
if (file.getName().equals("sanciones.db")) {
if (file.delete()) {
System.out.println("\n---Base de datos local borrada---\n");
}
}
mySQL.updateDataBase(penalties);
}
} catch (SQLException e) {
System.err.print("\nNo se ha podido conectar a la base de datos");
System.err.flush();
//e.printStackTrace();
}
}
}