How to use user input as parameter for query?

Question

So I'm very new to java and SQL and they are my first programming languages. I am trying to do some work with JDBC. I want to allow for a user to input an id and return a query based on the variable. If someone could at least point me in the right direction... Here is the code I'm starting with. Mind you its crude but just trying to get a working piece of code so I can better implement it in my main class.

 Scanner input = new Scanner(System.in);
    Class.forName("org.sqlite.JDBC");
    Connection conn =
            DriverManager.getConnection("jdbc:sqlite:C:\\Users\\Derek\\Documents\\Databases\\example.sqlite");
    Statement stat = conn.createStatement();
    PreparedStatement prep1 = conn.prepareStatement(
            "insert into MedType values (?, ?);");
    PreparedStatement prep2 = conn.prepareStatement(
            "insert into Media values (?, ?,?, ?,?, ?);");

    System.out.print("Please choose a database(MedType or Media): ");
    String db=input.next();

    if(db.equalsIgnoreCase("MedType"))
    {
    System.out.print("Enter in ID: ");
    String answer1 = input.next();

    System.out.print("");
    String answer2 = input.nextLine();

    System.out.print("Enter in Description: ");
    String answer3 = input.nextLine();

    prep1.setString(1, answer1);//add values into cell
    prep1.setString(2, answer3);
    prep1.addBatch();//add the columns that have been entered

    }
conn.setAutoCommit(false);
    prep1.executeBatch();
    prep2.executeBatch();
    conn.setAutoCommit(true);




    System.out.print("Please Enter Query(One or All): ");
    String q=input.next();


    ResultSet rs= stat.executeQuery("select * from MedType;");


    if(q.equalsIgnoreCase("all")){
    while (rs.next()) {
        System.out.print("All ID = " + rs.getString("ID") + " ");
        System.out.println("All Description = " + rs.getString("Description"));}
    }
   if(q.equalsIgnoreCase("one")){
         System.out.print("Enter ID: ");

    }
   int idNum=input.nextInt();
    ResultSet oneRs = stat.executeQuery("select * from MedType Where"+ (rs.getString("ID")+"="+idNum));

   if(q.equalsIgnoreCase("one")){


        while (oneRs.next()) {
            System.out.print("ID = " + oneRs.getString("ID") + " ");
            System.out.println("Description = " + oneRs.getString("Description"));
        }
    }

    rs.close();
    oneRs.close();
    conn.close();

}
}

ResultSet oneRs = stat.executeQuery("select * from MedType Where"+
   (rs.getString("ID")+"="+idNum));

This is where I'm having trouble. Creating a statement that says return something from the table if its id is equal to the user input. I get this error

Exception in thread "main" java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "=": syntax error)

yes. and all the code works if i use a static number instead of trying to use user input. ID is a column in the MedType table. idNum is the string stored in the Scanner method after it asks for user to input ID to search for — derek
– derek, Commented May 3, 2013 at 8:32
Probabluy a space is missing between "where"+ (rs.getString("ID") ? — AllTooSir
– AllTooSir, Commented May 3, 2013 at 8:33
noob unchained helped move the problem along. there needed to be a space after where for it to separate the returned result from the rest of the string. however it still does not output the query — derek
– derek, Commented May 3, 2013 at 8:41

Jayant Jadhav · Accepted Answer · 2013-05-03 09:59:21Z

In query you are trying to access single row by passing id.. In generally sql query we are using to access single row by passing some information. select * from MedType where id=3 this query will return you result set containing row or rows with id equals to 3. so in your code your query should be select * from MedType where id="+idNum+" if in your db id column is int. and keep this query in if block only i.e

 if(q.equalsIgnoreCase("one"))
{
         System.out.print("Enter ID: ");
         int idNum=input.nextInt();
    ResultSet oneRs = stat.executeQuery("select * from MedType Where id="+idNum+" ");
    // if id column in db is int if it is string then use id='"+idNum+"'                                            

        while (oneRs.next())
       {
            System.out.print("ID = " + oneRs.getString("ID") + " ");
            System.out.println("Description = " + oneRs.getString("Description"));
        }
 }

Donal Fellows · Accepted Answer · 2014-01-16 11:20:06Z

1

In your query:

select * from MedType Where"+ (rs.getString("ID")+"="+idNum

you seem to try to grab the ID from the first resultset where you return all tuples.

That won't work as in the where clause the ID won't be there (as there is no result right now without rs.next()). If there was a result then you potentially have something like 'where 3 = 3' (3 would be the result of the previously returned value. Have you tried simply to use:

select * from MedType Where ID = " + idNum

Hope that makes sense.

edited Jan 16, 2014 at 11:20

Donal Fellows

139k19 gold badges161 silver badges222 bronze badges

answered May 3, 2013 at 9:19

howiewylie

1714 bronze badges

1 Comment

derek Over a year ago

ResultSet oneRs = stat.executeQuery("select * from MedType Where "+ ("ID"+"="+idNum)); I tried this first and it kept saying the syntax was wrong. i dont think i had the = in quotes. thank you very much.

Collectives™ on Stack Overflow

How to use user input as parameter for query?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related