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
|
package net.tylermurphy.ken.database;
import net.tylermurphy.ken.Ken;
import java.sql.*;
public class SelfRoleTable {
private final Database database;
public SelfRoleTable(Database database){
String sql = """
CREATE TABLE IF NOT EXISTS self_role_data (
guild_id BIGINT NOT NULL,
page INT NOT NULL,
data TEXT NOT NULL,
PRIMARY KEY (guild_id,page)
);""";
try(Connection connection = database.connect(); Statement statement = connection.createStatement()) {
statement.executeUpdate(sql);
} catch (SQLException e) {
Ken.getInstance().getLogger().error("SQL Error: " + e.getMessage());
}
this.database = database;
}
public String getData(long guildId, int page){
String sql = "SELECT * FROM self_role_data WHERE guild_id=? AND page=?";
try(Connection connection = database.connect(); PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, guildId);
statement.setInt(2, page);
ResultSet rs = statement.executeQuery();
if(rs.next()) return rs.getString("data");
else return null;
} catch (SQLException e) {
Ken.getInstance().getLogger().error("SQL Error: " + e.getMessage());
return null;
}
}
public boolean setData(long guildId, int page, String data){
String sql = "INSERT OR REPLACE INTO self_role_data (guild_id, page, data) VALUES(?,?,?)";
try(Connection connection = database.connect(); PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, guildId);
statement.setInt(2, page);
statement.setString(3, data);
return statement.executeUpdate() != 0;
} catch (SQLException e) {
Ken.getInstance().getLogger().error("SQL Error: " + e.getMessage());
return false;
}
}
public boolean deleteData(long guildId, int page){
String sql = "DELETE FROM self_role_data WHERE guild_id = ? AND page = ?; UPDATE self_role_data SET page = page - 1 WHERE guild_id = ? AND page > ?;";
try(Connection connection = database.connect(); PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, guildId);
statement.setInt(2, page);
statement.setLong(3, guildId);
statement.setInt(4, page);
return statement.executeUpdate() != 0;
} catch (SQLException e) {
Ken.getInstance().getLogger().error("SQL Error: " + e.getMessage());
return false;
}
}
public int getPages(long guildId) {
String sql = "SELECT MAX(page) AS pageCount FROM self_role_data WHERE guild_id = ?";
try(Connection connection = database.connect(); PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, guildId);
ResultSet rs = statement.executeQuery();
return rs.getInt("pageCount");
} catch (SQLException e) {
Ken.getInstance().getLogger().error("SQL Error: " + e.getMessage());
return -1;
}
}
}
|
