SimpleCodeStuffs|JDBC Tutorial Archives - Page 2 of 4

Home » JDBC Tutorial

JDBC PreparedStatement example to Delete a record

Posted by Mohaideen Jamil in JDBC Tutorial

In this example let us learn how to delete a record from a table via JDBC PreparedStatement.

To issue a delete statement, calls the PreparedStatement.executeUpdate() as show below

String deleteQuery = "DELETE EMPLOYEE WHERE USER_ID = ?";
PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery);
preparedStatement.setInt(1, 1000);
// execute delete SQL statement
preparedStatement.executeUpdate();

Full example…

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPreparedStmtDeleteExample
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
		+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

public static void main(String[] argc)
{
	try
	{
		deleteRecordFromTable();
	}
	catch (SQLException e)
	{
		System.out.println(e.getMessage());
	}
}

private static void deleteRecordFromTable() throws SQLException
{
	Connection connection = null;
	PreparedStatement prepareStmt = null;
	String deleteQuery = "DELETE EMPLOYEE WHERE USER_ID = ?";
try
{
	connection = getDBConnection();
	prepareStmt = connection.prepareStatement(deleteQuery);
	prepareStmt.setInt(1, 1000);
	// execute update SQL statement
	prepareStmt.executeUpdate();
	System.out.println("Record is deleted!");
}
catch (SQLException e)
{
	System.err.println(e.getMessage());
}
	
finally
{
	if (prepareStmt != null)
	{
		prepareStmt.close();
	}
	if (connection != null)
	{
		connection.close();
	}
	}
}

private static Connection getDBConnection()
{
	Connection dbConnection = null;
	try
	{
		Class.forName(dbDriver);
	}
	catch (ClassNotFoundException e)
	{
		System.err.println(e.getMessage());
	}

	try
	{
	dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return dbConnection;
	}
	catch (SQLException e)
	{
		System.err.println("Db "+e.getMessage());
	}
	return dbConnection;
}
}

Five Steps to connect a database via JDBC driver

Posted by Mohaideen Jamil in JDBC Tutorial

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:

Load and register the JDBC driver
Creating connection
Create a statement object to process a query.
Executing queries and processing the ResultSet
Explicitly closing JDBC objects(resultset,statement & connection)

1) Load and register the JDBC driver

The forName() method of the java.lang.Class is used to load and register the JDBC driver:

Syntax

public static void forName(String className)throws ClassNotFoundException

Example

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

2) Create the connection object

The getConnection() method of java.sql.DriverManager is used to establish connection with database. It is an overloaded method that takes 3 parameters (URL, username, and password)

Syntax

1) public static Connection getConnection(String url, String name, String password)
throws SQLException

In case, when the URL contains the username and password then the following method is used

2) public static Connection getConnection(String url)throws SQLException

Example

Connection connection=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE","UserName","Password");

3) Create the Statement object

The createStatement() method of java.sql.Connection interface is used to create statement object which is responsible for running & executing queries against the database.

Syntax:

public Statement createStatement()throws SQLException

Example:

Statement stmt=connection.createStatement();

4) Executing the query and processing the ResultSet

Once a Statement object has been constructed, the next step is to execute the query. This is done by using the executeQuery() method of the Statement object. On executing this method it returns the object of ResultSet which contains the ResultSet produced by executing the query.

Syntax:

public ResultSet executeQuery(String sql)throws SQLException

Execute query and process the ResultSet


ResultSet rset=stmt.executeQuery("select * from employee");
// Processing the ResultSet
while(rset.next())
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}

5) Explicitly closing JDBC objects

Once the Resultset,statement & Connection objects have been used, they must be closed explicitly. This is done by calling close() method of the ResultSet ,Statement and Connection classes.

Syntax

public void close()throws SQLException

Example

connection.close();
rset.close();

If you do not explicitly call the close() method for each of these objects as soon as you are done with them, your programs are likely to run out of resources. The best practice is to Close JDBC objects in finally block and assign null value afterwards, so that it will be immediately eligible for garbage collection.

JDBC PreparedStatement example to Insert a Record

Posted by Mohaideen Jamil in JDBC Tutorial

Here is an example to show you how to insert a record into table via JDBC PreparedStatement.

To issue an insert statement, calls the PreparedStatement.executeUpdate() method

** UPDATE: Complete JDBC tutorial now available here.

String insertTableQuery= "INSERT INTO EMPLOYEE"
  + "(USER_ID, USERNAME, CREATED_BY) VALUES (?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertTableQuery);
preparedStatement.setInt(1, 1000);
preparedStatement.setString(2, "nilafar");
preparedStatement.setString(3, "admin");
// execute insert SQL statement
preparedStatement .executeUpdate();

Full example…

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPreparedStmtInsertExample
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
			+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

