Java Tutorial

• Product Components of JDBC
• Understanding the JDBC Architecture
• JDBC Driver and Its Types
• JDBC Versions
• Relational Database Concepts
• Understanding Common SQL statements
• Introduction to java.sql package
• Driver Manager Class
• Understanding Connection Object
• JDBC Example with MySQL
• Creating a Database in MySQL
• Description of Database Table
• Inserting values in MySQL database table
• JDBC - Java Database Connectivity Tutorials
  
  

BEE to rate computer data centres from next year

NEW DELHI: The Bureau of Energy Efficiency will start rating, from the next year, the energy-guzzling computer data centres that host companies' networking equipment and applications, on the basis of their efficiency.

"The BEE under the Power Ministry and Indian Green Building Council (IGBC) along with the software giants like Wipro and Infosys is developing a system to measure the data centres," said Prem C Jain, Chairman of IGBC, an arm of Confederation of Indian Industry (CII).

The data centres would be measurable which means how much the centre can consume beyond their data storage. They will then be rated as "Silver", "Gold" or "Platinum", depending on their consumption capacity, he explained.

Jain was of the opinion that the rating for data centres becomes all the more important in view of rapid growing information technology sector and internet usage.

However, though the guidelines would not be mandatory, he asserted that the rating would help the operators and owners of these centres reduce their environmental impact besides saving energy.

By combining various measures, savings of between 30 and 40 per cent over conventional data centre energy consumption are achievable.

"And unless they (for instance IT firms) opt for green data centres their energy bill will shoot up sharply which is also not good for climate change," he added.

"Several IT companies like IBM and Wipro have already expressed their desire for such a rating system which would help them setting up energy-efficient data centres," Jain said.

Retrieving All Rows from a Database Table

Here, you will learn how to retrieve all rows from a database table. You know that table contains the data in rows and columns format. If you want to access the data from a table then you need to use some APIs and methods. See brief descriptions for retrieving all rows from a database table as below:

Description of program:
Program establishes the connection between MySQL database and java file so that the we can retrieve all data from a specific database table. If any exception occurs then shows a message "SQL code does not execute.".

Description of code:

executeQuery(String sql):
This method executes the SQL statement and returns a single ResultSet object. It takes string type parameter for executing the SQL statement.

SELECT * FROM table_name:
Above code retrieves all data from specific database table.

getInt(String column_name):
This method is of java.sql.ResultSet interface that takes string type parameter and returns an integer type values.

getString(String column_name):
This method is same as getInt() method but it returns the string type values.

Here is the code of program:

import java.sql.*;

public class GetAllRows{

public static void main(String[] args) {

System.out.println("Getting All Rows from a table!");

Connection con = null;

String url = "jdbc:mysql://localhost:3306/";

String db = "jdbctutorial";

String driver = "com.mysql.jdbc.Driver";

String user = "root";

String pass = "root";

try{

Class.forName(driver).newInstance();

con = DriverManager.getConnection(url+db, user, pass);

try{

Statement st = con.createStatement();

ResultSet res = st.executeQuery("SELECT * FROM employee6");

System.out.println("Emp_code: " + "\t" + "Emp_name: ");

while (res.next()) {

int i = res.getInt("Emp_code");

String s = res.getString("Emp_name");

System.out.println(i + "\t\t" + s);

}

con.close();

}

catch (SQLException s){

System.out.println("SQL code does not execute.");

}

}

catch (Exception e){

e.printStackTrace();

}

}

}

 

Output of Program

 

HR Interview Questions and Job Interview Questions

Tell me about yourself.
Since this is often the opening question in an interview, be extra careful that you don't run off at the mouth. Keep your answer to a minute or two at most. Cover four topics: early years, education, work history, and recent career experience. Emphasize this last subject. Remember that this is likely to be a warm-up question. Don't waste your best points on it.

What do you know about our organization?.

You should be able to discuss products or services, revenues, reputation, image, goals, problems, management style, people, history and philosophy. But don't act as if you know everything about the place. Let your answer show that you have taken the time to do some research, but don't overwhelm the interviewer, and make it clear that you wish to learn more.

You might start your answer in this manner: "In my job search, I've investigated a number of companies.

Yours is one of the few that interests me, for these reasons..."

Give your answer a positive tone. Don't say, "Well, everyone tells me that you're in all sorts of trouble, and that's why I'm here", even if that is why you're there.

Why do you want to work for us?

The deadliest answer you can give is "Because I like people." What else would you like-animals?

Here, and throughout the interview, a good answer comes from having done your homework so that you can speak in terms of the company's needs. You might say that your research has shown that the company is doing things you would like to be involved with, and that it's doing them in ways that greatly interest you. For example, if the organization is known for strong management, your answer should mention that fact and show that you would like to be a part of that team. If the company places a great deal of emphasis on research and development, emphasize the fact that you want to create new things and that you know this is a place in which such activity is encouraged. If the organization stresses financial controls, your answer should mention a reverence for numbers.

If you feel that you have to concoct an answer to this question - if, for example, the company stresses research, and you feel that you should mention it even though it really doesn't interest you- then you probably should not be taking that interview, because you probably shouldn't be considering a job with that organization.

Your homework should include learning enough about the company to avoid approaching places where you wouldn't be able -or wouldn't want- to function. Since most of us are poor liars, it's difficult to con anyone in an interview. But even if you should succeed at it, your prize is a job you don't really want.

In what way java is 'simple'?

In what way java is 'simple'?

Ans:) No langauage is simple,but JAVA is a bit easier compared to the popular object oriented programming language C++, which has been the dominant software development language in the past.Java is partially modelled after C++,but greatly simplified and improved.For example,pointers and multiple inheritance in C++ make programming complicated.Java replaced multiple inheritance in C++ with a simple structure called interface and eliminated pointers. Java uses automatic memory allocation and garbage collection, while C++ requires the programmer to allocate memory to collect the garbage

What are the features of java?

What are the features of java?

Ans:) As stated in the Java language whitepaper by SUN:" Java is simple,objet oriented,distributed,interpreted, robust,secure,architecture neutral,portable,high performance, multithreaded and dynamic".

What is OOP

What is OOP
Ans:) OOP is the fullform for "Object Oriented Programming". Object Oriented Programming providesgreat flexibility,modularity,clarity,and reusability through method abstraction and class inheritance

Java Interview Questions

What is JAVA?
Ans:) Java is a cross-platform,object oriented,network based and multimedia ready language. Java, formerly known as oak, is an object-oriented programming language developed by Sun. It shares many superficial similarities with C, C++, and Objective C (for instance for loops have the same syntax in all four languages); but it is not based on any of those languages, nor have efforts been made to make it compatible with them. Java is the internet programming language as the future of computing is highly influenced by the Internet.

JDBC - Java Database Connectivity Tutorials

JDBC is Java application programming interface that allows the Java programmers to access database management system from Java code. It was developed by JavaSoft, a subsidiary of Sun Microsystems.
Definition
Java Database Connectivity in short called as JDBC. It is a java API which enables the java programs to execute SQL statements. It is an application programming interface that defines how a java programmer can access the database in tabular format from Java code using a set of standard interfaces and classes written in the Java programming language.
JDBC has been developed under the Java Community Process that allows multiple implementations to exist and be used by the same application. JDBC provides methods for querying and updating the data in Relational Database Management system such as SQL, Oracle etc.
The Java application programming interface provides a mechanism for dynamically loading the correct Java packages and drivers and registering them with the JDBC Driver Manager that is used as a connection factory for creating JDBC connections which supports creating and executing statements such as SQL INSERT, UPDATE and DELETE. Driver Manager is the backbone of the jdbc architecture.
Generally all Relational Database Management System supports SQL and we all know that Java is platform independent, so JDBC makes it possible to write a single database application that can run on different platforms and interact with different Database Management Systems.
Java Database Connectivity is similar to Open Database Connectivity (ODBC) which is used for accessing and managing database, but the difference is that JDBC is designed specifically for Java programs, whereas ODBC is not depended upon any language.
In short JDBC helps the programmers to write java applications that manage these three programming activities:

1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and
3. Retrieving and processing the results received from the database in terms of answering to your
query.

Inserting values in MySQL database table

After making the table in the database, we need to insert the values in the database. Here we are going to see, how we can insert values in the MySQL database table. We know that tables store data in rows and column format. After creating a database table, you need to insert the values in it. In this section, we are providing an example with code that provides the facility for inserting the values in MySQL database table.
Description of program:
First of all this program establishes the connection with MySQL database through the JDBC driver, after only that we will be able to insert the values in specific table with the help of some APIs and methods. If any values get inserted in the table then shows a message "1 row affected" but if any problems comes while inserting the data in the table then it will displays the message "SQL statement is not executed!".
Description of code:
INSERT table_name VALUES(field_values):
Above code is used, when you want to insert values in the database table with appropriate value.
Here is the code of program:
import java.sql.*;

public class InsertValues{
public static void main(String[] args) {
System.out.println("Inserting values in Mysql database table!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"root","root");
try{
Statement st = con.createStatement();
int val = st.executeUpdate("INSERT employee VALUES("+13+","+"'Aman'"+")");
System.out.println("1 row affected");
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Output of program:
C:\vinod\jdbc\jdbc\jdbc-mysql>javac InsertValues.java

C:\vinod\jdbc\jdbc\jdbc-mysql>java InsertValues
Inserting values in Mysql database table!
1 row affected
Retrieving All Rows from a Database Table
Here, you will learn how to retrieve all rows from a database table. You know that table contains the data in rows and columns format. If you want to access the data from a table then you need to use some APIs and methods. See brief descriptions for retrieving all rows from a database table as below:
Description of program:
Program establishes the connection between MySQL database and java file so that the we can retrieve all data from a specific database table. If any exception occurs then shows a message "SQL code does not execute.".
Description of code:
executeQuery(String sql):
This method executes the SQL statement and returns a single ResultSet object. It takes string type parameter for executing the SQL statement.
SELECT * FROM table_name:
Above code retrieves all data from specific database table.
getInt(String column_name):
This method is of java.sql.ResultSet interface that takes string type parameter and returns an integer type values.
getString(String column_name):
This method is same as getInt() method but it returns the string type values.
Here is the code of program:
import java.sql.*;

public class GetAllRows{
public static void main(String[] args) {
System.out.println("Getting All Rows from a table!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db, user, pass);
try{
Statement st = con.createStatement();
ResultSet res = st.executeQuery("SELECT * FROM employee6");
System.out.println("Emp_code: " + "\t" + "Emp_name: ");
while (res.next()) {
int i = res.getInt("Emp_code");
String s = res.getString("Emp_name");
System.out.println(i + "\t\t" + s);
}
con.close();
}
catch (SQLException s){
System.out.println("SQL code does not execute.");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Output of program:
C:\vinod\jdbc\jdbc-mysql>java GetAllRows
Getting All Rows from a table!
Emp_code: Emp_name:
10 vinod
11 Amar
15 Aman
1 sushil
Count Rows from a Database Table
After creating a database table, if we want to know number of rows in a table then we can get it very easily by using the simple database query. See brief description below:
Description of program:
For this program to work firstly we need to establish the connection with MySQL database by the help of JDBC driver. When the connection has been established we need to pass a table name from the given database in the query and the rows will be counted and the result will be displayed. If any exception is thrown then it will show "SQL statement is not executed!"
Description of code:
SELECT COUNT(*) FROM table_name;
This code is used to count the rows of given table.
table_name: It is a name of the table of which we want to see the rows.
Here is the code of program:
import java.io.*;
import java.sql.*;

public class CountRows{
public static void main(String[] args) {
System.out.println("Count number of rows in a specific table!");
Connection con = null;
int count = 0;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
ResultSet res = st.executeQuery("SELECT COUNT(*) FROM "+table);
while (res.next()){
count = res.getInt(1);
}
System.out.println("Number of column:"+count);
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
output of program:
C:\vinod\jdbc\jdbc\jdbc-mysql>javac CountRows.java

C:\vinod\jdbc\jdbc\jdbc-mysql>java CountRows
Count number of rows in a specific table!
Enter table name:
student
Number of column:6
Getting Column Names from a database table in Java
Here we are providing you an example with code that retrieves all columns name in a specific database table. Sometimes, you need to know the number of columns and the names of the columns of the table, so you can retrieve it with the help of this example. See detail information given below:
Description of program:
Create a class ColumnName. The name of the class should be such that the other person can easily understand what this program is going to do. Strictly follow the naming conventions of java. Now declare a static method inside which creates the connection with MySQL database through the JDBC driver. After establishing the connection then you get all columns name and number of columns of specified table with the help of some APIs and methods.
Description of code:
ResultSetMetaData:
This is an interface of java.sql package that can be used for getting information about types and properties of columns in a ResultSet object.
getColumnCount():
Above method retrieves number of columns (integer types data) in the ResultSet object.
getColumnName(int column):
This method returns columns name (string type data) from ResultSetMetaData object and takes integer type value.
Here is the code of program:
import java.sql.*;

public class ColumnName{
public static void main(String[] args) {
System.out.println("Getting Column Names Example!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
try{
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM employee6");
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount();
System.out.println("Number of Column : "+ col);
System.out.println("Columns Name: ");
for (int i = 1; i <= col; i++){ String col_name = md.getColumnName(i); System.out.println(col_name); } } catch (SQLException s){ System.out.println("SQL statement is not executed!"); } } catch (Exception e){ e.printStackTrace(); } } } Output of program: C:\vinod\jdbc\jdbc-mysql>javac ColumnName.java

C:\vinod\jdbc\jdbc-mysql>java ColumnName
Getting Column Names Example!
Number of Column : 2
Columns Name:
Emp_code
Emp_name
Adding a New Column Name in Database Table
In this jdbc tutorial program we are going to learn about adding a new column in database table. Sometimes it happens that we have created a table and forgets to add some important column name into it. Later while retrieving the data from that table we come to know that the table doesn't contains that particular column we are searching for. So there is no need to get panic. We have the solution for this, we are describing it with the help of the simple example. Brief description given below:
Description of program:
Firstly we need to create a connection with the help of JDBC driver for connecting to the MySQL database. Remember, in this program we are going to add columns to an existing database table. After establishing the connection, it takes table name, column name and it's data type and at last add a new column in the table. If the column gets inserted or added in the table then it shows "Query OK, n rows affected" otherwise it will displays a message "Table or column or data type is not found!".
Description of code:
ALTER TABLE table_name ADD col_name data_type;
Above code is used for adding a new column in the database table and takes appropriate attributes:
table_name: This is a table name in which you want to add a new column name.
col_name: It is a name of the column that you want to add in a table.
data_type: This is a data type of new column.
Here is the code of program:
import java.io.*;
import java.sql.*;

public class AddColumn{
public static void main(String[] args) {
System.out.println("Adding new column in table example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
System.out.println("Enter column name:");
String col = bf.readLine();
System.out.println("Enter data type:");
String type = bf.readLine();
int n = st.executeUpdate("ALTER TABLE "+table+" ADD "+col+" "+type);
System.out.println("Query OK, "+n+" rows affected");
}
catch (SQLException s){
System.out.println("Tabel or column or data type is not found!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Database Table: Student Table
Stu_id Stu_name Stu_sub
100 vinod computer
101 raju math
102 ranjan bio
103 Aman phy
Output of program:
C:\vinod\jdbc\jdbc\jdbc-mysql>javac AddColumn.java

C:\vinod\jdbc\jdbc\jdbc-mysql>java AddColumn
Adding new column in table example!
Enter table name:
Student
Enter column name:
Stu_marks
Enter data type:
integer
Query OK, 4 rows affected
After adding a new column: Student Table
Stu_idl Stu_name Stu_sub Stu_marks
100 vinod computer NULL
101 raju math NULL
102 ranjan bio NULL
103 Aman phy NULL

Description of Database Table

This section introduces you how to get the detailed information about the database table. As we all know that, table has some specific information like: field, type, null etc. But how to get all those information by the database. By using a simple query we can get all information about the table like the number of columns and rows in it, its type, whether the field is null or not, whether the column is unique or primary and much more.
Description of program:
While making this program firstly we should establish the connection with MySQL database through the JDBC driver. When the connection has been established, pass the table name in the database query and use some java methods to get the detail description of table. When the program will gets execute then it will show field name, type and null of the database table. If any field have null value then shows "YES" and if not null then shows "NO". If any problem is created at the time of query, it will show "SQL statement is not executed!".
Description of code:
DESCRIBE table_name;
This code is used to know the brief description of a database table. It takes the name of that table of which we want to see the description.
getColumns(String cat, String sche, String tab, String col_pat)::
This method returns ResultSet object and provides descriptions of table. It takes four string type arguments as given below:
String cat: This is a table catalog. It may be null value.
String sche: It shows schema of table. It may be null value.
String tab: This is a table name. It must require for getting the information.
String col_pat: It shows column name pattern.
COLUMN_NAME: Shows column name
TYPE_NAME: data types of column like: integer, varchar, char etc.
COLUMN_SIZE: Column size
NULLABLE: column can be null.
Here is the code of program:
import java.io.*;
import java.sql.*;

public class DiscriptionTable{
public static void main(String[] args) {
System.out.println("See Description of Table Example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
ResultSet rs = st.executeQuery("DESCRIBE "+table);
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount();
for (int i = 1; i <= col; i++){ String col_name = md.getColumnName(i); System.out.print(col_name+"\t"); } System.out.println(); DatabaseMetaData dbm = con.getMetaData(); ResultSet rs1 = dbm.getColumns(null,"%",table,"%"); while (rs1.next()){ String col_name = rs1.getString("COLUMN_NAME"); String data_type = rs1.getString("TYPE_NAME"); int data_size = rs1.getInt("COLUMN_SIZE"); int nullable = rs1.getInt("NULLABLE"); System.out.print(col_name+"\t"+data_type+"("+data_size+")"+"\t"); if(nullable == 1){ System.out.print("YES\t"); } else{ System.out.print("NO\t"); } System.out.println(); } } catch (SQLException s){ System.out.println("SQL statement is not executed!"); } } catch (Exception e){ e.printStackTrace(); } } } Output of program: C:\vinod\jdbc\jdbc\jdbc-mysql>javac DiscriptionTable.java

C:\vinod\jdbc\jdbc\jdbc-mysql>java DiscriptionTable
See Description of Table Example!
Enter table name:
vk
Field Type Null Key Default Extra
id int(11) NO
name char(1) YES
Deleting a Table from Database
Imagine a situation where we need to delete a table from the database. We can do it very easily by using the commands in the MySQL database. But how we can delete the table using java methods and API. In this section we are describing, how to delete a table from database using java methods. Java provides the facility for deleting a specific table from a given database with the help of some specified methods. See detailed information given below:
Description of program:
Create a class DeleteTable inside which, firstly establishes the connection with MySQL database. After establishing the connection we will delete a table from specific database. If the table which we want to delete get deletes then we will print the message "Table Deletion process is completely successfully!", otherwise it will display " Table is not exists!".
Description of code:
DROP TABLE table_name:
Above code is used for deleting any table from a given database.
Here is the code of program:
import java.sql.*;

public class DeleteTable{
public static void main(String[] args) {
System.out.println("Tabel Deletion Example");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbctutorial";
String driverName = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try{
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(url+dbName, userName, password);
try{
Statement st = con.createStatement();
st.executeUpdate("DROP TABLE Employee1");
System.out.println("Table Deletion process is completly successfully!");
}
catch(SQLException s){
System.out.println("Table is not exists!");
}
con.close();
}
catch (Exception e){
e.printStackTrace();
}
}
}
Retrieving Tables from a Database
In database system it is very important to know about the tables. To work with this, it is very important to know how to retrieve a table and create a table in the database. This section provides you a facility for retrieving tables from a specific database through an example. In relational database, all the data is stored in the tabular format (rows and columns). See detail information below:
Description of program:
In this program we are establishing the connection between the MySQL database and Java file. We will retrieve the table with the help of some java methods and APIs interface. If the database has one or more tables then it shows all tables, otherwise displays the message "No any table in the database".
Description of code:
DatabaseMetaData:
This is an interface of java.sql package that implemented by driver vendors. It tells about the data of the data like number of tables in the database , the information about the columns of the table.
getMetaData():
It is a method of Connection interface. This method has metadata around the database and retrieves DatabaseMetaData object.
ResultSet:
The ResultSet is an interface that provides getter methods (getBoolean, getString, getTable and so on) for retrieving values. A ResultSet object is by default not updatable and forward only.
getTables(null, null, "%", types):
This method returns ResultSet and takes the following string types parameter:

catalog : Table catalog name (may be null)
schemaPattern : Table catalog name (may be null)
tableNamePattern : Table name("%")
types : Table type
rs.next():
This method returns the next element of the ResultSet object.
getString("Table name"):
Above method retrieves the values from ResultSet object. It takes string type value.
Here is the code of program:
import java.sql.*;

public class AllTableName{
public static void main(String[] args) {
System.out.println("Listing all table name in Database!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
try{
DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbm.getTables(null,null,"%",types);
System.out.println("Table name:");
while (rs.next()){
String table = rs.getString("TABLE_NAME");
System.out.println(table);
con.close();
}
}
catch (SQLException s){
System.out.println("No any table in the database");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Output of program:
C:\vinod\jdbc\jdbc-mysql>javac AllTableName.java
C:\vinod\jdbc\jdbc-mysql>java AllTableName
Listing all table name in Database!
Table name:
employee
employee11
employee2
employee3
employee4
employee5
employee6
employee8
employee9
java_datatypes
java_datatypes2
javatypes

Creating a Database in MySQL

After establishing the connection with MySQL database by using the JDBC driver, you will learn how we can create our database. A database is a large collection of data or information stored in our computer in an arranged way. It helps us for accessing, managing and updating the data easily. In this example we are going to create a database by MySQL and with the help of some java methods and SQL statement. A RDBMS (Relational Database Management System) is a type of DBMS (Database Management System) which stores the data in the form of tables. So, we can view and use the same database in many different ways.
Description of program:
Firstly this program establishes the connection with MySQL database and takes a database name as its input in the database query and only after that it will create a new database and show a message "1 row(s) affected" otherwise, it displays "SQL statement is not executed!".
Description of code:
CREATE DATABASE db_name;
Above code is used for creating a new database. It takes a database name and then a new database is created by that name.
Here is the code of program:
import java.io.*;
import java.sql.*;

public class CreateDatabase{
public static void main(String[] args) {
System.out.println("Database creation example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader
(new InputStreamReader(System.in));
System.out.println("Enter Database name:");
String database = bf.readLine();
st.executeUpdate("CREATE DATABASE "+database);
System.out.println("1 row(s) affacted");
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Output of program:
C:\vinod\jdbc\jdbc\jdbc-mysql>javac CreateDatabase.java

C:\vinod\jdbc\jdbc\jdbc-mysql>java CreateDatabase
Database creation example!
Enter Database name:
RoseIndia
1 row(s) affacted
Creating a Database Table
Database: A database is a large collection of data or information to stored in our computer in an arranged way. It helps us for accessing, managing and updating the data easily. In this example we are using MySQL database, which is a RDBMS. A RDBMS (Relational Database Management System) is a type of DBMS (Database Management System) which stores the data in the form of tables. RDBMS is very powerful as it doesn't need to aware how the data is related or how it is going to be extracted from the database. So, we can view the same database in many different ways.
Table: A table is basic component of database (DB) that has number of rows and columns. All tables are stored in a specific database.
Here we are providing you an example with code and it's description that helps you to create a database table by using java file. Brief description given below:
Description of program:
Firstly in this program we are going to establish the connection with database and creating a table with some fields. If table name already exists then we are displaying the message "Table already exists!".
Description of code:
Statement:
It is a interface. Statement object executes the SQL statement and returns the result it produces.
createStatement():
It is a method of Connection interface. which returns Statement object. This method will compile again and again whenever the program runs.
CREATE TABLE table_name(field_name):
An appropriate code used for creating a table with given field name.
executeUpdate(String table):
This method also executes SQL statement that may be INSERT, UPDATE OR DELETE statement are used in the code. It takes string types parameters for SQL statement. It returns int.
Here is the code of program:
import java.sql.*;

public class CreateTable{
public static void main(String[] args) {
System.out.println("Table Creation Example!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbctutorial";
String driverName = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try{
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(url+dbName, userName, password);
try{
Statement st = con.createStatement();
String table = "CREATE TABLE Employee11(Emp_code integer, Emp_name varchar(10))";
st.executeUpdate(table);
System.out.println("Table creation process successfully!");
}
catch(SQLException s){
System.out.println("Table all ready exists!");
}
con.close();
}
catch (Exception e){
e.printStackTrace();
}
}
}

Creating a MySQL Database Table to store Java Types
Dear user, consider a case where we need to store a java types in our database table. Now one question may arise in your mind that whether the MySQL supports java types or not. This section describes how to create a table in MySQL database that stores all java types. Here we are providing you an example with code for creating a table to store java types. Brief description is given below:
Description of program:
In this program we are going to establish the connection between MySQL database table and java file. After the connection has been established creates a database table for storing all java types. We have used most of the java types provided to us by the jdbc.
Here is the code of program:
import java.sql.*;

public class CreateMySqlTable{

public static void main(String[] args) {
System.out.println("Creating a Mysql Table to Store Java Types!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db, user, pass);
try{
Statement st = con.createStatement();
<<<<<<< CreateMySqlTable.shtml String table = "CREATE TABLE java_DataTypes2 ("+ "typ_boolean BOOL, " ======= String table = "CREATE TABLE java_DataTypes2("+ "typ_boolean BOOL, " >>>>>>> 1.7 + "typ_byte TINYINT, "
+ "typ_short SMALLINT, "
+ "typ_int INTEGER, "
+ "typ_long BIGINT, "
+ "typ_float FLOAT, "
+ "typ_double DOUBLE PRECISION, "
+ "typ_bigdecimal DECIMAL(13,0), "
+ "typ_string VARCHAR(254), "
+ "typ_date DATE, "
+ "typ_time TIME, "
+ "typ_timestamp TIMESTAMP, "
+ "typ_asciistream TEXT, "
+ "typ_binarystream LONGBLOB, "
+ "typ_blob BLOB)";

st.executeUpdate(table);
System.out.println(table);

con.close();
}
catch (SQLException s){
System.out.println
("Table is all ready exists!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

JDBC Example with MySQL

Mapping MySQL Data Types in Java
Data types of MySQL and Java programming language are not same, its need some mechanism for transferring data between an database using MySQL data types and a application using Java data types. We need to provide Java mappings for the common MySQL data types. We have to confirm that we have proper type information then only we can correctly store and retrieve parameters and recover results from MySQL statements.
There is no particular reason that the Java data type needs to be exactly isomorphic to the MySQL data type. For example, Java String don't precisely match any of the MySQL data CHAR type, but it gives enough type information to represent CHAR, VARCHAR or LONGVARCHAR successfully.
The following table represent the default Java mapping for various common MySQL data types:
MySQL Type Java Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte []
VARBINARY byte []
LONGVARBINARY byte []
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Tiimestamp
1. CHAR, VARCHAR and LONGVARCHAR
MySQL data types CHAR, VARCHAR, LONGVARCHAR are closely related. CHAR represents a small, fixed-length character string, VARCHAR represents a small, variable-length character string, and LONGVARCHAR represents a large, variable-length character string. There is no need for Java programmer to distinguish these three MySQL data types. These can be expressed identically in Java. These data types could be mapped in Java to either String or char[]. But String seemed more appropriate type for normal use. Java String class provide a method to convert a String into char[] and a constructor for converting a char[] into a String.

The method ResultSet.getString allocates and returns a new String. It is suitable for retrieving data from CHAR, VARCHAR and LONGVARCHAR fields. This is suitable for retrieving normal data, but LONGVARCHAR MySQL type can be used to store multi-megabyte strings. So that Java programmers needs a way to retrieve the LONGVARCHAR value in chunks. To handle this situation, ResultSet interface have two methods for allowing programmers to retrieve a LONGVARCHAR value as a Java input stream from which they can subsequently read data in whatever size chunks they prefer. These methods are getAsciiStream and getCharacterStream, which deliver the data stored in a LONGVARCHAR column as a stream of ASCII or Unicode characters.

2. NUMERIC and DECIMAL
The NUMERIC and DECIMAL MySQL data types are very similar. They both represent fixed point numbers where absolute precision is required. The most convenient Java mapping for these MySQL data type is java.math.BigDecimal. This Java type provides math operations to allow BigDecimal types to be added, subtracted, multiplied, and divided with other BigDecimal types, with integer types, and with floating point types.

We also allow access to these MySQL types as simple Strings and char []. Thus, the Java programmers can use the getString() to retrieve the NUMERICAL and DECIMAL results.

3. BINARY, VARBINARY and LONGVARBINARY
These MySQL data types are closely related. BINARY represents a small, fixed-length binary value, VARBINARY represents a small, variable-length binary value and LONGVARBINARY represents a large, variable-length binary value. For Java programers there is no need to distinguish among these data types and they can all be expressed identically as byte arrays in Java. It is possible to read and write SQL statements correctly without knowing the exact BINARY data type. The ResultSet.getBytes method is used for retrieving the DECIMAL and NUMERICAL values. Same as LONGVARCHAR type, LONGVARBINARY type can also be used to return multi-megabyte data values then the method getBinaryStream is recommended.

4. BIT
The MySQL type BIT represents a single bit value that can be 'zero' or 'one'. And this MySQL type can be mapped directly to the Java boolean type.

5. TINYINT, SMALLINT, INTEGER and BIGINT
The MySQL TINYINT type represents an 8-bit integer value between 0 and 255 that may be signed or unsigned. SMALLINT type represents a 16-bit signed integer value between -32768 and 32767. INTEGER type represents a 32-bit signed integer value between -2147483648 and 2147483647. BIGINT type represents an 64-bit signed integer value between -9223372036854775808 and 9223372036854775807. These MySQL TINYINT, SMALLINT, INTEGER, and BIGINT types can be mapped to Java's byte, short, int and long data types respectively.

6. REAL, FLOAT and DOUBLE
The MySQL REAL represents a "single precision" floating point number that supports seven digits of mantissa and the FLOAT and DOUBLE type represents a "double precision" floating point number that supports 15 digits of mantissa. The recommended Java mapping for REAL type to Java float and FLOAT, DOUBLE type to Java double.

7. DATE, TIME and TIMESTAMP
These three MySQL types are related to time. The DATE type represents a date consisting of day, month, and year, the TIME type represents a time consisting of hours, minutes, and seconds and the TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The standard Java class java.util.Date that provides date and time information but does not match any of these three MySQL date/time types exactly, because it has DATE and TIME information but no nanoseconds.

That's why we define three subclasses of java.util.Date. These are:

• java.sql.Date for SQL DATE information.
• java.sql.Time for SQL TIME information.
• java.sql.Timestamp for SQL TIMESTAMP information.
Connecting to a MySQL Database in Java
In java we have been provided with some classes and APIs with which we can make use of the database as we like. Database plays as very important role in the programming because we have to store the values somewhere in the back- end. So, we should know how we can manipulate the data in the database with the help of java, instead of going to database for a manipulation. We have many database provided like Oracle, MySQL etc. We are using MySQL for developing this application.
In this section, you will learn how to connect the MySQL database with the Java file. Firstly, we need to establish a connection between MySQL and Java files with the help of MySQL driver . Now we will make our account in MySQL database so that we can get connected to the database. After establishing a connection we can access or retrieve data form MySQL database. We are going to make a program on connecting to a MySQL database, after going through this program you will be able to establish a connection on your own PC.
Description of program:
This program establishes the connection between MySQL database and java files with the help of various types of APIs interfaces and methods. If connection is established then it shows "Connected to the database" otherwise it will displays a message "Disconnected from database".
Description of code:
Connection:
This is an interface in java.sql package that specifies connection with specific database like: MySQL, Ms-Access, Oracle etc and java files. The SQL statements are executed within the context of the Connection interface.
Class.forName(String driver):
This method is static. It attempts to load the class and returns class instance and takes string type value (driver) after that matches class with given string.
DriverManager:
It is a class of java.sql package that controls a set of JDBC drivers. Each driver has to be register with this class.
getConnection(String url, String userName, String password):
This method establishes a connection to specified database url. It takes three string types of arguments like:
url: - Database url where stored or created your database
userName: - User name of MySQL
password: -Password of MySQL
con.close():
This method is used for disconnecting the connection. It frees all the resources occupied by the database.
printStackTrace():
The method is used to show error messages. If the connection is not established then exception is thrown and print the message.
Here is the code of program:
import java.sql.*;

public class MysqlConnect{
public static void main(String[] args) {
System.out.println("MySQL Connect Example.");
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
System.out.println("Connected to the database");
conn.close();
System.out.println("Disconnected from database");
} catch (Exception e) {
e.printStackTrace();
}
}
}

Understanding Connection Object

A Connection object represents a connection with a database. When we connect to a database by using connection method, we create a Connection Object, which represents the connection to the database. An application may have one or more than one connections with a single database or many connections with the different databases also.
We can use the Connection object for the following things:
1). It creates the Statement, PreparedStatement and CallableStatement objects for executing the SQL statements.
2). It helps us to Commit or roll back a jdbc transactionn.
3). If you want to know about the database or data source to which you are connected then the Connection object gathers information about the database or data source by the use of DatabaseMetaData.
4). It helps us to close the data source. The Connection.isClosed() method returns true only if the Connection.close() has been called. This method is used to close all the connection.
Firstly we need to to establish the connection with the database. This is done by using the method DriverManager.getConnection(). This method takes a string containing a URL. The DriverManager class, attempts to locate a driver that can connect to the database represented by the string URL. Whenever the getConnection() method is called the DriverManager class checks the list of all registered Driver classes that can connect to the database specified in the URL.
Syntax:

String url = "jdbc: odbc: makeConnection";
Connection con = DriverManager.getConnection(url, "userID", "password");

Driver Manager Class

The JDBC Driver Manager

The JDBC Driver Manager is a very important class that defines objects which connect Java applications to a JDBC driver. Usually Driver Manager is the backbone of the JDBC architecture. It's very simple and small that is used to provide a means of managing the different types of JDBC database driver running on an application. The main responsibility of JDBC database driver is to load all the drivers found in the system properly as well as to select the most appropriate driver from opening a connection to a database. The Driver Manager also helps to select the most appropriate driver from the previously loaded drivers when a new open database is connected.
The DriverManager class works between the user and the drivers. The task of the DriverManager class is to keep track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. It even keeps track of the driver login time limits and printing of log and tracing messages. This class is mainly useful for the simple application, the most frequently used method of this class is DriverManager.getConnetion(). We can know by the name of the method that this method establishes a connection to a database.
The DriverManager class maintains the list of the Driver classes. Each driver has to be get registered in the DriverManager class by calling the method DriverManager.registerDriver().
By calling the Class.forName() method the driver class get automatically loaded. The driver is loaded by calling the Class.forName() method. JDBC drivers are designed to tell the DriverManager about themselves automatically when their driver implementation class get loads.
This class has many methods. Some of the commonly used methods are given below:
1. deregisterDriver(Driver driver) : It drops the driver from the list of drivers registered in the DriverManager class.
2. registerDriver(Driver driver) : It registers the driver with the DriverManager class.
3. getConnection(String url) : It tries to establish the connection to a given database URL.
4. getConnection(String url, Sting user, String password) : It tries to establish the connection to a given database URL.
5. getConnection(String url, Properties info) : It tries to establish the connection to a given database URL.
6. getDriver(String url) : It attempts to locate the driver by the given string.
7. getDrivers() : It retrieves the enumeration of the drivers which has been registered with the DriverManager class.
Understanding Data Source
The JDBC API provides the DataSource interface as an alternative to the DriverManager for establishing the connection. A DataSource object is the representation of database or the data source in the Java programming language. DataSouce object is mostly preferred over the DriverManager for establishing a connection to the database.

DataSource object can be thought as a factory for making connections to the particular database that the DataSource instance represents.
DataSource has a set of properties that identify and describe the real world data source that it represents. The properties include information about the location of the database server, the network protocol use to communicate with the server the name of the database and so on.

DataSource object works with JNDI (Java Naming and Directory interface) naming service so application can use the JNDI API to access the DataSource object.
In short we can say that the DataSource interface is implemented to provide three kinds of connections:
1). Basic DataSource class
This class is provided by the driver vendor. It is used for portability and easy maintence.
2). To provide connection pooling.
It is provided by the application server vendor or driver vendor. It works with ConnectionPoolDataSource class provided by a driver vendor. Its advantage is portability, easy maintenence and increased performance.
3). To provide distributed transactions
This class works with an XADataSource class, which is provided by the driver vendor. Its advantages are easy maintenence, portability and ability to participate in distributed transactions.

Introduction to java.sql package

This package provides the APIs for accessing and processing data which is stored in the database especially relational database by using the java programming language. It includes a framework where we different drivers can be installed dynamically to access different databases especially relational databases.
This java.sql package contains API for the following :
1 Making a connection with a database with the help of DriverManager class

a) DriverManager class: It helps to make a connection with the driver.
b) SQLPermission class: It provides a permission when the code is running within a Security Manager, such as an applet. It attempts to set up a logging stream through the DriverManager class.
c) Driver interface : This interface is mainly used by the DriverManager class for registering and connecting drivers based on JDBC technology.
d). DriverPropertyInfo class : This class is generally not used by the general user.
2). Sending SQL Parameters to a database :

a). Statement interface: It is used to send basic SQL statements.
b). PreparedStatement interface: It is used to send prepared statements or derived SQL statements from the Statement object.
c). CallableStatement interface : This interface is used to call database stored procedures.
d). Connection interface : It provides methods for creating statements and managing their connections and properties.
e). Savepoint : It helps to make the savepoints in a transaction.

3). Updating and retrieving the results of a query:

a). ResultSet interface: This object maintains a cursor pointing to its current row of data. The cursor is initially positioned before the first row. The next method of the resultset interface moves the cursor to the next row and it will return false if there are no more rows in the ResultSet object. By default ResultSet object is not updatable and has a cursor that moves forward only.
4.) Providing Standard mappings for SQL types to classes and interfaces in Java Programming language.

a). Array interface: It provides the mapping for SQL Array.
b). Blob interface : It provides the mapping for SQL Blob.
c). Clob interface: It provides the mapping for SQL Clob.
d). Date class: It provides the mapping for SQL Date.
e). Ref interface: It provides the mapping for SQL Ref.
f). Struct interface: It provides the mapping for SQL Struct.
g). Time class: It provides the mapping for SQL Time.
h). Timestamp: It provides the mapping for SQL Timestamp.
i). Types: It provides the mapping for SQL types.
5). Metadata
a). DatabaseMetaData interface: It keeps the data about the data. It provides information about the database.
b). ResultSetMetaData: It gives the information about the columns of a ResultSet object.
c). ParameterMetaData: It gives the information about the parameters to the PreparedStatement commands.
6). Exceptions
a). SQLException: It is thrown by the mehods whenever there is a problem while accessing the data or any other things.
b). SQLWarning: This exception is thrown to indicate the warning.
c). BatchUpdateException: This exception is thrown to indicate that all commands in a batch update are not executed successfully.
d). DataTruncation: It is thrown to indicate that the data may have been truncated.
7). Custom mapping an SQL user- defined type (UDT) to a class in the java programming language.
a). SQLData interface: It gives the mapping of a UDT to an intance of this class.
b). SQLInput interface: It gives the methods for reading UDT attributes from a stream.
c). SQLOutput: It gives the methods for writing UDT attributes back to a stream.

