JDBC Complete guide with Examples

:What is JDBC ?

  • JDBC stands for java database connectivity.
  • JDBC is an API that help us to achieve connectivity between java program and database
  •  If we have web application and if we have database then web application must interact with database to read or modifies a data.
  • JDBC is help us to Achieve this functionality.
  •  TO interact with any database JDBC must needed. JDBC Complete guide

Advantage of JDBC

  • Since JDBC API built upon java so it inherits properties of Java.
  • JDBC is Database independent.
  • Using JDBC we can interact with multiple database.
  • We can achieve high Performance using JDBC with the help of:
    o Prepare statement
    o Callable statement
  • JDBC support Stored Procedure.

Necessary step to work with JDBC

  1. Load and Register JDBC Driver
  2. Get the Database connection via Driver.
  3. Create Statement object( SQL query) via connection.
  4. Execute the Statement Object and return the Resultset.
  5. Close all JDBC object and Connection.

JDBC Execution diagram


What is Driver ?

  • Driver is additional software component required to JDBC to interact with database.
  • Driver are Provided by Database vendor and they are Database dependent.
  • Driver is a concrete class present in jar file
  • Driver help us to establish database connection, transfer database query and result between java program and database.

There are two ways to load driver

First way:
import java.sql.DriverManager;
import com.mysql.jdbc.Driver;
Driver ref = new Driver();
DriverManager.registerDriver(ref);
Second way:

Another way to load driver using Class.forname, this approach is most common approach to load driver and it automatically register driver class.

Class.forName(“com.mysql.jdbc.Driver”);

Types of Driver:

There are Four types of Driver

  1. Type-1 JDBC ODBC Bridge
  2. Type-2 Native API
  3. Type-3 Network protocol
  4. Type-4 Native Protocol
  • Type-1 and Type-2 require Client side installation so it is less efficient.
  • Type-3 and Type-4 Driver develop using java so it is efficient.
  • Type-3 driver support More database.
  • Type-4 Driver give high performance because it is directly connected with database
Step to Configure Driver:

1. Configure Project build path

  • Download MySQL zip file and Extract it.
  • Open eclipse IDE.
  • Right click on Project.
  • Select build path.
  • Choose option “Add External Archive” and Select MySQL connector jar file.
What is Jar file ?
  • It is collection of ‘ .class ‘ plus other necessaryresources(text,xml etc).
  •  Jar file help us to transfer the java application fromone place to another place.

Prerequisite:

1. Install any database(in my case it is mysql)
2. Configure java build path and add external jar of mysqlWe have already created student_detail table in mysqlJDBC Complete guide
Now we will fetch that data using Java Program

Necessary step to work with JDBC

  1. Load and Register JDBC Driver
  2. Get the Database connection via Driver.
  3. Create Statement object( SQL query) via connection.
  4. Execute the Statement Object and return the Resultset.
  5. Close all JDBC object and Connection.

 Java program to fetch data from MySQL database

[pastacode lang=”java” manual=”package%20com.spy.javaforlearn%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.ResultSet%3B%0Aimport%20java.sql.SQLException%3B%0Aimport%20java.sql.Statement%3B%0A%0Aimport%20com.mysql.jdbc.Driver%3B%0A%0Apublic%20class%20Myfirstjdbcprog%20%0A%7B%0A%09public%20static%20void%20main(String%5B%5D%20args)%0A%09%7B%09%0A%09%09Connection%20con%3Dnull%3B%0A%09%09Statement%20stmt%3Dnull%3B%0A%09%09ResultSet%20rs%3Dnull%3B%0A%09%09%09%09%0A%09%09try%0A%09%09%7B%09%09%09%0A%09%09%09%2F————–*1.%20load%20the%20driver%20————*%2F%0A%09%09%09%0A%09%09%20%20%20%20%20Driver%20rf1%20%3D%20new%20Driver()%3B%0A%09%09%0A%09%09%20%20%20%20%20DriverManager.registerDriver(rf1)%3B%0A%09%09%0A%09%09%20%20%20%20%20%2F*———————————–%0A%09%09%20%20%20%20%20%20*%202.%20get%20the%20db%20connection%20via%20driver%0A%09%09%20%20%20%20%20%20*%20%0A%09%09%20%20%20%20%20%20*%20dburl-protocol%3Asubprotocol%3Asubname%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20*%20Protocol%3A%20JDBC%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20*%20Subprotocol%3Amysql(%20it’s%20depends%20on%20database)%0A%09%09%20%20%20%20%20%20*%20subname%20consist%3A%0A%09%09%20%20%20%20%20%20*%20–hostname%0A%09%09%20%20%20%20%20%20*%20–port%20number%3B%0A%09%09%20%20%20%20%20%20*%20–db%20name%0A%09%09%20%20%20%20%20%20*%20–user%20%26%20password%09%09%20%20%20%20%20%20%0A%09%09%20%20%20%20—————————————-%20*%2F%0A%09%09%0A%09String%20url%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%20%20%20%20%20%20%20%20con%20%3D%20DriverManager.getConnection(url)%3B%0A%09%09%20%20%20%0A%09%09%0A%09%09%2F*%20—–3.%20issues%20sql%20query%20via%20connection——-*%2F%0A%09%09%09%09%0A%09%09String%20query%3D%22select%20*from%20student_detail%22%3B%0A%09%09stmt%3Dcon.createStatement()%3B%0A%09%09rs%3Dstmt.executeQuery(query)%3B%0A%09%09%09%09%0A%09%09%2F*——-4.Process%20the%20result%20return%20by%20sql%20query%20—–*%2F%0A%09%09%0A%09%09while(rs.next())%0A%09%09%20%20%7B%0A%09%09%09String%20id%3Drs.getString(%22user_id%22)%3B%0A%09%09%09String%20f_name%3Drs.getString(%22f_name%22)%3B%0A%09%09%09String%20l_name%3Drs.getString(%22l_name%22)%3B%0A%09%09%09%09%0A%09%09%09System.out.println(%22user_id%3A%22%2Bid)%3B%0A%09%09%09System.out.println(%22first%20name%3A%22%2Bf_name)%3B%0A%09%09%09System.out.println(%22last%20name%3A%22%2Bl_name)%3B%0A%09%09%09%0A%09%09%09System.out.println(%22*******************%22)%3B%09%0A%09%09%20%20%20%7D%0A%09%09%7D%0A%09%09%0A%09%09catch(SQLException%20e)%0A%09%09%7B%0A%09%09%09e.printStackTrace()%3B%0A%09%09%7D%0A%09%09%0A%09%09%2F*—–5.%20Close%20all%20JDBC%20object%20in%20Finally%20block———*%2F%0A%09%09finally%0A%09%09%7B%0A%09%09%09try%0A%09%09%09%7B%0A%09%09%09if(con!%3Dnull)%0A%09%09%09%20%20%20%7B%0A%09%09%09%20%20%20%20con.close()%3B%0A%09%09%09%20%20%20%7D%0A%09%09%09if(stmt!%3Dnull)%0A%09%09%09%20%20%7B%0A%09%09%09%20%20stmt.close()%3B%0A%09%09%09%20%20%7D%0A%09%09%09%0A%09%09%09if(rs!%3Dnull)%0A%09%09%09%20%20%7B%0A%09%09%09%20%20%20%20rs.close()%3B%0A%09%09%09%20%20%7D%0A%09%09%20%20%20%20%20%7D%0A%09%09%09%0A%09%09%09catch(SQLException%20e)%0A%09%09%09%0A%09%09%09%20%20%20%7B%0A%09%09%09%09e.printStackTrace()%3B%0A%09%09%09%20%20%20%7D%0A%09%09%09%0A%09%09%09%7D%09%09%09%0A%09%09%7D%09%0A%09%7D” message=”Java Program to fetch data from MySql Database” highlight=”” provider=”manual”/]

	
Output of the above program:
user_id:1
first name:bhavesh
last name:lakhani
*******************
user_id:2
first name:vivel
last name:patel
*******************
user_id:3
first name:abhilash1
last name:shah
*******************
getConnection method:
getc2
There are Three overloaded Version of getconnection(url) Method:
getc

