JDBC

Advanced Java

JDBC : To develop Persistence layer
Servlets : To develop Web layer
JSP : To develop Presentation layer

	JDBC --> Hibernate --> JPA
	Servlets --> Spring Web MVC
	JSP --> Angular / React JS

Prerequisites 

	1) Core Java
		- Class
		- Object
		- Variables
		- Methods
		- Arrays
		- Strings
		- OOPS
	2) SQL (DDL, DML, DQL, DCL)
	3) Basics of HTML

What is Software Project ?

Types of Applications using Java

Standalone Applications

	Ex: Eclipse IDE, Calculator, Notepad++ etc..

Web Applications (C 2 B)

	Ex: www.gmail.com, www.facebook.com etc...

Distributed Applications (B 2 B)

	Ex: gpay, phonepay, paytm, makemytrip etc...

How to deliver project to client ?

Standalone Application

Web Application

Enterprise Application (Distributed Application)

Java Project Architecture

Java Project Architecture
Presentation Layer : User Interface (UI)

	 - HTML & CSS
	 - Java Script
	 - BootStrap
	 - JSP (Java Server Pages)
	 - Angular / React JS

Web Layer : It contains logic to deal with Request & Response

	- Servlets
	- Spring Web MVC

Business Layer : It contains business logic

	- form validation
	- sending email
	- sending OTP
	- generate excel / pdf
	- calculations

Persistence Layer : It contains logic to communicate with database

	- JDBC
	- Spring JDBC
	- Hibernate
	- Data JPA

JAR Files

How to create JAR file 

	// Syntax to create a jar file
	jar  cvfe  <filename.jar>  <main-class>  *.class

	a)  jar is a command to deal with jar files
			c - create
			v - verbose
			f - file
			e - entrypoint
	b)  main-class represents entrypoint for our application execution
	c) *.class means packaging all .class files available in current working directory

How to run JAR file 

	// Syntax to run jar file
	java  -jar  <jar-file-name.jar>

Note: Only the jar files with entry point can be executed.

How to extract JAR file 

	// Syntax to extract jar file
	jar -xvf  <jar-file-name.jar>

	x - extract
	v - verbose
	f - file

Note: -v flag is optional while creating and extracting a jar file.

Task:

	- Create User class & Student class 
	- Compile both User & Student classes
	- Package User.class & Student.class as a jar file (Name: project.jar)
	- After jar is created then delete all .java & .class files
	- Create Demo.java class with main ( ) method. Create Objects for User & Student  and print hashCode of both objects.

CLASSPATH

What is the difference between PATH & CLASSPATH ?

What is API ?

How to create documentation for our project ? 

	// Syntax to create documentation
	javadoc *.java

	// Documentation comment
	
	/**
	*
	*
	*
	*/

What is Build Path ?

Note: Loading the driver is optional if you are using IDE and you have set the build path in the IDE.

Java De-Compiler

Database

JDBC

JDBC Architecture

Note: We need to download that jar file and add to project build path.

JDBC API Components

Interfaces 

	Driver
	Connection
	Statement
	PreparedStatement
	CallableStatement
	ResultSet
	RowSet

Classes 

	DriverManager
	Types
	Date

Exceptions 

	SQLException

Steps to develop JDBC Program 

	1) Load the Driver class
	2) Get Connection from Database
	3) Create Statement / Prepared Statement / Callable Statement
	4) Execute Query
	5) Process the Result
	6) Close the Connection

Note: Every database have limited no of connections, so we should always close the connection after our work is done.
Note: When we load the driver class, static block of driver class will be executed and it will register the driver.

Setup Database & Table in MySQL 

	-- To display list of databases available
	$ show databases;
	
	-- To create new database in DB server
	$ create database advjdb;
	
	-- To Select database to perform our operations
	$ use advjdb;
	
	-- To display all tables available in the database
	$ show tables;
	
	-- Create Table in the database
	CREATE TABLE BOOKS ( 
		BOOK_ID INT(10),
		BOOK_NAME VARCHAR(100),
		BOOK_PRICE INT(10)
	);
	
	-- To make changes permanent in database
	$ commit;

First JDBC Application 

	// Java program to insert record into database
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.Statement;
	
	public class InsertBook {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String INSERT_SQL = "INSERT INTO BOOKS VALUES(102, 'Python', 2000)";

		public static void main(String[] args) throws Exception {
			// Step-1 : Load Driver
			Class.forName("com.mysql.cj.jdbc.Driver");
			// Step-2 : Get DB Connection
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			// Step-3 : Create Statement
			Statement stmt = con.createStatement();
			// Step-4 : Execute Query
			int rowsEffected = stmt.executeUpdate(INSERT_SQL);
			// Step-5 : Process Result
			System.out.println("Record Inserted Count :: " + rowsEffected);
			// Step-6 : Close Connection
			con.close();
		}
	}

Assignment 1: Develop JDBC application to update a record in database table.

Assignment 2: Develop JDBC application to delete a record from database table.

Types Of Queries 

	// Syntax to execute non-select queries
	int executeUpdate(String sql);

Note: The above method parameter represents query which we want to execute and method return type represents how many rows effected in db table with given query.

	// Syntax to execute select queries
	ResultSet executeQuery(String sql);

Select Operation using JDBC 

	// JDBC Application to Select records from database
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.Statement;
	import java.sql.ResultSet;

	public class SelectBooks {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String SELECT_SQL = "SELECT * FROM BOOKS WHERE BOOK_ID = 1002";

		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_SQL);
	
			if (rs.next()) {
				int bookid = rs.getInt("BOOK_ID");
				String name = rs.getString("BOOK_NAME");
				double price = rs.getDouble("BOOK_PRICE");
	
				System.out.println(bookid);
				System.out.println(name);
				System.out.println(price);
			} else {
				System.out.println("No Records Found");
			}
			con.close();
		}
	}

Requirement : Write a java program to retrieve all the records from the database table and display on the console.

	// Java program to retrieve all the records from database table
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.Statement;
	import java.sql.ResultSet;
	
	public class SelectBooks {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String SELECT_SQL = "SELECT * FROM BOOKS";
	
		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_SQL);
	
			while (rs.next()) {
				System.out.println(rs.getInt("BOOK_ID"));
				System.out.println(rs.getString("BOOK_NAME"));
				System.out.println(rs.getDouble("BOOK_PRICE"));
			}
			con.close();
		}
	}

Note : By Default ResultSet cursor will move in forward direction. Based on the Requirement we can make it as Bi Directional.

