php editor Banana brings you a special question and answer about connecting Java to a MySQL database. This article will explain to you how to connect to a MySQL database in a Java application, allowing you to easily master relevant knowledge and improve your programming skills. Whether you are a beginner or an experienced developer, you can find a solution that suits you in this article to make database operations more efficient and convenient. Let’s dive in!

Question content

How to connect to mysql database using java?

When I try, I get

java.sql.sqlexception: no suitable driver found for jdbc:mysql://database/table
    at java.sql.drivermanager.getconnection(drivermanager.java:689)
    at java.sql.drivermanager.getconnection(drivermanager.java:247)

or

java.lang.classnotfoundexception: com.mysql.jdbc.driver

or

java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver

Solution

Data source

drivermanager is a pretty old way of doing things. A better approach is to get the DataSource object. You can use JNDI to find the application server container that has been configured for you:

context context = new initialcontext();
datasource datasource = (datasource) context.lookup("java:comp/env/jdbc/mydb");

...or instantiate and configure one directly from the database driver, such as com.mysql.cj.jdbc.mysqldatasource (see documentation):

mysqldatasource datasource = new mysqldatasource();
datasource.setuser("scott");
datasource.setpassword("tiger");
datasource.setservername("mydbhost.example.org");

...and then get the connection from it, same as above:

connection conn = datasource.getconnection();
statement stmt = conn.createstatement();
resultset rs = stmt.executequery("select id from users");
…
rs.close();
stmt.close();
conn.close();

In modern java, use the try-with-resources syntax to automatically close jdbc resources (now AutoCloseable).

try (
    connection conn = datasource.getconnection();
    statement stmt = conn.createstatement();
    resultset rs = stmt.executequery("select id from users");
) {
    …
}

Here are step-by-step instructions on how to install mysql and jdbc and how to use it:

1. Download and install mysql server. Just do it the usual way. Remember it whenever you change the port number. Default is 3306.

2. Download jdbc driver and put it in the classpath , unzip the zip file and put the included jar file in the classpath. Vendor-specific jdbc drivers are concrete implementations of the JDBC API (tutorial here).

   If you are using an IDE such as eclipse or netbeans, you can add the jar file to the classpath by adding it as a library to the Build Path property .

   If you do this in the "normal" way in the command console, you need to specify the jar file in the -cp or -classpath parameter when executing the java application path.

   java -cp .;/path/to/mysql-connector.jar com.example.yourclass

   . just adds the current directory to the classpath so it can find com.example.yourclass and ; is the classpath separator because It's in the window. On unix and clones, : should be used.

   If you are developing a servlet-based war application and want to manage the connections manually (actually, this is a bad practice), then you need to ensure that the jar ends up in the built /web-inf/lib middle. See also How to add JAR libraries to WAR project without facing java.lang.ClassNotFoundException? Classpath vs Build Path vs /WEB-INF/lib. A better approach is to install the physical jdbc driver jar file in the server itself and configure the server to create a jdbc connection pool. Here is an example for tomcat:How should I connect to JDBC database / datasource in a servlet based application?

3. Create database in mysql. Let's create a database javabase. Of course you want to rule the world, so we use utf-8 as well.

   create database javabase default character set utf8 collate utf8_unicode_ci;

4. java accesses Create a user, which accesses grant. Simply because using root is a bad practice.

   create user 'java'@'localhost' identified by 'password';
    grant all on javabase.* to 'java'@'localhost' identified by 'password';

   Yes, here java is the username and password is the password.

5. Determine jdbc url. To connect to a mysql database using java, you need a jdbc url with the following syntax:

   jdbc:mysql://hostname:port/databasename

   • hostname：安裝 mysql 服務(wù)器的主機(jī)名。如果它安裝在運(yùn)行 java 代碼的同一臺機(jī)器上，那么您可以只使用 localhost。它也可以是 ip 地址，例如 127.0.0.1。如果您遇到連接問題，并且使用 127.0.0.1 而不是 localhost 解決了該問題，那么您的網(wǎng)絡(luò)/dns/主機(jī)配置有問題。

   • port：mysql 服務(wù)器監(jiān)聽的 tcp/ip 端口。默認(rèn)情況下為 3306。

   • databasename：您要連接的數(shù)據(jù)庫的名稱。那是 javabase。

   所以最終的 url 應(yīng)如下所示：

   jdbc:mysql://localhost:3306/javabase

