Getting Started - Java Platform


Conceptual Design

JED is an acronym for Java Editor For Datatables. JED is the server side business logic used to communicate with the client side javascript/jquery library known simply as datatables.editor.js or Editor (built by SpryMedia). The author of jquery Editor has decided on a communications protocol involving JSON (Javascript Object Notation). Parameters are sent to the server in the usual manner as HTTP GET and POST Requests, however, once the server side code executes, it returns a JSON string in response to every transaction that takes place. This means of communicating from your browser to the server is critical. Therefore, JED contains a class dedicated to containing all data recieved from HTTP GET and POST Requests which is aptly named "Parameters".

As you read further and study the control code, you'll come to see how an instance of Parameters is utilized and passed as a parameter into the constructor of JED's own class named "Editor". i.e.:
Editor editor = new Editor(Database db, String tablename, String id, Parameters params);

You can regard the Editor class as a manager since it marshals all the resources available, calling upon other classes in formulating a coherent SQL (Structured Query Language) statement, execute the query against a database, and from the database response, formulate a JSON response for the client side.

JED was designed to mimic the various components that make up an SQL statement, whether it be a simple SELECT query or more complex INSERT, UPDATE, or DELETE statement. For example, if we examine a simple SQL query such as
"SELECT employees.firstname, employees.lastname, employees.email FROM employees WHERE employees.id = 3456"
This statement will return a single record from the database that may look like this: | John | Denver | jdenver@someplace.com |

Having an idea of how your SQL statement is going to look ahead of time will help you to think about what classes you will need to involve from the JED library and how they interact to get the end result you are looking for. Therefore, an understanding of the SQL language is a prerequisite to working with JED.

The SQL statement itself can be broken down into established keywords and other objects. JED contains a class named Query which is aware of all the keywords that make up this query which are SELECT, FROM, and WHERE. All that is written between the SELECT keyword and the FROM keyword are fields within a database table. Therefore, JED has a Field class that allows you to specify the name of the database table and one of its fields for every instance of Field you create. i.e.: Field field1 = new Field("employees", "firstname", Field.Type.STRING);

The conditional at the end of the SQL statement is governed by another class named WhereCondition. It is through its constructor that you specify the condition on the SELECT query. i.e.:

Field field4 = new Field("employees","id",Field.Type.INT);
WhereCondition where = new WhereCondition(field4, "3456"WhereCondition.Comparator.EQUALS);

SELECT employees.firstname, employees.lastname, employees.email FROM employees WHERE employees.id = 3456

In summary, SELECT, FROM, and WHERE are keywords, shown here in red font, known to the Query class. An instance of the Query class is created internally by an instance of the Editor class. It is the Query class that really generates the SQL statement based on all the information provided. In blue font are the field names within the "employees" table of the database and whose details are captured within the Field class. In black font, after the FROM keyword, is the name of the database table, which you pass as a parameter to an instance of Editor and each Field constructor. In orange font, you have your condition on the statement governed by an instance of the WhereCondition class.

If we were to code the entire thing it would look like this:

Parameters params = new Parameters();
... collect all parameters from POST Request and load into params...
Database db = new Database("db_name");
Editor editor = new Editor(db,"employees""id", params);
Field field1 = new Field("employees","firstname", Field.Type.STRING);
Field field2 = new Field("employees","lastname", Field.Type.STRING);
Field field3 = new Field("employees","email", Field.Type.STRING);
Field field4 = new Field("employees","id", Field.Type.INT);
WhereCondition where = new WhereCondition(field4, "3456"WhereCondition.Comparator.EQUALS);
editor.addField(field1);
editor.addField(field2);
editor.addField(field3);
editor.addWhere(where);
editor.Process();
out.println(editor.toJSONString());

That's it. These lines of code are all you need to complete a simple SQL statement for the database. Keep in mind that for each field we indicate in our code, we would have the same number of fields represented on the client side in a form presented to the user for updates and inserts. Also, since we're dealing with client side fields, JED also performs validation on those fields, so for each Field object we can impose certain restrictions for validation purposes.