Assignment 1 : Registration & Login 

	// Assignment 1 : Registration & Login
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.Scanner;
	
	public class LoginRegistration {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";

		public static void main(String[] args) throws Exception {
			String name;
			String email;
			String pass;
			String no;
			Character choice;
			
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement st = con.createStatement();
			PreparedStatement ps;
			ResultSet rs;
			
			boolean flag = true;
			
			do {
				System.out.println("Choose one option : ");
				System.out.println("1. Registration (Press 'R')");
				System.out.println("2. Login (Press 'L')");
				System.out.println("3. Print All Users (Press 'P')");
				System.out.println("4. Quit (Press 'Q')");
				System.out.print("\nEnter your choice : ");
				
				Scanner sc = new Scanner(System.in);
				choice = sc.nextLine().toUpperCase().charAt(0);
				
				switch (choice) {
				case 'R':
					System.out.println("\nRegistration Strated ...");
					
					System.out.print("Enter name : ");
					name = sc.nextLine();
					
					System.out.print("Enter email : ");
					email = sc.nextLine();
					
					ps = con.prepareStatement("SELECT EMAIL FROM USER WHERE EMAIL = ?");
					ps.setString(1, email);
					rs = ps.executeQuery();
					
					if(rs.next()) {
						System.out.println("Duplicate email");
						System.out.println("Registration ended\n");
						break;
					}
					
					System.out.print("Enter pass : ");
					pass = sc.nextLine();
					
					System.out.print("Enter mobile no : ");
					no = sc.nextLine();
					
					ps = con.prepareStatement("INSERT INTO USER VALUES(?, ?, ?, ?)");
					ps.setString(1, name);
					ps.setString(2, email);
					ps.setString(3, pass);
					ps.setString(4, no);
					ps.executeUpdate();
					
					System.out.println("Registration successfull\n");
					
					break;
					
				case 'L' :
					login(con, sc);
					
					break;
					
				case 'P' :
					print(st);
					
					break;
					
				case 'Q' :
					flag = false;
					break;
				default:
					System.out.println("Invalid choice");
					break;
				}
			} while(flag);
			
			con.close();
		}
	
		private static void print(Statement st) throws SQLException {
			ResultSet rs;
			rs = st.executeQuery("SELECT * FROM USER");
			System.out.println("\nPrinting started ...");
			while(rs.next()) {
				System.out.print(rs.getString(1)+" ");
				System.out.print(rs.getString(2)+" ");
				System.out.print(rs.getString(3)+" ");
				System.out.println(rs.getString(4));
			}
			System.out.println("Printing ended\n");
		}
	
		private static void login(Connection con, Scanner sc) throws SQLException {
			
			String email;
			String pass;
			PreparedStatement ps;
			ResultSet rs;
			
			System.out.println("\nLogin Started ...");
			
			System.out.print("Enter email : ");
			email = sc.nextLine();
			
			System.out.print("Enter pass : ");
			pass = sc.nextLine();
			
			ps = con.prepareStatement("SELECT PASS FROM USER WHERE EMAIL = ?");
			ps.setString(1, email);
			
			rs = ps.executeQuery();
			
			if(rs.next() && pass.equals(rs.getString("PASS"))) {
					System.out.println("Login successfull\n");
			} else {
				char logFlag;
				System.out.println("Invalid Credentials");
				System.out.println("Do you want to try again ?");
				System.out.println("Pres 'Y' for Yes or 'N' for No");
				
				logFlag = sc.nextLine().toUpperCase().charAt(0);
				
				if(logFlag == 'Y') {
					login(con,sc);
				} else {
					System.out.println();
				}
			}
		}
	}

ResultSet

Result Set

Note: By Default ResultSet is FORWARD_DIRECTIONAL

	// Java Program using ResultSet
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.Statement;
	
	public class Test {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";

		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = st.executeQuery("select * from books");
	
			rs.absolute(2);
			rs.updateInt(3, 2500);
			rs.updateRow();
			
			con.close();
		}
	}

	// Java Program using ResultSet
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DatabaseMetaData;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.Statement;
	
	public class SelectBooks {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String SELECT_SQL = "SELECT BOOK_ID, BOOK_NAME, BOOK_PRICE FROM BOOKS";
	
		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery(SELECT_SQL);
	
			System.out.println("Query Execution Completed... Data available in ResultSet...");
			while (rs.next()) {
				System.in.read();
				System.in.read();
				rs.refreshRow();
				System.out.println(rs.getInt(1) + "---" + rs.getString(2) + "--" + rs.getDouble(3));
			}
			con.close();
		}
	}

Q: What is difference between SENSITIVE and INSENSITIVE ResultSet ?

ResultSetMetaData 

	// Java Program on ResultSetMetaData
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.Statement;
	
	class Test {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";

		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from books");
			
			ResultSetMetaData metaData = rs.getMetaData();
			
			System.out.print("Column count : "+metaData.getColumnCount());
			System.out.println();
			
			for(int i = 1; i <= metaData.getColumnCount(); i++) {
				String name = metaData.getColumnName(i);
				System.out.println(name);
			}
			
			con.close();
		}
	}

Prepared Statement

Note: Positional Parameters index will start from 1.

	public String login (String name , String pwd ) {
		"select * from user where names = ' "+name+" ' and pass = ' "+pwd+" ' ";
	}

	name : ashok--
	pwd: 123

	select * from users where uname=ashok -- and pwd = 123;

Note: By using PreparedStatement we can avoid SQL injections. We should not prepare a query using concatenation because in that case there is a chance of SQL injection.

	// Java Program using PreparedStatement
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.Statement;
	import java.util.Scanner;
	
	class Test {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";

		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			PreparedStatement ps = con.prepareStatement("insert into books values(?, ?, ?)");
			
			ps.setInt(1, 104);
			ps.setString(2, "AWS");
			ps.setInt(3, 4000);
			
			int rowsInserted = ps.executeUpdate();
			System.out.println("Rows Inserted : "+rowsInserted);
			
			con.close();
		}
	}

Assignment 2 : Retrieve Books 

	// Assignment 2 : Retrieve Books
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.util.Scanner;
	
	public class DynamicSelectBooks {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";

		public static void main(String[] args) throws Exception {
			Scanner s = new Scanner(System.in);
			System.out.println("Enter Price");
			double price = s.nextDouble();
	
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
	
			StringBuilder sql = new StringBuilder("SELECT * FROM BOOKS");
			if (price > 0) {
				sql.append(" WHERE BOOK_PRICE <= ?");
			}
	
			PreparedStatement pstmt = con.prepareStatement(sql.toString());
			if (price > 0) {
				pstmt.setDouble(1, price);
			}
	
			ResultSet rs = pstmt.executeQuery();
	
			while (rs.next()) {
				System.out.println(rs.getInt(1) + "--" + rs.getString(2) + "--" + rs.getDouble(3));
			}
			
			con.close();
		}
	}

