Reading table data from a MySQL database

Data can be stored in database tables also. This recipe demonstrates how we can read data from a table in MySQL.

Getting ready

In order to perform this recipe, we will require the following:

1. Download and install MySQL community server from http://dev.mysql.com/downloads/mysql/. The version used in this recipe is 5.7.15.
2. Create a database named data_science. In this database, create a table named books that contains data as follows:

   

   The choice of the field types does not matter for this recipe, but the names of the fields need to exactly match those from the exhibit shown here.

3. Download the platform independent MySql JAR file from http://dev.mysql.com/downloads/connector/j/, and add it an external library into your Java project. The version used in this recipe is 5.1.39.

How to do it...

1. Create a method as public void readTable(String user, String password, String server) that will take the user name, password, and server name for your MySQL database as parameters:

           public void readTable(String user, String password, String   
             server){ 
   

2. Create a MySQL data source, and using the data source, set the user name, password, and server name:

           MysqlDataSource dataSource = new MysqlDataSource(); 
             dataSource.setUser(user); 
             dataSource.setPassword(password); 
             dataSource.setServerName(server); 
   

3. In a try block, create a connection for the database. Using the connection, create a statement that will be used to execute a SELECT query to get information from the table. The results of the query will be stored in a result set:

           try{ 
             Connection conn = dataSource.getConnection(); 
             Statement stmt = conn.createStatement(); 
             ResultSet rs = stmt.executeQuery("SELECT * FROM  
               data_science.books"); 
   

4. Now, iterate over the result set, and retrieve each column data by mentioning the column name. Note the use of the method that gives us the data you need to know the field type before you can use them. For instance, as we know that the ID filed is integer, we are able to use the getInt() method:

           while (rs.next()){ 
             int id = rs.getInt("id"); 
             String book = rs.getString("book_name"); 
             String author = rs.getString("author_name"); 
             Date dateCreated = rs.getDate("date_created"); 
             System.out.format("%s, %s, %s, %sn", id, book, author, 
               dateCreated); 
           }

5. Close the result set, the statement, and connection after iteration:

           rs.close(); 
             stmt.close(); 
             conn.close(); 
   

6. Catch some exceptions as you can have during this reading data from the table and close the method:

        }catch (Exception e){ 
           //Your exception handling mechanism goes here. 
          } 
        } 

The complete method, the class, and the driver method to execute the method are as follows:

import java.sql.*; 
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; 
public class TestDB{ 
     public static void main(String[] args){ 
          TestDB test = new TestDB(); 
          test.readTable("your user name", "your password", "your MySQL 
              server name"); 
     } 
     public void readTable(String user, String password, String server) 
         { 
          MysqlDataSource dataSource = new MysqlDataSource(); 
          dataSource.setUser(user); 
          dataSource.setPassword(password); 
          dataSource.setServerName(server); 
          try{ 
               Connection conn = dataSource.getConnection(); 
               Statement stmt = conn.createStatement(); 
               ResultSet rs = stmt.executeQuery("SELECT * FROM 
                   data_science.books"); 
               while (rs.next()){ 
                    int id = rs.getInt("id"); 
                    String book = rs.getString("book_name"); 
                    String author = rs.getString("author_name"); 
                    Date dateCreated = rs.getDate("date_created"); 
                    System.out.format("%s, %s, %s, %sn", id, book, 
                        author, dateCreated); 
               } 
               rs.close(); 
               stmt.close(); 
               conn.close(); 
          }catch (Exception e){ 
               //Your exception handling mechanism goes here. 
          } 
     } 
} 

This code displays the data in the table that you created.