KajooBadam

Dynamic Query

Table in Oracle Database :- Insert , Update , Delete , Select

Select( Ctrl+a) , Copy ( Ctrl+c ) and Run the code for practice

package dynamicQuery;
import java.sql.*;
import java.util.*;

public class insert {

public static void main(String[] args) {
try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","myresult ","myresult ");
	Statement stmt=con.createStatement();
    Scanner sc=new Scanner(System.in);

System.out.println("Serial number no of student");
    int a=sc.nextInt();
    System.out.println("Name  of student");
    String b=sc.next();
    System.out.println("Subject of student");
    String c=sc.next();
    System.out.println("Marks  of student");
    int d=sc.nextInt();
    System.out.println("Status of student");
    String e=sc.next();
    String q="insert into student values("+a+",' "+b+" ',' "+c+" ', "+d+ ",'" +e+ "')" ;
    stmt.execute(q);

String r="Select *from student ";
   ResultSet rs =  stmt.executeQuery(r);
   System.out.println("DATA  OF STUDENT TABLE ");//assume that EMP is already created;
   while(rs.next() )
   {
   	int a1= rs.getInt(1);
   	System.out.print(a1+ "  " );
   	
   	String b1=rs.getString(2);
   	System.out.print(b1 + "      ");
   	
   	String c1=rs.getString(3);
   	System.out.print(c1 + " ");
   	
   	int d1= rs.getInt(4);
   	System.out.print(d1+ "  " );
   	
   	String e1=rs.getString(5);
   	System.out.print(e1 +" " );
       
   	System.out.println("\n");
}  
   }

catch(Exception e)
{System.out.println("some errror");
	}}}

package dynamicQuery;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class update {

public static void main(String[] args) {String choice; 
	Scanner sc =new Scanner(System.in);
	System.out.println("Enter Sr no of the row ");
	int a=sc.nextInt();
		
	try{
	/* for printing the Existing data of table */	   
		Class.forName("oracle.jdbc.driver.OracleDriver");//fully qualified class name
	    Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "myresult", "myresult");
		Statement stmt=con.createStatement();

String r="Select *from student where srno ="+a ;
		   ResultSet rs =  stmt.executeQuery(r);
		   System.out.println("DATA  OF STUDENT TABLE ");//assume that EMP is already created;
		   while(rs.next() )
		   {
		   	int a1= rs.getInt(1);
		   	System.out.print(a1+ "  " );
		   	
		   	String b1=rs.getString(2);
		   	System.out.print(b1 + "      ");
		   	
		   	String c1=rs.getString(3);
		   	System.out.print(c1 + " ");
		   	
		   	int d1= rs.getInt(4);
		   	System.out.print(d1+ "  " );
		   	
		   	String e1=rs.getString(5);
		   	System.out.print(e1 +" " );
		       
		   	System.out.println("\n");
		   }     
		   
		
		
		do{
			System.out.println("Press q for updating Name");
			System.out.println("Press w For updating sub ");
			System.out.println("Press e for updating marks");
			System.out.println("Press r for exit");
			String option =sc.next();
			char c = option.charAt(0);
			switch(c)
				{
				case 'q':
				case 'Q':
				System.out.println("Enter the Updated Name");	
				String name=sc.next();	
				String b1="update student set sname='"+name+ "'where srno=" +a ;
			    stmt.execute(b1);
				break;
				
				case'w':
				case 'W':
					System.out.println("Enter the Updated Subject");	
					String sub=sc.next();	
					String b2="update student set sub='"+sub+ "'where srno=" +a ;
				    stmt.execute(b2);
					break;
				
				case'e':
				case 'E':
					System.out.println("Enter the Updated Name");	
					int marks=sc.nextInt();	
					String b3="update student set marks='"+marks+ "'where srno=" +a ;
				    stmt.execute(b3);
				break;
				case 'X':
				case 'x':
				System.exit(0);
				break;
				default:
				System.out.println("Sorry your choice is wrong:");
				}
			    System.out.println("Do you want to continue y/n"); 
				Scanner Stdin =new Scanner(System.in);
				choice =Stdin.next();
				}
				while(choice.charAt(0)!= 'n');
			
	
	    String r1="Select *from student where srno ="+a ;
	    ResultSet rs1 =  stmt.executeQuery(r1);
	    System.out.println("DATA  OF STUDENT TABLE ");//assume that EMP is already created;
	    while(rs1.next() )
	    {
	   	int a1= rs1.getInt(1);
	   	System.out.print(a1+ "  " );
	   	
	   	String b1=rs1.getString(2);
	   	System.out.print(b1 + "      ");
	   	
	   	String c1=rs1.getString(3);
	   	System.out.print(c1 + " ");
	   	
	   	int d1= rs1.getInt(4);
	   	System.out.print(d1+ "  " );
	   	
	   	String e1=rs1.getString(5);
	   	System.out.print(e1 +" " );
	       
	   	System.out.println("\n");
	   }     
	  
	}
	
    catch(Exception e)
	{
    	System.out.println("some errror "+e);
	}
	
	}}

