JDBC

JDBC Projects

JDBC Project 1

adplus-dvertising
JAVA DATABASE CONNECTIVITY

Java database connectivity is an API which defines how the client may access the database. It provide methods to querying the database, and updating the database. 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, they are

  1. Making connection to the database.
  2. Connect to a data source, like a database.
  3. Send queries and update statements to the database.

JDBC process and retrieve the results 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.

Database Connectivity Steps

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

Loading the database driver:-

The driver class is loaded by 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. Most of the time 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!);
}

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! );
}

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, whose code is define below:

Statement statement = dbConnection.createStatement();

There are three types of statements which we used in jdbc ,they are define as follows:

  1. Statement: Execute simple sql queries without parameters. Statement createStatement() Creates a 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.

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 is use to 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 Driver: They are also known as ODBC bridge drivers which uses a bridge technology for connecting 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: The most efficient driver in all and 100% written in java.
JDBC url's

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 usage 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

UseExisting 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.

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 and use.

No configuration for network computers: There is no configuration of JDBC required at the 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

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.

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.

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 will 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 into the Student and Course tables which we already created.

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();