Java database connectivity : JDBC Tutorial

Java database connectivity steps

How to create jdbc connection in java?

What is difference between statement and preparedstatement in java ?

In the previous jdbc tutorial named as types of jdbc drivers in java  we learned about what is jdbc in java and jdbc drivers in java . Now in this jdbc tutorial we will learn how to create jdbc connection in java or java database connectivity  with mysql or jdbc connection in java with oracle.

There are 6 steps used  to connect a java application with the database through JDBC. 

These steps are as follows:
  •       Create an ODBC Data Source Name.
  •       Register the Driver class or load the jdbc drivers in java
  •       Create  a connection
  •        Create statement and execute query.
  •        Loop the result set until the data is available in table
  •         Free resources or Close connection
All these  java database connectivity or jdbc drivers in java steps are explained here with their code .

jdbc steps
Step 0 – Create an ODBC DSN (Data Source Name)
Step 1 – Load the jdbc drivers in java
Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”); //for JDBC-ODBC Bridge

Parameter passed in forName() method to connect with different database are as follows:

To connect with MySql – Class.forName (“org.gjt.mm.mysql.Driver”);

Jdbc connection in java with Oracle – Class.forName (“oracle.jdbc.driver.OracleDriver”);

Jdbc connection with SqlServer Class.forName (“com.microsoft.jdbc.sqlserver.SQLServerDriver”);

Step 2 – Create a connection

Connection conn = DriverManager.getConnection(“jdbcURL”,”username”,”password”);

here jdbcURL syntax is written as 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.
example –
For mysql database connection in java – jdbc:mysql://127.0.0.1:3306/student
For Oracle – jdbc:oracle:thin:@127.0.0.1:1521:student
For SQL Server –
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.

types of statements in jdbc
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();

Difference between PreparedStatement and Statement

This is important to note that prepared statement java PreparedStatement is better as compare to Statement .Some differences between prepared statement java PreparedStatement and Statement are explained here 
 In case of  PreparedStatement it means prepared statement java query remains in the parsed form.Generally SQL statements can belong to any one of the two categories: static or dynamic.
With a static query the string does not change you create it once then call it over and over again.
In contrast  with a dynamic query you create a new SQL command by concatenating strings and variables after that instantiating a new Statement object and executing that command. 
Either way each time we execute a new command the database must compile the SQL code. This compilation can include various activities such as costly syntax checking, name validation, and pseudo code generation.
From the database performance point of view we should use prepared statement java PreparedStatement. A PreparedStatement is provide the Java encapsulation of a parameterized query in which the SQL statement compiles a single time but it can execute many times. 
If we want to change the query conditions then we employ placeholders (?) within the statement to indicate bind variables. After such bind variables (parameters to the query) are set the query executes.

Create table using PreparedStatement JDBC

Class : JDBCTest.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import com.computersciencejunction.util.JDBCUtil;

/**
 * This class is used to create a table in DB
 * using PreparedStatement.
 * @author computersciencejunction
 */
public class JDBCTest {
public static void main(String args[]){
Connection conn = null;
PreparedStatement preparedStatement = null;

String query = “create table EMPLOYEE(”
+ “EMPLOYEE_ID NUMBER(5) NOT NULL, ”
+ “NAME VARCHAR(20) NOT NULL, ”
+ “SALARY NUMBER(10) NOT NULL, ”
+ “PRIMARY KEY (EMPLOYEE_ID) )”;

try{
//get connection
conn = JDBCUtil.getConnection();

//create preparedStatement
preparedStatement = conn.preparedStatement(query);

//execute query
preparedStatement.execute();

//close connection
preparedStatement.close();
conn.close();

      System.out.println(“Table created successfully.”);
}catch(Exception e){
e.printStackTrace();
}
}
}

Class JDBCUtil.java

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * This is a utility class for JDBC connection.
 * @author computersciencejunction
 */
public class JDBCUtil {
//JDBC and database properties.
private static final String DB_DRIVER =
           “oracle.jdbc.driver.OracleDriver”;
private static final String DB_URL =
        “jdbc:oracle:thin:@localhost:1521:XE”;
private static final String DB_USERNAME = “system”;
private static final String DB_PASSWORD = “oracle”;

public static Connection getConnection(){
Connection conn = null;
try{
//Register the JDBC driver
Class.forName(DB_DRIVER);

//Open the connection
conn = DriverManager.
getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

if(conn != null){
   System.out.println(“Successfully connected.”);
}else{
   System.out.println(“Failed to connect.”);
}
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
}

Output

Successfully connected.
Table created successfully

Leave a Reply

Your email address will not be published. Required fields are marked *

x