Life in code, apps and OS's

Gathering MySQL Records and displaying them in JAVA

Posted on: November 4, 2008

This tutorial will read all rows in a MySQL database and print out the resaults.

Create a new Java source file and save it as JdbcExample3.java in the same folder where you’ve been keeping others files for this tutorial. Now copy/paste following code in it:

package com.stardeveloper.example;

import java.sql.*;

public class JdbcExample3 {

  public static void main(String args[]) {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql:///examples",
        "root", "secret");

      st = con.createStatement();
      rs = st.executeQuery("SELECT user_id, first_name, last_name, " +
        "country_code FROM users");

      while(rs.next()) {
        int userId = rs.getInt(1);
        String firstName = rs.getString(2);
        String lastName = rs.getString(3);
        String countryCode = rs.getString(4);

        System.out.println(userId + ". " + lastName + ", " +
            firstName + " (" + countryCode + ")");
      }

    } catch (Exception e) {
      System.err.println("Exception: " + e.getMessage());
    } finally {
      try {
        if(rs != null)
          rs.close();
        if(st != null)
          st.close();
        if(con != null)
          con.close();
      } catch (SQLException e) {
      }
    }
  }
}

Explanation
First line is the package statement that tells the Java compiler that our JdbcExample3 class belongs to com.stardeveloper.example package.

package com.stardeveloper.example;

Next we import the required JDBC classes from java.sql package.

import java.sql.*;

Next we give a name to our class, i.e. JdbcExample3.

public class JdbcExample3 {
	...
}

Next we create a main() method for our class. This is where our JDBC code will reside.

public static void main(String args[]) {
	...
}

We create 3 variables to hold Connection, Statement and ResultSet objects for us which we’ll create later in a try/catch/finally block.

	Connection con = null;
	Statement st = null;
	ResultSet rs = null;

We then enter a try/catch/finally block. Our data access code will reside in the try block, exception notification code in the catch block and code to close the connection in the finally block.

	try {
		...
	} catch (Exception e) {
      System.err.println("Exception: " + e.getMessage());
	} finally {
		...
	}

First thing we do to display records is to obtain a connection to MySQL database and that we do that my first loading it’s JDBC (Connector/J) driver and then using DriverManager to obtain a connection to our “examples” database. You might want to change the password from “secret” to whatever is your password for ‘root’ account.

	Class.forName("com.mysql.jdbc.Driver").newInstance();
	con = DriverManager.getConnection("jdbc:mysql:///examples",
	  "root", "secret");

Once we are connected, we create a new SQL Statement object.

	st = con.createStatement();

We then execute this statement to obtain a ResultSet which contains all the records from the “users” table and we do that by executing a SELECT SQL statement.

	rs = st.executeQuery("SELECT user_id, first_name, last_name, " +
		"country_code FROM users");

We then iterate through that ResultSet to obtain values for each field of the “users” table. We then print these values on the user console.

	while(rs.next()) {
		int userId = rs.getInt(1);
		String firstName = rs.getString(2);
		String lastName = rs.getString(3);
		String countryCode = rs.getString(4);

		System.out.println(userId + ". " + lastName + ", " +
		firstName + " (" + countryCode + ")");
	}

In the finally block we close off our ResultSet, Statement and Connection objects.

	try {
		if(rs != null)
			rs.close();
		if(st != null)
			st.close();
		if(con != null)
			con.close();
	} catch (SQLException e) {}

Compiling JdbcExample3.java
To compile JdbcExample3.java, execute following command at the command prompt from the folder where JdbcExample3.java is residing:

javac -d . JdbcExample3.java
Command Prompt - Compiling JdbcExample3.java
Command Prompt – Compiling JdbcExample3.java

You should now have JdbcExample3.class file under the com/stardeveloper/example folder.

Running JdbcExample3
Before we run our Java program, make sure that MySQL server is running and that you’ve properly created and setup “examples” database and “users” table. Now to run our Java program, execute following command at the command prompt from the folder from where you compiled JdbcExample3.java:

java com.stardeveloper.example.JdbcExample3
Command Prompt - Running JdbcExample3
Command Prompt – Running JdbcExample3

Congratulations! you’ve successfully been able to display all the records from “users” tables in the MySQL “examples” database using JDBC in your Java program.

Summary
In this tutorial we learned how to display records from a table in a MySQL database using JDBC in a Java program.

In next article we will learn what is java.sql.PreparedStatement and how to make use of it.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


  • How To Dyndns Ubuntu | Order Goods: […] DynDNS Client Setup on Ubuntu | Life in code, apps and OS’s – May 18, 2008  · 7 Responses to "DynDNS Client Setu
  • lizrandolph10886: Wonderful article! We are linking to this particularly great post on our website.nKeep up the great writing. Click
  • waseem Ahmad: Dear. i have a separate application server and a separate mysql database server. when i want to access the application server to login from the local
%d bloggers like this: