[JAVA] DataSource connection from WebSphere to MySQL (DataSource property change?)

When I created a DataSource definition from WebSphere Application Server (WAS) to MySQL, I found something interesting with the custom properties of DataSource, so I investigated it.

Create DataSource and test connection

WAS supports JDBC connections that comply with the JDBC4 driver [^ 1]. So let's do it right away.

Creating a JDBC provider

A JDBC connection (DataSource) consists of the following three elements.

1. J2C authentication data
2. JDBC provider
3. JDBC data source

Set in order.

Creating J2C authentication data

WAS can securely (?) [^ 2] save database-connected users as J2C authentication data. To the extent that it is clearly stated, holding the user name and password as J2C authentication data enhances interoperability. Does it mean that the effect of changing the password of the DB access user can be absorbed by the J2C authentication data and does not affect the application (Is it possible for a server-side application to embed the password in the source or property nowadays? ). [^ 2]: I don't see any mention of it, but I'm sure it does encryption. Even the Liberty Profile can be encrypted (xor or AES128).

The JDBC driver is downloaded from the Official Site. We will proceed on the premise of "Connector / J 5.1.45" which is the latest at the time of writing this article.

Log in to the WAS management console and open "Security"-"Global Security"-"Java Authentication / Authorization Service"-"J2C Authentication Data". "New Press "Create". Any Enter the alias, DB connection user ID and password, and press "OK". Server level Will create the authentication data.

Creating a JDBC provider

The JDBC provider is a function that loads the JDBC driver. Just specify the driver path for the settings. Open "Resources"-"JDBC"-"JDBC Provider", set "Scope" to the server, and "Create new". "Database -Set "Type" to "User-defined", enter " com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource "[^ 3] in" Implementation class name ", enter an arbitrary name and description, and go to" Next ". I will. "Class Enter " $ {MYSQL_JDBC_DRIVER_PATH} /mysql.jar "in" Path "and go to" Next ". Is it a good practice to define the driver path as a WebSphere variable for portability? One thing to keep in mind when placing the driver Jar file is that if you make a mistake, do not put it under "/ lib" or "/ lib / ext" in the WAS directory. If you place it here, the driver will be loaded from the class loader when the server starts, and it will operate in duplicate when creating the DataSource. Also, suppose you leave the "jar" file here and at one point you change your mind and put the driver back in another directory. With the driver update, even if you replace only the latter file, the class loader at the time of bootstrap will load the left-behind "jar", and you will end up using the old driver. Hmm. In order to avoid such troubles and to prevent it from being overwritten or erased by fix packs etc., it seems wise not to place it under the product directory. This time, place it as "/home/piro/mysql.jar". [^ 3]: As summarized in the appendix, the implementation class name for XADataSource is "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource".

Open "Environment"-" WebSphere Variables", set "Scope" to Server, and "Create New". Variable name Enter the value and click "OK" to "Save".

Create DataSource

