DataBase Operation Using JDBC
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 tne 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.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|