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;
23 Class.forName("com.mysql.jdbc.Driver").newInstance();
24 db = DriverManager.getConnection(global.dbConstant.dbLink + global.dbConstant.dbName, global.dbConstant.dbAdmin, global.dbConstant.dbPassword);
25 stmt = db.createStatement();
26 } catch (ClassNotFoundException e) {
28 } catch (SQLException e) {
30 } catch (InstantiationException e) {
32 } catch (IllegalAccessException e) {
38 public static Long time()
40 java.util.Date date= new java.util.Date();
41 Timestamp now=new Timestamp(date.getTime());
45 //pulisce confmail e connessi da tutte le richieste maggiori di 10 minuti
46 private static void deletetmp() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
48 PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConfermaMail+" WHERE "+global.dbConstant.clTime+"<=?");
49 preparedStmt.setLong (1,(time()-600000));
50 preparedStmt.execute();
51 preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConnessi+" WHERE "+global.dbConstant.clTime+"<=?");
52 preparedStmt.setLong (1,(time()-600000));
53 preparedStmt.execute();
56 //divide la stringa in user e password
57 private static String splitUsrPass(String userAndPass, int code) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
61 ArrayList<String> userPass= suString.stringToArrayList(userAndPass);
62 if(userPass.size()>=1)
64 if(userPass.size()>=2)
68 if(!user.contains("@studio.unibo.it") && user.length()<16)
72 if (pass.length()>6) {
74 return registration(user, pass);
76 return login(user, pass);
78 return recupero(user);
86 //routin di gestione recupero pass
87 private static String recupero(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
89 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"'");
92 if(returnQuery.next())
94 EmailSender email = new EmailSender(global.emailConstant.email,
95 global.emailConstant.emailPass,
97 global.emailConstant.email,
99 "OGGETTO: recupero password LogicPlayer",
100 "la tua password di LogicPlayer è "+returnQuery.getString(global.dbConstant.clPass));
102 return "mail-pass-ok";
112 //routin di gestione della registrazione
113 private static String registration(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
116 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"'");
117 if(returnQuery!=null)
119 if(returnQuery.next())
121 if(returnQuery.getString(global.dbConstant.clUser).compareTo(user)==0 && returnQuery.getString(global.dbConstant.clPass).compareTo(pass)==0)
122 return "già-registrato";
125 returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConfermaMail+" where "+global.dbConstant.clUser+"='"+user+"'");
126 if(returnQuery!=null)
128 if(returnQuery.next())
130 ran=returnQuery.getString(global.dbConstant.clRandom);
131 if(returnQuery.getString(global.dbConstant.clPass).compareTo(pass)!=0)
133 PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbConfermaMail+" SET "+global.dbConstant.clPass+" =? where user=?");
134 preparedStmt.setString (1,pass);
135 preparedStmt.setString (2,user);
136 preparedStmt.execute();
141 Random random = new Random();
142 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
143 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConfermaMail+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+","+global.dbConstant.clRandom+","+global.dbConstant.clTime+") VALUES(?,?,?,?)");
144 preparedStmt.setString (1,user);
145 preparedStmt.setString (2,pass);
146 preparedStmt.setString (3,ran);
147 preparedStmt.setDouble (4,(double)time());
148 preparedStmt.execute();
153 Random random = new Random();
154 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
155 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConfermaMail+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+","+global.dbConstant.clRandom+","+global.dbConstant.clTime+") VALUES(?,?,?,?)");
156 preparedStmt.setString (1,user);
157 preparedStmt.setString (2,pass);
158 preparedStmt.setString (3,ran);
159 preparedStmt.setDouble(4, (double) time());
160 preparedStmt.execute();
162 EmailSender email = new EmailSender(global.emailConstant.email,
163 global.emailConstant.emailPass,
164 global.emailConstant.smtpName,
165 global.emailConstant.email,
167 "OGGETTO: Registrazione LogicPlayer",
168 "per confermare la tua email all'applicazione LogicPlayer clicka sul link: http://"+global.serverName+":"+global.portaHttpServer+"/test?id="+ran);
170 return "mail-reg-ok";
173 //routin conferma registrazione
174 private static String confirmRegistration(String hash) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
176 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConfermaMail+" where "+global.dbConstant.clRandom+"='"+hash+"'");
177 if(returnQuery!=null)
179 if(returnQuery.next())
181 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbUtenti+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+") VALUES(?,?)");
182 preparedStmt.setString (1,returnQuery.getString(global.dbConstant.clUser));
183 preparedStmt.setString (2,returnQuery.getString(global.dbConstant.clPass));
184 preparedStmt.execute();
185 preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConfermaMail+" WHERE "+global.dbConstant.clRandom+"=?");
186 preparedStmt.setString (1,hash);
187 preparedStmt.execute();
188 return "registrazione-ok";
191 return "registrazione-error";
194 //routin di gestione login
195 private static String login(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
198 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"' and "+global.dbConstant.clPass+"='"+pass+"'");
199 if(returnQuery!=null)
201 if(returnQuery.next())
203 if(returnQuery.getString(global.dbConstant.clUser).compareTo(user)==0 && returnQuery.getString(global.dbConstant.clPass).compareTo(pass)==0)
204 return putConnessi(user);
207 return "login-error";
211 //informazioni sulla connessione dell'utente
212 private String infoConnessi(String message) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
214 ArrayList<String> split= suString.stringToArrayList(message);
217 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConnessi+" WHERE "+global.dbConstant.clTempKey+"='"+split.get(1)+"'");
218 if(returnQuery!=null)
220 if(returnQuery.next())
226 return "request-login";
229 //inserisce l'utente in connessi
230 private static String putConnessi(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
232 Random random = new Random();
233 String ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
234 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConnessi+" ("+global.dbConstant.clUser+","+global.dbConstant.clTempKey+","+global.dbConstant.clTime+") VALUES(?,?,?)");
235 preparedStmt.setString (1,user);
236 preparedStmt.setString (2,ran);
237 preparedStmt.setDouble(3, (double) time());
238 preparedStmt.execute();
242 //remposta il rimer dell'utente nella tabella connessi
243 private String connessiTimer(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
245 PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbConnessi+" SET "+global.dbConstant.clTime+" =? where "+global.dbConstant.clTempKey+"=?");
246 preparedStmt.setDouble (1,(double)time());
247 preparedStmt.setString (2,key);
248 preparedStmt.execute();
249 return "timer-settato";
252 private String exerciseAccept(String request) throws SQLException, IOException, NoSuchAlgorithmException {
253 ArrayList<String> input=suString.stringToArrayList(request);
254 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM " + global.dbConstant.tbConnessi + " where " + global.dbConstant.clTempKey + "='" + input.get(0) + "'");
256 if(returnQuery!=null)
258 if(returnQuery.next())
259 utente=returnQuery.getString(global.dbConstant.clUser);
263 String[] esercizi=suString.stringToVectorString(EsNameParser.esName());
264 for(String esercizio : esercizi)
266 if(esercizio.contentEquals(input.get(1)))//se l'esercizio esiste
268 if(MD5.digest(esercizio).contentEquals(input.get(2)))//se il digest è uguale
270 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbEsercizi+" ("+global.dbConstant.clEsercizio+","+global.dbConstant.clUser+","+global.dbConstant.clVoto+","+global.dbConstant.clTime+") VALUES(?,?,?,?)");
271 preparedStmt.setDouble(4, Double.parseDouble(input.get(3)));
272 preparedStmt.setInt (3,Integer.valueOf(input.get(4)));
273 preparedStmt.setString (2,utente);
274 preparedStmt.setString (1,input.get(1));
275 preparedStmt.execute();
284 private String userExercise(String request) throws SQLException {
285 ArrayList<String> arrayResult=new ArrayList<String>();
286 ArrayList<String> input=suString.stringToArrayList(request);
288 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConnessi+" where "+global.dbConstant.clTempKey+"='"+input.get(0)+"'");
290 if(returnQuery!=null)
292 if(returnQuery.next())
293 utente=returnQuery.getString(global.dbConstant.clUser);
297 returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbEsercizi+" where "+global.dbConstant.clUser+"='"+utente+"'");
298 while(returnQuery.next())
300 arrayResult.add(returnQuery.getString(global.dbConstant.clEsercizio)+"/"+MD5.digest(returnQuery.getString(global.dbConstant.clEsercizio))+"/"+returnQuery.getDouble(global.dbConstant.clTime)+"/"+returnQuery.getInt(global.dbConstant.clVoto));
302 result=suString.multiToString(arrayResult);
307 private String cambiaPass(String request) throws ClassNotFoundException, SQLException, InstantiationException, IOException, IllegalAccessException {
308 ArrayList<String> input=suString.stringToArrayList(request);
309 if(input.size()>=3) {
310 String result = login(input.get(0), input.get(1));
311 if(!result.contains("err"))
313 PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbUtenti+" SET "+global.dbConstant.clPass+" =? where "+global.dbConstant.clUser+"=?");
314 preparedStmt.setString (1,input.get(2));
315 preparedStmt.setString (2,input.get(0));
316 preparedStmt.execute();
323 private String esAndMD5()
325 String list=EsNameParser.esName();
326 ArrayList<String> esList=suString.stringToArrayList(list);
327 ArrayList<String> ritorno=new ArrayList<String>();
328 for(String esercizio : esList)
330 ritorno.add(esercizio+"/"+MD5.digest(esercizio));
332 return suString.multiToString(ritorno);
335 public String dbQuery(String input) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException, NoSuchAlgorithmException {
337 if (input.charAt(0) == '0')
338 return splitUsrPass(input.substring(2), 0);
339 else if (input.charAt(0) == '1')
340 return splitUsrPass(input.substring(2), 1);
341 else if (input.charAt(0) == '2')
342 return recupero(input.substring(2));
343 else if (input.charAt(0) == '3')
344 return confirmRegistration(input.substring(1));
345 else if (input.charAt(0) == '4')
346 return EsNameParser.esName();
347 else if (input.charAt(0) == '5')
348 return infoConnessi(input.substring(2));
349 else if (input.charAt(0) == '6')
350 return connessiTimer(input.substring(2));
351 else if (input.charAt(0) == '7')
353 else if (input.charAt(0) == '8')
354 return exerciseAccept(input.substring(2));
355 else if (input.charAt(0) == '9')
356 return userExercise(input.substring(2));
357 else if(input.charAt(0)=='a' && input.charAt(1)=='0')
358 return cambiaPass(input.substring(3));