JDBC connection 8.0 or above version mysql

JDBC connection 8.0 or above version mysql

IDE:idea

prepare in advance:

1. MySQL 8.0 or above has been installed

2. Go to mysql official website to download the JDBC driver (jar package)

After downloading, what we need is the file with the suffix named jar, as shown in the figure below (I downloaded version 8.0.20 here)

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-mGx6mgzR-1623172239028)(https://i.loli.net/2021/06/09/rZm4P3DBe87s1gv.png )]

step:

1. Create a new JDBC project (in fact, it is a folder), I named it myjdbc here

Insert picture description here

2. Create a new libs folder under myjdbc to import the jar package needed to connect to the database. Copy the previously downloaded jar package to the libs directory, this needs to find the path of the project, and then enter the libs to copy. Note that I put my path here. Each person’s project storage location is different, so readers need to find the path where their project is located. The libs folder needs to be created by yourself (there is no libs folder when you just create a new project, and the name of libs is also taken by yourself. , Readers can also use other names)

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-8bLioxYI-1623172130706)(https://i.loli.net/2021/06/09/Mfg6uD94UN57m3G.png )]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-R6xXIOJt-1623172130713)(https://i.loli.net/2021/06/09/fP2ihImYTZJz4DR.png )]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Hu9ysFs9-1623172130719)(https://i.loli.net/2021/06/09/BMYQW45VULsvfzq.png )]

After copying the jar package to the libs folder, you also need to right-click the jar package in the idea and add it as a library to be successful.

[External link image transfer failed. The origin site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-w6vXUELF-1623172130724)(https://i.loli.net/2021/06/09/l59cB4XRDInhPud.png )]

I have already added it as a library here, so I won’t include the screenshot after right-clicking.

Create a database (readers who have already built a database can ignore it):

Press win+R to enter the cmd command to enter the command line program, enter mysql -u root -p, and then enter the password set when installing mysql, enter mysql

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ZYbqM4Pb-1623172130728)(https://i.loli.net/2021/06/09/x5DEavKj2otJ8Hn.png )]

PS: The student who reported an error at this step may not have configured the mysql environment variable, you can search the Internet and come back after setting it.

Here are some commonly used mysql commands:

查看当前有哪些数据库:show databases;
创建数据库:create database 数据库名;
创建数据库时设置字符编码:create database 数据库名称 charset utf8;
查看某个数据库的信息:show create database 数据库名;
进入或切换数据库:use 数据库名;
显示当前数据库:select database();
表相关:
查看当前有哪些表:show tables;
创建表:create table 表名(字段1,字段类型,...);
查看表属性信息:show create table 表名;
查询表中的数据:select * from 表名;
插入数据:insert into 表名(字段1,字段2,...) values('','',''...);
删除数据:delete from 表名 where 条件表达式
更新数据:update 表名 set 属性名 = 修改后的值 where 属性名 条件表达式

Then mysql comes with four databases after installation, these four databases cannot be deleted. Let's create a new database first:

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Q57zSEhL-1623172781615)(https://i.loli.net/2021/06/09/Gyzvth6FLPHUCTD.png )]

I named it myjdbc here. After it is built, you can use the show databasescommand to view which databases are currently available:

[External link image transfer failed. The origin site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-GmB2WRK5-1623172130733)(https://i.loli.net/2021/06/09/PfqlyMeOKDVE4Yu.png )]

Note: Except for myjdbc, which we just created, the other four libraries are already there.

Then use the switch database command to use 数据库名switch to the database myjdbc we just created:

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-rKjC1bMf-1623172130735)(https://i.loli.net/2021/06/09/6w3XJuyQAt4K9hB.png )]

Then create a new table users:

create table 表名(字段1,字段类型,...);

Insert the data again:

insert into 表名(字段1,字段2,...) values('','',''...);

After inserting the data, query the data:

select * from 表名;

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-NHuKYEij-1623172130736)(https://i.loli.net/2021/06/09/S98m73AVepxTfkz.png )]

Then we can write java files in idea to connect to mysql.

Write java files

Create a new java file in the src directory

1. Load and register the database driver
Class.forName("com.mysql.cj.jdbc.Driver");here is a fixed way

2. Obtain the database connection through DriverManager
The specific way to obtain the database connection is as follows:

			String url = "jdbc:mysql://localhost:3306/myjdbc?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
            String username = "root";
            String password = "这里需要改成你自己的密码";

            conn = DriverManager.getConnection(url, username, password);

The myjdbc behind 3306 in the url is the name of the database, which needs to be changed to the name of the database to be connected, and the other places remain unchanged, just copy it directly.

3. Obtaining the Statement object through the Connection object
. There are three ways to create a Statement:

  • createStatement(): Create a basic Statement object
  • prepareStatement(): Create a prepareStatement object
  • prepareCall(): Create a CallableStatement object

Take the creation of a basic Statement object as an example, the creation method is as follows:
Statement stmt = conn.createStatement();

4. Use the Statement object to execute SQL statements.
All Statements have the following 3 methods to execute SQL statements:

  • execute(): can execute any SQL statement
  • executeQuery(): usually execute the query statement, and return the ResultSet object representing the result set after execution
  • executeUpdate(): Mainly used to execute DML and DDL statements. When DML statements are executed, such as insert, update, and delete, the number of rows affected by the SQL statement is returned, and 0 is returned when the DDL statement is executed.
            String sql = "select * from users"; //这里的sql语句可以改
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql); //结果集

5. Operating the ResultSet result set
If the executed SQL statement is a query statement, the execution result will return a ResultSet object in which the query result of the SQL statement is stored. The program can retrieve the query result by operating the ResultSet object.
6. Close the connection and release resources.

So far, the general implementation steps and completion of the JDBC program.

Let's put a complete code for querying data:
The program reads data from the table users that we have built before, and prints the results on the console.

public static void main(String[] args) throws Exception{
        Connection conn = null;
        Statement stmt = null;
        //PreparedStatement preStmt = null;
        ResultSet rs = null;

        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/myjdbc?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
            String username = "root";
            String password = "root";

            conn = DriverManager.getConnection(url, username, password);
            String sql = "select * from users";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            System.out.println("id\tname\tpassword\temail\tbirthday");
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String psw = rs.getString("password");
                String email = rs.getString("email");
                String birthday = rs.getString("birthday");
                System.out.println(id + "\t" + name + "\t" + psw + "\t" + email + "\t"
                + birthday);
            }
//            String sql = "insert into users(name, password, email, birthday)"
//                    + "values(?, ?, ?, ?)";

            //1.创建执行sql语句的prepareStatement对象
//            preStmt = conn.prepareStatement(sql);
//            //2.为sql语句中的参数赋值
//            preStmt.setString(1, "zl");
//            preStmt.setString(2, "123456");
//            preStmt.setString(3, "[email protected]");
//            preStmt.setString(4, "1789-12-23");
//            //3.执行sql语句
//            preStmt.executeUpdate();
        }
        catch (Exception e){
            e.printStackTrace();;
        }
        finally {
            if(rs != null){
                try {
                    rs.close();
                }
                catch (Exception e){
                    e.printStackTrace();
                }
                rs = null;
            }
            if(stmt != null){
                try {
                    stmt.close();
                }
                catch (Exception e){
                    e.printStackTrace();
                }
                stmt = null;
            }
//            if(preStmt != null){
//                try {
//                    preStmt.close();
//                }
//                catch (Exception e){
//                    e.printStackTrace();
//                }
//                preStmt = null;
//            }
            if(conn != null){
                try {
                    conn.close();
                }
                catch (Exception e){
                    e.printStackTrace();
                }
                conn = null;
            }
        }


    }

Run result: As

Insert picture description here


you can see, this is the same as the result we accessed in the command line before:

Insert picture description here