Create database in My SQL Server
1. Create database in My SQL server.
CREATE DATABASE database_name;
CREATE DATABASE project;
2. Create username and password to access the database.
CREATE USER ‘user_name’@’host_name’ IDENTIFIED BY ‘password’;
CREATE USER 'kamal'@'localhost' IDENTIFIED BY ‘kamal';
3. Grant permission for the user.
GRANT ALL PRIVILEGES ON database_name.* TO username@hostname IDENTIFIED BY password;
GRANT ALL PRIVILEGES ON project.* TO kamal@localhost IDENTIFIED BY 'kamal;
4. Create necessary tables.
CREATE TABLE table_name (col1 type, ….);
Configure My SQL in Glassfish Server
1. Install the Glassfish server.
2. Copy the mysql-connector-java-5.1.16-bin.jar file in the Glassfish\lib directory.
3. Restart the server.
4. Access the admin console of the Glassfish server and click on the Resources --> JDBC resources
5. Create JDBC connection pool
a. Click on the connection pools.
b. Enter the name of the pool, resource type and database vendor as shown in the picture.
c. Click next.
d. Now add additional properties as below.
e. Click on the connection pool that you created and click the ping button to verify whether the configuration is correct.
6. Create JDBC resources.
a. Enter the JNDI name and select the pool that you have created.
Access the My SQL data source which is configured in the glassfish server from servlet.
The below code is used to lookup the MySQL data source configured in the server.
//Create Initial Context
InitialContext ctx = new InitialContext ();
//Lookup the data source
DataSource dataSource = (DataSource) ctx.lookup ("jdbc/mysql");
//Create connection and statement
Connection connection = dataSource.getConnection();
Statement st = connection.createStatement();
Configure My SQL in JBOSS Server
1. Install the JBOSS server.
2. Copy the mysql-connector-java-5.1.16-bin.jar file in the default\lib directory.
3. In the deploy directory, create a xml file which should be ends with ‘-ds.xml’. Add the below code in that file.
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>mysqlds</jndi-name>
<!--<use-java-context>false</use-java-context>-->
<connection-url>jdbc:mysql://localhost:3306/project </connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>username</user-name>
<password>password</password>
<exception-sorter-class-name>
com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
</exception-sorter-class-name>
<valid-connection-checker-class-name>
com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
</valid-connection-checker-class-name>
<min-pool-size>5</min-pool-size>
<max-pool-size>50</max-pool-size>
<idle-timeout-minutes>5</idle-timeout-minutes>
</local-tx-datasource>
</datasources>
4. Restart the server.
Access My SQL data source which is configured in the glassfish server from servlet.
//Create Initial Context
InitialContext ctx = new InitialContext ();
//Lookup the data source
DataSource dataSource = (DataSource) ctx.lookup ("java:/mysqlds");
//Create connection and statement
Connection connection = dataSource.getConnection();
Statement st = connection.createStatement();
Note: If you use set use-java-context property to false, the lookup should be
DataSource dataSource = (DataSource) ctx.lookup ("mysqlds");