How to interact with (MySQL) Databases in JSF

Question

I come from PHP and am trying to build a Web Application with JSF. I really like the simplicity of some tasks with JSF, (for instance the use of Ajax) but when it comes to database integration I am very confused and don't know where and how to start, no matter how much I am reading about it.

In the past I simply created and administrated my MySQL databases in PhpMyAdmin and did the connection via PHP, in JSF it seems to be way more difficult.

I am using Apache Tomcat as Servlet Container, can I even run PhpMyAdmin on Tomcat? Furthermore I read about some different approaches to accessing the databases in the Web Application, like JPA and JDBC using Hibernate as a connection tool.

So what I am basically asking for in this question is for beginner tips and tutorials regarding database connection in Java Server Faces. Also I want to know if there is a way I could still use PhpMyAdmin to manage and create my databases.

I am sorry for this question beeing so generic, but after hours of researching this topic is still so unclear to me that I would love to get some information from experienced people.

Answer 1

Only for the beginning and understanding how to establish a connection to db in JSF, i'll give you an example how to do it manually without using any other Frameworks, later you could use Hibernate or what ever you want.

1-) be sure that your mysql server is running

2-) create a new dynamic web Project(if not already exists)

3-) download the mysql Connector jar file and put it under web-inf/lib

4-) in your web.xml define the DB Vars. something like this:

<context-param>
    <param-name>JDBC-DRIVER</param-name>
    <param-value>com.mysql.jdbc.Driver</param-value>
</context-param>
<context-param>
    <param-name>DB-SERVER</param-name>
    <param-value>jdbc:mysql://localhost:3306/db_name...</param-value>
</context-param>
<context-param>
    <param-name>DB-USER</param-name>
    <param-value>root or your db username</param-value>
</context-param>
<context-param>
    <param-name>DB-PASSWORD</param-name>
    <param-value>...db user password ...</param-value>
</context-param>

5-) create a simple Database Connection Manager example:

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.regex.Pattern;

import javax.faces.context.FacesContext;


public class DBM implements Serializable{
    private static final long serialVersionUID = 9204275723046653468L;
    private String db_server   = "";
    private String db_user     = "";
    private String db_password = "";
    private String db_driver   = "";

    public Connection connection = null;

    public DBM() throws Exception {
        init();
    }

    private void init()throws Exception{
        FacesContext fc = FacesContext.getCurrentInstance();
        db_server   = fc.getExternalContext().getInitParameter("DB-SERVER");
        db_user     = fc.getExternalContext().getInitParameter("DB-USER");
        db_password = fc.getExternalContext().getInitParameter("DB-PASSWORD");
        db_driver   = fc.getExternalContext().getInitParameter("JDBC-DRIVER");
        Class.forName(db_driver);
    }   

    public Connection initConnection() throws Exception{
        if( this.connection == null ){
            this.connection = DriverManager.getConnection(db_server, db_user, db_password);
            this.connection.setAutoCommit(false);
        }else if( this.connection.isClosed() ){
            this.connection = null;
            this.connection = DriverManager.getConnection(db_server, db_user, db_password);
            this.connection.setAutoCommit(false);
        }
        return this.connection;
    }

    public void closeConnection(){
        try {
            if( this.connection != null ){
                this.connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void commitConnection(){
        try {
            if( this.connection != null && !this.connection.isClosed() ){
                this.connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void rollbackConnection(){
        try {
            if( this.connection != null && !this.connection.isClosed() ){
                this.connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6-) now lets create a test Class named User where we use the db Connection:

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;



public class User implements Serializable{
    private static final long serialVersionUID = -7667553477247791348L;
    private int id;
    private String name;    
    private String category;
    private static String db_table = "db table name of this class";

    public User(){

    }
    public User(int id, String name, String cat) {
        super();
        this.id = id;
        this.name = name;
        this.category = cat;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getCategory() {
        return category;
    }
    public void setCategory(String category) {
        this.category = category;
    }
    public static List<User> getListByCategory(DBM dbm, String cid, boolean close) throws Exception{
        List<User> list            = new ArrayList<User>();
        PreparedStatement preState = null;
        ResultSet resultSet        = null;
        try {
            if( dbm == null ){
                dbm = new DBM();
            }
            String sql = "SELECT * FROM "+db_table+" WHERE _CATEGORY_ID=?";
            preState   = dbm.initConnection().prepareStatement(sql); 
            preState.setString(1, cid);
            resultSet  = preState.executeQuery();
            while (resultSet.next()) {
                list.add( new User(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3)) );                
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            if( preState != null )
                preState.close();
            if( close && dbm.connection != null )
                dbm.connection.close();         
        }
        return list;
    }

}