[TUT] SQL Clan System!

[harveylowndes]

Current Version - 1.1

Perpose: To make a working clan system
Base: Winterlove
Difficulty: Medium

Version 1 features

• Make Clan
• Join Clan
• Leave Clan
• Delete Clan (Not working)
• My Clan (Clan Owner not working)

Version 1.1 features

• Fixed Delete Clan
• Fixed Clan Owner

Step 1 - Setting up sql (Skip if you have sql)

server.java
First import this as we will be using this library.

import java.sql.*;

At the top of the main method add this. This is used to create the connection.

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(MySQLURL, MySQLUser, MySQLPassword);
		} catch(Exception e) {	
			e.printStackTrace();
		}

Where it says while(!shutdownServer) add this.

			try {
				if (conn.isClosed()) {
					Class.forName("com.mysql.jdbc.Driver").newInstance();
					conn = DriverManager.getConnection(MySQLURL, MySQLUser, MySQLPassword);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

Now we need to close the connection so above playerHandler.destruct() add.

		try {
			conn.close();
		} catch (Exception e) {
			misc.println("-!- MySQL Error -!-");
			e.printStackTrace();
		}

Now there are some variables that we have used that now need to declare.

	public static String MySQLURL = "jdbc:mysql://localhost:3306/DATABASE"; //The MySQL URL. Change the DATABASE to your database
	public static String MySQLUser = "root"; //The MySQL login name
	public static String MySQLPassword = "password"; //The MySQL login pass

	public static Connection conn; //Class Connection (in java libary) is now declared
	public static boolean MySQLLogginIn = false; //Starts/Stops the connections

Now make a new database and name it what you called it in MySQLURL and import these.

CREATE TABLE `clans` (
  `clanName` varchar(20) NOT NULL default '',
  `clanPass` varchar(20) NOT NULL default '',
  `clanOwner` varchar(20) NOT NULL default '',
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=1 ;

Mirror 1: http://www.megaupload.com/?d=KE47I3CV
Mirror 2: http://rapidshare.com/files/126872804/Files.zip.html
Download and extract these files in your server files.

Make your run.bat look something like.

java -Xmx1024m -cp .;./jython.jar;./MySql/mysql-connector-java-3.0.17-ga-bin.jar server

Step 2 - Saving and Loading

player.java

Declare these as they will be widly used.

	public String pclanName;
	public String pclanPass;
	public int pclanRights;
	public boolean inClan = false;

client.java

Finding playerXP = loadgame.playerXP; and under add this. (So when we use these variables, they will load them).

				pclanName = loadgame.pclanName;
				pclanRights = loadgame.pclanRights;
				pclanPass = loadgame.pclanPass;
				inClan = loadgame.inClan;

PlayerSave.java

Declare these.

	public String pclanName = "";
	public String pclanPass = "";
	public int pclanRights;
	public boolean inClan;

In public PlayerSave add this. (Saves all data).

		pclanName = plr.pclanName;
		pclanPass = plr.pclanPass;
		pclanRights = plr.pclanRights;
		inClan = plr.inClan;

Step 3 - Creating commands and methods

client.java

We need to import the java sql library.

import java.sql.*;

Now add these methods.

Method to add clan to database.

/* Clan Handling */

	public void appendClan(String clanName1, String clanPass1, String clanOwner1) 
	{
		
		try {
				PreparedStatement stmt = null;
				Statement statement = server.conn.createStatement();
				String query = "SELECT * FROM clans WHERE clanName = '" + clanName1 + "'";
				ResultSet results = statement.executeQuery(query);
				stmt = null;
				query = "INSERT INTO clans VALUES ('"+clanName1+"', '"+clanPass1+"', '"+clanOwner1+"')";
				stmt = server.conn.prepareStatement(query);
				stmt.executeUpdate();
				pclanName = clanName1;
				pclanPass = clanPass1;
				inClan = true;
				pclanRights = 4;
		} catch(SQLException e) {
			e.printStackTrace();
		}
  	}

Method for joining a clan to make sure details are correct; Authentication method.

	public boolean loadClan(String clanName1, String clanPass1)
	{
		server.MySQLLogginIn = true;
		try {
			Statement statement = server.conn.createStatement();
			String query = "SELECT * FROM clans WHERE clanName = '" + clanName1 + "' AND clanPass = '"+ clanPass1 + "'";
			ResultSet results = statement.executeQuery(query);
			while(results.next()) {
				return true;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

Method for checking if the clan name is in use or checking if it exists (because owner might have deleted it).

	public boolean clanExists(String clanName1)
	{
		server.MySQLLogginIn = true;
		try {
			Statement statement = server.conn.createStatement();
			String query = "SELECT * FROM clans WHERE clanName = '" + clanName1 + "'";
			ResultSet results = statement.executeQuery(query);
			while(results.next()) {
				return true;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

Method for checking who the clan owner is

	public String getClanOwner()
	{
		server.MySQLLogginIn = true;
		String clanOwner = "";
		try {
			Statement statement = server.conn.createStatement();
			String query = "SELECT * FROM clans WHERE clanName = '" + pclanName + "'";
			ResultSet results = statement.executeQuery(query);
			while(results.next()) {
				clanOwner = results.getString("clanOwner");
				return clanOwner;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return "";
	}

Method for deleting a clan (if owner).

	public void deleteClan()
	{
		server.MySQLLogginIn = true;
		String update;
		int recordsUpdated;
		ResultSet result = null;
		try {
			Statement statement = server.conn.createStatement();
			recordsUpdated = statement.executeUpdate("DELETE FROM clans WHERE clanName = '" + pclanName + "'");
			inClan = false;
			pclanRights = 0;
			pclanName = "";
			pclanPass = "";
			sendMessage("Clan successfully deleted!");
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
/* End */

In case 121 add this to cancel un-needed connections in loading.

server.MySQLLogginIn = false;

In the method initialize, above the welcome message (or under) add this.

		if(inClan && !clanExists(pclanName))
		{
			pclanName = "";
			pclanPass = "";
			pclanRights = 0;
			inClan = false;
			sendMessage("The clan you were in got deleted!");
			sendMessage("You are not currently in a clan!");
		}

What this does is checks if the clan still exists and if not, it will remove you from the clan. This is here because the owner has the options to delete clans.

Now for the commands. Add this (to make things look neater).

	public void clanCommand(String command) {
		actionAmount++;
		if (command.startsWith("mclan"))
		{
			if(command.length() > 6 && command.length() < 23)
			{
				if(!clanExists(command.substring(6, 11)))
				{
					if(!inClan)
					{
						appendClan(command.substring(6, 11), command.substring(12), playerName);
						sendMessage("Clan "+ command.substring(6, 11) +" with the password "+ command.substring(12) +" created!");
					} else {
						sendMessage("You are already in a clan!");
					}
				} else {
					sendMessage("Clan name exists!");
				}
			} else {
				sendMessage("Syntax error! ::mclan ##### ##########");
			}
		}
		else
		if (command.startsWith("jclan"))
		{
			try {
				if(command.length() > 6 && command.length() < 23)
				{
					if(!inClan)
					{
						if(loadClan(command.substring(6, 11), command.substring(12)))
						{
							pclanName = command.substring(6, 11);
							inClan = true;
							pclanRights = 1;
							sendMessage("You have joined the clan "+ command.substring(6, 11));
						} else {
							sendMessage("Invalid Name or Password!");
						}
					} else {
						sendMessage("You are already in a clan! Type ::lclan to leave!");
					}
				} else {
					sendMessage("Syntax error! ::jclan ##### password");
				}
			} catch(Exception e) {
				e.printStackTrace();
				sendMessage("Error whilst joining clan!");
			}
		}
		else
		if (command.startsWith("lclan"))
		{
			if(inClan) {
				if(pclanRights != 4)
				{
					inClan = false;
					pclanRights = 0;
					pclanName = "";
					pclanPass = "";
				} else {
					sendMessage("You are the owner, you must delete the clan!");
					sendMessage("Type ::dclan to delete the clan!");
				}
			} else {
				sendMessage("Not in a clan!");
			}
		}
		else
		if (command.startsWith("dclan"))
		{
			if(inClan) {
				if(pclanRights == 4)
				{
					deleteClan();
				} else {
					sendMessage("You do not have the rights to do this!");
				}
			} else {
				sendMessage("Not in a clan!");
			}
		}
		else
		if (command.startsWith("myclan"))
		{
			if(inClan) {
				sendMessage("Clan Name "+pclanName);
				sendMessage("Clan Owner "+clanOwner);
				sendMessage("Clan Rights "+pclanRights);
			} else {
				sendMessage("Not in a clan!");
			}
		}
	}

Now, in case 103 (command packet) add this.

clanCommand(playerCommand);

Now we are finished, here are the commands

::mclan ##### password - Create a clan with a 5 lettered name and a password
::jclan ##### password - Join an existing clan (need password to join)
::lclan - Leave current clan
::dclan - Delete current clan
::myclan - Displays clan info

Future updates

::clanpass password - Sets new clan pass
::dclanwar ##### - Declares clan war on an existing clan
::clanwar - Clan war info
::clanyell - Yell to clan (Really easy to add)

Credits: 80% Me - Pratically everything, 5% Winterlove for things like sendMessage, 15% Whitefang (A lil guide)

Note: Don't say this doesn't work, it does.
Note: Clan delete and Clan Owner (in command ::myclan) may not work!

You can now download winterlove with XharveyX's Clan System v1 - http://www.megaupload.com/?d=FLSAWJ1N

[MentaL]

[GhostSnyper]

Ah, you're a technition! Not bad. Good job in Sqling this, as it's done pretty well.although I don't see much use for the sqlLogginIn boolean. The connection to the database is only ever active while a query is being made, so as long as you don't have a looping method, you won't need this. although you could use this for connecting and disconnecting to save bandwidth, although it wouldn't make much of a difference. Overall great guide, mate

side note: If you've included your JDBC driver in your system path variable, you won't need the -cp /sql/jdbc.jar or whatever. I have it in my sql set up tutorial

[bounty-hunter]

i dont think anyone is going to do this because its to long -.-
Just give us the full download -.-

[harveylowndes]

I will upload a server with it later.

The msqllogginin boolean was going to be used for something i didnt get round to.

[harveylowndes]

http://www.megaupload.com/?d=FLSAWJ1N

Server download!