Inner Exception: java.sql.SQLException: No database selected
I know this question is very common among persistence users. I have visited so many threads and questions in so many forums and still don't get a solution.
I have created RESTful web services from a database (option in Netbeans 8.0.2). I also created a connection pool and JNDI for this project. The names are correct as shown here.
Using EclipseLink (JPA 2.1) by default I get this response:
GET RequestFailed RequestFailed → Status: (500) Response: {HTTP Status 500 - Internal Server Error
Exception report type
messageInternal Server Error
description The server encountered an internal error that prevented it from fulfilling this request.
exceptions
javax.servlet.ServletException: javax.ejb.EJBException
The main reason
javax.ejb.EJBException
The main reason
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Inner Exception: java.sql.SQLException: Database not thrown Error Code : 1046 Call: SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone number, profile_pic, refresh_expires, refresh_token, reg_id, token_created type, total_balance user_name, user_type FROM users Request: ReadAllQuery (referenceclass = Users sql = "SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone number, profile_piresic, refresh_exp ,refresh_token, reg_id, token_created, total_balance, type_work, user_name, user_type FROM users ")
The main reason
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: Database not selected Error Code: 1046 Call: SELECT id , access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone number, profile_pic, refresh_expires, refresh_token, reg_id, token_created, total_balance, type_work, userpe_name, user request : ReadAllQuery (referenceclass = Users sql = "SELECT id, access_expires, access_token, address, bank_account_type, created, device_id, device_type, email, facebook_id, grazie_code, latitude, longitude, password, phone number, profile_pic, refresh_expires, refresh_token, reg_created , total_balance,type_work, user_name, user_type FROM users ")
The main reason
java.sql.SQLException: no database selected
note A full trace of the exception stack and its root causes are available in the GlassFish Server Open Source Edition 4.1 logs. GlassFish Server Open Source Edition 4.1
my persistence.xml file looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="dwolla_apiPU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/dwolla</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<shared-cache-mode>ALL</shared-cache-mode>
<properties>
<property name="eclipselink.target-database" value="MySQL"/>
<property name="eclipselink.logging.logger" value="DefaultLogger"/>
<property name="eclipselink.ddl-generation" value="create-tables"/>
<property name="eclipelink.logging.level" value="FINEST"/>
<!-- JDBC connection properties -->
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/dwolla"/>
<property name="javax.persistence.jdbc.user" value="root"/>
<property name="javax.persistence.jdbc.password" value=""/>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<property name="eclipselink.jdbc.batch-writing" value="JDBC"/>
<property name="javax.persistence.schema-generation.database.action" value="create-tables"/>
</properties>
</persistence-unit>
</persistence>
domain.xml:
<resources>
<jdbc-resource pool-name="__TimerPool" jndi-name="jdbc/__TimerPool" object-type="system-admin" />
<jdbc-resource pool-name="DerbyPool" jndi-name="jdbc/__default" object-type="system-all" />
<jdbc-resource pool-name="APIConnectionPool" jndi-name="jdbc/dwolla" object-type="user" />
<jdbc-connection-pool name="__TimerPool" datasource-classname="org.apache.derby.jdbc.EmbeddedXADataSource" res-type="javax.sql.XADataSource">
<property value="${com.sun.aas.instanceRoot}/lib/databases/ejbtimer" name="databaseName" />
<property value=";create=true" name="connectionAttributes" />
</jdbc-connection-pool>
<jdbc-connection-pool is-isolation-level-guaranteed="false" name="DerbyPool" datasource-classname="org.apache.derby.jdbc.ClientDataSource" res-type="javax.sql.DataSource">
<property value="1527" name="PortNumber" />
<property value="APP" name="Password" />
<property value="APP" name="User" />
<property value="localhost" name="serverName" />
<property value="sun-appserv-samples" name="DatabaseName" />
<property value=";create=true" name="connectionAttributes" />
</jdbc-connection-pool>
<jdbc-connection-pool is-isolation-level-guaranteed="false" name="APIConnectionPool" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" res-type="javax.sql.DataSource">
<property name="URL" value="jdbc:mysql://localhost:3306/dwolla"/>
<property value="3306" name="PortNumber" />
<property value="" name="Password" />
<property value="root" name="User" />
<property value="localhost" name="serverName" />
<property value="dwolla" name="DatabaseName" />
<property value=";create=true" name="connectionAttributes" />
</jdbc-connection-pool>
</resources>
<servers>
<server name="server" config-ref="server-config">
<resource-ref ref="jdbc/__TimerPool" />
<resource-ref ref="jdbc/__default" />
<resource-ref ref="jdbc/dwolla" />
</server>
</servers>
source to share
I got a solution. As you can see, I edited the domain.xml of the Glassfish server and set the connection pool and JNDI properties. I went to the Glassfish admin console and edited the connection pool, I realized the server was not recognizing the changes I made. It was showing the url property as "jdbc: mysql: //: 3306 /", not sure why it didn't get the properties from domain.xml but finally it works now.
source to share
To fix this error, go to Glassfish Admin Console, go to JNDI> Advanced Properties .
In the Name column, confirm that the user, password, URL is correct , unless you edit the values accordingly.
Change url tojdbc:mysql://localhost/{database}
Note that {database}
is the name of your database. Save changes and test.
source to share