Transcript prepared statement

EQEmu Java Server Manager
Project for
CS541 – 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 MySQL 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.
MySQL JDBC Connector



In order to interface with the MySQL
database I needed to use the MySQL
JDBC Connector.
The MySQL JDBC Connector is a “Native
Java driver that converts JDBC (Java
Database Connectivity) calls to the network
protocol used by the MySQL 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, pop3, 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 MySQL queries
and telnet commands.
comweb.nl/java/Console/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 SHA1 hash form.
In order to hash the password in a
prepared statement I needed to surround
the ? related to the password field with
SHA1(?).
Importing java.Sql.Timestamp allowed me
to use the java.util.Date().getTime()
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 MySQL 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/

ProjectEQ Database & Scripts: http://peqtgc.com

EQEmulator Database Schema:
http://www.eqemulator.net/wiki/wikka.php?wakka=CategoryDatabaseSchema

Simple Java Console: http://www.comweb.nl/java/Console/Console.html

Apache Commons: http://commons.apache.org/net/

Using MySQL 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.java2s.com/Tutorial/Java/0340__Database/InsertTIMESTAMPvalu
e.htm
Hashed Passwords update through JDBC (StackOverflow):
http://stackoverflow.com/questions/3879565/hashed-passwords-updatedthrough-jdbc-become-corrupt-more-of-encoding-problem