Trang chủ‎ > ‎IT‎ > ‎IBM Bluemix‎ > ‎

Bluemix fundamentals: Add an SQL database to your Java app

Overview

Learn how to create and add an SQL database to your Java web applications on Bluemix. Explore a data-driven, dynamically generated web store that changes appearance depending on current inventory stock level. Use the web-based Bluemix SQL Database Console to view and maintain your SQL databases.

This tutorial is part of the "Bluemix fundamentals" tutorial series on developerWorks.

 

What you'll do in this tutorial

1
 

Step 1. Download the code

Step 2. Deploy the WAR file to Bluemix

Step 3. Create an SQL database service instance and bind it to your app

Step 4. Import the app into your Eclipse workspace

Step 5. Examine the code structure

Step 6. Create a data-enabled WAR and deploy to Bluemix

Step 7. Add data with JPA

Step 8. Access SQL database via Bluemix SQL database console

Step 9. Explore data-driven dynamic website generation

Other Bluemix services

Quiz time!

Congratulations — you've completed the Bluemix fundamentals!

Let's get started

0
 

In the previous tutorial, "Add a world of cloud services to your application," you learned that services are simply code that's hosted on Bluemix or by a third-party partner that can add value to your deployed Bluemix apps.

Services lend powerful functions to your apps such as databases, mobile application support, analytics, legacy systems integration, and security management. They are like plug-ins in the sense that you can add and bind a service to your app at any time.

Of the available Bluemix services, database services are the most commonly used. And the SQL database service is one of the most popular web application additions.

Most web applications that are designed for the classic three-tiered architecture (such as Java EE apps) require a database service in the third tier:

Diagram of the three-tiered architecture

Your application logic runs in the stateless application tier, while data is stored in the database tier. This architecture enables you to scale your application to an increasing number of users simply by increasing the number of application instances.

Here's a quick overview of how you'll work with Bluemix in this tutorial to add an SQL database service to your Bluemix application.

Diagram of steps in this tutorial

You'll create an instance of an SQL database service and bind it to your Java application. Then you'll create tables (also known as schemas) in your database and populate them with data. You'll learn how your web application can dynamically change appearance depending on the data in the database. Finally, you'll use the IBM SQL Database Console on Bluemix as an alternative way to access your data.

Knowledge that you need for this tutorial

0
 

Software that you need for this tutorial

0
 

Step 1. Download the code

0
 
  1. Click this button to download the code for the sample Java web app, javasqltutorial.zip:

    Download the sample Java web app

  2. Save javatutorial1.zip to your computer and extract its contents, which consists of two files:
    • lauren.war is a standard, deployable Java EE WAR file that contains the servlet, JSPs, and configuration that constitute the application.
    • llldbproject.zip is an Eclipse project archive. It contains the complete Eclipse project for this tutorial.

Step 2. Deploy the WAR file to Bluemix

0
 

The SQL Database service on Bluemix offers an instance of IBM DB2 that you can bind and use immediately in your application. The current free beta plan offers an instance that supports up to 10 concurrent connections and 100 MB; other plans offer options up to 500GB and 100 concurrent connections.

You'll now deploy the app to Bluemix and create and bind an instance of the SQL Database service to it.

  1. If you're not already logged in to Bluemix, run this command from your OS command prompt to log in:

    cf api https://api.ng.bluemix.net/
    cf login

  2. Deploy lauren.war to Bluemix by running this command:

    cf push your-app-name -p lauren.war

    The name you choose for your application must be unique on Bluemix; in other words, it cannot be used by any other Bluemix user. If the name (called a "route") is already in use, you'll get an error.

  3. Open https://your-app-name.mybluemix.net/ in your browser to try out the app, a simple web store called Lauren's Lovely Landscapes. You may already be familiar with the app from previous tutorials. The store currently sells three prints; each print's page displays the associated name, image, and price. But you'll notice that there is no print listed in the web store! This is because this web store displays the print inventory from a database, and this version is not connected to any database. You'll establish that connection in the next step.

Step 3. Create an SQL database service instance and bind it to your app

