8. Query the Database and Output JSON

How do we specify, connect, and query the database? Then convert that data to a JSON string to send?

To do this we’ll need to:

8.1. Gradle Build Tool

Any large project involving multiple files needs some way to specify how it will be built. Starting around 2000, Java projects started using a scripting tool called Ant. A few years later, a tool called Maven was built using Ant, that helped standardize how apps were built. Ant and Maven are still popular, although another tool called Gradle is gaining in popularity.

For this next project, we’ll be using Gradle. By default, IntelliJ will set up new projects in Maven. We’ll create a new project and specify Gradle instead:

../../_images/new_gradle_project.png

Creating a new gradle project

We will also use a library to bind our Java object to JSON. We can check that on the next screen:

../../_images/binding.png

Then finally create the app with your own name and package:

../../_images/create_app.png

Finally, you’ll need to create a new GitHub project for this, and set up a Git repository. Just like any other project, we don’t want to check in every file, so create a .gitignore file in the root of the project and add these to filter things out:

.gitignore file
/.gradle/
/.idea/
/build/
/src/main/webapp/META-INF/context.xml
.DS_Store

8.2. Helper Libraries

In our Gradle project, you’ll find a file named build.gradle. It specifies all the libraries we’ll use in our project. We don’t have to find or download the libraries, Gradle will take care of this for us. We just tell Gradle what libraries we need. In our case, we’ll use JSON-Bind for creating JSON notation, and MySQL-Connector for hooking up to the database.

Update build.gradle to have this for a dependency section:

dependencies {
    implementation('javax.json.bind:javax.json.bind-api:1.0')
    implementation('org.glassfish:jakarta.json:1.1.5')
    implementation('org.eclipse:yasson:1.0.3')
    compileOnly('javax.servlet:javax.servlet-api:4.0.1')
    implementation('mysql:mysql-connector-java:8.0.23')

    testImplementation("org.junit.jupiter:junit-jupiter-api:${junitVersion}")
    testRuntimeOnly("org.junit.jupiter:junit-jupiter-engine:${junitVersion}")
}

8.3. DB Connection Pool Information

Now, we need to set up the the connection. We don’t want to hard-code the connection information into our code. That would make updates too difficult, and how would you have a different database for production, testing, and your development? To (partially) solve this, we keep the information in a separate text file.

In addition to the WEB-INF folder, we need a directory called META-INF stored ad the same level. Connection information is stored in a file called META-INF/context.xml.

../../_images/directory_setup.png

How the directory tree should look

Here is an example of what it should look like. Update the file below with your server name and password. For example, my url looks like:

url="jdbc:mysql://cis320.cp6n5ccfdx2q.us-west-2.rds.amazonaws.com:3306/cis320"

META-INF/context.xml which MUST go in .gitignore
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 <?xml version="1.0" encoding="UTF-8"?>
 <Context>
     <Resource name="jdbc/cis320"
               auth="Container"
               type="javax.sql.DataSource"
               maxTotal="50"
               maxWaitMillis="-1"
               username="cis320"
               password="PUT_PASSWORD_HERE"
               driverClassName="com.mysql.cj.jdbc.Driver"
               url="jdbc:mysql://PUT_SERVER_NAME_HERE:3306/cis320"
               maxActive="15"
               maxIdle="3"
               testOnBorrow="true"
               removeAbandoned="true"
               removeAbandonedTimeout="55"
               validationQuery="SELECT 1"
               validationInterval="34000"
               timeBetweenEvictionRunsMillis="34000"
               minEvictableIdleTimeMillis="55000"
               factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
     />
     <ResourceLink name="jdbc/cis320"
                   global="jdbc/cis320"
                   type="javax.sql.DataSource" />
 </Context>

WAIT! This has our DB password in it. And server. We NEVER check this into a version control system. You have to add this to .gitignore. If you fail to do this, then you’ve compromised every individual in your database and perhaps grabbed headline news. No pressure.

You probably need this in the .gitignore:

src/main/webapp/META-INF/context.xml

Before you commit, you can do a git status -u and make sure that the context.xml nor META-INF directory is set as untracked (which would be picked up and added if you did a git add -A). If you fail to get this part right, you’ll need to re-do a lot of things. And possibly expose your customer info.

Tomcat has built in classes to manage a connection pool. It takes a long time to build a connection, so we reuse connections to the database between web requests. Tomcat looks for this specific file in this specific directory when it starts up. If it exists, it will read context.xml file and set up that pool.

8.4. Connecting to the Database

We need to create a class that will manage our database connections.

8.4.1. Creating a Class

It is easy to create a new Java class inside a package. Right-click on the package and select a new Java class.

../../_images/new_class.png

If your class is called DBHelper, then it must go into a file called DBHelper.java. If you rename the class, you have to rename the file.

Furthermore, if DBHelper is in a package called edu.simpson.computerscience.webdevelopment it must be stored in a directory path of: edu/simpson/computerscience/webdevelopment. The IDE will take care of the file naming and directory structure for you.

8.4.2. Understanding DB Connection Code

The basic code to create a database connection in Java, using a connection pool, looks like this. (Don’t type in any code yet, we need to set up your project first.)

