Monday, December 14, 2009

Mysql Connection Pool in GlassFish 2.x.x

As a followup to a previous post, regarding the creation of an oracle connection pool in GlassFish 2.1.1, we will create a MySQL connection pool.

First of all, we need to download from www.mysql.com the latest jdbc driver:

http://dev.mysql.com/downloads/connector/j/5.1.html

Extract the downloaded archive and take the mysql-connector-java-x.x.x-bin.jar from the compressed archive and put in {glassfish}/lib directory and restart glassfish:

asadmin stop-domain domain1
asadmin start-domain domain1

Now we need to navigate to the administrator application:
http://localhost:4848/
 
Open the 'Resources' -> 'JDBC' -> 'Connection Pools' and select 'New.
Fill in the Connection Pool 'Name'.
Select 'Resource Type' : 'javax.sql.DataSource'.
Select 'Database Vendor' : 'MySQL'.
Select 'Next'.

At the next page go down at the additional Properties.
Find and edit the following properties :
Select 'Name' : 'User' and 'Value' : {db_user}
Select 'Name' : 'Password' and 'Value' : {db_user_pass}
Select 'Name' : 'URL' and 'Value' :
jdbc:mysql://[host]:[port]/[database name]
Select 'Finish'.

Navigate to Connection Pools and select the pool you just created.
Click Ping to test that it is working. If it is not, it is probably because you have mistyped the connection credentials. Go to 'Additional Properties' tab of the connection pool detail page and correct any errors. Try ping again.

Open the 'Resources' -> 'JDBC' -> 'JDBC Resources' and select 'New.
Fill the JNDI Name like 'jdbc/myconnnection'.
Select from 'Pool Name' combo box the pool we just created.
And select 'OK'.

The Connection Pool is ready!


p.s.
You can find me on fiverr for more personalized requests on any java app server configuration problem or java error that you encounter, with deliverance of less than a day (true!) and money back guarantee if not satisfied.