0
 
  1. Create an instance of the SQL database service using the free beta plan, and name it "laurenlandscapes":

    cf create-service sqldb sqldb_free laurenlandscapes

  2. Bind this instance of the SQL database service to the running app:

    cf bind-service your-app-name laurenlandscapes

Even though the database service is now bound to the app, the code in the app does not yet use the database. You will address this in the next few steps by creating a new WAR file and deploying it to Bluemix.

Step 4. Import the app into your Eclipse workspace

0
 
  1. Start your Eclipse IDE and select File > Import. Then select General > Existing Projects into Workspace.
  2. Click the Select archive file option.
  3. Browse to and select the llldbproject.zip file.
  4. Click Finish.

The project is now imported into your workspace. You can see its structure in the Enterprise Explorer pane on the left. You may notice various problems listed in the Markers pane. You can safely ignore them as you work through the rest of this tutorial.

The next step familiarizes you with the project and the code.

Step 5. Examine the code structure

0
 

With your project open in Eclipse, take a look at the Enterprise Explorer pane on the left:

Enterprise Explorer pane

Expand Java Resources to see the Java source code files. Expand WebContent to see the two JSP files that make up the website.

The structure of the code should look familiar from previous tutorials.

As a refresher, web requests for a page of the Lauren's Lovely Landscapes store first go through the DispatchServlet, and then are forwarded to one of the three JSP pages — alaska.jsp, antartica.jsp, or australia.jsp — one for each photo print available for sale. The DispatchServlet attaches a WebsiteTitle object to the request, and each JSP page uses the WebsiteTitle to set the title to Lauren's Lovely Landscapes.

One difference that you will notice: The individual per-photo JSPs no longer exist; instead, a single, database-driven printdisp.jsp page now handles the display of the photo print detail and pricing.

New printdisp.jsp page

Click to see larger image

DispatchServlet now forwards to only one of two JSPs, home.jsp or printdisp.jsp. Requests destined for home.jsp are forwarded with a list of prints attached. home.jsp then generates the homepage based on prints available.

If the user selects any of the available prints, DispatchServlet forwards the incoming request to printdisp.jsp and attaches the information about a print to the request. printdisp.jsp then dynamically generates the title, jpg reference, description, and price for the print.

Let's try it out. First, you need to create a new data-enabled WAR and deploy it to Bluemix.

Step 6. Create a data-enabled WAR and deploy to Bluemix

