How to retrieve all the name from MySQL?


Problem :

I want to retrieve all the name and the number of row from MySQL to java. So far I only able to retrieve the total row number but I only get the last name. What's wrong here ?

StaffManagement.java

adminAPI api= new adminAPI();
try {
      int num= api.displayCheckBoxAndLabel();
      String allName= api.displayName();
      System.out.println(num+allName);
}

adminAPI

public int displayCheckBoxAndLabel() throws Exception // get the number of row 
    {
        int count = 0;
        String sql="Select count(*) AS adminID from admin";
        DatabaseConnection db = new DatabaseConnection();
        Connection  conn =db.getConnection();
        PreparedStatement  ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();
         while(rs.next()) 
         {  
             count= rs.getInt("adminID");

         }
         ps.close();
         rs.close();
         conn.close();
         return count ;
    }

    public String displayName() throws Exception // get all the name 
    {
        String name = null;
        String sql="Select name from admin";
        DatabaseConnection db = new DatabaseConnection();
        Connection  conn =db.getConnection();
        PreparedStatement  ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();
         while(rs.next()) 
         {  
             name= rs.getString("name");

         }
         ps.close();
         rs.close();
         conn.close();
         return name ;
    }


Solution :

You currently return a single String, and your method iterates all of the admin names (but terminates after the final row, so that's your result). Instead, build a List of names and return that. You could also use a try-with-resources close to close your Connection, Statement and ResultSet instances. Something like

public List<String> displayName() throws Exception // get all the name
{
    String sql = "Select name from admin";
    List<String> names = new ArrayList<>();
    DatabaseConnection db = new DatabaseConnection();
    try (Connection conn = db.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            names.add(rs.getString("name"));
        }
    }
    return names;
}

Mysql Tutorials

Mysql Howto..

how to check where mysql dababase exists from rails env?

How to write a mysql query to meet below requirements?

How to convert the now() to timestamp

How do I make a SQL table from the result of another select?

How to import csv file into mysql by phpmyadmin

how to host mysql database with asp.net website [closed]

how to make date looking at jsp only yyyy-mm-dd format - truncate time

MySQL tagging question: how to select an item that has been tagged as X, Y, and Z?

How to make MySQL group by choose one value before another?

How to connect to MySQL Database from eMbedded Visual C++

How to store comments in a site?

How to parse a string to insert a row into a mysql table?

How to search inside multiple tables in MySQL

MySQL: How to select distinct values from a table?

How to calculate AVG of generated query in mySQL?

How to filter for a specific set of characters ending with any digit?

How to create MySQL prepared statement string without connecting to database

How to get query plan for a running process in mysql

How to run a mysql loop from a posted array and extract results to a CSV file

How to get 7 days before from today date in mysql using query?

How do I create a sequence in MySQL?

How to hide a column in mysql

mysql show how many connections to a table

View counter - how to only count up once per user? - PHP / Laravel 4

How to set autoreconnect option with mysql connector c++

php has been upgraded and all links show the index page, but the link titles display the correct format

How can I tell the raw size of a MySQL DB snapshots in Amazon RDS?

How to insert the last id from a table into another table in MySQL?

How to split string with date as delimiter?

How to format the DateTimePicker into hh:mm:ss and What is the appropriate type to save time in mysql?