-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB.java
97 lines (93 loc) · 2.9 KB
/
DB.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
import java.sql.*;
class DB{
//Male and female representing
public final String MALE = "Male";
protected final String FEMALE = "Female";
//Database Variables
protected String TABLE_NAME;
protected String DB_NAME;
protected final String DB_ADDRESS = "jdbc:mysql://localhost:3307/";
protected final String DB_USERNAME = "root";
protected final String DB_PASSWORD = "password";
private Connection conn;
public DB(String dbName,String userstable){
this.DB_NAME = dbName;
this.TABLE_NAME = userstable;
try{
conn = DriverManager.getConnection(DB_ADDRESS+DB_NAME+"?useSSL=false","root", "Amakau@123");
}catch(SQLException ex){
ex.printStackTrace();
}
}
private String nameColo = "firstname,lastname,telephone,gender";
public String booltoGender(boolean gender){
if(gender)return FEMALE;
else return MALE;
}
public void CreateUser(String fname,String lname,String telephone,String dob,Boolean gender){
try{
Statement stmt = conn.createStatement();
stmt.executeUpdate(
"INSERT INTO "+TABLE_NAME+" ("+nameColo+") VALUES('"+fname+"','"+lname+"','"+telephone+"','"+","+dob+","+booltoGender(gender)+"');"
);
}catch(SQLException ex){
ex.printStackTrace();
}
}
public double GetRatio(){return (double) (CountRows("`gender`='"+FEMALE+"'")/CountRows("`gender`='"+MALE+"'"));}
public boolean CheckUser(String fname,String lname,String telephone,String dob,boolean gender){
try{
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery("SELECT "+nameColo+" FROM "+TABLE_NAME+" WHERE `firstname`='"+fname+"' AND `lastname`='"+lname+"' AND `telephone`='"+telephone+"' AND `date_of_birth`='"+dob+"' AND `gender`='"+booltoGender(gender)+"' limit 1;"
);
return res.next();
}catch(SQLException ex){
ex.printStackTrace();
}
return false;
}
public int CountRows(String... condition){
try{
Statement stmt = conn.createStatement();
String str = "SELECT COUNT(*) FROM Users " ;
if (condition.length != 0){
str += "WHERE ";
for (int i=0;i<condition.length;i++){
str += condition[i];
}
}
str+=";";
ResultSet res = stmt.executeQuery(str);
res.next();
return res.getInt(1);
}catch(SQLException ex){
ex.printStackTrace();
return -1;
}
}
public String[][] GetUsers() throws Exception{
int nRows = CountRows();
if (nRows > 0) {
String[][] users = new String[nRows][4];
try{
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery("SELECT * FROM "+TABLE_NAME+" LIMIT "+Integer.toString(nRows)+";");
while(res.next()){
nRows -= 1;
String[] user = {
res.getString("firstname"),res.getString("lastname"),
res.getString("telephone"),res.getString("date_of_birth"),
res.getString("gender"),
};
users[nRows] = user;
}
return users;
}catch(Exception e){
e.printStackTrace();
throw e;
}
}else{
throw new Exception("No Users found");
}
}
}