Java 連接 MySQL

Java 連接 MySQL

我們這次用 MySQL Connector/J 8.0 來進行連結

先來裝一下相依性

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.20</version>
</dependency>

裝好了之後,要怎麼用呢?

載入 Driver

首先我們要載入 MySQLDriver

try {
  Class.forName("com.mysql.cj.jdbc.Driver");
  // Class.forName("com.mysql.jdbc.Driver"); // 這個已經不能用了
} catch (ClassNotFoundException e) {
  throw new RuntimeException("找不到指定的類別");
}

有看到被註解掉的那行,他是舊版的 Driver 位置,現在檔案依舊存在,只是裡面只剩下一行:

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.mysql.jdbc;

import java.sql.SQLException;

public class Driver extends com.mysql.cj.jdbc.Driver {
    public Driver() throws SQLException {
    }

    static {
        System.err.println("Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.");
    }
}

會跳出 err 的提醒訊息 ( 但實際上是可以使用的唷!因為他還是有繼承 com.mysql.cj.jdbc.Driver )


ok! 載入完了 Driver 就開始來連線吧!

取得 Connection 物件

try {
      Connection conn = DriverManager
	        			.getConnection(
        					"jdbc:mysql://127.0.0.1:4089/neon", // DB 的位置
        					"wayne", // 使用者
	    			    	"xxx" // 密碼
      					);
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
}

以上的 DriverManager.getConnection() 方法所產生的 Connection 物件就跟
mysql -u wayne -pxxx -P 4089 -h127.0.0.1 neon
一樣!

對 DB 進行操作

先來看一下 demo 用的 table 結構 ( 這是直接用 w3schools 的範例~ )

desc MyGuests;
+-----------+-----------------+------+-----+---------------------+-------------------------------+
| Field     | Type            | Null | Key | Default             | Extra                         |
+-----------+-----------------+------+-----+---------------------+-------------------------------+
| id        | int(6) unsigned | NO   | PRI | NULL                | auto_increment                |
| firstname | varchar(30)     | NO   |     | NULL                |                               |
| lastname  | varchar(30)     | NO   |     | NULL                |                               |
| email     | varchar(50)     | YES  |     | NULL                |                               |
| reg_date  | timestamp       | NO   |     | current_timestamp() | on update current_timestamp() |
+-----------+-----------------+------+-----+---------------------+-------------------------------+

語法:

CREATE TABLE MyGuests (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(30) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	email VARCHAR(50),
	reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) 

在進行操作的時候我們不是直接使用 Connection 物件,而是使用 java.sql.Statement 物件,他是 SQL 陳述的代表物件,可以使用 Connection 的 createStatement() 物件來建立 Statement 物件

Statement stat =conn.createStatement();

Insert 資料

在 insert 的時候要注意,使用的是 executeUpdate() 方法

會回傳 int 結果,表示資料變動的筆數

Statement stat = conn.createStatement();
int num = stat.executeUpdate("insert into MyGuests (firstname, lastname, email) values ('Chen', 'Tom', 'tom@xxx.com');");

查詢資料

查詢資料時,使用的是 executeQuery() 方法

會回傳 java.sql.ResultSet 物件

Statement stat = conn.createStatement();
ResultSet resultSet = stat.executeQuery("Select * From MyGuests");

while (resultSet.next()) {
    int id =  resultSet.getInt("id");
	String firstname =  resultSet.getString("firstname");
    String lastname =  resultSet.getString("lastname");
    String email =  resultSet.getString("email");
    Date reg_date =  resultSet.getDate("reg_date");
  
	System.out.println("================================");
	System.out.println(id);
	System.out.println(firstname);
    System.out.println(lastname);
    System.out.println(email);
    System.out.println(reg_date);
    System.out.println("================================");
}

最後一樣把所有程式碼放上來!

public class LoadDriver {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
//            Class.forName("com.mysql.jdbc.Driver"); // 這個已經不能用了
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("找不到指定的類別");
        }


        try {
            Connection conn = DriverManager
              					.getConnection(
	              					"jdbc:mysql://127.0.0.1:4089/neon", // DB 的位置
              						"wayne", // 使用者
          					    	"xxx" // 密碼
         					   	);
            Statement stat = conn.createStatement();
          	int num = stat.executeUpdate("insert into MyGuests (firstname, lastname, email) values ('Chen', 'Tom', 'tom@xxx.com');");
            ResultSet resultSet = stat.executeQuery("Select * From MyGuests");
          
          
         	while (resultSet.next()) {
                int id =  resultSet.getInt("id");
                String firstname =  resultSet.getString("firstname");
                String lastname =  resultSet.getString("lastname");
                String email =  resultSet.getString("email");
                Date reg_date =  resultSet.getDate("reg_date");

                System.out.println("================================");
                System.out.println(id);
                System.out.println(firstname);
                System.out.println(lastname);
                System.out.println(email);
                System.out.println(reg_date);
                System.out.println("================================");

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

wayne

No Comment

Leave a Reply

Your email address will not be published.

文章分類