Showing posts with label JDBC. Show all posts
Showing posts with label JDBC. Show all posts

Friday, February 4, 2022

How the Java Language Could Better Support Composition and Delegation

Core Java, Java, Java Exam, Oracle Java Preparation, Oracle Java Guides, Oracle Java Skills, Oracle Java Certification

This article outlines a way of improving the Java language to better support composition and delegation. Engage in the discussion and contribute to evolving the Java Language.

The Java language lacks explicit semantic support for composition and delegation. This makes delegating classes hard to write, error-prone, hard to read and maintain. For example, delegating a JDBC ResultSet interface entails writing more than 190 delegating methods that essentially provide no additional information, as illustrated at the end of this article, and only add ceremony.

More generally, in the case of composition, Σ m(i) delegating methods need to be written where m(i) is the number of methods for delegate i (provided that all delegate method signatures are disjunct across all the delegates).  

The concept of language support for delegation is not new and there are numerous articles on the subject, including [Bettini08] and [Kabanov11]. Many other programming languages like Kotlin (“Derived”)  and Scala (“export”) have language support for delegation.

External Tools

Many IDEs have support for generating delegated methods. However, this neither impacts the ability to read nor understand a delegating class. Studies show that code is generally more read than written. There are third-party libraries that provide delegation (e.g. Lombok) but these are non-standard and provide a number of other drawbacks.

More generally, it would be possible to implement a subset of the functionality proposed here in third-party libraries leveraging annotation processors and/or dynamic proxies.

Trends and Industry Standards

As the drawbacks with inheritance were more deeply understood, the trend is to move towards composition instead. With the advent of the module system and generally stricter encapsulation policies, the need for semantic delegation support in the Java language has increased even more.

I think this is a feature that is best provided within the language itself and not via various third-party libraries. Delegation is a cornerstone of contemporary coding. 

In essence, It should be much easier to “Favor composition over inheritance” as stated in the book “Effective Java” by Joshua Bloch  [Bloch18, Item 18].

Java Record Classes

Many of the problems identified above were also true for data classes before record classes were introduced in Java 14. Upon more thorough analysis, there might be a substantial opportunity to harvest many of the findings made during the development of records and apply these in the field of delegation and composition.

On the Proposal

My intention with this article is not to present a concrete proposal of a way to introduce semantic support for composition and delegation in Java. On the contrary, if this proposal is one of the often 10-15 different discarded initial proposals and sketches on the path that needs to be traversed before a real feature can be proposed in the Java language, it will be a huge success. The way towards semantic support for composition and delegation in Java is likely paved with a number of research papers, several design proposals, incubation, etc. This feature will also compete against other features, potentially deemed to be more important to the Java ecosystem as a whole.

One motto for records was “model data as data” and I think that we should also “model delegation as delegation”.  But what is delegation? There are likely different views on this within the community. 

When I think of delegation, the following springs to mind: A delegating class has the following properties:

1. Has one or more delegates

2. Delegates methods from its delegates

3. Encapsulates its delegates completely

4. Implements and/or uses methods from its delegates (arguably)

An Outline – The Emissary

Core Java, Java, Java Exam, Oracle Java Preparation, Oracle Java Guides, Oracle Java Skills, Oracle Java Certification
In the following, I will present an outline to tackle the problem. In order to de-bikeshed the discussion, I will introduce a new keyword placeholder called “emissary” which is very unlikely ever to be used in a real implementation. This word could later be replaced by “delegator” or any other descriptive word suitable for the purpose or perhaps even an existing keyword.

An emissary class has many similarities to a record class and can be used as shown in the example below:

public emissary Bazz(Foo foo, Bar bar);

As can be seen, the Bazz class has two delegates (Foo and Bar) and consequently an equivalent desugared class  is created having two private final fields:

private final Foo foo;

private final Bar bar;

An emissary class is also provided with a constructor. This process could be the same as for records with canonical and compact constructors:

public final class Bazz {

    private final Foo foo;

    private final Bar bar;

    public Bazz(Foo foo, Bar bar) {

       this.foo = foo;

       this.bar = bar;

    }

}

It also makes the emissary class implement Foo and Bar. Because of this, Foo and Bar must be interfaces and not abstract or concrete classes. (In a variant of the current proposal, the implementing interfaces could be explicitly declared).

public final class Bazz implements Foo, Bar {

    private final Foo foo;

    private final Bar bar;

   public Bazz(Foo foo, Bar bar) {

       this.foo = foo;

       this.bar = bar;

   }

}

Now, in order to continue the discussion, we need to describe the example classes Foo and Bar a bit more which is done hereunder:

public interface Foo() {

    void f();

}

public interface Bar() {

    void b();

}

