Wednesday, April 4, 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>


18 comments:

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

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

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

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. This comment has been removed by a blog administrator.

    ReplyDelete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. If Oracle DataBase Admin is a job that you're dreaming of, then we, Infycle are with you to make it into reality. Infycle Technologies is the best Oracle training institute in Chennai, provides various courses along with levels of highly demanded software courses such as Java, Python, Hadoop, Big Data, etc., in 100% full-fledged hands-on practical training with specialized tutors in the field. In addition to that, the mock interviews will be assigned for the candidates, so that, they can face the interviews with full confidence and can get their job for what they trained. For getting all these, call 7502633633 and get a free demo class.Best Oracle Training Institute in Chennai

    ReplyDelete
  12. Title:
    Best Oracle PLSQL Training in Chennai | Infycle Technologies

    Description:
    If SQL is a job that you're dreaming of, then we, Infycle are with you to make your dream into reality. Infycle Technologies offers the best Oracle PLSQL Training in Chennai, along with various levels of Oracle courses such as Oracle DBA, etc., in hands-on practical training with professional tutors in the field. The training will be tested by various levels of preparation strategies for the placement and by that the mock Best place for training will be given for the candidates, so that, they can face the interviews with full confidence. For your enhanced future, call 7502633633 to know more offers and training

    ReplyDelete
  13. This summer don’t miss this double-up gain Surprise offer from Infycle Technologies, best Python training institute in Chennai. This means top to bottom a complete syllabus about Python course in Chennai with a various levels of training that brings a hidden programmer with you. In addition to training, Placement development will also be given for meeting interviews from top firms. Call 7502633633 for more offers and details of the free demo class, that will change your career idea.
    Best software training in chennai

    ReplyDelete
  14. Title:
    Top Oracle Training Institute in Chennai | Infycle Technologies

    Description:
    Technologies is one of the best Oracle Training Institute in Chennai, which offers various programs in Oracle such as Oracle PLSQL, Oracle DBA, etc., in the 200% hands-on practical training with specialized trainers in the field. In addition to that, the mock interviews will be arranged for the candidates, so that, they can face the interviews without any fear. Of all that, 100% placement assurance will be given here. To have the words above in the real world, call 7502633633 to Infycle Technologies and grab a free demo to know more.
    best training institute in chennai

    ReplyDelete
  15. Grab Data Science Certification in Chennai for skyrocketing your career with Infycle Technologies, the best Software Training & Placement institutes in and around Chennai. In addition to the Certification, Infycle also gives the best placement training for personality tests, interview preparation, and mock interviews for leveling up the candidate's grades to a professional level.

    ReplyDelete