weekend class meterials(jdbc)

TUTIORALS FROM MADHAV:

     JAVA-SERVLETS     JAVA-JDBC     JAVA-JSP       HIBERNATE-SHCEMABASED 

    HIBERNATE-ANNOTATIONS     SPRING-IOC       SPRING –AOP-SCHEMABASED   

    SPRING-AOP-ANNOTATIONS      SPRING -DAO     SPRIN-MVC     SPRING-SECUTITY  

create table student(id number(6),

name varchar2(20),address varchar2(20),

course varchar2(20),institute varchar2(20));

public class DriverManager

extends Object

The basic service for managing a set of JDBC drivers.

getConnection

public static Connection getConnection(String url,

                                       String user,

                                       String password)

                                throws SQLException

Attempts to establish a connection to the given database URL. The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.

Parameters:

url - a database url of the form jdbc:subprotocol:subname

user - the database user on whose behalf the connection is being made

password - the user's password

Returns:

a connection to the URL

Throws:

SQLException - if a database access error occurs

public interface Connection extends Wrapper

A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.

A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.

Methods:

1.     createStatement

2.     prepareStatement

3.     prepareCall

4.     setAutoCommit

5.     rollback

6.     close

7.     getMetaData

example program:

//this program will describes getting the connection

FirstJdbc.java

package softech;

import java.sql.*;

public class FirstJdbc {

