Data can be stored in database tables also. This recipe demonstrates how we can read data from a table in MySQL.
In order to perform this recipe, we will require the following:
Download and install MySQL community server from http://dev.mysql.com/downloads/mysql/. The version used in this recipe is 5.7.15.
Create a database named
data_science. In this database, create a table namedbooksthat 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.
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.
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){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);In a
tryblock, create a connection for the database. Using the connection, create a statement that will be used to execute aSELECTquery 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");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); }Close the result set, the statement, and connection after iteration:
rs.close(); stmt.close(); conn.close();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.