Performing Database Operations Java Sql Insert Update Delete Select

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. 
   
7. String query="INSERT INTO users('matin','mansoori') VALUES(Fname,Lname)";                              PreparedStatement ps=conn.prepareStatement(query);
8. ps.executeUpdate();
9. System.out.println("Inserted");
10. ps.close();
11. conn.close();
12. }//end of try
13. catch(Exception exx){
14. System.out.print(exx.getMessage());
15. }//end of catch
16. }//end of main
17. }//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. 
   
8. String query="UPDATE users set Fname='Dilshad' where Fname='matin' ";                               
9. PreparedStatement ps=conn.prepareStatement(query);
10. ps.executeUpdate();
11. System.out.println("Updated");        
12. ps.close();
13. conn.close();
14. }//end of try
15. catch(Exception exx){
16. System.out.print(exx.getMessage());
17. }//end of catch
18. }//end of main
19. }//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. 
   
8. String query="DELETE * FROM users";                               
9. PreparedStatement ps=conn.prepareStatement(query);
10. ps.executeUpdate();
11. System.out.println("Deleted");        
12. ps.close();
13. conn.close();
14. }//end of try
15. catch(Exception exx){
16. System.out.print(exx.getMessage());
17. }//end of catch
18. }//end of main
19. }//end of class

Hope this article will help you to understand basic SQL Database Operations using a JAVA Application.