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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
package com.earth2me.essentials.protect.data;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ProtectedBlockMySQL extends ProtectedBlockJDBC {
public ProtectedBlockMySQL(String url, String username, String password) throws PropertyVetoException {
super("com.mysql.jdbc.Driver", url, username, password);
}
private static final String QueryCreateTable =
"CREATE TABLE IF NOT EXISTS `EssentialsProtect` ("
+ "`worldName` varchar(60) NOT NULL,"
+ "`x` int(11) NOT NULL, `y` int(11) NOT NULL, `z` int(11) NOT NULL,"
+ "`playerName` varchar(150) DEFAULT NULL,"
+ "KEY `pos` (`worldName`,`x`,`z`,`y`)"
+ ") ENGINE=MyISAM DEFAULT CHARSET=utf8";
@Override
protected PreparedStatement getStatementCreateTable(Connection conn) throws SQLException {
return conn.prepareStatement(QueryCreateTable);
}
private static final String QueryUpdateFrom2_0TableCheck =
"SHOW COLUMNS FROM `EssentialsProtect` LIKE 'id';";
private static final String QueryUpdateFrom2_0Table =
"ALTER TABLE `EssentialsProtect` "
+ "CHARACTER SET = utf8, ENGINE = MyISAM,"
+ "DROP COLUMN `id`,"
+ "CHANGE COLUMN `playerName` `playerName` VARCHAR(150) NULL AFTER `z`,"
+ "CHANGE COLUMN `worldName` `worldName` VARCHAR(60) NOT NULL,"
+ "ADD INDEX `position` (`worldName` ASC, `x` ASC, `z` ASC, `y` ASC),"
+ "DROP PRIMARY KEY ;";
@Override
protected PreparedStatement getStatementUpdateFrom2_0Table(Connection conn) throws SQLException {
PreparedStatement testPS = null;
ResultSet testRS = null;
try {
testPS = conn.prepareStatement(QueryUpdateFrom2_0TableCheck);
testRS = testPS.executeQuery();
if (testRS.first()) {
return conn.prepareStatement(QueryUpdateFrom2_0Table);
} else {
return conn.prepareStatement("SELECT 1;");
}
} finally {
if (testRS != null) {
testRS.close();
}
if (testPS != null) {
testPS.close();
}
}
}
private static final String QueryDeleteAll = "DELETE FROM EssentialsProtect;";
@Override
protected PreparedStatement getStatementDeleteAll(Connection conn) throws SQLException {
return conn.prepareStatement(QueryDeleteAll);
}
private static final String QueryInsert =
"INSERT INTO EssentialsProtect (worldName, x, y, z, playerName) VALUES (?, ?, ?, ?, ?);";
@Override
protected PreparedStatement getStatementInsert(Connection conn, String world, int x, int y, int z, String playerName) throws SQLException {
PreparedStatement ps = conn.prepareStatement(QueryInsert);
ps.setString(1, world);
ps.setInt(2, x);
ps.setInt(3, y);
ps.setInt(4, z);
ps.setString(5, playerName);
return ps;
}
private static final String QueryCountByPlayer =
"SELECT COUNT(playerName), SUM(playerName = ?) FROM EssentialsProtect "
+ "WHERE worldName = ? AND x = ? AND y = ? AND z = ? GROUP BY x;";
@Override
protected PreparedStatement getStatementPlayerCountByLocation(Connection conn, String world, int x, int y, int z, String playerName) throws SQLException {
PreparedStatement ps = conn.prepareStatement(QueryCountByPlayer);
ps.setString(1, playerName);
ps.setString(2, world);
ps.setInt(3, x);
ps.setInt(4, y);
ps.setInt(5, z);
return ps;
}
private static final String QueryPlayersByLocation =
"SELECT playerName FROM EssentialsProtect WHERE worldname = ? AND x = ? AND y = ? AND z = ?;";
@Override
protected PreparedStatement getStatementPlayersByLocation(Connection conn, String world, int x, int y, int z) throws SQLException {
PreparedStatement ps = conn.prepareStatement(QueryPlayersByLocation);
ps.setString(1, world);
ps.setInt(2, x);
ps.setInt(3, y);
ps.setInt(4, z);
return ps;
}
private static final String QueryDeleteByLocation =
"DELETE FROM EssentialsProtect WHERE worldName = ? AND x = ? AND y = ? AND z = ?;";
@Override
protected PreparedStatement getStatementDeleteByLocation(Connection conn, String world, int x, int y, int z) throws SQLException {
PreparedStatement ps = conn.prepareStatement(QueryDeleteByLocation);
ps.setString(1, world);
ps.setInt(2, x);
ps.setInt(3, y);
ps.setInt(4, z);
return ps;
}
private static final String QueryAllBlocks =
"SELECT worldName, x, y, z, playerName FROM EssentialsProtect;";
@Override
protected PreparedStatement getStatementAllBlocks(Connection conn) throws SQLException {
return conn.prepareStatement(QueryAllBlocks);
}
}
|
