What is DATABASE ? Type of DATABASE

What is DATA?

Data is a raw fact which describes the attributes of an entity.

For Example Human:- To describe a human we have to tell there appearance as well as there name, age, address, etc. which is called as data.

What is DATABASE?

    A database is an organized collection of structured information or data in a computer system. A database is maintained by a database management system. Both, the data and the database management system, along with the applications that are associated with them, are often shortened to the just database. A database is a Place/medium where we can store data in an organized and systematic manner.

Database

What is DBMS?

• DBMS is Database Management System.
• It is software which is used to maintain and manage the database.
• DBMS Function is Security and Authorization.

Type of DATABASE

There are many different types of databases. The best database for a specific organization depends on how the organization intends to use the data.

1. Relational databases    
2. Object-oriented databases
3. Distributed databases
4. Data warehouses
5. NoSQL databases
6. Graph databases
7. OLTP database
8. Open-source databases  
9. Cloud databases       
10.  Multimodel database
11. Document OR JSON database

What is SQL?

    SQL stands for Structured Query Language. It is a programming language which is used to communicate with RDBMS. It is used to perform tasks such as updating, Deletion, Insertion, and Retrieval of data from the database.

 JDBC Transaction

JDBC Transaction

• Exchange data or information between two media is known as Transaction.

•  By default, the AutoCommit mode is set to boolean true value because of which all the data are automatically saved into the Database Server whenever we perform any   Database operation.

• We can explicitly disable AutoCommit mode by using setAutoCommit() and passing a boolean false argument.

Syntex:-

        +void setAutoCommit()

  eg:-

        con.setAutoCommit(false);

Note:-

•  We have to disable the AutoCommit mode before we begin the transaction but after establishing a connection with Database Server.

• Once the AutoCommit mode is disabled we have to explicitly save the data into the Database Server by using commit() at the end of the transaction.

