Chapter 33 Exercise 9, Introduction to Java Programming, Tenth Edition Y. Daniel LiangY.

33.9 (Access and update a Staff table) Write a JSF program that views, inserts, and updates staff information stored in a database, as shown in Figure 33.32. The view button displays a record with a specified ID. The Staff table is created as follows:

create table Staff (
id char(9) not null,
lastName varchar(15),
firstName varchar(15),
mi char(1),
address varchar(20),
city varchar(20),
state char(2),
telephone char(10),
email varchar(40),
primary key (id)
);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

@ManagedBean
@SessionScoped
public class Exercise09 {

    private Statement statement;
    private Connection connection;

    private String id = "";
    private String lastName = "";
    private String firstName = "";
    private String mi = "";
    private String address = "";
    private String city = "";
    private String state = "";
    private String telephone = "";
    private String errorMessage = "";
        
    public Exercise09() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "root", "root");
            statement = connection.createStatement();
        } catch (SQLException | ClassNotFoundException ex) {
           
        }        
        
        try {
            ResultSet rset = statement.executeQuery("select * from Staff limit 1;");
            if(rset.next()) {
                id = rset.getString(1);
                lastName = rset.getString(2);
                firstName = rset.getString(3);
                mi = rset.getString(4);
                address = rset.getString(5);
                city = rset.getString(6);
                state = rset.getString(7);
                telephone = rset.getString(8);
                errorMessage = "";  
            } else {
                id = "";
                resetData();
                errorMessage = "Table is empty";
            }                
        } catch (SQLException ex) {
            id = "";
            resetData();
            errorMessage = "Table is empty";
        }        
    }
    
    public void doView() {
        if((id == null) || (id.equals(""))) {
            id = "";
            resetData();
            errorMessage = "Enter ID";   
        } else {
            try {
                ResultSet rset = statement.executeQuery("select * from Staff where id = " + id + " limit 1;");
                if(rset.next()) {
                    id = rset.getString(1);
                    lastName = rset.getString(2);
                    firstName = rset.getString(3);
                    mi = rset.getString(4);
                    address = rset.getString(5);
                    city = rset.getString(6);
                    state = rset.getString(7);
                    telephone = rset.getString(8);
                    errorMessage = "";            
                } else {
                    resetData();
                    errorMessage = "ID " + id + " not found";   
                }                
            } catch (SQLException ex) {
                resetData();
                errorMessage = "ID " + id + " not found";      
            }
        }        
    }
    
    public void doInsert() {
        if((id == null) || (id.equals(""))) {
            errorMessage = "Enter ID";  
        } else {
            String queryString = "insert into Staff (id, lastName, firstName, mi, address, city, state, telephone) values (?, ?, ?, ?, ?, ?, ?, ?);";
            try {
                PreparedStatement preparedStatement = connection.prepareStatement(queryString);
                preparedStatement.setString(1, id);
                preparedStatement.setString(2, lastName);
                preparedStatement.setString(3, firstName);
                preparedStatement.setString(4, mi);
                preparedStatement.setString(5, address);
                preparedStatement.setString(6, city);
                preparedStatement.setString(7, state);
                preparedStatement.setString(8, telephone);
                preparedStatement.executeUpdate();
                errorMessage = "";   
            } catch (SQLException e2) {
                errorMessage = e2.getMessage();   
            }
        }        
    }
    
    public void doUpdate() {
        if((id == null) || (id.equals(""))) {
           resetData();
           errorMessage = "ID " + id + " not found";   
        } else {
           String queryString = "update Staff set lastName = ?, firstName = ?, mi = ?, address = ?, city = ?, state = ?, telephone = ? where id = ?";
           try {
               PreparedStatement preparedStatement = connection.prepareStatement(queryString);
               preparedStatement.setString(1, lastName);
               preparedStatement.setString(2, firstName);
               preparedStatement.setString(3, mi);
               preparedStatement.setString(4, address);
               preparedStatement.setString(5, city);
               preparedStatement.setString(6, state);
               preparedStatement.setString(7, telephone);
               preparedStatement.setString(8, id);
               preparedStatement.executeUpdate();
               errorMessage = "";  
           } catch (SQLException e2) {
               errorMessage = e2.getMessage();   
           }
        }       
    }
    
    public void doDelete() {
        if((id == null) || (id.equals(""))) {
            id = "";
            resetData();
            errorMessage = "Enter ID"; 
        } else {
            try {
                statement.executeUpdate("delete from Staff where id = " + id + "");
                try {
                    ResultSet rset = statement.executeQuery("select * from Staff limit 1;");
                    if (rset.next()) {
                        id = rset.getString(1);
                        lastName = rset.getString(2);
                        firstName = rset.getString(3);
                        mi = rset.getString(4);
                        address = rset.getString(5);
                        city = rset.getString(6);
                        state = rset.getString(7);
                        telephone = rset.getString(8);
                        errorMessage = "";  
                    } else {
                        id = "";
                        resetData();
                        errorMessage = "Table is empty";
                    }
                } catch (SQLException ex) {
                    id = "";
                    resetData();
                    errorMessage = "Table is empty";        
                }
            } catch (SQLException ex) {
                id = "";
                resetData();
                errorMessage = ex.getMessage();   
            }
        }        
    }
    
    private void resetData() {
        lastName = "";
        firstName = "";
        mi = "";
        address = "";
        city = "";
        state = "";
        telephone = "";
    }

    public String getAddress() {
        return address;
    }

    public String getCity() {
        return city;
    }

    public String getFirstName() {
        return firstName;
    }

    public String getId() {
        return id;
    }

    public String getLastName() {
        return lastName;
    }

    public String getMi() {
        return mi;
    }

    public String getState() {
        return state;
    }

    public String getTelephone() {
        return telephone;
    }

    public String getErrorMessage() {
        return errorMessage;
    }
    
    public void setAddress(String address) {
        this.address = address;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public void setMi(String mi) {
        this.mi = mi;
    }

    public void setState(String state) {
        this.state = state;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }
    
}

<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="http://xmlns.jcp.org/jsf/html">
    <h:head>
        <title>Exercise09</title>
    </h:head>
    <h:body>
        <h:form>
            <center>
                <table>
                    <tr align="center"><td height="50"><p><font color = "#FF0000"><h:outputLabel value="#{exercise09.errorMessage}"/></font></p></td></tr><tr align="left"><td>
                    <fieldset><legend>Staff Information</legend>
                        <p><h:inputText size = "5"  style="background-color: yellow" value="#{exercise09.id}" /></p>
                        <p>Last Name: <h:inputText value="#{exercise09.lastName}" />
                            First Name: <h:inputText value="#{exercise09.firstName}" />
                            MI: <h:inputText size="5" value="#{exercise09.mi}" /></p>
                        <p>Address: <h:inputText value="#{exercise09.address}" /></p>
                        <p>City: <h:inputText value="#{exercise09.city}" />
                            State: <h:inputText size="5" value="#{exercise09.state}" /></p>
                        <p>Telephone: <h:inputText value="#{exercise09.telephone}" /></p>
                    </fieldset></td></tr><tr align="center"><td>
                            <p><h:commandButton value="View" action="#{exercise09.doView()}"/>
                        <h:commandButton value="Insert" action="#{exercise09.doInsert()}"/>
                        <h:commandButton value="Update" action="#{exercise09.doUpdate()}"/>
                        <h:commandButton value="Delete" action="#{exercise09.doDelete()}"/>
                    </p></td></tr>
                </table>
            </center>
        </h:form>
    </h:body>
</html>