Assignment 3 : Retrieve Employees 

	-- Creating Employee Table
	CREATE TABLE EMPLOYEE (
		EMP_ID INT PRIMARY KEY,
		EMP_NAME VARCHAR(100),
		EMP_SALARY INT,
		EMP_DEPT VARCHAR(100),
		EMP_GENDER VARCHAR(10),
		WORK_LOCATION VARCHAR(100)
	);

	-- Inserting Records into Employee Table
	INSERT INTO EMPLOYEE VALUES (1, 'John', 15000.00, 'Admin', 'Male', 'Hyd');
	INSERT INTO EMPLOYEE VALUES (2, 'Smith', 16000.00, 'HR', 'Male', 'Delhi');
	INSERT INTO EMPLOYEE VALUES (3, 'Anil', 7000.00, 'Security', 'Male', 'Hyd');
	INSERT INTO EMPLOYEE VALUES (4, 'Rose', 12000.00, 'HR', 'FeMale', 'Hyd');
	INSERT INTO EMPLOYEE VALUES (5, 'Cathy', 16000.00, 'Sales', 'FeMale', 'Delhi');

	// Assignment 3 : Retrieve Employees
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.util.Scanner;
	
	public class SearchEmps {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		
		public static void main(String[] args) throws Exception{
			Scanner s = new Scanner(System.in);
			
			System.out.println("Enter Dept :: ");
			String dept = s.next();
			
			System.out.println("Enter Location :: ");
			String workLocation  = s.next();
			
			System.out.println("Enter Gender :: ");
			String gender = s.next();
			
			StringBuilder sql = new StringBuilder("SELECT * FROM EMPLOYEE WHERE 1=1 ");
			
			if(dept!=null && !dept.equals("null")){
				sql.append(" AND EMP_DEPT= ?");
			}
			
			if(workLocation!=null && !workLocation.equals("null")) {
				sql.append(" AND WORK_LOCATION = ?");
			}
			
			if(gender!=null && !gender.equals("null")) {
				sql.append(" AND EMP_GENDER = ?");
			}
			
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			
			PreparedStatement  pstmt = con.prepareStatement(sql.toString());
			
			int index = 1;
			
			if(dept!=null && !dept.equals("null")){
				pstmt.setString(index, dept);
				index ++;
			}
			
			if(workLocation!=null && !workLocation.equals("null")) {
				pstmt.setString(index, workLocation);
				index ++;
			}
			
			if(gender!=null && !gender.equals("null")) {
				pstmt.setString(index, gender);
			}
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getInt(1) + "--" +rs.getString(2)+"--"+rs.getInt(3)+"--"+
						rs.getString(4)+"--"+rs.getString(5)+ "--"+rs.getString(6));
			}
			con.close();
		}
	}

Assignment 4 : Salary Increment 

	// Assignment 4 : Approach 1 (Not Recommended)
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.Statement;
	import java.util.Scanner;
	
	public class EmpHike {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String SELECT_SQL = "SELECT * FROM EMPLOYEE";
		private static final String UPDATE_SAL_SQL = "UPDATE EMPLOYEE SET EMP_SALARY=? WHERE EMP_ID=?";
	
		public static void main(String[] args) throws Exception {
			Scanner s = new Scanner(System.in);
			System.out.println("Enter Emp Hike :: ");
			double hike = s.nextDouble();
			
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_SQL);
			PreparedStatement pstmt = con.prepareStatement(UPDATE_SAL_SQL);

			while(rs.next()) {
				int empId = rs.getInt("EMP_ID");
				double existingSal = rs.getDouble("EMP_SALARY");
				double newSal = existingSal + (existingSal * hike) / 100;
				pstmt.setDouble(1, newSal);
				pstmt.setInt(2, empId);
				pstmt.executeUpdate();
			}

			System.out.println("Update completed....");
			con.close();
		}
	}


	// Assignment 4 : Approach - 2 (Recommended)
	package in.ashokit;
		
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.Statement;
	import java.util.Scanner;
	
	public class EmpHike {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		
		public static void main(String[] args) throws Exception {
			Scanner s = new Scanner(System.in);
			System.out.println("Enter Emp Hike :: ");
			double hike = s.nextDouble();
			
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			
			String UPDATE_SAL_SQL = "UPDATE EMPLOYEE SET EMP_SALARY=EMP_SALARY + (EMP_SALARY * ?) / 100 ";
	
			PreparedStatement pstmt = con.prepareStatement(UPDATE_SAL_SQL);
			pstmt.setDouble(1, hike);
	
			pstmt.executeUpdate();
			
			System.out.println("Update completed....");
			con.close();
		}
	}

Note: With the above code in the single shoot we can update the salary of all the employees because business logic will execute at database.

Requirement 

	// JDBC Application to give salary hike
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.util.Scanner;
	
	class Test {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
	
		public static void main(String[] args) throws Exception {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Scanner sc = new Scanner(System.in);
	
			System.out.print("Enter Admin dept hike : ");
			int admin = sc.nextInt();
			System.out.println();
			
			System.out.print("Enter HR dept hike : ");
			int hr = sc.nextInt();
			System.out.println();
			
			System.out.print("Enter Security dept hike : ");
			int security = sc.nextInt();
			System.out.println();
			
			System.out.print("Enter Sales dept hike : ");
			int sales = sc.nextInt();
			System.out.println();
			
			PreparedStatement ps = con.prepareStatement("update employee set emp_salary = emp_salary + (emp_salary*?/100) where emp_dept = ?");
			
			ps.setInt(1, admin);
			ps.setString(2, "Admin");
			ps.executeUpdate();
			
			ps.setInt(1, hr);
			ps.setString(2, "HR");
			ps.executeUpdate();
			
			ps.setInt(1, security);
			ps.setString(2, "Security");
			ps.executeUpdate();
			
			ps.setInt(1, sales);
			ps.setString(2, "Sales");
			ps.executeUpdate();
			
			System.out.println("Update done ..");
			con.close();
		}
	}

