=============
Database
=============

=> Database is a software which is used to store the permanentley

	Ex: Oracle, MySQL,  SQLServer, PostGresSQL

=> To work with database we need to install Database software

=> Database Server software will store the data in the form of tables, rows and columns

=> Database Client software is used to communicate with Database Server Software

										
									SQL 
			DataBase client -----------------------------------> Database Server

Note: SQL (Structured Query Language) queries will be used to communicate Database Server


=> To communicate with Oracle DB server we can use "SQL Developer" as a client software

=> To communicate with MySQL DB server we can use "MySQL Workbench" as a client software

Note: SQL Developer  & MySQL Workbench softwares are used to execute SQL queries

======
JDBC
======

=> JDBC API released by Sun Microsystem

=> Using JDBC API we can communicate with Database software using Java Program

=> JDBC API will act as mediator between Java Program and Database software

								
								JDBC API
			Java Program ----------------------------------> Database Server


=> JDBC API contains set of interfaces & classes


=> Database Software vendors provided implementation for JDBC API. As part of JDBC API implementation they provided Database Driver

Note: Every Database software having its own Driver.

			
				Oracle Database ------------> OracleDriver

				MySQL Database ----------> MySQL Driver

				SQL Server ------------------> SQLDriver


Note: Driver is a program which knows how to connect with Database Software.

=> Database Driver software released as jar file

		
			MySQL Driver ===========> mysql-connector.jar

			Oracle Driver ==========> ojdbc14.jar


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

===========================================
Q)Explain about JDBC Programming Model  
===========================================

----------------------------- Requesting for connection  ----------------------------- 

=> Java program being a client should get connected to the database server.  

=> Java program makes use of DriverManager  OR DataSource  

=> By calling  getConnection() method on DriverManager   or DataSource, JDBC client requests for database connection.  

Connection connection=DriverManager.getConnection(...);  

Note:- Object Oriented representation of JDBC client’s session with the database server is nothing but Connection object.  

=> As long as this session is active(i.e. this connection is open) so long, Java program can perform CRUD operations with  the database.  

----------------------------- Creating the Statement object  ----------------------------------  

=> By calling createStatement() method on the connection object,Statement object is created.  
Statement statement=connection.createStatement();  

=> Statement object is the designatory object to submit an SQL statement from a Java program to the DBMS.  

--------------------------------- Submitting the SQL statement  -----------------------------------  

=> Statement object has 2 methods to submit an SQL statement to the DBMS.  

	1) executeUpdate(String dml)  

	2) executeQuery(String drl)  

=> executeUpdate() method is used to submit INSERT,UPDATE and DELETE SQL statements to the DBMS.  

=> executeQuery() method is used to sbmit SELECT statement to the DBMS.  

------------------------------closing the Statement  -------------------------  

=> By calling close() method on the Statement object, Statement is closed.  

		statement.close();  

=> To release JDBC resources of the JDBC client maintained by the database server, Statement should be closed in JDBC client.  

------------------------------ closing the Connection  --------------------------  

=> By calling close() method on the Connection object, connection is closed in the JDBC Client.  
connection.close();  

=> To release networking resources of the JDBC client maintained  by the database server, connection should be closed in JDBC client. 


==============================
Steps to develop JDBC Program
==============================

1) Load the Driver class (will be loaded automatically)

2) Get Connection from Database

3) Create Statement / PreparedStatement / Callable Statement

4) Execute Query

5) Process the Result

6) Close the Connection


====================================
Setup Database & Table in MySQL DB
===================================

### MySQL DB Installation Video : https://www.youtube.com/watch?v=EsAIXPIsyQg&t=1s


============= 1) Write  a Java program to connect to MySQL DBMS(Database Server) =========================

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

public class ConnectionApplication {

	private static final String DB_URL = "jdbc:mysql://localhost:3306";

	private static final String DB_UNAME = "root";

	private static final String DB_PWD = "ashokit";

	public static void main(String[] args) throws Exception {

		// Step-1 : Get DB Connection
		Connection con = DriverManager.getConnection(DB_URL, DB_UNAME, DB_PWD);		

		System.out.println("JDBC client connected to MySQL server"); 

		// Step-2 : Close Connection
		con.close();

		System.out.println("After performing database operations, connection is closed"); 

	}
}

=============================================================================================================
Q) Write a Java program to store an account's data(accno,name,balance) into the database.
==============================================================================================================

Note:- Before writing and executing this program, the following things should have been done in MySQL DBMS. 

Step 1:- Login to MySQL DBMS. 

Step 2:- create a database 

mysql> CREATE DATABASE hdfcdb; 

Step 3:- enter into the database. 

mysql> USE  hdfcdb; 

STEP 4:- create the table 

mysql> CREATE TABLE ACCOUNT(ACCNO INT(11),NAME VARCHAR(20),BALANCE DOUBLE(10,2)); 


//StoreAccount.java 
//StoreAccount.java 

import java.sql.*;//making JDBC API available 

class StoreAccount  { 

public static void main(String[] args) throws SQLException  { 

Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hdfcdb","root","ashokit"); 
Statement statement=connection.createStatement(); 
int re=statement.executeUpdate("INSERT INTO ACCOUNT VALUES(10001,'Rama',50000)"); 
System.out.println(re+" account stored into the database successfully"); 
statement.close(); 
connection.close(); 
} 
}

==============================================================================================================
Q)Write a Java program to increase the balance of all the accounts by Rs.1000. 
===============================================================================================================
//DepositApplication.java 
//StoreAccount.java 

import java.sql.*;//making JDBC API available 
class DepositApplication { 

public static void main(String[] args) throws SQLException { 
 Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hdfcdb","root","ashokit");

 Statement statement=connection.createStatement(); 
 
 int re=statement.executeUpdate("UPDATE ACCOUNT SET BALANCE=BALANCE+1000"); 
 
 System.out.println(re+" accounts updated"); 
 
 statement.close(); 
 
 connection.close(); 

} 
}
===============================================================================================================