      public static void main(String[] args) {

           

            try

            {

                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                  Connection con=DriverManager.getConnection("jdbc:odbc:goddati", "system", "system");

                  System.out.println("connection obtained");

            }

            catch(Exception e)

            {

                  e.printStackTrace();

            }

     

Statement createStatement()

                          throws SQLException

Creates a Statement object for sending SQL statements to the database. SQL statements without parameters are normally executed using Statement objects. If the same SQL statement is executed many times, it may be more efficient to use a PreparedStatement object.

Result sets created using the returned Statement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY. The holdability of the created result sets can be determined by calling getHoldability().

Returns:

a new default Statement object

Throws:

SQLException - if a database access error occurs or this method is called on a closed connection

prepareStatement

PreparedStatement prepareStatement(String sql)

                                   throws SQLException

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

Returns:

a new default PreparedStatement object containing the pre-compiled SQL statement

Throws:

SQLException - if a database access error occurs or this method is called on a closed connectionprepareCall

CallableStatement prepareCall(String sql)

                              throws SQLException

Creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure.

Parameters:

sql - an SQL statement that may contain one or more '?' parameter placeholders. Typically this statement is specified using JDBC call escape syntax.

Returns:

a new default CallableStatement object containing the pre-compiled SQL statement

Throws:

SQLException - if a database access error occurs or this method is called on a closed connection.

setAutoCommit

void setAutoCommit(boolean autoCommit)

                   throws SQLException

Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.

Parameters:

autoCommit - true to enable auto-commit mode; false to disable it

Throws:

SQLException - if a database access error occurs, setAutoCommit(true) is called while participating in a distributed transaction, or this method is called on a closed connection

See Also:

getAutoCommit()

rollback

void rollback()

              throws SQLException

Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled.

Throws:

SQLException - if a database access error occurs, this method is called while participating in a distributed transaction, this method is called on a closed connection or this Connection object is in auto-commit mode

close

void close()

           throws SQLException

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

getMetaData

DatabaseMetaData getMetaData()

                             throws SQLException

Retrieves a DatabaseMetaData object that contains metadata about the database to which this Connection object represents a connection. The metadata includes information about the database's tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on.

Returns:

a DatabaseMetaData object for this Connection object

Throws:

SQLException - if a database access error occurs or this method is called on a closed connection

Statement:

public interface Statement extends Wrapper

The object used for executing a static SQL statement and returning the results it produces.
By default, only one ResultSet object per Statement object can be open at the same time.
Methods:

1.     executeUpdate

2.     executeQuery

3.     addBatch

4.     executeBatch

executeUpdate

int executeUpdate(String sql)

                  throws SQLException

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Parameters:

sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

Throws:

SQLException - if a database access error occurs, this method is called on a closed Statement or the given SQL statement produces a ResultSet object

Example program:

//this program will describes sending sql statement  to dbms

StatementEx.java

package softech;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class StatementEx {

            public static void main(String[] args) {

                        try

                        {

                                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                                    Connection con=DriverManager.getConnection("jdbc:odbc:goddati", "system", "system");

                                    System.out.println("connection obtained");

                                    //getting Statement object

                                    Statement st=con.createStatement();

                           //sending sql statement to database

                                    st.executeUpdate("insert into student values(01,'madhav','hyd','java','softech')");

                      System.out.println("record is inserted");          

                        }

                       

                        catch(Exception e)

                        {

                                    e.printStackTrace();

                        }

            }

}

executeQuery

ResultSet executeQuery(String sql)

                       throws SQLException

Executes the given SQL statement, which returns a single ResultSet object.

Parameters:

sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement

Returns:

a ResultSet object that contains the data produced by the given query; never null

Throws:

SQLException - if a database access error occurs, this method is called on a closed Statement or the given SQL statement produces anything other than a single ResultSet object

addBatch

void addBatch(String sql)

              throws SQLException

Adds the given SQL command to the current list of commmands for this Statement object. The commands in this list can be executed as a batch by calling the method executeBatch.

Parameters:

sql - typically this is a SQL INSERT or UPDATE statement

Throws:

SQLException - if a database access error occurs, this method is called on a closed Statement or the driver does not support batch updatesexecuteBatch

int[] executeBatch()

                   throws SQLException

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch

Example program:

//this program will describes sending batch files to dbms

BatchFileEx.java

package softech;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class BatchFileEx {

      public static void main(String[] args) {

           

            try

            {

                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                  Connection con=DriverManager.getConnection("jdbc:odbc:goddati", "system", "system");

                  System.out.println("connection obtained");

                  //getting Statement object

                  Statement st=con.createStatement();

               //sending sql statement to database

                  st.addBatch("insert into student values(02,'narendar','hyd','java','softech')");

                  st.addBatch("insert into student values(03'prasad','hyd','java','softech')");

              st.executeBatch();

                  System.out.println("batch statements are executed");

            }

           

            catch(Exception e)

            {

                  e.printStackTrace();

            }

      }

}

public interface PreparedStatement extends Statement

An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES

                                     SET SALARY = ? WHERE ID = ?");

   pstmt.setBigDecimal(1, 153833.00)

   pstmt.setInt(2, 110592)

Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.

Methods:’

1.     executeUpdate

2.     executeQuery

executeUpdate

int executeUpdate()

                  throws SQLException

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

Throws:

SQLException - if a database access error occurs; this method is called on a closed PreparedStatement or the SQL statement returns a ResultSet object

Example program:

//this progam will describes  sending sql statements through PrepareStatement.

PrepareStatementEx.java

package softech;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.*;

public class PrepareStatementEx {

      public static void main(String[] args) {

           

            try

            {

                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                  Connection con=DriverManager.getConnection("jdbc:odbc:goddati", "system", "system");

                  System.out.println("connection obtained");

                  //getting PrepareStatement object

                                

                  PreparedStatement pstmt = con.prepareStatement("insert into student values(?,?,?,?,?)");

                  pstmt.setInt(1, 104);

                  pstmt.setString(2,"hari");

                  pstmt.setString(3, "hyd");

                  pstmt.setString(4,"cpp");

                  pstmt.setString(5, "softech");

                  pstmt.executeUpdate();

                  System.out.println("record inserted");

                 

            }

           

            catch(Exception e)

            {

                  e.printStackTrace();

            }

      }

}

executeQuery

ResultSet executeQuery()

    

     

            throws SQLException

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Returns:

a ResultSet object that contains the data produced by the query; never null

Throws:

SQLException - if a database access error occurs; this method is called on a closed PreparedStatement or the SQL statement does not return a ResultSet object

public interface ResultSet extends Wrapper:

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

       Statement stmt = con.createStatement(

                                      ResultSet.TYPE_SCROLL_INSENSITIVE,

                                      ResultSet.CONCUR_UPDATABLE);

       ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");

       // rs will be scrollable, will not show changes made by others,

       // and will be updatable

rs.absolute(5); // moves the cursor to the fifth row of rs

       rs.updateString("NAME", "AINSWORTH"); // updates the 

          // NAME column of row 5 to be AINSWORTH

       rs.updateRow(); // updates the row in the data source

methods:

1.     absolute

2.     previous

3.     getInt

4.     getFloat

5.     getString

6.     absolute

boolean absolute(int row)

                 throws SQLException

Moves the cursor to the given row number in this ResultSet object.

previous

boolean previous()

                 throws SQLException

Moves the cursor to the previous row in this ResultSet object.

getInt

int getInt(int columnIndex)

           throws SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

Example program:

//this program will describes getting data from ResultSet

package softech;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class ResultSetEx {

      public static void main(String[] args) {

     

            try

            {

                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                  Connection con=DriverManager.getConnection("jdbc:odbc:goddati", "system", "system");

                  System.out.println("connection obtained");

                  //getting Statement object

                  Statement st=con.createStatement();

               //getting ResultSet object

                 

                  ResultSet rs=st.executeQuery("select id,name from student");

                  //displaying data from ResultSet object

                  while(rs.next())

                  {

                        int a=rs.getInt(1);

                        String s=rs.getString(2);

                        System.out.println("id="+a +"name="+s);

                  }

                 

                System.out.println("records are selected");      

            }

           

            catch(Exception e)

            {

                  e.printStackTrace();                 

            }

      }

}E

public interface CallableStatement

extends PreparedStatement

The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

   {call <procedure-name>[(<arg1>,<arg2>, ...)]}