Understanding Common SQL statements

The commonly used SQL statements are:
1): Select
2): Insert
3): Update
4): Delete
SQL Select statement:
The SELECT statement is used to select data from a table.

Syntax: Select column_names FROM table_name;
The result from a SQL query is stored in a resultset. The SELECT statement has mainly three clauses.
1). Select
2.) From
3). Where
The Select specifies the table columns that are retrieved. The From clause tells from where the tables has been accessed. The Where clause specifies which tables are used. The Where clause is optional, if not used then all the table rows will be selected.
We can see that we have used semicolon at the end of the select statement. It is used to separate each SQL statement in database systems which helps us to execute more than one SQL statement in the same call to the server.
SQL INSERT Statement:
This statement allows you to insert a single or multiple records into the database. We can specify the name of the column in which we want to insert the data.
Syntax: Insert into table_name values (value1, value2..);
The Insert statement has mainly three clauses.
1). Insert: It specifies which table column has to be inserted in the table.
2). Into : It tells in which the data will be stored.
3). Values: In this we insert the values we have to insert.
We can also specify the columns for which we want to insert data.
The UPDATE Statement:
The Update statement is used to modify the data in the table. Whenever we want to update or delete a row then we use the Update statement.
The syntax is :
UPDATE table_name Set colunm_name = new_value WHERE column_name = some_name;
The Update statement has mainly three clauses.
1). UPDATE: It specifies which table column has to be updated.
2). Set: It sets the column in which the data has to be updated.
3). Where: It tells which tables are used.
SQL DELETE Statement:
This delete statement is used to delete rows in a table.
Systax:
DELETE FROM table_name WHERE column_name = some_name;
The Delete statement has following clauses.
1). Delete: It specifies which table column has to be deleted.
2). From: It tells from where the Table has been accessed.
3). Where: It tells which tables are used.