6. Test the connection 使用 java 到 mysql。使用 main() 方法創(chuàng)建一個(gè)簡單的 java 類來測試連接。

    string url = "jdbc:mysql://localhost:3306/javabase";
    string username = "java";
    string password = "password";
   
    system.out.println("connecting database ...");
   
    try (connection connection = drivermanager.getconnection(url, username, password)) {
        system.out.println("database connected!");
    } catch (sqlexception e) {
        throw new illegalstateexception("cannot connect the database!", e);
    }
   

   如果您收到 sqlexception: no合適的驅(qū)動(dòng)程序，則意味著 jdbc 驅(qū)動(dòng)程序根本沒有自動(dòng)加載，或者 jdbc url 錯(cuò)誤（即，任何加載的驅(qū)動(dòng)程序都無法識別它）。另請參見 The infamous java.sql.SQLException: No suitable driver found。通常，當(dāng)您將 jdbc 4.0 驅(qū)動(dòng)程序放入運(yùn)行時(shí)類路徑中時(shí)，應(yīng)該會(huì)自動(dòng)加載它。要排除其中一個(gè)或另一個(gè)，您可以隨時(shí)手動(dòng)加載它，如下所示：

    System.out.println("Loading driver ...");
   
    try {
        Class.forName("com.mysql.cj.jdbc.Driver"); // Use com.mysql.jdbc.Driver if you're not on MySQL 8+ yet.
        System.out.println("Driver loaded!");
    } catch (ClassNotFoundException e) {
        throw new IllegalStateException("Cannot find the driver in the classpath!", e);
    }
   

   請注意，此處不需要調(diào)用 newinstance() 。對于mysql，只是為了修復(fù)舊的且有缺陷的org.gjt.mm.mysql.driver。 Explanation here。如果此行拋出 classnotfoundexception，則說明包含 jdbc 驅(qū)動(dòng)程序類的 jar 文件根本沒有放置在類路徑中。另請注意，拋出異常非常重要，以便立即阻止代碼執(zhí)行，而不是僅僅打印堆棧跟蹤然后繼續(xù)執(zhí)行其余代碼來抑制它。

   另請注意，您不需要每次在連接之前加載驅(qū)動(dòng)程序。只需在應(yīng)用程序啟動(dòng)期間一次就足夠了。

   如果您收到 sqlexception: 連接被拒絕 或 connection timed out 或 mysql 特定的 communicationsexception: 通信鏈路故障 ，則意味著數(shù)據(jù)庫根本無法訪問。這可能由以下一個(gè)或多個(gè)原因造成：

   1. jdbc url 中的 ip 地址或主機(jī)名錯(cuò)誤。
   2. 本地 dns 服務(wù)器無法識別 jdbc url 中的主機(jī)名。
   3. jdbc url 中的端口號缺失或錯(cuò)誤。
   4. 數(shù)據(jù)庫服務(wù)器已關(guān)閉。
   5. 數(shù)據(jù)庫服務(wù)器不接受 tcp/ip 連接。
   6. 數(shù)據(jù)庫服務(wù)器已耗盡連接。
   7. java 和 db 之間的某些東西正在阻塞連接，例如防火墻或代理。

   要解決其中一個(gè)問題，請遵循以下建議：

   1. 使用 ping 驗(yàn)證并測試它們。
   2. 刷新 dns 或在 jdbc url 中使用 ip 地址。
   3. 根據(jù)mysql db的my.cnf進(jìn)行驗(yàn)證。
   4. 啟動(dòng)數(shù)據(jù)庫。
   5. 驗(yàn)證 mysqld 是否在沒有 --skip-networking 選項(xiàng) 的情況下啟動(dòng)。
   6. 重新啟動(dòng)數(shù)據(jù)庫并相應(yīng)地修復(fù)代碼，使其關(guān)閉 finally 中的連接。
   7. 禁用防火墻和/或配置防火墻/代理以允許/轉(zhuǎn)發(fā)端口。

   請注意，關(guān)閉 connection 極其非常重要。如果您不關(guān)閉連接并在短時(shí)間內(nèi)不斷獲取大量連接，那么數(shù)據(jù)庫可能會(huì)耗盡連接，并且您的應(yīng)用程序可能會(huì)崩潰。始終獲取 try-with-resources statement 中的 connection。這也適用于 statement、preparedstatement 和 resultset。另見How often should Connection, Statement and ResultSet be closed in JDBC?

   這就是連接問題。您可以在 here 找到更高級的教程，了解如何借助基本 dao 類在數(shù)據(jù)庫中加載和存儲(chǔ)完整的 java 模型對象。

   對數(shù)據(jù)庫 connection 使用單例模式和/或 static 變量是一種不好的做法。參見其他 Is it safe to use a static java.sql.Connection instance in a multithreaded system? 這是第一個(gè)初學(xué)者錯(cuò)誤。確保您不會(huì)落入這個(gè)陷阱。

   The above is the detailed content of Connect Java to MySQL database. For more information, please follow other related articles on the PHP Chinese website!