9.1 Overview

9.2 Client-side database programming: real-world context

9.2.1 Example: client side logistics programs

9.3 Java database development environment: core project

9.3.1 Java project structure

You should already be familiar with the distinction between source files and executable files, which is a typical distinction in many programming languages. However, here is a quick summary for Java. In java you store source code within files that have a <.java> extension. When you compile/build your source code (i.e., the collection of .java files), typically, you will compile to a different kind of file known as a <.class> file. It is a good idea to separate the storage of these files. This can, simply, be that you do not confuse .class with .java files during development. For example, .java files are more precious than .class files because the latter are built from the former; if you delete a .class file, you can easily recompile. However, if you delete a .java file, you have to re-write the code. This is also reflected in the use of version control systems (cvs, svn, git etc) where it is only the .java files that are versioned.

Either way, the .class (built) files and the .java (developed source) files sit in separate directories within a project.

Figure 1: Project structure.

We will be using src for the source files and build for the built files.

The src directory: There is more internal structure within the source folder. Within the src directory you should separate tests from the main source code. This is because, as a developer you will develop tests for your code, but these should not be considered as part of the main source code – i.e., the tests are not something you would release as part of your final executable software, because are tests of the codebase, not part of the final software product itself in anyway. However, tests are source code <.java> so they go in their own test folder. Additionally, within the main and the test directories there will be more folders. For example, if a program relies on the use of images, then this is likely to be stored within a folder named resources. All .java files will be stored in the java sub-directories of the respective main or test folders, depending on their purpose.

The build directory: the typical default file that a .java is converted to is a .class file. Therefore, within the build folder we have a classes folder, and this will contain the directories that are created from the packages, and then inside those packages the appropriate .class files will reside. However, in java we can chose what kind of ‘binary’ file we want to create. Instead of having lots of .class files we might choose to build our .java files and archive them to a .jar file. This, for example, is something that was done during the build of the MySQL JDBC, which will download later. This important point here, then, is that the build directory can have subdirectories. Later on in the development of the project, this structure makes it easier to configure different build options without affecting the ones that exist.

9.4 Java database development environment

9.4.1 JDBC as a ‘dependency’

9.4.2 Preparation for use of MySQL JDBC

9.5 Simple connection class: MySqlCommunicator.java

9.5.1 Class declaration and imports

package ac.uk.gcu.data.db.rbd.conn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySqlCommunicator {
…
…
…

9.5.2 Class fields

/**
* Username to connect to database, associated with {@link PASSWORD}
*/
private static final String USERNAME = "root";
/**
* Password to connect to database, associated with {@link USERNAME}
*/
private static final String PASSWORD = "password";
/**
* This is the target url of the JDBC connection.
*/
private static final String TARGET_URL = "jdbc:mysql://localhost?&useSSL=false&/";
/**
* The connection to the database
*/
private Connection connnection;

9.5.3 Constructor method

/**
* Default constructor
*/
public MySqlCommunicator(){

     try {
       connnection = DriverManager.getConnection(TARGET_URL, USERNAME, PASSWORD);
     } catch (SQLException e) {
       e.printStackTrace();
     }
     System.out.println(toString());
}

9.5.4 The toString() method

/**
* Indicate whether or not the connection failed or is exists
*/
public String toString(){

   String str = this.getClass().getSimpleName() + " ...\n";
   str += "...Using TARGET_URL: <" + TARGET_URL + "> \n";
   str += "...Using USERNAME:  <" + USERNAME + "> \n";
   str += "...Using PASSWORD:  <" + PASSWORD + ">\n";
   
   if(connnection == null) {
      str += "FAILED";
   } else {
     str += "EXISTS";
   }
   return str;
}

9.5.5 The main() and getter methods

// Getters

public Connection getConnnection() {
    return connnection;
}

// Local test driver
public static void main(String[] args) {

     MySqlCommunicator comm = new MySqlCommunicator();
     Connection conn = comm.getConnnection();
     
     if(conn != null) {
        System.out.println("conn is ready to use");
     }
}

} // end of class

9.5.6 Summary

9.6 Simple query set class: CodepointQuerySet.java

9.6.1 Class fields

/**
  * The upper limit of the number of rows returned from the database
  */
  private int limit = 10;

/**
  * A list of String MySQL queries
  */
  private ArrayList<String> queries;
  
/**
  * The name of the database this class has been coded for
  */
  private String dbname = "codepoint_db";

9.6.2 Constructor methods

