Class.forName()
or DriverManager.registerDriver()
. To check this, you can have a look into the source code of JDBC Driver classes. After this, when we call DriverManager.getConnection()
method by passing the details regarding database configuration, DriverManager will make use of registered drivers to obtain the connection and return it to the caller program.setNull()
method of PreparedStatement interface, we can set the null value to an index. The syntax of the method is given below.void setNull(int parameterIndex, int sqlType) throws SQLException
setNull()
method to bind the null variable to a parameter. The setNull method takes index and SQL Types as argument, for example :ps.setNull(10, java.sql.Types.INTEGER);
getMetaData()
method. There are so many methods in DatabaseMetaData that we can use to get the database product name, it’s version and configuration details.DatabaseMetaData metaData = con.getMetaData();
String dbProduct = metaData.getDatabaseProductName();
prepareCall()
method of the Connection interface. getMetaData()
method of ResultSet object is used to create the ResultSetMetaData object.PreparedStatement pstmntobj = conn.prepareStatement(insert_query);
ResultSet resultsetobj = pstmntobj.executeQuery(“Select * from EMPLOYEE”);
ResultSetMetaData rsmd obj= resultsetobj.getMetaData();
commit()
method in Java to commit the data. Once the SQL execution is done, we can call the commit method.connectionobj.commit();
rollback()
method in Java to rollback the data. Rollback means to undo the changes. If any of the SQL statements are failed, we can call the rollback method to undo the changes.connectionobj.rollback();
rollback()
method to undo all the changes till the savepoint.setMaxRows(int) :
Defines how many rows a resultset can contain at a timeSetFetchSize(int) :
Defines the number of rows that will be read from the database.getGeneratedKeys()
method to get the value of this auto generated key. javax.sql
package and it is more powerful that DriverManager for database connections. We can use DataSource to create the database connection and Driver implementation classes does the actual work for getting connection. Apart from getting Database connection, DataSource provides some additional features such as: java.sql
package, referred to as the JDBC core API, and the javax.sql
package, referred to as the JDBC Optional Package API. This complete JDBC API is included in the Java™ Standard Edition (Java SE™), version 7. The javax.sql package extends the functionality of the JDBC API from a client-side API to a server-side API, and it is an essential part of the Java™ Enterprise Edition (Java EE™) technology.DML
deals with the SELECT, INSERT, DELETE, UPDATE, COMMIT
and ROLLBACK
.DDL
are CREATE, ALTER
and DROP
. While most DBMS engines allow DDL to be used dynamically, it is often not supported in transactions.setTransactionIsolation ()
method . You can get information about the level of isolation used by the Connection getTransactionIsolation ()
method.java.util.Date
contains information about the date and time, whereas java.sql.Date contains information about the date, but has no information about the time. If you need to store time information in the database, it is desirable to use the Timestamp or DateTime fields.Java.util.Date
is the main universal object. It simply stores the date (as long).java.sql.Date
extends java.util.Date
and adds the following functionality :getWarnings ()
method on Connection , Statement , ResultSet objects . SQL Warnings does not stop the execution of the query, but displays warning messages for the user. getClob()
method of PreparedStatement is used to get file information from the database. Let's see the table structure of the example to retrieve the file.CREATE TABLE "FILETABLE"
( "ID" NUMBER,
"NAME" CLOB
)
import java.io.*;
import java.sql.*;
public class RetrieveFile {
public static void main(String[] args) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps=con.prepareStatement("select * from filetable");
ResultSet rs=ps.executeQuery();
rs.next();//now on 1st row
Clob c=rs.getClob(2);
Reader r=c.getCharacterStream();
FileWriter fw=new FileWriter("d:\\retrivefile.txt");
int i;
while((i=r.read())!=-1)
fw.write((char)i);
fw.close();
con.close();
System.out.println("success");
}catch (Exception e) {e.printStackTrace(); }
}
}
Statement st = conn.createStatement( );
ResultSet rs = st.executeQuery();
String SQL = "Update item SET limit = ? WHERE itemType = ?";
PreparedStatement ps = conn.prepareStatement(SQL);
ResultSet rs = ps.executeQuery();
CallableStatement cs = con.prepareCall("{call SHOW_CUSTOMERS}");
ResultSet rs = cs.executeQuery();
addBatch()
and executeBatch()
methods are used. These 2 methods are available in the Statement and PreparedStatement classes of JDBC API.DriverManager.getConnection()
method, because of the following reasons :getConnection()
method.CallableStatement cs = con.prepareCall("{call insertEmployee(?,?,?,?,?)}");
stmt.setInt(1, id);
stmt.setString(2, name);
stmt.setString(3, role);
stmt.setString(4, address);
stmt.setString(5, salary);
//registering the OUT parameter before calling the stored procedure
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.executeUpdate();
We must register the OUT parameters before executing the CallableStatement.
setSTRUCT()
method and work with it. Connection con = null;
Statement st = null;
// Obtain connection here
st = con.createStatement();
ResultSet rs = null;
rs = st.executeQuery("SELECT * FROM users");
int recordsUpdated;
recordsUpdated = st.executeUpdate("DELETE FROM users WHERE user_id = 1");