EQEmu Java Server Manager Project for CS 541

EQEmu Java Server Manager Project for CS 541 – Database Systems By: Ronny L. Bull Instructor: Scott Spetka Ph. D.

Overview For my project I decided to create a front end server management utility for the open source EQEmulator game server Server Code: http: //eqemulator. org Database & Scripts: http: //peqtgc. com I wrote the program in Java because I wanted to get more experience using a standard programming language to interface with a My. SQL database

Requirements The server management utility needs to be able to connect to the MYSQL database with read/write permissions, it also needs to be able to communicate with the server over a telnet connection, and it requires a console area to display the output. It requires a simple to use menu driven system to perform all commands. Prompts are necessary to gather required information from the user.

My. SQL JDBC Connector In order to interface with the My. SQL database I needed to use the My. SQL JDBC Connector. The My. SQL JDBC Connector is a “Native Java driver that converts JDBC (Java Database Connectivity) calls to the network protocol used by the My. SQL database” http: //dev. mysql. com/usingmysql/java/

Apache Commons Net The management utility also required a telnet connection to perform some administrative tasks. To satisfy this requirement I use the Apache Commons Net library, which among other protocols (ftp, pop 3, smtp, tftp, etc. . . ) includes a telnet class. http: //commons. apache. org/net/

Connecting

Disconnecting

Interface Design I chose to go with a Swing based interface design. I used an open source Console class to display the output of the My. SQL queries and telnet commands. comweb. nl/java/Console. html A simple login area provides input boxes for server IP, port, database name, username and password.

Interface Design (Cont. ) A series of drop down menus that are broken up into categories provide quick access to administrative commands. Commands will provide prompts if input is necessary, the prompts are then used to fill variables that are used in SQL and Telnet commands.

Interface (Disconnected)

Interface (Connected)

Querying The Database Once a connection to the database is established through the JDBC driver you can begin executing queries by using statements. Queries can be pre-canned or use input variables that are changed with user prompts. A result set is created and can be printed to the console.

Query Example

Deleting Deleting items from the database is done in the same way as a query, with a statement. The only difference is there is no result set to construct. Prompts can be used to set variables in the delete statement.

Delete Example

Insert & Update Using Prepared Statements It took me a bit of time to figure out how to insert or update data. I tried many different combinations of statements similar to what was used for querying or deleting, however nothing would work. Finally I found that you can create precompiled SQL statements called prepared statements that allow you to reuse the statement and reduce execution time.

Prepared Statements (Cont. ) Prepared Statements allow you to create a SQL insert or update statement while using ? 's instead of actual values for the table data. You then can use variables to populate the ? 's upon executing the statement. In the case of my project I used pop-up boxes that prompt the user for the information, then uses it to complete the insert or update.

Inserting With Timestamps & Password Hashing The database I am working with stores account passwords in a SHA 1 hash form. In order to hash the password in a prepared statement I needed to surround the ? related to the password field with SHA 1(? ). Importing java. Sql. Timestamp allowed me to use the java. util. Date(). get. Time() function to insert a timestamp.

Insert Example

Update Example

Telnet Many server administration commands are performed via the telnet connection over TCP port 9000. In order to make the management tool more complete I decided to implement them as well as the SQL commands.

Messaging Via Telnet Messages can be sent from the program to the server over telnet. Any of the default channels can be used. Communication is one-way only. Program to server. Good for admin messages to players. IE. Server coming down for maintenance in 30 mins.

Telnet Example

Conclusion The My. SQL JDBC Connector proved to be very easy to use to perform basic queries on the database. Deletes were a bit more complex but not bad. Inserts and Updates were hard to grasp at first but once prepared statements were understood they became much simpler. Apache Commons is a very useful library for interfacing with most of the common networking protocols.

References EQEmulator: http: //www. eqemulator. org/ Project. EQ Database & Scripts: http: //peqtgc. com EQEmulator Database Schema: http: //www. eqemulator. net/wiki/wikka. php? wakka=Category. Database. Schema Simple Java Console: http: //www. comweb. nl/java/Console. html Apache Commons: http: //commons. apache. org/net/ Using My. SQL with Java: http: //dev. mysql. com/usingmysql/java/ Basic JDBC Concepts: http: //dev. mysql. com/doc/refman/5. 0/en/connector-jusagenotes-basic. html Using Prepared Statements: http: //download. oracle. com/javase/tutorial/jdbc/basics/prepared. html

References (Cont. ) Insert Timestamp value: http: //www. java 2 s. com/Tutorial/Java/0340__Database/Insert. TIMESTAMPval ue. htm Hashed Passwords update through JDBC (Stack. Overflow): http: //stackoverflow. com/questions/3879565/hashed-passwords-updatedthrough-jdbc-become-corrupt-more-of-encoding-problem