9.5 Get More Information From the Database

The result of a Java action is saved in the return variable of the action. The system basically expects a list of name value pairs defined in a object of class java.util.Properties. If the return is of a different type, the system tries to create one using the toString method of the return object and assigns it to the name result.

We can easily update the application so the information is retrieved from the AccountPassword table.

Update the Database Table

Add a new column named Account_Info, then populate some initial data

Account_Id	Account_Password	Account_Info
101	123	The secret formula of Coke is sugar
102	3456	The secret formula of Coke is water

Update the Java Class

The updated class is listed here. The updated lines are shown in red.

public Properties check(String accountNumber, String passwd)
    {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        boolean passwdOK = false;
        String info = "";

        try {
            // Establish the connection.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(CONN_URL, DB_USER, DB_PASSWORD);

            // Create and execute an SQL statement
            String sqlst = "SELECT Account_Password, Account_Info FROM AccountPassword " +
                                 " WHERE Account_Id = '" + accountNumber + "'";

            stmt = con.createStatement();
            rs = stmt.executeQuery(sqlst);

            // Check the return data
            if (rs.next()) {
                String passwdSaved = rs.getString(1).trim();
                if (passwdSaved.equals(passwd.trim())) {
                    passwdOK = true;
                    info = rs.getString(2).trim();
                }
            }

            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (con != null) try { con.close(); } catch(Exception e) {}
        }

        Properties props = new Properties();
        props.setProperty("result", passwdOK ? "true" : "false");
        props.setProperty("info", info);

        return props;
    }

Create a new variable to hold the information

To hold the information returned from the database, we need to add a variable secret_info. This is to be added to the top element. In the Check Password element, add another action to save the return value (use Set Variable action).

secret_info = check_password.info

Use the variable as a prompt