Skip to main content

JDBC Complete guide

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. screenshot_1

Advantage of JDBC

• Since JDBC API built upon java so it inherits advantage 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 driver
  2.  Get the db connection via driver
  3.  Issue sql query via connection
  4.  Process the result return by sql query
  5.  Close all JDBC object

JDBC is an API(Collection of Packages)
JDBC uses Driver to interact with databasedriver

What is Driver ?
  • Driver is additional software component require by 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.
How to load DriverThere 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
this approach automatically register driver class

Class.forname("com.mysql.jdbc.Driver").newInstance();

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
  • go to eclipse
  • 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 ‘ + other necessary
    resources(text,xml etc)
  •  Jar file help us to transfer the java application from
    one 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 mysql

table-data
Now we will fetch that data using Java Program

Necessary step to work with JDBC
  1. Load driver
  2.  get the db connection via driver
  3.  issue sql query via connection
  4. process the result return by sql query
  5. Close all JDBC object
Java program to fetch data from mysql database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.mysql.jdbc.Driver;
 
public class Myfirstjdbcprog 
{
 
	public static void main(String[] args)
	{
 
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
 
 
		try
		{
 
			/*
			 * 1. load the driver
			 */
 
		     Driver rf1 = new Driver();
 
		     DriverManager.registerDriver(rf1);
 
 
		     /*
		      * 2. get the db connection via driver
		      * 
		      * dburl-protocol:subprotocol:subname
                      * Protocol: JDBC
                      * Subprotocol:mysql( it's depends on database)
		      * subname consist:
		      * --hostname
		      * --port number;
		      * --db name
		      * --user & password		      
		     */
 
 
 
 
	String url="jdbc:mysql://localhost:3306/test?user=root&password=root";
        con=DriverManager.getConnection(url);
 
 
		/*
		 * 
		 * 3. issues sql query via connection
		 * 
		 */
 
 
		String query="select *from student_detail";
		stmt=con.createStatement();
		rs=stmt.executeQuery(query);
 
 
		/*
		 * 
		 *4. process the result return by sql query
                 *
		 */
 
 
		while(rs.next())
		  {
			String id=rs.getString("user_id");
			String f_name=rs.getString("f_name");
			String l_name=rs.getString("l_name");
 
 
			System.out.println("user_id:"+id);
			System.out.println("first name:"+f_name);
			System.out.println("last name:"+l_name);
 
			System.out.println("*******************");
 
		   }
 
		}
 
		catch(SQLException e)
		{
			e.printStackTrace();
		}
 
		/*
                 *  5. Close all JDBC object in Finally block
                 */
		finally
		{
			try
			{
			if(con!=null)
			   {
			    con.close();
			   }
			if(stmt!=null)
			  {
			  stmt.close();
			  }
 
			if(rs!=null)
			  {
			    rs.close();
			  }
		     }
 
			catch(SQLException e)
 
			   {
				e.printStackTrace();
			   }
 
			}			
		}	
	}
 
output:
 
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) Methodgetc

 

Exa.

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
    
  • 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 then select
    query(Means insert,update or delete)
  • It return number of rows affected in terms of integer

Resultset executequery()

  •  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.

1. SELECT* FROM student_detail
2. SELECT* FROM student_detail where user_id=2;
3. INSERT INTO student_detail VALUES(4,'milan','Khanpara');
JDBC program which fetch data from database using Statement Object
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.mysql.jdbc.Driver;
 
public class Myfirstjdbcprog 
{
 
	public static void main(String[] args)
	{
 
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
 
 
		try
		{
 
			/*
			 * load the driver
			 */
			 Driver rf1 = new Driver();
		         DriverManager.registerDriver(rf1);
 
           /*
	    * get the db connection via driver
            * 
            */
 
	 String url="jdbc:mysql://localhost:3306/test?user=root&password=root";
	 con=DriverManager.getConnection(url);
 
		/*
		 * 
		 * issues sql query via connection
		 * 
		 */
 
		//Static SQL query
		String query="select *from student_detail where user_id=1";
		stmt=con.createStatement();
		rs=stmt.executeQuery(query);
 
		/*
		 * process the result return by sql query
		 */
 
		while(rs.next())
		{
			String id=rs.getString("user_id");
			String f_name=rs.getString("f_name");
			String l_name=rs.getString("l_name");
 
			System.out.println("user_id:"+id);
			System.out.println("first name:"+f_name);
			System.out.println("last name:"+l_name);	
		  }
 
		}
 
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
			if(con!=null)
			{
				con.close();
			}
			if(stmt!=null)
			{
				stmt.close();
			}
 
			if(rs!=null)
			{
				rs.close();
			}	
		    }
 
