JDBC Definition- Jdbc is concept where java program connect with database.
JDBC API-

1-java.sql
2-javax.sql

jdbc-architecture

JDBC driver is s/w component that enables java application to interact with database.
There are 4 types of JDBC drivers.

[a]JDBC-ODBC bridge driver
 [b]Native-API driver (partially java driver)

[c]Network Protocol driver (fully java driver)

[d]Thin driver (fully java driver)[*widely used]

jdbc-types-of-drivers-available

-:[5 STEPS TO CONNECT TO DATABASE]:-

[a] Register the driver class

[b] Creating connection

[c] Creating statement

[d] Executing queries

[e] Closing connection

steps-to-connect-to-database

1- Register the Driver

Class.forName() is used to load the driver class explicitly.

Example for MySql –


Class.forName("com.mysql.jdbc.Driver");

2-Create a Connection

getConnection() method of DriverManager class is used to create a connection.

Syntax

getConnection(String url)
getConnection(String url, String username, String password)
getConnection(String url, Properties info)

Example for MySql-

String url ="jdbc:mysql://localhost/test";
here "test" is a name of database.
String username="root";

here “root” is a username of mysql.

String password="root";
here "root" is a password of mysql.
Connection con = DriverManager.getConnection(url, username, password);

3-Create SQL Statement

createStatement() or prepareStatement() method is invoked on current Connection object to create a Statement. Types of statements:- a-Statement b-PreparedStatement c-CallableStatement(Used for procedures and functions) jdbcstatement Example for MySql- a-Statement

Statement s=con.createStatement();

or b-PreparedStatement

PreparedStatement ps=con.prepareStatement(String sql);

4- Execute SQL Statement

Methods of SQL statement execution a-execute b-executeUpdate c-execute

executequeryvsexecuteupdatevsexecute

5-Closing the connection

The close()method of Connection interface is used to close the connection. Example of closing a connection

con.close();

code samples are here:

PreStep 1- install mysql database from here
MySql official database= click here

PreStep 2- Include mysql jar file in java project-

Download link= click here

1- Table creation and Data insertion

database =test

mysql username=root

mysql password=root

table=user


package jd;

import java.sql.*;

public class ExInsertStatement {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");

Statement st = con.createStatement();
 st.execute("CREATE TABLE IF NOT EXISTS user(name VARCHAR(20), age INT(10))");

PreparedStatement pst = con.prepareStatement("insert into user values (?,?)");
 pst.setString(1, "java");
 pst.setInt(2, 10);

pst.executeUpdate();

System.out.println("done");
 }

}

2- Data Updation

package jd;

import java.sql.*;

public class ExUpdateStatement {

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");

 PreparedStatement pst = con.prepareStatement("update user set name=? where age=?");
 pst.setString(1, "core");
 pst.setInt(2, 10);

 pst.executeUpdate();

 System.out.println("done");
 }

}

3-Data Fetching/Retrieve

package jd;

import java.sql.*;

public class ExSelectStatement {

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");

 PreparedStatement pst = con.prepareStatement("select * from user");
 ResultSet rs = pst.executeQuery();

 while (rs.next()) {
 System.out.println(rs.getString("name"));
 System.out.println(rs.getInt("age"));
 }
 }

}

4- Data deletion

package jd;

import java.sql.*;

public class ExDeleteStatement {

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");

 PreparedStatement pst = con.prepareStatement("delete from user");
 pst.executeUpdate();

 System.out.println("done");

 }

}
Advertisements