java.sql.Connection Interface
The Connection interface is used to connect a Java application with a particular database.By default a Connection object is in auto-commit mode, which means that it automatically commit changes after executing each statement. If auto-commit mode has been disabled, the method commit() must be called explicitly in order to commit changes; otherwise database changes will not be saved.
For Example:
Connection con=null;
• The connection URL for My-Sql database:
con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/database”,”uid”,”pwd”); • The connection URL for Oracle 10G database:
con=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:xe”,”username”,”password”); In above statement, “xe” is the service name and “1512” is the port number.
Some commonly used methods of Connection interface are listed below;
| Method | Description |
|---|---|
| Void close () throws SQLException | Release a database and JDBC resources. |
| Void commit() throws SQLException | Mark all changes made since the previous commit permanent and releases any database locks currently held by the connection. |
| Statement create Statement() throws SQL-Exception | Create a Statement object for sending SQL statement to the database |
| Statement createStatement (int resultSet-Type, int resultSetConcurrency) | Create a Statement object that will generate ResultSet objects with the given type and concurrency. |
| boolean getAutoCommit() throws SQLException | Gets the current auto-commit state. |
| DatabaseMetaData getMetaData() throws SQLException | Gets the metadata regarding this connection’s database. |
| boolean isClosed() throws SQLException | Test to see if a connection is closed |
| boolean isReadOnly() throws SQLException | Test to see if the connection is in read-only mode. |
| CallableStatement prepareCall(String str) throws SQLException | Create a CallableStatement object for calling database stored procedures. |
| PreparedStatement prepareStatement(String Sql) throws SQLException | Create a PreparedStatement object for sending parameterized SQL statements to the database. |
| Void rollback() throws SQLException | Drop all changes made since the previous commit/rollback and releases any database lock currently held by this connection. |
| Void setAutoCommit(boolean) throws SQLException | Sets this connection’s auto-commit mode. |
| PreparedStatement prepareStatement (String Sql , int resultSetType , int resultSet, Concurrency ) throws SQLException. | Create Prepared Statement Object with given type and concurrency. |
Java.sql.Statement Interface
The statement interface is used for excecuting a static SQL statement and return the results it produces.For Example:
Statement stmt=con.createStatement();
Statement query=”insert into users(uid,pwd) values(‘bce’,’sst’);”
Int result =stmt.executeUpdate(query);
Some of the commonly used methods are listed below:
| Method | Description |
|---|---|
| Void addBatch(String sql) throws SQLException | Add an SQL command to the current batch of commands for this Statement object. |
| Void clearBatch() throws SQLException | Makes the set of commands in the current batch empty. |
| Void close() throws SQLException | Releases the Statement object’s database and JDBC resources. |
| Int [] executeBatch() throws SQLExecution | Submits a batch of commands to the databases for execution and if all commands execute successfully, returns an array of update counts. |
| ResultSet executeQuery(String sql) throws SQLException | Execute an SQL statement that returns a single ResultSet object. |
| Int executeUpdate(String) throws SQLException | Execute Insert, Update and Delete Queries. |
| ResultSet getResultSet() throws SQLException | Return the current result as a ResultSet object. |
Java.sql.preparedStatement Interface
Java.sql.preparedStatement interface inherits java.sql.Statement interface. This interface is used to execute dynamic queries; these are parameterized SQL statements with IN parameters. It consists of various setter methods (setInt(),setFloat(),setString() etc).to set values for IN parameters.PreparedStatement pstmt = con.preparedStatement (“UPDATE USERS SET PWD = ? WHERE UID = ?”);
Pstmt.setString(1,”bce”);
Pstmt.setString(2,”sst”);
Some commonly used methods are listed below:
| Method | Description |
|---|---|
| Void setBlob(int, Blob) throws SQLException | Sets the designated parameter to the given Blob object |
| Void setBoolean(int, boolean) throws SQLException | Sets the designated parameter to a Boolean value |
| Void setByte(int, byte) throws SQLException | Sets the designed parameter to a byte value. |
| Void setDouble(int, double) | Sets the designed parameter to a double value. |
| Void setFloat(int,float) throws SQLException | Sets the designed parameter to a float value. |
| Void setInt(int,int) throws SQLException | Sets the designed parameter to a int value. |
| Void setLong(int,long) throws SQLException | Sets the designed parameter to a long value. |
| Void setObject(int , Object) throws SQLException | Sets the value of the designed parameter using the given object. |
| Void setShort(int , short) throws SQLException | Sets the designed parameter to a Short value. |
| Void setString(int, String) throws SQLException | Sets the designed parameter to a String value. |
| Void setTime(int, Time) throws SQLException | Sets the designed parameter to a java.sql.Time value. |
Implementation
The following program demonstrates how to perform insertion, deletion, updation operation with database table using My-Sql database. But first create a database name: “COLLEGE” having table, named: STUDENTS with fields: ROLLNO,SNAME,PER & BRANCH.
import java.sql.*;
class dBase
{
Connection con;
PreparedStatement pstmt;
dBase()
{
try
{
Class.forName("com.mysql.jdbc.Driver"); //loading Mt-Sql Driver
//connecting to database
con =DriverManager.getConnection("jdbc:mysql://localhost:3306/college","root","bce");
}
catch(Exception e)
{ e.printStackTrace(); }
}
void save(int roll,String name,float per,String branch)
{
try
{
pstmt=con.prepareStatement("insert into students values(?,?,?,?)");
pstmt.setInt(1,roll);
pstmt.setString(2,name);
pstmt.setFloat(3,per);
pstmt.setString(4, branch);
pstmt.executeUpdate();
pstmt.close();
System.out.println("Saved");
}
catch(SQLException e){e.printStackTrace();}
}
void delete(int roll)
{
try
{
pstmt=con.prepareStatement("delete from students where rollno=?");
pstmt.setInt(1,roll);
pstmt.executeUpdate();
pstmt.close();
System.out.println("deleted");
}
catch(SQLException e){ e.printStackTrace(); }
}
void update(int roll,String name,float per,String branch)
{
try
{
pstmt=con.prepareStatement("update students set sname=?,per=?,branch=? where rollno=?");
pstmt.setString(1,name);
pstmt.setFloat(2,per);
pstmt.setString(3,branch);
pstmt.setInt(4,roll);
pstmt.executeUpdate();
System.out.println("Updated...");
pstmt.close();
}
catch(SQLException e){System.out.println(e);}
}
public static void main(String args[])
{
dBase m=new dBase();
m.save(1011,"Raman",80.5f,"IT");//saving new record
m.save(102,"Amir Khan",90.9f,"CSE");
m.save(103,"Manmohan Singh",77,"IT");
m.update(103,"Harpal Singh",87,"IT");// updating record
m.delete(102); //deleting record
}
}
Explanation
The program consists a constructor and three methods: save(),delete() and update(). The constructor methods loads database driver and establishes the connection with My-Sql database;save() method receives rollno , name ,percentage and branch through arguments to save them in database table; up-date() method updates data in database table; and delete() method receives rollno as argument and fires delete query to delete record from table.java.sql.ResultSet Interface
A ResultSet object holds the data in table format retrieved on execution of select query on the database. It maintains a cursor; initially positioned before the first row. The next() method moves the cursor to the next row, it returns false when no more rows are there in the ResultSet.The ResultSet interface provides many getter methods for retrieving values from the current the current row. Values can be retrieved using either the index value of the column or the name of the column.
A ResultSet object is automatically closed when the Statement object is closed.
The code written below creates a ResultSet object and holds the result retrived by the executeQuery() Method.
Scrollable ResultSets
Now, let’s take a look at the other form of prepareStatement() method provided by Connection interface.It’s first parameter consists of SQL query, second parameter sets scrollability. Scrollability refers to whether you can move backward through the ResultSet. For second parameter you can pass one of three arguments, as shown follows.
| ResultSet.Type_SCROLL_SENSITIVE | Specify that ResultSet is scrollable and sensitive to changes made by others. | |
| ResultSet.Type_SCROLL_INSENSITIVE | Specify that ResultSet is scrollable not sensitive to changes made by others. | |
| ResultSet.Type_FORWARD_ONLY | Specify that ResultSet can be updated. |
| ResultSet.CONCUR_READ_ONLY | Specify that ResultSet cannot be updated. | |
| ResultSet.CONCUR_UPDATEABLE | Specify that ResultSet can be updated. |
Available on Amazon
Under the guidance of Founder & Author of "realJavaOnline.com".
M:9872246056