0
 
  1. Get the database credentials for the bound SQL database service instance from the deployed app.

    cf env your-app-name

    This will display the environment variables available to the deployed app. The bound SQL database service information will show up in VCAP_SERVICES. Look for something similar to this:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    "VCAP_SERVICES": {
    "sqldb": [
    {
    "credentials": {
    "db": "SQLDB",
    "host": "75.126.1.1",
    "hostname": "75.126.1.1",
    "jdbcurl": "jdbc:db2://75.126.1.1:50000/SQLDB",
    "password": "3pKxxxxxxx",
    "port": 50000,
    "uri": "db2://user1234:3pKxxxxxxx@75.126.1.1:50000/SQLDB",
    "username": "user1234"
    },

    Back in Eclipse, look for the persistence.xml file. You may need to expand JPA Content, or look under Java Resources > src > META-INF. Open persistence.xml and modify the following properties:

    javax.persistence.jdbc.url
    javax.persistence.jdbc.user
    javax.persistence.jdbc.password

    Change these property values to match your VCAP_SERVICES. For javax.persistence.jdbc.url, be sure to modify only the first part of the property value with the "jdbcurl" field from VCAP_SERVICES.

    These changes link the database access code to the SQL database service instance that you have created.

  2. Rebuild the project. From the Eclipse menu, select Project > Build All. If "Build All" is grayed out, make sure Project > Build Automatically is not checked.
  3. Create a new WAR file in Eclipse by selecting File > Export. Then, from the menu, select Web > WAR File. Browse to the destination folder where you want to place the WAR file and name it "laurendb.war." Click Finish to generate the WAR file.
  4. Deploy this data-enabled WAR file to Bluemix:

    cf push your-app-name -p laurendb.war

  5. Open https://your-app-name.mybluemix.net/ in your browser to try out the app. You will notice that there are no prints listed in the web store! This is because even though you have created and bound the SQL database service instance to the app, it is currently empty. You will add data to the database and populate the store in the next step.
Web store with no prints listed

Click to see larger image

Step 7. Add data with JPA

0
 

EclipseLink adds Java Persistence API (JPA) support for your Eclipse projects. For this tutorial, you need EclipseLink version 2.4.x, which is compatible with jpa-2.0 — the only version that's reliably supported by the current Liberty Profile server on Bluemix.

JPA automates the tedious coding work of translating between object access calls and relational database queries. It does this by mapping between annotated Plain Old Java Objects (POJOs) that you create, and rows that reside in tables within a relational database.

JPA maps between POJOs and rows in database tables

An annotated POJO is an Entity in JPA. With the help of a JPA EntityManager, you can populate the POJO's field and then persist it. This will actually write or update rows in the associated relational database. You can also fetch rows from a table using instances of the POJO, with help from the EntityManager.

In the case of this web store application, Print.java (under Java Resources > src > com.ibm.devworks.examples.lll") contains the annotated POJO definition, and represents a print that's available in Lauren's web store. This class has the following properties, which map one-to-one to database fields:

The link between the POJO and the relational database is described in apersistence unit. The persistence.xml file that you edited earlier contains the definition of a persistence unit. A persistence unit may involve other mapping files in addition to persistence.xml.

  • title is the title of the print
  • description is a product description for the print
  • imgsrc is the name of the JPEG file that contains the print
  • price is the price in cents for the print
  • quan is the number of copies of the print currently in stock

For example, Print.java is transformed from a POJO to a JPA Entity via the @Entity annotation:

1
2
3
4
5
6
7
8
9
10
11
 
@Entity
public class Print implements Serializable {
@Id
@GeneratedValue
private long id;
private String title;
private String description;
private String imgsrc;
private float price;
private int quan;
private static final long serialVersionUID = 1L;

This POJO maps directly to a print table in the SQL database. Instances of this POJO can map to rows in the table.

Data Definition Language, or DDL, typically employs the CREATE TABLE statement for SQL databases. JPA can automatically generate an associated SQL table by examining an entity (annotated POJO) and then generating an SQL statement. Table and field names can be mapped automatically or explicitly via additional annotations.

  1. In the Eclipse project, look for Java Resources > src > com.ibm.devworks.example.java.lll > DataSeeder.java. Right-click the file, select Run As > Java Application. This class has a main() method that:
    • Drops any existing print table in the database
    • Creates prints table according to our annotated POJO
    • Populates the table with data of the three prints

    All of the above is accomplished via JPA through DDL. For example, the code to drop and create the table is done through EclipseLink's DDL generation capabilities:

    propertiesMap.put(PersistenceUnitProperties.DDL_GENERATION,
    PersistenceUnitProperties.DROP_AND_CREATE);

    And populating the print table with the Alaska print record looks like this (em is the JPA EntityManager):

    1
    2
    3
    4
    5
    6
    7
    8
     
    print = new Print();
    print.setDescription("Lauren loves this photo even though she wasn't present when the
    photo was taken. Her husband took this photo on a guys' weekend in Alaska.");
    print.setTitle("Alaska");
    print.setImgsrc("alaska.jpg");
    print.setPrice(75);
    print.setQuan(1);
    em.persist(print);
  2. Reload your browser pointed at https://your-app-name.mybluemix.net/. You should now see the prints that are available in the store, but notice that the Australia print is highlighted in red and cannot be selected. This is because the print is out of stock. If you take a look at the code in DataSeeder.java, you will see that the quantity of the Australia print is set to 0.
Lauren's Lovely Landscapes store with Australia print out of stock

Click to see larger image

Step 8. Access the SQL database via the Bluemix SQL database console

0
 
  1. Log in to your Bluemix dashboard.
  2. Find the laurenlandscapes SQL database service that you created. Click SERVICES on the left pane to expand it, and select laurenlandscapes.laurenlandscapes SQL database service

    Click to see larger image

  3. Click the Launch button. This launches your instance for viewing and manipulation. You can load data from the Excel spreadsheet into your SQL database from here.SQL database console welcome page

    Click to see larger image

  4. For now, click Work with Tables and then select PRINT. In the right pane, select Browse Data. You should now see all the rows in the print table, inserted by DataSeeder from Step 7.SQL database console with print table rows discplayed

    Click to see larger image

Step 9. Explore data-driven dynamic website generation

0
 

In this final step in the tutorial, a new shipment of Australia prints has hypothetically arrived, so you need to update the quantity available in the database. You'll then see the website dynamically change to include that newly in-stock item.

  1. In the Eclipse project, from Java Resources > src > com.ibm.devworks.examples.java.lll, look for AddAustraliaPrint.java.
  2. Right-click AddAustraliaPrint.java and then select Run As > Java Application. This will increase the print quantity from 0 to 3.
  3. Reload your browser pointed at https://your-app-name.mybluemix.net/. You should now see all prints back in stock! The Australia print is no longer in red. Try selecting it to see the details.

AddAustraliaPrint.java operates similar to DataSeeder.java. Within a JPA transaction, it fetches and looks for the Australia print, updates its quantity, and then persists it back to the SQL database:

Click to see code listing

If you are wondering how home.jsp handles the dynamic rendering of out-of-stock prints, you can locate the JSTL <c:choose> code in the JSP — combined with the outofstock CSS style class to change its appearance:

1
2
3
4
5
6
7
8
9
10
11
12
 
<c:choose>
<c:when test"${print.quan < 1}">
<div class="preview outofstock">
<a href="printdisp?id=${print.id}" class="outofstock">
${print.title} - <i><small>(out of stock)</small></i><br>
</c:when>
<c:otherwise>
<div class="preview">
<a href="printdisp?id=${print.id}">
${print.title}<br>
</c:otherwise>
</c:choose>

Other Bluemix services

0
 

In addition to the SQL database service, Bluemix offers many interesting services that can add value and features to your application. For example, many of IBM Watson's learning and cognitive capabilities are now available as Bluemix services. You may want to browse through the Bluemix services catalog and imagine all of the new features that you can add to your deployed web applications.

Quiz

0
 

Test your knowledge of what you've learned in this tutorial. Answers are below.

  1. The SQL database service on Bluemix is actually backed by:
    1. Oracle databases
    2. MySQL databases
    3. Elasticsearch
    4. IBM DB2
    5. IBM Cloudant
  2. An annotated Java POJO is known in JPA as:
    1. ORM
    2. Relational mapping
    3. an entity manager
    4. an entity
    5. a persistence unit
  3. JPA is an abbreviation for:
    1. Java Programming Authority
    2. Java Persistence API
    3. Java Python Algol
    4. Java Persisted Archives
    5. Java Persisting Adapter
  4. A JPA persistence unit is defined in:
    1. jpa_config.xml
    2. persistence.xml
    3. unit.xml
    4. server.xml
    5. web.xml
  5. Through the Bluemix SQL database console, you can:
    1. Import data from Excel spreadsheet into SQL tables
    2. View data loaded into tables
    3. Run a query against data loaded into tables
    4. View the schema definition of SQL tables
    5. All of the above

Answers to quiz

1. d, 2. d, 3. b, 4. b, 5. e

 

Congratulations!

0
 

In this tutorial, you:

  • Created a Bluemix SQL database service instance
  • Bound the SQL database instance to a web application
  • Created tables and populated rows in an SQL database using JPA
  • Configured a web application to use the SQL database service instance
  • Managed an SQL database and viewed data through the Bluemix SQL Database Console
  • Explored and examined the power of JPA POJO to relational mappings (Object to Relational Mapping, or ORM)
  • Learned how to create data-driven dynamic web applications

This tutorial concludes the "Bluemix fundamentals" tutorial series on developerWorks! If you've progressed through them all, way to go!

For a steady stream of tutorials and technical resources to help you continue learning, developing, and connecting with Bluemix, see the Bluemix page on developerWorks.

Comments