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>
