Retrieving Data using JDBC Select Query
We can use Java JDBC Select statement in a java program to retrieve the data and display it for the respective Tables. JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. Select is the SQL keyword that performs a query. We invoke the jdbc select query (executequery) method, using the jdbc select data statement as the parameter. The tabular results of the query are captured in the ResultSet object, results. Note that executeQuery() always returns a ResultSet although it need not have any rows in it.
The return value for an executeQuery is a ResultSet object containing the results of the query sent to the DBMS,
To process each row in the ResultSet, we use the next() method. This method moves the pointer through the rows of data. The ResultSet maintains a cursor pointing to the current row. Because this cursor is initially positioned before the first row, we must call next() before we can see any rows at all. Below is a JDBC Program showing the use of executeQuery() to retrieve values from ResultSets using jdbc programming.
For my website I am creating the following 2 tables (Employee, Orders) as a part of the JDBC tutorial.
Employee_ID is the primary key which forms a relation between the 2 tables.
CREATE TABLE Employees ( Employee_ID INTEGER, Name VARCHAR(30) );
Employees Table:
Employee_ID | Name |
6323 | Hemanth |
5768 | Bob |
1234 | Shawn |
5678 | Michaels |
Orders Table:
CREATE TABLE Orders ( Prod_ID INTEGER, ProductName VARCHAR(20), Employee_ID INTEGER );
Prod_ID | Product Name | Employee_ID |
543 | Belt | 6323 |
432 | Bottle | 1234 |
876 | Ring | 5678 |
JDBC SQL Select Example
import javax.swing.JOptionPane;
import java.sql.*;
public class JDBCProgram{
static String userid="scott", password = "tiger";
static String url = "jdbc:odbc:bob"; // String url = "jdbc:mySubprotocol:myDataSource"; ?
static Statement stmt;
static Connection con;
public static void main(String args[]){
JOptionPane.showMessageDialog(null,"JDBC Programming showing Retrieval of Table Data");
int choice = -1;
do{
choice = getChoice();
if (choice != 0){
getSelected(choice);
}
}
while ( choice != 0);
System.exit(0);
}
public static int getChoice()
{
String choice;
int ch;
choice = JOptionPane.showInputDialog(null,
"1. Create Employees Table\n"+
"2. Create Products Table\n"+
"3. Insert data into Employees Table\n"+
"4. Insert data into Orders Table\n"+
"5. Retrieve data for Employees Table\n"+
"6. Retrieve data for Orders Table\n"+
"0. Exit\n\n"+
"Enter your choice");
ch = Integer.parseInt(choice);
return ch;
}
public static void getSelected(int choice){
if(choice==1){
createEmployees();
}
if(choice==2){
createOrders();
}
if(choice==3){
insertEmployees();
}
if(choice==4){
insertOrders();
}
if(choice==5){
retrieveEmployees();
}
if(choice==6){
retrieveOrders();
}
}
public static Connection getConnection()
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Class.forName("myDriver.ClassName"); ?
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
userid, password);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
return con;
}
/*CREATE TABLE Employees (
Employee_ID INTEGER,
Name VARCHAR(30)
);*/
public static void createEmployees()
{
Connection con = getConnection();
String createString;
createString = "create table Employees (" +
"Employee_ID INTEGER, " +
"Name VARCHAR(30))";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
JOptionPane.showMessageDialog(null,"Employees Table Created");
}
/*CREATE TABLE Orders (
Prod_ID INTEGER,
ProductName VARCHAR(20),
Employee_ID INTEGER
);*/
public static void createOrders()
{
Connection con = getConnection();
String createString;
createString = "create table Orders (" +
"Prod_ID INTEGER, " +
"ProductName VARCHAR(20), "+
"Employee_ID INTEGER )";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
JOptionPane.showMessageDialog(null,"Orders Table Created");
}
/*Employee_ID Name
6323 Hemanth
5768 Bob
1234 Shawn
5678 Michaels */
public static void insertEmployees()
{
Connection con = getConnection();
String insertString1, insertString2, insertString3, insertString4;
insertString1 = "insert into Employees values(6323, 'Hemanth')";
insertString2 = "insert into Employees values(5768, 'Bob')";
insertString3 = "insert into Employees values(1234, 'Shawn')";
insertString4 = "insert into Employees values(5678, 'Michaels')";
try {
stmt = con.createStatement();
stmt.executeUpdate(insertString1);
stmt.executeUpdate(insertString2);
stmt.executeUpdate(insertString3);
stmt.executeUpdate(insertString4);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
JOptionPane.showMessageDialog(null,"Data Inserted into Employees Table");
}
/* Prod_ID ProductName Employee_ID
543 Belt 6323
432 Bottle 1234
876 Ring 5678
*/
public static void insertOrders()
{
Connection con = getConnection();
String insertString1, insertString2, insertString3, insertString4;
insertString1 = "insert into Orders values(543, 'Belt', 6323)";
insertString2 = "insert into Orders values(432, 'Bottle', 1234)";
insertString3 = "insert into Orders values(876, 'Ring', 5678)";
try {
stmt = con.createStatement();
stmt.executeUpdate(insertString1);
stmt.executeUpdate(insertString2);
stmt.executeUpdate(insertString3);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
JOptionPane.showMessageDialog(null,"Data Inserted into Orders Table");
}
public static void retrieveEmployees(){
Connection con = getConnection();
String result = null;
String selectString;
selectString = "select * from Employees";
result ="Employee_ID\t\tName\n";
try {
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(selectString);
while (rs.next()) {
int id = rs.getInt("Employee_ID");
String name = rs.getString("Name");
result+=id+"\t\t"+ name+"\n";
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
JOptionPane.showMessageDialog(null, result);
}
public static void retrieveOrders(){
Connection con = getConnection();
String result = null;
String selectString;
selectString = "select * from Orders";
result ="Prod_ID\t\tProductName\t\tEmployee_ID\n";
try {
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(selectString);
while (rs.next()) {
int pr_id = rs.getInt("Prod_ID");
String prodName = rs.getString("ProductName");
int id = rs.getInt("Employee_ID");
result +=pr_id+"\t\t"+ prodName+"\t\t"+id+"\n";
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
JOptionPane.showMessageDialog(null, result);
}
}//End of class