Callable Statement 

	-- Procedure without IN & OUT Parameters
	DELIMITER $$
	CREATE PROCEDURE getBooksData( )
	BEGIN
		SELECT * FROM BOOKS;
	END $$

	-- Call the procedure in workbench
	call getBooksDat( );

	// JDBC App to fetch records from DB using Procedure
	package in.ashokit;
	
	import java.sql.CallableStatement;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	
	public class ProcedureCallEx {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String PROCEDURE = "call getBooksData()";
		
		public static void main(String[] args) throws Exception{
			Connection con  = DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
		
			CallableStatement cstmt = con.prepareCall(PROCEDURE);
			
			ResultSet rs = cstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getInt(1) + "-"+rs.getString(2)+"-"+rs.getDouble(3));
			}
			con.close();
		}
	}

	-- PROCEDURE WITH IN PARAMETER
	DELIMITER $$
	CREATE PROCEDURE getBookById(IN BID INT)
	BEGIN
		SELECT * FROM BOOKS WHERE BOOK_ID = BID;
	END $$

	-- Call the procedure in workbench
	call getBookById(101);

	// JDBC App to retrieve book by id using Procedure
	package in.ashokit;
	
	import java.sql.CallableStatement;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.util.Scanner;
	
	public class ProcedureINParamEx {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String PROCEDURE = "call getBookById(?)";
		
		public static void main(String[] args) throws Exception{
			Scanner s = new Scanner(System.in);
			System.out.print("Enter Book Id :: ");
			int bookId = s.nextInt();
			
			Connection con  = DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
			
			CallableStatement cstmt = con.prepareCall(PROCEDURE);
			cstmt.setInt(1, bookId);
			
			ResultSet rs = cstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getInt(1) + "-"+rs.getString(2)+"-"+rs.getDouble(3));
			}
			con.close();
		}
	}

	-- Procedure with IN & OUT Parameters
	DELIMITER $$
	CREATE PROCEDURE getBookNameByPrice( 
		IN bprice INT, 
		OUT bname VARCHAR(100) 
	)
	
	BEGIN
		SELECT BOOK_NAME as bname from BOOKS where BOOK_PRICE <= bprice ;
	END $$
	
	-- Call the procedure in workbench
	call getBookNameByPrice(5000, @result);

	// JDBC App to get book price by name using Procedure
	package in.ashokit;
	
	import java.sql.CallableStatement;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.Types;
	import java.util.Scanner;
	
	public class ProcedureINOUTParamEx2 {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String PROCEDURE = "call getBookNameByPrice(?, ?)";
	
		public static void main(String[] args) throws Exception {
			Scanner s = new Scanner(System.in);
			System.out.print("Enter Book Price :: ");
			double bookPrice = s.nextDouble();
	
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
	
			CallableStatement cstmt = con.prepareCall(PROCEDURE);
			cstmt.setDouble(1, bookPrice);
			cstmt.registerOutParameter(2, Types.VARCHAR);
	
			ResultSet rs = cstmt.executeQuery();
	
			while (rs.next()) {
				System.out.println(rs.getString(1));
			}
			con.close();
		}
	}

	-- Function getBookPriceById
	delimiter ##
	create function getBookPriceById(bid int) returns int deterministic
	begin
			declare bprice int;
	        select book_price into bprice from books where book_id = bid;
	        return bprice;
	end ##
	
	-- Call the function in workbench
	select getBookPriceById(101) from dual;

	// JDBC App to retrieve records from db using Stored Function
	import java.sql.CallableStatement;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.Types;
	
	class Test {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
	
		public static void main(String[] args) throws Exception {
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);

			CallableStatement cs = con.prepareCall("{ ? = call getBookPriceById(?) }");

			cs.registerOutParameter(1, Types.INTEGER);
			cs.setInt(2, 101);
			ResultSet rs = cs.executeQuery();
			
			rs.next();
			System.out.println(rs.getInt(1));
			
			con.close();
		}
	}

JDBC Batch Operations

JDBC Batch Operation

JDBC Batch Operation

	// JDBC App using Batch Operation
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.Statement;
	
	public class BatchOpsEx {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
	
		public static void main(String[] args) throws Exception {
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
			Statement stmt = con.createStatement();
	
			stmt.addBatch("INSERT INTO BOOKS VALUES(106, 'AI', 2800.00)");
			stmt.addBatch("INSERT INTO BOOKS VALUES(107, 'ML', 3800.00)");
			stmt.addBatch("INSERT INTO BOOKS VALUES(108, 'DS', 4800.00)");
	
			int[] count = stmt.executeBatch();
			System.out.println("Records Effected ::" + count.length);
	
			con.close();
	
			System.out.println("Execution Completed...");
		}
	}

Assignment - 1 : Insert 3 records into table using Batch Operation with PreparedStatement.

Assignment - 2 : Read Employee & Emp Address Data from keyboard and insert into DB table.

		Emp Data: ID, Name & Salary
		Address Data : City, State, Country

	CREATE TABLE EMP (
		EMP_ID    INT,
		EMP_NAME VARCHAR(100),
		EMP_SALARY INT
	);
	
	CREATE TABLE EMP_ADDRESS (	
		CITY VARCHAR(50),
		STATE VARCHAR(50),
		COUNTRY VARCHAR(50),
		EMP_ID INT
	);

Transactions in JDBC

Note: When we are performing Non-Select Operations (insert / update / delete) with database then Transaction is mandatory.

	// This is default behaviour of Connection obj
	con.setAutoCommit(true);

	con.setAutoCommit(false);

Note: When Auto Commit is false, we need to commit the transaction programmatically to save our operation in database.

	// Transaction Management Code Snippet
	Connnection con = DriverManager.getConnection(url, uname,pwd);
	con.setAutoCommit(false);
	try{
		// logic to execute queries
		con.commit( );
	} catch(Exception e){
		con.rollback( );
	}

	// JDBC App using Transaction Management
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	
	public class EmpAddrInsert {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String EMP_INSERT = "INSERT INTO EMP VALUES(?,?,?)";
		private static final String EMP_ADDR_INSERT = "INSERT INTO ADDRESS VALUES(?,?,?,?)";
	
		public static void main(String[] args) throws Exception {
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
	
			// By Default conn - autoCommit mode is true
			con.setAutoCommit(false);
	
			try {
				PreparedStatement pstmt = con.prepareStatement(EMP_INSERT);
				pstmt.setInt(1, 101);
				pstmt.setString(2, "John");
				pstmt.setDouble(3, 1000.00);
	
				pstmt.executeUpdate();
	
				pstmt = con.prepareStatement(EMP_ADDR_INSERT);
				pstmt.setString(1, "Hyd");
				pstmt.setString(2, "TG");
				pstmt.setString(3, "India");
				pstmt.setInt(4, 101);
	
				pstmt.executeUpdate();
	
				con.commit();
	
				System.out.println("Records Inserted...");
			} catch (Exception e) {
				System.out.println("Transcation Rolled Back....");
				con.rollback();
			}
			con.close();
		}
	}

Requirement : Develop JDBC application to read EMP_ID from Keyboard and then retrieve emp data along with address based on given emp_id from Database table.

	// JDBC App to retrieve data using join
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	
	public class SelectEmpWithAddr {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
		private static final String EMP_SELECT = "SELECT * FROM EMP e, EMP_ADDRESS a WHERE e.emp_id = a.emp_id and e.emp_id = ?";
	
		public static void main(String[] args) throws Exception {
			Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);
	
			PreparedStatement pstmt = con.prepareStatement(EMP_SELECT);
			pstmt.setInt(1, 101);
	
			ResultSet rs = pstmt.executeQuery();
	
			while (rs.next()) {
				System.out.println(rs.getInt(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(5));
				System.out.println(rs.getString(6));
			}
			con.close();
		}
	}

Connection Pooling

Connection Pooling

How to setup Connection Pool

Steps to Setup Hikari Connection Pool 

		1) Create Java Project in IDE
		2) Add below jars to project build path
				a) Hikari-CP.jar
				b) SLF4J-api.jar
				c) mysql-connector.jar
		3) Create Java class to setup connection pool like below

	// JDBC App using Hikari Connection Pool
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.Statement;
	import com.zaxxer.hikari.HikariConfig;
	import com.zaxxer.hikari.HikariDataSource;
	
	public class ConnectionFactory {
		private static final String DB_URL = "jdbc:mysql://localhost:3306/advjdb";
		private static final String DB_UNAME = "ashokit";
		private static final String DB_PWD = "AshokIT@123";
	
		public static void main(String[] args) throws Exception {
			HikariConfig config = new HikariConfig();
	
			config.setJdbcUrl(DB_URL);
			config.setUsername(DB_UNAME);
			config.setPassword(DB_PWD);
	
			config.setMaximumPoolSize(20);
			config.setMinimumIdle(5);
	
			HikariDataSource datasource = new HikariDataSource(config);
	
			Connection con = datasource.getConnection();
	
			String sql = "INSERT INTO BOOKS VALUES (202, 'Django', 4500.00)";
	
			Statement stmt = con.createStatement();
	
			stmt.executeUpdate(sql);
	
			System.out.println("RECORD INSERTED.....");
			con.close();
		}
	}

