JDBC

 Jdbc( Java database connectivity)

✓It helps to achieve the connectivity between Java and database.

✓Hibernate also make use of jdbc to interact with database.

✓If we have a web application and if it has a database, then it needs to interact with database to read or modify the data.

✓ Jdbc helps to do this and in the world of Java, jdbc is the one and only API that helps to interact with rdbms(DB) applications.

✓ Jdbc is DB dependent i.e. using Jdbc we can interact with any rdbms applications exist in the world( like Oracle, mySQL,DB2, Sybase etc).

✓"java.sql.*" is the package representation of jdbc.

✓ Any class/interface belongs to this package means it's part of jdbc.

Below are the necessary steps to work with jdbc:

1. Load the driver

2. Get the DB connection via driver

3. Issue SQL queries via connection

4. Process the results returned by SQL Query

5. Close all jdbc connections

Note:

✓ All these steps are mandatory

✓ All these steps are interdependent

Driver:

✓ Drivers are external software component required by jdbc to interact with RDBMS application.

✓ Drivers are provided by DB vendor and they are DB dependent.

✓ Using MySQL driver we can only interact with mySQL RDBMS application and using DB2 driver we can only interact with DB2 RDBMS application.

✓ Drivers, as the name implies acts like a bridge between Java application and RDBMS application.

✓ DB vendor provides the driver class in the form of JAR file.

Steps to load the Driver class into the program:

There are two ways to load the Driver class.

1. By invoking "registerDriver()" method present in "java.sql.DriverManager" class by passing an instance of "Driver" class.

Syntax: public void DriverManager.registerDriver(java.sql.Driver driverref) throws sqlException

ex: java.sql.Driver ref = new com.mysql.jdbc.Driver();

DriverManager.registerDriver(ref);

2. Load the Driver class with the help of java's "class.forName()" .

ex: class.forName("com.mysql.jdbc.Driver").new instance();

Driver types:

There are four types of Drivers.

1. Type 1 : JDBC-ODBC Bridge

2. Type 2 : Native-API Driver

3. Type 3 : Network- protocol Driver

4. Type 4 : Native protocol Driver

DB URL:

✓ database uniform resource locator.

✓ As the name implies, it uniquely identifies database or rdbms application in the network.

✓ the structure of DB URL looks like below

<protocal> :<sub protocol>: <sub name>

✓ it is case in-sensitive.

1. Protocol:

     i. It is mandatory information and case insensitive.

    ii. In case of Java/J2EE protocol is always "jdbc".

2. Sub protocol:

   i. It is mandatory information and case insensitive.

  ii. This information is provided by DB vendor and we have to refer the driver manual to get this information.

iii. In case of MySQL, sub protocol is "MYSQL" but in case of Oracle or DB2 it is different.

3. Sub name:

    i. It's mandatory information.

    ii. It consists of,

  • Host Name ( computer name or IP address and case-insensitive)

• port number( should be in digits)- it uniquely identifies an application in an Operating System.

• database name/schema name

• username and password( case sensitive)

Jdbc URL examples:

oracle: 

 jdbc:oracle:thin:myuser/mypassword@myserver:1521:mydb

MySQL:

jdbc:mysql://myserver:3306/mydb?user=myuser& password=mypassword

DriverManager:

✓ DriverManager is a concrete class present in jdbc API and as the name implies, it manages the Driver.

✓ It helps Java program to connect with database and for that it requires below following information.

i. Driver class    ii. DB URL

✓ By invoking "registerDriver()" method on DriverManager we can provide an object of Driver class.

✓ By invoking "getConnection()" method on DriverManager we can provide DB URL.

✓ DriverManager as overloaded version of getConnection() method, they are

i. Connection getConnection (String dbUrl) throws SQLException

ex: String dbUrl="jdbc.mysql://localhost:3306/dbname?user=root& password=root";

Connection con= DriverManager.getConnection(dbUrl);

ii. Connection getConnection (String dbUrl, String user, String password) throws SQLException

ex: String dbUrl = "jdbc:mysql://localhost:3306/dbname?";

String user="root";

String password="root";

iii. Connection getConnection (String dbUrl, Properties props) throws SQLException

ex: String dbUrl = "jdbc:mysql://localhost:3306/dbname?";

String filePath="D:\\db.properties";

FileReader reader = new FileReader ("filePath");

Properties props = new Properties ();

props.load(reader);

Connection con=DriverManager.getConnection(dbUrl,props);

Results of RDBMS application:

✓ whenever we issue "select SQL queries" to database it returns DB results.

✓ whenever we issue "other than select SQL queries" to database then it returns "number of rows affected count" in the form of integer.

✓ Hence, based on the results we can group SQL queries into two groups.

   i. Select SQL query

   ii. other than select SQL query

1. Static SQL queries:

  Any SQL queries "without conditions"/" all conditions with hardcoded values" are called as "static SQL queries".

ex:

   • select * from table;

   • select * from table where x=1;

   • insert into table (Id, name) values (1,'vijay');

2. Dynamic SQL queries:

   Any SQL queries which must have conditions and one/more conditions value get decided at run time are known as "Dynamic SQL queries".

ex:

   • select * from table where x=? and y=?;