1. How to load JDBC driver.
Govardhan import java.lang.*;
class test
{
public static void main(String ar[])
{
try{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
System.out.println("driver loaded...");
}
catch(ClassNotFoundException ce)
{
System.out.println("Driver not found...");
}
}
}
/*
How to run in cmd
1. set classpath=.;your mysql jar file's path
exp : set classpath=.;C:\Users\student\Desktop\pacific_dsa\jdbc\com.mysql.jdbc_5.1.5.jar
2. complie java file
exp : javac load.java
3. run class file
exp : java test
*/
2. Insert record data into emp table using Statement in JDBC.
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
int id,sal;
String name;
Scanner sc = new Scanner(System.in);
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
System.out.print("Enter Id : ");
id = sc.nextInt();
System.out.print("Enter Name : ");
name = sc.next();
System.out.print("Enter sal : ");
sal = sc.nextInt();
Connection con = DriverManager.getConnection(url,user,pass);
Statement st = con.createStatement();
//String query = "insert into emp values(101,'Ajay',10000)";
String query = "insert into emp values("+id+",'"+name+"',"+sal+")";
int i = st.executeUpdate(query);
if(i==1)
{
System.out.println("1 Record inserted...");
}
else
{
System.out.println("Error....");
}
}
}
3. Insert data into emp table using Prepared Statement in JDBC.
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
int id,sal;
String name;
Scanner sc = new Scanner(System.in);
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
System.out.print("Enter Id : ");
id = sc.nextInt();
System.out.print("Enter Name : ");
name = sc.next();
System.out.print("Enter sal : ");
sal = sc.nextInt();
Connection con = DriverManager.getConnection(url,user,pass);
String query = "insert into emp values(?,?,?)";
PreparedStatement pst = con.prepareStatement(query);
pst.setInt(1,id);
pst.setString(2,name);
pst.setInt(3,sal);
int i = pst.executeUpdate();
if(i==1)
{
System.out.println("1 Record inserted...");
}
else
{
System.out.println("Error....");
}
}
}
4. Delete record from emp table using Prepared Statement in JDBC.
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
int id,sal;
String name;
Scanner sc = new Scanner(System.in);
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
System.out.print("Enter Id : ");
id = sc.nextInt();
Connection con = DriverManager.getConnection(url,user,pass);
String query = "delete from emp where e_id = ?";
PreparedStatement pst = con.prepareStatement(query);
pst.setInt(1,id);
int i = pst.executeUpdate();
if(i>0)
{
System.out.println(i+" Records Deleted...");
}
else
{
System.out.println("Error...");
}
}
}
5. Update Record of emp table using Prepared Statement in JDBC.
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
int id,sal;
String name;
Scanner sc = new Scanner(System.in);
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
System.out.print("Enter id you want to update : ");
id = sc.nextInt();
System.out.print("Enter New Name : ");
name = sc.next();
System.out.print("Enter New sal : ");
sal = sc.nextInt();
Connection con = DriverManager.getConnection(url,user,pass);
String query = "update emp set e_name = ?,e_sal = ? where e_id = ?";
PreparedStatement pst = con.prepareStatement(query);
pst.setString(1,name);
pst.setInt(2,sal);
pst.setInt(3,id);
int i = pst.executeUpdate();
if(i>0)
{
System.out.println(i+" Records Updated...");
}
else
{
System.out.println("Error...");
}
}
}
6. Display all the data of emp table using Prepared Statement in JDBC.
import java.sql.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
String query = "select * from emp";
PreparedStatement pst = con.prepareStatement(query);
//pst.setString(1,"emp");
ResultSet rs = pst.executeQuery();
while(rs.next())
{
String data1 = rs.getString(1);
String data2 = rs.getString(2);
String data3 = rs.getString(3);
System.out.println(data1+"\t"+data2+"\t"+data3);
}
}
}
7. Display Particulat row data from table using Prepared Statement in JDBC.
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
Scanner sc = new Scanner(System.in);
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
String query = "select * from emp";
PreparedStatement pst = con.prepareStatement(query);
//pst.setString(1,"emp");
ResultSet rs = pst.executeQuery();
//gives 3rows data from the table (from ResultSet)
rs.absolute(3);
String data1 = rs.getString(1);
String data2 = rs.getString(2);
String data3 = rs.getString(3);
System.out.println(data1+"\t"+data2+"\t"+data3);
}
}
8. Display ResultSet's meta data using ReusltSetMetaData class.
import java.sql.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
Scanner sc = new Scanner(System.in);
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
//String driver = "com.mysql.cj.jdbc.Driver"; //8.0.23
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/employee";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
String query = "select * from emp";
PreparedStatement pst = con.prepareStatement(query);
//pst.setString(1,"emp");
ResultSet rs = pst.executeQuery();
ResultSetMetaData rsm = rs.getMetaData();
int total = rsm.getColumnCount();
System.out.println("\nTotal Column Count : "+total);
String cl_name = rsm.getColumnName(2);
System.out.println("2nd Column Name : "+cl_name);
String cl_type = rsm.getColumnTypeName(1);
System.out.println("1nd Column Type Name : "+cl_type);
String table = rsm.getTableName(1);
System.out.println("Table Name : "+table);
}
}
9. ResultSetMetaData : display number of columns with column names and size
import java.sql.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/gisuratc_govardhan";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
String query = "select * from batch";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
ResultSetMetaData rsmt = rs.getMetaData();
System.out.println(rsmt.getColumnCount());
for(int i=1;i<=rsmt.getColumnCount();i++)
{
System.out.println(rsmt.getColumnName(i)+"\t"+rsmt.getColumnTypeName(i)+"\t"+rsmt.getColumnDisplaySize(i));
}
}
}
10. CallableStatement : call procedure (without args)
import java.sql.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
Class.forName(driver);
//create connection
String url = "jdbc:mysql://localhost:3306/temp1";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
//write query
String query = "call disp1()";
//create CallableStatement
CallableStatement cs = con.prepareCall(query);
ResultSet rs = cs.executeQuery();
if(rs.next()==true)
{
System.out.println(rs.getString(1));
}
else
{
System.out.println("no data");
}
}
}
11. CallableStatement : procedure with arguments
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
Class.forName(driver);
//create connection
String url = "jdbc:mysql://localhost:3306/temp1";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
Scanner sc = new Scanner(System.in);
System.out.println("Enter two numbers : ");
int a = sc.nextInt();
int b = sc.nextInt();
//write query
//String query = "call addition(10,20)";
String query = "call addition(?,?)";
//create CallableStatement
CallableStatement cs = con.prepareCall(query);
cs.setInt(1,a);
cs.setInt(2,b);
ResultSet rs = cs.executeQuery();
if(rs.next()==true)
{
System.out.println(rs.getString(1));
}
else
{
System.out.println("no data");
}
}
}
/*
Mysql code
create database temp1;
use temp1;
delimiter //
create procedure addition(a int,b int)
begin
select a+b;
end//
delimiter ;
delimiter //
*/
12. CallableStatement : procedure with IN arguments : swap numbers
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
Class.forName(driver);
//create connection
String url = "jdbc:mysql://localhost:3306/temp1";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
Scanner sc = new Scanner(System.in);
System.out.println("Enter two numbers : ");
int a = sc.nextInt();
int b = sc.nextInt();
System.out.println("before swapping :");
System.out.println("A : "+a);
System.out.println("B : "+b);
//write query
String query = "call swapnums(?,?)";
//create CallableStatement
CallableStatement cs = con.prepareCall(query);
cs.setInt(1,a);
cs.setInt(2,b);
ResultSet rs = cs.executeQuery();
System.out.println("after swapping :");
System.out.println("A : "+a);
System.out.println("B : "+b);
}
}
/*
Mysql code
create database temp1;
use temp1;
delimiter //
create procedure swapnums(in a int,in b int)
begin
declare c int;
set c = a;
set a = b;
set b = c;
end//
delimiter ;
set @x=100;
set @y=200;
call swapnums(@x,@y);
select @x;
select @y;
*/
13. CallableStatement : procedure with INOUT arguments : swap numbers
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
Class.forName(driver);
//create connection
String url = "jdbc:mysql://localhost:3306/temp1";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
Scanner sc = new Scanner(System.in);
System.out.println("Enter two numbers : ");
int a = sc.nextInt();
int b = sc.nextInt();
System.out.println("before swapping :");
System.out.println("A : "+a);
System.out.println("B : "+b);
//write query
String query = "call swapnums1(?,?)";
//create CallableStatement
CallableStatement cs = con.prepareCall(query);
cs.setInt(1,a);
cs.setInt(2,b);
cs.registerOutParameter(1,Types.INTEGER);
cs.registerOutParameter(2,Types.INTEGER);
ResultSet rs = cs.executeQuery();
a = cs.getInt(1);
b = cs.getInt(2);
System.out.println("after swapping :");
System.out.println("A : "+a);
System.out.println("B : "+b);
}
}
/*
Mysql code
create database temp1;
use temp1;
delimiter //
create or replace procedure swapnums1(inout a int,inout b int)
begin
declare c int;
set c = a;
set a = b;
set b = c;
end//
delimiter ;
set @x=100;
set @y=200;
call swapnums1(@x,@y);
select @x;
select @y;
*/
14. CallableStatement : procedure with one in and second out type of arguments
import java.sql.*;
import java.util.*;
class test
{
public static void main(String ar[])throws ClassNotFoundException,SQLException
{
//load driver
String driver = "com.mysql.jdbc.Driver"; //5.1.5
Class.forName(driver);
//create connection
String url = "jdbc:mysql://localhost:3306/temp1";
String user = "root";
String pass = "";
Connection con = DriverManager.getConnection(url,user,pass);
Scanner sc = new Scanner(System.in);
System.out.println("Enter two numbers : ");
int a = sc.nextInt();
int b;
//write query
String query = "call pro1(?,?)";
//create CallableStatement
CallableStatement cs = con.prepareCall(query);
cs.registerOutParameter(2,Types.INTEGER);
cs.setInt(1,a);
ResultSet rs = cs.executeQuery();
b = cs.getInt(2);
System.out.println("A : "+a);
System.out.println("B : "+b);
}
}
/*
delimiter //
create procedure pro1(in a int,out b int)
begin
set b = a + 10;
set a = a + 5;
end//
delimiter ;
set @x = 100;
set @y = 200;
1.
call pro1(@x,@y);
select @x,@y;
100 110
2.
call pro1(10,20); ==> error bcz second parameter is out
3.
call pro1(10,@y);
select @y;
*/
15. DatabaseMetaData : Example
import java.sql.*;
class test
{
public static void main(String ar[])throws Exception
{
Class.forName("com.mysql.jdbc.Driver");
Connection con;
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/temp1","root","");
DatabaseMetaData ds = con.getMetaData();
System.out.println(ds.getURL());
System.out.println(ds.getUserName());
System.out.println(ds.getDriverName());
System.out.println(ds.getDriverVersion());
}
}