Advertisement

---

Top

< Prev

Next >

Java Database Connectivity(JDBC) API provide a set of classes and interfaces that allow a Java application to connect to a database and execute SQL statements such as -

• Creating a table.
• Inserting the data in the database.
• Retrieve the data from the database.
• Updating the data in the database.

In this article, we are going to explain how to retrieve records from an existing table within the Oracle database by SELECT SQL statement using its JDBC thin driver.

Note : The JDBC classes and interfaces are available in java.sql and javax.sql packages.

Steps to retrieve records from a table with SELECT SQL statement using JDBC-

• Loading and registering database driver to connect our Java application with a database.

Class.forName("oracle.jdbc.driver.OracleDriver");

In the upcoming example, we are going to create a table within the Oracle database using its JDBC driver.


• Creating a connection between the Java program and the Oracle database.

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger");

In the upcoming example, we are using Oracle's thin JDBC driver to create a table, where -
• localhost refers to our local computer where the Oracle database server is running.
• 1521 is the port number to connect to the database.
• XE stands for Oracle Expess Edition Database.
• scott is our username to connect to the Oracle database and tiger is the password.



• Creating a Statement object to excute SQL statements

Statement stmt = con.createStatement();

Creating a Statement object to using createStatement() method of Connection interface.


• Executing a SQL query using Statement object.

ResultSet rs = stmt.executeQuery(query);

query is a String object which specifies the SQL query to execute, it results in a ResultSet object containing the retrieved records in terms of row/rows.


• Retrieving records using ResultSet object.

rs.getString(String columnName);

Using ResultSet object, we can sequentially access the retrieved records(a row or multiple rows) using its getString(columnName) method, where columnName is the name of column whose information we want to extract, from the retrieved row.


• Closing the database connection

con.close();

con is an Connection reference used to close the connection with the database.



We are going to retrieve records from an existing table named MyTable within the Oracle database, which we created and filled with values when we learned -
• How to create a table using JDBC.
• How to insert values in table using JDBC.



ID	FirstName	LastName	Age
1	Tom	Hanks	61
2	Johnny	Depp	54
3	Brad	Pitt	53

Table - MyTable





Advertisement






Retrieving data from database using JDBC

We are going to execute 3 SQL SELECT queries and on the execution of each query, a row will be updated in an existing table MyTable, which we created when we learned how to create a table.



import java.sql.*;

class A
{
public static void main(String... ar)
{
try
{

//First SQL SELECT Query
String query1 =  "SELECT * FROM MyTable";					

//Second SQL SELECT Query
String query2 =  "SELECT * FROM MyTable WHERE Age = '53' ";			

//Third SQL SELECT Query
String query3 =  "SELECT FirstName FROM MyTable WHERE LastName = 'Hanks'";	


Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger");
Statement stmt = con.createStatement();


//Executing first SELECT query
ResultSet rs = stmt.executeQuery(query1);	

System.out.println("Result of executing query1");
System.out.println("ID " + "\t" + "FirstName" +  "\t" +  "LastName" + "\t" + "Age");

//looping through the number of row/rows retrieved after executing query2
while(rs.next()) 	
{
System.out.print(rs.getString("ID") + "\t");
System.out.print(rs.getString("FirstName") + "\t" + "\t");
System.out.print(rs.getString("LastName")+ "\t" +  "\t");
System.out.println(rs.getString("Age") + "\t");
}



//Executing second SELECT query
rs = stmt.executeQuery(query2);			
System.out.println("Result of executing query2");

//looping through the number of row/rows retrieved after executing query2
while(rs.next())	
{
System.out.print(rs.getString("ID") + "\t");
System.out.print(rs.getString("FirstName") + "\t" + "\t");
System.out.print(rs.getString("LastName")+ "\t" +  "\t");
System.out.println(rs.getString("Age") + "\t");
}



//Executing third SELECT query
rs = stmt.executeQuery(query3);			
System.out.println("Result of executing query3");
while(rs.next())
{
System.out.println(rs.getString(1));
}


}
catch(Exception e)
{
System.out.println(e);
}

}//main() method ends

}//class definition ends




Output

Result of executing query1
ID      FirstName       LastName        Age
1       Tom             Hanks           61
2       Johnny          Depp            54
3       Brad            Pitt            53
Result of executing query2
3       Brad            Pitt            53
Result of executing query3
Tom






Please share this article -

A




< Prev
Next >
< JDBC INSERT into table
JDBC UPDATE table >
< Gregorian class



Advertisement