4 import java.io.IOException;
6 import java.security.NoSuchAlgorithmException;
8 import java.util.Random;
10 import java.sql.Timestamp;
12 import java.util.ArrayList;
14 import java.lang.Double;
16 public class dbConnect
18 private static Connection db;
19 private static Statement stmt;
24 public static Long time()
26 java.util.Date date= new java.util.Date();
27 Timestamp now=new Timestamp(date.getTime());
31 //pulisce confmail e connessi da tutte le richieste maggiori di 10 minuti
32 private static void deletetmp() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
34 PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConfermaMail+" WHERE "+global.dbConstant.clTime+"<=?");
35 preparedStmt.setLong (1,(time()-600000));
36 preparedStmt.execute();
37 preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConnessi+" WHERE "+global.dbConstant.clTime+"<=?");
38 preparedStmt.setLong (1,(time()-600000));
39 preparedStmt.execute();
42 //divide la stringa in user e password
43 private static String splitUsrPass(String userAndPass, int code) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
47 ArrayList<String> userPass= suString.stringToArrayList(userAndPass);
48 if(userPass.size()>=1)
50 if(userPass.size()>=2)
54 if(!user.contains("@studio.unibo.it") && user.length()<16)
58 if (pass.length()>6) {
60 return registration(user, pass);
62 return login(user, pass);
64 return recupero(user);
72 //routin di gestione recupero pass
73 private static String recupero(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
75 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"'");
78 if(returnQuery.next())
80 EmailSender email = new EmailSender(global.emailConstant.email,
81 global.emailConstant.emailPass,
83 global.emailConstant.email,
85 "OGGETTO: recupero password LogicPlayer",
86 "la tua password di LogicPlayer è "+returnQuery.getString(global.dbConstant.clPass));
88 return "mail-pass-ok";
98 //routin di gestione della registrazione
99 private static String registration(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
102 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"'");
103 if(returnQuery!=null)
105 if(returnQuery.next())
107 if(returnQuery.getString(global.dbConstant.clUser).compareTo(user)==0 && returnQuery.getString(global.dbConstant.clPass).compareTo(pass)==0)
108 return "già-registrato";
111 returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConfermaMail+" where "+global.dbConstant.clUser+"='"+user+"'");
112 if(returnQuery!=null)
114 if(returnQuery.next())
116 ran=returnQuery.getString(global.dbConstant.clRandom);
117 if(returnQuery.getString(global.dbConstant.clPass).compareTo(pass)!=0)
119 PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbConfermaMail+" SET "+global.dbConstant.clPass+" =? where user=?");
120 preparedStmt.setString (1,pass);
121 preparedStmt.setString (2,user);
122 preparedStmt.execute();
127 Random random = new Random();
128 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
129 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConfermaMail+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+","+global.dbConstant.clRandom+","+global.dbConstant.clTime+") VALUES(?,?,?,?)");
130 preparedStmt.setString (1,user);
131 preparedStmt.setString (2,pass);
132 preparedStmt.setString (3,ran);
133 preparedStmt.setDouble (4,(double)time());
134 preparedStmt.execute();
139 Random random = new Random();
140 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
141 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConfermaMail+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+","+global.dbConstant.clRandom+","+global.dbConstant.clTime+") VALUES(?,?,?,?)");
142 preparedStmt.setString (1,user);
143 preparedStmt.setString (2,pass);
144 preparedStmt.setString (3,ran);
145 preparedStmt.setDouble(4, (double) time());
146 preparedStmt.execute();
148 EmailSender email = new EmailSender(global.emailConstant.email,
149 global.emailConstant.emailPass,
150 global.emailConstant.smtpName,
151 global.emailConstant.email,
153 "OGGETTO: Registrazione LogicPlayer",
154 "per confermare la tua email all'applicazione LogicPlayer clicka sul link: http://"+global.serverName+":"+global.portaHttpServer+"/test?id="+ran);
156 return "mail-reg-ok";
159 //routin conferma registrazione
160 private static String confirmRegistration(String hash) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
162 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConfermaMail+" where "+global.dbConstant.clRandom+"='"+hash+"'");
163 if(returnQuery!=null)
165 if(returnQuery.next())
167 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbUtenti+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+") VALUES(?,?)");
168 preparedStmt.setString (1,returnQuery.getString(global.dbConstant.clUser));
169 preparedStmt.setString (2,returnQuery.getString(global.dbConstant.clPass));
170 preparedStmt.execute();
171 preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConfermaMail+" WHERE "+global.dbConstant.clRandom+"=?");
172 preparedStmt.setString (1,hash);
173 preparedStmt.execute();
174 return "registrazione-ok";
177 return "registrazione-error";
180 //routin di gestione login
181 private static String login(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
184 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"' and "+global.dbConstant.clPass+"='"+pass+"'");
185 if(returnQuery!=null)
187 if(returnQuery.next())
189 if(returnQuery.getString(global.dbConstant.clUser).compareTo(user)==0 && returnQuery.getString(global.dbConstant.clPass).compareTo(pass)==0)
190 return putConnessi(user);
193 return "login-error";
197 //informazioni sulla connessione dell'utente
198 private String infoConnessi(String message) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
200 ArrayList<String> split= suString.stringToArrayList(message);
203 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConnessi+" WHERE "+global.dbConstant.clTempKey+"='"+split.get(1)+"'");
204 if(returnQuery!=null)
206 if(returnQuery.next())
212 return "request-login";
215 //inserisce l'utente in connessi
216 private static String putConnessi(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
218 Random random = new Random();
219 String ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
220 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConnessi+" ("+global.dbConstant.clUser+","+global.dbConstant.clTempKey+","+global.dbConstant.clTime+") VALUES(?,?,?)");
221 preparedStmt.setString (1,user);
222 preparedStmt.setString (2,ran);
223 preparedStmt.setDouble(3, (double) time());
224 preparedStmt.execute();
228 //remposta il rimer dell'utente nella tabella connessi
229 private String connessiTimer(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
231 PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbConnessi+" SET "+global.dbConstant.clTime+" =? where "+global.dbConstant.clTempKey+"=?");
232 preparedStmt.setDouble (1,(double)time());
233 preparedStmt.setString (2,key);
234 preparedStmt.execute();
235 return "timer-settato";
238 private String exerciseAccept(String request) throws SQLException, IOException, NoSuchAlgorithmException {
239 ArrayList<String> input=suString.stringToArrayList(request);
240 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM " + global.dbConstant.tbConnessi + " where " + global.dbConstant.clTempKey + "='" + input.get(0) + "'");
242 if(returnQuery!=null)
244 if(returnQuery.next())
245 utente=returnQuery.getString(global.dbConstant.clUser);
249 String[] esercizi=suString.stringToVectorString(EsNameParser.esName());
250 for(String esercizio : esercizi)
252 if(esercizio.contentEquals(input.get(1)))//se l'esercizio esiste
254 if(MD5.digest(esercizio).contentEquals(input.get(2)))//se il digest è uguale
256 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbEsercizi+" ("+global.dbConstant.clEsercizio+","+global.dbConstant.clUser+","+global.dbConstant.clVoto+","+global.dbConstant.clTime+") VALUES(?,?,?,?)");
257 preparedStmt.setDouble(4, Double.parseDouble(input.get(3)));
258 preparedStmt.setInt (3,Integer.valueOf(input.get(4)));
259 preparedStmt.setString (2,utente);
260 preparedStmt.setString (1,input.get(1));
261 preparedStmt.execute();
270 private String userExercise(String request) throws SQLException {
271 ArrayList<String> arrayResult=new ArrayList<String>();
272 ArrayList<String> input=suString.stringToArrayList(request);
274 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConnessi+" where "+global.dbConstant.clTempKey+"='"+input.get(0)+"'");
276 if(returnQuery!=null)
278 if(returnQuery.next())
279 utente=returnQuery.getString(global.dbConstant.clUser);
283 returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbEsercizi+" where "+global.dbConstant.clUser+"='"+utente+"'");
284 while(returnQuery.next())
286 arrayResult.add(returnQuery.getString(global.dbConstant.clEsercizio)+"/"+MD5.digest(returnQuery.getString(global.dbConstant.clEsercizio))+"/"+returnQuery.getDouble(global.dbConstant.clTime)+"/"+returnQuery.getInt(global.dbConstant.clVoto));
288 result=suString.multiToString(arrayResult);
293 private String cambiaPass(String request) throws ClassNotFoundException, SQLException, InstantiationException, IOException, IllegalAccessException {
294 ArrayList<String> input=suString.stringToArrayList(request);
295 if(input.size()>=3) {
296 String result = login(input.get(0), input.get(1));
297 if(!result.contains("err"))
299 PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbUtenti+" SET "+global.dbConstant.clPass+" =? where "+global.dbConstant.clUser+"=?");
300 preparedStmt.setString (1,input.get(2));
301 preparedStmt.setString (2,input.get(0));
302 preparedStmt.execute();
309 private String esAndMD5()
311 String list=EsNameParser.esName();
312 ArrayList<String> esList=suString.stringToArrayList(list);
313 ArrayList<String> ritorno=new ArrayList<String>();
314 for(String esercizio : esList)
316 ritorno.add(esercizio+"/"+MD5.digest(esercizio));
318 return suString.multiToString(ritorno);
321 public String dbQuery(String input) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException, NoSuchAlgorithmException {
322 Class.forName("com.mysql.jdbc.Driver").newInstance();
323 db = DriverManager.getConnection(global.dbConstant.dbLink+global.dbConstant.dbName, global.dbConstant.dbAdmin, global.dbConstant.dbPassword);
324 stmt = db.createStatement();
326 if (input.charAt(0) == '0')
327 return splitUsrPass(input.substring(2), 0);
328 else if (input.charAt(0) == '1')
329 return splitUsrPass(input.substring(2), 1);
330 else if (input.charAt(0) == '2')
331 return recupero(input.substring(2));
332 else if (input.charAt(0) == '3')
333 return confirmRegistration(input.substring(1));
334 else if (input.charAt(0) == '4')
335 return EsNameParser.esName();
336 else if (input.charAt(0) == '5')
337 return infoConnessi(input.substring(2));
338 else if (input.charAt(0) == '6')
339 return connessiTimer(input.substring(2));
340 else if (input.charAt(0) == '7')
342 else if (input.charAt(0) == '8')
343 return exerciseAccept(input.substring(2));
344 else if (input.charAt(0) == '9')
345 return userExercise(input.substring(2));
346 else if(input.charAt(0)=='a' && input.charAt(1)=='0')
347 return cambiaPass(input.substring(3));