Category Archives: JDBC

How to retrieve data from JDBC resultset in java

In this post we will learn How to retrieve data\row from JDBC resultset in java. Project structure in eclipse: SQL Query to create employee_table and insert few rows in MySQL (jdbcdb schema) (Note:Make sure you have created employee_table and inserted few records in jdbcdb database schema) DB.sql

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','martin.cs2017@yahoo.com',71000,'2017-05-17 00:00:00',400);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','paul.cs2017@gmail.com',91000,'2017-05-18 00:00:00',800);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','sam.cs2014@gmail.com',76000,'2017-05-16 00:00:00',300);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','paul.cs2009@yahoo.com',60000,'2017-05-17 00:00:00',400);

CREATE TABLE `employee_table` (

`employee_id` int(11) NOT NULL auto_increment,

`employee_name` varchar(60) NOT NULL,

`email` varchar(45) NOT NULL,

`salary` double default NULL,

`date_of_joining` datetime default NULL,

`bonus` decimal(10,0) default NULL,

PRIMARY KEY (`employee_id`)

) ;

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','[email protected]',71000,'2017-05-17 00:00:00',400);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',91000,'2017-05-18 00:00:00',800);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','[email protected]',76000,'2017-05-16 00:00:00',300);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',60000,'2017-05-17 00:00:00',400);

After running above db script in your MySQL jdbcdb schema employee_table will… Read More »

Transaction basic concept

What is transaction? Transaction is a logical unit of work where all operation should success or nothing should success. Or you can understand as below A transaction is a set of one or more SQL statements that is executed as a unit either all statements will be executed successfully or none of them. You can… Read More »

JDBC transaction management example

In previous post we learnt about Transaction basic concept In this post we will learn how to manage transaction in JDBC. In JDBC API we have Connection interface which provides: setAutoCommit(boolean) method to disable auto-commit mode by passing the false value. In JDBC by default commit is true so if you execute any statement it’s commits immediately commit() method to commit the transaction… Read More »

JDBC batch update with CallableStatement

In previous couple posts we learnt about JDBC batch update with Statement and JDBC batch update with PreparedStatement When you want to send a bunch of SQL queries to database in one go(one database call) to execute that’s what batch concept comes. This reduces network traffic and improves performance of your application In this post we will… Read More »

JDBC batch update with PreparedStatement

When you want to send a bunch of SQL queries to database in one database call to execute that’s what batch concept comes. This reduces network traffic and improves performance of your application In this post we will learn about JDBC batch update with java.sql.PreparedStatement. As you know java.sql.PreparedStatement deals with dynamic SQL with place holder(?). so Here… Read More »

JDBC batch update with Statement

When you want to send a bunch of SQL queries to database in one go(one call) to execute that’s what batch concept comes. This reduces network traffic and improves performance of your application In this post we will learn about JDBC batch update with java.sql.Statement. As you know java.sql.Statement always deals with Literal or hard coded SQL.… Read More »

Calling database custom Function Using CallableStatement

In this post you will learn how to call Stored Function Using JDBC CallableStatement. Here i am using MYSQL database but you are feel free to choose any database. Jdbc code would be same for all database only little changes may be in how to create SQL custom function from once database to another Project… Read More »

Get ResultSet By Calling StoredProcedure Using CallableStatement

In this post we will learn how map number of rows return by calling stored procedure in JDBC Result set. Project structure in eclipse: SQL Query to create employee_table in MySQL (jdbcdb schema) (Note:Make sure you created employee_table and Stored procedure(getEmployeeById) in jdbcdb database schema)

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

DROP PROCEDURE IF EXISTS getEmployeeById;
CREATE PROCEDURE `getEmployeeById`(IN empId INT)
BEGIN
	SELECT *FROM employee_table WHERE employee_id =empId;
END;

CREATE TABLE `employee_table` (

`employee_id` int(11) NOT NULL auto_increment,

`employee_name` varchar(60) NOT NULL,

`email` varchar(45) NOT NULL,

`salary` double default NULL,

`date_of_joining` datetime default NULL,

`bonus` decimal(10,0) default NULL,

PRIMARY KEY (`employee_id`)

) ;

DROP PROCEDURE IF EXISTS getEmployeeById;

CREATE PROCEDURE `getEmployeeById`(IN empId INT)

BEGIN

SELECT *FROM employee_table WHERE employee_id =empId;

END;

Insert some records in employee_table as below DBUtil.java class which is responsible to connect… Read More »

Calling StoredProcedure Using CallableStatement

In this post we will learn how to call Stored Procedure using JDBC CallableStatement. Project structure in eclipse: First of all we are going to create MySQL database schema named jdbcdb The MySQL jdbcdb database schema consists of the following tables: Customers: stores customer’s data. Employees: This table stores all employees information as well as the… Read More »

CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement

In previous couple of posts CRUD(Create,Read,Update and Delete) Operation using JDBC Statement and Reading data from database using JDBC Statement we have learnt how to work with JDBC Statement. When You want to execute literal or hard coded SQL then we do go for JDBC Statement. But most of scenarios/times in application you would like to… Read More »