MySQL / SQLite -- Database Storage

I've spent a good portion of my day searching for information on both MySQL and SQLite, reading about drivers, libraries, compatibility, etc. And I've come to terms that I am still confused somehow.

I run a minecraft server out of my basement (glorious, right?), so I threw a MySQL server on their too for kicks. I've been able to setup tables with columns for various simple data I would like to store so a plugin could access it.

To begin, I thought I'd try something simple:
Fill a column of the MySQL table with the Playernames of everybody that connects.

I've noticed many threads here that talk about MySQL, but they appear to be for people using other plugins that are already built, not ones that are in development. If one was writing a plugin to utilize MySQL, how should they take care of connecting to a database to store information gathered by that plugin.

These are the areas that have come up the most in my hunt, please help correct my misinterpretations if they exist:

• bukkit.yml :: Database section -> I don't think this is something that an individual plugin would reference this for its personal information deposit, I've begun ignoring this.
• SQLite -> Uses a local, single database file to store all the information. Seems to be the way to go, but I can't find a way for my server to use the correct driver, despite me getting the JDBC connector as well as a few other library jars.
• MySQL -> A separate server, but essentially functions like the above. JDBC is still required, and I have attempted this route, but still reach the java.sql.SQLException for no suitable driver. I used the Workbench to setup the tables, and have done this for other java projects in the past.
  
  
  Below I have my latest flop of a MySQL connection. This thread is essentially a puke of trial and errors to hopefully make this easier for some kind soul to point out where I'm failing to understand databases and their interaction with bukkit.

Code:

//Let's try some database stuff
        String dbUrl = "jdbc:mysql:11.111.111.11:3306/myplugindb";
        String dbClass = "com.mysql.jdbc.Driver";
        String query = "Select * FROM myplugin_table";
        String dbtime;
   
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(dbUrl);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
           
            while (rs.next())
            {
                dbtime = rs.getString(1);
                log.info(dbtime);
            } //end while
        }
       
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
       
        catch (SQLException e)
        {
            e.printStackTrace();
        }

I haven't debugged anything past getting the simple connection to work, so don't spend too much time on the result returns, I hope to be able to figure that much out.

 

JDBC is included in CB so there should not be a problem with the driver...
I use https://github.com/stelar7/MassiveAPI/tree/master/MassiveAPI/src/MAPI/Databases for mysql and it seems to work fine afaik

 

hmmm, I don't see anything wrong persay... BUT, I did manage to get mysql working without a lib from bukkit. What I do is... 1) make sure the driver is in my reference library, (same place I put my craftbukkit.jar). 2) i use this...

Code:

public static Connection connect() throws SQLException {
String db = "minecraft";
String url = "jdbc:mysql//localhost:3306/" + db;
String user = "user";
String password = "pass"
Connection con = DriverManager.getConnection(url, user, password);
return con;
}

that way, anytime I need to connect to my database in my code, I can just use this method rather than re-establishing a connection, or using a bunch of different variable names for statements and resultsets.
(hint: every query needs it's own result set. every result set needs it's own statement!)
so say I want to return something from a table, I would make a new method specifically for that... example:

Code:

public static void addPlayer(String player) throws SQLException {
Connection con = connect();
Statement st = con.createStatement();
//first I check if it already exists
ResultSet rs = st.executeQuery("SELECT * FROM PLAYERS WHERE NAME='" + player + "';");
if (!rs.next()) {
  //this means it doesn't exist, now I add the record
  st.executeUpdate("INSERT INTO PLAYERS(NAME) VALUES('" + player + "');");
}
}
// executeUpdate for whenever you make changes to the db, executeQuery when you're just pulling info 

Then you can execute this method whenever a player joins the server... or when ever you want (example purposes)

Code:

public void onPlayerJoin(PlayerJoinEvent event) {
String player = event.getPlayer().getName();
addPlayer(player);
}

 

Thanks for the responses! Glad to know that I wasn't entirely out of the ball park with this.

However, there are so many different ways to add libraries and .jar files to Eclipse I feel like. I've tried removing and re-adding the jdbc driver I have, but everything I do results in the same exception in my program at one time or another:

java.sql.SQLException: No suitable driver found for jdbc:mysql//11.111.111.11:3306/mytable

The steps I took was to take the jdbc 5.1 file that I have, and add it to my project under a folder that I labeled: "lib". I then added that to the java build path as a .jar, as well as going to the window preferences -> connectivity -> driver definitions, and adding it there as well.

I don't know where else I can add this file. Am I missing an import or some sort of way to tell the program where this driver is?

 

nah, just put it in the same place as your craftbukkit.jar
buildpath > add external jars
then add the .zip file of the driver.

it's misspelled.
jdbc:mysql://host:3306/database

 

And this would be for you kind sir.


Thanks for all the help Jeff, I'm up and communicating with the database now!

 

np man, took me about a week to learn all that... lol.

 

Could somebody post a link as to where I can download SQLite for Minecraft 1.4.2 ?

 

http://docs.oracle.com/javase/tutorial/jdbc/index.html

There is awesome tutorials and examples here.