quarta-feira, 4 de abril de 2012

Java Persistence API with MySQL, Eclipse and JBoss 7

PLEASE NOTE: This message was superseded by this one: Java Persistence API with EclipseLink


One of the problems to make this example work is to configure the MySQL Data Source appropriately in JBoss. This is not so trivial in practice, and is explained in the following sources: https://zorq.net/b/2011/07/12/adding-a-mysql-datasource-to-jboss-as-7/ and/or https://community.jboss.org/wiki/DataSourceConfigurationinAS7. It also exists in the admin guide of JBoss, currently in this URL: https://docs.jboss.org/author/display/AS7/Admin+Guide#AdminGuide-Datasources.

Let me refer to this tutorial here. I didn't follow it entirely, but I took advantage of the EclipseLink persistence.xml file it contains.

The first thing you need to do is to create a JPA Project, as follows: 


Next, you need to initialize the JPA project, as this:


Press the Next button until you reach the following screen:



Since we are using the EclipseLink as the Persistence provider, your persistence.xml in the Eclipse project should look like the following. There is a reference to a common.Student class, which I will present later on. Note that you may have to change the url, the driver, the user name and the password according to your needs. You may also want to change the table generation policies.

Another remark: this will only work for a stand-alone project. I propose a different version of persistence.xml if you want JPA to work from the JBoss application server (ahead).
<?xml version="1.0" encoding="UTF-8" ?>

<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"
version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">
<persistence-unit name="TestPersistence" transaction-type="RESOURCE_LOCAL">
<class>common.Student</class>
<properties>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/StudentDB" />
<property name="javax.persistence.jdbc.user" value="artur" />
<property name="javax.persistence.jdbc.password" value="fonseca" />

<!-- EclipseLink should create the database schema automatically -->
<property name="eclipselink.ddl-generation" value="create-tables" />
<property name="eclipselink.ddl-generation.output-mode"
value="database" />
</properties>

</persistence-unit>
</persistence>

Next, we need to add the MySQL driver in the eclipse project. I'm using my-sql-connector-java-5.1.19-bin.jar. You may want to use some other version (e.g. more recent, when it arrives). Just create a new folder, name it lib (the name doesn't really matter) and copy the jar file into it. Then use the context menu of the mouse over the my-sql-connector-java-5.1.19-bin.jar file and add it to the Build Path (Build Path-->Add to Build Path). Now, your project should already have access to the MySQL driver.

Before we write anything to the database, we need to define the classes that will hold our data. These will belong to the common package. Note that the Student class is ready for "JPA time", as it already contains all the annotations.

package common;


import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 *
 * @author filipius
 */
@Entity
@Table(name = "STUDENTS_TABLE")
public class Student implements Serializable {

    private static final long serialVersionUID = 1L;
    //we use this generation type to match that of SQLWriteStudents
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "studentid")
    private Long id;
    private String name;
    private String phone;

    public Student() {}

    public Student(String name, String phone) {
        this.name = name;
        this.phone = phone;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Student)) {
            return false;
        }
        Student other = (Student) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Student " + id + ": " + name + " " + phone;
    }
}




package common;


import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class GetStudentInfo {
static List<Student> list;

public static List<Student> get() {
list = new ArrayList<Student>();
boolean done = false;
Scanner sc = new Scanner(System.in);
while (!done) {
System.out.println("Student's name (empty to finish): ");
String name = sc.nextLine();
done = name.equals("");
if (!done) {
System.out.println("Student's phone: ");
String phone = sc.nextLine();
list.add(new Student(name, phone));
}
}
return list;
}
}




I will access the database using two different forms, for comparison purposes: 1) using standard SQL; 2) using JPA. Furthermore, they will interoperate. Let's start with SQL. Before that I include a picture with the entire structure of the Eclipse project to help you out:


Now, the class that writes to the database, using SQL:

package sql;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import common.GetStudentInfo;
import common.Student;

public class SQLWriteStudents {

    final static String name = "artur";
    final static String password = "fonseca";
    final static String tablename = "STUDENTS_TABLE";

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/StudentDB";
        Connection conn = (Connection) DriverManager.getConnection(url, name, password);
        Statement stmt = (Statement) conn.createStatement();

        String sql = "show tables like '" + tablename + "'";
        ResultSet rs;
        rs = stmt.executeQuery(sql);
        if (!rs.first()) { //create the table
            sql = "CREATE TABLE " + tablename + "(studentid BIGINT NOT NULL AUTO_INCREMENT, Name VARCHAR(254), Phone VARCHAR(20), PRIMARY KEY (studentid))";
            stmt.executeUpdate(sql);
        }

        List<Student> mylist = GetStudentInfo.get();

        int rows = 0;
        for (Student st : mylist) {
            sql = "INSERT INTO " + tablename
                    + "(Name, Phone)"
                    + "VALUES"
                    + "('" + st.getName() + "','" + st.getPhone() + "')";

            rows += stmt.executeUpdate(sql);
        }

