Java中的MySQL操作

1. 安装MYSQL

1. 版本选择：5.7版
2. 下载：mysql-installer-community-5.7.22.1.msi
3. 安装， root密码：1234
4. 环境变量配置
   • MYSQL_HOME： C:\Program Files\MySQL\MySQL Server 5.7
   • Path： %MYSQL_HOME%\bin

2. 安装客户端 SQLyog

1. 下载：SQLyog-12.4.1.exe
2. 安装
3. 创建试验数据库：

CREATE DATABASE DB_Rao;

USE DB_Rao;

CREATE TABLE users(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(40),

PASSWORD VARCHAR(40),

email VARCHAR(60),

birthday DATE

)CHARACTER SET utf8 COLLATE utf8_general_ci;


INSERT INTO users(NAME,PASSWORD,email,birthday) VALUES('张三','123456','zs@sina.com','1980-12-04');

INSERT INTO users(NAME,PASSWORD,email,birthday) VALUES('李四','123456','lisi@sina.com','1981-12-04');

INSERT INTO users(NAME,PASSWORD,email,birthday) VALUES('王五','123456','wangwu@sina.com','1979-12-04');

SELECT * FROM users;

3. JDBC环境搭建

1. 下载mysql数据库驱动jar包：mysql-connector-java-5.1.37-bin.jar,  链接：https://pan.baidu.com/s/1ivYm-Y1DOE3rp3UDlPbuPQ 提取码：lo4k
2. 在项目下新建文件夹lib，用于存放jar包
3. 将mysql驱动jar包复制到lib下
4. 选中lib，右键，Add as Library...

4. 样例程序

六大步骤，缺一不可。代码如下：

import com.mysql.jdbc.Driver;

import java.sql.*;

public class FirstDB {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //2.创建链接
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/DB_Rao", "root", "1234") ; 
        //3.得到操作数据库sql语句的对象statement
        Statement stmt = conn.createStatement();
        //4.执行sql语句
        String sql="select * from users";
        ResultSet rs=stmt.executeQuery(sql);
        //5.如何有返回结果，处理结果
        while(rs.next()){
            System.out.print(rs.getObject(1) + "\t");
            System.out.print(rs.getObject(2) + "\t");
            System.out.print(rs.getObject(3) + "\t");
            System.out.print(rs.getObject(4) + "\t\t\t");
            System.out.println(rs.getObject(5) + "\t");
            System.out.println("-------------------------------------------");
        }
        //6.关闭资源
        rs.close();
        stmt.close();
        conn.close();

    }
}