Freud's Blog

Stay hungry, stay foolish. 少年辛苦终身事,莫向光阴惰寸功。

Websphere Application Server(06)--WEB应用使用JNDI连接数据库

Posted on By Freud Kang

1. 准备Mysql数据库

1.1 在191安装docker

$ yum install -y docker
$ systemctl start docker

1.2 启动Mysql

docker run --name was-mysql -e MYSQL_ROOT_PASSWORD=root \
--privileged=true -d -p 3306:3306 mysql:5.7.26

1.3 创建数据库 was-test

$ docker ps
$ docker exec -it was-mysql /bin/sh
$ mysql -uroot -proot
CREATE DATABASE /*!32312 IF NOT EXISTS*/`WAS-TEST` /*!40100 DEFAULT CHARACTER SET utf8 */;

1.4 执行如下脚本创建 USER 表

USE WAS-TEST;

DROP TABLE IF EXISTS USER;

CREATE TABLE USER(
	ID BIGINT(32) NOT NULL AUTO_INCREMENT,
	USERNAME VARCHAR(128),
	PASSWORD VARCHAR(32),
  	PRIMARY KEY (ID)
) ENGINE=InnoDB;

INSERT INTO USER(USERNAME, PASSWORD) VALUES('USER_1','PASS_1');
INSERT INTO USER(USERNAME, PASSWORD) VALUES('USER_2','PASS_2');
INSERT INTO USER(USERNAME, PASSWORD) VALUES('USER_3','PASS_3');

2. 配置数据库连接池

2.1 配置JAAS-J2C认证

在WEB Console上依次点击 Security -> Global Security -> Java Authentication and Authorization Service -> J2C authentication data, 新建一个authentication的数据,指定连接数据库用的用户名和密码,此处为root/root

图片

2.2 上传Mysql驱动jar包

mysql-connector-java-5.1.6.jar上传至191和192的此目录处 /opt/IBM/WebSphere/AppServer/drivers/mysql-connector-java-5.1.6.jar

2.3 创建JDBC Provider

在WEB Console上依次点击Resources -> JDBC -> JDBC Providers, 选中Scope为Cluster=hellocluster然后点击New

图片

在如下弹出页面中,输入对应的信息

Database Type User-defined
Implementation class name com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
Name mysql-jdbc-provider

图片

在class path处,输入对应的jar地址 /opt/IBM/WebSphere/AppServer/drivers/mysql-connector-java-5.1.6.jar

图片

2.4 配置数据源

在WEB Console上依次点击Resources -> JDBC -> Data sources, 选中Scope为Cluster=hellocluster然后点击‘New’

图片

在输入基础信息步骤,输入如下信息:

Data source name mysql-jdbc-source
JNDI name jdbc/MysqlDBPool

图片

选择上一步创建好的Provider mysql-jdbc-provider

图片

Data store helper class name指定为如下 com.ibm.websphere.rsadapter.GenericDataStoreHelper

图片

设置security aliases中设置如下:

Coponent-managed authentication alias wasnode1CellManager01/mysqldb-credentials
Mapping-configuration alias DefaultPrincipalMapping
Container-Managed authentication alias wasnode1CellManager01/mysqldb-credentials

图片

2.5 配置连接属性

在WEB Console中依次点击Resources -> JDBC -> Data sources, 在scope中选择Cluster=hellocluster 然后点击 mysql-jdbc-source -> Additional Properties -> Custom properties, 修改或添加其中的serverName, portNumber, databaseName来设置ip, 端口和数据库

图片

图片

图片

2.6 同步配置

在WEB Console中依次点击System administration -> Nodes,然后选中两个Node,点击Synchronize

图片

2.7 测试数据源

在WEB Console中依次点击Resources -> JDBC -> Data sources, 在scope中选择Cluster=hellocluster 然后选中 mysql-jdbc-source, 点击Test connection

图片

3. Java代码连接JNDI

3.1 创建Dynamic Web Project

3.2 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
	id="WebApp_ID" version="3.0">

	<display-name>WAS-02</display-name>

	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.htm</welcome-file>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>default.html</welcome-file>
		<welcome-file>default.htm</welcome-file>
		<welcome-file>default.jsp</welcome-file>
	</welcome-file-list>
	
	<servlet>
		<servlet-name>TestServlet</servlet-name>
		<servlet-class>com.freud.TestServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>TestServlet</servlet-name>
		<url-pattern>/test</url-pattern>
	</servlet-mapping>
	
	<resource-ref>
	    <description>MySQL DB Connection Pool</description>
	    <res-ref-name>jdbc/MysqlDBPool</res-ref-name>
	    <res-type>javax.sql.DataSource</res-type>
	    <res-auth>Container</res-auth>
	    <res-sharing-scope>Shareable</res-sharing-scope>
  	</resource-ref>
  
</web-app>

3.3 DbFactory.java

package com.freud;

import java.sql.Connection;
import java.sql.SQLException;

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

public class DbFactory {

	public static Connection getConnection() {
		Connection conn = null;
		try {
			Context ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MysqlDBPool");
			if (ds != null) {
				conn = ds.getConnection();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConn(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

3.4 TestServlet.java

package com.freud;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class TestServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;

	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		StringBuilder sb = new StringBuilder("Response: \r\n");
		Connection conn = null;
		try {
			conn = DbFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("SELECT ID, USERNAME, PASSWORD FROM USER");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Integer id = rs.getInt("ID");
				String username = rs.getString("USERNAME");
				String password = rs.getString("PASSWORD");
				sb.append("id: ").append(id).append(", username: ").append(username).append(", password: ")
						.append(password).append("\r\n");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				DbFactory.closeConn(conn);
			}
		}
		response.getWriter().write(sb.toString());
	}
}

4. 部署并验证

4.1 导出EAR文件

导出WAS-02EAR.ear文件

4.2 部署

打开WEB Console,依次点击Application -> New Application -> New Enterprise Application

图片

档案选择WAS-02EAR.ear

图片

Map modules to servers中选择如下

图片

Map resource references to resources中选择Target resource JDNI Name为之前创建的jdbc/MysqlDBPool

图片

4.3 启动应用并验证

访问 http://192.168.62.191:9080/WAS-02/test

图片