Example:

1. getConnection(String url)

String url="jdbc:mysql://localhost:3306/test?user=root&password=root";
con=DriverManager.getConnection(url);

2. getConnection(String url,properties info)

String dburl="jdbc:mysql://localhost:3306/test";
FileReader f=new FileReader("E:\\myjdbc.properties");
Properties prop=new Properties();
prop.load(f);
con=DriverManager.getConnection(dburl,prop);

3. getConnectin(String url,String user,String password)

String url="jdbc:mysql://localhost:3306/test";
con=DriverManager.getConnection(url,root,root);
  • Most Efficient way to getconnection using getConnection(String url,properties info) Method
  • In this method we provide user name and password from our property file so it is secure.
  • You can change User without changing into source program.

JDBC Statement object

    • JDBC statement help us to issue SQL query to the database and Retrieve data from database.
    • There are three different type of JDBC statement.
  1. java.sql.Statement
  2. java.sql.PrepareStatement
  3. java.sql.CallableStatement

Note: Once we create JDBC statement then we must invoke any of below method to issue SQL query.

Method to issue different types of SQL query.

int executeUpdate()

  • It is used to execute other than select query(Means insert,update or delete)
  • It return number of rows affected in terms of integer

Resultset executeQquery()

  • It is used to execute select query
  • It return DB result in form of Resultset Object

boolean execute()

  • It is used to execute any type of sql query
  •  It return true if result of type DB Result
  • It returns false if result is type of integer
Note: If we use boolean execute() method then we must make use of getUpdateCount() OR getResultset() method to retrieve actual Result.

Statement Object

  • Statement object is an interface of java.sql.* package.
  • It is used execute Static SQL query

Static SQL query: Static SQL query may or may not have condition and it it has condition then condition value have to be hard coded in SQL query i.e.

  • SELECT* FROM student_detail
  • SELECT* FROM student_detail where user_id=2;
  • INSERT INTO student_detail VALUES(4,’milan’,’Khanpara’);

JDBC program which fetch data from database using Statement Object

