Java / database query : Retrieving multiple items

Question

I have code to retrieve a database item and it displays that in a mobile application using J2ME. I also use JSP for this so that my mobile app can get information from this.

I want to know how I can retrieve multiple items??

JavaBean:

public String doQuery() throws ClassNotFoundException, SQLException {
     //register driver
    DriverManager.registerDriver(new com.mysql.jdbc.Driver());

     //establish connection
    Conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/a1electric?user=root&password=raam030");

     //Create a Statement object from the Connection
    Statement stmt = Conn.createStatement();

    String sql = "SELECT JobID FROM employee WHERE employeeID=" +this.jobID;
    ResultSet rs = stmt.executeQuery(sql);
    String rt = "";
    rs.next();
    rt =  rs.getString("JobID");
    Conn.close();
    return rt;
   }

JSP Page:

  <jsp:useBean id="bean0" scope="session" class="data.queryBean"/>
<jsp:setProperty name="bean0" property="jobID" param="jobID"/>
<%= bean0.doQuery() %>

I would like to retrieve all the job IDs for this employee ID and display it.

Vikdor · Accepted Answer · 2012-11-14 04:33:14Z

6

If there are multiple JobIDs for a given EmployeeID, then your result set has all those items and you should browse through the resultset:

String sql = "SELECT JobID FROM employee WHERE employeeID=" +this.jobID;
ResultSet rs = stmt.executeQuery(sql);
List<String> jobIds = new ArrayList<String>();
while (rs.next()) {
    jobIds.add(rs.getString("JobID"));
}

On a related note, you should

try to use a connection pool (apache DBCP), instead of registering the driver and connecting to the DB everytime.

use PreparedStatement to avoid potential SQL injection attacks:

PreparedStatement stmt = con.prepareStatement(
    "SELECT JobID FROM employee WHERE employeeID= ?");
stmt.setInt(1, this.jobID);
ResultSet rs = stmt.executeQuery();

answered Nov 14, 2012 at 4:33

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

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

1 Comment

Ramesh Sivaraman Over a year ago

Thank you for that.. This is for my college project so I am not concerned about the security part at this time.

Collectives™ on Stack Overflow

Java / database query : Retrieving multiple items

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related