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.

All these  java database connectivity or jdbc drivers in java steps are explained here with their code .

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”);

Step 2 – Create a connection

Three types of statement can be used  Statement, PreparedStatement and CallableStatement. Here we are explaining syntax for each type.

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