iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 9
2
自我挑戰組

練習程式系列 第 9

java,mysql、jdbc

教學來源:
Normalization - 1NF, 2NF, 3NF and 4NF
Java JDBC Tutorial - Part 0: Overview

Mysql conectors下載:
https://www.mysql.com/products/connector/
https://ithelp.ithome.com.tw/upload/images/20190915/20111994Zzzs9WHigc.png
https://ithelp.ithome.com.tw/upload/images/20190915/20111994GxnZpiGO3T.png
https://ithelp.ithome.com.tw/upload/images/20190915/20111994eItyWTbMG2.png

下載完後,會有個壓縮檔,資料夾是mysql-connector-java-8.0.17,檔案有:
https://ithelp.ithome.com.tw/upload/images/20190915/20111994k6G9p4HiXH.png

新版本的mysql-connector-java-8.0.17.jar檔案跟之前版本的mysql-connector-java-5.1.48.jar內容不太一樣,所以舊程式會有一些錯誤。這邊先練習mysql-connector-java-5.1.48

回去下載mysql-connector-java-5.1.48:
https://ithelp.ithome.com.tw/upload/images/20190915/20111994xNeUTZbovc.png
https://ithelp.ithome.com.tw/upload/images/20190915/20111994UaYudpBM7r.png

裡面的mysql-connector-java-5.1.48.jar,又是一個壓縮檔,程式裡看到的com.mysql.jdbc.Driver,就在這裡,所以java要連接mysql需要的檔案,就是mysql-connector-java-5.1.48.jar這個壓縮檔。
https://ithelp.ithome.com.tw/upload/images/20190915/20111994SlMs9Rc6d0.png
https://ithelp.ithome.com.tw/upload/images/20190915/20111994ynhKGMnqN3.png
https://ithelp.ithome.com.tw/upload/images/20190915/201119949H7MY1Z1Kx.png
https://ithelp.ithome.com.tw/upload/images/20190915/20111994Rzs3Nwp1IS.png

那就來開始寫程式連接mysql

1 開啟IntelliJ IDEA
2 open module setting
https://ithelp.ithome.com.tw/upload/images/20190915/20111994gQJvxXYCLf.png
3 把mysql-connector-java-5.1.48.jar的路徑貼上--> Apply-->OK
https://ithelp.ithome.com.tw/upload/images/20190915/20111994PBovJUvEhK.png
4 開始寫程式,練習三個:
一 一般方法

import java.sql.*;

public class jdbc1 {
 public static void main(String[] args) {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("Driver loaded!");
  } catch (ClassNotFoundException e) {
   System.out.println("找不到驅動程式類別");
   e.printStackTrace();
  }

