💻Step 27:JDBC -24th +25th hour +code

💡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");

}

}

Interview Questions —

  • What is JDBC ?
  • What are steps to connect with database?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.