Relational Database Concepts

An important part of every business is to keep records. We need to keep records of our customers, the employees of our company, the emails etc. To keep all the data indivually is quite difficult and hectic job, because whenever if we need the record of a particular customer or an employee we need to search manually. It takes lot of time and still not reliable. Here comes the concept of databases.
What is database?
A database is an organized collection of information. A simple example of a database are like your telephone directory, recipe book etc.
A Relational model is the basis for any relational database management system (RDBMS). A relational model has mainly three components:
1) A collection of objects or relations,.
2) Operators that act on the objects or relations.
3) Data integrity methods.
To design a database we need three things:
1). Table
2). Rows
3). Columns
A table is one of the most important ingredient to design the database. It is also known as a relation, is a two dimensional structure used to hold related information. A database consists of one or more tables.

A table contains rows : Rows is a collection of instance of one thing, such as the information of one employee.

A table contains the columns: Columns contains all the information of a single type. Each column in a table is a category of information referred to as a field.
One item of data, such as single phone number of a person is called as a Data Value.
ACID Properties:
ACID properties are one of the important concept for databases. ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties of a DBMS allow safe sharing of data. Without these properties the inaccuracy in the data will be huge. With the help of the ACID properties the accuracy can be maintained.
Normalization:
Normalization is a design technique which helps the to design the relational databases. Normalization is essentially a two step process that puts data into tabular form by removing redundant data from the relational tables. A basic goal of normalization is to create a set of relational tables that are free of redundant data and the data should be consistent. Normalization has been divided into following forms.
!). First Normal Form: A relational table, by definition are in first normal form. All values of the columns are atomic. It means that it contains no repeating values.
2). A relationl table is in second normal form if it is in 1NF and every non- key column is fully dependent upon the primary key.
3). A relational table is in third normal form (3NF) if it is already in 2NF and every non- key column is non transitively dependent upon its primary key. The advantage of having table in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies.