Syntex:-

        +void commit()

  eg:-

        `con.commit();

•  If anyone of the database operation fails, then the rollBack() operation is called which is used to rollBack Entire executed Database Operation and transaction starts from the beginning.   

Syntex:-

          +void rollBack()

   eg:-

           con.rollBack();

Definition for JDBC Transaction:

• JDBC Transaction is considered to be a Single Business Unit which may have multiple SQL Statements which must be executed.

Need for JDBC Transaction:-

•  JDBC Transaction is needed to maintain Data Consistency in the Database Server.

Advantage of JDBC:-

•  It is used to achieve ACID Properties or rules where A refers to Atomicity, C refers to Consistency, I refers Isolation, D for Durability

Atomicity: Atomicity means Do Everything or Do Nothing.

•  Do Everything refers to a Complete or Successful transaction where if all the Database operations are successfully executed, then the data are saved into the Database   Server leading to Data Consistency.

•  Do Nothing refers to an Unsuccessful or Uncomplete transaction where if any of the Database operations fails, then the rollBack operation is called which is used to rollback the Entire executed Database operation, and the transaction starts from the beginning without saving any data into the Database Server due to data inconsistency.

SavePoint:-

Syntex:-

         java.sql.Savepoint sp=con.setSavePoint();

setSavePoint():-

•  setSavePoint() is a factory or helper method which is used to create and return implementation object of Savepoint interface.

• Hence the return type of setSavepoint() is the Savepoint interface.

"java.sql.PreparedStatment" and Batch in JDBC

 java.sql.PreparedStatment:-

• It is an interface which is a part of JDBC API, and its implementation are provided by respective DB server or vendors as a part of JDBC Driver.

•  PreparedStatment interface is a sub-interface of Statement interface.

•  It supports the concept of PlaceHolder.

• It supports the concept of Execution Plan( Compile once execute many times ).

•  In case of PreparedStatement, the query is passed at the time of implementation object creation of PreparedStatement interface.

•   Hence, PreparedStatement are also known as Pre-compiled Statement.

 Syntax:-

         java.sql.PreparedStatement pstmt = con.prepareStatement("Query");

• prepareStatement() is a factory or helper method which creates and return the implementation object of PreparedStatement interface.

•  Hence, the return type for prepareStatement() is PreparedStatement interface.

Note[JOB]:-

• Since Compilation takes place each time along with Execution whenever we execute query using Statement Interface, Performance of an application decreases.

•    Hence, it is a good practice to make use of PreparedStatement interface to deal with multiple records.

  Statement interface:-

    Statement stmt = con.createStatement(); 

    stmt.executeUpdate("Qry"); // Complication and Execution 

    stmt.executeUpdate("Qry"); 

 PreparedStatement interface:-

   PreparedStatement pstmt=con.prepareStatement("Qry"); // pass the qry while creating a platform

   pstmt.executeUpdate();

   pstmt.execcuteUpdate();

Batch Update:-

Batch:- it is a collection of only DML Query.

Need of Batch:- Batch Update is needed to make one single data base call and affect multiple records of multiple tables at once.

Advantage:- Batch Update greatly improves the performance of an application.

Batch Update is applicable only for DML Queries.

• In case of Batch Update after adding all the DML Queries into the Batch, the entire Batch will execute only once.

 There are 2 methods w.r.t Batch.

   a) addBatch()      b) executeBatch()

a) addBatch():-

•  This is used to add all the DML Queries into the Batch.

b) executeBatch():-

•  This method is used to execute all the DML Queries present inside the Batch only once by making one single DB call.

• It returns an integer Array.

•  The size of the integer Array represents the total no of DML Queries added into the Batch.

Note:-

• Whenever we use a Batch with Statement interface, then one single batch can contain all types of DML Queries in it.

• Whenever we use Batch with PreparedStatement interface, then one single batch can contain only one DML Query in it.

•   Hence, Batch with Statement interface is faster in Performance.

 Fetching the data from Database Server in JDBC

• Whenever we execute DQL Query, we get a result which is referred to as Resulatant or Processed Data.
•  The Processed or Resultant Data can be fetched by ResultSet Interface which is a part of JDBC API.
•   java.sql.ResultSet
• It is an interface which is a part of JDBC API and the implementations are provided by respective Database Server or vendors as a part of JDBC Driver.
•  A set of methods of ResultSet interface are used to fetch the Processes or Resultant data which are known as getXXX().
• There are two different methods which are overloaded methods present in a ResultSet Interface:-

   +XXX getXXX(int coloumnnumber/coloumnindex )  // ( coloumn number ---- coloumn index )

 

   +XXX getXXX(String coloumnname/coloumnlabel ) // ( coloumn name ----- coloumn label )

 If datatype is integer:-

  

  a) +int getInt(int coloumnnumber)

 

  b) +int getInt(String coloumnname)

 If datatype is String:-

  a) +String getString(int coloumnnumber)

 

  b) +String getString(String coloumnname)

 If datatype is Double:-

  a) +double getDouble(int coloumnnumber)

 

  b) +double getDouble(String coloumnname)

•  The return type of getXXX() method is the respective data-type.

• The return type of setXXX() method is void.

 // hasNext() --- next()  // Collection

 // next() --- getXXX()  // JDBC

 next():-

•  It is used to check whether the next record is present or not and returns a boolean value called true or false but not the record.

   + boolean next()

executeQuery():-

1.  It is a specialized method which is used to execute only DQL Queries.

2.  The outcome of a DQL is Processed or Resultant Data which can be fetched by ResultSet Interface which is a part of JDBC API.

3.  Hence, the Return type for executeQuery() is ResultSet interface.

4.  whenever we try to execute a DML query using this method it throws an exception called SQLException.

  Syntax:-

          +ResultSet executeQuery("Only DQL")

 

Implementation Object:-

  

        ResultSet rs = stmt.executeQuery(" DQL "); 

insert into thane.student values(1,'Oats',45.3); // Hard-coding

Oats -- Wheat

PlaceHolder:-

• It is a parameter which holds Dynamic values at the run time by the User.

•  In case of JDBC, place holder is represented by as " ? ".

Declaration of PlaceHolder in the Query:-

String inQry=" insert into thane.student values(?,?,?) ; // 1 - id  2 - name  3 - perc

String inQry1=" insert into thane.student values(?,?,?) ;

String upQry=" update thane.student set Product_Name = ? where id = ? ; //2 placeholders -- 2 times

String delQry=" delete from thane.student where id = ? ;

String selQry=" select * from thane.student where id = ? ;

Rules to set the data for a PlaceHolder:-

There are 3 different rules to set the data for PlaceHolder namely:-

1.  We have to set the data for a Placeholder before the execution.

2. The number of data must exactly match with the number of Placeholder.

3. We have to set the data for a Placeholder by using setXXX().

  +void setXXX(int placeholdernumber/placeholderindex, XXX data)

Ex:-

      sid=sc.nextInt();

     

        setInt(1, 15); // setInt(1, sid);

        setString(2, "Steve"); // setString(2, sc.next());

        setDouble(3, 34.5); // setDouble(3, sc.nextDouble());

 

Why is JDBC Driver is an implementation of JDBC API ?

Since all the Driver Classes must mandatorily implement java.sql.Interfaces which is a part of JDBC API, Hence JDBC Driver is an implementation of JDBC API.

Definition of JDBC:- Java Database Connectivity [JDBC] is a specification given in the form of abstraction API to achieve loose coupling between Java Application and DB Server.

Steps of JDBC

There are 6 different steps present in JDBC:-

1. Load and register the Driver[Driver refers as Driver Class].
2. Establish the connection with a DB server.
3. Create a platform or Statement.
4. Execute the SQL query or SQL statements.
5. Process the Resultant Data[Optional].
6. Close all the connections ( Costly Resources ).

Specification of JDBC

There are 3 different specifications present for JDBC namely:-

1. All the Driver Class must contain one Static Block in it.
2. All the Driver Class must mandatory implements java.sql.interfaces which is a part of JDBC API.
3. All the Driver Class must mandatorily be registered with DriverManager by using a static method called registerDriver() Method.

Ex:-

 Mysql (DataBase) :-

  import java.sql.*;

   + class Driver implements java.sql.interfaces

     {

        static

        {

  Driver d = new Driver();

           DriverManager.registerDriver( d ) ;

        }   

     }

  

1) Load and register the Driver[ Driver refers Driver classes ]:-

• In these steps, we have to load and register the Driver classes which are a part of JDBC Driver and which are provided by the database vendors. The Driver classes loaded and registered in 2 different ways namely:-

1.   Manually        
2.  by using forName()

1)Manually:-

creating an object of Driver class and registered it with DriverManager by using static method called registeDriver() Method.

  

My-sql(DB Server):-

   Driver d=new Driver();

   DriverManager.registerDriver(d);

Oracle(DB Server):-

   OracleDriver od=new OracleDriver();

   DriverManager.registerDriver( od );

2)2nd way:-

By using static method called forName() Method, we can load and register the Driver classes which is a part of JDBC Driver.

 Syntax:-

lang package:- java.lang.*;

Mysql:-  Class.forName("com.mysql.jdbc.Driver"); 

             [ checked Exception(ClassNotFoundException) ]

Oracle:- Class.forName("oracle.jdbc.driver.OracleDriver"); 

• It will load and register the Respective Driver Classes..  

2) Establish the connection with a DB Server:- 

• In this step, We have to establish the connection between the java application and the DB Server by using " getConnection() " Method. 
• Factory/ Helper Method:- It is used to create an implementation object.
• getConnection():- It is  Factory/ Helper Method which is used to create and return the implementation object of " Connection " interface based on URL, Hence the                          Return type will be Connection interface, And it is static in nature.

There are 3 different overloaded variants(forms) of getConnection() Method which are as follows:-

1.    getConnection(" URL")
2.   getConnection(" URL " , Properties info)
3.    getConnection(" URL " , " User ", " Password ") ;

• getConnection()- present in a HelperClass called " DriverManager ".
• Whenever we use getConnection() Method, it throws a checked Exception called SQL Exception.

java.sql.Connection:- It is an interface which is a part of JDBC API and the implementation are provided by the respective DB vendors as a part of JDBC Driver.

                    

java.sql.DriverManager:- It is a Helper class which is a part of JDBC API and which contains 2 important static methods in it namely:-

a)registerDriver()        

b)getConnection()

3) Create a Platform or Statement:-

• we need to create a Platform or Statement in order to execute the SQL Queries or SQL Statements.
• A Statement or Platform can be created either by using " Statement interface " or " PreparedStatement interface " or " CallableStatement interface ".

java.sql.Statement:- It is an interface that is a part of JDBC API and the implementations are provided by respective DB Vendors or Servers as a part of JDBC Driver.

Statement:- 

• if I want to create the platform by using " Statement " interface, have to use " createStatement() " Method. 
• " createStatement() " present in a Connection interface, It is a factory or helper method which is used to create and return the implementation object of " Statement " interface, Hence the return type for createStatement() is Statement interface.

4) Execute the SQL Query or SQL Statements:-

• In order to execute the SQL queries or SQL statements,  we have 3 different methods present namely:-

  a) execute() 

  b) executeUpdate()

c) executeQuery()    [ DQL{ SELECT } , DML{ INSERT, UPDATE , DELETE} etc]

• All these methods are present in Statement interface.

a) execute():-

• execute Method is a Generic Method, Since it is used for any type of SQL Queries.
• Hence, the return type is boolean.
• By default, execute() returns a boolean True in case of DQL and boolean False in case of DML.

b) executeUpdate():-

• executeUpdate() is a Specialized Method since it is used to execute Only DML Queries or DML Statement.
• The outcome of DML is 0-n integer value which gives the total no. of records affected in the DB server.
• Hence the return type is integer.
• whenever we try to execute DQL using this method, it throws SQL Exception.

5) Process the Resultant Data[Optional].

6) Close the connection:-

• We close the connection in a finally block, by using the if condition to avoid null pointer Exception.
• All the interfaces in JDBC API, we have to close it...!!!!
• Each Query is making one DB call.
• The more no of DB calls, will decrease the performance of an application.
• Each DB call is considered a costly resources.

What is Host and Url in JDBC J2EE

What is Host 

Host is a platform on which all the applications can be executed.

Type of Host

There are two different types of host-present namely:-

  a)Local Host     b)Remote(Global) Host

a) Local Host:- In case of localhost, the application is limited to only a particular system.

                     Example: Standalone Application(Desktop Application-like file manager, Calculator ).

b) Remote Host:- In case of a remote host, the application is not restricted or limited to any system.

                    Example:- Any Real-time Application ( Youtube etc).

What is URL?

URL (Uniform Resource Locator)

It is the path or address using which, we can access the resources uniquely over the network.

Constraints of Url are:- Protocol, Host+PortNo, ResourceName.

• In case of URL, Data refers to key and Value pair provided by the user. ( Servlet )
• The protocol for the Web Applications:- HTTP(Hypertext Transfer Protocol) / HTTPS(Hypertext Transfer Protocol Secure)
• The protocol of JDBC is JDBC:sub-protocol(Respective DB Server)

Syntax:- mainprotocol:sub-protocol://Host:PortNo/DB_NAME(Optional)

MySql(DB SERVER):

Local Host:-

                       Host Information              User Information 

            

                     jdbc:mysql://localhost:3306?user=root&password=root 

Remote Host:-

                     jdbc:mysql://192.168.10.16:3306?user=root&password=root

Oracle(DB SERVER):-

Local Host:-

            jdbc:oracle://localhost:1521?user=scott&password=tiger

 

Remote Host:-

                    jdbc:oracle://192.168.10.16:1521?user=scott&password=tiger

Implementation Classes == Driver Classes !!!!!

FQCN(Fully Qualified Class Name)

•      Package Name.Class Name

Standard Way of creating a package structure:-

• com/org.company_name.Application_name.

Different Driver Class provided by Respective db server or vendor:-

DB server                 FQCN

---------       --------------------------------------------

 Mysql       :-     com.mysql.jdbc.Driver

 Oracle      :-     oracle.jdbc.driver.OracleDriver

 Ms-sql      :-     com.microsoft.sqlserver.jdbc.SqlServerDriver

 Derby       :-     org.apache.derby.jdbc.EmbeddedDriver

a) All the Driver classes which is a part of JDBC Driver are provided by the respective DB servers or vendors in the form of jar file.

b) All the Driver classes must mandatory implements java.sql.interfaces which is a part of jdbc API.

• What is language and programming language? 
• What is JAVA and where is used ?
• What Is API (Application Programming Interface) In Java