R4R Java JDBC

JAVA DATA BASE CONNECTIVITY

Java database connectivity is the API which defines how the client may access the database. It provide methods to querying the database, and updating the data base .Using JDBC you can send SQL, PL/SQL statements to almost any relational database. JDBC is a Java API for executing SQL statements and supports basic SQL functionality. It provides RDBMS access by allowing you to embed SQL inside Java code.

JDBC can helps you to write java applications that manage following three programming activities:

• Making connection to database

• Connect to a data source, like a database

• Send queries and update statements to the database

• JDBC Retrieve the result and process the results which is  received from the database in response to  query

JDBC ARCHITECTURE

The JDBC API contains two major sets of interfaces: the one is the JDBC API for application writers, and the other is the lower-level JDBC driver API for driver writers. JDBC technology drivers fit into one of four categories. Applications and applets can access databases via the JDBC API using pure Java JDBC technology-based drivers.

DATA BASE CONNECTIVITY STEPS

Before start connectivity first import the import.java.sql.*; package

1.Loading the database driver.:- The driver class is loaded sssby calling Class.forName() with the Driver class name as an argument. As it loaded, the Driver class creates an instance of it. The client can connect to Database Server through JDBC Driver.The JDBC-ODBC Bridge driver is commonly used. as most of the Database servers support ODBC driver.

try {
	Class.forName(�sun.jdbc.odbc.JdbcOdbcDriver�); //Or any other driver
}
catch(Exception x)
	{
	System.out.println( “Unable to load the driver class!� );
}

2.Creating a JDBC connection.:-getConnection() method of JDBC  Driver manager is used to establish a connection to a database.  The DriverManager class is manage the Drivers those are instaled on the system.. It uses a username, password, and a jdbc url to establish a connection to the database and returns a connection object.Within the context of a Connection, SQL, PL/SQL statements are executed and results are returned.

try{
 Connection dbConnection=DriverManager.getConnection(url,�loginName�,�Password�)
}
catch( SQLException x ){
	System.out.println( “Could not  get the  connection!� );
}

3.Creating a JDBC statement object.:-
Once the connection is established we can interact with the database.  To execute SQL statements, it is need to instantiate a Statement object from connection object by using the createStatement() method.

Statement statement = dbConnection.createStatement();

There are three type of statements which we used in jdbc.

1.Statement:- Execute simple sql queries without parameters.
Statement createStatement()
Creates an SQL Statement object.

2.Prepared Statement: -Execute precompiled sql queries with or without parameters.
PreparedStatement prepareStatement(String sql)
returns a new PreparedStatement object. PreparedStatement objects are precompiled SQL statements.

3.Callable Statement: -Execute a call to a database stored procedure.
CallableStatement prepareCall(String sql)
returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements.

4.Executing a SQL statement with the Statement object, and returning a jdbc resultSet.
Statement interface defines methods that are used to interact with database via the execution of SQL statements. The Statement class has three methods for executing statements: executeQuery(), executeUpdate(), and execute(). For a SELECT statement, the method to use is executeQuery ..ResultSet provides access to a table of data generated by executing a Statement. The table rows are retrieved in sequence. A ResultSet maintains a cursor pointing to its current row of data. The next() method is used to successively step through the rows of the tabular results.
ResultSetMetaData Interface holds information on the types and properties of the columns in a ResultSet. It is constructed from the Connection object.

Types of JDBC drivers
JDBC drivers are available for most database platforms, from a number of vendors and in a number of different flavors. There are four categories of drivers:
1 JDBC Type1 JDBC:-ODBC bridge drivers :-Type 1 drivers use a bridge technology to connect a Java client to an ODBC database system. The JDBC-ODBC Bridge from Sun and InterSolv is the only existing example of a Type 1 driver.
.
2 JDBC Type2 Native--They mainly use native API for data access. and they  provide Java wrapper classes to invoked using JDBC drivers

.3.JDBC Type 3 Driver - They are written totally in Java and use vendor independent Net-protocol to access a vendor independent remote listener.

4.JDBC Type 4 driver:- most efficient driver in all. 100% written in java.

JDBC URLs
URL can use to specify  driver to use, machine on which DB resides, data base, user name and password.
URL format
jdbc:<subprotocol>:<subname>
Subprotocol:-A name to deterrmine which driver is to use eg odbc, ff-microsoft
Subname:-information for use by the driver about which host/database to connect to.

