sql and java querying data

Question

I have two tables, one contains college name and college id while another contains year wise enrollment of students in different colleges.

What I want is to print the number of students enrolled in a year for different colleges and total number of students enrolled every year.

Currently what I'm doing is getting college id from first table and then running a for loop for year and counting the number of students.

What is happening with this is inside for loop every time a new PreparedStatement is being created, which I feel is little expensive.

Is there a better way of running a SQL query to get the required data.

My code that is being currently used is below

PreparedStatement pstmt = conn.prepareStatement("select id from college where type_flag = '1' order by full_form");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
 for (int i = 1964; i < 2019; i++) {
 String pstmt11 = "select count(*) from students where id=" + rs.getInt("id") + "AND year = " + i;
 //System.out.println(pstmt11);
 PreparedStatement pstmt1 = conn.prepareStatement(pstmt11);
 //pstmt1.setInt(1, rs.getInt("case_type"));
 ResultSet rs1 = pstmt1.executeQuery();
 while (rs1.next()) {
 //Here I am getting the count
 }

Similarly for getting year wise count I am doing the same thing.

• Aniket Sahrawat: year BETWEEN 1964 AND 2019

Answer 1

Your usage of a PreparedStatement is not really using a prepared statement. You should only prepare it once with placeholders for the values then use setInt() to pass the values.

PreparedStatement pstmt = conn.prepareStatement("select id from college where type_flag = '1' order by full_form");
PreparedStatement pstmt1 = conn.prepareStatement("select count(*) from students where id=? and year=?");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
 for (int i = 1964; i < 2019; i++) {
 int id = rs.getInt("id");
 pstmt1.setInt(1, id);
 pstmt2.setInt(2, i);
 ResultSet rs1 = pstmt1.executeQuery();
 while (rs1.next()) {
 //Here I am getting the count
 }
 }
}

You should also use a placeholder for the first statement.

---

There is however no need for a loop or two statements. What you want can be done with a single query:

select id, year, count(*)
from students
where id in (select id 
 from college
 where type_flag = '1')
and year between 1964 and 2018
group by id, year;

---

I am surprised that college.id equals the id column in the students table. From a naming perspective I would have expected a students.college_id column, and students.id being the primary key of the students table identifying the student, not the college.

• Mark Rotteveel: Given the original code, your query should probably use select id, year, count() and group by id, year (at least to answer the "number of students enrolled in a year for different colleges", the current query answers "total number of students enrolled every year").
• vidhu: @ I had tried using the query suggested by you bt i was only using group by year but it was only returning 71 rows..can you suggest as what goes wrong when i use only group by year...secondly the id column in student table does refer to id column in college table
• user330315: @vidhu: you should ask a new question for that - providing sample data and the expected results.
• Aniket Sahrawat: @vidhu You need to start accepting answers. There is a green tick, hit it.