Create a data source in the same way. Any Enter the data source name and PNG name and go to "Next". When looking up from a JNDI name application, is it common to do a lookup ("java: comp / env / jdbc / mysqlmaster") with the ENC name? ([References](https: // www) .ibm.com/developerworks/jp/websphere/library/was/was_jndi/2.html)). JDBC Provider Specify the "MySQLConnectionPoolDataSource" set earlier in and go to "Next". "Data -The unfamiliar term "store helper class name" came out, but I will continue to "next" as it is (default setting). The helper class of DataSource seems to be an extension of WAS, and the one for the famous RDBMS is provided by WAS. But there is no MySQL. If not, you can make it yourself It seems. As far as I can see, it seems to define some kind of error handling unique to the RDBMS engine. Also specify the DB connection user set earlier from the J2C authentication alias and go to "Next" (I also enabled it for container management persistence because it may be used in an experiment someday). "End To "save" the configuration.

Connection test

Now that the DataSource is complete, why don't you try equipping it? ~~ Try to connect. Check and click "Test Connection". Well, you can't connect. If you connect with this, what on earth did you connect to which database! ?? Because it will be. The wizard didn't have any place to specify the MySQL URL. You can specify in the wizard that this is Oracle or Db2. This time it's "user-defined", that is, everything else, so you won't be asked for the URL. You have to define it yourself as a custom property of the DataSource.

So, when I open the properties of DataSource. .. .. There are a ridiculous number of properties. Not so much in the manual. .. ..

Explore the origin of custom properties

And so far is the introduction. that? What I thought was that among these 239 properties, there are "URL" and "url". Why are there two? Which one should I use? It will be.

It doesn't matter if you answer. Either way, if you enter the correct URL (eg " jdbc: mysql: // hostname: 3306 / dbname "), the test connection will succeed (the helper class in the example is the default generic class). There is a warning that it will remain, but let's go through).

I was wondering where the properties that are not mentioned in this manual are taken from. It must have been taken from the driver itself, but how on earth? Java beginners wondered. When I imported the whole "jar" file of MySQL Driver / J into Eclipse, it provided a method called ʻexposeAsXML ()[^ 4], so I was able to fetch the properties, but unfortunately "URL" Neither "neither" nor "url" appears, and I don't think all JDBC-compliant drivers provide such methods in the first place. [^ 4]: I just chased the source ofMysqlConnectionPoolDataSourceand implemented it in some class, but I lost the memo about what kind of inheritance relationship it was and I could not understand it. .. .. So, as a result of continuing the barren effort to find something like that from the [Java API docs](https://docs.oracle.com/javase/jp/8/docs/api/),BeanInfo I found a class with a nice name, `and wrote the following code.

GetJDBCProperty.java

package net.mognet.devel;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;

class JDBCProperty {
	public static String exposeAsHTML(Object obj) {
		StringBuilder sb = new StringBuilder();
		BeanInfo bi = null;
		PropertyDescriptor[] pd = null;
		
		sb.append("<html>\n");
		sb.append("<head>\n");
		sb.append("<title>" + obj.getClass().getName() + "</title>\n");
		sb.append("<style> table { border-style: solid; border-collapse: collapse; border-width: 1px; padding: 1px 1px 1px 1px ; } </style>\n");
		sb.append("</head>\n");
		sb.append("<body>\n");
		sb.append("<h1>Properties of " + obj.getClass().getName() + "</h1>\n");
		sb.append("<table border>\n");
		try {
			bi = Introspector.getBeanInfo(obj.getClass());
			pd = bi.getPropertyDescriptors();
			sb.append("<tr><th>name</th><th>displayName</th><th>type</th><th>shortDescription</th><th>value</th><th>getter</th><th>setter</th><th>toString</th></tr>\n");
			for(int i = 0; i < pd.length; i++) {
				sb.append("<tr>");
				sb.append("<td>" + pd[i].getName() + "</td>");
				sb.append("<td>" + pd[i].getDisplayName() + "</td>");
				sb.append("<td>" + pd[i].getPropertyType().getName() + "</td>");
				sb.append("<td>" + pd[i].getShortDescription() + "</td>");
				sb.append("<td>" + (String) pd[i].getValue(pd[i].getName()) + "</td>");
				Method m = pd[i].getReadMethod();
				if(m != null) sb.append("<td>" + m.toGenericString() + "</td>");
				else sb.append("<td>n/a</td>");
				m = pd[i].getWriteMethod();
				if(m != null) sb.append("<td>" + m.toGenericString() + "</td>");
				else sb.append("<td>n/a</td>");
				sb.append("<td>" + pd[i].toString() + "</td>");
				sb.append("</tr>\n");
			}
		} catch(IntrospectionException e) {
			e.printStackTrace();
		}
		sb.append("</table></body></html>");
		return sb.toString();
	}
}
public class GetJDBCProperty {
	public static void main(String[] args) throws TransformerException, ClassNotFoundException, InstantiationException, IllegalAccessException {
		Class<?> cls = Class.forName(args[0]);
		Object obj = (Object) cls.newInstance();
		System.out.print(JDBCProperty.exposeAsHTML(obj));
	}
}

We have also implemented the XML version and CSV version, but since the XML version is particularly hard to see, only the HTML version will be posted. main () seems to be exhausted, but just specify an appropriate class name in the first argument (pass the classpath of the other party as well). For the time being, the name is GetJDBCProperty, but it doesn't matter if the person you specify is a Bean type. So, when I tried using this, I was told that I was able to get most of the properties as a result (both "URL" and "url" came out).

Execution example

$ java -jar -cp <path_to_driver.jar>:. hoge.jar com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource > mysqlConnectionPool.html

appendix

JDBC implementation class name and URL

By the way, I tried to summarize the JDBC implementation class name and URL syntax in various RDBMS. If you arrange them in this way, Oracle's uniqueness (packaging rules and how to write URLs) stands out.

For your reference, the result of executing each JDBC driver (ConnectionPoolDataSource implementation class) is also for reference (it cannot always be used as a property of DataSource). com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

com.ibm.db2.jcc.DB2ConnectionPoolDataSource

com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource

oracle.jdbc.pool.OracleConnectionPoolDataSource