/**
 *
 * @param lim
 */
 public CodepointQuerySet(int lim) {
  
        limit = lim;
        queries = createQuerySet(lim);
 }
  
/**
 *
 */
public CodepointQuerySet() {

           queries = createQuerySet(limit);
}

9.6.3 The main () and getter methods

// Getters

public ArrayList<String> getQueries() {
       return queries;
}

public String getQuery(int i) {
       return queries.get(i);
}

public String getDbName() {
       return dbname;
}

// Main method
public static void main(String[] args) throws SQLException {

       CodepointQuerySet qs = new CodepointQuerySet();
       System.out.println(qs.toString());
}

Queries held in class <ac.uk.gcu.query.CodepointQuerySet> would appear as pure sql as follows:

SELECT Postcode FROM codepoint_data LIMIT 10
SELECT Eastings FROM codepoint_data LIMIT 10
SELECT Eastings, Northings FROM codepoint_data LIMIT 10
SELECT Postcode, Eastings, Northings FROM codepoint_data LIMIT 10

9.6.4 Summary

9.7 Querying RDB from java: MySqlApp.java

9.7.1 Class fields

/**
 * The connection to a MySQL database using the MySqlCommunicator class
 */
Connection connection;

/**
 * The statement object used to execute a query
 */
Statement statement;

/**
 * The result of a given query
 */
ResultSet result;

/**
 * An example set of queries to execute
 */
CodepointQuerySet queries;

9.7.2 Constructor

/**
 *
 * @throws SQLException
 */
public MySqlApp() throws SQLException {
    connnection = new MySqlCommunicator().getConnnection();
    statement = connnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);
    queries = new CodepointQuerySet();
}

9.7.3 The runQueries() method

/**
 * Run a set of queries
 *
 * @throws SQLException
 */
public void runQueries() throws SQLException {

       // Use the codepoint_db schema
       // ---------------------------
       String query = "USE " + queries.getDbName();
       System.out.println("Running query: " + query);
       statement.executeQuery(query); // no results from this query.
       
       // Run a CodepointQuerySet set of queries
       // --------------------------------------
       
       for (int i = 0; i < queries.getQueries().size(); i++) {
              query = queries.getQuery(i);
              System.out.println("Running query: " + query);
              result = statement.executeQuery(query);
              Codepoint.displayData(result);
       }
}

9.7.4 tidyUp()

/**
 * Close the fields that, which are currently in a state capable to affect
 * the state of the database.
 *
 * @throws SQLException
 */
public void tidyUp() throws SQLException {

       if (result != null) {
              result.close();
       }
       if (statement != null) {
              statement.close();
       }
       if (connnection != null) {
              connection.close();
       }
}

9.7.5 The main() method

// Main method
public static void main(String[] args) throws SQLException {

       MySqlApp app = new MySqlApp();
       app.runQueries();
       app.tidyUp();
}

MySqlCommunicator ...
...Using TARGET_URL: <jdbc:mysql://localhost?&useSSL=false&/>
...Using USERNAME: <root>
...Using PASSWORD: <password>
EXISTS
Running query: USE codepoint_db
Running query: SELECT Postcode FROM codepoint_data LIMIT 10
    Postcode
1:  AB101AA
2:  AB101AB
3:  AB101AF
4:  AB101AG
5:  AB101AH
6:  AB101AL
7:  AB101AN
8:  AB101AP
9:  AB101AQ
10: AB101AR

Running query: SELECT Eastings FROM codepoint_data LIMIT 10
    Eastings
1:  394251
2:  394232
3:  394181
4:  394251
5:  394371
6:  394326
7:  394367
8:  394291
9:  394371
10: 394371

Running query: SELECT Eastings, Northings FROM codepoint_data LIMIT 10
    Eastings|Northings
1:  394251|806376
2:  394232|806470
3:  394181|806429
4:  394251|806376
5:  394371|806359
6:  394326|806522
7:  394367|806542
8:  394291|806424
9:  394371|806359
10: 394371|806359

Running query: SELECT Postcode, Eastings, Northings FROM codepoint_data LIMIT 10
    Postcode|Eastings|Northings
1:  AB101AA|394251|806376
2:  AB101AB|394232|806470
3:  AB101AF|394181|806429
4:  AB101AG|394251|806376
5:  AB101AH|394371|806359
6:  AB101AL|394326|806522
7:  AB101AN|394367|806542
8:  AB101AP|394291|806424
9:  AB101AQ|394371|806359
10: AB101AR|394371|806359

9.8 Summary