Properties files in Java

Note: Every database will have different credentials so when we want to change the database then we have to change our java programs which is not a good practice.

	// Java Program using Properties
	import java.io.FileInputStream;
	import java.util.Properties;
	
	class Demo {
		public static void main(String[] args) throws Exception {
			FileInputStream fis = new FileInputStream("db.properties");

			Properties p = new Properties();
			p.load(fis);
			
			System.out.println(p.getProperty("db.url"));
			System.out.println(p.getProperty("db.uname"));
			System.out.println(p.getProperty("db.pwd"));
			
			fis.close();
		}
	}

Connection Factory

	// ConnectionFactory.java
	package in.ashokit;
	
	import java.io.File;
	import java.io.FileInputStream;
	import java.sql.Connection;
	import java.util.Properties;
	import javax.sql.DataSource;
	
	import com.zaxxer.hikari.HikariConfig;
	import com.zaxxer.hikari.HikariDataSource;
	
	public class ConnectionFactory {
		private static DataSource datasource = null;
	
		static {
			try {
				File f = new File("DB.properties");
				FileInputStream fis = new FileInputStream(f);
	
				Properties p = new Properties();
				p.load(fis);
	
				String url = p.getProperty("db.url");
				String uname = p.getProperty("db.uname");
				String pwd = p.getProperty("db.pwd");
				String poolSize = p.getProperty("db.poolSize");
	
				HikariConfig config = new HikariConfig();
	
				config.setJdbcUrl(url);
				config.setUsername(uname);
				config.setPassword(pwd);
				config.setMaximumPoolSize(Integer.parseInt(poolSize));
	
				datasource = new HikariDataSource(config);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	
		public static Connection getDBConnection() throws Exception {
			return datasource.getConnection();
		}
	}

	// BookStore.java
	package in.ashokit;
	
	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.Statement;
	
	public class BookStore {
		public static void main(String[] args) throws Exception {
			Connection con = ConnectionFactory.getDBConnection();
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from books");
	
			while (rs.next()) {
				System.out.println(rs.getInt(1) + "--" + rs.getString(2) + "--" + rs.getDouble(3));
			}
	
			rs.close();
			stmt.close();
			con.close();
		}
	}

Note: We need to close the resources in the reverse order in which they were opened. If we do not close the resources the pool manager will close them automatically.

Requirement: Develop a JDBC application to insert image into database.

	-- Create Person Table
	create table person (pid int, pimage blob(1000));

	// JDBC App to Insert image into database table
	package in.ashokit;
	
	import java.io.File;
	import java.io.FileInputStream;
	import java.sql.Connection;
	import java.sql.PreparedStatement;
	
	public class BookStore {
		public static void main(String[] args) throws Exception {
			File f = new File("file-path");
			FileInputStream fis = new FileInputStream(f);
	
			Connection con = ConnectionFactory.getDBConnection();
	
			String sql = "INSERT INTO PERSON VALUES (?, ?)";
	
			PreparedStatement pstmt = con.prepareStatement(sql);
	
			pstmt.setInt(1, 101);
			pstmt.setBlob(2, fis);
	
			int count = pstmt.executeUpdate();
	
			System.out.println("Rows Inserted :: " + count);
	
			pstmt.close();
			con.close();
		}
	}

Requirement: Develop a JDBC application to read Image from database and store it to a file.

	// JDBC App to read a image from database
	package in.ashokit;
	
	import java.io.FileOutputStream;
	import java.io.InputStream;
	import java.sql.Blob;
	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.Statement;
	
	public class BookStore {
		public static void main(String[] args) throws Exception {
			Connection con = ConnectionFactory.getDBConnection();
	
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM PERSON");
			
			if (rs.next()) {
				System.out.println(rs.getInt(1));
				byte[] stream = rs.getBytes(2);
	
				FileOutputStream fos = new FileOutputStream("path\\image.png");
				fos.write(stream);
				fos.close();
			}
			con.close();
		}
	}

Note: When we are reading image from database we can't print that image on the console. We need to store that image into a file using file output stream.

RowSet 

	// JDBC Program using RowSet
	import javax.sql.rowset.JdbcRowSet;
	import javax.sql.rowset.RowSetProvider;
	
	public class BookStore {
		public static void main(String[] args) throws Exception {
			JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
	
			rowSet.setUrl("jdbc:mysql://localhost:3306/advjdb");
			rowSet.setUsername("ashokit");
			rowSet.setPassword("AshokIT@123");
	
			rowSet.setCommand("select * from books")
			rowSet.execute();
	
			while (rowSet.next()) {
				System.out.print(rowSet.getInt(1) + "\t");
				System.out.print(rowSet.getString(2) + "\t");
				System.out.println(rowSet.getInt(3));
			}
			rowSet.close();
		}
	}

Types of JDBC Drivers

Note: Type-1, Type-2, Type-3 drivers are outdated, we are using Type-4 driver.

Debugging

What is Debugging ?

How to do Debugging ?

Drop to Frame

Servlet

Servlet

What is Web Application ?

Web Application Architecture

Web Application Architecture
Note: Multiple clients can send requests to server at a time.

Steps to develop Web Applications 

	1) Setup Server Software (Download & Install)
	2) Create Dynamic Web Project using IDE
	3) Setup Build Path for required libraries (servlet-api.jar)
	4) Create Servlet Classes to handle request & response
	5) Map servlet classes to URL Patterns
	6) Compile our source code (.class files will be created)
	7) Package .class files as war file (WAR means Web Archieve)
	8) Deploy war file in Server.
	9) Once application deployment completed then we can access our application in the browser using application URL.
			URL : http: // Server-Machine-IP : Server-Port / AppName / URL-Pattern

Note: Every Servlet should have unique URL pattern in the project.

	/login    ---> LoginServlet
	/register ---> RegisterServlet
	/dashboard ---> DashoboardServlet

Deployment : Keeping the war file in the server is called as Deployment.

Re-Deployment : Deploying latest war file into server is called Re-Deployment.

Un-Deployment : Removing war file from server is called as Un-Deployment.

Hot Deployment : Deploying only modified files in the server.

Note: In real time hot deployment will be used.

Apache Tomcat

Note: To run Tomcat Server in your system, you need to set path for JAVA_HOME (path upto java installation directory)

Note: If Oracle is installed in your system, then you can't use 8080 as port number for Tomcat.

Tomcat Setup in Windows

Tomcat Server Folder Structure

bin : It contains binary files to start & stop the server (startup.bat & shutdown.bat)

lib : It contains libraries (jars)

conf : It contains configuration files (server.xml & tomcat-users.xml)

webapps : This is called as Deployment folder (we need to keep war files here for deployment)

temp : It contains temporary files we can delete them

logs : It contains server execution log files

Note: We can change tomcat server default port in conf/server.xml file

