1. Define your own database and data table
2. Access the defined data table through JDBC and insert 2 records.
Requirements: 1. Submit SQL statements defined in database and data table;
2. Submit program implementation process;
3. Submit program running results.
This is a small class assignment assigned by my teacher.

What is JDBC

JDBC(Java DataBase Connectivity) literal translation: the operation of database in java language is actually a set of rules defined by the official (sun company) to operate all relational databases, i.e. interfaces. Various database manufacturers implement this set of interfaces and provide database driven jar packages. We can use this set of interfaces (JDBC) for programming, and the real executed code is the implementation classes in the driven jar packages.

step

The following is the IDEA import driver jar package
0. Create the project directory libs (this directory name can be taken at will)

1. Right click item – > click New – > Click directory – > enter name – > enter or confirm;
   

1. Import the driver jar package
0. Download. jar
Download from the official website
1. Copy. jar to the created libs project directory

2.Right click libs---->Add as library( Add As library)Really put jar Package load in

2. Register driver

String driver = "com.mysql.cj.jdbc.Driver";
Class.forName(driver);

3. Get the database Connection object Connection
4. Define sql statements
5. Get the object statement that executes the sql statement
6. Execute sql and accept the returned results
7. Treatment results
8. Release resources

data sheet

I use Navicat to create databases and tables and insert data. I won't say much here.

Error analysis and Solutions

The following two wrong solutions

Error 1. java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'
Error 2.com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Could not create connection to database server
Error 3. Loading class com. Mysql. JDBC. Driver '. This is predicted. The new driver class ISCOM. Mysql. CJ. JDBC. Driver'. The driver is automatically registered via the SPI and manual loading

Error 1. This is because the database encryption methods of MySQL versions 5. * and 8. * are different.

**Solution: * * Don't trust the Internet to modify the database. It will mess up the database.
Go to the official website to download the new. jar package.

Error 2. The driver and database do not match.
Solution: go to the official website to download the new. jar package.

Error: the registered driver password of version 3.8. * has been changed.
Solution: in version 8. * of the registered driver command, change com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver

code:

package rg.zy.jdbc;

import java.sql.*;

public class Main {
    public static void main(String[] args)  {


        String driver = "com.mysql.cj.jdbc.Driver";
        String URL = "jdbc:mysql://localhost:3306/rg2db";

        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        try {
             conn =DriverManager.getConnection(URL, "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql=null;
        Statement stmt=null;
        ResultSet rs=null;
        try {
            stmt= conn.createStatement();

        //check
        System.out.println("Table before addition, deletion and modification:");
        sql = "select *from lesson";
        rs = stmt.executeQuery(sql);

        while (rs.next()) {
            System.out.print(rs.getInt("id") + " ");
            System.out.print(rs.getString("name") + " ");
            System.out.print(rs.getString("teacher") + " ");
            System.out.print(rs.getInt("classhour")+" ");
            System.out.print(rs.getString("date") + " ");
            System.out.println("");
        }
        System.out.println("");
        //increase
        System.out.println("Add a row of data to the table: ID+compile+Zhangxizhi+64+time");
        sql="insert lesson(name,teacher,classhour,date) values('compile','Zhangxizhi',64,'"+ DateUtil.getStringDate()+"')";
        int count=stmt.executeUpdate(sql);
        //result
        System.out.print("Operation results:  ");
        if (count>0){
            System.out.println("Added successfully");
        }else{
            System.out.println("Add failed");
        }
        System.out.println("");
        //Delete
        System.out.println("delete ID The line for 3");
        sql="delete from lesson where id=3";
        count=stmt.executeUpdate(sql);
        //result
        System.out.print("Operation results:  ");
        if (count>0){
            System.out.println("Delete succeeded");
        }else{
            System.out.println("Deletion failed");
        }
        System.out.println("");
        //change
        System.out.println("hold ID 4 hours(classhour)Change to 32");
        sql ="update lesson set classhour=32 where id=4";
        count=stmt.executeUpdate(sql);
        //Processing results
        System.out.print("Operation results:  ");
        if (count>0){
            System.out.println("Modified successfully");
        }else{
            System.out.println("Modification failed");
        }
        System.out.println("");
        //check
        System.out.println("Table after addition, deletion and modification:");
        sql = "select *from lesson";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.print(rs.getInt("id") + " ");
            System.out.print(rs.getString("name") + " ");
            System.out.print(rs.getString("teacher") + " ");
            System.out.print(rs.getInt("classhour")+" ");
            System.out.print(rs.getString("date") + " ");
            System.out.println("");
        }
        rs.close();
        stmt.close();
        conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}