· 6 years ago · Jan 10, 2020, 03:30 PM
1package net.natroutter.netsb.utilities;
2
3import java.sql.Connection;
4import java.sql.DatabaseMetaData;
5import java.sql.DriverManager;
6import java.sql.PreparedStatement;
7import java.sql.ResultSet;
8import java.sql.SQLException;
9import java.sql.Statement;
10
11import com.sun.org.apache.bcel.internal.generic.IFGE;
12
13import net.natroutter.netsb.utilities.Logger.logType;
14
15public class Database {
16
17 private static Connection con = connection();
18
19 private static Connection connection() {
20 // SQLite connection string
21 String url = "jdbc:sqlite:Database.db";
22 Connection con = null;
23 try {
24 con = DriverManager.getConnection(url);
25 } catch (SQLException e) {
26 Logger.Add("Database connection failed!\n" + e.getMessage(), logType.Error);
27 }
28 return con;
29 }
30
31 public static void AddBalance(String UserID, Long value) {
32 if (UserExits(UserID)) {
33 Long OldBalance = GetBalance(UserID);
34 Long NewBalance = OldBalance + value;
35 if (NewBalance > Long.MAX_VALUE) {
36 SetBalance(UserID, Long.MAX_VALUE);
37 } else {
38 SetBalance(UserID, NewBalance);
39 }
40 } else {
41 SetBalance(UserID, value);
42 }
43 }
44
45 public static Long GetBalance(String UserID) {
46 if (UserExits(UserID)) {
47 String sql = "SELECT Balance FROM UserData where UserID=" + UserID + ";";
48
49 try {
50 Statement stmt = con.createStatement();
51 ResultSet rs = stmt.executeQuery(sql);
52 return rs.getLong("Balance");
53 } catch (SQLException e) {
54 Logger.Add("Database selection failed!\n" + e.getMessage(), logType.Error);
55 }
56 return null;
57 } else {
58 return (long)0;
59 }
60 }
61
62 public static boolean SetBalance(String UserID, Long value) {
63
64 if (value > Long.MAX_VALUE) {
65 value = Long.MAX_VALUE;
66 }
67
68 if (UserExits(UserID)) {
69
70 String sql = "UPDATE UserData set Balance = ? where UserID=" + UserID + ";";
71
72 try{
73 PreparedStatement pstmt = con.prepareStatement(sql);
74 pstmt.setLong(1, value);
75 pstmt.executeUpdate();
76 return true;
77 } catch (SQLException e) {
78 Logger.Add("Database update failed!\n" + e.getMessage(), logType.Error);
79 }
80
81 } else {
82
83 String sql = "INSERT INTO UserData(UserID, Balance) VALUES(?,?)";
84
85 try{
86 PreparedStatement pstmt = con.prepareStatement(sql);
87 pstmt.setString(1, UserID);
88 pstmt.setLong(2, value);
89 pstmt.executeUpdate();
90 return true;
91 } catch (SQLException e) {
92 Logger.Add("Database insert failed!\n" + e.getMessage(), logType.Error);
93 }
94
95 }
96 return false;
97
98 }
99
100 private static void CreateUserDataTable() {
101 // SQL statement for creating a new table
102 String sql = "CREATE TABLE IF NOT EXISTS UserData (UserID text NOT NULL, Balance integer, Warns integer)";
103
104 try {
105 Statement stmt = con.createStatement();
106 stmt.execute(sql);
107 } catch (SQLException e) {
108 Logger.Add("Database table creation failed!\n" + e.getMessage(), logType.Error);
109 System.out.println(e.getMessage());
110 }
111 }
112
113
114 public static boolean UserExits(String UserID) {
115 CreateUserDataTable();
116
117 try {
118 String query = "SELECT (count(*) > 0) as found FROM UserData WHERE UserID LIKE ?";
119 PreparedStatement pst = con.prepareStatement(query);
120 pst.setString(1, UserID);
121
122 try (ResultSet rs = pst.executeQuery()) {
123 // Only expecting a single result
124 if (rs.next()) {
125 return rs.getBoolean(1);
126 }
127 }
128 } catch (SQLException e) {
129 Logger.Add("Database userexits check failed!\n" + e.getMessage(), logType.Error);
130 }
131 return false;
132 }
133
134 private static Integer CountRows(String Table) {
135 Integer count = 0;
136
137 try{
138 Statement pstmt = con.createStatement();
139 ResultSet res = pstmt.executeQuery("SELECT COUNT(*) FROM "+Table);
140 while (res.next()){
141 count = res.getInt(1);
142 }
143 return count;
144 } catch (SQLException e) {
145 Logger.Add("Database row count failed!\n" + e.getMessage(), logType.Error);
146 }
147 return null;
148 }
149}