java gui mysql query for different buttons

Question

For school I'm working on a program that connects to a MySQL database and perform queries such as an ATM machine might.

So far I've made a simple program where I've been putting my connection code in the submit button, but now I need to support many different buttons for balance query, withdrawal, etc.

For my initial screen where the user enters their login and PIN I have the following code, but I'm kind of lost as to how I can do these queries under other buttons with the variables I have in this button:

private void bSubmitActionPerformed(java.awt.event.ActionEvent evt) {                                        
    String login = jLogin.getText();
    String pin = jPin.getText();


    try {
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/bankaccount", "root", "password");

        Statement st = (Statement) con.createStatement();
        PreparedStatement ps;
        String sqlCommand;
        ResultSet rs;

        sqlCommand = "SELECT * FROM accounts WHERE loginID= '" + login + "' AND pin='" + pin + "'";

        rs = st.executeQuery(sqlCommand);

        if (rs.next()) {

            do {

                card2.setVisible(true);
                card1.setVisible(false);



            } while (rs.next());
        } else {
            jWarning.setText("please try again");
        }





    } catch (Exception e) {
    }
}

manix · Accepted Answer · 2012-05-31 15:34:48Z

1

    public ResultSet execute(String sqlCommand ){

    ResultSet rs = null;

    try {
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/bankaccount", "root", "password");

        Statement st = (Statement) con.createStatement();
        PreparedStatement ps;


        rs = st.executeQuery(sqlCommand);


    } catch (Exception e) {
    }

    return rs;

}


public void submitButton1(){
     String login = jLogin.getText();
    String pin = jPin.getText();

ResultSet rs = execute("SELECT * FROM accounts WHERE loginID= '" + login + "' AND pin='" + pin + "'");

   /// do whatever with your rs

}

public void submitButton2(){
     String login = jLogin.getText();
    String pin = jPin.getText();

ResultSet rs = execute("more sql");

   /// do whatever with your rs

}

appending the update method:

public int update(String sqlCommand ){

       int affected_rows = 0;
        try {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/bankaccount", "root", "password");

            Statement st = (Statement) con.createStatement();                

            affected_rows = st.executeUpdate(sqlCommand);


        } catch (Exception e) {
        }

        return affected_rows;

    }

The update method also can be used to insert and delete. An update statament could be something like this:

update accounts  
set columnA = "a value",  
columnB = "other value"  
where loginID = 4;

edited May 31, 2012 at 15:34

answered May 30, 2012 at 0:28

manix

14.7k11 gold badges74 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Drek Pil Over a year ago

works good, what can i add if i would also like to update the table ?

manix Over a year ago

you can add another method like public int update(String sqlCommand ){} and then use st.executeUpdate(sqlCommand); instance of executeQuery. Given statement (sqlCommand ) may be an INSERT, UPDATE, or DELETE statement and return the total of rows affected

Drek Pil Over a year ago

do i have to declared the connection again in the body of that method ? could you update your answer so i can cleary understand

Drek Pil Over a year ago

do i need to re delcare the result set in that method ? i get rs not found, also how how the update statement be written ? im kinda lost here for this part of it

manix Over a year ago

Sorry, the post had a mistake. No, you don't because the update statements don't return resultset, otherwise an int is returned according to the total of rows affected, for this reason the update method return an int value.

Collectives™ on Stack Overflow

java gui mysql query for different buttons

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related