JAVA - JDBC

  • 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());
        		
        	}
        }