Connecting Java EE application to MySQL in WebSphere Application Server V8.5 Liberty Profile

Z Jacek Laskowski - Wiki Amatora Programowania
Skocz do: nawigacji, wyszukiwania

Connecting your enterprise application to a database in WebSphere Liberty Profile server requires setting up a library with a JDBC driver and a corresponding data source in server.xml, and then using the JNDI name in the application. It's simple as it sounds and it's hardly to be easier (although doable).

Spis treści

Configuring MySQL

Before accessing MySQL you should configure a database and a table where a servlet that comes with the enterprise application will insert records. It's assumed that MySQL has already been installed and initialized.

jacek:~/apps/mysql
$ ./bin/mysqld_safe
121019 09:48:02 mysqld_safe Logging to '/Users/jacek/apps/mysql/data/iMac.local.err'.
121019 09:48:02 mysqld_safe Starting mysqld daemon with databases from /Users/jacek/apps/mysql/data

In another terminal session, run the command mysql -u root -ppassw0rd < liberty-mysql.ddl with the liberty-mysql.ddl script to create necessary database structures.

jacek:~/apps/mysql
$ cat liberty-mysql.ddl 
drop database if exists liberty;
create database liberty;
use liberty;
create table users (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id));
desc users;
 
jacek:~/apps/mysql
$ mysql -u root -ppassw0rd < liberty-mysql.ddl 
Warning: Using a password on the command line interface can be insecure.
Field	Type	Null	Key	Default	Extra
id	mediumint(9)	NO	PRI	NULL	auto_increment
name	char(30)	NO		NULL

Configuring Liberty Profile - server.xml

The configuration file for Liberty Profile server instance is server.xml that's by default in ${server.config.dir}, i.e. the usr/servers/[server-name] directory.

The easiest way to apply your changes to server.xml is to use IBM WebSphere Application Server V8.5 Liberty Profile Developer Tools V8.5 and the Liberty Profile Configuration Editor.

You may also use an XML editor to edit the file directly yourself, but it obviously requires more knowledge about the configuration settings of Liberty Profile.

The server.xml that connects Liberty Profile to MySQL database called liberty should be as follows.

<server description="new server">
  <dataSource jndiName="jdbc/mysql">
    <jdbcDriver id="mysqlDriver" libraryRef="mysql-connector" />
    <properties URL="jdbc:mysql://localhost/liberty" password="{xor}Lz4sLChvLTs=" user="root" />
  </dataSource>
  <library description="MySQL JDBC Driver" id="mysql-connector" name="MySQL Connector">
    <fileset dir="/Users/jacek/apps/mysql-connector-java" id="mysql-connector-jar" includes="mysql-connector-java-*.jar" />
  </library>
</server>

It assumes that the JDBC driver for MySQL is in the /Users/jacek/apps/mysql-connector-java directory.

See Liberty profile: Configuration elements in the server.xml file for a description of the XML elements - dataSource, jdbcDriver, library and fileset. With the Liberty Profile Configuration Editor it boils down to selecting appropriate elements in GUI.

Fire up the Liberty Profile server.

jacek:~/apps/wlp
$ ./bin/server run 
Launching defaultServer (wlp-1.0.0.20120428-1251/websphere-kernel_1.0.0) on OpenJDK 64-Bit Server VM, version 1.7.0-u10-b06-20120906 (en_US)
[AUDIT   ] CWWKE0001I: The server defaultServer has been launched.
[AUDIT   ] CWWKF0011I: The server defaultServer is ready to run a smarter planet.

It assumes that the name of the server to start is defaultServer. Give the name of the server after run if it's different.

Deploying servlet with @Resource annotation

You can do a lookup in a programmatic or declarative approach. We'll follow the latter and will use the @Resource annotation in a servlet.

package pl.japila.websphere;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
 
@WebServlet("/users")
public class UserResource extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    @Resource(lookup = "jdbc/mysql")
    private DataSource libertyDb;
 
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String name = request.getParameter("name");
        if (name == null || name.trim().length() == 0) {
            name = "Jacek";
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = libertyDb.getConnection();
            stmt = conn.prepareStatement("INSERT USERS SET NAME = ?");
            stmt.setString(1, name);
            int result = stmt.executeUpdate();
 
            PrintWriter out = response.getWriter();
            out.printf("<html><h2>%s record inserted for %s</h2></html>", result, name);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignored) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignored) {
                }
            }
        }
    }
 
}

With the servlet you're ready to deploy it to Liberty Profile. When you initiate the deployment, the server will notice that the application uses features not available yet in the current configuration and react appropriately installing them.

[AUDIT   ] CWWKG0016I: Starting server configuration update. 
[AUDIT   ] CWWKG0017I: The server configuration was successfully updated in 0.6 seconds.
[AUDIT   ] J2CA8004I: The dataSource default-0 is available as jdbc/mysql.
[AUDIT   ] J2CA8000I: The jdbcDriver mysqlDriver is available.
[AUDIT   ] CWWKZ0058I: Monitoring dropins for applications. 
[AUDIT   ] CWWKZ0058I: Monitoring dropins for applications. 
[AUDIT   ] CWWKT0016I: Web application available (default_host): http://localhost:9080/AccessMySQL/*
[AUDIT   ] CWWKZ0001I: Application AccessMySQL started in 0.49 seconds.
[AUDIT   ] CWWKF0012I: The server installed the following features: [jdbc-4.0, localConnector-1.0, servlet-3.0, jndi-1.0].
[AUDIT   ] CWWKF0008I: Feature update completed in 6.155 seconds.

The server.xml will ultimately look as follows:

<server description="new server">
  <dataSource jndiName="jdbc/mysql">
    <jdbcDriver id="mysqlDriver" libraryRef="mysql-connector"/>
    <properties URL="jdbc:mysql://localhost/liberty" password="{xor}Lz4sLChvLTs=" user="root" />
  </dataSource>
 
  <library description="MySQL JDBC Driver" id="mysql-connector" name="MySQL Connector">
    <fileset dir="/Users/jacek/apps/mysql-connector-java" id="mysql-connector-jar" includes="mysql-connector-java-*.jar" />
  </library>
 
  <featureManager>
    <feature>localConnector-1.0</feature>
    <feature>servlet-3.0</feature>
    <feature>jdbc-4.0</feature>
  </featureManager>
 
  <applicationMonitor updateTrigger="mbean" />
 
  <application id="AccessMySQL" location="AccessMySQL.war" name="AccessMySQL" type="war" />
</server>

Testing

Upon executing the servlet with the name parameter set to a name, i.e. http://localhost:9080/AccessMySQL/users?name=Agata, the browser should display a page with the following output.

Websphere-liberty-mysql-record-inserted.png

Look at the USERS table in MySQL to ensure the record(s) are indeed there.

jacek:~/apps/mysql
$ mysql -u root -ppassw0rd liberty -e 'select * from users'
Warning: Using a password on the command line interface can be insecure.
+----+-------+
| id | name  |
+----+-------+
|  1 | Agata |
+----+-------+

There's the record for the servlet's execution so you should be well confident that it really works. Congratulations!

Questions? Suggestions? Ideas? You're kindly welcome to send me an email to jacek@japila.pl. Thanks!

Osobiste
Przestrzenie nazw

Warianty
Działania
Nawigacja
Narzędzia