Java- select certain rows from column with SQLite

Question

Sorry if the title is not precise. I am using a custom class to get data from a SQLite database.

For example: the method below is supposed to return list of users, which are members of a certain department. Each user in the USER table has a column with id of the department he belongs to.

At the moment I am getting all the users and then comparing their department IDs to the targetID of the department I am looking for. Is there a way to get just the set of users that have a particular department ID, so that I don't have to check each one's department id?

private List<User> getDepartmentMembers(int targetID) {
    List<User> members = new ArrayList<User>();
    Connection c = null;
    Statement statement = null;
    try {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:TheatroData.sqlite");
        c.setAutoCommit(false);
        statement = c.createStatement();
        ResultSet rs = statement.executeQuery( "SELECT * FROM USERS;" );
        while ( rs.next() ) {
            int  id = rs.getInt(Constants.ID_KEY); 
            if (id == targetID ){
                User tmp = null;
                int position = rs.getInt(Constants.POSITION_KEY);

                if (position == Constants.DEPARTMENT_HEAD)
                    tmp = new DepartmentHead();
                else if (position == Constants.DEPARTMENT_MANAGER)
                    tmp = new DepartmentManager();
                else if (position == Constants.DEPARTMENT_MEMBER);
                tmp = new GruntUser();

                tmp.setID(id);
                tmp.setName(rs.getString(Constants.NAME_KEY));
                tmp.setPosition(position);
                tmp.setUsername(rs.getString(Constants.USERNAME_KEY));
                tmp.setLastname(rs.getString(Constants.SURNAME_KEY));
                tmp.setDepartment(targetID);
                tmp.setPassword(rs.getString(Constants.PASS_KEY));

                members.add(tmp);
            }
        }
        rs.close();
        statement.close();
        c.close();
    } catch ( Exception e ) {
        System.err.println( e + " -in getDepartmentMembers" + e.getClass().getName() + ": " + e.getMessage());
    }
    return members;
}

I was thinking I need something like this:

 ResultSet rs = statement.executeQuery( "SELECT * FROM USERS where department = ?;", targetID );

You were thinking correctly: pass the target ID as part of the where clause for the query and execute it in SQL and delete the logic comparing the IDs in Java. Is it not working or something? You answered your own question. — Code Whisperer
– Code Whisperer, Commented Apr 13, 2015 at 15:34
Are you looking for users from all departments or just one particular department. If just one department then your thinking is correct. — Blip
– Blip, Commented Apr 13, 2015 at 15:37
Oh yes I see, thank you- the correct way was to pass it inside of the statement. ResultSet rs = statement.executeQuery( "SELECT * FROM USERS where department = "+targetID+";"); — CluelessStudent
– CluelessStudent, Commented Apr 13, 2015 at 15:38

Juergen · Accepted Answer · 2015-04-13 16:07:40Z

1

In an ideal world, you could do it as you wrote:

ResultSet rs = statement.executeQuery( "SELECT * FROM USERS WHERE department = ?;", targetID );

But, executeQuery from JDBC does currently not provide the possibility for argument binding. So you have to use "Prepared Statements".

Instead of

statement = c.createStatement();
ResultSet rs = statement.executeQuery( "SELECT * FROM USERS;" );

do:

prepared = c.prepareStatement("SELECT * FROM USERS WHERE department = ?;");
prepared.setString(1, targetID);
ResultSet rs = prepared.executeQuery();

When you need more than one parameter, you can use a different syntax for replacing it, for example "?001". See SQLite Documentation: C/C++ Interface Section 5.

Also remove the Java coding for your own selection of the right department.

Since the CluelessStudent presented a different solution, involving string concatenation, I want to say the following:

I would definitively discourage string concatenation! You always should use argument binding and not string concatenation! String concatenation is a huge security risk, since it can be used for so called "SQL injection attacks". See Wikipedia: SQL Injection

edited Apr 13, 2015 at 16:07

answered Apr 13, 2015 at 15:42

Juergen

12.8k7 gold badges43 silver badges56 bronze badges

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

5 Comments

CluelessStudent Over a year ago

Actually that didnt work, because executeQuery only takes a String apparently. It has to passed within the statement as: ResultSet rs = statement.executeQuery( "SELECT * FROM USERS where department = "+targetID+";");

Juergen Over a year ago

Thanks, but than I guess, that there is a different method available to use argument binding. What you do here, is string concatenation and that is widely discouraged today. When a library does only provide string concatenation for that, you should step away from it!

Juergen Over a year ago

@CluelessStudent: My newest example should work. You have to use prepared statements, since JDBC does not support argument binding for direct statements.

CluelessStudent Over a year ago

Oh I didn't know that- this is first time I am doing something with a database. Thank you for the help! :)

Juergen Over a year ago

@CluelessStudent: You are welcome. Thanks for the upvote. We all learn. I did not know JDBC either, but I use SQLite from other languages. String concatenation was the only method used in old PHP versions and that is one of the reasons, that many PHP applications have so many security holes. Of course, when you are totally sure that your argument values are correct (and are not provided from user input), you could use string concatenation, but I see it as good practice to avoid it. Also you can make your programs faster, since you can prepare once and use the prepared statement multiple times.

user4341206 · Accepted Answer · 2015-04-13 15:41:36Z

1

Yes you pratcially answered your own question. You can also do like this.

String query = "SELECT * FROM USERS where department = (?)";
PreparedStatement statement = c.prepareStatement(sql);
statement.setInt(1, targetId);
ResultSet rs = statement.executeQuery();
while(rs.next()){
   //you get only records that have id = targetId
}
//close rs, statement and connection!!!

answered Apr 13, 2015 at 15:41

user4341206

6451 gold badge7 silver badges27 bronze badges

Comments

CluelessStudent · Accepted Answer · 2015-04-13 15:40:52Z

0

I was just passing a wrong statement. The correct way:

ResultSet rs = statement.executeQuery( "SELECT * FROM USERS where department = "+targetID+";");

answered Apr 13, 2015 at 15:40

CluelessStudent

371 silver badge8 bronze badges

1 Comment

Juergen Over a year ago

Since I am not using Java so much, I don't know, if the original statement really works. But I would definitively discourage this usage! You always should use argument binding and not string concatenation! String concatenation is a huge security risk, since it can be used for so called "SQL injection attacks".

Collectives™ on Stack Overflow

Java- select certain rows from column with SQLite

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related