HTTP Protocol

HTTP as Mediator

HTTP Request Format & HTTP Response Format

HTTP Request Format & HTTP Response Format
Note: Client can send data in request url as well as in the request body also but server can send data in response body only.

HTTP Methods

GET : To get the data from server

POST : To send data to server (insertion)

PUT : To update the data

DELETE : To delete the data

Note: In servlets we will use only GET & POST method.

HTTP Status Codes 

	1xx  --->  Information Codes (100 - 199)
	2xx  --->  Success Codes (200 - 299)
	3xx  --->  Redirection Codes (300 - 399)
	4xx  --->  Client Error Codes (400 - 499)
	5xx  --->  Server Error Codes (500 - 599)

Servlet API

Note: Container provide environment to run our web application.

There are 3 Definitions for Servlets

Servlet API

Steps to Develop Web Application using Servlet 

	1) Create Dynamic Web Project in IDE
	2) Add 'servlet-api.jar' file to project build path
	3) Create a Servlet class by extending HttpServlet class & write required methods to handle request & response.
	4) Configure servlet in web.xml (deployment descriptor) or use Annotation to map with url-pattern
	5) Run the web application using Server

	// First Servlet Application

	import java.io.IOException;
	import java.io.PrintWriter;

	import javax.servlet.annotation.WebServlet;
	import javax.servlet.http.HttpServlet;
	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;

	@WebServlet("/welcome")
	public class WelcomeServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			pw.append("<h1>Welcome to Servlets</h1>");
		}
	}

	// doGet method Syntax
	public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		// logic
	}

	// Servlet Application to Greet User

	import java.io.IOException;
	import java.io.PrintWriter;

	import javax.servlet.annotation.WebServlet;
	import javax.servlet.http.HttpServlet;
	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;

	@WebServlet("/greet")
	public class GreetServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			pw.append("<h1>Good Morning</h1>");
		}
	}

How Server will process each request ?

Accessing Web Application
Note: If requested resource is not available then server will prepare Error response & it will store it into Response object.

How Server Process Request

Note: To process client requests, server will use Threads. For every request one thread will be created.

Note : HTTP is a stateless protocol that means it will not remember anything about previous requests. It will treat every request as first request.

Note: When multiple users access our application at a time then burden will increase on the server. When burden increased then our server might crash. If server is crashed then our application will be down (nobody can access, it will effect our business).

Load Balancer Architecture

Requirement: Develop Web Application To display Greet Message to user based on current hour.

	6 AM - 11:55 AM  ===> Good Morning
	12:00 PM - 4:00 PM ===> Good Afternoon
	4:00 PM - 8:00 PM ===> Good Evening
	8:00 PM - 5:55 AM ===> Good Night

	import java.io.IOException;
	import java.io.PrintWriter;
	import java.time.LocalTime;

	import javax.servlet.annotation.WebServlet;
	import javax.servlet.http.HttpServlet;
	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;

	@WebServlet("/greet")
	public class GreetUser extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			LocalTime time = LocalTime.now();
			PrintWriter pw = resp.getWriter();

			if(time.isAfter(LocalTime.of(6, 0)) && time.isBefore(LocalTime.of(11, 55))) {
				pw.write("<h1>Good Morning !!</h1>");
			} else if(time.isAfter(LocalTime.of(12, 0)) && time.isBefore(LocalTime.of(16, 0))) {
				pw.write("<h1>Good Afternoon !!</h1>");
			} else if(time.isAfter(LocalTime.of(16, 0)) && time.isBefore(LocalTime.of(20, 0))) {
				pw.write("<h1>Good Evening !!</h1>");
			} else if(time.isAfter(LocalTime.of(20, 0)) || time.isBefore(LocalTime.of(5, 55))) {
				pw.write("<h1>Good Night !!</h1>");
			} else {
				pw.write("<h1>No Greetings !!</h1>");
			}
		}
	}

What is web.xml file ?

Mapping Servlet to URL Pattern using web.xml 

	<!-- URL-Pattern configuration in web.xml -->
	<web-app >
		<servlet>
			<servlet-name>greetservlet</servlet-name>
			<servlet-class>in.ashokit.GreetServlet</servlet-class>
		</servlet>

		<servlet-mapping>
			<servlet-name>greetservlet</servlet-name>
			<url-pattern>/greet</url-pattern>
		</servlet-mapping>
	</web-app>

What is Welcome File ? 

	<!-- Welcome page configuration in web.xml -->
	<web-app>
		<welcome-file-list>
			<welcome-file>index.html</welcome-file>
		</welcome-file-list>
	</web-app>

Note: We can configure multiple welcome file also.

	<!-- Multiple welcome pages configuration in web.xml -->
	<web-app>
		<welcome-file-list>
			<welcome-file>index.html</welcome-file>
			<welcome-file>index.jsp</welcome-file>
		</welcome-file-list>
	</web-app>

Note: If both files are not available then it will show error page with 404 status code (resource not found).

Accessing Servlet From HTML File 

	<!-- index.html -->
	<body>
		<h1>Welcome to My Application</h1>
		<a href="welcome"> Welcome Msg </a> <br />
		<a href="greet"> Greet Msg </a>
	</body>

	// WelcomeServlet.java
	@WebServlet("/welcome")
	public class WelcomeServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			pw.append("<h1>Welcome to Servlets</h1>");
		}
	}

	// GreetServlet.java
	@WebServlet("/greet")
	public class GreetServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			pw.append("<h1>Good Morning</h1>");
		}
	}

Note: First href attribute accessing WelcomeServlet using url-pattern ("/welcome") and second href attribute accessing GreetServlet using url-pattern ("/greet").

How to access Servlet from the Form

Requirement : Develop a web application to take username from the form and display welcome msg on the browser.

Accessing Servlet from the Form

	<!-- index.html -->
	<body>
		<form action="greet">
		    <h1>Welcome to Our Application</h1>
		    Enter name : <input type="text" name="name" />
		    <br />
		    <button type="submit">Submit</button>
		</form>
	</body>

	// GreetServlet.java
	@WebServlet("/greet")
	public class GreetUser extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			String name = req.getParameter("name");

			PrintWriter pw = resp.getWriter();
			pw.append(name+", Good Morning !!");
		}
	}

GET Method

POST Method 

	<!-- Insert Book Form -->
	<body>
		<form action="bookServlet" method="post">
			Book Id : <input type="number" name="bookId"> <br>
			Book Name : <input type="text" name="bookName"> <br>
			Book Price : <input type="number" name="bookPrice"> <br>
			<button type="submit">Submit</button>
		</form>
	</body>

	// Book Servlet to capture form data
	@WebServlet("/bookServlet")
	public class BookServlet extends HttpServlet {
		public void doPost(HttpServletRequest req, HttpServletResponse res) throws IOException{
			String bookId = req.getParameter("bookId");
			String bookName = req.getParameter("bookName");
			String bookPrice = req.getParameter("bookPrice");
			
			System.out.println(bookId);
			System.out.println(bookName);
			System.out.println(bookPrice);
			
			PrintWriter pw = res.getWriter();
			
			pw.write("Form Submitted");
		}
	}

