poixen poixen -4 years ago 97
MySQL Question

No Database Selected JPA Persistence

I have connections to my database running. I can execute the following with no issue:

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/people", "root", "r00t");

PreparedStatement statement = (PreparedStatement) conn.prepareStatement("select * from users");
ResultSet result = statement.executeQuery();


However, after setting up JPA and a persistant class, I always get a "No Database Selected" error. It doesn't seem like I need to adjust my database config (MySQL connected to Glassfish 3.1) otherwise the above code wouldn't work.

The call being made:

SELECT USERNAME, FIRSTNAME, LASTNAME, PASSWORD, PERMISSION FROM users


I have tried this call directly in MySQL Workbench and it doesnt work.

This one does work:

SELECT USERNAME, FIRSTNAME, LASTNAME, PASSWORD, PERMISSION FROM people.users


I have been playing round and cant seem to add the database name anywhere ("people").
Here is what I have so far:

Using EclipseLink 2.0.x

JPA implimentation: Disable Library Configuration

Connection: Local MySQL (I have my database successfully connected)

Schema: people

From my servlet:

package com.lowe.samples;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mysql.jdbc.PreparedStatement;

@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

@PersistenceContext
private EntityManager em;

public TestServlet() {
super();
}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

PrintWriter out = response.getWriter();
out.println("<h1>DataBase Test:<h1>");

try {

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/people", "root", "r00t");

PreparedStatement statement = (PreparedStatement) conn.prepareStatement("select * from users");
ResultSet result = statement.executeQuery();

// prep the table
out.print("<table border=\"5\">");
out.print("<tr>");
out.print("<td>UserName</td>");
out.print("<td>FirstName</td>");
out.print("<td>LastName</td>");
out.print("<td>Password</td>");
out.print("<td>Permission</td>");
out.print("</tr>");

while(result.next()) {
out.print("<tr>");
out.print("<td>" + result.getString(1) + "</td>");
out.print("<td>" + result.getString(2) + "</td>");
out.print("<td>" + result.getString(3) + "</td>");
out.print("<td>" + result.getString(4) + "</td>");
out.print("<td>" + result.getString(5) + "</td>");
out.print("</tr>");
}

out.print("</table>");

User u = (User)this.em.createNamedQuery("User.findAll").getResultList();
out.print("User Name: " + u.getFirstName());

} catch (ClassNotFoundException e) {
out.print("<h4>" + e.getMessage() + "</h4>");
e.printStackTrace();
} catch (SQLException e) {
out.print("<h4>" + e.getMessage() + "</h4>");
e.printStackTrace();
}
}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}

}


My persistance class:

package com.lowe.samples;

import java.io.Serializable;
import javax.persistence.*;


/**
* The persistent class for the users database table.
*/
@Entity
@Table(name="users")
@NamedQuery(name="User.findAll", query="SELECT u FROM User u")
public class User implements Serializable {
private static final long serialVersionUID = 1L;

@Id
private String userName;

private String firstName;

private String permission;

private String lastName;

private String password;

public User() {
}

public String getUserName() {
return this.userName;
}

public void setUserName(String userName) {
this.userName = userName;
}

public String getFirstName() {
return this.firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getPermission() {
return this.permission;
}

public void setPermission(String permission) {
this.permission = permission;
}

public String getLastName() {
return this.lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getPassword() {
return this.password;
}

public void setPassword(String password) {
this.password = password;
}

}


the persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="MyFriends">
<jta-data-source>jdbc/MySQLDataSource</jta-data-source>
<class>com.lowe.samples.User</class>
</persistence-unit>
</persistence>

Answer Source

The first piece of code is using JDBC to load the database driver, connect and execute a query. The fact that it works shows that you have the MySQL driver in your classpath and that you can connect to your database using the URL and credentials provided. It has nothing to do with JPA.

Your persistent.xml has persistence unit named "MyFriends" with a JTA-aware data source configured:

<jta-data-source>jdbc/MySQLDataSource</jta-data-source>

This data source is bound to JNDI.

According to the error you are getting the connection pool is misconfigured, probably lacking the database name in the URL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download