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