[pastacode lang=”java” manual=”package%20com.spy.javaforlearn%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.ResultSet%3B%0Aimport%20java.sql.SQLException%3B%0Aimport%20java.sql.Statement%3B%0Aimport%20com.mysql.jdbc.Driver%3B%0A%0Apublic%20class%20Myfirstjdbcprog%20%0A%7B%0A%0A%09public%20static%20void%20main(String%5B%5D%20args)%0A%09%7B%09%0A%09%09Connection%20con%3Dnull%3B%0A%09%09Statement%20stmt%3Dnull%3B%0A%09%09ResultSet%20rs%3Dnull%3B%0A%09%09%0A%09%09try%0A%09%09%7B%09%09%09%0A%09%09%09%2F*——-%20load%20the%20driver—–*%2F%0A%09%09%09%20Driver%20rf1%20%3D%20new%20Driver()%3B%0A%09%09%20%20%20%20%20DriverManager.registerDriver(rf1)%3B%0A%09%09%0A%20%20%20%20%20%20%20%20%20%20%20%2F*——%20get%20the%20db%20connection%20via%20driver—–%20*%2F%0A%0A%09%20String%20url%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%09%20con%3DDriverManager.getConnection(url)%3B%0A%09%09%20%09%20%20%0A%09%09%2F*——-issues%20sql%20query%20via%20connection——-*%2F%0A%09%09%2F%2FStatic%20SQL%20query%0A%09%09String%20query%3D%22select%20*from%20student_detail%20where%20user_id%3D1%22%3B%0A%09%09stmt%3Dcon.createStatement()%3B%0A%09%09rs%3Dstmt.executeQuery(query)%3B%0A%0A%09%09%2F*—–%20process%20the%20result%20return%20by%20sql%20query—–*%2F%0A%0A%09%09while(rs.next())%0A%09%09%7B%0A%09%09%09String%20id%3Drs.getString(%22user_id%22)%3B%0A%09%09%09String%20f_name%3Drs.getString(%22f_name%22)%3B%0A%09%09%09String%20l_name%3Drs.getString(%22l_name%22)%3B%0A%09%09%09%0A%09%09%09System.out.println(%22user_id%3A%22%2Bid)%3B%0A%09%09%09System.out.println(%22first%20name%3A%22%2Bf_name)%3B%0A%09%09%09System.out.println(%22last%20name%3A%22%2Bl_name)%3B%09%0A%09%09%20%20%7D%0A%09%09%7D%0A%09%09catch(SQLException%20e)%0A%09%09%7B%0A%09%09%09e.printStackTrace()%3B%0A%09%09%7D%0A%09%09finally%0A%09%09%7B%0A%09%09%09try%0A%09%09%09%7B%0A%09%09%09if(con!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09con.close()%3B%0A%09%09%09%7D%0A%09%09%09if(stmt!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09stmt.close()%3B%0A%09%09%09%7D%0A%09%09%09%0A%09%09%09if(rs!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09rs.close()%3B%0A%09%09%09%7D%09%0A%09%09%20%20%20%20%7D%0A%09%09%09%0A%09%09%09catch(SQLException%20e)%0A%09%09%09%0A%09%09%09%20%7B%0A%09%09%09%09e.printStackTrace()%3B%0A%09%09%09%20%7D%09%09%0A%09%09%09%7D%09%09%09%09%09%0A%09%09%7D%09%09%0A%09%7D” message=”JDBC Program to fetch data from Database using Statement object” highlight=”” provider=”manual”/]

	
output:
user_id:1
first name:bhavesh
last name:lakhani

Prepare Statement :

  • PrepareStatement is an interface in java.
  • Object of Prepare Statement help us to execute Dynamic SQL query.

Dynamic SQL query :It must have condition, and one or more condition value get decided at run time i.e.

  • SELECT *FROM student_detail where user_id=? ;
  • INSERT INTO student_detail VALUES(?,?,?) ;
How to Pass Command line argument in Eclipse ?
step 1: click on run arrow beside run button

JDBC Complete guide
step 2: Go to main and select appropriate Project and Main class

runc

step:3 Open argument and give value
Note: If you want to pass multiple argument then separate it using space.

runc2

Program to Fetch data from database using PreparedStatement object

[pastacode lang=”java” manual=”package%20com.spy.javaforlearn%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.PreparedStatement%3B%0Aimport%20java.sql.ResultSet%3B%0A%0Aimport%20%20com.mysql.jdbc.Driver%3B%20%20%0Apublic%20class%20PrepareStatement1%0A%7B%0A%09public%20static%20void%20main(String%5B%5D%20args)%0A%09%7B%0A%09%09Connection%20con%3Dnull%3B%0A%09%09PreparedStatement%20pstmt%3Dnull%3B%0A%09%09ResultSet%20rs%3Dnull%3B%0A%09%09try%0A%09%09%7B%0A%09%09%2F%2F——-1.Load%20the%20Driver——%0A%09%09%09%0A%09%09Driver%20driverref%3Dnew%20Driver()%3B%0A%09%09DriverManager.registerDriver(driverref)%3B%0A%09%09%0A%20%20%20%20%20%20%20%20%2F%2F—–Get%20the%20db%20connection%20via%20driver———%0A%09%09%09%0A%09%20%20%20%20String%20dburl%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%09%20%20%20%20con%3DDriverManager.getConnection(dburl)%3B%0A%09%09%0A%09%09%2F%2F——–Issue%20sql%20query——–%0A%09%20%20%20%20%2F%2F———–dynamic%20SQL———-%0A%09%09%09%0A%20%20%20%20%20%20%20%20String%20query%3D%22Select%20*from%20Student_detail%20where%20user_id%3D%3F%22%3B%0A%09%09pstmt%3Dcon.prepareStatement(query)%3B%0A%09%09pstmt.setString(1%2C%20args%5B0%5D)%3B%2F%2Fassigning%20command%20line%20arg%0A%09%09rs%3Dpstmt.executeQuery()%3B%0A%09%09%0A%09%09%2F%2F——-Process%20result%20return%20by%20mysql%20query——–%0A%09%09%0A%09%09while(rs.next())%0A%09%09%7B%0A%09%09%09String%20id%3Drs.getString(%22user_id%22)%3B%0A%09%09%09String%20f_name%3Drs.getString(%22f_name%22)%3B%0A%09%09%09String%20l_name%3Drs.getString(%22l_name%22)%3B%0A%09%09%09%0A%09%09%09System.out.println(%22user_id%3A%22%2Bid)%3B%0A%09%09%09System.out.println(%22first%20name%3A%22%2Bf_name)%3B%0A%09%09%09System.out.println(%22last%20name%3A%22%2Bl_name)%3B%0A%09%09%7D%0A%09%20%20%20%20%20%7D%0A%09%09catch(Exception%20e)%0A%09%09%20%7B%0A%09%09%09e.printStackTrace()%3B%0A%09%09%20%7D%0A%09%09%09%0A%09%09finally%0A%09%09%7B%0A%09%09%09try%0A%09%09%09%7B%0A%09%09%0A%09%09%09%20%20if(con!%3Dnull)%0A%09%09%0A%09%09%09%20%20%20%7B%0A%09%09%09%20%20%20%20%20con.close()%3B%0A%09%09%0A%09%09%09%20%20%20%7D%0A%09%09%0A%09%20%09%20%20%20%20%20%20if(pstmt!%3Dnull)%0A%09%09%20%20%20%20%20%20%7B%0A%09%09%20%20%20%20%20%09pstmt.close()%3B%0A%09%09%20%20%20%20%20%20%7D%0A%09%09%0A%09%09%20%20%20%20%20%20if(rs!%3Dnull)%0A%09%09%20%20%20%20%20%20%7B%0A%09%09%09%20%20%20%20%20rs.close()%3B%0A%09%09%20%20%20%20%20%20%7D%0A%09%09%20%20%20%20%20%7D%0A%09%20%20%20%20%20%20%20%20%20%20%20%20%20%20catch(Exception%20e)%0A%09%09%20%20%20%20%20%20%7B%0A%09%09%20%20%20%20%20%20%20System.out.println(%22Exception%20occur%22)%3B%20%20%20%0A%09%09%20%20%20%20%20%20%7D%0A%09%20%09%20%7D%0A%09%20%20%20%7D%09%09%09%09%0A%20%20%20%20%20%7D” message=”JDBC Program to fetch data from Database using PreparedStatement object” highlight=”” provider=”manual”/]

	
output:
user_id:2
first name:vivel
last name:patel

Stored Procedure :

  • Stored procedure is a set of SQL query and it is present at database side.
  • Stored procedure improve the performance because it present at database so it is like compile one time use many time.
Syntax to create Stored Procedure
CREATE PROCEDURE procedure_name()
BEGIN
.......
procedure logic goes here
.......
END;

Steps to create and run Stored procedure

  1. First change the delimiter using command
    Delimiter delimiter_symbol;

     DELIMITER &
  2. Create procedure

    Create Procedure to display all data of student_detail table.

    
    

    [pastacode lang=”sql” manual=”CREATE%20PROCEDURE%20getAllStudent()%0ABEGIN%0A%0ASelect%20*from%20student_detail%3B%0A%0AEND%3B” message=”SQL Procedure to display student data” highlight=”” provider=”manual”/]

procedre1

Again make delimiter as it is and call procedure using below command

CALL getAllstudent();

change-delimiter


Scenario how store procedure is important:

Create store procedure insert data into table, if data is already exist
then update otherwise insert itIn our case we already created student_detail tabletable_studentstep: 1 change delimiter

    DELIMITER @

step: 2 create procedure

 CREATE PROCEDURE student_insert_update(in id int,
                                        in fname varchar(10),
                                        in lname varchar(10))
 
 BEGIN
 DECLARE regno_count int;

 select count(*) into regno_count from student_detail where user_id=id;
 
 if regno_count>0 then

 update student_detail
 set f_name=fname,
 l_name=lname where user_id=id;

 else
 insert into student_detail values(id,fname,lname);
 end if;
 
 END @

step:3 again make delimiter as it is

DELIMITER ;

step 4 :call procedure

CALL student_insert_update(2,'vivek','patel');

Note: Here we pass some data in procedure This record is already exist in table so procedure will update that data.

MySQL step by step Execution of procedure

final_dtaa
Here We updated data whose user_id=2


Callable Statement:

  • It is an interface of java.sql.* and It is used to Execute Stored Procedure.

[pastacode lang=”java” manual=”package%20com.spy.javaforlearn%3B%0A%0Aimport%20java.sql.CallableStatement%3B%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.ResultSet%3B%0Aimport%20java.sql.SQLException%3B%0Aimport%20com.mysql.jdbc.Driver%3B%0A%0Apublic%20class%20CallableStatement1%0A%7B%0A%09public%20static%20void%20main(String%5B%5D%20args)%0A%09%7B%09%0A%09%09Connection%20con%3Dnull%3B%0A%09%09CallableStatement%20stmt%3Dnull%3B%0A%09%09ResultSet%20rs%3Dnull%3B%0A%09%09%0A%09%09try%20%0A%09%09%7B%0A%09%09%09%09%09%09%09%09%2F*%0A%09%09%09%20*%20load%20the%20driver%0A%09%09%09%20*%2F%0A%09%09%09Driver%20driverref%3Dnew%20Driver()%3B%0A%09%09%09DriverManager.registerDriver(driverref)%3B%0A%09%09%09%0A%09%09%09%2F*%0A%09%09%09%20*%20get%20db%20connection%20via%20driver%0A%09%09%09%20*%2F%0A%09%09%09%09%20%20%20String%20url%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%09%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20con%3DDriverManager.getConnection(url)%3B%0A%09%09%09%0A%09%20%20%20%20%20%20%20%20%20%20%20%20%20%2F*%0A%09%09%20%20%20%20%20%20*%20issue%20sql%20query%20via%20driver%0A%09%09%20%20%20%20%20%20%20%20%20*%2F%0A%09%09%09%0A%09%20%20%20%20%20%20%20%20%20%20String%20query%3D%22call%20student_insert_update(4%2C’Ankur’%2C’Panesariya’)%22%3B%0A%09%09%09%0A%09%09%09%0A%09%09%20stmt%3Dcon.prepareCall(query)%3B%0A%09%09%09%0A%09%09%09%09%09%09%0A%09%09%20%20boolean%20isdbresult%20%3D%20stmt.execute()%3B%0A%09%09%09%09%09%0A%09%09%20%20if(isdbresult)%09%0A%09%09%20%20%7B%0A%09%09%09%20System.out.println(%22result%20is%20type%20of%20dbresult%22)%3B%20%0A%09%09%09%20%0A%09%09%09%20%20rs%3D%20stmt.getResultSet()%3B%0A%09%09%09%20%0A%09%09%09%20while(rs.next())%0A%09%09%09%20%7B%09%09%09%09%20%0A%09%09%09%09String%20userid%3Drs.getString(%22User_id%22)%3B%20%0A%09%09%09%09String%20fnm%3Drs.getString(%22f_name%22)%3B%0A%09%09%09%09String%20lnm%3Drs.getString(%22l_name%22)%3B%0A%09%09%09%0A%09%09%09%09%0A%09%09%09%09System.out.println(userid)%3B%0A%09%09%09%09System.out.println(fnm)%3B%20%0A%09%09%09%09System.out.println(lnm)%3B%0A%09%09%09%09%20%0A%09%09%09%09%20%20%09%09%09%20%7D%09%09%09%20%0A%09%09%20%20%7D%0A%09%09%20%20else%0A%09%09%20%20%7B%0A%09%09%09%20%20System.out.println(%22result%20is%20type%20of%20integer%20count%3A%22)%3B%0A%09%09%09%0A%09%09%09%20%20int%20count%3Dstmt.getUpdateCount()%3B%0A%20%20%0A%09%09%09%20%20System.out.println(%22no%20of%20row%20affected%3A%20%22%2Bcount)%3B%0A%09%09%09%20%20%09%09%20%20%7D%0A%09%09%09%09%09%09%0A%09%09%7D%20%0A%09%09%0A%09%09catch%20(SQLException%20e)%20%0A%09%09%7B%0A%09%09%09e.printStackTrace()%3B%0A%09%09%7D%0A%09%09%09%09%09%09%0A%09%09%2F*%0A%09%09%20*%20close%20all%20jdbc%20object%0A%09%09%20*%2F%0A%09%09%0A%09%09finally%0A%09%09%7B%0A%09%09%09%0A%09%09%09try%20%7B%0A%09%09%09%09if(con!%3Dnull)%0A%09%09%09%09%0A%09%09%09%09%7B%0A%09%09%09%09%09con.close()%3B%0A%09%09%09%09%7D%0A%09%09%09%09%0A%09%09%09%09if(stmt!%3Dnull)%0A%09%09%09%09%7B%0A%09%09%09%09%09stmt.close()%3B%0A%09%09%09%09%7D%0A%09%09%09%09%0A%09%09%09%09if(rs!%3Dnull)%0A%09%09%09%09%7B%0A%09%09%09%09%09rs.close()%3B%0A%09%09%09%09%7D%0A%09%09%09%09%0A%09%09%09%7D%0A%09%09%09%0A%09%09%09catch%20(SQLException%20e)%20%0A%09%09%09%7B%0A%09%09%09%09%0A%09%09%09%09e.printStackTrace()%3B%0A%09%09%09%7D%09%0A%09%09%7D%0A%09%20%7D%0A%7D” message=”Java Program to call stored procedure in database” highlight=”” provider=”manual”/]

output:
result is type of integer
count:
no of row affected: 1

Now you can see data in Mysql database

screenshot_1

Transaction

  • Transaction is a group of SQL query which are executed as a unit so that either all of them executed or none of them executed
  •  Transaction Help us to achieve Data consistency.
Steps To obtain Transaction

Step 1:  Begin transaction by disabling auto commit mode.

con.setautocommit(false);

step 2:  Issue set of SQL query

step 3:  If no error then Commit Transaction

con.commit();

step 4:  If error then Rollback the Transaction

Scenario: Suppose we are issuing three query which are inserting value into three different table now
suppose after issuing two query some Exception occur so we have to Rollback of Previously Executed query using Transaction Concept.

[pastacode lang=”java” manual=”package%20com.spy.javaforlearn%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.PreparedStatement%3B%0Aimport%20java.sql.SQLException%3B%0A%0Apublic%20class%20TransactionExample%20%0A%7B%0Apublic%20static%20void%20main(String%5B%5D%20args)%0A%7B%0A%09Connection%20con%3Dnull%3B%0A%09PreparedStatement%20pstmt1%3Dnull%3B%0A%09PreparedStatement%20pstmt2%3Dnull%3B%0A%09PreparedStatement%20pstmt3%3Dnull%3B%0A%09%0A%09try%20%0A%09%7B%0A%09%09%0A%09%20%20%20%20Class.forName(%22com.mysql.jdbc.Driver%22).newInstance()%3B%0A%09%09%0A%09%0A%09%20%20%20%20String%20db%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%09%20%20%20%20con%3DDriverManager.getConnection(db)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%2F*%0A%09%20%20%20%20%20*%201.begin%20transaction%20by%20setting%20con.autocmmit(false)%20method%0A%09%20%20%20%20%20*%2F%0A%09%09%20%0A%09%20%20%20con.setAutoCommit(false)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%2F*%0A%09%20%20%20%20%20*%202.issue%20set%20of%20sql%20query%0A%09%20%20%20%20%20*%2F%0A%09%20%20%20%20%0A%09%20%20%20%20String%20query1%3D%22insert%20into%20student_detail%20values(5%2C’sahil’%2C’desai’)%22%3B%0A%09%20%20%20%20pstmt1%3Dcon.prepareStatement(query1)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20int%20count1%3D%20pstmt1.executeUpdate()%3B%0A%09%20%20%20%20%0A%09%20%20%20%20System.out.println(%22rows%20affected%20in%20student_detail%20%22%2Bcount1)%3B%0A%09%20%20%20%20%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20String%20query2%3D%22insert%20into%20User_credential%20values(5%2C’sahil’%2C’abc5′)%22%3B%0A%09%20%20%20%20pstmt2%3Dcon.prepareStatement(query2)%3B%0A%09%20%20%20%0A%09%20%20%20%20int%20count2%3D%20pstmt2.executeUpdate()%3B%0A%09%20%20%20%20%0A%09%20%20%20%20System.out.println(%22rows%20affected%20in%20user_credential%20%22%2Bcount2)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%09%20%20%20%0A%09%20%20%20%20int%20a%3D5%2F0%3B%20%20%2F%2F%20Exception%0A%09%20%20%20%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20String%20query3%3D%22insert%20into%20contact_detail%20values(5%2C’352246′)%22%3B%0A%09%20%20%20%20pstmt3%3Dcon.prepareStatement(query3)%3B%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20int%20count3%3D%20pstmt3.executeUpdate()%3B%0A%09%20%20%20%20%0A%09%20%20%20%20System.out.println(%22rows%20affected%20in%20Contact%20detail%20%22%2Bcount3)%3B%0A%09%20%20%20%20%0A%09%0A%20%20%20%20%20%20%20%20%20%20%20%20con.commit()%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20System.out.println(%22profile%20successfully%20created%20%22)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%20%20%0A%09%7D%0A%09catch(Exception%20e)%0A%09%7B%0A%09%09System.out.println(%22Something%20Wrong%2C%20Please%20try%20again…%22)%3B%0A%09%09%0A%09%09try%20%0A%09%09%7B%0A%09%09%09con.rollback()%3B%0A%09%09%7D%20%0A%09%09catch%20(SQLException%20e1)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%09%09%0A%09%09%09e1.printStackTrace()%3B%0A%09%09%7D%0A%09%09%0A%09%09System.out.println(%22unable%20to%20create%20profile%3A%22)%3B%0A%09%7D%0A%09%0A%09%0A%09finally%0A%09%7B%0A%09%09%0A%09%09try%20%7B%0A%09%09%09if(con!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09con.close()%3B%0A%09%09%09%7D%0A%09%09%09if(pstmt1!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09pstmt1.close()%3B%0A%09%09%09%7D%0A%09%09%09if(pstmt2!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09pstmt2.close()%3B%0A%09%09%09%7D%0A%09%09%09if(pstmt3!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09pstmt3.close()%3B%0A%09%09%09%7D%0A%09%09%7D%0A%09%09catch%20(Exception%20e)%20%0A%09%09%7B%0A%09%09%09%0A%09%09%09e.printStackTrace()%3B%0A%09%09%7D%0A%09%0A%09%09%0A%09%20%20%7D%0A%09%0A%09%7D%0A%0A%7D%0A” message=”” highlight=”” provider=”manual”/]

output:

rows affected in student_detail1
rows affected in user_credential1
Something Wrong, Please try again...
unable to create profile:

Now If There is NO error Then Profile should Successfully create.

[pastacode lang=”java” manual=”package%20com.spy.javaforlearn%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.PreparedStatement%3B%0Aimport%20java.sql.SQLException%3B%0A%0Apublic%20class%20TransactionExample%20%0A%7B%0Apublic%20static%20void%20main(String%5B%5D%20args)%0A%7B%0A%09Connection%20con%3Dnull%3B%0A%09PreparedStatement%20pstmt1%3Dnull%3B%0A%09PreparedStatement%20pstmt2%3Dnull%3B%0A%09PreparedStatement%20pstmt3%3Dnull%3B%0A%09%0A%09try%20%0A%09%7B%0A%09%09%0A%09%09Class.forName(%22com.mysql.jdbc.Driver%22).newInstance()%3B%0A%09%09%0A%09%0A%09%20%20%20%20String%20db%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%09%20%20%20%20con%3DDriverManager.getConnection(db)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%2F*%0A%09%20%20%20%20%20*%201.begin%20transaction%20by%20setting%20con.autocmmit(false)%20method%0A%09%20%20%20%20%20*%2F%0A%09%20%20%20%20con.setAutoCommit(false)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%2F*%0A%09%20%20%20%20%20*%202.issue%20set%20of%20sql%20query%0A%09%20%20%20%20%20*%2F%0A%09%20%20%20%20%0A%09%20%20%20%20String%20query1%3D%22insert%20into%20student_detail%20values(5%2C’sahil’%2C’desai’)%22%3B%0A%09%20%20%20%20pstmt1%3Dcon.prepareStatement(query1)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20int%20count1%3D%20pstmt1.executeUpdate()%3B%0A%09%20%20%20%20%0A%09%20%20%20%20System.out.println(%22rows%20affected%20in%20student_detail%20%22%2Bcount1)%3B%0A%09%20%20%20%20%0A%09%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20String%20query2%3D%22insert%20into%20User_credential%20values(5%2C’sahil’%2C’abc5′)%22%3B%0A%09%20%20%20%20pstmt2%3Dcon.prepareStatement(query2)%3B%0A%09%20%20%20%0A%09%20%20%20%20int%20count2%3D%20pstmt2.executeUpdate()%3B%0A%09%20%20%20%20%0A%09%20%20%20%20System.out.println(%22rows%20affected%20in%20user_credential%20%22%2Bcount2)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%0A%09%20%2F%2F%20%20%20int%20a%3D5%2F0%3B%0A%09%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20String%20query3%3D%22insert%20into%20contact_detail%20values(5%2C’352246′)%22%3B%0A%09%20%20%20%20pstmt3%3Dcon.prepareStatement(query3)%3B%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20int%20count3%3D%20pstmt3.executeUpdate()%3B%0A%09%20%20%20%20%0A%09%20%20%20%20System.out.println(%22rows%20affected%20in%20Contact%20detail%20%22%2Bcount3)%3B%0A%09%20%20%20%20%0A%09%0A%20%20%20%20%20%20%20%20%20con.commit()%3B%0A%20%20%20%20%20%20%20%20%20System.out.println(%22profile%20successfully%20created%20%22)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%20%20%20%20%0A%09%7D%0A%09catch(Exception%20e)%0A%09%7B%0A%09%09System.out.println(%22Something%20Wrong%2C%20Please%20try%20again…%22)%3B%0A%09%09%0A%09%09%0A%09%09%0A%09%09try%20%0A%09%09%7B%0A%09%09%09con.rollback()%3B%0A%09%09%7D%20%0A%09%09catch%20(SQLException%20e1)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%09%09%0A%09%09%09e1.printStackTrace()%3B%0A%09%09%7D%0A%0A%09%09%0A%09%09System.out.println(%22unable%20to%20create%20profile%3A%22)%3B%0A%09%20%20%20%20%7D%0A%09%0A%09%0A%09%20%20%20%20%20finally%0A%09%20%20%20%20%20%7B%0A%09%09%0A%09%09try%20%7B%0A%09%09%09if(con!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09con.close()%3B%0A%09%09%09%7D%0A%09%09%09if(pstmt1!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09pstmt1.close()%3B%0A%09%09%09%7D%0A%09%09%09if(pstmt2!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09pstmt2.close()%3B%0A%09%09%09%7D%0A%09%09%09if(pstmt3!%3Dnull)%0A%09%09%09%7B%0A%09%09%09%09pstmt3.close()%3B%0A%09%09%09%7D%0A%09%20%20%20%20%20%20%7D%0A%09%09%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20catch%20(Exception%20e)%20%0A%09%09%7B%0A%09%09%09%0A%09%09%09e.printStackTrace()%3B%0A%09%09%7D%0A%09%0A%09%09%0A%09%20%20%20%20%7D%0A%09%0A%09%7D%0A%0A%20%20%7D%0A” message=”” highlight=”” provider=”manual”/]

output:

rows affected in student_detail 1
rows affected in user_credential 1
rows affected in Contact detail 1
profile successfully created 

Now you can see Table data

student_detail
st-detail
Contact_detail
contact
user_credential
user_credential

Verifying username and Password Using JDBC

We have already created table Password_info
password_table
JDBC program

[pastacode lang=”java” manual=”package%20jdbc%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.ResultSet%3B%0Aimport%20java.sql.SQLException%3B%0Aimport%20java.sql.PreparedStatement%3B%0A%0Aimport%20com.mysql.jdbc.Driver%3B%0A%0Apublic%20class%20Login%20%0A%7B%0Apublic%20static%20void%20main(String%5B%5D%20args)%0A%7B%0A%0A%09Connection%20con%3Dnull%3B%0A%09PreparedStatement%20p%20%3Dnull%3B%0A%09ResultSet%20rs%3Dnull%3B%0A%09%0A%09%0A%09String%20username%3D%22bhavesh%22%3B%0A%09String%20password%3D%2212345%22%3B%0A%09%0A%09%2F*%0A%09%20*%20load%20the%20driver%0A%09%20*%2F%0A%09%0A%09try%20%0A%09%7B%0A%0A%09%09%0A%09%09Driver%20driverref%3Dnew%20Driver()%3B%0A%09%09DriverManager.registerDriver(driverref)%3B%0A%09%09%0A%09%09%0A%09%09%0A%09%09String%20dburl%3D%22jdbc%3Amysql%3A%2F%2Fbhavesh-pc%3A3306%2Ftest%3Fuser%3Dj2ee%26password%3Dj2ee%22%3B%0A%09%09con%3DDriverManager.getConnection(dburl)%3B%0A%09%09%0A%09%09%2F*%0A%09%09%20*%20issue%20sql%20query%20via%20driver%0A%09%09%20*%2F%0A%09%09%0A%09%09String%20q%20%3D%20%22%20select%20*from%20password_info%20where%20user%3D%3F%20and%20current_password%3D%3F%20%22%3B%0A%09%09%0A%09%09%20p%3D%20con.prepareStatement(q)%3B%0A%09%09%20%0A%09%09%20p.setString(1%2Cusername)%3B%0A%09%09%20p.setString(2%2Cpassword)%3B%0A%09%09%09%09%09%0A%09%20%20%20%20rs%3Dp.executeQuery()%3B%0A%09%20%20%0A%09%09if(rs.next())%0A%09%09%7B%0A%09%09%09System.out.println(%22Login%20successful%22)%3B%0A%09%09%7D%0A%09%09else%0A%09%09%7B%0A%09%09%09%0A%09%09%09System.out.println(%22wrong%20credential%2C%20Try%20again%22)%3B%0A%09%09%7D%0A%09%09%7D%0A%09catch%20(Exception%20e)%0A%20%20%20%20%7B%0A%09%09%0A%09%09e.printStackTrace()%3B%0A%09%7D%0A%09%09%2F*%0A%09%09%20*%20close%20all%20jdbc%20object%0A%09%09%20*%2F%0A%09%09%0A%09%09finally%0A%09%09%7B%0A%09%09%0A%09%09%09try%20%7B%0A%09%09%09%09if(con!%3Dnull)%0A%09%09%09%09%7B%0A%09%09%09%09%09con.close()%3B%0A%09%09%09%09%7D%0A%09%09%09%09if(p!%3Dnull)%0A%09%09%09%09%7B%0A%09%09%09%09%09p.close()%3B%0A%09%09%09%09%7D%0A%09%09%09%09if(rs!%3Dnull)%0A%09%09%09%09%7B%0A%09%09%09%09%09rs.close()%3B%0A%09%09%09%09%7D%0A%09%09%09%7D%20%0A%09%09%09catch%20(SQLException%20e)%20%0A%09%09%09%7B%0A%09%09%09%09%0A%09%09%09%09e.printStackTrace()%3B%0A%09%09%09%7D%0A%09%09%09%09%09%0A%09%09%7D%0A%09%09%09%0A%20%20%20%7D%0A%09%09%0A%7D%0A” message=”JDBC Program to verify username and password” highlight=”” provider=”manual”/]

output:
Login successful


Change Password functionality using JDBC

we already created table Password_info in mysqlpassword_table

JDBC program to Implement change password functionality

[pastacode lang=”java” manual=”package%20jdbc%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.PreparedStatement%3B%0Aimport%20java.sql.SQLException%3B%0A%0Apublic%20class%20Passwordupdate%0A%7B%0A%0A%09public%20static%20void%20main(String%5B%5D%20args)%0A%09%7B%0A%09%09%0A%09%20Connection%20con%3Dnull%3B%0A%09%20PreparedStatement%20pstmt%3Dnull%3B%0A%20%20%20%20%20%0A%09%20String%20username%3D%22bhavesh%22%3B%0A%09%20String%20old_password%3D%2212345%22%3B%0A%09%20String%20new_password%3D%224321%22%3B%0A%09%0A%20%20try%7B%20%0A%09%20%20%20%20%0A%09%20%20%20%20%20%2F*%0A%09%20%20%20%20%20%20*%201.load%20the%20driver%0A%09%20%20%20%20%20%20*%2F%0A%09%09Class.forName(%22com.mysql.jdbc.Driver%22).newInstance()%3B%0A%09%0A%09%09%2F*%0A%09%09%20*%202.get%20the%20db%20connection%20via%20driver%0A%09%09%20*%2F%0A%09%09String%20dburl%3D%22jdbc%3Amysql%3A%2F%2Flocalhost%3A3306%2Ftest%3Fuser%3Droot%26password%3Droot%22%3B%0A%09%20%20%20%20con%3DDriverManager.getConnection(dburl)%3B%0A%09%20%20%20%20%0A%09%20%20%20%20%2F*%0A%09%20%20%20%20%20*%203.issue%20sql%20query%20via%20connection%0A%09%20%20%20%20%20*%2F%0A%09%09String%20query%3D%22update%20password_info%20%22%0A%09%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2B%20%22%20set%20current_password%3D%3F%20%22%0A%09%09%09%09%20%20%20%20%20%2B%20%22%20where%20user%3D%3F%20and%20current_password%3D%3F%22%3B%0A%09%20%20%20%20%20%20%20%20%20%20%20%20%0A%09%09%0A%09%09%20%20%20%20%20%20%20pstmt%3Dcon.prepareCall(query)%3B%0A%09%09%20%20%20%20%20%20%20%0A%09%09%20%20%20%20%20%20%20pstmt.setString(1%2Cnew_password)%3B%0A%09%09%20%20%20%20%20%20%20pstmt.setString(2%2Cusername)%3B%0A%09%09%20%20%20%20%20%20%20pstmt.setString(3%2Cold_password)%3B%0A%09%09%20%20%20%20%20%20%20%0A%09%09%20%20%20%20%20%20%20%0A%09%09%20%20%20%20%20%20%20int%20count%3Dpstmt.executeUpdate()%3B%0A%09%09%20%20%20%0A%09%09%20%20%20%20%20%20if(count%3E0)%0A%09%09%20%20%20%20%20%20%7B%0A%09%09%20%20%20%20%09%20%20System.out.println(%22password%20successfully%20changed%22)%3B%0A%09%09%20%20%20%20%20%20%7D%0A%09%09%20%20%20%20%20%20else%0A%09%09%20%20%20%20%20%20%7B%0A%09%09%20%20%20%20%20%20%20System.out.println(%22unable%20to%20change%20password%2C%20provide%20right%20credential%22)%3B%0A%09%09%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%7D%0A%20%20%0A%20%20%20%20%20%20%20%20catch(Exception%20e)%0A%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%09%0A%09%20%20%20%20%20%20%20e.printStackTrace()%3B%0A%20%20%20%20%20%20%20%20%20%7D%0A%20%20%0A%20%20%20%20%20%20%20%20%20%20%20finally%0A%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%09%20%20%20try%20%7B%0A%09%09%09%09if(con!%3Dnull)%0A%09%09%09%09%20%20%20%7B%0A%09%09%09%09%09%20%20%20con.close()%3B%0A%09%09%09%09%20%20%20%7D%0A%09%09%09%09%20%20%20if(pstmt!%3Dnull)%0A%09%09%09%09%20%20%20%7B%0A%09%09%09%09%09%20%20%20%0A%09%09%09%09%09%20%20pstmt.close()%3B%20%20%0A%09%09%09%09%20%20%20%7D%0A%09%09%09%7D%20%0A%20%20%20%20%20%20%20%20%09%20%20%20%0A%20%20%20%20%20%20%20%20%09%20%20%20catch%20(SQLException%20e)%20%0A%20%20%20%20%20%20%20%20%09%20%20%20%7B%0A%09%09%09%09%2F%2F%20TODO%20Auto-generated%20catch%20block%0A%09%09%09%09e.printStackTrace()%3B%0A%09%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%20%20%0A%09%7D%0A%20%20%7D%0A” message=”JDBC Program to implement change password functionality” highlight=”” provider=”manual”/]

output:
password successfully changed

    After changing passwordupdated-password


Implementing Login functionality in JDBC using MVC Architecture

Model Class

[pastacode lang=”java” manual=”package%20com.javaforlearn%3B%0A%0Apublic%20class%20Student_info%20%0A%7B%0A%0A%09String%20username%3B%0A%09String%20password%3B%0A%09%0A%09public%20String%20getUsername()%20%0A%09%7B%0A%09%09return%20username%3B%0A%09%7D%0A%09%0A%09public%20void%20setUsername(String%20username)%20%0A%09%7B%0A%09%09this.username%20%3D%20username%3B%0A%09%7D%0A%09%0A%09public%20String%20getPassword()%20%0A%09%7B%0A%09%09return%20password%3B%0A%09%7D%0A%09%0A%09public%20void%20setPassword(String%20password)%0A%09%7B%0A%09%09this.password%20%3D%20password%3B%0A%09%7D%0A%7D” message=”Model Class” highlight=”” provider=”manual”/]

DAO(data access object) class

[pastacode lang=”java” manual=”package%20com.javaforlearn%3B%0A%0Aimport%20java.sql.Connection%3B%0Aimport%20java.sql.DriverManager%3B%0Aimport%20java.sql.PreparedStatement%3B%0Aimport%20java.sql.ResultSet%3B%0Aimport%20java.sql.SQLException%3B%0A%0Aimport%20com.mysql.jdbc.Driver%3B%0A%0Apublic%20class%20Student_DAO%20%0A%7B%0A%09%0A%09%0A%09Connection%20con%3Dnull%3B%0A%09PreparedStatement%20p%20%3Dnull%3B%0A%09ResultSet%20rs%3Dnull%3B%0A%09%0A%09%0A%09public%20ResultSet%20authentication(String%20user%2C%20String%20pass)%0A%09%0A%09%7B%0A%09%09%0A%09%09%2F*%0A%09%09%20*%20load%20the%20driver%0A%09%09%20*%2F%0A%09%09%0A%09%09try%20%0A%09%09%7B%0A%0A%09%09%09%0A%09%09%09Driver%20driverref%3Dnew%20Driver()%3B%0A%09%09%09DriverManager.registerDriver(driverref)%3B%0A%09%09%09%0A%09%09%09%0A%09%09%09%0A%09%09%09String%20dburl%3D%22jdbc%3Amysql%3A%2F%2Fbhavesh-pc%3A3306%2Ftest%3Fuser%3Dj2ee%26password%3Dj2ee%22%3B%0A%09%09%09con%3DDriverManager.getConnection(dburl)%3B%0A%09%09%09%0A%09%09%09%2F*%0A%09%09%09%20*%20issue%20sql%20query%20via%20driver%0A%09%09%09%20*%2F%0A%09%09%09%0A%09%09%09String%20q%20%3D%20%22%20select%20*from%20password_info%20where%20user%3D%3F%20and%20current_password%3D%3F%20%22%3B%0A%09%09%09%0A%09%09%09%20p%3D%20con.prepareStatement(q)%3B%0A%09%09%09%20%0A%09%09%09%20p.setString(1%2Cuser)%3B%0A%09%09%09%20p.setString(2%2Cpass)%3B%0A%09%09%09%09%09%09%0A%09%09%20%20%20%20rs%3Dp.executeQuery()%3B%0A%09%09%20%20%20%20%0A%09%09%20%20%20%20%0A%09%09%7D%0A%09%09catch%20(Exception%20e)%0A%09%20%20%20%20%7B%0A%09%09%09%0A%09%09%09e.printStackTrace()%3B%0A%09%09%7D%0A%09%09%09%0A%09%09return%20rs%3B%0A%09%09%0A%09%7D%0A%7D” message=”DAO Class” highlight=”” provider=”manual”/]

Main class

[pastacode lang=”java” manual=”package%20com.javaforlearn%3B%0A%0Aimport%20java.sql.ResultSet%3B%0Aimport%20java.sql.SQLException%3B%0A%0Apublic%20class%20Login%20%0A%7B%0A%09%0A%20%20%20public%20static%20void%20main(String%5B%5D%20args)%0A%20%20%20%7B%0A%20%20%20%20%20%20%20%0A%09%20%20%20%0A%09%20%20%20%0A%09%20%20%20Student_info%20rf%3Dnew%20Student_info()%3B%0A%09%20%20%20%0A%09%20%20%20rf.setUsername(%22bhavesh%22)%3B%0A%09%20%20%20rf.setPassword(%224321%22)%3B%0A%09%20%20%20%0A%09%20%20%20Student_DAO%20rf1%3Dnew%20Student_DAO()%3B%0A%09%20%20%20%0A%09%20%20%20ResultSet%20rs%3Drf1.authentication(rf.getUsername()%2Crf.getPassword())%3B%0A%09%20%20%20%0A%09%20%20%20try%20%0A%09%20%20%20%7B%0A%09%09%20%20%20%0A%09%09if(rs.next())%0A%09%09%20%20%20%7B%0A%09%09%09%20%20%20System.out.println(%22Login%20successful%22)%3B%0A%09%09%09%0A%09%09%20%20%20%7D%0A%09%09else%0A%09%09%7B%0A%09%09%09System.out.println(%22Wrong%20credential%2C%20Try%20again%22)%3B%09%0A%09%09%7D%0A%09%09%0A%09%09%0A%09%20%20%7D%20%0A%09%20%20%20%0A%09%20%20%20catch%20(SQLException%20e)%20%0A%09%20%20%20%7B%0A%09%09%0A%09%09e.printStackTrace()%3B%0A%09%20%20%20%7D%0A%09%09%09%0A%20%20%20%7D%0A%09%09%0A%7D” message=”Main Class” highlight=”” provider=”manual”/]

output:
Login successful

Hope you find easy to understand the concepts. Please Like, Comment and share……….

Read here a quick guide to TypeScript

6 Replies to “JDBC Complete guide with Examples”

  1. Puneet

    Pls write complete blog on SQL starting from basic to advance level like create statements, select, join( in detail) also primary, foreign, unique,etc with examples

    Procedures
    Trigger
    View
    Etc in detail with example

Leave a Reply