Glassfish - No suitable driver for mysql when tryi

问题:

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?

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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.

回答5:

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.

回答6:

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.

回答7:

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) :-(

回答8:

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.