Java database connectivity : JDBC Tutorial

java database connectivity steps with mysql database connection in java are explained in this tutorial. with a jdbc program. Difference between prepared statement java and statement in java

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

COMMENTS

BLOGGER
Name

addressing modes types,1,advance-java,2,aktu entrance exam,1,aktu exam schedule,1,ASP,1,bare machine,1,base register and limit register,1,C Programming,18,C Plus Plus,1,c programming notes for gate,18,C programming Tutorials,8,Cache Memory,1,Childcare,1,CJ,1,Cloud Computing,1,CN,4,Computer Architecture,2,Computer architecture based questions for gate exam,11,Computer architecture Tutorials,1,Computer Network,3,Computer Network Study Material,2,Computer network study material for gate,1,Computer Networks,10,Computer networks gate questions with answer,3,computer networks notes,1,computer networks tutorial,1,Computer Science Study Material for Gate,13,computer science study material for gate exam,17,contiguous memory allocation,2,Core Java,3,cyber crime report,1,Cyber crime status,1,cybercrime and security,1,cybercrime examples,1,data communications and networking,1,Data Structure,2,Data Structure Questions,1,Data Transmission Architecture,1,Data Transmission in wsn,1,database normalization,1,dbmas study material,1,DBMS,8,dbms gate questions with answer,1,dbms multiple choice questions with answers for gate,1,dbms question paper,1,DE,1,Digital Electronics,1,DS,4,Electroencephalogram,1,ER diagram Tutorial,1,Gate 2017,3,Gate 2017 Admit card,1,GATE 2020,1,gate cse c programming questions,10,gate cse study material,2,gate cse syllabus,2,gate practice set,7,gate questions on c programming,10,gate study material for computer science,14,Gate study material for computer science 2017,1,gate study material for cse,46,General,3,HCL Aptitude Test,1,HR Interview Questions,1,HTML,4,Important Date of Gate 2017 Exam,1,Information Security Policy,1,internal and external fragmentation,1,Java Tutorials,3,JDBC,2,JDBC Tutorial,1,JS,1,memory fragmentation,1,memory management,1,memory management questions and answer in os,1,Motivational,4,NCER,1,Numerical Techniques Lab,1,OOT,1,Operating System,6,Operating System Gate Questions,2,Operating System Objective Questions,4,Operating System Questions Bank,1,operating system study material for gate exam,11,operating system tutorial notes,8,Operating System tutorials resident monitor,1,page swapping,1,paged memory allocation,1,paged memory allocation in operating system,1,Regression testing,1,relocation register,1,routing table,1,Software Engineering,10,Software Engineering baes study material for gate,1,Software Quality Assurance,3,software verification methods,1,Stack,1,Study Material for gate Computer Science,5,swapping in memory management,1,swapping in operating system,1,TCS Code Vita,1,TCS Interview Questions,1,Technical Interview,1,Technical Questions from DBMS,1,Tips to Learn Coding,1,UML,1,Virtualization,1,What is process control block ?,1,what is software testing?,1,Wireless Sensor Network,3,worst fit algorithm for memory allocation,1,XML,2,
ltr
item
Computer Science Junction: Java database connectivity : JDBC Tutorial
Java database connectivity : JDBC Tutorial
java database connectivity steps with mysql database connection in java are explained in this tutorial. with a jdbc program. Difference between prepared statement java and statement in java
https://1.bp.blogspot.com/-nFCc7aHUlm4/XRDcnwSVAoI/AAAAAAAABV0/y_F8pa1jSswns-iT1DV1cGJcDLHGO8xvwCLcBGAs/s400/jdbcsteps.png
https://1.bp.blogspot.com/-nFCc7aHUlm4/XRDcnwSVAoI/AAAAAAAABV0/y_F8pa1jSswns-iT1DV1cGJcDLHGO8xvwCLcBGAs/s72-c/jdbcsteps.png
Computer Science Junction
https://www.computersciencejunction.in/2019/04/jdbc-steps-to-connect-to-database-in-java.html
https://www.computersciencejunction.in/
https://www.computersciencejunction.in/
https://www.computersciencejunction.in/2019/04/jdbc-steps-to-connect-to-database-in-java.html
true
425357657003182083
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share. STEP 2: Click the link you shared to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy