[Tutorial] How to connect to your MySQL server through your server

[GhostSnyper]

Seeing as I forgot about this, I'll go ahead and make the Tutorial. For now, I'm only going to show you how to connect, and the basic Syntax on how to send and receive data from the Database. I have not properly set up loading and saving, so that is for another day.

Step 1: Getting the Database driver and setting it up
No I'm not pulling your leg. The JDBC driver, if you haven't guessed, stands for Java Database Connect driver. It has all of the necessary classes nested in it for immediate SQL-java interaction. The driver can be downloaded Here

Now I'd recommend renaming this to something simple, like JDBC Driver. Next, you want to store it somewhere safe, where you won't accidentally delete it. In this tutorial, I stored it in

C:\Program Files\Java

We're going to set up this path in our system variables, just like what we did with our JDK. Navigate to your control panel ( Start>Control Panel). Now double click System. Then click the advanced tab, and click Environment Variables.
Find the PATH variable, on the bottom panel, labeled System Variables
My Path variable looks like so; yours should have the beginning look similar

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Intel\DMIX;C:\Program Files\QuickTime\QTSystem\;c:\program files\java\jdk1.6.0_06\bin;C:\Program Files\Autodesk\Backburner\;C:\Program Files\Common Files\Autodesk Shared\;C:\Program Files\MySQL\MySQL Server 5.0\bin;C:\Program Files\Java\JDBC.Jar.;

The bold is the path I added for the JDBC driver. the . after the Jar is important, as it allows access inside of the Jar as well; also, make sure to add a ; at the end to separate for the next comment.

Step 2: Connecting to the database from your Run Escape server.
Firstly, make a new class called Database.java. In it, include the following code. Look through it, and try and understand. I've left comments inside to help out

import java.sql.*;

public class Database {

	private static Connection conn = null;
	private static Statement state;

	public static void createConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance(); //Initialized the JDBC Driver
			//sets the connection and opens the 
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/RuneScape"
					, "root", ""); // ("jdbc:mysql://SQL IP:SQL port /database" "SQLUser" "SQLPass")
			state = conn.createStatement(); // sets state to use connection to send SQL statements
		} catch (Exception e) { // Must catch the errors
			e.printStackTrace(); // Prints the error, and where the error occurs in the source file
		}
	}
	
	public static ResultSet sqlQuery(String s){
		try{ // In SQL, try's are needed, because there is always the chance of error
			if((s.toLowerCase()).startsWith("select")){
				ResultSet result = state.executeQuery(s);
				return result;
			}
			else{
				state.executeUpdate(s);
			}
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		return null; //must have the return statement. This usually doesn't occur
	}
	
	public static void closeConnection(){
	 //try's to close the connections to avoid memory leaks. used try in case it's closed, so no fatals are thrown
		try {
			state.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

variables are static and private because they, in this instance, are called directly from the server class. Next we need to have our connection initialized in the server class.

Open your server.java. Now if you are more advanced, and neat, put this where you see fit. otherwise add this below your main, like so

 public static void main(java.lang.String args[])
{
    Database.createConnection();

The above code basically initializes the void createConnection in the Database class(DUH). the other voids I've introduced so you can learn to use them.


Tomorrow, I'll explain the ResultSet method :)

[MentaL]

[-fedexer-]

Very nice mate :winky: , god i have an itch to get moving with my server, but it would be relatively pointless before my holidays.

I hope people can learn how to use and implement SQL using your tutorials Ghost.

[GhostSnyper]

Thanks there, Fedexter. I pretty much hand coded the Database class for my Rs2d source, and I'll be trying to integrate that soon

[-fedexer-]

Nice, mwuahaha you must work on spelling the username right :P fedexer, not fedexter xD

You could have gone with some more originality rather than database.java ^^, anyway i look forward to seeing what you create soon enough =D

[I Rule MU]

Amazing....yet again!

[GhostSnyper]

Thanks Mu. And Sorry, Fed, i know there isn't a t, but my hand chokes me every time I hit that backspace :(

I'm not going for originality lol, I'm going for functionality. it's easy to judge what it does, and it gets the job done

[-fedexer-]

True true, ha it's cool, if you type the t go for it :P or just call em by Fed , i accept that xD

Aye functionality is the way ahead, i just hope people use this , and give up on flat files >.>

[GhostSnyper]

Flat file for the effing lose. Tomorrow, I'll be explaining the basics of loading and saving characters... as for now, it's sleep sleep time. Gots a job interview tomorrows >:)

[harveylowndes]

To everyone,

If you are interested in using SQL and like the learning side of things, i suggest you watch this.

http://sean.cruels.net/java/31/31.htm

Thanks,
Harvey Lowndes

[GhostSnyper]

This explains the use of odbc, using oracle, which none of us can afford. also, this is a standalone program, so it's uses of System.exit(1); are not recommended in server usage. also, I've done everything he's done, only specifically for a free database.

[AndyJay1]

                           ~ AndysScape Compiler ~
Compiling Server... Please Wait...
database.java:3: class Database is public, should be declared in a file named Da
tabase.java
public class Database {
       ^
Note: Some input files use or override a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
1 error
Press any key to continue . . .

[-fedexer-]

                           ~ AndysScape Compiler ~
Compiling Server... Please Wait...
database.java:3: class Database is public, should be declared in a file named Da
tabase.java
public class Database {
       ^
Note: Some input files use or override a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
1 error
Press any key to continue . . .

Well done... you failed to copy and paste the code into a file called Database.java , have a cookie.

How you managed that... i don't know, and don't want to know

[AndyJay1]

Nvm, fixed the problem :)

You need to save the file as Database.java NOT database.java

The 'D' needs upper-casing :)

[AndyJay1]

Well done... you failed to copy and paste the code into a file called Database.java , have a cookie.

How you managed that... i don't know, and don't want to know

Nah, if people actually gave us the correct file name with a CAPITAL D, it might work. So think before jumping to conclusions.

[-fedexer-]

Yes.. and you would know this if you knew enough to know that the class name, also has to be the file name, exactly as it is.