.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> General >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Insert update delete select records using jdbc

Posted By:satyapriyanayak       Posted Date: July 21, 2014    Points: 200    Category: General    URL: http://www.dotnetspark.com  

In this article we will know how to insert update delete and select records from database using JDBC (Java Database Connectivity).
 

Here we are going to use Type-1 driver

Type-1 Driver- The JDBC type 1 driver, also known as the JDBC-ODBC bridge is a database driver implementation that the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls. The bridge is usually used when there is no pure-Java driver available for a particular database. The driver is implemented in the sun.jdbc.odbc.JdbcOdbcDriver class. The driver is platform-dependent as it makes use of ODBC, which in turn depends on native libraries of the operating system.

Steps in the JDBC connection

1.Load or register the driver with Class.forName().
2.Establish Connection with the database with Connection Interface.
3.Create Statement objects for Queries.
4.Execute the statements, which may or may not return ResultSet.
5.Manipulate the ResultSet.
6.Close the Statements and Connection objects.

Creation of dsn(database source name) for Oracle
 
Start-Control panel- Administrative Tools- Data Sources (ODBC)-go to system dsn tab-click add button-select a driver for which you want to set up data source (for Oracle- Oracle in XE)-select it and click finish-give any name in data source name textbox-then click ok button.

Note:- Here Username=system, Password=pintu and Dsn name=dsn1

Table Creation

create table employee(empno int,empname varchar(50),sal int)

Example:- To insert record into a table

/*To insert record into a table by using Statement*/
import java.sql.*;
import java.util.*;
public class insert
{
public static void main(String args[]) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
Scanner sc=new Scanner(System.in);
System.out.print("Enter the Employee Number :");
int x=sc.nextInt();
System.out.print("Enter the Employee Name : ");
String y=sc.next();
System.out.print("Enter the Employee's salary : ");
int z=sc.nextInt();
Statement stmt=con.createStatement();
stmt.executeUpdate("insert into employee(empno,empname,sal) values("+x+",'"+y+"',"+z+")");
System.out.println("Record Successfully Inserted...");
con.close();
}
}

Compile

Javac insert.java
Java insert

 
Example:- To update record in a table

/*To update record in a table by using Statement*/
import java.sql.*;
import java.util.*;
public class update
{
public static void main(String args[]) throws Exception
{
Scanner sc=new Scanner(System.in);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
Statement stmt=con.createStatement();
System.out.print("Enter the Employee number: ");
int x=sc.nextInt();
System.out.print("Enter the new salary : ");
int z=sc.nextInt();
System.out.print("Enter the Employee name: ");
String y=sc.next();
String sql="update employee set sal="+z+",empname='"+y+"' where empno="+x;
int no=stmt.executeUpdate(sql);
if(no>0)
System.out.println(no+" Records Successfully Updated");
else
System.out.println("Invalid employee number");
con.close();
}
}

Compile

Javac update.java
Java update

 
Example:- To delete record from a table

/*To delete record from a table by using Statement*/
import java.sql.*;
import java.util.*;
public class delete
{
public static void main(String args[]) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Scanner sc=new Scanner(System.in);
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
Statement stmt=con.createStatement();
System.out.print("Enter lower limit salary: ");
int lb=sc.nextInt();
System.out.print("Enter upper limit salary: ");
int ub=sc.nextInt();
String sql="delete from employee where sal between "+lb+" and "+ub;
int no=stmt.executeUpdate(sql);
System.out.println(no +" Records Successfully Deleted...");
con.close();
}
}

Compile

Javac delete.java
Java delete

 
Example:- To select record from a table

/*To select record from a table by using Statement*/
import java.sql.*;
public class select
{
public static void main(String args[]) throws Exception{
 //Step-1
//load the class for driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Step -2
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
//Step -3
System.out.println("Connected to database");
Statement stmt=con.createStatement();
//Step-4
ResultSet rs=stmt.executeQuery("select * from employee");
//Fetching data from ResultSet and display
while(rs.next())
{
//to fetch value from a column having number type of value
int x=rs.getInt("empno");
//to fetch value from a column having varchar/text type of value
String y=rs.getString("empname");
//to fetch value from a column having number type of value
int z=rs.getInt("sal");
System.out.println(x+"   "+y +" "+z);
}
//Step-5
con.close();
}
}

Compile

Javac select.java
Java select



 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend