Creating a MySQL database for a GUI applet

Thus far I have been working entirely with what I call text file code, run from the command line, and with nothing to see beyond a few cryptic messages to the screen. The process was interesting because it revealed differences in the required coding modifications extend beyond making the connection and include minor but critical differences in the SQL itself.

The next step is to modify a GUI applet. It is ages since I cobbled this together, so I have no idea where to begin. From memory there is something sitting on a website, which does not connect to a database, and there is a link (deliberately not hyperlinked here) to another applet sitting on my own server. That server has long been consigned to the bin, and by now the disk back up probably also erased.

My habit with code is to store it in many places and to give the locations labels which will have absolutely no meaning to me when I come looking for it later. While I was fumbling around my local hard drive, I suddenly remembered that I have a Java.net project: Rasch-itembank. What a great place to store code!

The database connection was coded as follows:

/**
* This method loads the database driver
*/
public String dataDriv() {
try {
String ldriver = "org.apache.derby.jdbc.ClientDriver";
Class.forName(ldriver);
buffer = "driver succeeds... ";
} catch(Exception ex) {
buffer = "driver error: " + ex.getMessage();
}
return buffer;
}
/**
* This method makes the connection
*/
public String dataConn() {
try {
// String strconn = "jdbc:derby://192.168.2.3:1527/dbAMJ";
String strconn = "jdbc:derby://202.65.70.207:1527/dbAMJ";
liveconn = DriverManager.getConnection( strconn );
smt = liveconn.createStatement();
buffer = "connection succeeds... ";
} catch(Exception ex) {
buffer = "connection error: " + ex.getMessage();
}
return buffer;
}

Converting for MySQL this will require replacing:

String ldriver = "org.apache.derby.jdbc.ClientDriver";

with

String ldriver = "com.mysql.jdbc.Driver";

and

String strconn = "jdbc:derby://202.65.70.207:1527/dbAMJ";
liveconn = DriverManager.getConnection( strconn );

with

String url = "jdbc:mysql://localhost:3306/dbAMJ";
liveconn = DriverManager.getConnection(url,"jhipp", "h1pp0");

Prior running this, dbAMJ needed to be created on the MySQL server, and I needed to look up the SQL I ran to do this. Eventually, I found it, in another small text file class. It was going to require a lot of editing, because it included the "Create=true" switch, which probably doesn't work for MySQL. I therefore opted to adapt the class I had used to create my financial database as follows:

import java.sql.*;

public class AMdbCreate {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("com.mysql.jdbc.Driver");
// Make connection
String url = "jdbc:mysql://localhost:3306/mysql";
connect = DriverManager.getConnection(url,"root", "my password");
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are the makedatabase lines
*/
System.out.println("try makedatabase dbAMJ");
try {
stat.executeUpdate("CREATE DATABASE dbAMJ");
System.out.println("makedatabase succeeds");
} catch(Exception ex) {
System.out.println("makedatabase fails");
System.out.println("CREATE DATABASE dbAMJ");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

This ran successfully with the following output:

try connection
connection succeeds
try makedatabase dbAMJ
makedatabase succeeds
disconnection succeeds

I then adapted the other two classes I had used to add a user and a table to my financial database. Adding the user was fine, but as I half expected, there was a problem creating the table. The screen output was as follows:

try connection
connection succeeds
try maketable
maketable fails
CREATE TABLE Items (Itemid INT
GENERATED BY DEFAULT AS IDENTITY, Partid BIGINT,
OpCode SMALLINT, Itemdet CHAR(9), Raw SMALLINT,
Rate DOUBLE, primary key (Itemid))
SQLException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL
server version for the right syntax to use near
'GENERATED BY DEFAULT AS IDENTITY, Partid BIGINT,
OpCode SMALLINT, Itemdet CHAR(9' at line 1

I suspected the problem lay with the "auto-increment" option on the item id. The syntax I used for this had come from the Derby SQL reference manual.

A quick Google search revealed a page in the MySQL online manual, which gave the correct language and syntax for MySQL.

All I had to do was change my Itemid field as follows:

Itemid INT NOT NULL AUTO_INCREMENT,

And the table was created without a problem. I am now ready to try running the applet.