how to retrieve data from database correctly in java

Question

I wrote this code in order to display the name of employee who has the maximum salary but when the output wasn't correct it appeared null not "mmm kkk" !! although I filled the table and this is the contents :

HERE

this is my code, can any one help me ?? :(

 public static void displayMaxSalary() throws ClassNotFoundException, SQLException

{   
    int size=0;
    int count=0;
    String maxSalary=null;
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/task4DB?"
              + "user=root&password=123");
    PreparedStatement st = con.prepareStatement("select * from task4Table ");
    ResultSet r1=st.executeQuery();

      while (r1.next()) {

          size++;
      }


      int salaries[]=new int[size];
      String Names[]=new String[size];

       while (r1.next()) {


          salaries[count]= r1.getInt("salary");
          Names[count]=  r1.getString("fName")+""+r1.getString("lName");
          count++;
      }

       for(int i=1;i< salaries.length;i++)

       {
           if(salaries[i]>salaries[i-1])
           {
               maxSalary= Names[i];
           }

       }


     System.out.println("The name of employee who has the higher salary is :");
     System.out.println( maxSalary);



} //end-displayMaxSalary.

darijan · Accepted Answer · 2013-06-19 09:50:30Z

5

Try this SQL statement:

select fName, max(salary) from task4table

A problem with your code:

In this loop you iterate to the end of the result set:

while (r1.next()) {
    size++;
}

And then, when you want to iterate again

while (r1.next()) {
    salaries[count]= r1.getInt("salary");
    Names[count]=  r1.getString("fName")+""+r1.getString("lName");
    count++;
}

r1.next() returns false so this iteration does not happen. You either do it in one loop, or you execute query again. But, as I said, to it with proper sql statement using max.

answered Jun 19, 2013 at 9:50

darijan

9,80327 silver badges38 bronze badges

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

1 Comment

Akari Over a year ago

but this query gave me the name of minimum salary ??

Manoj · Accepted Answer · 2013-06-19 10:05:52Z

3

You change query to get all maximum salary persons in single query

PreparedStatement st = con.prepareStatement("
select fName, lName, salary from task4Table where salary = 
(select max(salary) from task4Table)");
ResultSet r1 = st.executeQuery();

Iterate this resultset.

answered Jun 19, 2013 at 10:05

Manoj

3292 gold badges6 silver badges15 bronze badges

Comments

Talha · Accepted Answer · 2013-06-19 10:21:59Z

2

Try following code:

public static void displayMaxSalary() throws ClassNotFoundException, SQLException

{   

    String maxSalary;
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/task4DB?"
              + "user=root&password=123");
    PreparedStatement st = con.prepareStatement("select * from task4Table order by salary desc limit 1");
    ResultSet r1=st.executeQuery();

     if(r1.next()) {

           maxSalary =  r1.getString("fName")+""+r1.getString("lName");
      }

     System.out.println("The name of employee who has the higher salary is :");
     System.out.println( maxSalary);


} //end-displayMaxSalary.

answered Jun 19, 2013 at 10:21

Talha

6992 gold badges13 silver badges33 bronze badges

Comments

duffymo · Accepted Answer · 2013-06-19 09:54:27Z

2

There's a lot wrong with your code, but here's your immediate problem: you aren't finding the max correctly.

The right way is to use SQL, as the answer above mine shows. If you insist on going your way, try this:

int maxSalary = Integer.MIN_VALUE;
for (int i = 0; i < salaries.length; i++) {
    if (salaries[i] > maxSalary) {
        maxSalary = salaries[i];
    }
}

Your coding style is not good. You need to be consistent about brace placement, spaces, blank lines, etc. You should also memorize and follow the Sun Java coding conventions.

edited Jun 19, 2013 at 9:54

answered Jun 19, 2013 at 9:50

duffymo

310k46 gold badges376 silver badges571 bronze badges

Collectives™ on Stack Overflow

how to retrieve data from database correctly in java

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related