Beiträge von TomS

package net.toms.whitelist.sql;

import java.sql.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
* Created by Tom S. for SmyleGames.DE
* Copyright 2012-2016
* Code is safed for SmyleGames.DE by Creative-Commons License 4.0
*/
public class MySQL {

public static final String TABLE_CODES = "whitelist_codes";
public static final String TABLE_PLAYERS = "whitelist_players";

private static String host, user, db, pass;
private static int port;
private static Connection con;
private static ExecutorService executor;

static {
executor = Executors.newCachedThreadPool();
}

public static void setConnectionData(String host, String user, String db, String pass){
setConnectionData(host, user, db, pass, 3306);
}

public static void setConnectionData(String host, String user, String db, String pass,int port){
MySQL.host = host;
MySQL.user = user;
MySQL.db = db;
MySQL.pass = pass;
MySQL.port = port;
}

public static void connect(){
try {
con = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + db ,user,pass);
setup();
} catch (SQLException e) {
e.printStackTrace();
}
}

public static boolean isConnected(){
try {
return con != null && con.isValid(1);
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}

public static void disconnect(){
try {
if(isConnected()){
con.close();
con = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

private static void setup(){
executor.execute(new Runnable() {

@Override
public void run() {
try {
{
String qry = "CREATE TABLE IF NOT EXISTS "+TABLE_CODES+ "(code TEXT)";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.executeUpdate();
stmt.close();
}
{
String qry = "CREATE TABLE IF NOT EXISTS " + TABLE_PLAYERS + "(id INT auto_increment, username TEXT, PRIMARY KEY(id))";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.executeUpdate();
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
});
}

public static void addCode(final String code){
executor.execute(new Runnable() {

@Override
public void run() {
try {
String qry = "INSERT INTO " + TABLE_CODES + " (code) VALUES (?)";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.setString(1, code);
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
});
}

public static void removeCode(final String code){
executor.execute(new Runnable() {

@Override
public void run() {
try {
String qry = "DELETE FROM " + TABLE_CODES + " WHERE code = ?";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.setString(1, code);
stmt.executeUpdate();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
});
}

public static boolean isWhitelisted(String username){
boolean returnStatement = false;
try {
String qry = "SELECT * FROM " + TABLE_PLAYERS + " WHERE username = ?";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.setString(1, username);
ResultSet rs = stmt.executeQuery();

if(rs.first()) returnStatement= true;
stmt.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return returnStatement;
}

public static void setWhitelisted(final String username, final boolean status){
executor.execute(new Runnable() {

public void run() {
try {
boolean whitlisted = isWhitelisted(username);
if(status){
if(!whitlisted){
String qry = "INSERT INTO " + TABLE_PLAYERS + " (username) VALUES (?)";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.setString(1, username);
stmt.executeUpdate();
stmt.close();
}
}else{
if(whitlisted){
String qry = "DELETE FROM " + TABLE_PLAYERS + " WHERE username = ?";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.setString(1, username);
stmt.executeUpdate();
stmt.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
});
}
}