Featured Posts

Integrating GlassFish Application server with Apache... A typical production topology for GlassFish will be a front ending GlassFish with Apache for serving the static files. To integrate GlassFish Application Server with Apache web server follow the below...

Readmore

Some Java, JEE and WebSphere stuffs Rss

Creating and configuring a MySQL DataSource in GlassFish Application Server

Posted by Albin Joseph | Posted in GlassFish | Posted on 06-08-2008

23

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 5.00 out of 5)
15,731 views

Creating and configuring a MySQL DataSource in GlassFish Application Server.

Follow the below steps for creating and configuring a MySQL DataSource in GlassFish application server.

1. Download MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/3.1.html

2. Extract the contents of the zip file

3. Copy mysql-connector-java-x.x.x-bin.jar to GLASS_FISH_INSTALL_DIR\lib folder.

3. Start your GlassFish Application server by issuing the command ‘asadmin.bat start-domain domain1′ from GLASS_FISH_INSTALL_DIR\bin directory.

4. Login to GlassFish admin console. The default url for GlassFish admin console will be http://localhost:4848/login.jsf. The default username and password for accessing the admin console will be admin and adminadmin respectively.

5. From Common Task menu expand Resources menu by clicking on Resources menu.

6. Expand JDBC under resources.

7. Click on ‘Connection Pools’ under JDBC menu. The Connection Pools page will be displayed.

8. Click on New.

9. Enter a name for your JDBC Connection pool. Select javax.sql.ConnectionPoolDataSource as your ‘Resource Type’ and select MySQL as your ‘Database vendor’.

10. Click on Next.

11. Fill the details according to your need. Minimum you need to fill the following fields.
a. DatabaseName
b. Password
c. URL (The format will be jdbc:mysql://localhost:3306/test. Where test is your database name.)
d. Url (The format will be jdbc:mysql://localhost:3306/test. Where test is your database name.)
e. ServerName
f. User

12. Click on Finish. You will be taken back to the Connection Pools page.

13. Click on the Connection Pool you created. You will be taken to ‘Edit Connection Pool’ page.

14. Click on Ping. If your connection pool is setup correctly you will get a Ping Succeeded message.

15. Now click on JDBC Resorces under JDBC menu.

16. Click on New.

17. Enter a JNDI Name for your data source. Select the pool you created by following the above steps as your ‘Pool Name’.

18. Click on OK. You are done.

To obtain a connection using the above DataSource, use the following code.

	Context ctx = new InitialContext();
	DataSource ds = (DataSource) ctx.lookup("jdbc/mysqltest");
	Connection con = ds.getConnection();
  • Share/Bookmark

Read More

Comments (23)

FAN-TAS-TIC :)

nice

great and very helpful thanxxxx :-)

very useful, thx!

many many many thanks
cool you said it

it worked. i did get a 500 error when setting up the connection pool. I used a work around for that. dont specify the vendor type and kind of resource type, just give a name and click next. thi way you wont get the 500 error. now specify all values yourself manually on the next page. remember all the info like username, password, url etc will have to be added youse the add property feature in the advanced tab now.

THanks!!
- Sandeep

thanx a lot, very helpful

I have done all the steps as shown in the web site. But am getting the following error.
Could anyone give me the solution.Thanks in advance.

Exception in thread “main” javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial

Are you trying to execute the code from a stand alone java application with main? If yes can you try it from a JSP? main method will not a context.

Hi Albin,
I created a context in one method and called that method in mail as shown below.

public String getResults(String uName,String passWord) throws Exception
	{
 
		Connection conn = null;
		String myResult = null;
		try
		{
			Context ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("sekar");
			 conn = ds.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from employee_syntel where name = '" uName "' and password = '"  passWord);
 
			if (rs!=null) {
				myResult = "valid";				
			}
		}
		catch (SQLException e)
		{
			conn.close();
		}
		return myResult;
	}
	public static void main(String[] args) throws Exception {
		EmployeeDAO emp = new EmployeeDAO();
		String name = emp.getResults("admin", "admin");
		}

Can u give me a solution for this. Thanks in advance.

[...] http://www.albeesonline.com/blog/2008/08/06/creating-and-configuring-a-mysql-datasource-in-glassfish... (per la creazione della connessione JDBC su Glassfish – attenzione dice di scaricare i driver jdbc 3.1 mentre io ho scaricato i 5.1) [...]

Thanks very much Albin for the tips on how to set up a MyQSL connection pool for Glassfish. It was very very helpful. :mrgreen:

Just one thing though – If you are getting an error message that says something like:

Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

Try putting the MySQL Driver (mysql-connector-java-x.x.x .jar) in the domain director in the SDK folder:

