Java: how to print the rows from database?

Question

I have this program:

class DataRetrieve {

    DataRetrieve() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/phonebook", "root", "1234");
            Statement st = con.createStatement();
            st.executeQuery("select * from contacts");
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
}

public class MainProgram {

    public static void main(String[] args) {
        DataRetrieve dr = new DataRetrieve();
        //here i want to print that table rows into Console using this
        System.out.println(); // How do you print here that table rows?
    }
}

Can anyone explain how to print this database information in System.out.println?

You'll probably want to start with some introductory Java tutorials. Your `DataRetrieve` class doesn't provide any way to get any data within it. It executes a query in the constructor, but doesn't store the results anywhere outside the scope of that constructor. Nor does it have any other methods for accessing such stored data. — David, Jul 24 '14 at 11:33
Check out the answer from Frans in this post: http://stackoverflow.com/questions/10903206/enabling-mysql-general-query-log-with-jdbc — JamesB, Jul 24 '14 at 11:41

user2640782 · Accepted Answer · 2014-07-24T11:56:39.890

You can create a ResultSet.

ResultSet rs = st.executeQuery("select * from contacts");

Then you can iterate over ResultSet, and get the rows.

while (rs.next()) {
    System.out.println(rs.getString(1)); //gets the first column's rows.
}

To get all the column's datas:

ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();

while (rs.next()) {
    for(int i = 1; i < columnsNumber; i++)
        System.out.print(rs.getString(i) + " ");
    System.out.println();
}

If you want to print the database information from MainProgram calls, you can return the ResultSet and iterate over it in your main method.

In this case you should create a method in MainProgram.

class DataRetrieve {

    DataRetrieve() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/phonebook", "root", "1234");
            Statement st = con.createStatement();
            rs = st.executeQuery("select * from contacts");
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

    public ResultSet getResultSet() {
          return rs;
    }
private ResultSet rs = null;
}

And in your main method:

public static void main(String[] args) {
        DataRetrieve dr = new DataRetrieve();
        //here i want to print that table rows into Console using this
        System.out.println(); // How do you print here that table rows?

        ResultSet rs = dr.getResultSet();
        while (rs.next()) {
            System.out.println(rs.getString(1)); //gets the first column's rows.
        }
}

Small Issue Identified: The for loop should be changed from: for(int i = 1; i < columnsNumber; i++) to for(int i = 1; i <= columnsNumber; i++) This will allow the last column from the db query to be returned. — bitShredder, Jan 11 '20 at 22:20

score 2 · Answer 2 · edited Jul 24 '14 at 11:48

Use the following code:

class DataRetrieve {

    DataRetrieve() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/phonebook", "root", "1234");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from contacts");
            System.out.println(rs.getInt(1));
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
}

public class MainProgram {

    public static void main(String[] args) {
        DataRetrieve dr = new DataRetrieve();
        //here i want to print that table rows into Console using this
        System.out.println(); // How do you print here that table rows ?
    }
}

Well I have caught the rows returned from database in ResultSet instance. You can use getInt(xxx), getString(xxx) and etc to get the related values and then to print them.

It's better to show full example with printing out values in `MainProgram.main` — Regent, Jul 24 '14 at 11:49

SparkOn · Answer 3 · 2014-07-24T12:15:02.680

One basic fault you did is opened the connection but never closed it. Its not a good practice you should always close it for releasing the resources.

In your code first st.executeQuery("select * from contacts"); will return a ResultSet object what you need to do is just iterate the ResultSet and get the rows.

DataRetrieve() {
try {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("yourUrl", "userName", "password");
    Statement st = con.createStatement();
    ResultSet resultSet = st.executeQuery("select * from contacts");
    while(resultSet.next())
        {
        String columnName1= rs.getString("nameOfColumn1");
        int columnName2= rs.getInt("nameOfColumn2");
        float columnName3= rs.getFloat("nameOfColumn3");
        System.out.println(columnName1+"\t"+ columnName2+"\t"+ columnName3);
      }
  }
}
catch(SQLException e) {
     e.printStackTrace();
}
finally{
   //dont forget the closing statements
 }

Java: how to print the rows from database?

3 Answers3