  try {
   Connection connection = getConnection();
   Statement statement = connection.createStatement();
   // 查詢city表
   try (ResultSet resultSet = statement.executeQuery("SELECT * FROM city")) {
    while (resultSet.next()) {
     System.out.println(resultSet.getString("Name") + "    " + resultSet.getString(
      "CountryCode") + "   " + resultSet.getString("District"));
    }
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 //設定檔
 private static Connection getConnection() throws SQLException {
  String serverName = "localhost";
  String database = "world";
  String url = "jdbc:mysql://" + serverName + "/" + database;
  // 帳號和密碼
  String user = "root";
  String password = "root";
  return DriverManager.getConnection(url, user, password);
 }
}

二 rowset(不太了解rowset是什麼,之後再說)

import javax.sql.RowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import java.sql.*;


public class jdbc2 {
 public static void main(String[] args) throws Exception {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("Driver loaded!");
  } catch (ClassNotFoundException e) {
   System.out.println("找不到驅動程式類別");
   e.printStackTrace();
  }

  RowSetFactory factory = RowSetProvider.newFactory();
  try (RowSet rowSet = factory.createJdbcRowSet()) {
   getConnection1(rowSet);
   getConnection1(rowSet).setCommand("SELECT * FROM city");
   getConnection1(rowSet).execute();
   while (rowSet.next()) {
    System.out.print(rowSet.getString("Name") + "\t");
    System.out.println(rowSet.getString("District"));
   }

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

 }


 //設定檔
 private static RowSet getConnection1(RowSet rowSet) throws SQLException {
  String serverName = "localhost";
  String database = "world";
  String url = "jdbc:mysql://" + serverName + "/" + database;
  // 帳號和密碼
  String user = "root";
  String password = "root";
  rowSet.setUrl(url);
  rowSet.setUsername(user);
  rowSet.setPassword(password);
  return rowSet;
 }
}

三 prepareStatement可以防止Sql Injection。

資安這條路 04 - [Injection] SQL injection
https://ithelp.ithome.com.tw/articles/10240102

import java.sql.*;

public class jdbc3 {
 public static void main(String[] args) throws Exception {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("Driver loaded!");
  } catch (ClassNotFoundException e) {
   System.out.println("找不到驅動程式類別");
   e.printStackTrace();
  }
  Connection connection = getConnection();
  PreparedStatement statement = connection.prepareStatement("SELECT * FROM world.country WHERE Continent=?");
  statement.setString(1, "Antarctica");
  ResultSet myRs = statement.executeQuery();
  while (myRs.next()) {
   String Name = myRs.getString("Name");
   String Region = myRs.getString("Region");
   System.out.printf("%s, %s\n", Name, Region);
  }

 }


 //設定檔
 private static Connection getConnection() throws SQLException {
  String serverName = "localhost";
  String database = "world";
  String url = "jdbc:mysql://" + serverName + "/" + database;
  // 帳號和密碼
  String user = "root";
  String password = "root";
  return DriverManager.getConnection(url, user, password);
 }

 private static void display(ResultSet myRs) throws SQLException {
  while (myRs.next()) {
   String Name = myRs.getString("Name");
   String Region = myRs.getString("Region");
   System.out.printf("%s, %s\n", Name, Region);
  }
 }

如果要直接用命令提示字元執行程式,要用指令:

1 javac (檔名).java

2 java -cp (class檔案位置路徑,不包含檔名);(mysql-connector-java-5.1.48.jar的所有路徑,包含檔名) (class檔名)

或是先到檔案位置路徑(cd),在用.代表檔案位置路徑-->java -cp .;(mysql-connector-java-5.1.48.jar的所有路徑,包含檔名) (class檔名)

像是:

java -cp .;D:\Desktop\MySQL\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48.jar jdbc1

就會有這樣的結果:

Driver loaded!
Fri May 22 13:44:19 CST 2020 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.
Kabul    AFG   Kabol
Qandahar    AFG   Qandahar
Herat    AFG   Herat
Mazar-e-Sharif    AFG   Balkh
Amsterdam    NLD   Noord-Holland
Rotterdam    NLD   Zuid-Holland
Haag    NLD   Zuid-Holland
Utrecht    NLD   Utrecht
Eindhoven    NLD   Noord-Brabant
Tilburg    NLD   Noord-Brabant
Groningen    NLD   Groningen
Breda    NLD   Noord-Brabant

其他問題:

Handling the null value from a resultset in JAVA


上一篇
java,HttpURLConnection
下一篇
android 簡介
系列文
練習程式37

1 則留言

0
CookieTsai
iT邦新手 5 級 ‧ 2020-05-19 16:06:33

classpath 似乎應該用冒號分開?

看更多先前的回應...收起先前的回應...

請問是指 這樣java -cp .;D:\Desktop\MySQL\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48.jar jdbc1

改成
java -cp .,D:\Desktop\MySQL\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48.jar jdbc1

目前我測是java -cp .; 才會正確
這個java -cp ., 會有
Error: Could not find or load main class jdbc1
Caused by: java.lang.ClassNotFoundException: jdbc1

更正冒號:
java -cp .:D:\Desktop\MySQL\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48.jar jdbc1

顯示這個:
Error: Could not find or load main class jdbc1
Caused by: java.lang.ClassNotFoundException: jdbc1

感謝您的測試,可能因為我的環境是在 Mac book 上執行,我確實是用冒號在執行會通過。

剛剛留意到你的指令是在 Windows,可能因為有 D:\ 的存在或許是差異的原因。

隨意找了一篇文章 Link 發現確實 Linux 和 Windows 環境不同。

謝謝您,學到了。謝謝你看我寫的東西/images/emoticon/emoticon41.gif

我要留言

立即登入留言