Postgres with Glassfish connection pool

Question

I am new to the Java EE World and want to use PostgreSQL as my database in an web application. I use Glassfish as my application server and added a connection pool via the administration interface (I used this site for help). Now I don't know what is the "right" way to use the connection pool in my application (in fact I currently don't know how to even get an connection from the pool and write a simple query).

We need to write pretty complex queries, so I don't know if I should create a mapping for every table and use the mappings, or just use sql and some kind of row mapper to pars the results (we used the Spring RowMapper before).

So my Question are:

1. What different ways are there to use the connections from the pool?
2. What are the (dis)advantages of these patterns?
3. How to create a clever mapping that can handle complicated and performance intensive query's.
4. Does this work with Hibernate? And if so, how?

Answer 1

1 - If your connection pool is configured using glassfish, you may develop a simple EJB and inject the connection using annotation, I think this is the best way to handle connexion from your pool.

(All examples are compatible with hibernate and work well on postgresql database)

eg:

@Stateless
public class myEjb
{
    // inject the entityManager  
    @PersistenceContext(unitName = "myPu")
    private EntityManager em;

    public Car findCarById(Long carId)
    {
        Car aCar = (Car) em.find(Car.class, carId);
        return aCar;
    }
}

the unitName "myPu" is the JNDI name of your JDBC resource configured in the glassfish administration console.

2 - the advantage of this pattern is that it is not code dependant, you may not change your JDBC ressource regarding the dev, test or production environment. The JDBC url, user and pass are configured on the server and not in an XML file that you may carefully change each time you switch from an environment to another. This way you don't have to handle the transaction, this is the server that commit or rollback on exception. If you need to handle the transaction yourself, you may add the annotation under the Stateless one:

@TransactionManagement(value=TransactionManagementType.BEAN) 

you the need to handle by yourself the transaction like:

em.getTransaction().begin();
try
{
    // do something with the entityManager
    em.getTransaction().commit();
}
catch(MyException ex)
{
    em.getTransaction().rollback();
}

3 - If you need to create complexe request, you may declare some named queries on your entity. below a possible implementation of the Car entity:

@Entity
public class Car 
{
    @NamedQueries(
    {
        @NamedQuery(name = "Car.findByBrandAndColor", query = "SELECT c FROM Car WHERE c.brand = :brand AND color = :color")
    })
}

below an example of function to add to your EJB that use the previous named query:

public List<Car> findAllCarByBrandAndColor(String aBrand, String aColor)
{
    List<Car> theCars;

    Query theQuery = em.createNamedQuery("Car.findByBrandAndColor");
    theQuery.setParameter("brand", aBrand);
    theQuery.setParameter("color", aColor);

    try
    {
        theCars = (List<Car>) query.getResultList();
    }
    catch(NoResultException ex)
    {
        theCars = null;
    }

    return theCars;
}

Of course, you can write named queries as complex as you want (named queries are cached and may be better for performance), but if you really need to query directly the database, you may use native queries as below:

// the query is native SQL
Query theQuery = em.createNativeQuery("SELECT * FROM car WHERE color = 'blue' AND brand = 'toyota'", Car.class);
Car aCar = (Car) query.getSingleResult();