1, What is JDBC

JDBC(Java DataBase Connectivity) is a java API for executing sql statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in java language. The essence is a set of specifications officially provided to help developers quickly connect different relational databases.

2, How to use JDBC

1. Download driver
https://dev.mysql.com/downloads/connector/j/
If necessary, you can download the latest version or the historical version (click Archives). I choose version 5.1.49.

After downloading, reduce the compression and copy mysql-connector-java-5.1.49.jar. We will use this package in the project below.

3, Quick start (seven steps)

1. Import jar package:
Copy the jar package just copied to the in the project. Create a libs folder and copy it to this folder. And add it to the library reference class library. Right click Add as Library
2. Register driver

Class.forName("com.mysql.jdbc.Driver"); 
//After MySQL 5, you can omit to register the driver. The configuration file java.sql.Driver has been loaded.

3. Get connection

String url="jdbc:mysql://127.0.0.1:3306/helloworld";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password);

4. Get performer object

Statement stat = con.createStatement();

5. Execute the sql statement and receive the returned results

String sql = "SELECT * FROM user";
ResultSet rs = stat.executeQuery(sql);

6. Processing results

while(rs.next()) {
    System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
}

7. Release resources

con.close();
stat.close();
rs.close();

We create a Bean file in the project, which corresponds to the admin table in the database helloworld.

 public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://127.0.0.1:3306/helloworld";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);
        PreparedStatement pst = con.prepareStatement("SELECT * FROM admin");
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            Admin ad = new Admin();
            ad.setId(rs.getInt("id"));
            ad.setUsername(rs.getString("username"));
            ad.setPassword(rs.getString("password"));
            System.out.println(ad);
        }
        rs.close();
        pst.close();
        con.close();
    }

4, Introduction to function classes

1. DriverManager: drive management objects

Tell the program which database driver to use
The driver jar package after mysql5 can omit the step of registering the driver. In the jar package, there is a java.sql.Driver configuration file, which specifies com.mysql.jdbc.Driver.
Get database connection
static Connection getConnection(String url, String user, String password);
Return value: Connection database Connection object

• url: Specifies the path of the connection. Syntax: jdbc:mysql://ip Address (domain name): port number / database name
• User: user name
• Password: password

2. Connection: database connection object

Get performer object

• Get the ordinary performer object: Statement createStatement();
• Get the precompiled performer object: PreparedStatement prepareStatement(String sql);

Management services

• Start transaction: setAutoCommit(boolean autoCommit); If the parameter is false, the transaction is started.
• Commit transaction: commit();
• Rollback transaction: rollback();

Release resources

• Release the database connection object immediately: void close();

3. Statement: the object that executes the sql statement

Execute DML statement: int executeUpdate(String sql);

• Return value int: returns the number of affected rows.
• Parameter sql: insert, update and delete statements can be executed.

Execute DQL statement: ResultSet executeQuery(String sql);

• Return value ResultSet: encapsulates the result of the query.
• Parameter sql: select statements can be executed.

Release resources

• Release the performer object immediately: void close();

4. ResultSet: result set object

Execute DML statement: int executeUpdate(String sql);

• Return value int: returns the number of affected rows
• Parameter sql: insert, update and delete statements can be executed.

Execute DQL statement: ResultSet executeQuery(String sql);

• Return value ResultSet: encapsulates the result of the query.
• Parameter sql: select statements can be executed.

Release resources

• Release the performer object immediately: void close();

5, Add, delete, modify and query

The presentation layer invokes the control layer - > the control layer invokes the Service layer - > the business layer invokes the data access layer (Dao). The domain layer usually represents the javaBean corresponding to the database table - one-to-one.
The project directory is as follows:

1. Control layer

public class AdminController {
    private AdminServiceImpl server = new AdminServiceImpl();
//    Query all administrators
    @Test
    public void findAll(){
        ArrayList<Admin> all = server.findAll();
        for (Admin ad:all){
            System.out.println(ad);
        }
    }
//    Condition query: query student information according to id
    @Test
    public void findById(){
        Admin ad = server.findById(2);
        System.out.println(ad);
    }
    //Add administrator
    @Test
    public void insert(){
        Admin admin = new Admin(5,"aa","123");
        Integer insert = server.insert(admin);
        System.out.println(insert);
    }
    //Modify administrator
    @Test
    public void update(){
        Admin admin = server.findById(3);
        admin.setUsername("lvmanba");
        Integer update = server.update(admin);
        if(update!=0){
            System.out.println("Modified successfully");
        }else{
            System.out.println("Modification failed");
        }
    }
    //Delete administrator
    @Test
    public void delete(){
        Integer delete = server.delete(5);
        if(delete!=0){
            System.out.println("Delete succeeded");
        }else{
            System.out.println("Deletion failed");
        }
    }
}