        System.out.println("Added " + rows + " students.");
    }
}


It should already work. Start the MySQL server, and create the database (StudentDB). In the following figure, I added two students to the database. You can go directly to the mysql interface in the command line to check if these students entered the database, once the program finishes.





Let's do that programmatically, as well:

package sql;

import java.sql.DriverManager;
import java.sql.ResultSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class SQLReadStudents {

final static String name = "artur";
final static String password = "fonseca";

public static void main(String[] args) {
try {
String url = "jdbc:mysql://localhost:3306/StudentDB";
Connection conn = (Connection) DriverManager.getConnection(url, name, password);
Statement stmt = (Statement) conn.createStatement();
ResultSet rs;

rs = stmt.executeQuery("SELECT * FROM STUDENTS_TABLE");
while (rs.next()) {
Long id = rs.getLong("studentid");
String nme = rs.getString("Name");
String phone = rs.getString("Phone");
System.out.println(id + ": " + nme + " " + phone);
}
conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}



The result is the following:



But the real motivation for this blog entry was JPA. Therefore, let's write two correspondent classes, one to write, the other to read from the Student Database (table STUDENTS_TABLE, as you can see from the Student entity class above).


package jpa;

import common.Student;
import common.GetStudentInfo;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JPAWriteStudents {

    public static void main(String[] args) {
        List<Student> mylist = GetStudentInfo.get();

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("TestPersistence");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();

        tx.begin();
        for (Student st : mylist)
            em.persist(st);
        tx.commit();

        //after commit we have ids:
        for (Student st : mylist)
            System.out.println(st);
    }
}


We can add new students, running this program:



And let's read the database:


package jpa;

import common.Student;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

/**
 *
 * @author filipius
 */
public class JPAReadStudents {
public static void main(String args[]) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("TestPersistence");
EntityManager em = emf.createEntityManager();

String query = "SELECT s FROM Student s";
@SuppressWarnings("unchecked")
List<Student> mylist = (List<Student>) em.createQuery(query).getResultList();
for (Student st : mylist)
System.out.println(st);
}
}


We got the following after we run this class:



I would lie to you if I said something like "pretty easy, hum?". Although JPA allows code to be shorter and more readable, the knobs and bolts of JPA may keep the newbie away. I find all the configuration details pretty obscure, and I lost countless hours, to have this straight, both as a standalone project and as part of a JBoss deployed project. To help you a little bit with this, let me add a persistence.xml file that is working for me in a JPA project, which is part of an EAR.  A major difference is that the provider is, in this case, an hibernate class, which exists somewhere in the JBoss AS libraries.

<?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="Proj2-JPA" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/MySqlDS</jta-data-source>
<class>medicos.Medico</class>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
<property name="hibernate.hbm2ddl.auto" value="create" />
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/Proj2DB"/>
<property name="javax.persistence.jdbc.user" value="artur"/>
<property name="javax.persistence.jdbc.password" value="fonseca"/>
</properties>
</persistence-unit>
</persistence>


10 comentários:

  1. Only SQL is working OK, I have this message "No Persistence provider for EntityManager named TestPersistence" using JPA.

    ResponderEliminar
  2. For me everything worked as it is. Thanks for the probably simplest tutorial+example on JPA on the net.

    ResponderEliminar
  3. perfect explanation about java programming .its very useful.thanks for your valuable information.java training institute in chennai | java training in velachery

    ResponderEliminar
  4. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.

    AWS Online Training | Online AWS Certification Course - Gangboard
    AWS Training in Chennai | AWS Training Institute in Chennai Velachery, Tambaram, OMR
    AWS Training in Bangalore |Best AWS Training Institute in BTM ,Marathahalli

    ResponderEliminar
  5. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.

    angularjs Training in chennai
    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    ResponderEliminar
  6. I have been meaning to write something like this on my website and you have given me an idea. Cheers.
    python interview questions and answers | python tutorials

    ResponderEliminar
  7. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    Devops training in sholinganallur
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram

    ResponderEliminar
  8. A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article. I am learning a lot from you.

    rpa training in electronic-city | rpa training in btm | rpa training in marathahalli | rpa training in pune

    ResponderEliminar
  9. This is very good content you share on this blog. it's very informative and provide me future related information.
    Java training in Tambaram | Java training in Velachery

    Java training in Omr | Oracle training in Chennai

    ResponderEliminar
  10. It seems you are so busy in last month. The detail you shared about your work and it is really impressive that's why i am waiting for your post because i get the new ideas over here and you really write so well.

    Data Science course in kalyan nagar | Data Science course in OMR

    Data Science course in chennai | Data science course in velachery

    Data science course in jaya nagar | Data science training in tambaram


    ResponderEliminar