CODEWITHSUNDEEP
javamysql
user1937623
user1937623

Reputation: 15

Taking data from mysql with java

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);

Upvotes: 0

Views: 93

Answers (1)

Sotirios Delimanolis
Sotirios Delimanolis

Reputation: 280178

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.

Upvotes: 1

Related Questions