Java

Tuesday 3 April 2012

weekend class meterials(jdbc)



TUTIORALS FROM MADHAV:


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

    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:
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>, ...)]}


1 comment: