Performing Database Operations Java Sql Insert Update Delete Select

Share:
Performing Database Operations in Java |SQL
INSERT,UPDATE,DELETE and SELECT.
This post is going to help you in learning how to do basic database operations using JDBC(Java Databse Connectivity) API.These basic operations are INSERT,UPDATE,SELECT and DELETE statements in SQL.Here we are going to use MySql DataBase.

Prerequisites:
-Eclipse IDE or Any
-JDK
-MySql Connector Jar File.

Creating Database:
-use the following statement
create database database_name; 
Here i want my database name as thetechmatin;

so execute the following statement
create database thetechmatin;
That's all for Database Creation.

Now create a Table give name as users,execute the below statement.
create table users(Fname varchar(20),Lname varchar(20));

So now we have a database thetechmatin and a Table users with    column Fname and Lname.
   ----------------------------
   |    Fname     |   Lname |
    ---------------------------
Now first we are going to perform INSERT Operation from java application.
-So a create project in Eclipse IDE,give any name for example JDBCOperations.
-Add MySql Connector Jar File.
-To add jar file to your project right click on project 1)now click on configure build path,2)click on Add External Jars,3)Nowfind your downloaded mysql connector jar file and double click  on that jar file and say apply and close.

-Now Register the Driver Class.
To register the driver class use the below code
Class.forName("com.mysql.jdbc.Driver");

-Create the connection.
To create the connection with the Database use the below code
Use DriverManager Class Factory Method getConnection(); which returns the instance of Connection Interface.
Connection conn=DriverManager.getConnection("url","username","password");

-Create the statement.
-To create the statement use instance of Connection interface which you got in previous step,call createStatement() methos or PreparedStateemt() method using the conn instance.
conn.createStatement(); or conn.prepareStatement();//recommended
It will returns the instance of Statement OR PreparedStatement Interface,it will help us to execute the query using its factory methods i.e.,execute(),executeQuery(),executeUpdate() using these method we can get the instance of ResultSet Interface which  will help us to get the data from the table.

See Below Example For Better UnderStanding
Now create a class give name as JDBCOperationsClass.
  1. public class JDBCOperationsClass{
  2. public static void main(String args[]){
  3. try {
  4. Class.forName("com.mysql.jdbc.Driver");
  5. Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/tech      matin","root","");

  6. String query="INSERT INTO users('matin','mansoori') VALUES(Fname,Lname)";                              PreparedStatement ps=conn.prepareStatement(query);
  7. ps.executeUpdate();
  8. System.out.println("Inserted");
  9. ps.close();
  10. conn.close();
  11. }//end of try
  12. catch(Exception exx){
  13. System.out.print(exx.getMessage());
  14. }//end of catch
  15. }//end of main
  16. }//end of class  
NOW PERFORMING SELECT OPERATION TO FETCH RECORDS FROM TABLE.
  1. public class JDBCOperationsClass{
  2. public static void main(String args[]){
  3. try{
  4. //registering the driver class
  5. Class.forName("com.mysql.jdbc.Driver");
  6. //creating connection
  7. Connection conn
  8. =DriverManager.getConnection("jdbc:mysql://localhost:3306/thetechmatin","root","");
  9. //writing query
  10. String query="Select DISTINCT(Fname) from users";         
  11. PreparedStatement ps=conn.prepareStatement(query);
  12. ResultSet rs=ps.executeQuery();
  13. while(rs.next()){
  14. String name=rs.getString("Fname");
  15. System.out.println(name);
  16. }//end of while
  17.     ps.close();
  18.     conn.close();
  19. }//end of try
  20. catch(Exception exx){
  21. System.out.print(exx.getMessage());
  22. }//end of catch
  23. }//end of main
  24. }//end of class
NOW PERFORMING UPDATE OPERATION TO UPDATE DATA INTABLE.
  1. public class JDBCOperationsClass{
  2. public static void main(String args[]){
  3. try{
  4. Class.forName("com.mysql.jdbc.Driver");
  5. Connection conn
  6. =DriverManager.getConnection("jdbc:mysql://localhost:3306/thetechmatin","root","");

  7. String query="UPDATE users set Fname='Dilshad' where Fname='matin' ";                               
  8. PreparedStatement ps=conn.prepareStatement(query);
  9. ps.executeUpdate();
  10. System.out.println("Updated");        
  11. ps.close();
  12. conn.close();
  13. }//end of try
  14. catch(Exception exx){
  15. System.out.print(exx.getMessage());
  16. }//end of catch
  17. }//end of main
  18. }//end of class
NOW PERFORMING DELETE OPERATION TO DELETE ALL DATA FROM A TABLE.
  1. public class JDBCOperationsClass{
  2. public static void main(String args[]){
  3. try{
  4. Class.forName("com.mysql.jdbc.Driver");
  5. Connection conn
  6. =DriverManager.getConnection("jdbc:mysql://localhost:3306/thetechmatin","root","");

  7. String query="DELETE * FROM users";                               
  8. PreparedStatement ps=conn.prepareStatement(query);
  9. ps.executeUpdate();
  10. System.out.println("Deleted");        
  11. ps.close();
  12. conn.close();
  13. }//end of try
  14. catch(Exception exx){
  15. System.out.print(exx.getMessage());
  16. }//end of catch
  17. }//end of main
  18. }//end of class
Hope this article will help you to understand basic SQL Database Operations using a JAVA Application.


3 comments:

  1. column name must be fname, lname and values must be matin, mansoori

    String query="INSERT INTO users('matin','mansoori') VALUES(Fname,Lname)";

    ReplyDelete
    Replies
    1. it must have any conditions
      DELETE * FROM users

      Delete
  2. thanks for making this blog.. u made easy to learn and understand .. thank you so much

    ReplyDelete

You May Like Also

Programming Knowledge


Java JDK
Reversing String in Java
Java Generic Concept With Simple Example
Java Generic Objects Behavior In Non-Generic Area
Creating Connection With MySql Databse Using Java Application
Performing Database Operations Java MySql Insert Update Delete Select
Java Inserting Data in MySql Databse Table
Java Basic Login System Using MySql Database Table
JSP Login System Using MySql Databse
Jsp Servlet Login And Logout System Using Mysql Database Table
What is jdk in Java?
Exaplain inheritance in Java with example?
Exaplain polymorphism in Java with example?
Exaplain abstraction in Java with example?
Exaplain encapsulation in Java with example?
What is the use of this keyword in Java?
Explain ArrayList With Example
Explain LinkedList With Example
Explain Vector Class With Example
Explain Vector Class With Example
Generics in java with example
Behavior of generic objects
How can i pass generic class to a method in java
How to create a login system in jsp using mysql database?
How to create a login and logout system in jsp & servlet using mysql database?
How to connection a simple Java application with mysql database?
How to insert values in mysql databse usiong Java applications?
Perform insertion, retrieval, updatation and delete operations?
How to create a simple login system in java using mysql database?

PHP Login System Using MySql Database
PHP Complete Login System With Session And Logout Using MySql Database