By declaring an emissary class we, unsurprisingly, also get the actual delegation methods so that Bazz will actually implement its interfaces Foo and Bar:

public final class Bazz implements Foo, Bar {

    private final Foo foo;

    private final Bar bar;

    public Bazz(Foo foo, Bar bar) {

        this. Foo = foo;

        this.bar = bar;

    }

    @Override

    void f() {

        foo.f();

    }

    @Override

    void b() {

        bar.b();

    }

}

If the delegates contain methods with the same signature, these would have to be explicitly “de-ambigued”, for example in the same way as default methods in interfaces. Hence, if Foo and Bar both implements c() then Bazz needs to explicitly declare c() to provide reconciliation. One example of this is shown here where both delegates are invoked:

@Override

void c() {

    foo.c();

    bar.c();

}

Nothing prevents us from adding additional methods by hand, for example, to implement additional interfaces the emissary class explicitly implements but that is not covered by any of the delegates.

It is also worth noting that the proposed emissary classes should not get a hashCode(), equals() or toString() methods generated. If they did, they would violate property C and leak information about its delegates. For the same reason, there should be no de-constructor for an emissary class as this bluntly would break encapsulation. Emissary classes should not implement Serializable and the likes by default.

An emissary class, just like a record class, is immutable (or at least unmodifiable and therefore shallowly immutable) and is hence thread-safe if all the delegates are.

Finally, an emissary class would extend java.lang.Emissary, a new proposed abstract class similar to java.lang.Enum and java.lang.Record.

Comparing Record with Emissary

Comparing the existing record and the proposed emissary classes yield some interesting facts:

record

◉ Provides a generated hashCode() method

◉ Provides a generated equals() method

◉ Provides a generated toString() method

◉ Provides component getters

◉ Cannot declare instance fields other than the private final fields which correspond to components of the state description

emissary

◉ Does not provide a generated hashCode() method

◉ Does not provide a generated equals() method

◉ Does not provide a generated  toString() method

◉ Provides delegating methods

◉ Implements delegates (in one variant)

◉ Can declare additional final instance fields other than the private final fields which correspond to delegates

both 

◉ A private final field for each component/delegate of the state description

◉ A public constructor, whose signature is the same as the state/delegate description, that initializes each field from the corresponding argument; (canonical constructor and compact constructor)

◉ Gives up the ability to decouple API from representation

◉ Implicitly final, and cannot be abstract (ensuring immutability)

◉ Cannot extend any other class (ensures immutability)

◉ Extends a java.lang class other than Object.

◉ Can declare additional methods not covered by the properties/delegates

Anticipated Use Cases

Here are some use cases of the emissary class:

Composition

Providing an Implementation for one or several interfaces using composition:

  public emissary FooAndBar(Foo foo, Bar bar);

Encapsulation

Encapsulating an existing instance of a class, hiding the details of the actual implementation:

private emissary EncapsulatedResultSet(ResultSet resultSet);

  …

  ResultSet rs = stmt.executeQuery(query);

  return new EncapsulatedResultSet(rs);

Disallow down-casting

Disallow the down-casting of an instance. I.e. an emissary class implements a restricted sub-set of its delegate’s methods where the non-exposed methods cannot be invoked via casting or reflection. 

String implements CharSequence and in the example below, we provide a String viewed as a CharSequence whereby we cannot down-cast the CharSequence wrapper back to a String. 

private emissary AsCharSequence(CharSequence s);

  return new AsCharSequence(“I am a bit incognito.”);

Services and Components

Providing an implementation of an interface that has an internal implementation. The internal component package is typically not exported in the module-info file:

public emissary MyComponent(MyComponent comp) {

      public MyComponent() {

          this(new InternalMyComponentImpl());

      }

      // Optionally, we may want to hide the public 

      // constructor

      private MyComponent(MyComponent comp) {

         this.comp = comp;

      } 

  }

  MyComponent myComp = ServiceLoader.load(MyComponent.class)

                           .iterator()

                           .next();

Note: If InternalMyComponentImpl is composed of an internal base class, contains annotations, has non-public methods, has fields etc. These will be completely hidden from direct discovery via reflection by the emissary class and under JPMS, it will be completely protected from deep reflection. 

Comparing Two ResultSet Delegators

Comparison between two classes delegating a ResultSet:

Emissary Class

// Using an emissary class. A one-liner

public emissary EncapsulatedResultSet(ResultSet resultSet);

IDE Generation

// Using automatic IDE delegation. About 1,000 lines!
 
public final class EncapsulatedResultSet implements ResultSet {
 
    private final ResultSet delegate;
 
 
    public EncapsulatedResultSet(ResultSet delegate) {
 
        this.delegate = delegate;
 
    }
 
    @Override
 
