CODEWITHSUNDEEP
javasqlitejdbc
derek
derek

Reputation: 123

How to use user input as parameter for query?

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)

Upvotes: 0

Views: 16311

Answers (2)

howiewylie
howiewylie

Reputation: 171

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.

Upvotes: 1

Jayant Jadhav
Jayant Jadhav

Reputation: 318

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"));
        }
 }

Upvotes: 2

Related Questions