11: How to create a table dynamically from a jdbc application?.

      //import section
       import java.sql.*;
      import java.io.*;

      public class CreateTableEx
      {
               public static void main(String[] args)throws Exception
               {
                           //create buffered reader object
                  BufferedReader br = new BufferedReader(new       InputStreamReader(System.in));

                  //load and register the driver
                  Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

                  //establish connection
                  Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);

                  //create statement object
                  Statement st = con.createStatement();

                  //take table name as dynamic input
                  System.out.println(“Enter table name”);
                  String tname = br.readLine();

                  //execute sql query
                  St.executeUpdate(“create table”+tname+”(eno number,ename varchar2(10),esal number,eaddr varchar2(10))”);

                  System.out.println(“table created successfully”);

                  //closing the connection
                  con.close();
      }
}

12: How to insert records into a table from a JDBC application?

import java.sql.*;
import java.io.*;
public class InsertTableEx
{
   public static void main(String[] args) throws Exception
   {
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection con  =               DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”durga”);
Statement st = con.createStatement();
while(true)
{
            System.out.println(“Enter emp number”);
            Int eno = Integer.parseInt(br.readLine());
            System.out.println(“Enter emp name”);
            String ename = br.readLine();
            System.out.println(“Enter emp sal”);
            Float esal = Float.parseFloat(br.readLine());
            System.out.println(“Enter emp address”);
            String eaddr = br.readLine();
st.executeUpdate(“insert into emp1 values(“+eno+”,’”+ename+”’,”+esal+”,’”+eaddr+”’)”);
            System.out.println(“read successfully inserted”);
            System.out.println(“one more record[y/n]);
            String option = br.readLine();
            If(option.equals(“n”))
                        break;
}
      }
}

13: How to update a table  from a jdbc application?.

import java.sql.*;
public class UpdateTableEx
{
   public static void main(String[] args)throws Exception
   {
            //load n register the driver in alternative way to Class.forName
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xee”,”system”,”durga”);

Statement st = con.createStatement();
int updateCount = st.executeUpdate(“update emp1 set esal = esal+500 where esal<9000”);
System.out.println(“records updated……..”+updateCount);

con.close();
   }
}

14: How to delete records from  a table  from  jdbc application?.

import java.sql.*;
public class DeleteTableEx
{
   public static void main(String[] args)throws Exception
   {
            Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”durga”);

            Statement st = con.createStatement();
            int updateCount = sst.executeUpdate(“delete from emp3 where esal>=7000”);
            System.out.println(“records deleted………”+updateCount);

            con.close();
   }
}

15:What is ment by ResultSet object and How to Fetch the Data from Database?.

ResultSet:-

ResultSet is an Object which can be used to maintain the fetched data from database in the JDBC applications
           
When we execute a selection group sql query, either with executeQuety()  or with execute() automatically a ResultSet object will be created at heap memory with the fetched data from database.

• To get the ResultSet object reference directly we will use executeQuery(..).
• When we create a ResultSet object automatically a cursor will be create called as “ResultSet cursor” to read the data from ResultSet object.
• When we create the ResultSet object by default ResultSet cursor will be created before the first record.
• If we want to read the data from ResultSet object every time we need to check whether the next record is available or not. If the next record is available automatically we need to move that ResultSet cursor to next record position.
• To perform this work we will use the following method from ResultSet interface.

                        public boolean next()

• After getting ResultSet cursor to a record position then we need to get the data from respective fields of the particular record, for this we will use following method.

public xxx getXxx(int fno)
            (or)
public xxx getXxx(String fname)

            where xxx is byte, shor, int, long, float, double, char.

Eg:   while(rs.next())
            {
System.out.println(rs.getInt(1)+”   ”+rs.getString(2)+”   ”+rs.getFloat(3)+”   ”+rs.getString(4));
                        }

The following example demonstrates how to fetch the data from database through ResultSet object.

import java.sql.*;
public class FetchEx
{
   public static void main(String[] args)throws Exception
   {
            Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(“select * from emp1”);
            System.out.println(“ENO      ENAME      ESAL      EADDR”);
            System.out.println(“********************************”);
            while(rs.next())
            {
                        System.out.println(rs.getInt(1)+””+rs.getString(2)+”   ”+rs.getFloat(3)+”              ”+rs.getString(4));
            }
   }
}

16:Ingeneral execute() method can be used to execute selection group SQl queries for getting the data from Database , but execute() return a boolean value true so here how it possible to fetch the data from database?