    public boolean next() throws SQLException {
 
        return delegate.next();
 
    }
 
  // About 1000 additional lines are not shown here for brevity…

Source: javacodegeeks.com

Wednesday, July 29, 2020

Difference between JDBC and Hibernate in Java

Oracle JDBC, Oracle Hibernate, Oracle Java Tutorial and Material, Oracle Java Exam Prep

Java is one of the most powerful and popular server-side languages in the current scenario. One of the main features of a server-side language is the ability to communicate with the databases. In this article, let’s understand the difference between two ways of connecting to the database (i.e.) JDBC and Hibernate.

Before getting into the differences, let us first understand what each of them actually means.

JDBC: JDBC stands for Java Database Connectivity. It is a java application programming interface to provide a connection between the Java programming language and a wide range of databases (i.e), it establishes a link between the two so that a programmer could send data from Java code and store it in the database for future use.

Hibernate: Hibernate is an open-source, non-invasive, light-weight java ORM(Object-relational mapping) framework to develop objects which are independent of the database software and make independent persistence logic in all JAVA, JEE. It simplifies the interaction of java applications with databases. Hibernate is an implementation of JPA(Java Persistence API).

The following table describes the differences:

JDBC   HIBERNATE 
In JDBC, one needs to write code to map the object model’s data representation to the schema of the relational model.  Hibernate maps the object model’s data to the schema of the database itself with the help of annotations. 
JDBC enables developers to create queries and update data to a relational database using the Structured Query Language (SQL).  Hibernate uses HQL (Hibernate Query Language) which is similar to SQL but understands object-oriented concepts like inheritance, association etc. 
JDBC code needs to be written in a try catch block as it throws checked exception(SQLexception).  Whereas Hibernate manages the exceptions itself by marking them as unchecked. 
JDBC is database dependent i.e. one needs to write different codes for different database.  Whereas Hibernate is database independent and same code can work for many databases with minor changes. 
Creating associations between relations is quite hard in JDBC.  Associations like one-to-one, one-to-many, many-to-one, and many-to-many can be acquired easily with the help of annotations. 

Monday, May 11, 2020

JDBC Driver

JDBC Driver is a software component that enables java application to interact with the database. There are 4 types of JDBC drivers:

1. JDBC-ODBC bridge driver
2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)

1) JDBC-ODBC bridge driver


The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls. This is now discouraged because of thin driver.

JDBC Driver, Oracle Java Guides, Oracle Java Learning, Oracle Java Tutorial and Materials, Java Exam Prep

Oracle does not support the JDBC-ODBC Bridge from Java 8. Oracle recommends that you use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC Bridge.

Advantages:

◉ easy to use.
◉ can be easily connected to any database.

Disadvantages:

◉ Performance degraded because JDBC method call is converted into the ODBC function calls.
◉ The ODBC driver needs to be installed on the client machine.

2) Native-API driver


The Native API driver uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java.

JDBC Driver, Oracle Java Guides, Oracle Java Learning, Oracle Java Tutorial and Materials, Java Exam Prep

Advantage:

◉ performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

◉ The Native driver needs to be installed on the each client machine.
◉ The Vendor client library needs to be installed on client machine.

3) Network Protocol driver


The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in java.

JDBC Driver, Oracle Java Guides, Oracle Java Learning, Oracle Java Tutorial and Materials, Java Exam Prep

Advantage:

◉ No client side library is required because of application server that can perform many tasks like auditing, load balancing, logging etc.

Disadvantages:

◉ Network support is required on client machine.
◉ Requires database-specific coding to be done in the middle tier.
◉ Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be done in the middle tier.

4) Thin driver


The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.

JDBC Driver, Oracle Java Guides, Oracle Java Learning, Oracle Java Tutorial and Materials, Java Exam Prep

Advantage:

◉ Better performance than all other drivers.
◉ No software is required at client side or server side.

Disadvantage:

Drivers depend on the Database.

Friday, May 8, 2020

Java JDBC

Oracle Java Tutorial and and Material, Oracle Java Learning, Oracle Java JDBC, Oracle Java Exam Prep

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database. There are four types of JDBC drivers:

◉ JDBC-ODBC Bridge Driver,
◉ Native Driver,
◉ Network Protocol Driver, and
◉ Thin Driver

We have discussed the above four drivers in the next chapter.

We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we can save, update, delete and fetch data from the database. It is like Open Database Connectivity (ODBC) provided by Microsoft.

Oracle Java Tutorial and and Material, Oracle Java Learning, Oracle Java JDBC, Oracle Java Exam Prep

The current version of JDBC is 4.3. It is the stable release since 21st September, 2017. It is based on the X/Open SQL Call Level Interface. The java.sql package contains classes and interfaces for JDBC API. A list of popular interfaces of JDBC API are given below:

◉ Driver interface
◉ Connection interface
◉ Statement interface
◉ PreparedStatement interface
◉ CallableStatement interface
◉ ResultSet interface
◉ ResultSetMetaData interface
◉ DatabaseMetaData interface
◉ RowSet interface

A list of popular classes of JDBC API are given below:

◉ DriverManager class
◉ Blob class
◉ Clob class
◉ Types class

Why Should We Use JDBC


Before JDBC, ODBC API was the database API to connect and execute the query with the database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).

We can use JDBC API to handle database using Java program and can perform the following activities:

1. Connect to the database
2. Execute queries and update statements to the database
3. Retrieve the result received from the database.

Monday, February 10, 2020

Top 10 JDBC Interview questions answers for Java programmer

JDBC Interview Question and Answer


JDBC Questions are integral part of any Java interview, I have not seen any Java Interview which is completed without asking single JDBC Interview question, there are always at least one or two question from JDBC API. In this article I have summarized few frequently asked questions in JDBC, they ranges from easy to difficult and beginner to advanced. Questions like distributed transaction management and 2 phase commit is tough to answer until you have real experience but mostly asked in various J2EE interviews. This is not an extensive list of JDBC question answers but practicing or revising this question before going to any Java interview certainly helps.

Oracle Java Study Materials, Oracle Java Prep, Oracle Java Study Materials, Oracle Java Guides

10 JDBC Interview question answer in Java


Here is my list of frequently asked JDBC question in Java, I have tried to provide answer to most of question. If you have any interesting JDBC question which you have faced and not in this list then please share with us.

Question 1: What is JDBC?

Answer : One of the first JDBC interview question in most of interviews. JDBC is java database connectivity as name implies it’s a java API for communicating to relational database, API has java classes and interfaces using that developer can easily interact with database. For this we need database specific JDBC drivers.

Question 2: What are the main steps in java to make JDBC connectivity?

Answer : Another beginner level JDBC Interview question, mostly asked on telephonic interviews. Here are main steps to connect to database.

◉ Load the Driver: First step is to load the database specific driver which communicates with database.

◉ Make Connection: Next step is get connection from the database using connection object, which is used to send SQL statement also and get result back from the database.

◉ Get Statement object: From connection object we can get statement object which is used to query the database

◉ Execute the Query: Using statement object we execute the SQL or database query and get result set from the query.

◉ Close the connection: After getting resultset and all required operation performed the last step should be closing the database connection.

Oracle Java Study Materials, Oracle Java Prep, Oracle Java Study Materials, Oracle Java Guides
Question 3: What is the mean of “dirty read“ in database?

Answer : This kind of JDBC interview question is asked on 2 to 4 years experience Java programmer, they are expected to familiar with database transaction and isolation level etc. As the name it self convey the meaning of dirty read “read the value which may or may not be correct”. in database when one transaction is executing and changing some field value same time some another transaction comes and read the change field value before first transaction commit or rollback the value ,which cause invalid value for that field, this scenario is known as dirty read.

Question 4: What is 2 phase commit?

Answer : This is one of the most popular JDBC Interview question and asked at advanced level, mostly to senior Java developers on J2EE interviews. Two phase commit is used in distributed environment where multiple process take part in distributed transaction process. In simple word we can understand like if any transaction is executing and it will effect multiple database then two phase commit will be used to make all database synchronized with each other.

In two phase commit, commit or rollback is done by two phases:

1. Commit request phase: in this phase main process or coordinator process take vote of all other process that they are complete their process successfully and ready to commit if all the votes are “yes” then they go ahead for next phase. And if “No “then rollback is performed.

2. Commit phase: according to vote if all the votes are yes then commit is done.

Similarly when any transaction changes multiple database after execution of transaction it will issue pre commit command on each database and all database send acknowledgement and according to acknowledgement if all are positive transaction will issue the commit command otherwise rollback is done .

Question 5: What are different types of Statement?

Answer : This is another classical JDBC interview question. Variants are Difference between Statement, PreparedStatemetn and CallableStatement in Java. Statement object is used to send SQL query to database and get result from database, and we get statement object from connection object.

There are three types of statement:

1. Statement: it’s a commonly used for getting data from database useful when we are using static SQL statement at runtime. it will not accept any parameter.
              Statement stmt = conn.createStatement( );
      ResultSet rs = stmt.executeQuery();

2. PreparedStatement: when we are using same SQL statement multiple time its is useful and it will accept parameter at runtime.
 
              String SQL = "Update stock SET limit = ? WHERE stockType = ?";
      PreparedStatement pstmt = conn.prepareStatement(SQL);
      ResultSet rs = pstmt.executeQuery();

3. Callable Statement: when we want to access stored procedures then callable statement are useful and they also accept runtime parameter. It is called like this
         
      CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
      ResultSet rs = cs.executeQuery();

Question 6: How cursor works in scrollable result set?

Answer : Another tough JDBC Interview question, not many Java programmer knows about using Cursor in Java.

in JDBC 2.0 API new feature is added to move cursor in resultset backward forward and also in a particular row .

There are three constant define in result set by which we can move cursor.

◉ TYPE_FORWARD_ONLY: creates a nonscrollable result set, that is, one in which the cursor moves only forward
◉ TYPE_SCROLL_INSENSITIVE : a scrollable result set does not reflects changes that are made to it while it is open
◉ TYPE_SCROLL_SENSITIVE: a scrollable result set reflects changes that are made to it while it is open

Question 7: What is connection pooling?

Answer : This is also one of the most popular question asked during JDBC Interviews. Connection pooling is the mechanism by which we reuse the recourse like connection objects which are needed to make connection with database .In this mechanism client are not required every time make new connection and then interact with database instead of that connection objects are stored in connection pool and client will get it from there. so it’s a best way to share a server resources among the client and enhance the application performance.


Question 8: What do you mean by cold backup, hot backup?

Answer : This question is not directly related to JDBC but some time asked during JDBC interviews. Cold back is the backup techniques in which backup of files are taken before the database restarted. In hot backup backup of files and table is taken at the same time when database is running. A warm is a recovery technique where all the tables are locked and users cannot access at the time of backing up data.

Question 9: What are the locking system in JDBC

Answer : One more tough JDBC question to understand and prepare. There are 2 types of locking in JDBC by which we can handle multiple user issue using the record. if two user are reading the same record then there is no issue but what if users are updating the record , in this case changes done by first user is gone by second user if he also update the same record .so we need some type of locking so no lost update.

Optimistic Locking: optimistic locking lock the record only when update take place. Optimistic locking does not use exclusive locks when reading

Pessimistic locking: in this record are locked as it selects the row to update

Question 10: Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

Answer: No, we can open only one statement object when using JDBC-ODBC Bridge.

That’s all on this list of 10 JDBC Interview question with answer. As I said JDBC API and there concepts are integral part of any Java interview and there is always atleast one question from JDBC. Since most application uses datbase in backend, JDBC becomes critical for any Java developer.

Wednesday, January 22, 2020

Transaction Management in JDBC

Transaction represents a single unit of work.

Oracle Java Tutorials and Materials, Oracle Java Certifications, Oracle Java Study Materials, Oracle JDBC

The ACID properties describes the transaction management well. ACID stands for Atomicity, Consistency, isolation and durability.

Atomicity means either all successful or none.

Consistency ensures bringing the database from one consistent state to another consistent state.

Isolation ensures that transaction is isolated from other transaction.

Durability means once a transaction has been committed, it will remain so, even in the event of errors, power loss etc.

Advantage of Transaction Mangaement

fast performance It makes the performance fast because database is hit at the time of commit.

Oracle Java Tutorials and Materials, Oracle Java Certifications, Oracle Java Study Materials, Oracle JDBC

In JDBC, Connection interface provides methods to manage transaction.

Method Description 
void setAutoCommit(boolean status)   It is true bydefault means each transaction is committed bydefault.
void commit()   commits the transaction. 
void rollback()   cancels the transaction. 

Simple example of transaction management in jdbc using Statement


Let's see the simple example of transaction management using Statement.

import java.sql.*;  
class FetchRecords{  
public static void main(String args[])throws Exception{  
Class.forName("oracle.jdbc.driver.OracleDriver");  
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
con.setAutoCommit(false);  
  
Statement stmt=con.createStatement();  
stmt.executeUpdate("insert into user420 values(190,'abhi',40000)");  
stmt.executeUpdate("insert into user420 values(191,'umesh',50000)");  
  
con.commit();  
con.close();  
}}  

If you see the table emp400, you will see that 2 records has been added.

Example of transaction management in jdbc using PreparedStatement


Let's see the simple example of transaction management using PreparedStatement.

import java.sql.*;  
import java.io.*;  
class TM{  
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");  
con.setAutoCommit(false);  
  
PreparedStatement ps=con.prepareStatement("insert into user420 values(?,?,?)");  
  
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));  
while(true){  
  
System.out.println("enter id");  
String s1=br.readLine();  
int id=Integer.parseInt(s1);  
  
System.out.println("enter name");  
String name=br.readLine();  
  
System.out.println("enter salary");  
String s3=br.readLine();  
int salary=Integer.parseInt(s3);  
  
ps.setInt(1,id);  
ps.setString(2,name);  
ps.setInt(3,salary);  
ps.executeUpdate();  
  
System.out.println("commit/rollback");  
String answer=br.readLine();  
if(answer.equals("commit")){  
con.commit();  
}  
if(answer.equals("rollback")){  
con.rollback();  
}  
  
  
System.out.println("Want to add more records y/n");  
String ans=br.readLine();  
if(ans.equals("n")){  
break;  
}  
  
}  
con.commit();  
System.out.println("record successfully saved");  
  
