Posts Tagged ‘jdbc’

Statements In JDBC JDBC Interview Questions JDBC Statements PreparedStatement and CallableStatement JDBC Technology Call Stored Procedures

Statements In JDBC

 

Statements  in JDBC are carrier for sql queries in JDBC.  Statements in JDBC are obtained from connection objects.

Connection is the handle for the session between JDBC and Database server.Without connection object we cannot have a statement object and without statement object,sql queries cannot be run at the database server side from the JDBC code.

There are three types of statements in JDBC.These are as follows :

1.Statement

2.Prepared Statement

3.Callable Statement

 

1.Statement : These statements are used to execute queries like select statements at the server side. Every time a statement is run it is compiled at the server (database side),it is compiled at the server side and run.

The code looks like this :

Class.forName("driver class");
Connection conn = DriverManager.getConnection("connection string","username","password");
String sql = "select * from tablename";
Statement st  = conn.createStatement(sql);
st.executeQuery();

 

2.Prepared Statement : These statements are used where we have to execute same query multiple number of times with small changes like update value.In such cases we do not need to compile the query again and again at the server side but we need to compile he query once and keep executing again and again at the the server side.Thus,prepared statements help in this case as these are precompiled statements which can be executed ample times thus saving time.

 

The code looks like this :

Class.forName("driver class");
Connection conn = DriverManager.getConnection("connection string","username","password");
String sql = "insert into tablename values (?,?,?)";
PreparedStatement pst  = conn.prepareStatement(sql);
pst.setString(1,"java");
pst.setInt(2,3);
pst.setFloat(3,3.14);
pst.executeQuery();

 

The ‘?’ are placeholders to feed the value to the precompiled statements.setXXX methods are used to set the values to the placeholders depending upon the  data type at the server side.In the code pst.setString(1,”java”),1 denotes the first place holder.

 

3.Callable Statement : These statements are used to execute stored procedures and function at the database server side.

The code looks like this :

Class.forName("driver class");
Connection conn = DriverManager.getConnection("connection string","username","password");
String sql = "{? = execute procesdureorfunctionname(?,?)}";
CallableStatement cst  = conn.prepareCall(sql);
cst.registerOutParameter(1,java.sql.Types.String);
cst.setString(2,"java");
cst.setInt(3,3);

cst.execute();
String str = cst.getString(1);

 

Number of View :2471

Tags: , , , , , , , , , , ,

No Comments


Driver Registration In JDBC JDBC Connection Using the JDBC Driver Registering the JDBC driver Registering A JDBC driver JDBC Database Connections Basics Of JDBC How To Establish A JDBC Connection

Driver Registration In JDBC

An interface links two unrelated objects.A driver in JDBC context is an interface which links java code to database for communication.

Before ever the connection is established between the java programming end and the database end,the driver needs to be registered with the JVM by the means of DriverManager class.The DriverManager class has a vector of drivers registered to it.Once the entry has been made in the vector of the DriverManager class the driver is said to be registered.Each driver has the implementation and is capable of registering itself with the DriverManager class.This implementation is written in the static initialization block of the driver class.

We know that the static initialization block for a class is executed every time we load the class in the class loader. So, in order to run the static initialization block in the class loader we need to run the following line of code so that the driver registers itself with the DriverManager class :

Class.forName("DriverClass");

We can load or compile a class in class loader by calling the Class.forName(“class name”) method of the reflection concept in java.

Now let us have a look at how  the static initialization block in the driver class looks like :

 

class Driver{

 static{
    --------
    DriverManager.registerDriver(Driver Class);
    ----
    ----
    ----
    }
}

 

The function registerDriver() of the DriverManager class takes driver class as one of the parameters and looks for an entry ofr that driver in the vector it has. If an entry is found it means that the driver is already registered and the reference is returned from the vector.UIf entry is not available in the vector,the entry is made and reference is returned.This way we say that the driver is registered and connection can be made with the database as follows :

DriverManager.getConnection("Connection String","username","password");

Here connection String is depenedent on what database we are using.A typical example is :

jdbc:postgresql://host:port/database

Here ,username and password are username and password for the database.

Number of View :1146

Tags: , , , , , , , ,

No Comments


Transaction Locking And Isolation In JDBC Transaction isolation levels Using Transactions JDBC Isolation Example JDBC Guide Basics Of JDBC

Transaction Locking And Isolation In JDBC

Transaction is a set of instructions which follow atomicity,consistency,Integrity and durability.

1. Atomicity : This states that either all actions should complete or none should occur.

2. Consistency : This imposes certain business rule.

3. Integrity : This states that the two actions shall not interfere with each other or access each others data.

4. Durability : This states that the changes occurred due to a certain action or transaction must be stored in a durable persistent storage.

 

When we talk about the Transaction Locking And Isolation In JDBC, following are the issues we need to address when to concurrent transactions are taking place :

1. Dirty read : This happens when uncommitted data is read.Typical example is :

a. Transaction 1 inserts a row into a table.

b.Transaction 2 reads the new row.

c.Transaction 1 rolls back.

2. Non-repeatable read : This happens when data reads from the same table for the same row are not same.Typical example is

a. Transaction 1 reads a row.

b. Transaction 2 updates the row.

c. Transaction 1 reads the same row a second time and gets a different  results.

 

3. Phantom Read : This occurs due to appearing of new records when same query is run at different times.Typical example is :

a. Transaction 1 reads all rows that satisfy a WHERE clause.

b. Transaction 2 inserts additional rows that satisfies the WHERE clause.

c. Transaction 1 executes  the WHERE condition and picks up the additional row.

There are different transaction isolation levels used from JDBC coding to address these issues.The function to set isolation level is :

 

con.setTranscationIsolationLevel(intvalue);

Different integer values can be provided as follows :

0 – JDBC_TRANSACTION_NONE
1 – JDBC_TRANSACTION_READ_UNCOMMITTED
2 – JDBC_TRANSACTION_READ_COMMITTED
4 – JDBC_TRANSACTION_REPEATABLE_READ
8 – JDBC_TRANSACTION_SERIALIZABLE

Once we set the isolation levels using the above function,we can address the issues of transaction level in JDBC connection.As the isolation     level increases it gets stricter and lower level problems are also addressed.If we have set the value to 8 ,the isolation level with value lesser than 8 ,ie. 0,1,2,4, are also taken  care of.

Number of View :1272

Tags: , , , , , , , , ,

No Comments



SetPageWidth
Content Protected Using Blog Protector By: PcDrome.

Mahol Dot Org is Stephen Fry proof thanks to caching by WP Super Cache