C:\Sun\SDK\domains\domain1\lib\ext

Or whichever domain folder for your Glassfish server is using.

I got that problem solved with help from Gerald’s Blog

http://www.innoq.com/blog/gs/2007/10/howto_get_jsasglassfish_with_m.html

Thanks

Thank you for this guide Joseph.It was a week that I’m strugglin’ with the configuration of a data source in a web app made with Tomcat 5.5 and im using Glassfish.Now i resolved my problem.God bless you!

Hello!

Thank you for this guide it was very useful for me!! Now I would like to contribute with this achievements:

I have been researching on making some configuration tools for configuring new resources programatically in a Glassfish and it was difficult to find good examples over the internet. For this reason, I think this code could be helpful. These examples were created to configure datasource resources in the AppServer. For creating them we need three objects, connection-pool, jdbc-resource and resource-ref. I explain the main steps to achieve the challenge.

Getting all the objectNames from the AppServer.

Management remoteHome;
Set objectNames = remoteHome.queryNames(new ObjectName(""), null);

Taking attributes from the managementObjects, once you have the objectName. .
This example is for accesing the ConnectionPool attributes for other remoteObjects it will be the same structure:

this.objectName = objectName;
this.remoteHome = remoteHome;
// Takes the attributes from the datasource-connection-pool
try {
String[] attributes = {"allow-non-components-callers", "associate-with-thread", "connection-creation-retry-attemps","connection-creation-retry-interval-in-seconds","connection-leak-reclaim","connection-leak-timeout-in-seconds", "connection-validation-method","datasource-classname","description","fail-all-connections","idle-timeout-in-seconds","is-connection-validation-required","is-isolation-level-guaranteed","lazy-connection-association","lazy-connection-enlistment","match-connections","max-connection-usage-count","max-pool-size","max-wait-time-in-millis","name","non-transactional-connections","pool-resize-quantity","res-type","statement-timeout-in-seconds","steady-pool-size","transaction-isolation-level","validate-atmost-once-period-in-seconds","validation-table-name","wrap-jdbc-objects"};
 
connectionPoolProperties = remoteHome.getAttributes(objectName, attributes);
 
datasourceClassname = (String) remoteHome.getAttribute(objectName, "datasource-classname");
connectionPoolName = (String) remoteHome.getAttribute(objectName, "name");
 
 
Creating Datasource objects (other Management Objects should have similar "create" methods) .
 
DomainRoot domainRoot;
 
AppserverConnectionSource appserver = new AppserverConnectionSource(
AppserverConnectionSource.PROTOCOL_RMI, hostName, JMX_PORT,
user, password, null);
 
domainRoot = appserver.getDomainRoot();
 
// check whether the con.pool already exists, else, create one
Map conPoolMap = domainRoot
.getDomainConfig().getJDBCConnectionPoolConfigMap();
if (conPoolMap.containsKey(connPoolName)) {
logger.fine("JDBC Connection pool : " + connPoolName
+ " already exists");
report.append("JDBC Connection pool : " + connPoolName
+ " already exists \n");
// domainRoot.getDomainConfig().createJDBCConnectionPoolConfig(connPoolName,
// datasourceClassname, connectionPropertiesMap);
 
} else {
try {
domainRoot.getDomainConfig().createJDBCConnectionPoolConfig(
connPoolName, datasourceClassname, connectionPropertiesMap);
} catch (Exception e) {
report.append("Incorrect format of database properties, the DataSource has not been created");
return report.toString();
}
logger.fine("JDBC Connection pool : " + connPoolName
+ " created ! ");
report.append("JDBC Connection pool : " + connPoolName
+ " created ! \n");
 
 
}
// check whether the jdbc resource already exists, else, create one
Map resMap = domainRoot.getDomainConfig()
.getJDBCResourceConfigMap();
if (resMap.containsKey(resourceName)) {
logger.fine("JDBC Resource : " + resourceName + " already exists");
report.append("JDBC Resource : " + resourceName
+ " already exists \n");
} else {
domainRoot.getDomainConfig().createJDBCResourceConfig(resourceName,
connPoolName, null);
logger.fine("JDBC Resource : " + resourceName + " created !");
report.append("JDBC Resource : " + resourceName + " created ! \n");
}
// create a resource-ref for the instance "server"
Map map1 = domainRoot.getDomainConfig()
.getStandaloneServerConfigMap();
{
StandaloneServerConfig sc = map1.get("server");
Map rm = sc.getResourceRefConfigMap();
 
if (rm.containsKey(resourceName)) {
logger.fine(resourceName
+ " already referenced for the server instance");
report.append(resourceName
+ " already referenced for the server instance \n");
} else {
sc.createResourceRefConfig(resourceName, true);
logger.fine(resourceName
+ " reference created for the server instance");
report.append(resourceName
+ " reference created for the server instance \n");
}
 
return report.toString();
}

Accesing remote methods in a management object.

These methods could be found through the Jconsole. You have to connect to the jmxservice (service:jmx:rmi:///jndi/rmi://localhost:8686/jmxrmi). In the lines below there is an example of using the remote method setProperties from the amx object: X-JDBCConnectionPoolConfig.

DomainRoot domainRoot;
 
AppserverConnectionSource appserver = new AppserverConnectionSource(
AppserverConnectionSource.PROTOCOL_RMI, hostName, JMX_PORT,
user, password, null);
 
 
domainRoot = appserver.getDomainRoot();
MBeanServerConnection mBeanConnection = appserver
.getExistingMBeanServerConnection();
Set objectNames = mBeanConnection.queryMBeans(
new ObjectName(""), null);
// domainRoot.getDomainConfig().
Iterator objectNamesIterator = objectNames.iterator();
while (objectNamesIterator.hasNext()) {
ObjectInstance objectInstance = (ObjectInstance) objectNamesIterator
.next();
// System.out.println("Object Names de MBeanConnection"+
// objectNamesIterator.next().toString());
ObjectName objectName = objectInstance.getObjectName();
 
if (objectName.getKeyProperty("j2eeType") != null) {
if (objectName.getKeyProperty("j2eeType").equals(
"X-JDBCConnectionPoolConfig")) {
if(objectName.getKeyProperty("name") != null){
if(objectName.getKeyProperty("name").equals(resourceName)){
//System.out.println(objectName.toString());
 
String[] params = { key,value };
 
String[] signature = { "java.lang.String",
"java.lang.String" };
 
MBeanInfo info = mBeanConnection.getMBeanInfo(objectName);
//This is the method for invoking remote methods y dynamic Mbeans.
mBeanConnection.invoke(objectName, "setPropertyValue",params, signature);
 
techReportBuilder.append("New Property setted in " + resourceName + "Key= "+key +"value= "+value);

I hope these examples I have written above are helpful for anyone trying to develop programmatic tools to manage a Glassfish AppServer.

Thanks pakito. Thanks for the great guide.

Hello!

Now I am researching trying to do the same on GlassFish clusters but it doesn’t work. I have created the resource and the connection pool through the GUI and the “standalone-server” uses it correctly but when I try to use my JDBC resource through anyone of my clusters it is imposible. The exception is:

Error in allocating a connection. Cause: Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

I thought it was a problem of libraries but I have tried to put the libraries in each “cluster-config/lib” and it doesn’t work. I also have introduced in the classpath through the GUI in the cluster-config (JVM Settings) and it doesn’t work neither.

Do you have any ideas?? or any place or documents where I can learn how to do this? I can’t come up with other ideas.

Thank you very much!!

Pakito!!

Hi!!

I have found the fail. It was that I had to restart the instances after adding the classpath prefix in the JVM Settings in the cluster1-config. I was only restarting the full domain but that is not valid.

To sum up, for configuring a datasource in a clustered glassfish enviroment it is necessary to create the datasource (exactly as Joseph Albin has described before) and after, you have to attach them to the corresponding clusters (in the Target Tab) you have to add the path for the driver libraries in each cluster. For this, follow this steps
1º In the tree (on the left) expand the configuration (last one)
2º Go to “cluster1-config”
3º JVM Settings
4º In the top go to the path Settings Tab
5º add the pathfile where the driver is.
It is recommendable to copy the driver (.jar file) to the lib directories of each cluster, in my case: /usr/local/glassfish/domains/domain1/config/cluster1-config/lib/ext/mysql-connector-java-3.1.14-bin.jar but it still works as long as it is the correct path.

REMEMBER: after applying this changes you should restart the instances of that cluster!

Thank you!! Bye!!

Thanks Albin! :smile:

Just a quick follow-up note for newbies like me (+:

Problem:
Glassfish is unable to locate the driver even after the jar is copied into glassfish-install-dir/lib

Solution:
Navigate to Glassfish Admin Console/JVM Settings/JVM Options
Append the driver to the jdbc drivers parameter.
E.g. Djdbc.drivers=org.apache.derby.jdbc.ClientDriver;com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

:-) Outstanding!

these are very helpfull material.

Hi, it mentioned by article on adding the following to the source :

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup(“jdbc/mysqltest”);
Connection con = ds.getConnection();

Is this mean I write a jar file or where to add the code sun-web.xml or where? Thanks.

I have tried the above connection setting, but still not successful, so I thought it might be the code.

Appreciate any help. Thank you.

Write a comment