More simple MySQL-JDBC examples

In order to consolidate my understanding of JDBC using the MySQL Connector/J driver, I shall convert all of the files from my financial mini-application, for which I described the JDBC-Derby connection in detail here. They are all examples of what I would call text file code, with each class very short and easy to follow. I shall work through half a dozen of these before attempting to convert any gui code.

In the first example I shall add a user to my dbFin database. R.G. Baldwin, to whose article I have referred in previous posts, achieved this in the same file/class as the creation of a database. I wanted to keep my first MySQL-JDBC class very simple. I didn't want confusion over the syntax of long strings mucking it up. So I shall create a special class to achieve that practically redundant but theoretically possibly important task.

The class having been created, the code was as follows:

import java.sql.*;

public class FindbUAdd {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
String dCreate = "GRANT SELECT,INSERT,UPDATE,DELETE," +
"CREATE,DROP " +
"ON dbFin.* TO 'jhipp'@'localhost' " +
"IDENTIFIED BY 'h1pp0'";
/**
* 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/dbFin";
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 makeuser lines
*/
System.out.println("try makeuser");
try {
stat.executeUpdate(dCreate);
System.out.println("makeuser succeeds");
} catch(Exception ex) {
System.out.println("makeuser fails");
System.out.println(dCreate);
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");
}
}
}
}
}

So far, with the code included in my previous post, and the code above, we have achieved what was achieved with twelve characters included in a single line using JDBC for Derby. The twelve characters were:

;create=true

included at the end of:

String connn = "jdbc:derby:dbFin;create=true";

The substantive part of my original FindbCreate class was the creation of a table. I shall now attempt this with a third mini-class:

import java.sql.*;

public class FintabCreate {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
String dCreate = "CREATE TABLE TransRaw " +
"(TRidx INTEGER, " +
"dDate DATE, TrType CHAR(5), StCode CHAR(3), " +
"TrPrice REAL, TrQ INTEGER, primary key (TRidx))";
/**
* 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/dbFin";
connect = DriverManager.getConnection(url,"jhipp", "h1pp0");
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 maketable lines
*/
System.out.println("try maketable");
try {
stat.executeUpdate(dCreate);
System.out.println("maketable succeeds");
} catch(Exception ex) {
System.out.println("maketable fails");
System.out.println(dCreate);
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");
}
}
}
}
}

The output from this was:

try connection
connection succeeds
try maketable
maketable succeeds
disconnection succeeds

This now takes me to the end of my original FindbCreate class, and is a suitable point for a break.