Hows my MySQL?

I started hooking MySQL into one of my plugins (a custom / private one for my server), and I was wondering if I can get some feedback on it. I would really like to know if I can do better in some areas, and if some areas in my code were pretty good. Keep in mind, this is the first time I ever implemented MySQL into java.

MySQL.java (open) MySQL.java (close)

Code:java

package me.number1_Master.DGChatManager.Utils;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
publicclass MySQL
{
/*
*Variables
*/
static String url = "jdbc:mysql://localhost:3306/DGChatManager";
static String usrName = "root";
static String pass = "203215";
 
publicstaticbooleanconnected = false;
 
static Connection conn = null;
 
/*
*MySQL Utilities
*/
publicstaticvoid connect()
{
try
{
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception err)
{ Log.e("MySQL: " + err.getMessage()); }
conn = DriverManager.getConnection(url, usrName, pass);
connected = true;
}
catch(SQLException err)
{ Log.e("MySQL: connect()"); }
}
publicstaticvoid close()
{
try
{
conn.close();
connected = false;
Log.i("MySQL: Closing ...");
}
catch(SQLException err) 
{ Log.e("MySQL: close()"); }
}
static ResultSet execute(String query)
{
try
{
PreparedStatement stmt = conn.prepareStatement(query);
 
if(query.contains("SELECT")) 
{
stmt.executeQuery();
return stmt.getResultSet();
}
else if(query.contains("INSERT") || query.contains("UPDATE") || query.contains("DELETE")) stmt.executeUpdate();
else stmt.execute();
}
catch(SQLException err)
{ Log.e("MySQL: execute()"); }
returnnull;
}
static Object convertResult(ResultSet rs, boolean type)
{
try
{
if(rs.next() && rs.getObject(1) != null)
{
Object o;
if(type) o = rs.getString(1);
else o = rs.getInt(1);
rs.close();
return o;
}
else
{
if(type) return null;
elsereturn 0;
}
}
catch(SQLException err) 
{ Log.e("MySQL: convertResult(): " + err.getMessage()); }
if(type) returnnull;
elsereturn 0;
}
publicstaticvoid purge()
{
int amount = (Integer) convertResult(execute("SELECT COUNT(*) FROM DGChatManager"), false);
if(amount == 0) return;
 
int purged = 0;
 
String curDate = (String) convertResult(execute("SELECT CURDATE() FROM DGChatManager LIMIT 1"), true);
 
for(int i = 1; i<=amount; i++)
{
String date = (String) convertResult(execute("SELECT date FROM DGChatManager WHERE id=" + i), true);
if(date == null)
{
amount++;
continue;
}
 
String playerName = (String) convertResult(execute("SELECT playername FROM DGChatManager WHERE id=" + i), true);
if(playerName.equals("number1_Master") || playerName.equals("---") || playerName.equals("---") || playerName.equals("---") || playerName.equals("---"))
{
continue;
}
 
String[] dSplit = date.split("-");
String[] cdSplit = curDate.split("-");
int year = 0; int curYear = 0; int month = 0; int curMonth = 0;
try
{
year = Integer.parseInt(dSplit[0]);
curYear = Integer.parseInt(cdSplit[0]);
month = Integer.parseInt(dSplit[1]);
curMonth = Integer.parseInt(cdSplit[1]);
}
catch(NumberFormatException err)
{ Log.e(err.getMessage()); }
 
if(year < curYear || month < curMonth)
{
execute("DELETE FROM DGChatManager WHERE id=" + i);
purged++;
continue;
}
}
Log.i("MySQL: " + purged + " deleted from database!");
}
/*
*Add to MySQL!
*/
publicstaticvoid addTable()
{
execute("CREATE TABLE IF NOT EXISTS DGChatManager (id INT NOT NULL AUTO_INCREMENT, playername VARCHAR(20) NOT NULL, medals INT NOT NULL, date VARCHAR(10) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB");
 
if(getPlayer("number1_Master") == null) 
{
addPlayer("number1_Master");
addMedal("number1_Master");
}
if(getPlayer("---") == null)
{
addPlayer("---");
addMedal("---");
}
if(getPlayer("---") == null)
{
addPlayer("---");
addMedal("---");
}
if(getPlayer("---") == null) 
{
addPlayer("---");
addMedal("---");
}
}
public static void addPlayer(String playerName)
{
execute("INSERT INTO DGChatManager ( playername, medals, date ) VALUES ( '" + playerName + "', 0, CURDATE())");
}
public static void addMedal(String playerName)
{
execute("UPDATE DGChatManager SET medals=medals+1 WHERE playername='" + playerName + "'");
}
/*
*Get from MySQL!
*/
public static String getPlayer(String playerName)
{
return (String) convertResult(execute("SELECT playername FROM DGChatManager WHERE playername='" + playerName + "'"), true);
}
public static int getMedals(String playerName)
{
return (Integer) convertResult(execute("SELECT medals FROM DGChatManager WHERE playername='" + playerName + "'"), false);
}
}

 

I wouldn't use a boolean to see whether you have a connection or not, since the connection might close, even if you don't explicitly ask it to close. However, there's a method in the connection class that gives you the connection status:

Code:

if(conn.isValid(3))
    //is connected
else
    //not connected

 

Ahhhh! Thank you. So you are saying to create a method getConnection which return a boolean. The boolean would return true or false if conn.isValid();

I gotcha!