Basic SQL Operations Using Java

Today's post, we are going to see how to make an application that interacts with a database. We will learn how to CREATE, READ, UPDATE, and DELETE rows on MySQL the database application we are going to use. 

• CREATE operation creates a new row to the specified table. This operation is being used to register a new account to a website. 
• READ operation returns rows from the specified table. This is mostly used to retrieve information searched by the user.
• UPDATE operation updates certain numbers of row to the table. Programmers use this operation to edit an information. Example usage is updating the member's last name and age.
• DELETE operation deletes a row from a table. Usage included account deactivation.

For our future tutorials, we will be creating a database with tables we're going to feed it with a dummy information. But for now, we will see a very basic structure of a database table:

Table Name: members

| ID | first_name | last_name | age |
-----------------------------------------
| 1  | John          |  Collin       | 24   |
-----------------------------------------
| 2  | Peter         | Guy          | 24   |
-----------------------------------------
| 3  | Brook        | Hill           | 13   |
-----------------------------------------
| 4  | James        | Duff         | 12   |
-----------------------------------------

How to search using SELECT SQL operation: Prints all members that has an age of 24.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Searcher{

public static void search(int age){
  try{
   Class.forName("com.mysql.jdbc.Driver");
   Connection connect = DriverManager.getConnection(CONNECTION);
   Statement statement = connect.prepareStatement("SELECT * from members where age=?");
   statement.setInt(1, age);
   ResultSet resultSet = statement.executeQuery();
   
   while(resultSet.next()){
    System.out.println("Name: " + resultSet.getString("name");
   }
   connect.close();
   statement.close();
   resultSet.close();
  }catch(Exception e){
   e.printStackTrace();
  }
}

 public static void main(String args[]){
                System.out.println("Members with age 24");
  Searcher.search(24); //we want to print all members with age 24.
 }
}

How to create row using CREATE SQL operation: Adds member to a members table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Adder{

 public void addMember(String first_name, String last_name, int age){
  try{
   Class.forName("com.mysql.jdbc.Driver");
   connect = DriverManager.getConnection(CONNECTION);
   statement = connect.prepareStatement("INSERT INTO members (first_name, last_name, age) VALUES (?,?,?)");
   statement.setString(1, first_name);
   statement.setString(2, last_name);
   statement.setInt(3, age);
   statement.executeUpdate();
   connect.close();
   statement.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }

 public static void main(String args[]){
  Adder.addMember("Jonas", "Robinso", 25);
                System.out.println("A member has been added!");
 }
}

How to update row using UPDATE SQL operation: Edits member to a members table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Updater{

 public void update(String first_name){
  try{
   Class.forName("com.mysql.jdbc.Driver");
   connect = DriverManager.getConnection(CONNECTION);
   statement = connect.prepareStatement("UPDATE members SET age=" + 25 + " WHERE first_nam='" + first_name + "'");
   statement.executeUpdate();
   connect.close();
   statement.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }

 public static void main(String args[]){
  Updater.update("Brook");
 }
}

How to delete a row using DELETE SQL operation: Deletes a member from a table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Deleter{

 public void delete(String first_name){
  try{
   Class.forName("com.mysql.jdbc.Driver");
   connect = DriverManager.getConnection(CONNECTION);
   statement = connect.prepareStatement("DELETE from members where first_name='" + first_name + "'");
   statement.executeUpdate();
   connect.close();
   statement.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }

 public static void main(String args[]){
  Deleter.delete("Brook");
 }
}

It is strongly recommended to run these classes on your favorite Java IDE like and play around until you get the hang of it and make a cool ideas for your next Java application :)