Source code: com/rsi/ipstat/DBManager.java
1 /*
2
3 This software is open-source and is distributed under the terms of the GNU
4 General Public License.
5
6 Copyright (c) 2000-2002 Sergey I. Rotar <rsi@isp.od.ua>
7
8 */
9
10 package com.rsi.ipstat;
11
12 import java.sql.*;
13 import java.io.*;
14 import java.util.*;
15
16 import com.wrox.connectionpool.*;
17
18 public class DBManager
19 {
20 static PoolManager poolManager = PoolManager.getInstance();
21
22 public static UserStat getUserStat(String user, String time, int port)
23 {
24 PreparedStatement st;
25 Connection con = null;
26 UserStat us = new UserStat();
27
28 try {
29 con = poolManager.getConnection(Define.Project);
30
31 StringBuffer query = new StringBuffer();
32 query.append("SELECT BytesIn, BytesOut, DATE_FORMAT(UpdateTime,'%H:%i:%s %d.%m.%Y') FROM ");
33 query.append(user);
34 query.append(" WHERE DATE_FORMAT(UpdateTime,'%d.%m.%Y')='");
35 query.append(time);
36 query.append("' AND PID=");
37 query.append(port);
38 query.append(" ORDER BY UpdateTime;");
39 st = con.prepareStatement(query.toString());
40
41 ResultSet rs = st.executeQuery();
42 while (us.realLength < Define.ROWS && rs.next()){
43 us.bytein[us.realLength] = rs.getLong(1);
44 us.byteout[us.realLength] = rs.getLong(2);
45 us.time[us.realLength] = rs.getString(3);
46 us.realLength++;
47 }
48 rs.close();
49 st.close();
50 }
51 catch (Exception e) {
52 System.out.println(e.getMessage());
53 }
54 finally {
55 poolManager.freeConnection(Define.Project, con);
56 }
57 return us;
58 }
59
60 public static UserStat getUserStatByDay(String user, String time, int port)
61 {
62 PreparedStatement st;
63 Connection con = null;
64 UserStat us = new UserStat();
65
66 try {
67 con = poolManager.getConnection(Define.Project);
68
69 StringBuffer query = new StringBuffer();
70 query.append("SELECT SUM(BytesIn), SUM(BytesOut), DATE_FORMAT(UpdateTime, '%d.%m.%Y') AS ft FROM ");
71 query.append(user);
72 query.append(" WHERE DATE_FORMAT(UpdateTime, '%m.%Y')='");
73 query.append(time);
74 query.append("' AND PID=");
75 query.append(port);
76 query.append(" GROUP BY ft ORDER BY ft;");
77 st = con.prepareStatement(query.toString());
78
79 ResultSet rs = st.executeQuery();
80 while (us.realLength < Define.ROWS && rs.next()) {
81 us.bytein[us.realLength] = rs.getLong(1);
82 us.byteout[us.realLength] = rs.getLong(2);
83 us.time[us.realLength] = rs.getString(3);
84 us.realLength++;
85 }
86 rs.close();
87 st.close();
88 }
89 catch (Exception e) {
90 System.out.println(e.getMessage());
91 }
92 finally {
93 poolManager.freeConnection(Define.Project, con);
94 }
95 return us;
96 }
97
98 public static UserStat getUserStatByDay(String user, String from_time, String till_time, int port)
99 {
100 PreparedStatement st;
101 Connection con = null;
102 UserStat us = new UserStat();
103
104 try {
105 con = poolManager.getConnection(Define.Project);
106
107 StringBuffer query = new StringBuffer();
108 query.append("SELECT SUM(BytesIn), SUM(BytesOut), DATE_FORMAT(UpdateTime, '%d.%m.%Y') AS ft FROM ");
109 query.append(user);
110 query.append(" WHERE DATE_FORMAT(UpdateTime, '%Y.%m.%d') BETWEEN '");
111 query.append(from_time);
112 query.append("' AND '");
113 query.append(till_time);
114 query.append("' AND PID=");
115 query.append(port);
116 query.append(" GROUP BY ft ORDER BY ft;");
117 st = con.prepareStatement(query.toString());
118
119 ResultSet rs = st.executeQuery();
120 while (us.realLength < Define.ROWS && rs.next()) {
121 us.bytein[us.realLength] = rs.getLong(1);
122 us.byteout[us.realLength] = rs.getLong(2);
123 us.time[us.realLength] = rs.getString(3);
124 us.realLength++;
125 }
126 rs.close();
127 st.close();
128 }
129 catch (Exception e) {
130 System.out.println(e.getMessage());
131 }
132 finally {
133 poolManager.freeConnection(Define.Project, con);
134 }
135 return us;
136 }
137
138 public static MaxSum getUserStatMax(String user, String time, int port)
139 {
140 PreparedStatement st;
141 Connection con = null;
142 MaxSum ms = new MaxSum();
143
144 try {
145 con = poolManager.getConnection(Define.Project);
146
147 StringBuffer query = new StringBuffer();
148 query.append("SELECT MAX(BytesIn), MAX(BytesOut), SUM(BytesIn), SUM(BytesOut) FROM ");
149 query.append(user);
150 query.append(" WHERE DATE_FORMAT(UpdateTime,'%d.%m.%Y')='");
151 query.append(time);
152 query.append("' AND PID=");
153 query.append(port);
154 query.append(";");
155 st = con.prepareStatement(query.toString());
156
157 ResultSet rs = st.executeQuery();
158 if (rs.next()) {
159 ms.maxin = rs.getLong(1);
160 ms.maxout = rs.getLong(2);
161 ms.sumin = rs.getLong(3);
162 ms.sumout = rs.getLong(4);
163 }
164 rs.close();
165 st.close();
166 }
167 catch (Exception e) {
168 System.out.println(e.getMessage());
169 }
170 finally {
171 poolManager.freeConnection(Define.Project, con);
172 }
173 return ms;
174 }
175
176
177 public static UserStat getSummaryStat(String user, String time, String str, int port)
178 {
179 PreparedStatement st;
180 Connection con = null;
181 UserStat us = new UserStat(1);
182
183 try {
184 con = poolManager.getConnection(Define.Project);
185
186 StringBuffer query = new StringBuffer();
187 query.append("SELECT SUM(BytesIn), SUM(BytesOut) FROM ");
188 query.append(user);
189 query.append(" WHERE DATE_FORMAT(UpdateTime,'");
190 query.append(str);
191 query.append("')='");
192 query.append(time);
193 query.append("' AND PID=");
194 query.append(port);
195 query.append(";");
196 st = con.prepareStatement(query.toString());
197
198 ResultSet rs = st.executeQuery();
199 if (rs.next()) {
200 us.bytein[0] = rs.getLong(1);
201 us.byteout[0] = rs.getLong(2);
202 }
203 st.close();
204 rs.close();
205 }
206 catch (Exception e) {
207 System.out.println(e.getMessage());
208 }
209 finally {
210 poolManager.freeConnection(Define.Project, con);
211 }
212 return us;
213 }
214
215 public static UserStat getSummaryStat(String user, String from_time, String till_time, String str, int port)
216 {
217 PreparedStatement st;
218 Connection con = null;
219 UserStat us = new UserStat(1);
220
221 try {
222 con = poolManager.getConnection(Define.Project);
223
224 StringBuffer query = new StringBuffer();
225 query.append("SELECT SUM(BytesIn), SUM(BytesOut) FROM ");
226 query.append(user);
227 query.append(" WHERE DATE_FORMAT(UpdateTime,'");
228 query.append(str);
229 query.append("') BETWEEN '");
230 query.append(from_time);
231 query.append("' AND '");
232 query.append(till_time);
233 query.append("' AND PID=");
234 query.append(port);
235 query.append(";");
236 st = con.prepareStatement(query.toString());
237
238 ResultSet rs = st.executeQuery();
239 if (rs.next()) {
240 us.bytein[0] = rs.getLong(1);
241 us.byteout[0] = rs.getLong(2);
242 }
243 rs.close();
244 st.close();
245 }
246 catch (Exception e) {
247 System.out.println(e.getMessage());
248 }
249 finally {
250 poolManager.freeConnection(Define.Project, con);
251 }
252 return us;
253 }
254
255 ///////////////////////////////////////////////////////////////////////////////////////
256 ///////////////////////////////////* User Table *//////////////////////////////////////
257 ///////////////////////////////////////////////////////////////////////////////////////
258
259 public static boolean userAdd(UserTable usr)
260 {
261 Connection con = null;
262 boolean res = false;
263
264 try {
265 con = poolManager.getConnection(Define.Project);
266 Statement st = con.createStatement();
267
268 StringBuffer query = new StringBuffer();
269 query.append("INSERT INTO Users (Name, Passwd, HostName, IPAddress, IPMask, Invert, LimitIn, LimitOut, DayLimitIn, DayLimitOut, Enabled, WarnProc, StopProc, Email, LimitTotal, Day, Month, Year, Expiration) VALUES ('");
270 query.append(usr.user[0]);
271 query.append("', PASSWORD('");
272 query.append(usr.passwd[0]);
273 query.append("'), '");
274 query.append(usr.hostname[0]);
275 query.append("', '");
276 query.append(usr.ip_addr[0]);
277 query.append("', '");
278 query.append(usr.ip_mask[0]);
279 query.append("', ");
280 if (usr.invert[0])
281 query.append("1,");
282 else
283 query.append("0,");
284 query.append(usr.limit_in[0]);
285 query.append(", ");
286 query.append(usr.limit_out[0]);
287 query.append(", ");
288 query.append(usr.day_limit_in[0]);
289 query.append(", ");
290 query.append(usr.day_limit_out[0]);
291 query.append(", ");
292 // OUR MEMBERS
293 if (usr.Enabled[0])
294 query.append("1,");
295 else
296 query.append("0,");
297 query.append(usr.WarnProc[0]);
298 query.append(", ");
299 query.append(usr.StopProc[0]);
300 query.append(", '");
301 query.append(usr.Email[0]);
302 query.append("', ");
303 query.append(usr.LimitTotal[0]);
304 query.append(", ");
305 query.append(usr.Day[0]);
306 query.append(", ");
307 query.append(usr.Year[0]);
308 query.append(", ");
309 query.append(usr.Month[0]);
310 query.append(", ");
311 query.append(usr.Expiration[0]);
312 // OUR MEMBERS
313 query.append(");");
314 st.executeUpdate(query.toString());
315 System.out.println(query.toString());
316
317 st.close();
318 res = true;
319 }
320 catch (Exception e) {
321 System.out.println(e.getMessage());
322 }
323 finally {
324 poolManager.freeConnection(Define.Project, con);
325 }
326 return res;
327 }
328
329 public static boolean userUpdate(UserTable usr)
330 {
331 Connection con = null;
332 PreparedStatement ps;
333 boolean res = false;
334 String user = usr.user[0];
335
336 try {
337 con = poolManager.getConnection(Define.Project);
338
339 StringBuffer query = new StringBuffer();
340 query.append("SELECT Name FROM Users WHERE ID=");
341 query.append(usr.id[0]);
342 query.append(";");
343 ps = con.prepareStatement(query.toString());
344
345 ResultSet rs = ps.executeQuery();
346 if (rs.next()) user = rs.getString(1);
347 rs.close();
348 ps.close();
349
350 Statement st = con.createStatement();
351 query = new StringBuffer();
352 query.append("UPDATE Users SET Name='");
353 query.append(usr.user[0]);
354 query.append("', ");
355 if (usr.passwd[0] != null && !usr.passwd[0].equals("")) {
356 query.append("Passwd=PASSWORD('");
357 query.append(usr.passwd[0]);
358 query.append("'), ");
359 }
360 query.append("HostName='");
361 query.append(usr.hostname[0]);
362 query.append("', IPAddress='");
363 query.append(usr.ip_addr[0]);
364 query.append("', IPMask='");
365 query.append(usr.ip_mask[0]);
366 if (usr.invert[0])
367 query.append("', Invert=1");
368 else
369 query.append("', Invert=0");
370 query.append(", LimitIn=");
371 query.append(usr.limit_in[0]);
372 query.append(", LimitOut=");
373 query.append(usr.limit_out[0]);
374 query.append(", DayLimitIn=");
375 query.append(usr.day_limit_in[0]);
376 query.append(", DayLimitOut=");
377 query.append(usr.day_limit_out[0]);
378 // OUR MEMBERS
379 if (usr.Enabled[0])
380 query.append(", Enabled=1");
381 else
382 query.append(", Enabled=0");
383 query.append(", WarnProc=");
384 query.append(usr.WarnProc[0]);
385 query.append(", StopProc=");
386 query.append(usr.StopProc[0]);
387 query.append(", Email='");
388 query.append(usr.Email[0]);
389 query.append("', LimitTotal=");
390 query.append(usr.LimitTotal[0]);
391 query.append(", Day=");
392 query.append(usr.Day[0]);
393 query.append(", Month=");
394 query.append(usr.Month[0]);
395 query.append(", Year=");
396 query.append(usr.Year[0]);
397 query.append(", Expiration=");
398 query.append(usr.Expiration[0]);
399 // OUR MEMBERS
400 query.append(" WHERE ID=");
401 query.append(usr.id[0]);
402 query.append(";");
403 st.executeUpdate(query.toString());
404 System.out.println(query.toString());
405
406 query = new StringBuffer();
407 query.append("ALTER TABLE ");
408 query.append(user);
409 query.append(" RENAME ");
410 query.append(usr.user[0]);
411 query.append(";");
412 System.out.println(query.toString());
413 st.executeUpdate(query.toString());
414
415 st.close();
416 res = true;
417 }
418 catch (Exception e) {
419 System.out.println(e.getMessage());
420 }
421 finally {
422 poolManager.freeConnection(Define.Project, con);
423 }
424 return res;
425 }
426
427 public static boolean userDelete(String user)
428 {
429 Connection con = null;
430 boolean res = false;
431
432 try {
433 con = poolManager.getConnection(Define.Project);
434 Statement st = con.createStatement();
435
436 StringBuffer query = new StringBuffer();
437 query.append("DELETE FROM Users WHERE Name='");
438 query.append(user);
439 query.append("';");
440 st.executeUpdate(query.toString());
441
442 query = new StringBuffer();
443 query.append("DROP TABLE ");
444 query.append(user);
445 query.append(";");
446 st.executeUpdate(query.toString());
447
448 st.close();
449 res = true;
450
451 }
452 catch (Exception e) {
453 System.out.println(e.getMessage());
454 }
455 finally {
456 poolManager.freeConnection(Define.Project, con);
457 }
458 return res;
459 }
460
461 public static String getUser(String user, String passwd)
462 {
463 Connection con = null;
464 PreparedStatement st;
465 String usr = new String();
466
467 try {
468 con = poolManager.getConnection(Define.Project);
469
470 StringBuffer query = new StringBuffer();
471 query.append("SELECT Name FROM Users WHERE Name='");
472 query.append(user);
473 query.append("' and Passwd=PASSWORD('");
474 query.append(passwd);
475 query.append("');");
476 st = con.prepareStatement(query.toString());
477
478 ResultSet rs = st.executeQuery();
479 if (rs.next()) usr = rs.getString(1);
480 rs.close();
481 st.close();
482 }
483 catch (Exception e) {
484 System.out.println(e.getMessage());
485 }
486 finally {
487 poolManager.freeConnection(Define.Project, con);
488 }
489 return usr;
490 }
491
492 public static boolean checkUser(String user, int id)
493 {
494 Connection con = null;
495 PreparedStatement st;
496 boolean res = false;
497
498 try {
499 con = poolManager.getConnection(Define.Project);
500
501 StringBuffer query = new StringBuffer();
502 query.append("SELECT ID FROM Users WHERE ID!=");
503 query.append(id);
504 query.append(" AND Name='");
505 query.append(user);
506 query.append("';");
507 st = con.prepareStatement(query.toString());
508
509 ResultSet rs = st.executeQuery();
510 if (rs.next()) res = true;
511 rs.close();
512 st.close();
513 }
514 catch (Exception e) {
515 System.out.println(e.getMessage());
516 }
517 finally {
518 poolManager.freeConnection(Define.Project, con);
519 }
520 return res;
521 }
522
523 public static boolean checkIP(String ip, int id)
524 {
525 Connection con = null;
526 PreparedStatement st;
527 boolean res = false;
528
529 try {
530 con = poolManager.getConnection(Define.Project);
531
532 StringBuffer query = new StringBuffer();
533 query.append("SELECT ID FROM Users WHERE ID!=");
534 query.append(id);
535 query.append(" AND IPAddress='");
536 query.append(ip);
537 query.append("';");
538 st = con.prepareStatement(query.toString());
539
540 ResultSet rs = st.executeQuery();
541 if (rs.next()) res = true;
542 rs.close();
543 st.close();
544 }
545 catch (Exception e) {
546 System.out.println(e.getMessage());
547 }
548 finally {
549 poolManager.freeConnection(Define.Project, con);
550 }
551 return res;
552 }
553
554 public static UserTable getUserByID(int id)
555 {
556 Connection con = null;
557 PreparedStatement st;
558 UserTable ut = new UserTable(1);
559
560 try {
561 con = poolManager.getConnection(Define.Project);
562
563 StringBuffer query = new StringBuffer();
564 query.append("SELECT Name, HostName, IPAddress, IPMask, Invert, LimitIn, LimitOut, DayLimitIn, DayLimitOut, Enabled, WarnProc, StopProc, Email, LimitTotal, Day, Month, Year, Expiration FROM Users WHERE ID=");
565 query.append(id);
566 query.append(";");
567 st = con.prepareStatement(query.toString());
568
569 ResultSet rs = st.executeQuery();
570 if (rs.next()) {
571 ut.id[0] = id;
572 ut.user[0] = rs.getString(1);
573 ut.hostname[0] = rs.getString(2);
574 ut.ip_addr[0] = rs.getString(3);
575 ut.ip_mask[0] = rs.getString(4);
576 ut.invert[0] = rs.getBoolean(5);
577 ut.limit_in[0] = rs.getLong(6);
578 ut.limit_out[0] = rs.getLong(7);
579 ut.day_limit_in[0] = rs.getLong(8);
580 ut.day_limit_out[0] = rs.getLong(9);
581 // OUR MEMBERS
582 ut.Enabled[0] = rs.getBoolean(10);
583 ut.WarnProc[0] = rs.getLong(11);
584 ut.StopProc[0] = rs.getLong(12);
585 ut.Email[0] = rs.getString(13);
586 ut.LimitTotal[0] = rs.getLong(14);
587 ut.Day[0] = rs.getLong(15);
588 ut.Month[0] = rs.getLong(16);
589 ut.Year[0] = rs.getLong(17);
590 ut.Expiration[0] = rs.getLong(18);
591 // OUR MEMBERS
592 ut.realLength = 1;
593 }
594 rs.close();
595 st.close();
596 }
597 catch (Exception e) {
598 System.out.println(e.getMessage());
599 }
600 finally {
601 poolManager.freeConnection(Define.Project, con);
602 }
603 return ut;
604 }
605
606 public static UserTable getUserList()
607 {
608 Connection con = null;
609 PreparedStatement st;
610 int count = getUserCount();
611 UserTable ut = new UserTable(count);
612
613 try {
614 con = poolManager.getConnection(Define.Project);
615
616 st = con.prepareStatement("SELECT ID, Name, IPAddress, IPMask, LimitIn, LimitOut, DayLimitIn, DayLimitOut, Enabled, WarnProc, StopProc, Email, LimitTotal, Day, Month, Year, Expiration FROM Users ORDER BY Name;");
617
618 ResultSet rs = st.executeQuery();
619 while (ut.realLength < count && rs.next()) {
620 ut.id[ut.realLength] = rs.getInt(1);
621 ut.user[ut.realLength] = rs.getString(2);
622 ut.ip_addr[ut.realLength] = rs.getString(3);
623 ut.ip_mask[ut.realLength] = rs.getString(4);
624 ut.limit_in[ut.realLength] = rs.getLong(5);
625 ut.limit_out[ut.realLength] = rs.getLong(6);
626 ut.day_limit_in[ut.realLength] = rs.getLong(7);
627 ut.day_limit_out[ut.realLength] = rs.getLong(8);
628 // OUR MEMBERS
629 ut.Enabled[ut.realLength] = rs.getBoolean(9);
630 ut.WarnProc[ut.realLength] = rs.getLong(10);
631 ut.StopProc[ut.realLength] = rs.getLong(11);
632 ut.Email[ut.realLength] = rs.getString(12);
633 ut.LimitTotal[ut.realLength] = rs.getLong(13);
634 ut.Day[ut.realLength] = rs.getLong(14);
635 ut.Month[ut.realLength] = rs.getLong(15);
636 ut.Year[ut.realLength] = rs.getLong(16);
637 ut.Expiration[ut.realLength] = rs.getLong(17);
638 // OUR MEMBERS
639 ut.realLength++;
640 }
641 rs.close();
642 st.close();
643 }
644 catch (Exception e) {
645 System.out.println(e.getMessage());
646 }
647 finally {
648 poolManager.freeConnection(Define.Project, con);
649 }
650 return ut;
651 }
652
653 public static UserTable getUserShortList()
654 {
655 Connection con = null;
656 PreparedStatement st;
657 int count = getUserCount();
658 UserTable ut = new UserTable(count);
659
660 try {
661 con = poolManager.getConnection(Define.Project);
662
663 st = con.prepareStatement("SELECT ID, Name, IPAddress, IPMask FROM Users ORDER BY Name;");
664
665 ResultSet rs = st.executeQuery();
666 while (ut.realLength < count && rs.next()) {
667 ut.id[ut.realLength] = rs.getInt(1);
668 ut.user[ut.realLength] = rs.getString(2);
669 ut.ip_addr[ut.realLength] = rs.getString(3);
670 ut.ip_mask[ut.realLength] = rs.getString(4);
671 ut.realLength++;
672 }
673 rs.close();
674 st.close();
675 }
676 catch (Exception e) {
677 System.out.println(e.getMessage());
678 }
679 finally {
680 poolManager.freeConnection(Define.Project, con);
681 }
682 return ut;
683 }
684
685 public static int getUserCount()
686 {
687 Connection con = null;
688 PreparedStatement st;
689 int count = 0;
690
691 try {
692 con = poolManager.getConnection(Define.Project);
693
694 st = con.prepareStatement("SELECT COUNT(*) FROM Users;");
695
696 ResultSet rs = st.executeQuery();
697 if (rs.next()) count = rs.getInt(1);
698 rs.close();
699 st.close();
700 }
701 catch (Exception e) {
702 System.out.println(e.getMessage());
703 }
704 finally {
705 poolManager.freeConnection(Define.Project, con);
706 }
707 return count;
708 }
709
710 ///////////////////////////////////////////////////////////////////////////////////////
711 ///////////////////////////////////* Port Table *//////////////////////////////////////
712 ///////////////////////////////////////////////////////////////////////////////////////
713
714 public static boolean portAdd(PortTable port)
715 {
716 Connection con = null;
717 boolean res = false;
718
719 try {
720 con = poolManager.getConnection(Define.Project);
721 Statement st = con.createStatement();
722
723 StringBuffer query = new StringBuffer();
724 query.append("INSERT INTO Ports (Name, Source1, Source2, Destination1, Destination2) VALUES ('");
725 query.append(port.name[0]);
726 query.append("', ");
727 query.append(port.src1[0]);
728 query.append(", ");
729 query.append(port.src2[0]);
730 query.append(", ");
731 query.append(port.dst1[0]);
732 query.append(", ");
733 query.append(port.dst2[0]);
734 query.append(");");
735 st.executeUpdate(query.toString());
736
737 st.close();
738 res = true;
739 }
740 catch (Exception e) {
741 System.out.println(e.getMessage());
742 }
743 finally {
744 poolManager.freeConnection(Define.Project, con);
745 }
746 return res;
747 }
748
749 public static boolean portUpdate(PortTable port)
750 {
751 Connection con = null;
752 boolean res = false;
753
754 try {
755 con = poolManager.getConnection(Define.Project);
756
757 Statement st = con.createStatement();
758 StringBuffer query = new StringBuffer();
759 query.append("UPDATE Ports SET Name='");
760 query.append(port.name[0]);
761 query.append("', Source1=");
762 query.append(port.src1[0]);
763 query.append(", Source2=");
764 query.append(port.src2[0]);
765 query.append(", Destination1=");
766 query.append(port.dst1[0]);
767 query.append(", Destination2=");
768 query.append(port.dst2[0]);
769 query.append(" WHERE PID=");
770 query.append(port.pid[0]);
771 query.append(";");
772 st.executeUpdate(query.toString());
773
774 st.close();
775 res = true;
776 }
777 catch (Exception e) {
778 System.out.println(e.getMessage());
779 }
780 finally {
781 poolManager.freeConnection(Define.Project, con);
782 }
783 return res;
784 }
785
786 public static boolean portDelete(int pid)
787 {
788 Connection con = null;
789 boolean res = false;
790
791 try {
792 con = poolManager.getConnection(Define.Project);
793 Statement st = con.createStatement();
794
795 StringBuffer query = new StringBuffer();
796 query.append("DELETE FROM Ports WHERE PID=");
797 query.append(pid);
798 query.append(";");
799 st.executeUpdate(query.toString());
800
801 UserTable ut = getUserShortList();
802 for (int i = 0; i < ut.realLength; i++) {
803 query = new StringBuffer();
804 query.append("DELETE FROM ");
805 query.append(ut.user[i]);
806 query.append(" WHERE PID=");
807 query.append(pid);
808 query.append(";");
809 st.executeUpdate(query.toString());
810 }
811
812 st.close();
813 res = true;
814
815 }
816 catch (Exception e) {
817 System.out.println(e.getMessage());
818 }
819 finally {
820 poolManager.freeConnection(Define.Project, con);
821 }
822 return res;
823 }
824
825 public static boolean checkPort(String name, int pid)
826 {
827 Connection con = null;
828 PreparedStatement st;
829 boolean res = false;
830
831 try {
832 con = poolManager.getConnection(Define.Project);
833
834 StringBuffer query = new StringBuffer();
835 query.append("SELECT PID FROM Ports WHERE PID!=");
836 query.append(pid);
837 query.append(" AND Name='");
838 query.append(name);
839 query.append("';");
840 st = con.prepareStatement(query.toString());
841
842 ResultSet rs = st.executeQuery();
843 if (rs.next()) res = true;
844 rs.close();
845 st.close();
846 }
847 catch (Exception e) {
848 System.out.println(e.getMessage());
849 }
850 finally {
851 poolManager.freeConnection(Define.Project, con);
852 }
853 return res;
854 }
855
856 public static PortTable getPortByID(int pid)
857 {
858 Connection con = null;
859 PreparedStatement st;
860 PortTable pt = new PortTable(1);
861
862 try {
863 con = poolManager.getConnection(Define.Project);
864
865 StringBuffer query = new StringBuffer();
866 query.append("SELECT Name, Source1, Source2, Destination1, Destination2 FROM Ports WHERE PID=");
867 query.append(pid);
868 query.append(";");
869 st = con.prepareStatement(query.toString());
870
871 ResultSet rs = st.executeQuery();
872 if (rs.next()) {
873 pt.pid[0] = pid;
874 pt.name[0] = rs.getString(1);
875 pt.src1[0] = rs.getInt(2);
876 pt.src2[0] = rs.getInt(3);
877 pt.dst1[0] = rs.getInt(4);
878 pt.dst2[0] = rs.getInt(5);
879 pt.realLength = 1;
880 }
881 rs.close();
882 st.close();
883 }
884 catch (Exception e) {
885 System.out.println(e.getMessage());
886 }
887 finally {
888 poolManager.freeConnection(Define.Project, con);
889 }
890 return pt;
891 }
892
893 public static PortTable getPortList()
894 {
895 Connection con = null;
896 PreparedStatement st;
897 int count = getPortCount();
898 PortTable pt = new PortTable(count);
899
900 try {
901 con = poolManager.getConnection(Define.Project);
902
903 st = con.prepareStatement("SELECT PID, Name, Source1, Source2, Destination1, Destination2 FROM Ports ORDER BY Name;");
904
905 ResultSet rs = st.executeQuery();
906 while (pt.realLength < count && rs.next()) {
907 pt.pid[pt.realLength] = rs.getInt(1);
908 pt.name[pt.realLength] = rs.getString(2);
909 pt.src1[pt.realLength] = rs.getInt(3);
910 pt.src2[pt.realLength] = rs.getInt(4);
911 pt.dst1[pt.realLength] = rs.getInt(5);
912 pt.dst2[pt.realLength] = rs.getInt(6);
913 pt.realLength++;
914 }
915 rs.close();
916 st.close();
917 }
918 catch (Exception e) {
919 System.out.println(e.getMessage());
920 }
921 finally {
922 poolManager.freeConnection(Define.Project, con);
923 }
924 return pt;
925 }
926
927 public static PortTable getPortShortList()
928 {
929 Connection con = null;
930 PreparedStatement st;
931 int count = getPortCount();
932 PortTable pt = new PortTable(count);
933
934 try {
935 con = poolManager.getConnection(Define.Project);
936
937 st = con.prepareStatement("SELECT PID, Name FROM Ports ORDER BY Name;");
938
939 ResultSet rs = st.executeQuery();
940 while (pt.realLength < count && rs.next()) {
941 pt.pid[pt.realLength] = rs.getInt(1);
942 pt.name[pt.realLength] = rs.getString(2);
943 pt.realLength++;
944 }
945 rs.close();
946 st.close();
947 }
948 catch (Exception e) {
949 System.out.println(e.getMessage());
950 }
951 finally {
952 poolManager.freeConnection(Define.Project, con);
953 }
954 return pt;
955 }
956
957 public static int getPortCount()
958 {
959 Connection con = null;
960 PreparedStatement st;
961 int count = 0;
962
963 try {
964 con = poolManager.getConnection(Define.Project);
965
966 st = con.prepareStatement("SELECT COUNT(*) FROM Ports;");
967
968 ResultSet rs = st.executeQuery();
969 if (rs.next()) count = rs.getInt(1);
970 rs.close();
971 st.close();
972 }
973 catch (Exception e) {
974 System.out.println(e.getMessage());
975 }
976 finally {
977 poolManager.freeConnection(Define.Project, con);
978 }
979 return count;
980 }
981
982
983 }