example
jdbc:odbc:SIG-ODBC
Use the ODBC-JDBC Bridge to use to the ODBC connection called "SIG-ODBC".
An ODBC connection "SIG-ODBC" must be set-up on the client before this would work.

ADVANTAGES OF JDBC
1UseExisting enterprise Data:-With JDBC technology, businesses are not locked in any proprietary architecture, and can continue to use their installed databases and access information easily , even if it is stored on different database management systems.

2.Simplify The Enterprise development:-By the combination of the Java API and the JDBC API development of application be easy and economical. JDBC helps the developer by hiding complexity of data . JDBC API is simple to deploy program ,to learn an d use.

3.No configuration for network computers:-There is no configuration of JDBC required at th client side. all the information required to make a connection is completely defined by the JDBC URL or by a DataSource object registered with a Java Naming and Directory Interface (JNDI) naming service because JDBC driver is written in the Java programming language, So no or zero configuration for clients supports the network computing and centralizes software maintenance.

KEY FEATURES OF JDBC
1.Full access to metadata:-The JDBC API provides metadata access that enables the development of sensible applications those need to understand the facilities and capabilities of a specific database connection.
2.No installation is required:-A pure JDBC technology-based driver does not require special installation; it is automatically downloaded as part of the applet that makes the JDBC calls.
3.Database Connection Identified by URL:-JDBC technology exploits the advantages of Internet-standard URLs to identify database connections. The JDBC API includes an even better way to identify and connect to a data source, using a DataSource object, that makes code even more portable and easier to maintain.
 

An example to understand JDBC
import java.sql.* ;

class JDBCExample
{
 public static void main( String args[] )
 {
  try
     {
      // Load the database driver
      Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;

      // Get a connection to the database
      Connection conn = DriverManager.getConnection( "jdbc:odbc:Database" ) ;

      // Print all warnings
      for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )
         {
          System.out.println( "SQL Warning:" ) ;
          System.out.println( "State  : " + warn.getSQLState()  ) ;
          System.out.println( "Message: " + warn.getMessage()   ) ;
          System.out.println( "Error  : " + warn.getErrorCode() ) ;
         }

      // Get a statement from the connection
      Statement statm = conn.createStatement() ;

      // Execute the query
      ResultSet rs = statm.executeQuery( "SELECT * FROM Emp" ) ;

      // Loop through the result set
      while( rs.next() )
         System.out.println( rs.getString(1) ) ;

      // Close the result set, statement and the connection
      rs.close() ;
      statm.close() ;
      conn.close() ;
     }
  catch( SQLException se )
     {
      System.out.println( "SQL Exception:" ) ;

      // Loop through the SQL Exceptions
      while( see != null )
         {
          System.out.println( "State  : " + see.getSQLState()  ) ;
          System.out.println( "Message: " + see.getMessage()   ) ;
          System.out.println( "Error  : " + see.getErrorCode() ) ;

          see = see.getNextException() ;
         }
     }
  catch( Exception e )
     {
      System.out.println( e ) ;
     }
 }
}

JDBC CREATE TABLE

Using JDBC it is possible to relate one database table to another by creating a column in the table to derive its value from the value of a column in another table. If the tables had no columns in common, then there would be no way to relate them to one another. you can link tables together you can easily extract data from multiple tables with a single query, if your query mechanism supports this type of query.
In order to integrate your tables into a single database, you’ll need to ensure that each table has a column that contains a value unique to that table. Such a column is called a key. following is the jdbc program showing the use of executeUpdate() to create a table jdbc programming.here I am creating two tables as per my requirement Student and coursecourse_Id is the primary key which relate two tables In below example Course_Id is the primary key which relate two tables student and course

CREATE TABLE STUDENT(
Course_id INTEGER
Student_name VARCHAR(30)
);

CREATE TABLE COURSE(
Course_id INTEGER
Course_name VARCHAR(30)
Course_duration VARCHAR(20)
);

INSERTING DATA IN SQL TABLES
we will insert data in tables Student and Course which we create  already.
we use executeUpdate() to create a table and insert row into using java jdbc insert data statement.

UPDATE TABLE
JDBC update statement is used to update the data of the database.
 use jdbc executeupdate (uses jdbc update query) to update a table. For instance in a statement like
int n = stmt.executeUpdate();

Tolal:0 Click:
Show All Comments

Post Your Comments
Your Name:
Your Email ID :
Comments :
URL
	=* Enter SUM