Java基础系列36-Java操作MySQL数据库

一. 环境准备

工具：idea、MySQL、MySQL连接驱动：mysql-connector-java-5.1.45.jar

1.1 MySQL连接驱动下载

一般是在Oracle官方网站进行下载:
https://downloads.mysql.com/archives/c-j/

要选择对应的版本，我这边选择 5.1.49版本

1.2 MySQL数据库安装

MySQL数据库之前已经安装，此处略过步骤

IP ： 10.31.1.119
端口 : 3306
用户名: root
密码 : 3306
数据库: test

1.3 idea搭建Java工程

1. 用idea搭建一个Java工程，如下所示:
   

2. idea导入jar包
   

然后选中第一步的MySQL 连接的jar包

我们可以看到外部依赖的jar包

二.码代码

代码:
mysqlConnect

package com.zqs.mysql.crud;

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

/**
 * @author  只是甲
 * @date    2021-07-27
 * @remark  Java连接MySQL
 */

public class mysqlConnect {
    public static final String url =  "jdbc:mysql://10.31.1.119/test";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "abc123";

    public Connection conn = null;
    public PreparedStatement pst = null;

    public mysqlConnect(String sql){
        try{
            Class.forName(name);//指定连接类型
            conn = DriverManager.getConnection(url,user,password);//获取连接
            pst = conn.prepareStatement(sql);//准备执行语句
        } catch (Exception e){
            e.printStackTrace();
        }

    }

    public void close(){
        try {
            this.conn.close();
            this.pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

Demo1

package com.zqs.mysql.crud;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author  只是甲
 * @date    2021-07-27
 * @remark  Java连接MySQL
 */


public class Demo1 {
    static String sql = null;
    static mysqlConnect db1 = null;
    static ResultSet ret = null;

    public static void main(String[] args) {
        sql = "select empno, ename, sal from emp";//sql语句
        db1 = new mysqlConnect(sql);//创建mysqlConnect对象

        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            while (ret.next()){
                String uid = ret.getString(1);
                String uname = ret.getString(2);
                String usal = ret.getString(3);
                System.out.println(uid + "\t"  + uname + "\t" + usal);
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}

测试记录:

"C:\Program Files\Java\jdk1.8.0_144\bin\java.exe" "-javaagent:E:\Program Files\JetBrains\IntelliJ IDEA 2020.1\lib\idea_rt.jar=56359:E:\Program Files\JetBrains\IntelliJ IDEA 2020.1\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_144\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\jxcell.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_144\jre\lib\rt.jar;C:\Users\Administrator\IdeaProjects\MySQL_CRUD\out\production\MySQL_CRUD;E:\迅雷下载\mysql-connector-java-5.1.49\mysql-connector-java-5.1.49-bin.jar" com.zqs.mysql.crud.Demo1
Tue Jul 27 12:17:25 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
7369	SMITH	800.00
7499	ALLEN	1600.00
7521	WARD	1250.00
7566	JONES	2975.00
7654	MARTIN	1250.00
7698	BLAKE	2850.00
7782	CLARK	2450.00
7788	SCOTT	3000.00
7839	KING	5000.00
7844	TURNER	1500.00
7876	ADAMS	1100.00
7900	JAMES	950.00
7902	FORD	3000.00
7934	MILLER	1300.00

Process finished with exit code 0

参考:

1. https://www.cnblogs.com/town123/p/8336244.html