			catch(SQLException e)
 
			 {
				e.printStackTrace();
			 }
 
 
			}
 
		}
 
	}
 
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.

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

run-config
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 Prepare statement object
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
import  com.mysql.jdbc.Driver;//Driver class
 
public class PrepareStatement1
{
	public static void main(String[] args)
	{
 
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try
		{
 
		//1.Load the Driver
 
		Driver driverref=new Driver();
		DriverManager.registerDriver(driverref);
 
        //get the db connection via driver
 
	String dburl="jdbc:mysql://localhost:3306/test?user=root&password=root";
	con=DriverManager.getConnection(dburl);
 
		//issue sql query
 
 
	       //dynamic SQL
                String query="Select *from Student_detail where user_id=?";
		pstmt=con.prepareStatement(query);
		pstmt.setString(1, args[0]);//assigning command line arg
		rs=pstmt.executeQuery();</strong>
 
		//process result return by mysql query
 
		while(rs.next())
		{
			String id=rs.getString("user_id");
			String f_name=rs.getString("f_name");
			String l_name=rs.getString("l_name");
 
 
			System.out.println("user_id:"+id);
			System.out.println("first name:"+f_name);
			System.out.println("last name:"+l_name);
		}
	     }
		catch(Exception e)
		 {
			e.printStackTrace();
		 }
 
		finally
		{
			try
			{
 
			  if(con!=null)
 
			   {
			     con.close();
 
			   }
 
	 	      if(pstmt!=null)
		      {
		     	pstmt.close();
		      }
 
		      if(rs!=null)
		      {
			     rs.close();
		      }
 
		     }
 
	              catch(Exception e)
		      {
		       System.out.println("Exception occur");   
		      }
 
	 	 }
	   }				
     }
 
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.
How to create Stored Procedure

syntax:

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.

CREATE PROCEDURE getAllStudent()
BEGIN
Select *from student_detail;
END;

procedre1

3. 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 table

table_student

step: 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.
Java Program Which calling stored procedure in database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
package com.spy.javaforlearn;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.mysql.jdbc.Driver;
 
public class CallableStatement1
{
 
	public static void main(String[] args)
	{
 
		Connection con=null;
		CallableStatement stmt=null;
		ResultSet rs=null;
 
 
		try 
		{
 
 
			/*
			 * load the driver
			 */
			Driver driverref=new Driver();
			DriverManager.registerDriver(driverref);
 
			/*
			 * get db connection via driver
			 */
 
	   String url="jdbc:mysql://localhost:3306/test?user=root&password=root";
	   con=DriverManager.getConnection(url);
 
	             /*
		      * issue sql query via driver
		     */
 
	String query="call student_insert_update(4,'Ankur','Panesariya')";
 
 
			stmt=con.prepareCall(query);
 
 
 
		  boolean isdbresult = stmt.execute();
 
		  if(isdbresult)	
		  {
			 System.out.println("result is type of dbresult"); 
 
			  rs= stmt.getResultSet();
 
			 while(rs.next())
			 {
 
				String userid=rs.getString("User_id"); 
				String fnm=rs.getString("f_name");
				String lnm=rs.getString("l_name");
 
 
				System.out.println(userid);
				System.out.println(fnm); 
				System.out.println(lnm);
 
 
			 }
 
		  }
		  else
		  {
			  System.out.println("result is type of integer count:");
 
			  int count=stmt.getUpdateCount();
 
			  System.out.println("no of row affected: "+count);
 
		  }
 
 
 
 
		} 
 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
 
 
 
		/*
		 * close all jdbc object
		 */
 
		finally
		{
 
			try {
				if(con!=null)
 
				{
					con.close();
				}
 
				if(stmt!=null)
				{
					stmt.close();
				}
 
				if(rs!=null)
				{
					rs.close();
				}
 
			}
 
			catch (SQLException e) 
			{
 
				e.printStackTrace();
			}	
		}
	 }
}
 
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class TransactionExample 
{
public static void main(String[] args)
{
	Connection con=null;
	PreparedStatement pstmt1=null;
	PreparedStatement pstmt2=null;
	PreparedStatement pstmt3=null;
 
	try 
	{
 
	    Class.forName("com.mysql.jdbc.Driver").newInstance();
 
 
	    String db="jdbc:mysql://localhost:3306/test?user=root&password=root";
	    con=DriverManager.getConnection(db);
 
 
	    /*
	     * 1.begin transaction by setting con.autocmmit(false) method
	     */
 
	   con.setAutoCommit(false);
 
 
	    /*
	     * 2.issue set of sql query
	     */
 
	    String query1="insert into student_detail values(5,'sahil','desai')";
	    pstmt1=con.prepareStatement(query1);
 
	    int count1= pstmt1.executeUpdate();
 
	    System.out.println("rows affected in student_detail "+count1);
 
 
 
 
	    String query2="insert into User_credential values(5,'sahil','abc5')";
	    pstmt2=con.prepareStatement(query2);
 
	    int count2= pstmt2.executeUpdate();
 
	    System.out.println("rows affected in user_credential "+count2);
 
 
 
	    int a=5/0;  // Exception</strong>
 
 
	    String query3="insert into contact_detail values(5,'352246')";
	    pstmt3=con.prepareStatement(query3);   
 
	    int count3= pstmt3.executeUpdate();
 
	    System.out.println("rows affected in Contact detail "+count3);
 
 
            con.commit();
            System.out.println("profile successfully created ");
 
 
	}
	catch(Exception e)
	{
		System.out.println("Something Wrong, Please try again...");
 
		try 
		{
			con.rollback();
		} 
		catch (SQLException e1) 
                {
 
			e1.printStackTrace();
		}
 
		System.out.println("unable to create profile:");
	}
 
 
	finally
	{
 
		try {
			if(con!=null)
			{
				con.close();
			}
			if(pstmt1!=null)
			{
				pstmt1.close();
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
			}
			if(pstmt3!=null)
			{
				pstmt3.close();
			}
		}
		catch (Exception e) 
		{
 
			e.printStackTrace();
		}
 
 
	  }
 
	}
 
}
 
 
output:
 
