Table of Contents
JDBC ( Java Database Connectivity) Tutorial
JDBC is known as java database connectivity. To connect java with any database we require a jdbc driver which implements all the interfaces provided in sql package. Java provides jdbc-odbc bridge driver.
Figure 1: Java Application Interaction with Database
JDBC uses ODBC drivers available on your machine. DB Vendor provides its own driver. To use them set driver (.jar) into class path/install them into your machine.
Package required – java.sql. In this package most of them are interfaces.
Types of Driver
Different types of drivers are as shown in figure 2.
Figure 2: Driver types
Driver Type 1:
Figure 3: Type 1 Driver
In type 1 driver JDBC ODBC Bridge Translates all JDBC calls into ODBC calls and send them to ODBC Driver.
Driver Type 2:
Figure 4: Type 2 Driver
The Native API or Partially java driver converts jdbc calls into database specific calls for databases such as SQL server, Oracle and Sybase. Type 2 Driver communicate directly with database server.
Driver Type 3:
Figure 5: Type 3 Driver
Driver Type 4:
Figure 6: Type 4 Driver
The native-protocol/all-Java driver converts JDBC calls into the vendor specific database management system (DBMS) protocol so that client applications can communicate directly with the database server. Level 4 drivers are completely implemented in Java to achieve platform independence and eliminate deployment administration issues.
Steps for java database connectivity:
Following steps are used to connect a java application with database.
Step 0 – Create an ODBC DSN (Data Source Name)
Step 1 – load the driver
Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”); //for JDBC-ODBC Bridge
Note: – if not using odbc then you will load vendor specific driver class. Check driver
documentation.
MySql – Class.forName (“org.gjt.mm.mysql.Driver”);
Oracle – Class.forName (“oracle.jdbc.driver.OracleDriver”);
SqlServer – Class.forName (“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
Step 2 – create a connection
Connection conn = DriverManager.getConnection(“jdbcURL”,”username”,”password”);
jdbcURL – jdbc::
E.g. – jdbc:odbc:dsnName
Where dsnName is the data source name that you have created earlier.
Note :- If you are not going through odbc then you have to provide the host string.
E.g. –
MySql – jdbc:mysql://127.0.0.1:3306/student
Oracle – jdbc:oracle:thin:@127.0.0.1:1521:student
SqlServer –
jdbc:microsoft:sqlserver://127.0.0.1:1433;SelectMethod=Cursor;DatabaseName=student
Step 3 – create a statement for executing your database transactions.
Three types of statement can be used Statement, PreparedStatement and CallableStatement. Here we are explaining syntax for each type.
Statement
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(“select * from emp”);
int i = st.executeUpdate(“insert into college(name) values(‘ITS’)”);
PreparedStatement
PreparedStatement pst = con.preapareStatement(“select * from emp where name = ? and dept = ?”);
pst.setString(1,”sumit”);
pst.setString(2,”it”);
ResultSet rs = st.executeQuery();
CallableStatement
CallableStatement cstmt = con.prepareCall (“{call getDailyTotal (?, ?)}”);
cstmt.setString (1, “Mon”);
cstmt.registerOutParameter (2, java.sql.Types.INTEGER);
cstmt.executeUpdate();
System.out.println (“Total is ” + cstmt.getInt (2));
Step 4 – loop the ResultSet for your results
while(rs.next()){
System.out.println(“Emp Name is : ”+rs.getString(“name”));
}
Step 5 – free the resources
rs.close();
st.close();
con.close();
Keywords: jdbc steps, jdbc drivers
