Setup MySQL connection in WildFly and Eclipse

You need to Install JDBC Driver on your Application Server, and make sure your database is up and running properly before you start.

Configurations in WildFly

In web panel, go to Configuration -> Subsystems -> Datasources -> Non-XA, and click Add. Basically, Non-XA is generally for development environment or small systems, which does not control transaction across servers. Whereas, XA will take control of transactions across servers, which is more suitable for production environment. Once you select Non-XA and click Add button, it will prompt a list of datasource types. Here I use MySQL as an example, so choose MySQL Datasource and hit next. Then give a name and JNDI name. Click on next button and it turns to JDBC Driver. Because I’ve installed/deployed MySQL Driver, I go to Detected Driver tab, and select something like this: mysql-connector-java-5.1.39-bin.jar_com.mysql.jdbc.Driver_5_1 (don’t do the Fabric one). Next, specify the connection URL, which should include your database name. Also fill out the database username and password, and leave security domain blank. Then go next and test connection. If everything goes right, it should return success. Click finish to store this datasource.

Setup DB Development Connection in Eclipse

First, open Eclipse and switch to Database Development Perspective. In the Data Source Explorer panel, right click on Database Connection -> new. Choose MySQL and hit next. Click on New Driver Definition icon and select the corresponding version of your downloaded connector JAR file. Go to JAR List tab and Clear All the Driver files. Click Add JAR/Zip and select the JAR file you downloaded. Go to Properties and specify Connection URL, Database Name, Password and User ID. Leave Driver Class as it is. Click OK. You should now see everything setup in the previous window, and click on Test Connection. If something gets wrong, compare the to the example I have here.

Database: Your database name
URL: jdbc:mysql://localhost:3306 (by default)
Username: database username
Password: database password
Save password: checked

Click finish to store the configures. (You can hit next to review your settings, but there is nothing to change.) You will see your new database connection listed under Database Connections. Right click on it and connect to database. Database tables are listed under Connection Name/Database Name/Schemas/Database Name/Table. To run a SQL, right click on the connection name to open SQL scrapbook. In SQL Scrapbook, select connection name (database type will be auto selected once you select a name), and then select database. Then you can write and execute any SQL in this scrapbook by right click on any line of your SQL. You can execute all, or few selected lines.

* I find if you use oracle database, you can integrate Beaver DB which is in Eclipse Marketplace in order to create tables and constraints in GUI. In my personal experience, to build a new project, I usually start with creating tables in database, then generate Entity Beans in project in the first place. But if I’m going to switch database types afterward, I just use Entity Beans to generate tables in the new database, instead of writing SQL for all the different Databases.

 

Advertisements

Install Mysql JDBC Driver on WildFly

First of, I found an article which explains how to install a jdbc driver on WildFly in a fancy way. However, I’m going to introduce an easy way to do it at the end. So let’s start with the one I found.

You may want to Initialize MySQL to begin with.

Configure JDBC Driver on WildFly 8

Used technologies in the tutorial:

  • Wild Fly 8.2.0.Final (Tested on WildFly 10 and it worked)
  • Mysql Connector 5.1.33 (Tested with MySQL Connector 5.1.39 and it worked)

1. Create the jboss module

Enter in the file path ${EAP_HOME}/modules/system/layers/base and create the directories com/mysql/driver/main.

Into the folder main copy the driver library jar, and create a file module.xml as showed in the sample below

<module xmlns="urn:jboss:module:1.3" name="com.mysql.driver">
    <resources>
        <resource-root path="mysql-connector-java-5.1.33.jar" />
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

For an explanation about the meaning of the fields take a look at Jboss Module Documentation.

2. Register the module as driver with CLI.
Start the server from the directory ${EAP_HOME}/bin and excute ./standalone.sh --server-config=${server-profile}
Start the management CLI by executing ./jboss-cli.sh (if you’re using windows, you should do it in cmd. Go to ${EAP_HOME}/bin, and execute jboss-cli)
Run the the command: /subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql.driver,driver-class-name=com.mysql.jdbc.Driver)”
If the operation is successful then the message below will be shown
{"outcome" => "success"}
In addition, the code below should be added to standalone.xml

...
<driver name="mysql" module="com.mysql.driver">
    <driver-class>com.mysql.jdbc.Driver</driver-class>
</driver>
...

The easy way

Login to WildFly web console, go to Deployments -> add a managed deployment. Then you can choose any divers you would like to deploy.

How about GlassFish?

For glassfish, these is no need to “install” the driver. before starting the server up, place the driver in the directory of %{your domain folder}\lib\ext. Then you will be good to go. You can then add pool and datasource under Resources -> JDBC within web console (add pool first, then ds). There are bugs with this module in GlassFish4. You may want to do it in GlassFish3 or Payara.
P.S. It seems if you reopen IDE, and do a clean startup of the server, then it will be ok.

Initialize MySQL

This article is for initializing non-installation MySQL database, and create a database schema.

Initialize the database

  1. unzip the MySQL non-installation package
  2. use cmd to go to bin directory
  3. execute mysqld --initialize-insecure --username=root --console

If you want to put the database file to a directory other than %mysql%/data (the default one), you need to edit the file %mysql%/my-default.ini. There are comments within the file which is very clear.
P.S. I noticed mysqld --initialize-insecure --username=root --console may not work. Try mysqld --initialize-insecure --console instead. Delete ${MYSQL_HOME}/data folder if execution was previously failed.

Create database schema

  1. start up mysql by execute mysqld --console
  2. leave the window open and open another cmd window
  3. go to bin folder and execute mysql -u root -p with leaving empty when it asks the password
  4. create a database by execute create database dbname; and use dbname (use is optional)
  5. Optionally, you can change the Password by executing: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Then it’s good for eclipse or other IDE to connect. In Eclipse, go to window -> preferences -> data management -> connectivity -> driver definitions to setup the connection. The connector jar file can be downloaded at MySQL’s official website.

In my experience, the only problem I encountered was WAMP server being installed which caused me a lots hack. The solution is to change the port in %mysql%/my-default.ini file.

Notice that you may still find some deprecated MySQL official documents which is not marked as deprecated. If you want to do some research on it, start from looking into parameters of mysqld to find more.