con.close();//before closing connection commit() is called  
}catch(Exception e){System.out.println(e);}  
  
}}  

It will ask to add more records until you press n. If you press n, transaction is committed.

Tuesday, January 21, 2020

Java Database Connectivity with Oracle

Java Database Connectivity with Oracle, Oracle Java Study Materials, Oracle Java Online Exam, Oracle Java Prep

To connect java application with the oracle database, we need to follow 5 following steps. In this example, we are using Oracle 10g as the database. So we need to know following information for the oracle database:

1. Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.

2. Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localhost:1521:xe where jdbc is the API, oracle is the database, thin is the driver, localhost is the server name on which oracle is running, we may also use IP address, 1521 is the port number and XE is the Oracle service name. You may get all these information from the tnsnames.ora file.

3. Username: The default username for the oracle database is system.

4. Password: It is the password given by the user at the time of installing the oracle database.

Create a Table

Before establishing connection, let's first create a table in oracle database. Following is the SQL query to create a table.

create table emp(id number(10),name varchar2(40),age number(3)); 

Example to Connect Java Application with Oracle database


In this example, we are connecting to an Oracle database and getting data from emp table. Here, system and oracle are the username and password of the Oracle database.

import java.sql.*; 
class OracleCon{ 
public static void main(String args[]){ 
try{ 
//step1 load the driver class 
Class.forName("oracle.jdbc.driver.OracleDriver"); 
 
//step2 create  the connection object 
Connection con=DriverManager.getConnection( 
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle"); 
 
//step3 create the statement object 
Statement stmt=con.createStatement(); 
 
//step4 execute query 
ResultSet rs=stmt.executeQuery("select * from emp"); 
while(rs.next()) 
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3)); 
 
//step5 close the connection object 
con.close(); 
 
}catch(Exception e){ System.out.println(e);} 
 



download this example

The above example will fetch all the records of emp table.

To connect java application with the Oracle database ojdbc14.jar file is required to be loaded.

download the jar file ojdbc14.jar

Two ways to load the jar file:

1. paste the ojdbc14.jar file in jre/lib/ext folder
2. set classpath

1) paste the ojdbc14.jar file in JRE/lib/ext folder:

Firstly, search the ojdbc14.jar file then go to JRE/lib/ext folder and paste the jar file here.

2) set classpath:

There are two ways to set the classpath:

◉ temporary
◉ permanent

Java Database Connectivity with Oracle, Oracle Java Study Materials, Oracle Java Online Exam, Oracle Java Prep
How to set the temporary classpath:

Firstly, search the ojdbc14.jar file then open command prompt and write:

C:>set classpath=c:\folder\ojdbc14.jar;.; 

How to set the permanent classpath:

Go to environment variable then click on new tab. In variable name write classpath and in variable value paste the path to ojdbc14.jar by appending ojdbc14.jar;.; as C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;

Source: javatpoint.com

Monday, January 13, 2020

Java Database Connectivity with 5 Steps

Java Database Connectivity, Oracle Java Tutorial and Material, Oracle Java Learning, Oracle Certifications

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:

1. Register the Driver class
2. Create connection
3. Create statement
4. Execute queries
5. Close connection

Java Database Connectivity, Oracle Java Tutorial and Material, Oracle Java Learning, Oracle Certifications


1) Register the driver class


The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException 

Note: Since JDBC 4.0, explicitly registering the driver is optional. We just need to put vender's Jar in the classpath, and then JDBC driver manager can detect and load the driver automatically.

Example to register the OracleDriver class

Here, Java program is loading oracle driver to esteblish database connection.

Class.forName("oracle.jdbc.driver.OracleDriver"); 

2) Create the connection object


The getConnection() method of DriverManager class is used to establish connection with the database.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException 
2) public static Connection getConnection(String url,String name,String password) 
throws SQLException 

Example to establish connection with the Oracle database

Connection con=DriverManager.getConnection( 
"jdbc:oracle:thin:@localhost:1521:xe","system","password"); 

3) Create the Statement object


The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException 

Example to create the statement object

Statement stmt=con.createStatement(); 

4) Execute the query


The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException 

Example to execute query

ResultSet rs=stmt.executeQuery("select * from emp"); 
 
while(rs.next()){ 
System.out.println(rs.getInt(1)+" "+rs.getString(2)); 


5) Close the connection object


