· 10 years ago · Aug 13, 2016, 02:34 AM
1package gilidon.host.machinecrafting;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.util.ArrayList;
8
9public class SQLManager {
10 public Connection connection;
11 private MachineCrafting plugin;
12 public SQLManager(final MachineCrafting plugin) {
13 this.plugin = plugin;
14 }
15
16 void openConnection(){
17 try {
18 this.connection = DriverManager.getConnection
19 ("jdbc:mysql://" + this.plugin.getConfig().getString("MySQL.host")
20 + ":"
21 + this.plugin.getConfig().getString("MySQL.port")
22 + "/"
23 + this.plugin.getConfig().getString("MySQL.database"),
24 new StringBuilder().append(this.plugin.getConfig().getString("MySQL.user")).toString(),
25 new StringBuilder().append(this.plugin.getConfig().getString("MySQL.password")).toString());
26 }
27 catch (Exception e) {
28 e.printStackTrace();
29 }
30 }
31
32 public void closeConnection(){
33 try {
34 this.connection.close();
35 } catch (Exception e) {
36 e.printStackTrace();
37 }
38 }
39
40 public void initDatabase(){
41 this.openConnection();
42 try {
43 final PreparedStatement sql = connection.prepareStatement
44 ("CREATE TABLE IF NOT EXISTS `MachineCrafting`"
45 + " (`ID` INT NOT NULL UNIQUE,"
46 + " `uuid` varchar(100),"
47 + " `town` varchar(100),"
48 + " `machine` varchar(100),"
49 + " `isname` varchar(100),"
50 + " `slot` INT,"
51 + " `remainingtime` INT,"
52 + " PRIMARY KEY (`ID`)) ;");
53 sql.execute();
54 sql.close();
55 } catch (Exception e) {
56 e.printStackTrace();
57 return;
58 }
59 }
60
61 public Integer nextID(){
62 Integer data = 0;
63 try{
64 PreparedStatement sql = connection.prepareStatement
65 ("SELECT `ID` FROM `MachineCrafting` ORDER BY `ID` DESC LIMIT 1;");
66 ResultSet rs = sql.executeQuery();
67 if(rs.next()){
68 data = rs.getInt("id") + 1;
69 }
70 rs.close();
71 sql.close();
72 }catch(Exception e){
73 e.printStackTrace();
74 }
75 return data;
76 }
77
78 public void countdown(){
79 try{
80 PreparedStatement sql = connection.prepareStatement
81 ("SELECT * FROM `MachineCrafting`;");
82 ResultSet rs = sql.executeQuery();
83 while(rs.next()){
84 if(rs.getInt("remainingtime") > 0){
85 PreparedStatement sql3 = connection.prepareStatement
86 ("UPDATE `MachineCrafting` SET `remainingtime` =? WHERE `id`=?;");
87 sql3.setInt(1, rs.getInt("remainingtime") - 1);
88 sql3.setInt(2, rs.getInt("id"));
89 sql3.executeUpdate();
90 sql3.close();
91 }
92 if(rs.getInt("remainingtime") < 0){
93 PreparedStatement sql3 = connection.prepareStatement
94 ("UPDATE `MachineCrafting` SET `remainingtime` =? WHERE `id`=?;");
95 sql3.setInt(1, 0);
96 sql3.setInt(2, rs.getInt("id"));
97 sql3.executeUpdate();
98 sql3.close();
99 }
100 }
101 rs.close();
102 sql.close();
103 }catch(Exception e){
104 e.printStackTrace();
105 }
106 }
107
108 public String nameToUUID(String name){
109 String uuid = "";
110 try{
111 PreparedStatement sql = connection.prepareStatement
112 ("SELECT * FROM `always_online` WHERE `name`=?;");
113 sql.setString(1, name);
114 ResultSet rs = sql.executeQuery();
115 while (rs.next()){
116 uuid = rs.getString("uuid");
117 }
118 rs.close();
119 sql.close();
120 }catch(Exception e){
121 e.printStackTrace();
122 }
123 return uuid;
124 }
125
126 //Gets player's active crafts for this machine from the SQL database
127 public ArrayList<String> getCraft(String name, String town, String machine, Integer slot){
128 String uuid = nameToUUID(name);
129 ArrayList<String> data = new ArrayList<String>();
130 data.add(0, "0");
131 data.add(1, "blank");
132 String timestring = "timestring";
133 try{
134 PreparedStatement sql = connection.prepareStatement
135 ("SELECT * FROM `MachineCrafting` WHERE `uuid`=?;");
136 sql.setString(1, uuid);
137 ResultSet rs=sql.executeQuery();
138 while (rs.next()){
139 if(rs.getInt("slot")==slot && town.equals(rs.getString("town")) && machine.equals(rs.getString("machine"))){
140 Integer remainingtime = rs.getInt("remainingtime");
141 timestring = remainingtime.toString();
142 String isname = rs.getString("isname");
143 data.set(0, timestring);
144 data.set(1, isname);
145 }
146 }
147 rs.close();
148 sql.close();
149 }catch(Exception e){
150 e.printStackTrace();
151 }
152 return data;
153 }
154
155 //Removes specified craft from database
156 public void removeItem(String name, String town, String machine, int slot){
157 String uuid = nameToUUID(name);
158 try{
159 PreparedStatement sql = connection.prepareStatement
160 ("SELECT * FROM `MachineCrafting` WHERE `uuid`=?;");
161 sql.setString(1, uuid);
162 ResultSet rs=sql.executeQuery();
163 while (rs.next()){
164 if(rs.getInt("slot")==slot && town.equals(rs.getString("town")) && machine.equals(rs.getString("machine"))){
165 Integer id = rs.getInt("ID");
166 PreparedStatement sql1 = connection.prepareStatement
167 ("DELETE FROM `MachineCrafting` WHERE `ID`=?;");
168 sql1.setInt(1, id);
169 sql1.execute();
170 sql1.close();
171 }
172 }
173 }catch(Exception e){
174 e.printStackTrace();
175 }
176 }
177
178 //Inserts craft into MySQL database
179 public void newCraft(String name, String town, String machine, Integer slot, String isname, Integer remainingtime){
180 String uuid = nameToUUID(name);
181 try{
182 PreparedStatement sql = connection.prepareStatement
183 ("INSERT INTO `MachineCrafting`"
184 + " (`ID`, `uuid`, `town`, `machine`, `isname`, `slot`, `remainingtime`)"
185 + " VALUES (?,?,?,?,?,?,?);");
186 sql.setInt(1, nextID());
187 sql.setString(2, uuid);
188 sql.setString(3, town);
189 sql.setString(4, machine);
190 sql.setString(5, isname);
191 sql.setInt(6, slot);
192 sql.setInt(7, remainingtime);
193 sql.execute();
194 sql.close();
195 }catch(Exception e){
196 e.printStackTrace();
197 }
198 }
199
200//Final closing bracket
201}