• Execute() can be used to execute both selection group sql query and updation group sql query.
• If we use execute() to execute a selection group sql query then DBE(Database engine) will execute that sql query and  send back the fetched data from database to java application. Now java application will prepare a ResultSet object with the fetched data but execute() will return “true” as a Boolean value.
• At this situation to get the reference of the ResultSet object explicitily, we will use the following method from Statement object.

                        public ResultSet getResultSet()

            Eg:  boolean b = st.execute(“select * from emp1”);
                    System.out.println(b);
                    ResultSet rs = st.getResultSet();

17:Ingeneral execute() method can be used to execute updatation group SQl queries for updating the data on Database , but execute() return a boolean value false  so here how it possible to get the records updated count value(int value)?

• Execute() can be used to execute both selection group sql queries and updation group sql queries.

• If we use execute() to execute an updation group sql query then DBE will execute it and send back the records updated count value to the java application. But execute() will return “false” as a Boolean value. At this instance, to get the records updated count value explicitly we will use the following method from Statement object.

                        public int getUpdateCount()

            Eg:      import java.sql.*;
                        public class FetchEx
                        {
                                    public static void main(String[] args)throws Exception
                                    {
                                                Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);

                                                Statement st = con.createStatement();
boolean b = st.execute(“update emp1 set esal=esal+500 where esal<9000”);
System.out.println(b);
int updateCount = st.getUpdateCount();j
System.out.println(updateCount);
                                    }
                        }

 18: If we use selection group SQL query to executeUpdate() ,what happened?

• If we use selection group sql query as a parameter to executeUpdate(…) then JVM will send that sql query to the DBE, DBE will fetch the data and send  back to the java application here java application will store the fetched data in the form of ResultSet object. But executeUpdate() is expecting records  updated count value.

            Due to this contradiction JVM will rise an exception like java.lang.SQLException.

If we handle the above exception properly then we will get ResultSet abject and we will get the data from Database

import java.sql.*;
class Test
{
public static void main(String[] args)
{
            Statement st=null;
            try
            {         
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

                     Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);

                        st = con.createStatement();
boolean b = st.executeUpdate(“select * from emp1”);
            }
            catch(Exception e)
            {
                        ResultSet rs=st.getResultSet();
            System.out.println(“ENO      ENAME      ESAL      EADDR”);
            System.out.println(“********************************”);
            while(rs.next())
            {
                        System.out.println(rs.getInt(1)+””+rs.getString(2)+”   ”+rs.getFloat(3)+”              ”+rs.getString(4));
            }

                        e.printStackTrace();
            }

}

19: If we use updatation group SQL query to executeQuery() ,what happened?

• If we use updation group sql query as a parameter to executeQuery() then JVM will send that sql query to the DBE, DBE will perform updations on the database and send back records updated count value to the java application. But here executeQuery() is expecting ResultSet object reference.

            Due to this contradiction JVM will rise an exception like java.lang.SQLException.
           
import java.sql.*;
class Test
{
public static void main(String[] args)
{
            Statement st=null;
            try
            {         
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

                     DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);

                        st = con.createStatement();
  boolean b = st.executeQuery(“update  emp1 set esal=esal+1000 where         esal <10000”);
            }
            catch(Exception e)
            {
                        int count=st.getUpdateCount();
                        System.out.println(count);

                        e.printStackTrace();
            }

}

20: What is ment by ResultSet and What are the types of ResultSets are available in JDBC application?

            In jdbc applications ResultSets could be classified in the following two ways.

• On the basis of ResultSet privilizations (Concurancy):- 

There are 2 types of ResultSets.

• Read only ResultSet
• Updatable ResultSet

Read only ResultSet:-   It is a ResultSet, which will allow the users to read the        data only. To refer this ResultSet, we will use the following constant from ResultSet interface.

                        public static final int CONCUR_READ_ONLY;

 Updatable ResultSet:-  If is a ResultSet object, which will allow users to perform some updations on its content. To refer this ResultSet we will use the following constant from ResultSet interface.

                        public static final int CONCUR_UPDATABLE;

2)On the  basis of  the ResultSet cursor movement:-

There are 2 types of ResultSets.

• Forward  only ResultSet
• Scrollable ResultSet

Forward only ResultSet:-  It is a ResultSet object, which will allow the users to iterate the data in any forward direction. To refer this ResultSet object we will use the following  constant from ResultSet interface.

                        public static final int TYPE_FORWARD_ONLY;

Scrollable ResultSet:-  These are the ResultSet objects, which will allow the users to iterate the data in both forward and backward directions.
            There are 2 types of Scrollable ResultSets.

• Scroll sensitive ResultSets
• Scroll in sensitive ResultSets.