By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

Syntax of close() method

public void close()throws SQLException 

Example to close connection

con.close(); 

Note: Since Java 7, JDBC has ability to use try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement.

Saturday, January 11, 2020

JDBC - Introduction

What is JDBC?


JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

Oracle Java Tutorial and Materials, Java Prep, Oracle Java Online Exam, Oracel Java Guides

The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage.

◉ Making a connection to a database.

◉ Creating SQL or MySQL statements.

◉ Executing SQL or MySQL queries in the database.

◉ Viewing & Modifying the resulting records.

Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables, such as −

◉ Java Applications

◉ Java Applets

◉ Java Servlets

◉ Java ServerPages (JSPs)

◉ Enterprise JavaBeans (EJBs).

All of these different executables are able to use a JDBC driver to access a database, and take advantage of the stored data.

JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.

JDBC Architecture


The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC Architecture consists of two layers −

◉ JDBC API: This provides the application-to-JDBC Manager connection.

◉ JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

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.

Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC drivers and the Java application −

Oracle Java Tutorial and Materials, Java Prep, Oracle Java Online Exam, Oracel Java Guides

Common JDBC Components


The JDBC API provides the following interfaces and classes −

◉ DriverManager: This class manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication sub protocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.

◉ Driver: This interface handles the communications with the database server. You will interact directly with Driver objects very rarely. Instead, you use DriverManager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects.

◉ Connection: This interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database is through connection object only.

◉ Statement: You use objects created from this interface to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.

◉ ResultSet: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.

◉ SQLException: This class handles any errors that occur in a database application.

The JDBC 4.0 Packages


The java.sql and javax.sql are the primary packages for JDBC 4.0. This is the latest JDBC version at the time of writing this tutorial. It offers the main classes for interacting with your data sources.

The new features in these packages include changes in the following areas −

◉ Automatic database driver loading.

◉ Exception handling improvements.

◉ Enhanced BLOB/CLOB functionality.

◉ Connection and statement interface enhancements.

◉ National character set support.

◉ SQL ROWID access.

◉ SQL 2003 XML data type support.

◉ Annotations.

Wednesday, March 27, 2019

How to use Callable Statement in Java to call Stored Procedure? JDBC Example

The CallableStatement of JDBC API is used to call a stored procedure from Java Program. Calling a stored procedure follows the same pattern as creating PreparedStatment and than executing it. You first need to create a database connection by supplying all the relevant details e.g. database URL, which comprise JDBC protocol and hostname, username, and password. Make sure your JDBC URL is acceptable by JDBC driver you are using to connect to the database. Every database vendor uses different JDBC URL and they have different driver JAR which must be in your classpath before you can run the stored procedure from Java Program.

Oracle Java Certifications, Oracle Java Learning, Oracle Java Tutorial and Material

Once you are done with initial setup, you can obtain CallableStatement from Connection by calling prepareCall(String SQL) method, where SQL must be in the format required by your database vendor e.g. Microsoft SQL Server requires curly braces e.g.
{call Books.BookDetails_Get(?)}.

This stored proc requires an INPUT parameter which must be set by calling setXXX() method on the CallableStatement object before you can execute the query.

Once this is done, just call the executeQuery() method of CallableStatement and it will return the ResultSet contains all the rows returned by this stored proc.

Just loop through ResultSet and extract all the rows. You have successfully run the stored procedure from Java Program using CallableStatement.

Steps to call a stored procedure from Java


1) Create a database connection.

Connection con = null;
try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String url = "jdbc:sqlserver://localhost:42588;";
   con = DriverManager.getConnection(url, "username", "pw");
} catch (Exception e) {
   e.printStackTrace();
}

2) Create a SQL String

You need to create SQL using a String variable to call the stored procedure, for example, CallableStatement e.g. {call Books.BookDetails_Get(?)}. This is database dependent, for Oracle the format is different it starts with BEGIN and ends with ENDS instead of curly braces e.g.

String SQL = "{call Books.BookDetails_Get(?)}" // for Microsoft SQL Server
String Oracle = "BEGIN BOOKDETAILS_GET(?); END;";

3) Create CallableStatement Object

You can create a CallableStatement by calling Connection.prepareCall(SQL) method, pass the SQL created in the previous step.

CallableStatement cs = con.prepareCall(SQL);

4)  Provide Input Parameters

You can set the input parameter by calling various setXXX() method depending upon the data type of query parameters on the CallableStatement object, similar to PreparedStatment e.g.

cs.setString(1, "982928");

5)  Call Stored Procedure

You can execute a stored procedure on the database by calling executeQuery() method of CallableStatement class, as shown below:

ResultSet rs = cs.executeQuery();

