Creating JDBC SQL Statements. Displaying Records from a MySQL Database

Overview
In this tutorial following topics will be covered:

• What are JDBC SQL Statements?
• What are different types of JDBC SQL Statements?
• How to create and execute JDBC SQL Statements?
• Basics of creating databases and tables in MySQL.
• How to display records from a table in MySQL?

This tutorial assumes that you've got MySQL and Connector/J JDBC driver properly setup on your system. If not have a look at following introductory articles/tutorials:

• Installing and Configuring MySQL Database and Connector/J JDBC Driver on Microsoft Windows.
• Connecting to a MySQL Database using Connector/J JDBC Driver.

In the previous articles we learned that we obtain a java.sql.Connection object which represents a database connection from java.sql.DriverManager class. We learned that to properly connect to a database we need to have a properly installed JDBC driver, and we did that by installing Connector/J JDBC driver for MySQL. We also learned that after loading JDBC driver we have to correctly specify a database URL to DriverManager to obtain a connection.

We'll now move on to learn how to create and execute JDBC SQL Statements.

What are JDBC SQL Statements?
SQL stands for "Structured Query Language". It is a standard query language supported by almost all RDBMS vendors. You can use SQL to display (SELECT), add (INSERT), edit (UPDATE) and remove (DELETE) records from a table in a database.

A database server like MySQL contains 0 or more databases. A database then contains 0 or more different tables. The tables in turn contain 1 more fields. You add a record to a table by using the INSERT SQL statement. To display records from a table you use SELECT SQL query.

Following are some of the introductory articles/tutorials on SQL at Stardeveloper:

• SQL In Simple English - Part 1.
• SQL In Simple English - Part II.

java.sql.Statement

Java is an object-oriented language. Everything in the World of Java is represented by an object. So the SQL queries that we learned above are also represented by a generic interface of type java.sql.Statement. All SQL statements are represented by this interface.

You use different methods provided by java.sql.Statement interface to retrieve (SELECT) or update (INSERT, UPDATE, DELETE) records in a database.

What are different types of JDBC SQL Statements?
There are 3 types of JDBC SQL Statements:

• java.sql.Statement - Top most interface which provides basic methods useful for executing SELECT, INSERT, UPDATE and DELETE SQL statements.
• java.sql.PreparedStatement - An enhanced verion of java.sql.Statement which allows precompiled queries with parameters. It is more efficient to use java.sql.PreparedStatement if you have to specify parameters to your SQL queries.
• java.sql.CallableStatement - Allows you to execute stored procedures within a RDBMS which supports stored procedures (MySQL doesn't support stored procedures at the moment).

In this tutorial we'll be focusing our attention only on the java.sql.Statement object.

How to create and execute JDBC SQL Statements?
To create a JDBC SQL statement, we make use of java.sql.Connection.createConnection() method like this:

Connection con = null;
Statement st = null;

// Obtain connection here
st = con.createStatement();

Calling Connection.createStatement() gives you an empty JDBC Statement object. So how do you specify which query to use? Here is how you'd do it:

ResultSet rs = null;

rs = st.executeQuery("SELECT * FROM users");

We execute a SELECT SQL statement to retrieve all the records from the "users" table.

The Statement.executeQuery() method is used when you want to retrieve records from a database i.e. you are executing SELECT SQL statements. But what if you want to INSERT, UPDATE or DELETE records from a table? You then have to make use of Statement.executeUpdate() method like this:

int recordsUpdated;

recordsUpdated = st.executeUpdate("DELETE FROM users WHERE user_id = 1");

Above query deletes a user from the "users" tables where the user_id field's value is '1'. The number of records affected are returned by this method which we save in the recordsUpdated variable.

Connection con = null;
Statement st = null;

// Obtain connection here
st = con.createStatement();

ResultSet rs = null;

rs = st.executeQuery("SELECT * FROM users");

int recordsUpdated;

recordsUpdated = st.executeUpdate("DELETE FROM users WHERE user_id = 1");