You may be thinking it would be easier just to write out the SQL statement as a string and send it to the database for actioning. The Query class ensures correct SQL syntax consistantly, taking into account variations on differing database systems requirements. And the code above does much more as you will see as we get into field validation, SQL Insertion Attacks, and the feature of uploading files. More on those topics later.

Our SQL statements can get more complex, but the classes in JED try to simplify things for us. For instance, there are times when we want to include fields from more than just one table in our SQL statement. In this case we require an instance of the Join class. Each instance of Join represents another table and its fields within the database. For this scenario, you are directed to examples Join Tables 1 and Join Tables 2 with corresponding tutorials Join 1 Tutorial and Join 2 Tutorial. Each of these tutorials will provide clarity on how best to write your code to get the desired results.

Directory Structure

Generally, you have a specific directory structure you must adhere to. In Tomcat there is a directory named webapps. It is in this directory you will begin to create your home directory which will house your web appliction. For the sake of this tutorial, we will name our home directory JEditor. The directory structure below it is also required. Within your home directory you must have two subdirectories name META-INF and WEB-INF. Note the example below:

[JEditor]
    |
    |_ index.html
    |_ media
    |    |_ css
    |    |    |_ images
    |    |    |_ scss
    |    |_ js
    |    |    |_ jquery.dataTables.js
    |    |    |_ dataTables.editor.js
    |    |_ extensions
    |    |_ images
    |_ META-INF
    |        |_ context.xml
    |_WEB-INF
            |_ web.xml
            |_ classes
            |    |_ log4j2.xml
            |_ lib
            |    |_ jed-1.#.jar
            |    |_ gson-2.8.1.jar
            |    |_ log4j-api-2.8.2.jar
            |    |_ log4j-core-2.8.2.jar
            |    |_ log4j-web-2.8.2.jar
            |    |_ commons-io-2.5.jar
            |    |_ commons-fileupload-1.3.3.jar
            |    |_ tika-app-1.16.jar
            |    |_ mysql-connector-java-5.1.18.jar
            |_ logs
                |_ jed.log

The most important files are indicated in red font with the JED library in purple font. To handle DataTables on the client side you will need minimally the two files indicated under the js folder. You can obtain these files on SpryMedia's website for DataTables and Editor. Other cascading style sheet and javascript files can be obtained there as well. Within the META-INF folder is the context.xml file. It is extremely important as it is used to set up a connection pool and provide the Servlet Container with all the information it requires to connect with your backend database. Below is an example of what one looks like:

<!-- New Context for MySQL Database Driver -->
<!-- The following URL will point you to the correct syntax regarding CONTEXTs:
http://jakarta.apache.org/tomcat/tomcat-5.5-doc/config/context.html -->
<Context path="/JEditor" docBase="JEditor" debug="0"
reloadable="true" crossContext="true">
    <!--Logger className="org.apache.catalina.logger.FileLogger"
    prefix="localhost_jeditor_log." suffix=".txt"
    timestamp="true"/ -->
    <Valve className="org.apache.catalina.valves.AccessLogValve"
    prefix="localhost_jeditor_log." suffix=".txt"
    pattern="common"/>
    <Resource name="jdbc/fcs_db" auth="Container" type="javax.sql.DataSource"
    username="webapp" password="secret" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/fcs_db?autoReconnect=true"
    maxTotal="8" maxIdle="3" maxWaitMillis="10000"/>
</Context>

Your context.xml file should look similar with different parameters set for username, password, and driverClassName if it is not referring to a MySQL database. Consult the documentation for your Servlet Container for more information regarding context.xml. Your database drivers (contained in a jar file) for your particular system should reside in the WEB-INF\lib folder. In our case, we were using mysql-connector-java-5.1.18.jar. Database drivers are generally obtainable from the database vendor. Since MySQL is owned by Oracle, we obtained the necessary connector file from their website. Note that JED conforms to three database systems: MySQL, Microsoft SQLServer, and Oracle. MySQL is regarded as the default database system.

When your database has been created, be sure to use a GRANT query against it to allow access from your web application. The syntax is as follows:

GRANT SELECT,INSERT,UPDATE,DELETE ON [database_name].* TO [username]@localhost IDENTIFIED BY "[password]"

Substitute database_name, username and password with the actual names like:

GRANT SELECT,INSERT,UPDATE,DELETE ON myDB.* TO webapp@localhost IDENTIFIED BY "secret"

This is an important step to ensure your web application can connect to your database.

You will find in your downloaded zip archive the following dependency files: jed-1.#.jar, gson-2.8.1.jar, log4j-api-2.8.2.jar, , log4j-core-2.8.2.jar, log4j-web-2.8.2.jar, commons-io-2.5.jar, commons-fileupload-1.3.3.jar, tika-app.1.16.jar. GSON is owned by GOOGLE and best supports the serialization and de-serialization of JSON strings into Java objects and vise versa. You can learn more about GSON here. You will notice that all jar files are also placed in the WEB-INF/lib directory. JED has upgraded to log4j2 and you can learn more about Log4j2 here. Now that JED has upgraded to log4j2, we no longer have to perform any initialization for it in our web applications. Just be sure to place your log4j2.xml configuration file inside the classes folder. log4j2 will auto initialize and configure itself based on the xml file. Your log4j2.xml file should look similar to this:

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="INFO">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
        </Console>
        <File name="MyFile" fileName="JEditor/WEB-INF/logs/jed.log" >
            <PatternLayout pattern="%d{yyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
        </File>
    </Appenders>
    <Loggers>
        <Root level="debug">
            <AppenderRef ref="Console" />
            <AppenderRef ref="MyFile"/>
        </Root>
    </Loggers>
</Configuration>

Next we want to address the web.xml file that should contain a reference to the resource created in your context.xml file. The resource attribute should proceed after your servlet mapping attributes.

<resource-ref>
  <description>DB Connection</description>
  <res-ref-name>jdbc/fcs_db</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

Once you have all your files in place, it is then only a matter of creating HTML (Hypertext Markup Language) in your *.jsp files, making them reference any Cascading Style Sheet and JavaScript files as you would find in the examples on this website. Then you need Java Server Pages or Servlets to handle the incoming requests from the client side. Please refer to the Java tab on all examples on this website to view the various ways you can configure and manipulate your database and present the data to the client side via JSON (Javascript Object Notation).

Controller Classes

We use Java Server Pages to handle the server side processing of the parameter data coming in from the client side, perform any of the four functions: SELECT, UPDATE, INSERT or DELETE, and return a JSON string to the client side with the results of one of those functions having been processed. Naturally, in a production environment Servlets should be used. Java Server Pages are used here only for demonstration purposes so that you can view the source code under the Java tab in each example. As you view the examples on this website, you will notice that they all follow a particular pattern, which we will describe next.