This will return a ResultSet object which contains rows returned by your stored procedure.

6) Extract Rows from ResultSet

You can get the data from the ResultSet by Iterating over ResultSet and print the results or create Java objects, as shown below:

while(rs.next()){
  System.out.println(rs.getString(1));
}

This will print the first column of every row. You should also close the ResultSet object once you are done with it.

Oracle Java Certifications, Oracle Java Learning, Oracle Java Tutorial and Material

Java Program to call Stored Procedure in SQL Server using CallableStatement


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 *
 * A Simple example to use CallableStatement in Java Program.
 */
public class Hello {

  public static void main(String args[]) {
   
    Connection con = null;
    try {
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       String url = "jdbc:sqlserver://localhost:42588;";
       con = DriverManager.getConnection(url, "username", "pw");
    } catch (Exception e) {
       e.printStackTrace();
    }

    String SQL = "{call Books.dbo.usp_BookDetails_Get(?)}";

    CallableStatement cs = con.prepareCall(SQL);

    cs.setString(1, "978-0132778046");

    cs.setString(2, "978-0132778047");

    ResultSet rs = cs.executeQuery();
   
    while(rs.next()){
      System.out.println(rs.getString(1));
    }
   
    rs.close();
  }
}

That's all about how to run CallableStatement in Java JDBC. If you are thinking to build your Data Access layer around stored procedures, which is a great design, then you should have a good understanding of CallableStatement.

They are the ones which are used to run the stored procedure from Java programs. By encapsulating your data access logic and SQL on a stored procedure, allow you to change them easily on SQL editor without making any change on Java side, which means you can implement new functionalities without building and deploying a new JAR file on Java side.

Friday, May 26, 2017

Different Types of JDBC Drivers in Java

There are mainly 4 types of JDBC drivers in Java, those are referred as type 1 to type 4 jdbc drivers. I agree its easy to remember them by type rather than with there actual name, Which I have yet to get in memory except plain old JDBC-ODBC bridge driver. By the way here are there full names :

Oracle JDBC, Oracle Java Tutorial and Material, Oracle Java Guides

Type 1 JDBC Driver is called JDBC-ODBC Bridge driver (bridge driver)
Type 2 JDBC Driver is referred as Native-API/partly Java driver (native driver)
Type 3 JDBC Driver is called AllJava/Net-protocol driver (middleware driver)
Type 4 JDBC Driver is called All Java/Native-protocol driver (Pure java driver)

1. JDBC ODBC Bridge Driver or Type 1 JDBC driver

In case of JDBC ODBC bridge driver all JDBC calls doesn't directly goes to database instead they go via ODBC driver. JDBC-ODBC driver translates JDBC calls into ODBC callas and send them to ODBC driver for passing to database. Since type 1 driver act as bridge between JDBC and ODBC and that's why its called JDBC-ODBC bridge driver. This driver is not fast and good for production use mainly because of several layer of translation on back and fourth database traffic but it has  advantage in terms of of availability and can be your last choice.

2. Native-API/partly Java driver or Type 2 JDBC driver


This is also called type 2 driver and its slightly better than type 1 JDBC driver. type 2 JDBC driver convert JDBC calls into database calls by using native API provided by database. This driver is database specific so once you switch from one database to another you need to change type 2 JDBC driver. performance is better than JDBC-ODBC bridge driver since communication layer is reduced. type 2 JDBC driver requires database native library to be available on client but it poses several version and compatibility issue. This was liked by Database vendors though because they can reuse there existing native libraries.

3. All Java/Net-protocol driver or Type 3 JDBC driver


both type 1 and type 2 JDBC drivers were not written in Java so there was need for pure Java JDBC driver to resolve portability issue. type 3 JDBC driver comes with pure java implementation (that's why All Java word ) but it uses 3 tier architecture where you have a Java client and Java Server which talk with Net protocol and Server speaking to database. type 3 JDBC driver never get popular among database vendors as it was costly for them to rewrite there existing native database library which was mainly on C and C++.

4. All Java/Native-protocol driver or Type 4 JDBC driver


type 4 JDBC driver is most popular among all four types of JDBC driver. it has not only implemented in Java but also incorporates all database call in single driver. It was pretty easy to use and deploy as well just include driver's jar in classpath and you are ready. It also removes 3 tier architecture of type 3 JDBC driver which makes it faster than type 3.  Major development happens on type 4 JDBC driver when database upgrade themselves, though some of them still upgrade native database library or type 2 driver.

That's all on quick overview of different types of JDBC drivers in Java. JDBC drivers has evolved from JDBC ODBC bridge driver to type 4 JDBC driver, which is clean and portable. There has been some buzz around JDBC driver 5 on Java community which may include some advanced functionality.