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 | email@example.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.:
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:
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.
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]
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 six files: jed-1.#.jar, gson-2.2.4.jar, log4j-1.2.17.jar, commons-io-2.4.jar, commons-fileupload-1.3.1.jar, tika-app.1.11.jar. JED requires the other 5 files as dependancies. 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. You can learn more about Log4j here. JED does NOT initialize Log4j. For your web application, you are required to initialize Log4j. While there are several ways to do this, the following approach is how we did it. In your zip archive file you will find two properties files, which you can edit at your discretion: log4j_CONSOLE.properties or log4j_FILE.properties. Alternatively, you can make one of your own properties files. The properties files contain instructions to either print error messages to the console, or you can have the error messages printed to a log file.
To initialize Log4j, we began by creating a servlet whose code looks like this:
Then we made an entry in the web.xml file that looks like this:
What happens is that when the application loads, web.xml is read in and the servlet Log4jInit gets called to initialize Log4j. The trick is to include in the web.xml file the tag: <load-on-startup>1</load-on-startup>. Log4jInit reads in the parameter value for log4j-init-file and loads the appropriate properties file, which has all the instructions in it telling Log4j where to print error messages, and in which format. In addition, you will notice we use the <resource-ref> tag to reference a resource to make our connection to the database possible. The Tomcat docs make mention of it here.
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
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.
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.
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.
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:
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.