package dynamicQuery;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class delete {
	public static void main(String[] arg ) {

try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","myresult ","myresult ");
			Statement stmt=con.createStatement();
		    Scanner sc=new Scanner(System.in);

System.out.println("Serial number no of table row to delete the row");
		    int a=sc.nextInt();
		    String q="delete from student where srno=" +a;
		    stmt.execute(q);

String r="Select *from student ";
		   ResultSet rs =  stmt.executeQuery(r);
		   System.out.println("DATA  OF STUDENT TABLE ");
		   while(rs.next() )
		   {
		   	int a1= rs.getInt(1);
		   	System.out.print(a1+ "  " );
		   	
		   	String b1=rs.getString(2);
		   	System.out.print(b1 + "      ");
		   	
		   	String c1=rs.getString(3);
		   	System.out.print(c1 + " ");
		   	
		   	int d1= rs.getInt(1);
		   	System.out.print(d1+ "  " );
		   	
		   	String e1=rs.getString(4);
		   	System.out.print(e1 +" " );
		       
		   	System.out.println("\n");
		}  
		   }

catch(Exception e)
		{System.out.println("some errror");
			}}}

package dynamicQuery;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class select {
	public void dataselect( ) {

System.out.println("Enter the Srno of row for viewing the data");
		    int a=sc.nextInt();
		    String r="Select *from student where srno=" +a;
		    ResultSet rs =  stmt.executeQuery(r);
		    System.out.println("DATA  OF STUDENT TABLE ");//assume that EMP is already created;
		    while(rs.next() )
		    {
		   	int a1= rs.getInt(1);
		   	System.out.print(a1+ "  " );
		   	
		   	String b1=rs.getString(2);
		   	System.out.print(b1 + "      ");
		   	
		   	String c1=rs.getString(3);
		   	System.out.print(c1 + " ");
		   	
		   	int d1= rs.getInt(1);
		   	System.out.print(d1+ "  " );
		   	
		   	String e1=rs.getString(4);
		   	System.out.print(e1 +" " );
		       
		   	System.out.println("\n");
		}  
		   }

catch(Exception e)
		{System.out.println("some errror");
			}}}

Dynamic Quries

Insert
Delete
select
update

Query Format

Dynamic select

Select command is used to view data of the table according to user which is already created in oracle Database.

Query is:- "Select *from student where srno=" +a;

EX In Java:-

Dynamic Delete

Delete command Delete the values from the table according to user which is already Exist in oracle Database.

Query is:- delete from student where srno=" +a;

EX In Java:-

Dynamic Update

Update command is using for updating the values of the table dynamically which is already Exist in oracle Database.

Query is:- update emp set ename='Tom' where eid=101

EX In Java:-

Dynamic Insert

insert command is used to insert data in table by user through keyboard which is already Exist in oracle Database.

Query is:- Select *from student

EX In Java:-