1. How to load JDBC driver.
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()); } }