Configure MySQL datasource in Glassfish 3.1

Part 1 of this article shows how to configure a MySQL datasource in Glassfish 3.1.

Part 2 of this article shows how to test the connection with a standalone Java client program.


Part 1

Assume Glassfish's installation folder is D:\glassfish3\glassfish

Copy mysql-connector-java-xxx-bin.jar to D:\glassfish3\glassfish\lib or D:\glassfish3\glassfish\domains\domain1\lib\ext

Start MySQL server, assume the following settings

URL: jdbc:mysql://localhost:3306/mydb
username: root
password: root

Start Glassfish, go to Admin Console, http://localhost:4848

Resources -> JDBC -> JDBC Connection Pools

Click on 'New'

Click on 'Next'

Scroll down, specify the following properties, and leave others as they are.

User	root
Password	root
Url	jdbc:mysql://localhost:3306/mydb
URL	jdbc:mysql://localhost:3306/mydb

Click on 'Save'

Go to Resources -> JDBC -> JDBC Resources

Click on 'New'

Click on 'OK'

Click on 'Ping' to test.

Part 2

Create a java project in Eclipse (must use JDK 1.6)

import java.sql.Connection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class Main {

    /**
     * @param args
     */
    public static void main(String[] args) 
      throws Exception{
        Context ctx = new InitialContext();
        DataSource ds 
          = (DataSource) ctx.lookup("jdbc/mysql");
        Connection con = ds.getConnection();
        con.close();
    }
}

Add the following two jar files to class path

Note: You can't directly copy gf-client.jar into project's folder. You have to point to the jar file under glassfish\lib

After starting MySQL server and Glassfish Server, we can test the program.

REQUIRED VS REQUIRES_NEW transaction attribute type

This article uses code examples to illustrate the difference between REQUIRED and REQUIRES_NEW transaction attribute type.

Create two entity beans.

@Entity
@Table(name="T_COMPANY")
public class Company{
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    
    @Column(name = "NAME")
    private String name;
    
    public Company() {
        super();
    }
    
    public Company(String name) {
        super();
        this.name = name;
    }
}

@Entity
@Table(name="T_PERSON")
public class Person{
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    
    @Column(name = "NAME")
    private String name;
    
    public Person() {
        super();
    }
    
    public Person(String name) {
        super();
        this.name = name;
    }
}

Create a stateless session bean (companyBean) with a method having REQUIRES_NEW transaction attribute type.

@Stateless(name="companyBean")
@Local(CompanyManager.class)
public class CompanyManagerBean 
    implements CompanyManager {

    @PersistenceContext(unitName="unit")
    private EntityManager em;
    
    @Override
    @TransactionAttribute
    (TransactionAttributeType.REQUIRES_NEW)
    public void save(Company company) {
        em.persist(company);
    }
}

Create another stateless session bean (personBean), and inject the companyBean into the personBean.

@Stateless(name="personBean")
@Local(PersonManager.class)
public class PersonManagerBean 
    implements PersonManager {

    @PersistenceContext(unitName="unit")
    private EntityManager em;
    
    @EJB(beanName="companyBean")
    private CompanyManager companyManager;
    
    @Override
    @TransactionAttribute
    (TransactionAttributeType.REQUIRED)
    public void save(Person person) {
        companyManager.save(new Company("Oracle"));
        em.persist(person);
    }
} 

Write a servlet to test

public class PersonServlet extends HttpServlet {

    @EJB(beanName="personBean")
    private PersonManager personManager = null;
    
    @Override
    protected void doGet(HttpServletRequest req, 
            HttpServletResponse resp)
            throws ServletException, IOException {
        personManager.save(new Person("Mingtao001"));
    }
}

Result:

Table T_COMPANY

Table T_PERSON

Now throw an exception in personBean

@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void save(Person person) {
    companyManager.save(new Company("Oracle"));
    em.persist(person);
    throw new RuntimeException("error!");
}

Clean the table and retest the servlet

Result:

Table T_COMPANY

Table T_PERSON

The SQL that saves the person has been rolled back while the SQL that saves the company was not affected.

Now change the transaction attribute type from REQUIRES_NEW to REQUIRED in companyBean.

@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void save(Company company) {
    em.persist(company);
}

Clean the table and retest the servlet

Result:

Table T_COMPANY

Table T_PERSON

Both SQLs have been rolled back as they are in the same transaction.