public static void main(String[] argc)
{
	try
	{
		insertRecordIntoTable();
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
}

private static void insertRecordIntoTable() throws SQLException
{
	Connection connection = null;
	PreparedStatement prepareStmt = null;

	String insertTableQuery = "INSERT INTO EMPLOYEE"
			+ "(USER_ID, USERNAME, CREATED_BY) VALUES (?,?,?)";

	try
	{
		connection = getDBConnection();
		prepareStmt = connection.prepareStatement(insertTableQuery);

		prepareStmt.setInt(1, 1000);
		prepareStmt.setString(2, "Nilafar");
		prepareStmt.setString(3, "admin");
 
		// execute insert SQL statement
		prepareStmt.executeUpdate();
		System.out.println("Record is inserted into EMPLOYEE table!");
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
		
	finally
	{
		if (prepareStmt != null)
		{
			prepareStmt.close();
		}
		if (connection != null)
		{
			connection.close();
		}
		}
	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);
		}
		
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		return dbConnection;
		}
		catch (SQLException e)
		{
			System.err.println("Db "+e.getMessage());
	        }
	return dbConnection;
}
}

Note: Once a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again.

Here is a simple example.

String insertTableQuery= "INSERT INTO EMPLOYEE"
  + "(USER_ID, USERNAME, CREATED_BY) VALUES (?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertTableQuery);
preparedStatement.setInt(1, 1000);
preparedStatement.setString(2, "nilafar");
preparedStatement.setString(3, "admin");
// execute insert SQL statement
preparedStatement .executeUpdate();

// Reuse the prepared statement
preparedStatement.setInt(1, 1001);
preparedStatement.setString(2, "Jamil");
preparedStatement.setString(3, "admin");
preparedStatement .executeUpdate();

JDBC PreparedStatement example to Create a Table

Posted by Mohaideen Jamil in JDBC Tutorial

In this example let us learn how to create a table in database via JDBC PrepareStatement. To issue a create statement, calls the PrepareStatement.executeUpdate() method like this :

PreparedStatement preparedStatement = connection.prepareStatement(createTableQuery);
// execute create SQL stetement
preparedStatement.executeUpdate();

Full example…

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPreparedStatementCreateExample
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
                                     + portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

public static void main(String[] argc)
{
	try
	{
		createEmployeeTable();
	}
	catch (SQLException e)
	{
		System.out.println(e.getMessage());
	}
}

private static void createEmployeeTable() throws SQLException
{
	Connection connection = null;
	PreparedStatement prepareStmt = null;

	String createTableQuery = "CREATE TABLE EMPLOYEE(USER_ID NUMBER(5) NOT NULL, "
		+ "USERNAME VARCHAR(20) NOT NULL, CREATED_BY VARCHAR(20) NOT NULL, "
		+ "PRIMARY KEY (USER_ID))";

try
{
	connection = getDBConnection();
	prepareStmt = connection.prepareStatement(createTableQuery);

	// execute the SQL statement
	prepareStmt.executeUpdate(createTableQuery);
	System.out.println("Table EMPLOYEE is created!");
}
catch (SQLException e)
{
	System.out.println(e.getMessage());
}
	
finally
{
	if (prepareStmt != null)
	{
		prepareStmt.close();
	}
	if (connection != null)
	{
		connection.close();
	}
	}
}

private static Connection getDBConnection()
{
	Connection dbConnection = null;
	try
	{
		Class.forName(dbDriver);
	}
	
	catch (ClassNotFoundException e)
	{
		System.out.println(e.getMessage());
	}

	try
	{
	dbConnection=DriverManager.getConnection(dbUrl,dbUser,dbPassword);
	return dbConnection;
	}
	catch (SQLException e)
	{
		System.out.println("Db "+e.getMessage());
	}

	return dbConnection;
}
}

JDBC Batch Updates

Posted by Mohaideen Jamil in JDBC, JDBC Tutorial

You can send multiple queries to the database at a time using batch update feature of Statement/PreparedStatement objects this reduces the number of JDBC calls and improves performance.

Note : Batch Update is not limit to Insert statement, it’s applied for Update and Delete statement as well.

Statement Batch Updates

To issue a Batch update , call the addBatch() and executeBatch() methods as shown below.

dbConnection.setAutoCommit(false);
 
statement = connection.createStatement();
statement.addBatch(insertTableQuery1);
statement.addBatch(insertTableQuery2);
statement.addBatch(insertTableQuery3);
 
statement.executeBatch();
dbConnection.commit();