import java.sql.Connection;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;

// .. etc ..

// Create a context. Uses context.xml
Context initContext = new InitialContext();

// Select which context to lookup.
Context envContext = (Context) initContext.lookup("java:/comp/env");

// Grab a source of database connection. Note how this matches the name
// field in context.xml.
DataSource ds = (DataSource) envContext.lookup("jdbc/cis320");

// Hey, now we've got a datasource for connections. Let's get a connection.
Connection conn = ds.getConnection();

What Java classes are we using here?

  • InitialContext - Used to get information from our context.xml file. Comes from the javax.naming package.
  • Context - This is a base class for InitialContext. It is just a more general version of the InitalContext class.
  • DataSource - This class manages our database connections. We get database connections from this class.
  • Connection - This class holds the information about our database connection.

So, InitialContext/Context is used to figure out our DataSource, password, and other items in context.xml. We get connections from the DataSource, and the connection itself is managed by Connection.

8.4.3. The DBHelper Class

I hate putting in all that code when I want a database connection. Plus what if I change a name? I don’t want to go through my entire program replacing “jdbc/cis320” with something new. So I typically put this in a helper class that looks like:

DBHelper.java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package edu.simpson.cis320.crud.cis320_crud_app;

import javax.naming.Context;
import java.sql.Connection;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBHelper {
    private final static Logger log = Logger.getLogger(DBHelper.class.getName());

    public static Connection getConnection() {
        try {
            log.log(Level.FINE, "Getting a database connection");

            // Create a context. Uses context.xml
            Context initContext = new InitialContext();

            // Select which context to lookup.
            Context envContext = (Context) initContext.lookup("java:/comp/env");

            // Grab a source of database connection. Note how this matches the name
            // field in context.xml.
            DataSource ds = (DataSource) envContext.lookup("jdbc/cis320");

            // Hey, now we've got a datasource for connections. Let's get a connection.
            Connection conn = ds.getConnection();
            return conn;
        }
        catch(Exception e) {
            // Whoops, something bad happened. Log it.
            log.log(Level.SEVERE, "Unable to get a database connection.", e);
            return null;
        }
    }
}

Then in the code I can just do:

conn = DBHelper.getConnection();

8.4.4. Refresher on Static Classes

Oh wait! This might have something you don’t know, or forgot.

Normally, when I call a method on a class, I have to have an instance, right? I can’t do this:

Dog.bark();

I can’t do the code above, because what dog do I want to have bark? I have to create an instance. Like the following:

spot = Dog();
fluffy = Dog();

spot.sit();
fluffy.bark();

Great, that makes sense. I need to associate a method with what object it is operating on. But not all functions should have an object. What about sine and cosine function? We don’t want to do this:

my_math = Math();
answer = my_math.sin(angle);

That code’s just dumb. What is the point of my_math? Conceptually there is no object. Why can’t I just do:

answer = sin(angle);

I can’t, because everything has to be in a class. To make a middle-ground, Java and other languages have static methods. Methods that don’t use instance variables, and can be called just like a function. A function where you don’t need to create an instance first:

answer = Math.sin(angle);

How do you create such a method? Use the static keyword.

