Glassfish - No suitable driver for mysql when trying to connect with datasource

Question

I am trying to complete this tutorial:

https://netbeans.org/kb/docs/javaee/ecommerce/connect-db.html

Part of it is setting up a database and trying to establish a connection using datasource and connection pooling.

I did everything that is in the tutorial but when i try to run my code i get the following error:

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

Here is the code that throws it:

<sql:query var = "result" dataSource = "jdbc/affablebean">
        SELECT * FROM category, product
        WHERE category.id = product.category_id
</sql:query>

I tried to connect to the database without using connection pooling and datasource and it worked like a charm.

try {
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver registered");
    } 
    catch (ClassNotFoundException ex) {
        Logger.getLogger(SqlService.class.getName()).log(Level.SEVERE, null, ex);
    }

That means that the driver is in the right lib folder in the glassfish directory so the problem must be somewhere else.

The tutorial has a troubleshooting section where they describe that if i get this kind of error "No suitable driver found for jdbc/affablebean" that means that i do not have resource reference in my web.xml. Well... I DO HAVE ONE and here it is:

<resource-ref>
    <description>Connects to database for AffableBean application</description>
    <res-ref-name>jdbc/affablebean</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

I have been trying to solve this problem 16 hours a day for more than two day but still NO luck.

Do you guys have some idea what is wrong? I browsed all the google and stackoverflow and found similar problems but the solution they give is "Make sure the mysql driver is in the right folder on the server".

Why do i connect to the database without dataSource object but can not connect with one?

You neeed to create datasource named jdbc/affabelbean from Netbeans. — Masudul, Jun 29 '13 at 09:50
I did everything that is described in the tutorial. Seems like you dont know the answer of my question so i dont see the point of commenting — Joro Seksa, Jun 29 '13 at 09:54
@JoroSeksa Did u add mysql jdbc driver in your project libraries?? — Prabhakar Manthena, Jun 29 '13 at 10:05
Glassfish automaticaly deploy the driver. I have not added it manually. The strange thing is that i connect to the database using Class.forName — Joro Seksa, Jun 29 '13 at 10:09
I have the exact same problem in Glassfish4 but the same app it works fine in GF3. So it could easily turn out that it is a bug in GF4. What app server are you using? — Cleric, Aug 15 '13 at 10:06
The link for the tutorial helped me. I didn't know that it is necessary to add New -> Glassfish -> JDBC Resource to project. — jmmk, Sep 01 '16 at 07:20

score 3 · Answer 1 · answered Jul 02 '15 at 19:26

So i found the solution to the exact same problem I was having. So it appears that the issue was within web.xml not containing a reference to the datasource. Simply add,

<resource-ref>
    <description>AffableBean DataSource</description>
    <res-ref-name>jdbc/affablebean</res-ref-name>
    <res-type>javax.sql.ConnectionPoolDataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

to your web.xml file and save and run file(right click inside the file and press "Run File").

Afterwards you should see the database tab pop up in your browser of choice.

score 2 · Answer 2 · answered Jul 31 '14 at 09:11

I had same error,

the problem is on when you set resource-type:

Resource Type: javax.sql.ConnectionPoolDataSource

try using:

Resource Type:=javax.sql.DataSource

thats strange in tutorial text is (the problem):

Click Next. In Step 5, Add Connection Pool Properties, specify the following details: Datasource Classname: com.mysql.jdbc.jdbc2.optional.MysqlDataSource Resource Type: javax.sql.ConnectionPoolDataSource Description: (Optional) Connects to the affablebean database

but on screenshot is correct.

Pozitron · Answer 3 · 2015-02-20T06:47:07.567

I had several issues with this https://netbeans.org/kb/docs/javaee/ecommerce/connect-db.html part of ecommerce tutorial.

First problem (no suitable driver fourd) appeared due to improper jdbc resource creation - i used 'new file \ glassfish \ jdbc connection pool' instead of 'new file \ glassfish \ jdbc resource'.

Redoing this step registered driver correctly and created pool (was indicated in glassfish servel log that mysql driver did register on server instance).

After that i couldn't get data via testDataSource.jsp - error was org.apache.derby.client.am.SqlException: Table/View 'CATEGORY' does not exist.

I couldn't figure out why org.apache.derby.client was even mentioned and after some lookup found discussion on netbeans forum: https://forums.netbeans.org/ntopic61746.html

This part of discussion was solution for my case:

try to change in step setup file sun-resources.xml or glassfish-resources.xml Setting up a JDBC data source and connection pool at step 6 -> change Resource Type to javax.sql.ConnectionPoolDataSource

and change in step setup file web.xml Referencing the data source from the application at step 4 -> change Resource Type to javax.sql.ConnectionPoolDataSource

p.s. i'm using netbeans 8.0.2, glassfish 4.1 and jdk1.7.0_21. There was no javax.sql.ConnectionPoolDataSource option in Netbeans' interface for 'edit resource reference \ resource type' combobox for web.xml. So i put this value there manually.

Thanks for the feedback, Joro. That's my very first post here. — Pozitron, Feb 20 '15 at 19:21
mine still don't work... it still gives "javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for jdbc/affablebean" " I'm using glassfish 4.1.1 — Qing Song, Jun 16 '16 at 03:14

score 1 · Answer 4 · answered Sep 22 '16 at 03:47

After a very tiresome research, having checked servers tomcat, wildfly,and Jboss, none seemed to work,I had problem with registering new datasource with glassfish, adding new jdbc connectionpool would throw java.lang.runtimeexception, the workaround for this issue was to reconfigure DerbyPool under jdbcconnection pools in admin console and feeding it with required Datasource Classname, url,username,and password to point it the database on mysql server.and it did work.

score 0 · Answer 5 · answered Jun 29 '13 at 10:12

0

I think you did not add Mysql JDBC Driver in your project libraries.

Add manually Mysql JDBC Driver in your project libraries and try again.

I think it will work.

answered Jun 29 '13 at 10:12

Prabhakar Manthena

2,223
3
16
30

score 0 · Answer 6 · answered Aug 16 '13 at 12:16

I have the exactly problem, and even thru Netbeans IDE, the DB connection test is fine while in Glassfish 4, it is not working, try all possible like include lib in project, put here and there. Finally the problem is fixed by remove Glassfish and install Glassfish 3.1.2.2, the project files is exactly same and working fine. Guess this is a bug or specail setting may needed in Glassfish 4.

score 0 · Answer 7 · answered Aug 03 '16 at 00:54

0

I had the same issue. The last 4 hours I was looking for a solution and it was so easy for me...

Make sure the "Status" in JDBC Resource (at Glassfish Admin Console) is Enabled (checkbox is checked) :-(

answered Aug 03 '16 at 00:54

crossoverlw

1

score 0 · Answer 8 · answered Dec 01 '16 at 15:54

Same problem here. I spent hours with this, tried some of the solutions offered here, and only one got me a step further: changing the Resource Type from "javax.sql.ConnectionPoolDataSource" to "javax.sql.DataSource. I immediately stumbled upon the next problem: the table "category" didn't exist. It actually does exist. I gave up on glassfish (this was my first time trying to use glassfish) and went back to Tomcat. Succes! So I decided it might be helpful to point people to another possible solution that worked for me. For those who are interested in the Tomcat solution, visit this page http://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#MySQL_DBCP_Example. Very clear explanation with an example. And don't forget to add the JSTL library to your project. Good luck.

Please consider briefly describing the solution from the link, so that if the link gets broken, the answer still holds value. — Roman, Dec 01 '16 at 15:58

Glassfish - No suitable driver for mysql when trying to connect with datasource

8 Answers8

Linked