rows affected in student_detail1
rows affected in user_credential1
<strong>Something Wrong, Please try again...</strong>
unable to create profile:

Now If There is NO error Then Profile should Successfully create.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class TransactionExample 
{
public static void main(String[] args)
{
	Connection con=null;
	PreparedStatement pstmt1=null;
	PreparedStatement pstmt2=null;
	PreparedStatement pstmt3=null;
 
	try 
	{
 
		Class.forName("com.mysql.jdbc.Driver").newInstance();
 
 
	    String db="jdbc:mysql://localhost:3306/test?user=root&password=root";
	    con=DriverManager.getConnection(db);
 
 
	    /*
	     * 1.begin transaction by setting con.autocmmit(false) method
	     */
	    con.setAutoCommit(false);
 
 
	    /*
	     * 2.issue set of sql query
	     */
 
	    String query1="insert into student_detail values(5,'sahil','desai')";
	    pstmt1=con.prepareStatement(query1);
 
	    int count1= pstmt1.executeUpdate();
 
	    System.out.println("rows affected in student_detail "+count1);
 
 
 
 
	    String query2="insert into User_credential values(5,'sahil','abc5')";
	    pstmt2=con.prepareStatement(query2);
 
	    int count2= pstmt2.executeUpdate();
 
	    System.out.println("rows affected in user_credential "+count2);
 
 
 
	 //   int a=5/0;
 
 
 
 
	    String query3="insert into contact_detail values(5,'352246')";
	    pstmt3=con.prepareStatement(query3);   
 
	    int count3= pstmt3.executeUpdate();
 
	    System.out.println("rows affected in Contact detail "+count3);
 
 
         con.commit();
         System.out.println("profile successfully created ");
 
 
 
 
	}
	catch(Exception e)
	{
		System.out.println("Something Wrong, Please try again...");
 
 
 
		try 
		{
			con.rollback();
		} 
		catch (SQLException e1) 
                {
 
			e1.printStackTrace();
		}
 
 
		System.out.println("unable to create profile:");
	    }
 
 
	     finally
	     {
 
		try {
			if(con!=null)
			{
				con.close();
			}
			if(pstmt1!=null)
			{
				pstmt1.close();
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
			}
			if(pstmt3!=null)
			{
				pstmt3.close();
			}
	      }
 
               catch (Exception e) 
		{
 
			e.printStackTrace();
		}
 
 
	    }
 
	}
 
  }
 
 
 
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
package jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
 
import com.mysql.jdbc.Driver;
 
