Computer Science Wiki

Java Database Connectivity (JDBC)

4 min read

Before working with JDBC, you have to download the driver for your JDBC implementation. You can download the driver from maven central repository or from its offical website and add it to your project using a build tool or manually install it. In the below example we use PostgreSQL for demonstration and assumed the PostgreSQL driver has been added to the project. In the database named demo we have a table student with two columns sid and sname.

  1. Import package
import java.sql.*;
  1. Load and register driver In newer versions of JDBC (JDBC 4.0+), you no longer need to explicitly load the driver if it’s included in your class path. You can skip this step unless you’re working with an older version of Java.
Class.forName("org.postgresql.Driver");
  1. Create connection PostgreSQL is a network database. The URL for connection follows this structure: jdbc:postgresql://<hostname>:<port>/<database_name>.
  • Replace <hostname> with your database server’s address (e.g., localhost or an IP).
  • Replace <port> with the port number if it differs from the default (PostgreSQL has a default port number 5432).
  • Replace <database_name> with the name of your database.

Below is an example.

String url = "jdbc:postgresql://localhost:5432/demo";  
String username = "postgres";  
String pwd = "0000";  
Connection con = DriverManager.getConnection(url, username, pwd);
  1. Create statement
String sql = "select sname from student where sid=0";
Statement st = con.createStatement();
  1. Execute statement For fetching data, use the executeQuery() method. This returns a ResultSet containing the query results. More about working with data are discussed later this page.
ResultSet rs = st.executeQuery(sql);
re.next();
String studentName = re.getString("sname");
System.out.println("Name of a student is " + studentName);
  1. Close connection When you finish interacting with the database, always close the connection.
con.close()

CRUD operations

Create

We can insert data into database using the execute() method. The method used for all types of SQL statements, it returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. The ResultSet object will be discussed in the Read section of this page.

String sql = "insert into student values (5, 'Jason')";
Statement st = con.createStatement();
st.execute(sql);
con.close();

Read

Process single data

The executeQuery() is for fetching the data from the database. It will return a type of ResultSet. The ResultSet object holds the tabular data returned by the executeQuery() method. The ResultSet object has a cursor/pointer which points to the current row. Initially this cursor is positioned before first row. On calling the next()method for the first time the result set pointer/cursor will be moved to the 1st row (from default position). The next() will also return a boolean value indicating there is a next result. 

String sql = "select sname from student where sid=0";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
// This will move the cursor/pointer to the first row of the result, this result will be returned by the getString()
rs.next();
// We want the data specifically from column sname (the query result may have data of mulitple columns)
// You can specify column index or column name ("name")
// This is equivalent of saying we want the result from column sname
String studentName = re.getString("sname");
System.out.println("Name of a student is " + studentName);
con.close();
Name of a student is Kevin

Process multiple data

The ResultSet may contain multiple rows of data. As mentioned before, the method next() returns a boolean value specifying whether the ResultSet object contains more rows. If there are no rows next to its current position this method returns false, else it returns true. Using this method in the while loop you can iterate the contents of the result set.

String sql = "select * from student";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
	System.out.print(rs.getInt("sid") + '-');
	System.out.println(rs.getString("sname"));
}
con.close();
0 - Kevin
1 - James
2 - Timmy

Update

String sql = "update student set sname='Max' where sid=5";
Statement st = con.createStatement();
st.execute(sql);
con.close();

Delete

String sql = "delete from student where sid=5";
Statement st = con.createStatement();
st.execute(sql);
con.close();

Prepared statement

We can use prepared statements to accept variable input, mitigate SQL injection attacks, improve performance and avoid the tedious and error-prone process of SQL statement concatenation.

int studentID = 3;  
String studentName = "John";  
  
String sql = "insert into student values (?,?)";  
PreparedStatement pstmt = con.prepareStatement(sql);  
pstmt.setInt(1, studentID); // Set the first parameter (sid)  
pstmt.setString(2, studentName); // Set the second parameter (sname)  
pstmt.execute();  
con.close();

Back to parent page: Java

Web_and_App_Development Programming_Languages Java Java_API JDBC

Reference:

Graph View

Backlinks