Java self study - add, delete and modify JDBC

Add, delete and modify JDBC

CRUD is the most common database operation, that is, adding, deleting, modifying and querying
C add (Create)
R read query (Retrieve)
U update
D delete

In JDBC, the operations of adding, deleting and modifying are very similar, just passing different SQL statements.

Step 1: add

This is the code in Hello JDBC for inserting data into the database

package jdbc;
   
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
   
public class TestJDBC {
    public static void main(String[] args) {
   
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
   
        try (
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
                "root", "admin");
            Statement s = c.createStatement();             
        )
        {
            String sql = "insert into hero values(null," + "'Ti Mo'" + "," + 313.0f + "," + 50 + ")";
            s.execute(sql);
               
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Step 2: delete

Deletion and addition are similar, but the executed SQL statements are different

package jdbc;
   
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
   
public class TestJDBC {
    public static void main(String[] args) {
   
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
   
        try (
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
                "root", "admin");
            Statement s = c.createStatement();             
        )
        {
            String sql = "delete from hero where id = 5";
            s.execute(sql);
               
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Step 3: modify

It's the same with the modification. Just execute another SQL statement

package jdbc;
   
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
   
public class TestJDBC {
    public static void main(String[] args) {
   
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
   
        try (
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
                "root", "admin");
            Statement s = c.createStatement();             
        )
        {
            String sql = "update hero set name = 'name 5' where id = 3";
            s.execute(sql);
               
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Practice: Add, delete, change

Design a method

public static void execute(String sql)

The parameter accepted by method is SQL statement. Whether it is added, deleted, or modified, this method will be called, and different SQL statements will be passed as parameters each time

Answer:

package jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class TestJDBC {
 
    public static void execute(String sql) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
 
        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
                "root", "admin"); Statement s = c.createStatement();) {
 
            s.execute(sql);
 
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    public static void main(String[] args) {
        execute("delete from hero where id = 1");
    }
}

Tags: Java SQL JDBC MySQL

Posted on Sat, 14 Mar 2020 10:00:14 -0700 by php4tric