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:
- Use the Gradle Build Tool to help build the project and manage libraries.
- Get some Helper Libraries for JSON and database connections so we don’t write everything from scratch.
- Create a configuration file to specify our DB Connection Pool Information.
- Create a class that will use that file to get a Connecting to the Database.
- Create a Java object to hold our data. Business Objects
- Create a database access object (DAO) to retrieve information from the database and populate our Java object. Querying the Database
- Write the Servlet that uses all these items to output our data in JSON format over the web.
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:
We will also use a library to bind our Java object to JSON. We can check that on the next screen:
Then finally create the app with your own name and package:
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:
/.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
.
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"
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.
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:
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:
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.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:
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:
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:
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:
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.