Taking data from mysql with java

Question

I am new to programming. I am trying to make a software. But i have problem.

The problem is : My software is school administration. So when i enter the student roll number in java i need to retrieve his information(name, age, etc) which i stored in mysql. But the code i entered always give the same information for whichever roll no i type. Please help

String LibraryCardNo = txtCard.getText();
 String firstName = "";
 String lastName = "";
 String Job = "";
 String Age = "";
 String LibraryCard = "";
 try{
 Class.forName("java.sql.Driver");
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/library?" + "user=root&password=ainkhalid"); 
 Statement s = con.createStatement();
 String query = "select * from application";
 ResultSet rs = s.executeQuery(query);
 while(rs.next()){
 firstName = rs.getString("firstName");
 lastName = rs.getString("lastName");
 Job = rs.getString("job");
 Age = rs.getString("age");
 LibraryCard = rs.getString("LibraryCardNo");
 }
 rs.close();
 s.close();
 con.close();
 }
 catch(Exception e){
 String msg = e.getMessage(); 
System.out.println("The problem is :"+msg); 
 }
 txtLast.setText(lastName);
 txtFirst.setText(firstName);
 txtJob.setText(Job);
 txtAge.setText(Age);

• Darius X.: "select * from application" should be something like: "select * from application where rollNumber = 787878"

Answer 1

That's because you are looping over the result, but only keeping a reference to the last ones

while(rs.next()){
 firstName = rs.getString("firstName");
 lastName = rs.getString("lastName");
 Job = rs.getString("job");
 Age = rs.getString("age");
 LibraryCard = rs.getString("LibraryCardNo");
}

The method ResultSet#next() returns true and moves its internal iterator as long as there is still data to read.

Try printing them right away or encapsulating all that data into a class, instantiating objects and adding them to a list, which you then print out.

public class Student {
 String firstName = "";
 String lastName = "";
 String job = "";
 String age = "";
 String libraryCard = "";
 // getters, setters, and constructors
}

And then

List<Student> students = new LinkedList<>();
while(rs.next()){
 firstName = rs.getString("firstName");
 lastName = rs.getString("lastName");
 Job = rs.getString("job");
 Age = rs.getString("age");
 LibraryCard = rs.getString("LibraryCardNo");
 Student student = new Student(firstName, lastName, Job, Age, LibraryCard);
 students.add(student);
}
// display them accordingly

Consider reading this official tutorial.

---

Obviously, if you only want to retrieve one row from the database, you'll have to switch up your query. Something like

 String query = "select * from application where roll = ?";

Use PreparedStatement and set values for the ? placeholder.