32 comments:

  1. hi...Thnk for the tutorial.I want to use php and mySQL in netbean (glassfish server)..How can i configure php and mySQL..thnx...

    ReplyDelete
  2. My friend azmutally,
    I have never tried php, mysql, netbeans and glassfish combination before. If you are still interested, I could spare some time to make a quick tutorial.

    ReplyDelete
  3. Hey Mario..

    Thanks a lot for this post.. it was really helpful.. flawless..

    Thanks once again...

    Regards,
    Ullas

    ReplyDelete
  4. I am using jsp and mySQL in netbean (glassfish server)..i am already connected to a database.... how will i use data from another database in a jsp program??

    ReplyDelete
  5. My friend bipin, I am afraid I don't understand your question. Could you make it more clear so that I can help you?
    You have run your project and it has already connected successfully to your mysql db?
    And you are trying to connect to another database? another mysql db or oracle db or postgre db?

    ReplyDelete
  6. Hello,

    I am on Win Vista. Have Glassfish 2,1 & 3v prelude, MySQL Server 5.1 & downloaded connector/j - mysql-connector-java-5.1.13-bin.

    I can connect my jsp/servlet to mysql using normal Class.forName. I am trying to connect thruough ConnectionPool. I don't know & am not able to find which glassfish version is running. I copied mysql-conn..jar file to
    1)C:\Program Files (x86)\GlassFish\glassfish-v3-prelude\glassfish\lib
    2)C:\Program Files (x86)\GlassFish\glassfish-v3-prelude\glassfish\domains\domain1\lib\ext
    3)C:\Program Files (x86)\glassfish-v2.1\lib

    Added 3 of them to classpath. Also added 1 & 3 to System Classpath in JVM Setting of the App server. Restarted Server, computer every time required.

    ConnectionPool Settings -
    Name: SurveyDBPool
    Datasource Classname:
    Vendor-specific classname that implements the DataSource and/or XADataSource APIs
    Resource Type: javax.sql.DataSource

    Properties :
    databaseName - surveys
    portNumber - 3306
    URL - jdbc:mysql://localhost:3306/surveys
    serverName - localhost
    user - root
    password - root

    With all this also, when I Ping, I get
    "Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource " error.

    Can you help me how to solve it. I surfed a lot & tried almost every possible trick I found on net.

    Your help will be highly appreciative.

    Thanks

    Trupti

    ReplyDelete
  7. Hello,

    I am on Win Vista. Have Glassfish 2,1 & 3v prelude, MySQL Server 5.1 & downloaded connector/j - mysql-connector-java-5.1.13-bin.

    I can connect my jsp/servlet to mysql using normal Class.forName. I am trying to connect thruough ConnectionPool. I don't know & am not able to find which glassfish version is running. I copied mysql-conn..jar file to
    1)C:\Program Files (x86)\GlassFish\glassfish-v3-prelude\glassfish\lib
    2)C:\Program Files (x86)\GlassFish\glassfish-v3-prelude\glassfish\domains\domain1\lib\ext
    3)C:\Program Files (x86)\glassfish-v2.1\lib

    Added 3 of them to classpath. Also added 1 & 3 to System Classpath in JVM Setting of the App server. Restarted Server, computer every time required.

    ConnectionPool Settings -
    Name: SurveyDBPool
    Datasource Classname:
    Vendor-specific classname that implements the DataSource and/or XADataSource APIs
    Resource Type: javax.sql.DataSource

    Properties :
    databaseName - surveys
    portNumber - 3306
    URL - jdbc:mysql://localhost:3306/surveys
    serverName - localhost
    user - root
    password - root

    With all this also, when I Ping, I get
    "Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource " error.

    Can you help me how to solve it. I surfed a lot & tried almost every possible trick I found on net.

    Your help will be highly appreciative.

    Thanks

    Trupti

    ReplyDelete
  8. Hey Trupti,
    As fas as I can tell, first of all you need to check which glassfish is running. When you log in the we b administrator, you can see in the top left the version of the server you are administering. Next, you don't need to restart the computer every time you start the server. All you need to do is open a command window --> cd C:\Program Files (x86)\glassfish-v2.1\bin --> and run the following command 'asadmin stop-domain domain1' (or the name of the domain you have created, which you can tell by the name of the directory under C:\Program Files (x86)\glassfish-v2.1\domains\). To start the server run 'asadmin start-domain domain1'.
    I presume you are administering the v3 glassfish.
    I will suggest that you check the 'Datasource Classname' to say MySQL or something at the first step of the wizard.
    Tell me if it works.

    ReplyDelete
  9. Trupti,
    you could also check here http://old.nabble.com/MySql-connector-problems,-Class-name-is-wrong-or-classpath-td21833176.html. Check the last post, it is important. Hope this helps.

    ReplyDelete
  10. And lastly, I think that GF3 needs the driver jar to be put in ${GLASSFISH_HOME}/glassfish/modules and then restart the server. Try that as a last resort.

    ReplyDelete
  11. Hi Komarios,

    Yes you are absolutely correct. I am runnign my app via NetBeans 6.5. With NetBeans Glassfish v2 is running and on my pc, Glassfish 3v prelude is auto started via J2EESDK.

    But now, I stopped glassfils v3 and started v2, set up all database & pool setting and got it ping at once. Added mysql-conn jar file in server JVM path System Classpath. Stopped & started the server. But yet when I run the app from netbeans, I get excep on "jdbc/.." it says NameNotFoundException:SurveyDBPool not found.
    The configs are done in web & sun-web.xml's also.
    The code I use to get the connection is :

    // This was seperated to find where the error is lying.
    InitialContext ic = new InitialContext();
    ctx = (Context) ic.lookup("java:comp/env");

    //DataSource ds = (DataSource) ctx.lookup("jdbc/SurveyDBPool"); // java:comp/env/jdbc/SurveyDBPool

    DataSource ds = (DataSource) ctx.lookup("jdbc/SurveyDBPool");
    conn = ds.getConnection();

    Excep is thrown at ctx.lookup.

    Has it anything to do while running with netbeans. B'coz netbeans starts & stops server auto. How can I work with netbeans and how to deploy on Glassfish.

    What can be the problem yet. Any ideas. Your help is and will be highly appreciated.

    Thanks

    ReplyDelete
  12. trupti,
    Since you are using NetBeans with GlassFish things are a little different. The GlassFish installation has two important directories. The first is the binaries installation in Program Files and the other is the NetBaans instance's configuration files under Documents and Setting/user/.netbeans or something similar.
    There two ways I can think of you can fix it. Either install the library inside the latter directory under directory lib (wherever you find it). Or customize the server from inside NetBeans and adding the mysql jar.
    And I just remembered that when I took my first baby steps in NetBeans I had achieved it by adding a MySQL custom library with the MySQL jar only, and I just added it to my project. From there everything run smoothly!
    Cheers.
    p.s. I apologize for my vagueness in paths. It's been years now that I am using linux.

    ReplyDelete
  13. hi komarios


    i made a connection pool just like u did say but i can't get connect to de database...

    i'm using MySQL, JSF, GlassFish all in netbeans and i got this error

    java.sql.SQLException: Error in allocating a connection. Cause: null

    Can you help me please??

    ReplyDelete
  14. Anonymous Friend,
    You need to give a little more about your connection pool configuration.
    But you could try the "ping" functionality from the GlassFish Administration Console, as described on this tutorial.
    Did it succeed? I presume that your connection details have a mispelling.
    cheers

    ReplyDelete
  15. hi!! is Anonymous Friend again hehe

    it is working now, the problem was with the connection pool that i was using in the aplication but thanks anyway.

    thinking well I have many question about client-server aplication can i ask you all them?? hehe or a few??.

    see u.

    ReplyDelete
  16. Anonymous Friend,
    I would be glad to help you as much as I can.
    So send over your questions!
    cheers

    ReplyDelete
  17. hi komarios is Anonymous Friend again


    Man can you help me??

    i need to full an ComboBox from a Table using JSF, JPA, Mysql.

    Thanks..

    ReplyDelete
  18. you can google (jpa combo box) it. The first result was this:
    http://ikennaokpala.wordpress.com/2010/05/30/populating-jsf-combo-box-with-database-values/
    hope it helps.

    ReplyDelete
  19. This helped! Thanks dude..

    ReplyDelete
  20. Thanks it helped

    ReplyDelete
  21. hey i'm working on mac and want to access to my localhost database...
    i did everything what you said.. particularly the jvm classpath setting and copying the jdbc connector in the folders... but he says all the time:
    Ping Connection Pool for bookstore is Failed. Ping 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.


    What can i do?

    ReplyDelete
  22. Anonymous friend,
    All I can think of is that you did not restart your Glassfish server after putting the mysql jar file in lib directory.
    Or you put it in a totally different directory.
    It is obvious that the server cannot find the Driver inside this jar file.
    If that doesn't help. I would suggest to redo this walkthrough from the beginning to end ( taking into account every little detail ) and if the error persists post to me again.
    Cheers mate

    ReplyDelete
  23. hey can anyone help me with this:

    Ping Connection Pool for hope is Failed. Ping failed Exception - Connection could not be allocated because: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Please check the server.log for more details. Ping failed Exception - Connection could not be allocated because: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Please check the server.log for more details.

    thanks a lot!!!!

    ReplyDelete
  24. Well komarios,
    I did exactly as u said and i am working with netbeans and glassfish 3.x but i get this problem komarios 'Class name is wrong or classpath is not set for .......' How can i solve this problem? Can u help me?

    ReplyDelete
  25. Error An error has occurred
    Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

    How can I solve it?

    Thanks!

    ReplyDelete
  26. You could check again if the folder you have put the library in, is correct. You could also check that you have restarted GlassFish after placing the mysql library in GlassFish. Generally, I would suggest that you follow the tutorial to the letter and you will succeedd.
    In any other case, you can post me in detail all the steps you have taken, and I'll try to spot the problem.

    ReplyDelete
  27. Hi i m trying to connect and i m getting an error similar to trupti's post

    now i have copied the jar files but i dont know how to set the class path names pls help me

    ReplyDelete
  28. Could you give a little more information? What is the error message. What steps you have done. The versions you have used.
    And I'll try to help you.

    ReplyDelete
  29. Thanks man,it works!

    ReplyDelete
  30. Hello marios, can help me with glassfish ..

    my pool connection is ok but when i put this statement


    SELECT * FROM proxies_priv;


    the browser give me this error

    exception

    javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for jdbc/myData"
    root cause

    javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for jdbc/myData"

    ReplyDelete
  31. Hi,
    The error is stating that glassfish can not find the DB Driver.
    Please follow the instructions on this post carefully (e.g. restart glassfish after mysql jar is put in lib directory).
    And if the problem persists, send me the error and the steps to reproduce the error.
    Cheers.
    p.s.
    If you need your problem solved in less that 24 Hours, you can find me in fiverr:
    https://www.fiverr.com/marios_debug/solve-configuration-problems-in-java-app-servers

    ReplyDelete