Java how to search SQL from user input to method

Question

Afternoon all,

I'm new to java and programming in general, so what I have so far is somewhat cobbled together from other snippets.

The premise of the class I have is that it will take an input from the user, (eventually via a SOAP service), search the SQL DB and return the relevant row. It will only return one row as the search is on the unique ID.

The below code works as I want it to, I just can't figure out how to code it to accept a string input to search on.

package my.pack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
//import java.util.List;

    public class ShowRow {

        public ArrayList<String> ShowResults(){

            Connection connection = null;
            String url = "jdbc:mysql://localhost:3306/";
            String dbName = "******";
            String driverName = "com.mysql.jdbc.Driver";
            String userName = "******";
            String password = "******";
            ArrayList<String> rowArray = new ArrayList<String>();

            try{
                  Class.forName(driverName).newInstance();
                  connection = DriverManager.getConnection(url+dbName, userName, password);

                  try{
                    Statement stmt = connection.createStatement();
                    String selectquery = "SELECT * FROM `thisTable` WHERE `uniqueID` = 12345";
                    ResultSet rs = stmt.executeQuery(selectquery);


                    while(rs.next()){
                      rowArray.add(rs.getString(1));
                      rowArray.add(rs.getString(2));
                      rowArray.add(rs.getString(3));

                      System.out.println(rowArray);
                    }
                  }
                  catch(SQLException s){
                    System.out.println(s);
                  }
                  connection.close();
                }
                catch (Exception e){
                  e.printStackTrace();
                }

            return rowArray;
        }
}

The row in question is;

String selectquery = "SELECT * FROM `thisTable` WHERE `uniqueID` = 12345";

Where 12345 would be taken from a user input.

To clarify 'user input' the following class would require an input for 'this' (but has little to do with my question otherwise!);

public class Input {
     public String typeHere(String this){
return "You typed " + this;
  }
}

Many thanks for your time and help!

Use placeholder ? and set it in the statement. Use PreparedStatement. — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Sep 5, 2013 at 14:08
why dont you put an int-parameter to the showResults(int id) function and use it in the statement? something like this: String selectquery = "SELECT * FROM thisTable WHERE uniqueID = " + id; — JohnnyAW
– JohnnyAW, Commented Sep 5, 2013 at 14:16
Amazing. Simple and effective! Thank you very much. (Thank you @SotiriosDelimanolis for your brilliant answer too!) — user2751003
– user2751003, Commented Sep 5, 2013 at 14:24

M Abbas · Accepted Answer · 2013-09-05 14:42:01Z

1

You should pass the user id as parameter to the ShowResults method and use PreparedStatement instead of Statement to prevent SQL Injection attacks.

public ArrayList<String> ShowResults(Integer userId) {
    ...
    ...
    ...
    PreparedStatement preStatement = connection.prepareStatement("SELECT * FROM thisTable WHERE uniqueID = ?");
    preStatement.setInt(1, userId);
    ResultSet rs = preStatement.executeQuery();
    ...
    ...
    ...
}

edited Sep 5, 2013 at 14:42

answered Sep 5, 2013 at 14:21

M Abbas

6,4994 gold badges37 silver badges48 bronze badges

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

3 Comments

user2751003 Over a year ago

Thank you for your answer. I'm getting an error on trying that, saying that "The method setInteger(Integer) is undefined for the type PreparedStatement".

user2751003 Over a year ago

Thanks for your help. I'm getting a different error now; "The method setInt(int, int) in the type PreparedStatement is not applicable for the arguments (Integer)"

user2751003 Over a year ago

OK, now I'm getting an error ... ha ha, only kidding! That works perfectly, thanks a million for your perseverance!

Collectives™ on Stack Overflow

Java how to search SQL from user input to method

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related