1. Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

SQL Operations in Java

Discussion in 'Programming & Webmastering' started by Wozzer, Dec 20, 2012.

  1. Wozzer

    Joined:
    Jun 30, 2008
    Messages:
    1,133 (0.48/day)
    Thanks Received:
    68
    Location:
    England
    Hi all,

    I'm trying to create an if statement that:
    "When a user input equals ANY of the the data in column "userID", Then execute"

    I'm aware you can use the .equals operation but not sure how you would link it to the column within the database.

    Wozzer
     
  2. Mindweaver

    Mindweaver Moderato®™ Staff Member

    Joined:
    Apr 16, 2009
    Messages:
    5,362 (2.62/day)
    Thanks Received:
    2,946
    Location:
    Statesville, NC
    Have you loaded the JDBC driver, and successfully connected to your database?
     
    Crunching for Team TPU
  3. 3870x2

    3870x2

    Joined:
    Feb 26, 2008
    Messages:
    4,875 (1.98/day)
    Thanks Received:
    689
    Location:
    Joplin, Mo
    It seems like something he would have already done if he is at this point.

    If you haven't, this would be the place to start.
     
  4. Mindweaver

    Mindweaver Moderato®™ Staff Member

    Joined:
    Apr 16, 2009
    Messages:
    5,362 (2.62/day)
    Thanks Received:
    2,946
    Location:
    Statesville, NC
    It would seem, but I'd like to know for sure. ;)

    If it's just the Select statement then something like this:

    Code:
    "[color=blue]SELECT[/color] * [color=blue]FROM[/color] UserTable [color=blue]WHERE[/color] userID = [color=red]'[/color]" + UserInput + "[color=red]'[/color];"
     
    Last edited: Dec 20, 2012
    Wozzer says thanks.
    Crunching for Team TPU
  5. Aquinus

    Aquinus Resident Wat-man

    Joined:
    Jan 28, 2012
    Messages:
    6,665 (6.46/day)
    Thanks Received:
    2,324
    Location:
    Concord, NH
    Instead of telling how how you're trying to solve the problem, maybe you should describe what you're doing (for the project,) and where you're at so we can have an idea about what you're trying to do rather than how you're trying to approach it.
     
  6. okidna

    okidna

    Joined:
    Jan 2, 2012
    Messages:
    474 (0.45/day)
    Thanks Received:
    351
    Location:
    Indonesia
    I only understand a little bit Java, so pardon me If I'm wrong, userInput is the user input (string variable), users is your table name, and I assume you already have your connection to your database :

    PHP:
    SQLConnection.setURL("insert_connection_string_here");
    con=SQLConnection.getNewConnection();
    Statement stmt con.createStatement();

    // execute your SQL statement here, for example :
    String sqlCommand "SELECT userID FROM users WHERE userID='"+userInput+"'";  
    ResultSet rs stmt.executeQuery(sqlCommand);

    bool hasRows false;
    while(
    rs.next()){
      
    hasRows true;
      
    // put the codes that you want to execute here
    }

    if(!
    hasRows)
    {
      
    // do stuff when no rows/match present.
    }
    Please CMIIW.

    And

    +1.
     
  7. Wozzer

    Joined:
    Jun 30, 2008
    Messages:
    1,133 (0.48/day)
    Thanks Received:
    68
    Location:
    England
    Thanks for the responses. Wasn't expecting so many to reply :laugh:

    To clarify - I have a java program. I only want users to access the software if they are registered.

    I've set up a database (and connected to it!). I want to run a query which checks if the users ID and passwords ID match. If matched, permissions will be granted and the main menu will open.

    Code:
     System.out.println("MySQL Connect Example.");
            Connection conn = null;
            String url = "jdbc:mysql://localhost:3306/";
            String dbName = "watson_users";
            String driver = "com.mysql.jdbc.Driver";
            String userName = "root";
            String password = "";
            try {
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(url + dbName, userName, password);
                System.out.println("Connected to the database");
    
                Statement st = (Statement) conn.createStatement();
    
                String query_to_update = "SELECT * FROM `user_database` WHERE CollarID='" + collarIDField.getText() + "' AND Password='" + passwordField.getText() + "'";
    
                System.out.println("Query: " + query_to_update);
    
                int val = st.executeUpdate(query_to_update);
                System.out.println("We've got to this point");
    
                conn.close();
            } catch (InstantiationException | IllegalAccessException | SQLException ex) {
                Logger.getLogger(MetaDataExtractor.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(this, "Can't connect to Database");
            } catch (ClassNotFoundException ex) {
                JOptionPane.showMessageDialog(this, "Can't connect to Database");
            }
    Error:
    Code:
    java.sql.SQLException: Can not issue SELECT via executeUpdate()
    Edit: Just noticed .executeUpdate only allows for INSERT, DELETE, and UPDATE.
     
    Last edited: Dec 20, 2012
  8. Jizzler

    Jizzler

    Joined:
    Aug 10, 2007
    Messages:
    3,454 (1.30/day)
    Thanks Received:
    645
    Location:
    Geneva, FL, USA
    Important is that you clean those input strings.

    Code:
    PreparedStatement selectUser = conn.prepareStatement("SELECT * FROM user_database WHERE CollarID= ? AND Password = ?");
    
    selectUser.setString(1, collarIDField.getText());
    selectUser.setString(2, passwordField.getText());
    
    if ( selectUser.execute() ) {
    	//get the data, welcome the user
    } else {
    	//tell them to get it right or bugger off
    }
    
    Now when I claim that my ID is "1; DROP TABLE user_database;" I'm not going to ruin your day. :)


    (hope I got it right, only write JAVA when I have to).
     
    Wozzer says thanks.
  9. okidna

    okidna

    Joined:
    Jan 2, 2012
    Messages:
    474 (0.45/day)
    Thanks Received:
    351
    Location:
    Indonesia
    .executeUpdate() generally used for altering the databases. DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this.

    Use .executeQuery() or .execute() if you want to use SELECT. Remember the output is in the form of ResultSet.
     
    Wozzer says thanks.
  10. Wozzer

    Joined:
    Jun 30, 2008
    Messages:
    1,133 (0.48/day)
    Thanks Received:
    68
    Location:
    England
    Just to clarify - Is that to prevent SQL injection attacks, etc?

    Thanks for all the replies. Working on it now.

    Edit - Login seems to be working although I don't think it's checking the actual results as it's opening my program based on .execute
    (IE - If anything is executed, do this).

    How can I check the returned results from my database?
    Thanks all!
    :toast:
     
    Last edited: Dec 20, 2012
  11. Jizzler

    Jizzler

    Joined:
    Aug 10, 2007
    Messages:
    3,454 (1.30/day)
    Thanks Received:
    645
    Location:
    Geneva, FL, USA
    Last edited: Dec 20, 2012
    okidna and Wozzer say thanks.
  12. Wozzer

    Joined:
    Jun 30, 2008
    Messages:
    1,133 (0.48/day)
    Thanks Received:
    68
    Location:
    England
    Will read up on the documentation later tonight. Many thanks Jizzler.

    lol @ the cartoon strip. :laugh:
     

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page