public class Login 
{
public static void main(String[] args)
{
 
	Connection con=null;
	PreparedStatement p =null;
	ResultSet rs=null;
 
 
	String username="bhavesh";
	String password="12345";
 
	/*
	 * load the driver
	 */
 
	try 
	{
 
 
		Driver driverref=new Driver();
		DriverManager.registerDriver(driverref);
 
 
 
		String dburl="jdbc:mysql://bhavesh-pc:3306/test?user=j2ee&password=j2ee";
		con=DriverManager.getConnection(dburl);
 
		/*
		 * issue sql query via driver
		 */
 
		String q = " select *from password_info where user=? and current_password=? ";
 
		 p= con.prepareStatement(q);
 
		 p.setString(1,username);
		 p.setString(2,password);
 
	    rs=p.executeQuery();
 
		if(rs.next())
		{
			System.out.println("Login successful");
		}
		else
		{
 
			System.out.println("wrong credential, Try again");
		}
		}
	catch (Exception e)
    {
 
		e.printStackTrace();
	}
		/*
		 * close all jdbc object
		 */
 
		finally
		{
 
			try {
				if(con!=null)
				{
					con.close();
				}
				if(p!=null)
				{
					p.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (SQLException e) 
			{
 
				e.printStackTrace();
			}
 
		}
 
   }
 
}
 
output:
Login successful

Change Password functionality using JDBC

we already created table Password_info in mysql

password_table

JDBC program to Implement change password functionality

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
package jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class Passwordupdate
{
 
	public static void main(String[] args)
	{
 
	 Connection con=null;
	 PreparedStatement pstmt=null;
 
	 String username="bhavesh";
	 String old_password="12345";
	 String new_password="4321";
 
  try{ 
 
	     /*
	      * 1.load the driver
	      */
		Class.forName("com.mysql.jdbc.Driver").newInstance();
 
		/*
		 * 2.get the db connection via driver
		 */
		String dburl="jdbc:mysql://localhost:3306/test?user=root&password=root";
	    con=DriverManager.getConnection(dburl);
 
	    /*
	     * 3.issue sql query via connection
	     */
		String query="update password_info "
	                 + " set current_password=? "
				     + " where user=? and current_password=?";
 
 
		       pstmt=con.prepareCall(query);
 
		       pstmt.setString(1,new_password);
		       pstmt.setString(2,username);
		       pstmt.setString(3,old_password);
 
 
		       int count=pstmt.executeUpdate();
 
		      if(count>0)
		      {
		    	  System.out.println("password successfully changed");
		      }
		      else
		      {
		       System.out.println("unable to change password, provide right credential");
		      }
         }
 
        catch(Exception e)
         {
 
	       e.printStackTrace();
         }
 
           finally
           {
        	   try {
				if(con!=null)
				   {
					   con.close();
				   }
				   if(pstmt!=null)
				   {
 
					  pstmt.close();  
				   }
			} 
 
        	   catch (SQLException e) 
        	   {
				// TODO Auto-generated catch block
				e.printStackTrace();
	           }
              }  
 
 
	}
  }
 
output:
password successfully changed
After changing password

updated-password

Implementing Login functionality in JDBC using MVC Architecture
Model Class
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.javaforlearn;
 
public class Student_info 
{
 
	String username;
	String password;
 
	public String getUsername() 
	{
		return username;
	}
 
	public void setUsername(String username) 
	{
		this.username = username;
	}
 
	public String getPassword() 
	{
		return password;
	}
 
	public void setPassword(String password)
	{
		this.password = password;
	}
}
DAO(data access object) class
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.mysql.jdbc.Driver;
 
public class Student_DAO 
{
 
 
	Connection con=null;
	PreparedStatement p =null;
	ResultSet rs=null;
 
 
	public ResultSet authentication(String user, String pass)
 
	{
 
		/*
		 * load the driver
		 */
 
		try 
		{
 
 
			Driver driverref=new Driver();
			DriverManager.registerDriver(driverref);
 
 
 
			String dburl="jdbc:mysql://bhavesh-pc:3306/test?user=j2ee&password=j2ee";
			con=DriverManager.getConnection(dburl);
 
			/*
			 * issue sql query via driver
			 */
 
			String q = " select *from password_info where user=? and current_password=? ";
 
			 p= con.prepareStatement(q);
 
			 p.setString(1,user);
			 p.setString(2,pass);
 
		    rs=p.executeQuery();
 
 
		}
		catch (Exception e)
	    {
 
			e.printStackTrace();
		}
 
		return rs;
 
	}
}
Main class
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.javaforlearn;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class Login 
{
 
   public static void main(String[] args)
   {
 
 
 
	   Student_info rf=new Student_info();
 
	   rf.setUsername("bhavesh");
	   rf.setPassword("4321");
 
	   Student_DAO rf1=new Student_DAO();
 
	   ResultSet rs=rf1.authentication(rf.getUsername(),rf.getPassword());
 
	   try 
	   {
 
		if(rs.next())
		   {
			   System.out.println("Login successful");
 
		   }
		else
		{
			System.out.println("Wrong credential, Try again");	
		}
 
 
	  } 
 
	   catch (SQLException e) 
	   {
 
		e.printStackTrace();
	   }
 
   }
 
}
 
output:
Login successful