public static double sin(double angle) {

8.5. Business Objects

You’ll need a simple Java class with getters and setters to represent objects in your program. Often each object represents a row on the table, and each field is a column in that table.

Here’s a Person class to get started with:

public class Person {

    private int id;
    private String first;
    private String last;
    private String phone;
    private String birthday;

    public int getId() {return id; }
    public void setId(int id) { this.id = id; }

    public String getFirst() { return first; }
    public void setFirst(String first) { this.first = first; }

    /* Add additional getters and setters for each field.
       Just follow the same pattern. */
}

8.6. Querying the Database

Typically, I create a “Data Access Object”. This object has static methods for each action, and each action is a query or update I want to run on the database. (Remember, static - no need to create an instance of the object.) For example, here is a the PersonDAO class that gets a list of people:

PersonDAO.java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
package edu.simpson.cis320.crud.cis320_crud_app;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.List;
import java.util.LinkedList;
import java.sql.PreparedStatement;

/**
 * Data Access Object for the Person table/class
 */
public class PersonDAO {
    private final static Logger log = Logger.getLogger(PersonDAO.class.getName());

    /**
     * Get a list of the people in the database.
     * @return Returns a list of instances of the People class.
     */
    public static List<Person> getPeople() {
        log.log(Level.FINE, "Get people");

        // Create an empty linked list to put the people we get from the
        // database into.
        List<Person> list = new LinkedList<Person>();

        // Declare our variables
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        // Databases are unreliable. Use some exception handling
        try {
            // Get our database connection
            conn = DBHelper.getConnection();

            // This is a string that is our SQL query.
            // Update for all our fields
            String sql = "select id, first from person";

            // If you had parameters, it would look something like
            // String sql = "select id, first, last, phone from person where id = ?";

            // Create an object with all the info about our SQL statement to run.
            stmt = conn.prepareStatement(sql);

            // If you had parameters, they would be set wit something like:
            // stmt.setString(1, "1");

            // Execute the SQL and get the results
            rs = stmt.executeQuery();

            // Loop through each record
            while(rs.next()) {
                // Create a new instance of the Person object.
                // You'll need to define that somewhere. Just a simple class
                // with getters and setters on the fields.
                Person person = new Person();

                // Get the data from the result set, and copy it to the Person
                // object.
                person.setId(rs.getInt("id"));
                person.setFirst(rs.getString("first"));
                /* FILL IN THE REST HERE */


                // Add this person to the list so we can return it.
                list.add(person);
            }
        } catch (SQLException se) {
            log.log(Level.SEVERE, "SQL Error", se );
        } catch (Exception e) {
            log.log(Level.SEVERE, "Error", e );
        } finally {
            // Ok, close our result set, statement, and connection
            try { if (rs != null) rs.close(); }
            catch (Exception e) { log.log(Level.SEVERE, "Error", e ); }

            try { if(stmt != null) stmt.close(); }
            catch (Exception e) { log.log(Level.SEVERE, "Error", e ); }

            try { if(conn != null) conn.close(); }
            catch (Exception e) { log.log(Level.SEVERE, "Error", e ); }
        }
        // Done! Return the results
        return list;
    }

}

8.7. Prepared Statements

What are prepared statements?

https://www.youtube.com/watch?v=_jKylhJtPmI

8.8. Write the Servlet

Then we need to create a new servlet. The servlet will output tie everything together. Query the database, output the JSON result.

8.8.1. Creating a Servlet

We can create servlets similar to the way we create classes:

../../_images/new_servlet.png

We want to update the application so that the URL is /api/name_list_get and print a simple Hello to see if it works.

The servlet itself will look like:

Hello World Servlet
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package edu.simpson.cis320.crud.cis320_crud_app;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.logging.Level;
import java.util.logging.Logger;


@WebServlet(name = "NameListGetServlet", value = "/api/name_list_get")
public class NameListGetServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        // Type of output (HTML, JSON, image, whatever
        response.setContentType("text/plain");

        // Get an object that can write to the network
        PrintWriter out = response.getWriter();

        // Write to the network
        out.print("Hello");
    }
}

Try the servlet out. You’ll need to manually change index.jsp to /api/name_list_get.

8.8.2. Debug Logging

Let’s add a “logger” that will output to our log file and help us debug later:

package edu.simpson.cis320.crud.cis320_crud_app;

import javax.json.bind.Jsonb;
import javax.json.bind.JsonbBuilder;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


@WebServlet(name = "NameListGetServlet", value = "/api/name_list_get")
public class NameListGetServlet extends HttpServlet {
    private final static Logger log = Logger.getLogger(PersonDAO.class.getName());

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        log.log(Level.FINE, "Get people servlet");

        // Get setup up to output JSON text
        response.setContentType("text/plain");
        PrintWriter out = response.getWriter();

        // Write out that string
        out.println("Hello");
    }
}

If that works ok, try getting a list of people from the DAO:

// Use our DAO to get a list of people
List <Person> peopleList = PersonDAO.getPeople();

// Write out that string
out.println("Hello, got " + peopleList.size() + " records.");

If that works, we need to convert that list to JSON format. We change the content type of our output to application/json and then use a class called JsonbBuilder to make our JSON string.

// Get setup up to output JSON text
response.setContentType("application/json");
PrintWriter out = response.getWriter();

// Use our DAO to get a list of people
List <Person> peopleList = PersonDAO.getPeople();

Jsonb jsonb = JsonbBuilder.create();
String jsonString = jsonb.toJson(peopleList);

// Write out that string
out.println(jsonString);

Out final program looks like:

NameListGetServlet.java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
import javax.json.bind.Jsonb;
import javax.json.bind.JsonbBuilder;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


@WebServlet(name = "NameListGetServlet", value = "/api/name_list_get")
public class NameListGetServlet extends HttpServlet {
    private final static Logger log = Logger.getLogger(PersonDAO.class.getName());

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        log.log(Level.FINE, "Get people");

        // Get setup up to output JSON text
        response.setContentType("application/json");
        PrintWriter out = response.getWriter();

        // Use our DAO to get a list of people
        List <Person> peopleList = PersonDAO.getPeople();

        Jsonb jsonb = JsonbBuilder.create();
        String jsonString = jsonb.toJson(peopleList);

        // Write out that string
        out.println(jsonString);
    }
}

8.9. Deploying the WAR File

The .war file can be uploaded to our java bean server, but you’ll likely run into two issues:

  • Where is the war file in Gradle?
  • The DB can’t connect and I just get a spinney web page.

The .war file is in the build/libs directory.

As our DB is only allowed to connect to our own computer, we need it to also connect to our server. Click into the RDS section and select your database. Then click on the link to your security group:

../../_images/select_db_security.png

Next, select MySQL for the port, then custom for the computer, and then click the ‘search’ which opens a list of stuff. Select your environment. If you aren’t sure which environment is yours, try selecting one, save, see if it works, then keep trying if it doesn’t.

8.10. List Records Lab

Once through this, complete Assignment 3 - List Records Part 1.