JDBC連線mysql-8.0實現增刪改查

（一）安裝配置mysql

官網下載mysql-8.0.11-winx64.zip

解壓到某個目錄（例如E:\mysql-8.0.11-winx64）

新增環境變數 MYSQL_HOME,值為E:\mysql-8.0.11-winx64(需要替換成實際解壓目錄)；修改環境變數PATH，在末尾新增%MYSQL_HOME%\bin;

開啟cmd視窗，在E:\mysql-8.0.11-winx64\bin目錄下執行以下命令，執行完畢後建立了E:\mysql-8.0.11-winx64\data目錄
mysqld --initialize -insecure

在E:\mysql-8.0.11-winx64目錄下新建配置檔案my.ini


執行mysqld -install安裝mysql服務

net start mysql  啟動mysql服務

mysql -u root -p  回車不輸入密碼

修改root密碼
alter user "root"@"localhost" identified by "123456";

退出再次登入
mysql -u root -p  需要輸入密碼
 

net stop mysql  停止mysql服務

（二）JDBC連線mysql實現增刪改查

（1）使用eclipse新建一個java project，名為mysqlDmo，新增mysql-connector-java-8.0.11.jar包，如下圖所示：

（2）利用JDBC來執行sql語句，有以下幾步：

（a）載入驅動

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

（b）連線資料庫，利用DriverManager.getConnection獲取Connection物件

String url = "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false";

String user = "root";String passwd = "123456";
DriverManager.getConnection(url, user, passwd);

需要注意的是如果url中沒有serverTimezone=UTC引數，將會報錯如下：

The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

url中還可以設定一些引數如connecTimeout, characterEncoding等。

（c）獲取Statement或者PreparedStatement例項

Statement sqlst = conn.createStatement();

（d）執行sql語句

ResultSet reset = sqlst.executeQuery("select * from tbl_stu");
while(reset.next()){
	String id = reset.getString("id");
	String name = reset.getString("name");
	String department = reset.getString("department");
	System.out.println("id:"+id+"    "+"name: "+name+"    "+"department: "+department);
}

(e)關閉資料庫連線

conn.close();

（2）建立一個測試表

net start mysql;

mysql -u root -p  回車輸入密碼

create database  test;
use test;

create table tbl_stu(
   id         int            NOT NULL ,
   name       varchar(32)             ,
   department varchar(64)             ,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tbl_stu values(1001, "kevin", "computer");
insert into tbl_stu values(1002, "angela", "account");

（3）測試程式如下：

package com.mysql.dmo;
import java.sql.*;

public class MysqlTest {

	public static void main(String[] args) {
		Connection conn = null;
		String url = "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false";
		String user = "root";
		String passwd = "123456";
		
		
		//載入驅動
		try{
			Class.forName("com.mysql.cj.jdbc.Driver");
			System.out.println("Successfully load com.mysql.cj.jdbc.Driver.");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		
		//使用DriverManager.getConnection獲取連線資料庫的物件
		try {
			conn = DriverManager.getConnection(url, user, passwd);
			System.out.println("database connected.");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			//獲取Statement例項
			Statement sqlst = conn.createStatement();
			
			//執行資料庫語句，獲取ResultSet,測試查詢
			System.out.println("test query using Statement...");
			ResultSet reset = sqlst.executeQuery("select * from tbl_stu");
			while(reset.next()){
				String id = reset.getString("id");
				String name = reset.getString("name");
				String department = reset.getString("department");
				System.out.println("id:"+id+"    "+"name: "+name+"    "+"department: "+department);
			}
			
			//利用PreparedStatement測試增刪改查
			System.out.println("test insert, update and delete using prepareStatement...");
			String sql=null;
			PreparedStatement prest = null;
			
			sql = "insert into tbl_stu values(?,?,?)";
			prest = conn.prepareStatement(sql);
			prest.setInt(1, 1003);
			prest.setString(2, "juddy");
			prest.setString(3, "science");
			prest.executeUpdate();
			
			sql = "update tbl_stu set name=? where id =1003";
			prest = conn.prepareStatement(sql);
			prest.setString(1, "grace");
			prest.executeUpdate();
			
			sql = "delete from tbl_stu where id =?";
			prest = conn.prepareStatement(sql);
			prest.setInt(1, 1001);
			prest.executeUpdate();
						
			sql = "select * from tbl_stu";
			prest = conn.prepareStatement(sql);
			reset = prest.executeQuery();
			while(reset.next()){
				String id = reset.getString("id");
				String name = reset.getString("name");
				String department = reset.getString("department");
				System.out.println("id:"+id+"    "+"name: "+name+"    "+"department: "+department);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//關閉資料庫連線
			try {
				conn.close();
				System.out.println("database disconnected.");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

}

執行結果如下：