9.0 JDBC

JDBC - Java Database Connectivity

JDBC is a technology use to communicate between application and database.






















STEPS

1. Create an Application

2. You need a translator 
       Class.forName("oracle.jdbc.driver.OracleDriver");

ojdbc6.jar will require if i am using oracle 11g

3. Create the connection (Road)
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","system","password");  

4. Vechile is required
Statement stmt=con.createStatement();  

5. SQL query as per requirement and the the resultset is required where database engine will give you the result

ResultSet rs=stmt.executeQuery("select * from emp");  
while(rs.next())
{  
   System.out.println(rs.getInt(1)+" "+rs.getString(2));  
}  

6. Close the Road/Connection
con.close();  




practice

1. Translator = Driver

Class.forName("oracle.jdbc.driver.OracleDriver");

2. Road = Connection

Connection con=Driver.getConnection("jdbc:oracle:thin:@localhost:1521:xe","username","password");

3. Vechicle = Statement object

Statement smt=con.createStatement();

4. Requirement = sql

Resultset rs = smt.executeQuery("select * from emp");

5. Package = Resultset

while(rs.next())

{

System.out.println(rs.getInt(1)+ " " + rs.getString(2));

}

6. Close

con.cose();



Example
1. Create a table in mysql

import java.sql.*;

public class FirstProgram {
public static void main(String args[])
{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/bikesh?autoReconnect=true&useSSL=false";
String user="root";
String password="root";
try{
System.out.println("Database connection started....");
Class.forName(driver);
System.out.println("Driver Loaded");
Connection con=DriverManager.getConnection(url,user,password);
System.out.println("Connection Established");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
System.out.println("Query executed");
System.out.println("Below is the output ...");
while(rs.next())
{
System.out.println("Id = "+rs.getString(1));
System.out.println("Name = "+rs.getString(2));
System.out.println("Age = "+rs.getString(3));
}
con.close();
System.out.println("Database connection successfully closed");
}
catch(Exception e){
e.printStackTrace();
}
}

}

NOTE :

WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

I was getting above warning - to fix this used below url.

String url="jdbc:mysql://localhost:3306/bikesh?autoReconnect=true&useSSL=false";

OUTPUT

Database connection started....

Driver Loaded

Conenction Established

Query executed

Below is the output ...

Id = 1

Name = Bikesh

Age = 27

Database connection successfully closed

1. INSERT Example using PreparedStatement

import java.sql.*;

public class Insert {
public static void main(String args[])
{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/bikesh?useSSL=false";
String username="root";
String password="root";
Connection con;
PreparedStatement ps;
ResultSet rs;

try{
Class.forName(driver);
con=DriverManager.getConnection(url,username,password);
String sqlinsert="insert into emp values(?,?,?)";
ps=con.prepareStatement(sqlinsert);
ps.setInt(1, 3);
ps.setString(2, "Maurya");
ps.setInt(3, 29);
ps.executeUpdate();

String sql="select * from emp";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next())
{
System.out.println(
"Id = "+rs.getString(1)+" "+
"Name = "+rs.getString(2)+ " "+
"Age = "+rs.getString(3))
;
}
con.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}

OUTPUT

Id = 1 Name = Bikesh Age = 27

Id = 2 Name = Amites Age = 29

Id = 3 Name = Maurya Age = 29

2. Update Example


import java.sql.*;

public class Insert {
public static void main(String args[])
{
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/bikesh?useSSL=false";
String username="root";
String password="root";
Connection con;
PreparedStatement ps;
ResultSet rs;

try{
Class.forName(driver);
con=DriverManager.getConnection(url,username,password);

String sqlupdate="update emp set name=? where id=?";
ps=con.prepareStatement(sqlupdate);
ps.setString(1, "Amitesh");
ps.setInt(2, 2);
ps.executeUpdate();

String sql="select * from emp";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next())
{
System.out.println(
"Id = "+rs.getString(1)+" "+
"Name = "+rs.getString(2)+ " "+
"Age = "+rs.getString(3))
;
}
con.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}

Id = 1 Name = Bikesh Age = 27

Id = 2 Name = Amitesh Age = 29

Id = 3 Name = Maurya Age = 29

3. Delete example

table data before deletion

Id = 1 Name = Bikesh Age = 27

Id = 2 Name = Amitesh Age = 29

Id = 3 Name = Maurya Age = 29

import java.sql.*;

public class Insert {

public static void main(String args[])

{

String driver="com.mysql.jdbc.Driver";

String url="jdbc:mysql://localhost:3306/bikesh?useSSL=false";

String username="root";

String password="root";

Connection con;

PreparedStatement ps;

ResultSet rs;

try{

Class.forName(driver);

con=DriverManager.getConnection(url,username,password);

String sqlupdate="delete from emp where id=?";

ps=con.prepareStatement(sqlupdate);

ps.setInt(1, 3);

ps.executeUpdate();

String sql="select * from emp";

ps=con.prepareStatement(sql);

rs=ps.executeQuery();

while(rs.next())

{

System.out.println(

"Id = "+rs.getString(1)+" "+

"Name = "+rs.getString(2)+ " "+

"Age = "+rs.getString(3))

;

}

con.close();

}

catch(Exception e){

e.printStackTrace();

}

}

}

 

OUTPUT

Id = 1 Name = Bikesh Age = 27

Id = 2 Name = Amitesh Age = 29