Servlet Application with DAO Layer 

	HTML Page ===========> User interface (Presentation Logic)
	Servlet ======> To handle User Request & Response
	JDBC ====> To communicate with Database

Note: We can write JDBC logic inside Servlet class but it is not recommended.

DAO

Note: For every table one DAO class we have to create.

Components 

	index.html ====> Presentation Logic
	BookServlet.java ====> To handle request & response
	BookDAO.java  ====> Persistence Logic (CRUD)
	ConnectionFactory.java ====> To handle ConnectionPool

	// Book DAO class to insert book into DB
	public class BookDAO {
		private static final String INSERT_SQL = "insert into books values(?,?,?)";
		
		public boolean saveBook(BookDTO dto) throws Exception {
			// logic
			return count > 0;
		}
	}

Note: In the above project, Servlet class capturing the data from the form and Storing into BookDTO object and calling DAO class method by giving BookDTO object as parameter.

	// Book DTO class to transfer book data
	public class BookDTO {
	
		private int bookId;
		private String bookName;
		private double bookPrice;
		
		// setters & getters
	}

Request Dispatcher

Request Dispatcher 1

	// Servlet app to redirect client request
	
	// FirstServlet.java
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
			PrintWriter pw = resp.getWriter();
			pw.write("<h1>First Servlet Response</h1>");
			
			RequestDispatcher rd = req.getRequestDispatcher("second");
			//rd.forward(req, resp);
			rd.include(req, resp);
		}
	}
	
	// SecondServlet.java
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			pw.write("<h1>Second Servlet Response</h1>");
		}
	}

Usecase 

	LoginServlet.java ===> Responsible to verify User Login Credentials.
	MyCourseServlet.java ===> Responsible to display logged in user purchased courses.

Request Dispatcher 2

ServletConfig & ServletContext

Note: To use init-parameters we have to define url-pattern in web.xml

Note: Context parameter should be available outside of servlet tag.

ServletConfig & ServletContext

	<!-- ServletConfig & ServletContext using web.xml -->
	<context-param>
	  	<param-name>greet</param-name>
	  	<param-value>Welcome</param-value>
	  </context-param>
	
	  <servlet>
	  	<servlet-name>firstServlet</servlet-name>
	  	<servlet-class>in.ashokit.servlet.FirstServlet</servlet-class>
	  	<init-param>
	  		<param-name>msg1</param-name>
	  		<param-value>This is First Servlet</param-value>
	  	</init-param>
	  </servlet>
	
	  <servlet-mapping>
	  	<servlet-name>firstServlet</servlet-name>
	  	<url-pattern>/first</url-pattern>
	  </servlet-mapping>
	  
	  <servlet>
	  	<servlet-name>secondServlet</servlet-name>
	  	<servlet-class>in.ashokit.servlet.SecondServlet</servlet-class>
	  	<init-param>
	  		<param-name>msg2</param-name>
	  		<param-value>This is Second Servlet</param-value>
	  	</init-param>
	  </servlet>
	
	  <servlet-mapping>
	  	<servlet-name>secondServlet</servlet-name>
	  	<url-pattern>/second</url-pattern>
	  </servlet-mapping>

	//  Servlet app using ServletConfig & ServletContext
	
	// FirstServlet.java
	public class FirstServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			
			// getting context object
			ServletContext servletContext = getServletContext();
			String greet = servletContext.getInitParameter("greet");
			
			// getting config object
			ServletConfig servletConfig = getServletConfig();
			String msg = servletConfig.getInitParameter("msg1");
			
			pw.write(greet+", "+msg);
		}
	}
	
	// SecondServlet.java
	public class SecondServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
			PrintWriter pw = resp.getWriter();
			
			// getting context object
			ServletContext servletContext = getServletContext();
			String greet = servletContext.getInitParameter("greet");
			
			// getting config object
			ServletConfig servletConfig = getServletConfig();
			String msg = servletConfig.getInitParameter("msg2");
	
			pw.write(greet + ", " + msg);
		}
	}

Servlet Questions

Q-1) How many objects will be created for a servlet class when we send multiple requests ?

Q-2) How many ways are there to create our own servlet ?

Q-3) Servlet (I) vs GenericServlet (AC) Vs HttpServlet (C) ?

Servlet vs GenericServlet vs HttpServlet

Q-4) What is Servlet Life Cycle ?

Q-5) What is the difference between RequestDispatcher and sendRedirect ( ) method ?

RequestDispatcher vs sendRedirect()

	// Servlet app on RequestDispatcher vs sendRedirect()
	
	// FirstServlet.java
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
			PrintWriter pw = resp.getWriter();
			pw.write("<h1>First Servlet</h1>");
			
			// Request Dispatch
			RequestDispatcher rd = req.getRequestDispatcher("second");
			rd.forward(req, resp);
		}
	}
	
	// SecondServlet.java
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
			PrintWriter pw = resp.getWriter();
			pw.write("<h1>Second Servlet</h1>");
		}
	}
	
	// ThirdServlet.java
	@WebServlet("/third")
	public class ThirdServlet extends HttpServlet {
		public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
			System.out.println("Third Servlet");
			
			// Request Redirect
			resp.sendRedirect("https://www.google.com");
		}
	}

Q-6) What are scoped variables in Servlets ?

Note: Request Scope also called as local scope.

Q-7) What is the difference between request & session in web app ?

Q-8) What is request parameter ?

Request Parameters

JSP

What is JSP ?

Note: Servlet Container and JSP container will be part of Server.

Note: The main purpose of JSP is to separate presentation logic and business logic.

JSP Questions

Q-1) What is Scriptlet in JSP ?

Q-2) What is directive in JSP ?

Q-3) What are jsp implicit objects ?

Q-4) What is JSP lifecycle ?

Q-5) jsp include vs jsp forward ?

Q-6) What is JSTL ?

MVC

MVC Architecture

MCA Architecture 1

MVC Architecture 2

Note: DTO classes are used to transfer data from one layer to another layer.

Note: If we develop our application with above architecture then it is called as Layered Architecture Based Application.

Note: Tomorrow if we want to change DAO layer logic from JDBC to hibernate then we can change only DAO layer classes without touching web layer & UI layer components. This is called as Loosely Coupling.

Filters & Session Tracking

Filters

Filters

Note: By default, filters are executed based on their declaration order in the application. If manually configured filters execute in the order they are mapped in web.xml

Filter Use Cases 

	1) Request Logging (storing request receive time)
	2) Authentication
	3) Page Visitors Count
	4) Request Modification
	5) Response Modification

	// Servlet App to count no of req
	
	// First Servlet
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
			response.getWriter().write("I am from first servlet");
		}
	}
	
	// Second Servlet
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
			response.getWriter().write("I am from second servlet");
		}
	}
	
	// Filter to count req
	@WebFilter(urlPatterns = "/*")
	public class MyFilter extends HttpFilter implements Filter {	  
		private static Integer count = 0;
		
		public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
			count++;
			
			System.out.println("Filter Executed");
			
			response.getWriter().write("Request Count :: "+count);
			response.getWriter().write("\n");
			
			chain.doFilter(request, response);
		}
	}

