• Entries (RSS)
  • Comments (RSS)

Creating and configuring a MySQL DataSource in GlassFish Application Server

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

Tagged Under : , , , ,

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

Read More

Comments

69 comments posted onCreating and configuring a MySQL DataSource in GlassFish Application Server

  1. FAN-TAS-TIC :)

  2. nice

  3. great and very helpful thanxxxx :-)

  4. very useful, thx!

  5. many many many thanks
    cool you said it

  6. 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

  7. thanx a lot, very helpful

  8. 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

  9. 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.

  10. 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.

  11. [...] 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) [...]

  12. 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

  13. Thanks

  14. 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!

  15. 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.

  16. Thanks pakito. Thanks for the great guide.

  17. 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!!

  18. 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!!

  19. Thanks Albin! :smile:

  20. 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

  21. :-) Outstanding!

  22. these are very helpfull material.

  23. 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.

  24. I have created JDBC Data source (Connection Pool)which I want configure in Springs Hibernate to getSession Factory. What should be the factory classes, URL etc. to configure it.
    Please help me

  25. Hi..

    Please refer to step 11(e). Servername. This is glassfish server we are using right? So can I just enter glassfish for that option? Or does the Servername mean something else?? Hope someone she some light on this…Thanks

  26. It is the database server name. You can either use the name of the server or the IP of the server.

  27. I’ve followed your instruction and when I try to do a ping, I’ve this error :

    Ping failed Exception – Connection could not be allocated because: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: java.net.ConnectException: Connection refused STACKTRACE: java.net.SocketException: java.net.ConnectException: Connection refused at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156) at com.mysql.jdbc.MysqlIO.(MysqlIO.java:284) at com.mysql.jdbc.Connection.createNewIO

    Do you have an idea?

    thanks

  28. Are u behind some firewall or something. I feel it is a network issue where it is not able to connect to the server.

  29. no, I apply this on my local mac os…

  30. I’m frustrated…I’m running Glassfish v3, have version 5.1.* of the connector, and have put the connector JAR file in the [glassfish_install]\lib and [domain]\ext\lib. I keep getting:

    Ping failed Exception – Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource Please check the server.log for more details.

    Help?

  31. I also appended the driver to my jdbc drivers parameter in my Java options. No dice.

  32. @Tom. Please put the mysql jar file only under the domain directory of your GF installation as mentioned by Mark.
    http://www.albeesonline.com/blog/2008/08/06/creating-and-configuring-a-mysql-datasource-in-glassfish-application-server/comment-page-1/#comment-49770

    If still its not working, please send me the log file and let me see if I can help.

  33. How to force the connection accept useUnicode=true&characterEncoding=UTF-8

  34. Try putting the connector jar in ..\glassfishv3\glassfish\domains\domain1\lib\ext

  35. Thanks for the post, very informative and good for newbies. I’ve got a simple login jsp file that has a bean and a servlet that connects to mysql db with glassfish inside netbeans. I’ve done all the proper required steps you’ve mentioned and all others mentioned in the post, however I am getting a http 400 the requested resource is not available, I’d appreciate any thoughts on this…

  36. I strongly believe that http 400 is not because of something wrong with the connection pool. HTTP 400 is Bad Request and it would be because of Malformed URL.

  37. Try adding two new properties with name and values useUnicode =true and characterEncoding=UTF-8 under ‘Additional Properties’

  38. Hey mate, great guide!

  39. I ve read a lot, but this articole simply helped me…it`s just the best…thanks for posting it

  40. Hi all. I am getting the same as earlier posted. I get:
    Communications link failure due to underlying exception

    This only happens once in a while. there seems to be some activity before it happens, so I wondering if there is another pool timing out. What are the settings I can check or that I have to make sure are correct?

    We are using Glassfish 2.1 (March, 2009) with MySQL 5.0.77.

    Thanks,
    Michael

  41. now , got success…thanks …great doc :grin:

  42. thank you soo much… server configuration is hell.. thanks for this tutorial

  43. just too good stuff.clear and concise.
    worked all way.jus a glitch of the “classspath not set”
    but that has been already discusd.

  44. VERY NICE Saved me a DAY (or even more) :)

  45. I did the above but i get Ping failed Exception – Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource Please check the server.log for more details.

    where do i put the mysql-connector-java-x.x.x-bin.jar?any classpath to be set?

  46. que bien

  47. Thanks for your very good description!

  48. Thank You Very Much!!!! Really Helpful and got out from a big trouble of connecting to the MySQL DB

  49. Hi
    I am not able to connect to MySql. Error :P ing failed Exception – Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource Please check the server.log for more details.

    I have Glassfish v3,eclipse helios,jdk1.6,My SqlConnector5.0.8 ,
    In jdbc Connection pool i have given..
    General Settings
    Pool Name:mynikki
    Resource Type:javax.mysql.datasource
    d.vendor-Mysql
    NEXT
    I got datasource classname-com.mysql.jdbc.jdbc2.optional.MysqlDataSource
    PROPERTY
    —————–
    databaseName-mylib_db(already i created in mysql ..using TORA application).
    portNumber -3306
    serverName – localhost
    user – harish( mysql username and psw-)
    password -dkm01

    this much things.When i ping it showing error.Pls pls pls help me.I am trying the same thing for last 3 days.

    Thanks

  50. Thank you so much. This was very helpful!

  51. Great help, worked for me.

  52. Thank you Albin, this it was a lot of help for me. I could set up my Glassfish Server.

  53. Great!

  54. [...] MySQL. I found quick and precise description of this process under Albin Joseph’s website “Some Java, JEE and WebSphere stuff” and also (but in German) under Torsten Horn’s website. The major points to remember are: the [...]

  55. Thank you so much :D

  56. Thanks heaps for this – it worked a treat :)

  57. Very useful. Hats off

  58. Thank a lot my friends. fantastic topic and comments.

  59. Excellent step by step configuration. Thanks a ton :)

  60. hi,

    great blog, but i have several questions,
    1> I have confusion like glassfish provides only connection pool in glassfish, it doesn’t create database in glassfish
    2> can we create database in the glassfish itself like a wamp server so that it will not have any dependency on other database server…

    please suggest solution..

    Regards,
    Mahendra

  61. I don’t think so. May be I am wrong.

  62. Hello. Thank you very much of this blog.

    More Power!

  63. I did it but in deployement I have this error: invalid ressource

  64. Also keep in mind that the default password for root created in MySQL is blank. It can be a bit tricky to configure your DB Connection Pool w/Blank password.

  65. [...] For production I’ve configured my MySQL database in glassfish using jdni. (When you don’t know how to do this, take a look at this blog post) [...]

  66. you are the man.

    Thanks a lot. This article is very helpful.

    rgds, Alex.

  67. Gracias Sapo y la CTM!

  68. Much appreciated

  69. I am now using GlassFish 4.1 and discovered that to configure MySql jdbc pool you MUST paste the connector jar into the glassfish/domains/domain1/lib folder (Step 3). Otherwise, the pool-PING will fail along with a server log message:
    Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource.
    Hope this will be corrected in the instructions. They are generally very helpful.

Post a Comment