From 16d0b5c228dd8d981a0d79944c70248ea813d63b Mon Sep 17 00:00:00 2001 From: ementalo Date: Wed, 27 Jun 2012 13:35:39 +0100 Subject: package name change to net.ess3 --- .../net/ess3/protect/data/ProtectedBlockMySQL.java | 160 +++++++++++++++++++++ 1 file changed, 160 insertions(+) create mode 100644 EssentialsProtect/src/net/ess3/protect/data/ProtectedBlockMySQL.java (limited to 'EssentialsProtect/src/net/ess3/protect/data/ProtectedBlockMySQL.java') diff --git a/EssentialsProtect/src/net/ess3/protect/data/ProtectedBlockMySQL.java b/EssentialsProtect/src/net/ess3/protect/data/ProtectedBlockMySQL.java new file mode 100644 index 000000000..9fd17d0a7 --- /dev/null +++ b/EssentialsProtect/src/net/ess3/protect/data/ProtectedBlockMySQL.java @@ -0,0 +1,160 @@ +package net.ess3.protect.data; + +import java.beans.PropertyVetoException; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; + + +public class ProtectedBlockMySQL extends ProtectedBlockJDBC +{ + public ProtectedBlockMySQL(final String url, final String username, final 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(final 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(final 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) + { + try + { + testRS.close(); + } + catch (SQLException ex) + { + Logger.getLogger(ProtectedBlockMySQL.class.getName()).log(Level.SEVERE, null, ex); + } + } + if (testPS != null) + { + try + { + testPS.close(); + } + catch (SQLException ex) + { + Logger.getLogger(ProtectedBlockMySQL.class.getName()).log(Level.SEVERE, null, ex); + } + } + } + } + private static final String QueryDeleteAll = "DELETE FROM EssentialsProtect;"; + + @Override + protected PreparedStatement getStatementDeleteAll(final 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(final Connection conn, final String world, final int x, final int y, final int z, + final String playerName) throws SQLException + { + final 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(final Connection conn, final String world, final int x, final int y, final int z, + final String playerName) throws SQLException + { + final 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(final Connection conn, final String world, + final int x, final int y, final int z) throws SQLException + { + final 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(final Connection conn, final String world, + final int x, final int y, final int z) throws SQLException + { + final 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(final Connection conn) throws SQLException + { + return conn.prepareStatement(QueryAllBlocks); + } +} -- cgit v1.2.3