Note: The above filter having the url pattern as /* that means this filter will execute for all the requests coming to our application

Note: chain.doFilter() is used for filter chaining that means it will call the next filter available in the application. If there is no filter available than it will forward the request to actual resource (servlet or jsp)

	// Request filtering based on time
	
	// First Servlet
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
			response.getWriter().write("I am from first servlet");
		}
	}
	
	// Second Servlet
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
			response.getWriter().write("I am from second servlet");
		}
	}
	
	// Request Filtering based on time
	@WebFilter(urlPatterns = "/*")
	public class MyFilter extends HttpFilter implements Filter {
		public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
			LocalTime time = LocalTime.now();
			int hourOfDay = time.getHour();
			
			if(hourOfDay >= 9 && hourOfDay < 17) {
				chain.doFilter(request, response);
			} else {
				response.getWriter().write("Working Hours Over !!");
			}
		}
	}

Note: The above filter contains the logic to process the request which comes between 9 AM to 5 PM.

Session Tracking

Session Tracking

Note: To implement all the above requirements, application should remember logged in user data.

Cookies 

	Cookie cookie = new Cookie("email", "ashok@gmail.com");
	resp.addCookie(cookie);

Cookies

	// Servlet App using Cookies
	
	// First Servlet
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			Cookie cookie = new Cookie("email", "ashok@gmail.com");
			resp.addCookie(cookie);
			
			resp.getWriter().write("First Servlet");
		}
	}
	
	// Second Servlet
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			Cookie[] cookies = req.getCookies();
			String  value = null;
			
			if(cookies != null) {
				value = cookies[0].getValue();
			} 
			
			if(value != null && !value.equals("")) {
				resp.getWriter().write("Second Servlet");
			} else {
				resp.getWriter().write("Invalid Request");
			}		
		}
	}

Hidden Form Fields 

	// Servlet App using Hidden Form Fields
	
	// First Servlet
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			resp.setContentType("text/html");
			
			PrintWriter pw = resp.getWriter();
			
			pw.write("<form action=\"second\" method=\"get\">");
			pw.write("<input type=\"hidden\" name=\"uname\" value=\"raja\"/>");
			pw.write("<button type=\"submit\">Submit</button>");
			pw.write("</form>");
		}
	}
	
	// Second Servlet
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			String uname = req.getParameter("uname");
			PrintWriter pw = resp.getWriter();
			
			if(uname != null && !uname.equals("")) {
				pw.write("I am from second servlet");
			} else {
				pw.write("Invalid Request");
			}
		}
	}

URL Re-Writing

Note: Data is visible in URL hence it is not recommended approach.

	// Servlet App using URL Re-Writing
	
	// First Servlet
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			resp.sendRedirect("http://localhost:8081/App/second?uname=raja");
		}
	}
	
	// Second Servlet
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			String uname = req.getParameter("uname");
			PrintWriter pw = resp.getWriter();
			
			if(uname != null && !uname.equals("")) {
				pw.write("I am from second servlet");
			} else {
				pw.write("Invalid Request");
			}
		}
	}

HttpSession

Note: For every user one session object will be created.

	// Servlet App using HttpSession
	
	// First Servlet
	@WebServlet("/first")
	public class FirstServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			// creating new session
			HttpSession session = req.getSession();
			session.setAttribute("email", "ashok@gmail.com");
			
			resp.getWriter().write("Session Created");
		}
	}
	
	// Second Servlet
	@WebServlet("/second")
	public class SecondServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			// return existing session
			HttpSession session = req.getSession(false);
			PrintWriter pw = resp.getWriter();
			String email = null;
			
			if(session != null) {
				email = (String) session.getAttribute("email");
			}
				
			if(email != null && !email.equals("")) {
				pw.write("I am from second servlet");
			} else {
				pw.write("Invalid Request");
			}
		}
	}
	
	// Logout Servlet
	@WebServlet("/logout")
	public class LogoutServlet extends HttpServlet {
		@Override
		protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
			// return existing session
			HttpSession session = req.getSession(false);
			PrintWriter pw = resp.getWriter();
			
			if(session != null) {
				// terminate existing session
				session.invalidate();
				pw.write("Logged Out");
			} else {
				pw.write("Invalid Request");
			}
		}
	}

Minor Requirements

Requirement 1

Requirement 1

Requirement 2

Requirement 2

Requirement 3

Requirement 3

Requirement 4

Requirement 4

Requirement 5

Requirement 5

Requirement 6

Requirement 6

Requirement 7

Requirement 7

Major Requirement

Login Page 

		1.1) Email text field
		1.2) Pwd text Filed
		1.3) Login Button
		1.4) Forgot Pwd Hyperlink
		1.5) Sign Up hyperlink

Registration Page 

		2.1) Fname text field
		2.2) Lname text field
		2.3) Email text field
		2.4) Pwd text field
		2.5) Gender radio button

Login & Registration Page

Forgot Password Page 

		3.1) Email

Reset Password Page 

		4.1) Email as lablel
		4.2) New Pwd
		4.3) Confirm Pwd

Forgot Password & Reset Password Page

Dashboard Page

Dashboard Page

Home

JDBC

Advanced Java

Prerequisites

What is Software Project ?

Types of Applications using Java

How to deliver project to client ?

Java Project Architecture

JAR Files

CLASSPATH

What is the difference between PATH & CLASSPATH ?

What is API ?

How to create documentation for our project ?

What is Build Path ?

Java De-Compiler

Database

JDBC

JDBC API Components

Steps to develop JDBC Program

Setup Database & Table in MySQL

First JDBC Application

Types Of Queries

Select Operation using JDBC

Assignment 1 : Registration & Login

ResultSet

ResultSetMetaData

Prepared Statement

Assignment 2 : Retrieve Books

Assignment 3 : Retrieve Employees

Assignment 4 : Salary Increment

Requirement

Callable Statement

JDBC Batch Operations

Transactions in JDBC

Connection Pooling

Properties files in Java

RowSet

Types of JDBC Drivers

Debugging

What is Debugging ?

How to do Debugging ?

Drop to Frame

Servlet

Servlet

What is Web Application ?

Web Application Architecture

Steps to develop Web Applications

Apache Tomcat

HTTP Protocol

Servlet API

Steps to Develop Web Application using Servlet

How Server will process each request ?

What is web.xml file ?

Accessing Servlet From HTML File

How to access Servlet from the Form

GET Method

POST Method

Servlet Application with DAO Layer

Request Dispatcher

ServletConfig & ServletContext

Servlet Questions

JSP

What is JSP ?

JSP Questions

MVC

MVC Architecture

Filters & Session Tracking

Filters

Session Tracking

Minor Requirements

Requirement 1

Requirement 2

Requirement 3

Requirement 4

Requirement 5

Requirement 6

Requirement 7

Major Requirement

Login Page

Registration Page

Forgot Password Page

Reset Password Page

Dashboard Page

≡