JDBC Versions

1). The JDBC 1.0 API.
2). The JDBC 1.2 API.
3). The JDBC 2.0 Optional Package API.
4). The JDBC 2.1 core API.
5) The JDBC 3.0 API.
6) The JDBC 4.0 API.
Features of JDBC 1.0 API
The JDBC 1.0 API was the first officially JDBC API launched consists of the following java classes and interfaces that you can open connections to particular databases.
This version includes a completely redesigned administration console with an enhanced graphical interface to manage and monitor distributed virtual databases.
Features of JDBC 1.2 API
1). It supports Updatabale ResultSets.
2). The DatabaseMetaData code has been refactored to provide more transparency with regard to the underlying database engine.
3) New pass through schedulers for increased performance.

Features of The JDBC 2.0 Optional Pacakage API
1). The use of DataSource interface for making a connection.
2). Use of JNDI to specify and obtain database connections.
3). It allows us to use Pooled connections, that is we can reuse the connections.
4). In this version the distrbuted transactions is possible.
5). It provides a way of handling and passing data using Rowset technology.
Features of the JDBC 2.1 core API.
1). Scroll forward and backward in a result set or has the ability to move to a specific row.
2). Instead of using SQL commands, we can make updates to a database tables using methods in the Java programming language
3). We can use multiple SQL statements in a a database as a unit, or batch.
4). It uses the SQL3 datatypes as column values. SQL3 types are Blob, Clob, Array, Structured type, Ref.
5). Increased support for storing persistent objects in the java programming language.
6). Supports for time zones in Date, Time, and Timestamp values.
7). Full precision for java.math.BigDecimal values.
Features of JDBC 3.0 API
1). Reusabilty of prepared statements by connection pools.
2). In this version there is number of properties defined for the ConnectionPoolDataSource. These properties can be used to describe how the PooledConnection objects created by DataSource objects should be pooled.
3) A new concept has been added to this API is of savepoints.
4). Retrieval of parameter metadata.
5). It has added a means of retrieving values from columns containing automatically generated values.
6). Added a new data type i.e. java.sql.BOOLEAN.
7). Passing parameters to CallableStatement.
8). The data in the Blob and Clob can be altered.
9). DatabaseMetaData API has been added.
Features of JDBC 4.0 :
1). Auto- loading of JDBC driver class.
2). Connection management enhancements.
3.) Support for RowId SAL type.
4). SQL exception handling enhancements.
5). DataSet implementation of SQl using Annotations.
6). SQL XML support
Features of JDBC 3.0 API
1. Reusabilty of prepared statements by connection pools.
2. In this version there is number of properties defined for the ConnectionPoolDataSource. These properties can be used to describe how the PooledConnection objects created by DataSource objects should be pooled.
3. A new concept has been added to this API is of savepoints: One of the useful new features is transactional savepoints. With JDBC 3.0, the transactional model is now more flexible. Now you can start a transaction , insert several rows in it and then create a savepoint. This savepoint serves as a bookmark. The application can rollback to the savepoint and then commit the group of inserts as if the updates have never been attempted. For eg:

Statement st=connection.createStatement();
int rowcount=st.executeUpdate("insert into employee values("tim"));
int rowcoutn=st.executeUpdate("insert into salary values(20000.0);
Savepoint sv=connection.setSavePoint("savepoint"); //create save point for inserts
int rowcount=st.executeUpdate("delete from employee");
connection.rollback(sv); //discard the delete statement but keeps the inserts
connection.commit(); //inserts are now permanent
4. Retrieval of parameter metadata.
5. It has added a means of retrieving values from columns containing automatically generated values.
6. Added a new data type i.e. java.sql.BOOLEAN.
7. Passing parameters to CallableStatement.
8. The data in the Blob and Clob can be altered: JDBC 3.0 introduces a standard mechanism for updating BLOB and CLOB data.
9. DatabaseMetaData API has been added.
10. It allows stored procedure parameters to be called by name.
Features of JDBC 4.0
JDBC 4 is a major new release with a strong focus ease-of-use and programmer productivity. JDBC 4's key priority is to make it easier for developers to work directly with the JDBC API. The new features availble in J2SE 1.5 added ease-of-development features in JDBC 4 In addition to ease-of-use, JDBC 4 introduces several SQL 203 features, like Java specific mapping for the new XML SQL type. Now in JDBC 4 every PreparedStatement is poolable by default.
1. Auto- loading of JDBC driver class: In JDBC 4 invoking the getConnection() on DriverManager will automatically load a driver. Upon loading the driver, an instance of the driver is created and the registerDriver() method is invoked to make that driver available to clients.
2. Connection management enhancements: In jdbc it may happen that a Connection is lying idle or not closed in a pool, then it became stale over time. This will led to the connection pool run out of resources due to stale connection. We know that the stale connection doesn't mean that a connection is closed. Now in JDBC 4 Connection class we have provided one method isValid(), which allows a client to query the database driver if a connection is still valid. As database is a shared resource, so many application can access the same data store. To maintain all the records of the client is a difficult task, now in JDBC 4 an application can associate metadata with a database connection via the setClientInfo() method, which takes a name/value pair, or a Properties object.
3. Support for RowId data type: JDBC introduces support for ROWID, a data type that had been in use in database products even before it became part of the SQL.
4. SQL exception handling enhancements: JDBC 4 addresses the error handling beautifully. As databases are often remotely accessible resources, problems such as network failures is common and it can cause exceptions when executing a database operation. SQL statements can also cause exceptions. Prior to JDBC 4, most JDBC operations generated a simple SQLException.
5. SQL XML support:

6. DataSet implementation of SQL using Annotations: The JDBC 4.0 specification leverages annotations to allow developers to associate a SQL query with a Java class without a need to write a lot of code to achieve this association.
Difference between JDBC 3.0 & JDBC 4.0
Features of JDBC 3.0
1. Reusabilty of prepared statements by connection pools.
2. In this version there is number of properties defined for the ConnectionPoolDataSource. These properties can be used to describe how the PooledConnection objects created by DataSource objects should be pooled.
3. A new concept has been added to this API is of savepoints: One of the useful new features is transactional savepoints. With JDBC 3.0, the transactional model is now more flexible. Now you can start a transaction , insert several rows in it and then create a savepoint. This savepoint serves as a bookmark. The application can rollback to the savepoint and then commit the group of inserts as if the updates have never been attempted. For eg:

Statement st=connection.createStatement();
int rowcount=st.executeUpdate("insert into employee values("tim"));
int rowcoutn=st.executeUpdate("insert into salary values(20000.0);
Savepoint sv=connection.setSavePoint("savepoint"); //create save point for inserts
int rowcount=st.executeUpdate("delete from employee");
connection.rollback(sv); //discard the delete statement but keeps the inserts
connection.commit(); //inserts are now permanent
4. Retrieval of parameter metadata.
5. It has added a means of retrieving values from columns containing automatically generated values.
6. Added a new data type i.e. java.sql.BOOLEAN.
7. Passing parameters to CallableStatement.
8. The data in the Blob and Clob can be altered: JDBC 3.0 introduces a standard mechanism for updating BLOB and CLOB data.
9. DatabaseMetaData API has been added.
10. It allows stored procedure parameters to be called by name.
Features of JDBC 4
JDBC 4 is a major new release with a strong focus ease-of-use and programmer productivity. JDBC 4's key priority is to make it easier for developers to work directly with the JDBC API. The new features availble in J2SE 1.5 added ease-of-development features in JDBC 4
In addition to ease-of-use, JDBC 4 introduces several SQL 203 features, like Java specific mapping for the new XML SQL type. Now in JDBC 4 every PreparedStatement is poolable by default.
1. Auto- loading of JDBC driver class: In JDBC 4 invoking the getConnection() on DriverManager will automatically load a driver. Upon loading the driver, an instance of the driver is created and the registerDriver() method is invoked to make that driver available to clients.
2. Connection management enhancements: In jdbc it may happen that a Connection is lying idle or not closed in a pool, then it became stale over time. This will led to the connection pool run out of resources due to stale connection. We know that the stale connection doesn't mean that a connection is closed. Now in JDBC 4 Connection class we have provided one method isValid(), which allows a client to query the database driver if a connection is still valid. As database is a shared resource, so many application can access the same data store. To maintain all the records of the client is a difficult task, now in JDBC 4 an application can associate metadata with a database connection via the setClientInfo() method, which takes a name/value pair, or a Properties object.
3. Support for RowId data type: JDBC introduces support for ROWID, a data type that had been in use in database products even before it became part of the SQL.
4. SQL exception handling enhancements: JDBC 4 addresses the error handling beautifully. As databases are often remotely accessible resources, problems such as network failures is common and it can cause exceptions when executing a database operation. SQL statements can also cause exceptions. Prior to JDBC 4, most JDBC operations generated a simple SQLException.
5. SQL XML support:
6. DataSet implementation of SQL using Annotations: The JDBC 4.0 specification leverages annotations to allow developers to associate a SQL query with a Java class without a need to write a lot of code to achieve this association.

JDBC Driver and Its Types

A JDBC driver translates standard JDBC calls into a network or database protocol or into a database library API call that facilitates communication with the database. This translation layer provides JDBC applications with database independence. If the back-end database changes, only the JDBC driver need be replaced with few code modifications required. There are four distinct types of JDBC drivers.

Type 1 JDBC-ODBC Bridge. Type 1 drivers act as a "bridge" between JDBC and another database connectivity mechanism such as ODBC. The JDBC- ODBC bridge provides JDBC access using most standard ODBC drivers. This driver is included in the Java 2 SDK within the sun.jdbc.odbc package. In this driver the java statements are converted to a jdbc statements. JDBC statements calls the ODBC by using the JDBC-ODBC Bridge. And finally the query is executed by the database. This driver has serious limitation for many applications. (See Figure 2.)

Type 1 JDBC Architecture


Type 2 Java to Native API. Type 2 drivers use the Java Native Interface (JNI) to make calls to a local database library API. This driver converts the JDBC calls into a database specific call for databases such as SQL, ORACLE etc. This driver communicates directly with the database server. It requires some native code to connect to the database. Type 2 drivers are usually faster than Type 1 drivers. Like Type 1 drivers, Type 2 drivers require native database client libraries to be installed and configured on the client machine. (See Figure 3.)

Type 2 JDBC Architecture


Type 3 Java to Network Protocol Or All- Java Driver. Type 3 drivers are pure Java drivers that use a proprietary network protocol to communicate with JDBC middleware on the server. The middleware then translates the network protocol to database-specific function calls. Type 3 drivers are the most flexible JDBC solution because they do not require native database libraries on the client and can connect to many different databases on the back end. Type 3 drivers can be deployed over the Internet without client installation. (See Figure 4.)
Java-------> JDBC statements------> SQL statements ------> databases.

Type 3 JDBC Architecture


Type 4 Java to Database Protocol. Type 4 drivers are pure Java drivers that implement a proprietary database protocol (like Oracle's SQL*Net) to communicate directly with the database. Like Type 3 drivers, they do not require native database libraries and can be deployed over the Internet without client installation. One drawback to Type 4 drivers is that they are database specific. Unlike Type 3 drivers, if your back-end database changes, you may save to purchase and deploy a new Type 4 driver (some Type 4 drivers are available free of charge from the database manufacturer). However, because Type drivers communicate directly with the database engine rather than through middleware or a native library, they are usually the fastest JDBC drivers available. This driver directly converts the java statements to SQL statements.
(See Figure 5.)

Type 4 JDBC Architecture


So, you may be asking yourself, "Which is the right type of driver for your application?" Well, that depends on the requirements of your particular project. If you do not have the opportunity or inclination to install and configure software on each client, you can rule out Type 1 and Type 2 drivers.

However, if the cost of Type 3 or Type 4 drivers is prohibitive, Type 1 and type 2 drivers may become more attractive because they are usually available free of charge. Price aside, the debate will often boil down to whether to use Type 3 or Type 4 driver for a particular application. In this case, you may need to weigh the benefits of flexibility and interoperability against performance. Type 3 drivers offer your application the ability to transparently access different types of databases, while Type 4 drivers usually exhibit better performance and, like Type 1 and Type 2 drivers, may be available free if charge from the database manufacturer.

Understanding the JDBC Architecture

JDBC is an API specification developed by Sun Microsystems that defines a uniform interface for accessing various relational databases. JDBC is a core part of the Java platform and is included in the standard JDK distribution.

The primary function of the JDBC API is to provide a means for the developer to issue SQL statements and process the results in a consistent, database-independent manner. JDBC provides rich, object-oriented access to databases by defining classes and interfaces that represent objects such as:
1. Database connections
2. SQL statements
3. Result Set
4. Database metadata
5. Prepared statements
6. Binary Large Objects (BLOBs)
7. Character Large Objects (CLOBs)
8. Callable statements
9. Database drivers
10. Driver manager
The JDBC API uses a Driver Manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The Driver Manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. The location of the driver manager with respect to the JDBC drivers and the servlet is shown in Figure 1.

A JDBC driver translates standard JDBC calls into a network or database protocol or into a database library API call that facilitates communication with the database. This translation layer provides JDBC applications with database independence. If the back-end database changes, only the JDBC driver need be replaced with few code modifications required. There are four distinct types of JDBC drivers.

Type 1 JDBC-ODBC Bridge. Type 1 drivers act as a "bridge" between JDBC and another database connectivity mechanism such as ODBC. The JDBC- ODBC bridge provides JDBC access using most standard ODBC drivers. This driver is included in the Java 2 SDK within the sun.jdbc.odbc package. In this driver the java statements are converted to a jdbc statements. JDBC statements calls the ODBC by using the JDBC-ODBC Bridge. And finally the query is executed by the database. This driver has serious limitation for many applications. (See Figure 2.)

Type 1 JDBC Architecture


Type 2 Java to Native API. Type 2 drivers use the Java Native Interface (JNI) to make calls to a local database library API. This driver converts the JDBC calls into a database specific call for databases such as SQL, ORACLE etc. This driver communicates directly with the database server. It requires some native code to connect to the database. Type 2 drivers are usually faster than Type 1 drivers. Like Type 1 drivers, Type 2 drivers require native database client libraries to be installed and configured on the client machine. (See Figure 3.)

Type 2 JDBC Architecture


Type 3 Java to Network Protocol Or All- Java Driver. Type 3 drivers are pure Java drivers that use a proprietary network protocol to communicate with JDBC middleware on the server. The middleware then translates the network protocol to database-specific function calls. Type 3 drivers are the most flexible JDBC solution because they do not require native database libraries on the client and can connect to many different databases on the back end. Type 3 drivers can be deployed over the Internet without client installation. (See Figure 4.)
Java-------> JDBC statements------> SQL statements ------> databases.

Type 3 JDBC Architecture


Type 4 Java to Database Protocol. Type 4 drivers are pure Java drivers that implement a proprietary database protocol (like Oracle's SQL*Net) to communicate directly with the database. Like Type 3 drivers, they do not require native database libraries and can be deployed over the Internet without client installation. One drawback to Type 4 drivers is that they are database specific. Unlike Type 3 drivers, if your back-end database changes, you may save to purchase and deploy a new Type 4 driver (some Type 4 drivers are available free of charge from the database manufacturer). However, because Type drivers communicate directly with the database engine rather than through middleware or a native library, they are usually the fastest JDBC drivers available. This driver directly converts the java statements to SQL statements.
(See Figure 5.)

Type 4 JDBC Architecture


So, you may be asking yourself, "Which is the right type of driver for your application?" Well, that depends on the requirements of your particular project. If you do not have the opportunity or inclination to install and configure software on each client, you can rule out Type 1 and Type 2 drivers.

However, if the cost of Type 3 or Type 4 drivers is prohibitive, Type 1 and type 2 drivers may become more attractive because they are usually available free of charge. Price aside, the debate will often boil down to whether to use Type 3 or Type 4 driver for a particular application. In this case, you may need to weigh the benefits of flexibility and interoperability against performance. Type 3 drivers offer your application the ability to transparently access different types of databases, while Type 4 drivers usually exhibit better performance and, like Type 1 and Type 2 drivers, may be available free if charge from the database manufacturer.

Product Components of JDBC

JDBC has four Components:
1. The JDBC API.
2. The JDBC Driver Manager.
3. The JDBC Test Suite.
4. The JDBC-ODBC Bridge.
1. The JDBC API.

The JDBC application programming interface provides the facility for accessing the relational database from the Java programming language. The API technology provides the industrial standard for independently connecting Java programming language and a wide range of databases. The user not only execute the SQL statements, retrieve results, and update the data but can also access it anywhere within a network because of it's "Write Once, Run Anywhere" (WORA) capabilities.
Due to JDBC API technology, user can also access other tabular data sources like spreadsheets or flat files even in the a heterogeneous environment. JDBC application programmming interface is a part of the Java platform that have included Java Standard Edition (Java SE ) and the Java Enterprise Edition (Java EE) in itself.
The JDBC API has four main interface:
The latest version of JDBC 4.0 application programming interface is divided into two packages
i-) java.sql
ii-) javax.sql.
Java SE and Java EE platforms are included in both the packages.
2. The JDBC Driver Manager.

The JDBC Driver Manager is a very important class that defines objects which connect Java applications to a JDBC driver. Usually Driver Manager is the backbone of the JDBC architecture. It's very simple and small that is used to provide a means of managing the different types of JDBC database driver running on an application. The main responsibility of JDBC database driver is to load all the drivers found in the system properly as well as to select the most appropriate driver from opening a connection to a database. The Driver Manager also helps to select the most appropriate driver from the previously loaded drivers when a new open database is connected.
3. The JDBC Test Suite.

The function of JDBC driver test suite is to make ensure that the JDBC drivers will run user's program or not . The test suite of JDBC application program interface is very useful for testing a driver based on JDBC technology during testing period. It ensures the requirement of Java Platform Enterprise Edition (J2EE).

4. The JDBC-ODBC Bridge.

The JDBC-ODBC bridge, also known as JDBC type 1 driver is a database driver that utilize the ODBC driver to connect the database. This driver translates JDBC method calls into ODBC function calls. The Bridge implements Jdbc for any database for which an Odbc driver is available. The Bridge is always implemented as the sun.jdbc.odbc Java package and it contains a native library used to access ODBC.

Now we can conclude this topic: This first two component of JDBC, the JDBC API and the JDBC Driver Manager manages to connect to the database and then build a java program that utilizes SQL commands to communicate with any RDBMS. On the other hand, the last two components are used to communicate with ODBC or to test web application in the specialized environment.