2. Business layer service
AdminService.java

public interface AdminService {
    //Query all administrators
    public abstract ArrayList<Admin> findAll();
    //Query administrator by criteria
    public abstract Admin findById(Integer id);
    //Add administrator
    public abstract Integer insert(Admin ad);
    //Modify administrator
    public abstract Integer update(Admin ad);
    //Delete administrator
    public abstract Integer delete(Integer id);
}

AdminServiceImpl.java

public class AdminServiceImpl implements AdminService {
    private AdminDao dao = new AdminDaoImpl();
    @Override
    public ArrayList<Admin> findAll() {
        return dao.findAll();
    }
    @Override
    public Admin findById(Integer id) {
        return dao.findById(id);
    }
    @Override
    public Integer insert(Admin ad) {
        return dao.insert(ad);
    }
    @Override
    public Integer update(Admin ad) {
        return dao.update(ad);
    }
    @Override
    public Integer delete(Integer id) {
        return dao.delete(id);
    }
}

3. Data access layer (Dao)
The code of AdminDao.java is the same as that of AdminService.java. Omit
AdminDaoImpl.java

public class AdminDaoImpl implements AdminDao {
    @Override
    public ArrayList<Admin> findAll() {
        ArrayList<Admin> list = new ArrayList<>();
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        String url="jdbc:mysql://127.0.0.1:3306/helloworld";
        String username = "root";
        String password = "root";
        try {
            //Get connection
            con = DriverManager.getConnection(url, username, password);
            //Get performer object
            sta = con.createStatement();
            String sql = "SELECT * FROM admin";
            //Execute sql
            res = sta.executeQuery(sql);
            //Processing results
            while (res.next()){
                Admin admin = new Admin();
                admin.setId(res.getInt("id"));
                admin.setUsername(res.getString("username"));
                admin.setPassword(res.getString("password"));
                list.add(admin);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(res!=null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (sta!=null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    @Override
    public Admin findById(Integer id) {
        Admin admin = new Admin();
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        String url="jdbc:mysql://127.0.0.1:3306/helloworld";
        String username = "root";
        String password = "root";
        try {
            //Get connection
            con = DriverManager.getConnection(url, username, password);
            //Get performer object
            sta = con.createStatement();
            String sql = "SELECT * FROM admin WHERE id ='"+ id +"'";
            //Execute sql
            res = sta.executeQuery(sql);
            //Processing results
            while (res.next()){
                admin.setId(res.getInt("id"));
                admin.setUsername(res.getString("username"));
                admin.setPassword(res.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(res!=null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (sta!=null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return admin;
    }
    @Override
    public Integer insert(Admin ad) {
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        Integer result = 0;
        String url="jdbc:mysql://127.0.0.1:3306/helloworld";
        String username = "root";
        String password = "root";
        try {
            //Get connection
            con = DriverManager.getConnection(url, username, password);
            //Get performer object
            sta = con.createStatement();
            String sql = "INSERT INTO admin VALUES ('"+ad.getId()+"','"+ad.getUsername()+"','"+ad.getPassword()+"')";
            //Execute sql
            //res = sta.executeQuery(sql);
            result = sta.executeUpdate(sql);
            //Processing results
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(res!=null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (sta!=null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
    @Override
    public Integer update(Admin ad) {
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        int result = 0;
        String url="jdbc:mysql://127.0.0.1:3306/helloworld";
        String username = "root";
        String password = "root";
        try {
            //Get connection
            con = DriverManager.getConnection(url, username, password);
            //Get performer object
            sta = con.createStatement();
            String sql = "UPDATE admin SET id='"+ ad.getId() +"',username='"+ ad.getUsername() +"',password='"+ ad.getPassword() +"' WHERE id ='"+ad.getId()+"'";
            //Execute sql
            //res = sta.executeQuery(sql);
            result = sta.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(res!=null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (sta!=null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
    @Override
    public Integer delete(Integer id) {
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        Integer result = 0;
        String url="jdbc:mysql://127.0.0.1:3306/helloworld";
        String username = "root";
        String password = "root";
        try {
            //Get connection
            con = DriverManager.getConnection(url, username, password);
            //Get performer object
            sta = con.createStatement();
            String sql = "DELETE FROM admin WHERE id ='"+ id +"'";
            //Execute sql
            result = sta.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(res!=null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (sta!=null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
}

6, Write JDBC tool class optimization code

1. Write the configuration file: it is located in the src Directory: the file name is: config.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/helloworld
username = "root";
password = "root";

2. Build tool class: JDBCUtils.java under utils directory

public class JDBCUtils {
    //1. Private constructor: the purpose is not to let other classes create objects
    private JDBCUtils(){}
    //Tool classes, then the methods are static.
    //2. Declare the required configuration variables
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;
    private static Connection con;
    //3. Provide static code blocks, read the information of the configuration file, assign values to variables, and register drivers
    static {
        //Read the information of the configuration file and assign a value to the variable | InputStream in = getClass().getResourceAsStream("resource Name")

        try {
            //Get by classloader
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
            Properties prop = new Properties();
            prop.load(is);

            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            //Register driver
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //4. Provides methods for obtaining data connections
    public static Connection getConnection() {
        try {
            con = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
    //5. Provides a way to free up resources
    public static void close(Connection con, Statement stat, ResultSet rs) {// Applicable to query
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection con, Statement stat) {// Use, addition, deletion and modification
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Replace the redundant code in AdminDaoImpl.java:

public class AdminDaoImpl implements AdminDao {
    @Override
    public ArrayList<Admin> findAll() {
        ArrayList<Admin> list = new ArrayList<>();
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            //Get connection
            con = JDBCUtils.getConnection();
            //Get performer object
            sta = con.createStatement();
            String sql = "SELECT * FROM admin";
            //Execute sql
            res = sta.executeQuery(sql);
            //Processing results
            while (res.next()){
                Admin admin = new Admin();
                admin.setId(res.getInt("id"));
                admin.setUsername(res.getString("username"));
                admin.setPassword(res.getString("password"));
                list.add(admin);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con,sta,res);
        }
        return list;
    }
    @Override
    public Admin findById(Integer id) {
        Admin admin = new Admin();
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            //Get connection
            con = JDBCUtils.getConnection();
            //Get performer object
            sta = con.createStatement();
            String sql = "SELECT * FROM admin WHERE id ='"+ id +"'";
            //Execute sql
            res = sta.executeQuery(sql);
            //Processing results
            while (res.next()){
                admin.setId(res.getInt("id"));
                admin.setUsername(res.getString("username"));
                admin.setPassword(res.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con,sta,res);
        }
        return admin;
    }
    @Override
    public Integer insert(Admin ad) {
        Connection con = null;
        Statement sta = null;
        Integer result = 0;
        try {
            //Get connection
            con = JDBCUtils.getConnection();
            //Get performer object
            sta = con.createStatement();
            String sql = "INSERT INTO admin VALUES ('"+ad.getId()+"','"+ad.getUsername()+"','"+ad.getPassword()+"')";
            //Execute sql
            //res = sta.executeQuery(sql);
            result = sta.executeUpdate(sql);
            //Processing results
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con,sta);
        }
        return result;
    }
    @Override
    public Integer update(Admin ad) {
        Connection con = null;
        Statement sta = null;
        int result = 0;
        try {
            //Get connection
            con = JDBCUtils.getConnection();
            //Get performer object
            sta = con.createStatement();
            String sql = "UPDATE admin SET id='"+ ad.getId() +"',username='"+ ad.getUsername() +"',password='"+ ad.getPassword() +"' WHERE id ='"+ad.getId()+"'";
            //Execute sql
            //res = sta.executeQuery(sql);
            result = sta.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con,sta);
        }
        return result;
    }
    @Override
    public Integer delete(Integer id) {
        Connection con = null;
        Statement sta = null;
        Integer result = 0;
        try {
            //Get connection
            con = JDBCUtils.getConnection();
            //Get performer object
            sta = con.createStatement();
            String sql = "DELETE FROM admin WHERE id ='"+ id +"'";
            //Execute sql
            result = sta.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
           JDBCUtils.close(con,sta);
        }
        return result;
    }
}