Basic Server Side Script Pattern

  1. The first thing you want to do is define the name of the database you are trying to connect to. i.e. String dbName = "editor";
  2. The second thing you want to do is define what tables from the database you want to use. You create a String array and the zeroeth element of that array will always be the parent table. All others that follow will be regarded as child tables. The child tables will be used with JOINs. We will talk more on those later. i.e. String[] tableNames = new String[]{"browsers"};
    This String array only has one element. When you want to include data from more than one table, you would have a comma separated list that looks more like this: String[] tableNames = new String[]{"employees","landlines","mobile"};
    "employees" would be regarded as the parent table and the others "landlines" and "mobile" would be regarded as child tables.
  3. For the parent table, it is mandatory for you to define the name of the column that is the Primary Key and identifier for every record. The id column needs to be a type that automatically increments for every new record that gets inserted into the table. In MySQL you define this using a property named AUTO_INCREMENT. There is more on this subject under the subtopic Conditions of Operation. i.e. String pKey = "id";
  4. Next, you define in another String array, a list of fields that will be displayed on the client side. i.e. String[] fields = new String[]{"browser","engine","platform","version","grade"};
  5. You will then collect all the parameters being passed to the server side by creating an instance of the class Parameters. i.e. Parameters params = new Parameters();
    We have created a routine that collects all the parameters that are currently known to be supported by DataTables. It is unlikely you will need to edit any of that portion of the scripts. If you feel you can improve upon the routine, then by all means do so.
  6. We now create an instance of the Database class. i.e. Database db = new Database(dbName);
    As a parameter to the constructor we pass the name of the variable that we defined earlier named dbName. If you were to use an Oracle database, the constructor for the Database allows you to specify this: Database db = new Database(dbName, Database.RDBMS.ORACLE);
  7. Next, we create an instance of the Editor class. i.e. Editor editor = new Editor(db, tableNames, pKey, params);
    Notice that we pass the variables defined earlier: db, tableNames, pKey, and params to the constructor. The Editor class deals with all these pieces of data in an appropriate manner.
  8. We now create an instance of the class Field for every table field defined in String[] array variable fields. i.e.

    Field field0 = new Field(tableNames[0],fields[0],Field.Type.STRING);
    field0.setValidator(new Validate(Validate.Type.REQUIRED));
    editor.addField(field0);

    The constructor for class Field requires three parameters: the name of the table, the name of the field, and the data type of the value it will store.
    If the field requires validation on the value being stored in it, then you need to call the method setValidator(...) on the field whose constructor requires an instance of the Validate class.
    Once the field is defined, you add it to the instance of the Editor class.

    SPECIAL NOTE: If a field does not require validation, there is a special Validate.Type named NOTREQUIRED. You might think this unnecessary, however I assure you it is quite necessary. In a world where we have unscrupulous individuals who wish to perform hacks on database systems, JED has incorporated validation code for any illegal character sequences that would potentially cause an SQL Insertion attack. It is in your best interest to use the NOTREQUIRED type to ensure validation checks are being performed behind the scenes and protecting your most valuable asset, the database.

  9. Call the method: editor.Process(). Calling this one method will determine which routine shall be executed upon the database: SELECT, INSERT, UPDATE or DELETE.
  10. Lastly, you will want to return to the client side the results in the form of a JSON string. You do this by calling the toJSONString() method on the instance of the Editor class. i.e. out.println(editor.toJSONString())

You will see variations on these scripts that deal with JOINs. You are invited to view those to discover how to collect data from multiple tables from the database.

Testing and Troubleshooting

To run your web application, you will need to use a URL similar to this: http://localhost:8080/JEditor/index.html if you are in your development environment. In your production environment the URL would contain your domain name: http://www.myplace.com/JEditor/index.html. 8080 is the port that Tomcat happens to listen on. Other Servlet Containers may use a different port.

When testing and troubleshooting your web application we recommend you use a browser like Firefox with the plugin FireBug. This combination of browser and development tool are indispensable when troubleshooting. With FireBug you can see what parameters are being passed to the server side and the JSON strings being returned to the client side.

Conditions of Operation

Database Management Systems are not all created equal, and with this in mind we have had to make some decisions in terms of how JED operates. There are a couple of items that require your attention concerning:

  1. Auto Increment on ID Fields
  2. Default Date Format
MySQL has the concept of AUTO_INCREMENT on ID fields. Other Database Management Systems don't have this, such as Oracle. However, using Sequence and Triggers, it is possible in Oracle to cause ID fields to automatically increment with the insertion of new records. We strongly encourage you to design your database tables to include an AUTO_INCREMENT ID field. JED operates under the assumption that when a new record is created that an ID field will increment and that the value of that ID will be retrieved so that the proper output JSON string to the client side will contain the correct information.

Regarding default Date formats, again MySQL supports the ISO_8601 standard date format which is: "yyyy-MM-dd". JED expects that the database default date format adheres to ISO_8601. We are aware that other Database Management Systems do not adhere to this format. JED attempts to read from and write to the database using ISO_8601. This format has been tested against MySQL, Oracle and SQL Server without any difficulty. You shouldn't need to make any changes to the database regarding this, but just be aware of it. We leave it to you to discover from your database vendor how best to change the default date format should an occasion arise that you are required to do so.

This concludes the Getting Started tutorial.