Full example – Batch Update using JDBC Statement

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStmtBatchUpdate
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

	public static void main(String[] argc)
	{

		try
		{
			batchInsertRecordsIntoTable();
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}
	}

	private static void batchInsertRecordsIntoTable() throws SQLException
	{
		Connection connection = null;
		Statement statement = null;

		String insertTableQuery1 = "INSERT INTO EMPLOYEE"
				+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
				+ "(1001,'Jamil','admin')";

		String insertTableQuery2 = "INSERT INTO EMPLOYEE"
				+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
				+ "(1002,'Ameer','admin')";

		String insertTableQuery3 = "INSERT INTO EMPLOYEE"
				+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
				+ "(1003,'Nilafar','admin')";

		try
		{
			connection = getDBConnection();
			statement = connection.createStatement();

			connection.setAutoCommit(false);

			statement.addBatch(insertTableQuery1);
			statement.addBatch(insertTableQuery2);
			statement.addBatch(insertTableQuery3);

			statement.executeBatch();
			connection.commit();
			System.out.println("Records are inserted into EMPLOYEE table!");

		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}
		finally
		{
			if (statement != null)
			{
				statement.close();
			}

			if (connection != null)
			{
				connection.close();
			}

		}

	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);
		}
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		return dbConnection;

		}
		catch (SQLException e)
		{
		System.out.println(e.getMessage());
		}

		return dbConnection;
	}
}

PreparedStatement Batch Updates

You can also use a PreparedStatement object to execute batch updates. The PreparedStatement enables you to reuse the same SQL statement, and just insert new parameters into it, for each update to execute. This method also avoids SQL Injection issue.

Here is an example (Batch Update using PreparedStatement):

package com.simplecode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCPstmtBatchUpdate
{

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

	public static void main(String[] argc)
	{
		try
		{
			batchInsertRecordsIntoTable();
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}
	}

private static void batchInsertRecordsIntoTable() throws SQLException
{
Connection connection = null;
PreparedStatement preparedStmt = null;
String insertTableQuery="INSERT INTO EMPLOYEE (USER_ID,USERNAME,CREATED_BY) VALUES (?,?,?)";

	try
	{
		connection = getDBConnection();
		preparedStmt = connection.prepareStatement(insertTableQuery);

		connection.setAutoCommit(false);

			preparedStmt.setInt(1, 1000);
			preparedStmt.setString(2, "Nilafar");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.setInt(1, 1001);
			preparedStmt.setString(2, "Azar");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.setInt(1, 1002);
			preparedStmt.setString(2, "Gokul");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.setInt(1, 1003);
			preparedStmt.setString(2, "Haripriya");
			preparedStmt.setString(3, "admin");
			preparedStmt.addBatch();

			preparedStmt.executeBatch();
			connection.commit();
			System.out.println("Record is inserted into EMPLOYEE table!");

		}

		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		} finally
		{
			if (preparedStmt != null)
			{
				preparedStmt.close();
			}

			if (connection != null)
			{
				connection.close();
			}
		}
	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);

		}
		catch (ClassNotFoundException e)
		{
			System.out.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		return dbConnection;
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
		}

		return dbConnection;
	}
}

Download It – BatchUpdates

JDBC Statement example to Select list of Records from database

Posted by Mohaideen Jamil in JDBC Tutorial

Here is an example to show you how to select the entire records from table via JDBC statement, and display all the records via a ResultSet object.

To issue a select query, calls the Statement.executeQuery method like this:

String selectTableSQL = "SELECT USER_ID, USERNAME from EMPLOYEE";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(selectTableQuery);
while (rs.next())
{
	String userid = rs.getString("USER_ID");
	String username = rs.getString("USERNAME");
}

Full example…
File: JDBCStatementSelectExample.java

package com.simplecode.jdbc;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementSelectExample

{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+ sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";

	public static void main(String[] argc)
	{
		try
		{
			selectRecordsFromEmployeeTable();
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}

	private static void selectRecordsFromEmployeeTable() throws SQLException
	{

		Connection dbConnection = null;
		Statement statement = null;

		String selectTableQuery = "SELECT USER_ID, USERNAME from EMPLOYEE";

		try
		{
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(selectTableQuery);

			// execute select SQL statement
			ResultSet rs = statement.executeQuery(selectTableQuery);

			while (rs.next())
			{
				String userid = rs.getString("USER_ID");
				String username = rs.getString("USERNAME");

				System.out.println("userid : " + userid);
				System.out.println("username : " + username);
			}

		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
		finally
		{
			if (statement != null)
			{
				statement.close();
			}

			if (dbConnection != null)
			{
				dbConnection.close();
			}
		}
	}

	private static Connection getDBConnection()
	{
		Connection dbConnection = null;
		try
		{
			Class.forName(dbDriver);
		}
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		try
		{
		dbConnection = DriverManager.getConnection(dbUrl, dbUser,dbPassword);
		return dbConnection;
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}

		return dbConnection;
	}

}

Download It – JDBCStatementSelectExample

Page 2 of 4«123 4 »

JDBC PreparedStatement example to Delete a record

Five Steps to connect a database via JDBC driver

JDBC PreparedStatement example to Insert a Record

JDBC PreparedStatement example to Create a Table

JDBC Batch Updates

Statement Batch Updates

PreparedStatement Batch Updates

JDBC Statement example to Select list of Records from database

Recently Updated Posts

Recent Posts

Join SCS

Our Legal Policies