.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 >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Dabase Operation in JAVA

Posted By:Abhisek Panda       Posted Date: October 30, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  
 

DataBase Opereation 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 insert, update and delete:-
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 is 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 Record Retrieval:-
import java.sql.onnection;
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:-
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 retrieval 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.


 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