Creating SQL based on Array list

Question

AIM: I am trying to create and run an sql query the same number or time as I have values in my Array List and incoorperate them into the SQL

Array List: //This will get all the team members names and add them to the array list

ResultSet rs = name.executeQuery("SELECT name from TEAM_MEMBERS");
            while (rs.next()) 
              { 
                 al.add(rs.getString("name"));
              } 
            rs.close();

SQL to run for each member of team. Please note that the team members will change so this can't be static.

ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = "+al+"");
            while (rs1.next()) 
              { 
                 al1.add(rs1.getString(1));
              } 
            rs1.close();

Ideally I would like to loop through the values in the al array and as part of that loop insert the values from the al array int he second sql which would then add the result from those queries to another array al1.

I thought one solution would be add the al array count to a int and then use that to generate my loop and I would then insert the values in the loop via an X value with a basic increment ++ on it. However that seems a little messy and Im thinking there is better solution that I am not aware of.

Any suggestions of help is appreciated.

Cheers.

First suggestion: read more about relational databases and the table relations using foreign key, you will save lot of time having a key to join those tables instead of the `name` column. Second suggestion: you can use a single query to get the desired data using `JOIN` sentences. Third suggestion: **never** pass parameters by `String` concatenation, instead use a `PreparedStatement` to prevent [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. — Luiggi Mendoza, Apr 16 '13 at 05:49
Thats for the suggestions Luiggi. My SQL isn't great hence the question, do you any more information on how I would structure the JOIN command? — Rhys, Apr 16 '13 at 05:57
Answer provided showing a basic example about these and an additional suggestion. — Luiggi Mendoza, Apr 16 '13 at 06:10

score 0 · Answer 1 · answered Apr 16 '13 at 05:48

0

why you don't use view you can create view from both tables, and execute only one query

answered Apr 16 '13 at 05:48

aymankoo

653
6
12

score 0 · Accepted Answer · edited May 23 '17 at 12:15

First suggestion: read more about relational databases and the table relations using foreign key, you will save lot of time having a key to join those tables instead of the name column.

Second suggestion: you can use a single query to get the desired data using JOIN sentences.

Third suggestion: never pass parameters by String concatenation, instead use a PreparedStatement to prevent SQL Injection attacks.

Fourth suggestion: it would be better if you retrieve List<SomeClass> instead of plain ArrayList<String>. This is because you're programming to interfaces, not to implementation and your SomeClass can hold the attributes you need to retrieve.

Tying all these concepts, you will have something like this:

MySQL Syntax:

CREATE TABLE TEAM_MEMBERS
(id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL);

CREATE TABLE PROJECT_TIME
(id INT PRIMARY KEY AUTO_INCREMENT,
hours INT NOT NULL,
date DATE NOT NULL,
teamMemberId INT NOT NULL,
FOREIGN KEY (teamMemberId) REFERENCES TEAM_MEMBERS(id));

Base query using JOIN

SELECT tm.name, sum(pt.hours) AS hours -- retrieving both name and sum of hours at the same time
FROM
    TEAM_MEMBERS tm --team members table
    INNER JOIN PROJECT_TIME pt ON pt.teamMemberId = tm.id -- joining team members with project time table (not sure if this really makes sense, but is based on your code example)
WHERE
    DATE = <some_date> -- used to show where the parameter will go
GROUP BY
    tm.name -- used when using SUM or another AGGREGATE functions

Using this base query on Java side:

//sample about the class
public class TeamMember {
    private String name;
    private int projectHours;
    //constructor...
    public TeamMember() {
    }
    //constructor sending parameters (because I'm lazy when having a class with few parameters)
    public TeamMember(String name, int projectHours) {
        this.name = name;
        this.projectHours = projectHours;
    }
    //getter and setters...
}

List<TeamMember> teamMembers = new ArrayList<TeamMember>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
    con = ... //initialize the connection
    String query = "SELECT tm.name, sum(pt.hours) AS hours FROM TEAM_MEMBERS tm " +
        "INNER JOIN PROJECT_TIME pt ON pt.teamMemberId = tm.id " +
        "WHERE DATE = ? GROUP BY tm.name";
    pstmt = con.prepareStatement(query);
    pstmt.setString(1, date); //assuming you have your date in String format
    //pstmt.setDate(1, date); //you could use this if your date variable is java.util.Date
    rs = pstmt.execute();
    while(rs.next()) {
        teamMembers.add(new TeamMember(rs.getString("name"), rs.getInt("hours"));
    }
} catch (Exception e) {
    //handle the Exception
} finally {
    //close the resources (this is a MUST DO)...
    try {
        if (rs != null) {
            rs.close();
        }
    } catch (SQLException sqle) {}
    try {
        if (pstmt != null) {
            pstmt.close();
        }
    } catch (SQLException sqle) {}
    try {
        if (con != null) {
            con.close();
        }
    } catch (SQLException sqle) {}
}

Creating SQL based on Array list

2 Answers2