DataBase Operation In Java Using JDBC

Abhisek
Posted by in Others category on for Beginner level | Views : 15685 red flag

This article will describe about how to insert, update, delete and retrieve records from database using JDBC in JAVA.
What is JDBC?



     Java database connectivity (JDBC) is the JavaSoftware specification of a standard application programming interface (API) that allows Java programs to access database management systems.

      In the program we are using MS SQL Server 2000 and Type1 JDBC Driver. First create a database and create a table(Employee)in it containing columns EmpID Name and Salary. Then create a DSN in your system which will points to the database we want to connect.

      In the following program we are inserting values to the database, updating the database, deleting values from the table and retrieving the values from the table using ResultSet.

CODE:-

Code For Inserting, Updating and Deletion

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

public class JDBCDemo {
public static void main(String[] args) {
try{

//loading the driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

System.out.println("Driver Is loaded");

//Setting Connection String

String dburl="jdbc:odbc:Demo";

//Getting connection Object

Connection con = DriverManager.getConnection(dburl,"sa","");

System.out.println("Connection established");

//Performing database Operation

Statement st=con.createStatement();

String sql="INSERT INTO EMPLOYEE VALUES('103','Liku',10000)";

//String sql="UPDATE EMPLOYEE SET SALARY=20000 WHERE EMPNAME='Abinash'";

//String sql="delete from employee where empid='103'";


int result=st.executeUpdate(sql);


System.out.println("No of rows affected=" + result);


//Releasing Resources

st.close();
con.close();

System.out.println("Connection closed");

}

catch(Exception ex)

{

ex.printStackTrace();

}
}

}


OUTPUT

Driver Is loaded
Connection established
No of rows affected=1
Connection closed

In this case you can run only one query at a time. So the other two are commented.

Code for Retrieval Of Records

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

public class JDBCDemo {
public static void main(String[] args) {

try{

//loading driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

System.out.println("Driver loaded");

//Setting Connection String

String dburl="jdbc:odbc:Demo";

//Get connection Object

Connection con = DriverManager.getConnection(dburl,"sa","");

System.out.println("Connection established");

//database Operation

Statement st=con.createStatement();

String sql="select*from employee";

ResultSet rs=st.executeQuery(sql);

while(rs.next())

{

System.out.print("Employee ID= "+ rs.getString(1)+" : ");

System.out.print("Employee Name= "+ rs.getString(2)+" : ");

System.out.println("Employee Salary= "+ rs.getString(3));

}

//Releasing Resources

rs.close();

st.close();

con.close();

System.out.println("Connection closed");

}

catch(Exception ex)

{

ex.printStackTrace();

}
}

}


OUTPUT

Driver loaded
Connection established
Employee ID= 108 : Employee Name= Abhisek : Employee Salary= 23000.0
Employee ID= 103 : Employee Name= Abinash : Employee Salary= 25000.0
Employee ID= 107 : Employee Name= Mukesh : Employee Salary= 20000.0
Connection closed

Description

First we have to import the classes from the sql package
import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.Statement;
import java.sql.ResultSet;

Now we have to load the jdbc odbc driver using the following method

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

     Then we have to create a connection string which will point to the DSN we created. Then create the con object and call the getConnection()

 String dburl="jdbc:odbc:Demo";
 Connection con = DriverManager.getConnection(dburl,"sa","");

dburl is only a string variable and sa is the user ID for the database. The password is blank here.

To pass the sql statement we have to use a object of Statement class.
 Statement st=con.createStatement();
String sql="select*from employee";

       For insert, delete and update you have to use executeUpdate() method and for retieval use executeQuery() and pass the sql statement to it.

 int result=st.executeUpdate(sql);
ResultSet rs=st.executeQuery(sql);

We have to create ResultSet object as the return type of executeQuery() is ResultSet.

Use while loop to retrieve all the values from the ResultSet.

At last release the resources.
 rs.close();
 st.close();

 con.close();

Exception handling is necessary here.


Page copy protected against web site content infringement by Copyscape

About the Author

Abhisek
Full Name: Abhisek Panda
Member Level: Bronze
Member Status: Member
Member Since: 10/11/2009 6:25:59 AM
Country: India
Abhisek Panda

Thanks and Regards Abhisek Panda Go Green And Save Your Future

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)