Using variables to create SQL statements

Question

I'm trying to make a sql query builder type program that uses user input data to build custom queries for the table

so far i have

public int checkBetweenDates() throws SQLException{
        String t1 = "2015-07-08"; //or later some user input variable
        String t2 = "2015-07-09";//or later some user input variable
        String id = "22 03 E7 99";//or later some user input variable
        int rowCount = -1;
        //Statement stmt = null;        

        String dateChoice = "select count(*) " 
                + "from dancers " 
                + "where ts between (t1) and (t2)"
                + "and id = (id)"
                + "values (?)";

        Connection conn = DriverManager.getConnection(host, username, password);
        System.out.println("Connected:");
         PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(dateChoice);
         preparedStmt.setString    (1, t1);
//       preparedStmt.setString    (2, t2);
//       preparedStmt.setString    (3, id);
        // stmt = conn.createStatement();
        ResultSet rs = preparedStmt.executeQuery(dateChoice); 
        try {
                rs = preparedStmt.executeQuery(dateChoice);
                rs.next();
                rowCount = rs.getInt(1);
                System.out.println(rowCount);
            }
         catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            rs.close();
            preparedStmt.close();
        }
        return rowCount; 

    }

So it connects and everything fine but it doesnt execute the query saying something wrong with the sql syntax for values(?,?,?)

Any help would be awesome thanks guys!!

Carl

there are syntax differences between sql server and mysql. which one will you target with your tool? moreover which server gave you the above error? mysql or sql server? — Paolo
– Paolo, Commented Jul 10, 2015 at 10:47
Like @Mini pointed out, the SQL is not proper. Two problems i see 1. t1 and t2 are strings but your code is not concatenating it and using directly. 2. values() is for insert and not for select. Suggest to run it at SQL prompt before executing via JDBC. On a different note, in current day world, JPA (with EclipseLink/Hibernate) provides a darn good support for static and dynamic queries. My two cents is to look at those instead of trying to come up with your own. — Rakesh Kumar Cherukuri
– Rakesh Kumar Cherukuri, Commented Jul 10, 2015 at 10:57

Amit · Accepted Answer · 2015-07-10 10:56:05Z

Try this, Changes in query and in setting prepared statement parameters,

public int checkBetweenDates() throws SQLException{
        String t1 = "2015-07-08"; //or later some user input variable
        String t2 = "2015-07-09";//or later some user input variable
        String id = "22 03 E7 99";//or later some user input variable
        int rowCount = -1;
        //Statement stmt = null;        

        String dateChoice = "select count(*) " 
                + "from dancers " 
                + "where ts between ? and ?"
                + "AND id = ?";

        Connection conn = DriverManager.getConnection(host, username, password);
        System.out.println("Connected:");
         PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(dateChoice);
       preparedStmt.setString    (1, t1);
       preparedStmt.setString    (2, t2);
       preparedStmt.setString    (3, id);
        // stmt = conn.createStatement();
        ResultSet rs = preparedStmt.executeQuery(dateChoice); 
        try {
                rs = preparedStmt.executeQuery(dateChoice);
                rs.next();
                rowCount = rs.getInt(1);
                System.out.println(rowCount);
            }
         catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            rs.close();
            preparedStmt.close();
        }
        return rowCount; 

    }

Share the exact error if doesn't work for you.

George_v · Accepted Answer · 2015-07-10 10:53:33Z

0

Change this:

String dateChoice = "select count(*) " 
            + "from dancers " 
            + "where ts between (t1) and (t2)"
            + "and id = (id)"
            + "values (?)";

According to the database syntax that you are using. For example if you using a webserver with Mysql go and type the query to see where the typo is. (if you using mysql it needs dancers to every table)

answered Jul 10, 2015 at 10:53

George_v

837 bronze badges

Comments

gary · Accepted Answer · 2015-07-10 11:03:58Z

0

First, you seem to have edited this method many times to try fix the problem, which has left it in a confused state.

remove the "values (?)" from the sql statement, it does not belong here, it seems to be left over from a prepared insert statement.

call preparedStmt.executeQuery() with zero arguments, you have already supplied it with the sql string and only call it ONCE, you assign a value to rs twice.

your sql statement should contain exactly three question marks, try

select count(*) from dancers where ts between ? and ? and id = ?

next call preparedStmt.setString() three times to supply values t1, t2 and id.

Also, remember to close the connection object in the finally block.

answered Jul 10, 2015 at 11:03

gary

5194 silver badges11 bronze badges

Collectives™ on Stack Overflow

Using variables to create SQL statements

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related