Java 如何在 JSF 中与（MySQL）数据库交互

How to interact with (MySQL) Databases in JSF

提问by Jakob Abfalter

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.

我来自 PHP，正在尝试使用 JSF 构建一个 Web 应用程序。我真的很喜欢 JSF 的一些任务的简单性（例如 Ajax 的使用），但是当涉及到数据库集成时，我很困惑，不知道从哪里开始以及如何开始，无论我读了多少关于它.

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.

过去我只是在 PhpMyAdmin 中创建和管理我的 MySQL 数据库并通过 PHP 进行连接，在 JSF 中似乎要困难得多。

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.

我使用 Apache Tomcat 作为 Servlet 容器，我什至可以在 Tomcat 上运行 PhpMyAdmin 吗？此外，我阅读了一些访问 Web 应用程序中数据库的不同方法，例如使用 Hibernate 作为连接工具的 JPA 和 JDBC。

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.

所以我在这个问题中基本上要求的是关于 Java Server Faces 中数据库连接的初学者技巧和教程。另外我想知道是否有一种方法我仍然可以使用 PhpMyAdmin 来管理和创建我的数据库。

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.

我很抱歉这个问题如此笼统，但经过数小时的研究后，这个话题对我来说仍然很不清楚，我很想从有经验的人那里得到一些信息。

采纳答案by Rami.Q

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.

只是为了开始和理解如何在 JSF 中建立到 db 的连接，我会给你一个例子，如何在不使用任何其他框架的情况下手动完成，稍后你可以使用 Hibernate 或任何你想要的。

1-) be sure that your mysql server is running

1-) 确保您的 mysql 服务器正在运行

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

2-) 创建一个新的动态 Web 项目（如果尚未存在）

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

3-) 下载 mysql Connector jar 文件并将其放在 web-inf/lib 下

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

4-) 在你的 web.xml 中定义 DB Vars。像这样：

<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:

5-) 创建一个简单的数据库连接管理器示例：

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:

6-) 现在让我们创建一个名为 User 的测试类，我们在其中使用 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;
    }

}