将 Java 连接到 MySQL 数据库

Question

提问by abson

How do you connect to a MySQL database in Java?

如何在 Java 中连接到 MySQL 数据库？

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

Answer 1

采纳答案by Sean Owen

DriverManageris a fairly old way of doing things. The better way is to get a DataSource, either by looking one up that your app server container already configured for you:

DriverManager是一种相当古老的做事方式。更好的方法是DataSource通过查找已为您配置的应用服务器容器来获取 .

Context context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/myDB");

or instantiating and configuring one from your database driver directly:

或直接从您的数据库驱动程序实例化和配置一个：

MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("scott");
dataSource.setPassword("tiger");
dataSource.setServerName("myDBHost.example.org");

and then obtain connections 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();

Answer 2

回答by heffaklump

String url = "jdbc:mysql://127.0.0.1:3306/yourdatabase";
String user = "username";
String password = "password";

// Load the Connector/J driver
Class.forName("com.mysql.jdbc.Driver").newInstance();
// Establish connection to MySQL
Connection conn = DriverManager.getConnection(url, user, password);

Answer 3

回答by Kilian Foth

Here's the very minimum you need to get data out of a MySQL database:

这是从 MySQL 数据库中获取数据所需的最低要求：

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection
   ("jdbc:mysql://localhost:3306/foo", "root", "password");

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM `FOO.BAR`");
stmt.close();
conn.close();

Add exception handling, configuration etc. to taste.

添加异常处理、配置等。

Answer 4

回答by BalusC

Here's a step by step explanation how to install MySQL and JDBC and how to use it:

下面一步一步讲解如何安装MySQL和JDBC以及如何使用：

Downloadand install the MySQL server. Just do it the usual way. Remember the port number whenever you've changed it. It's by default 3306.
Downloadthe JDBC driver and put in classpath, extract the ZIP file and put the containing JAR file in the classpath. The vendor-specific JDBC driver is a concrete implementation of the JDBC API(tutorial here).
If you're using an IDE like Eclipse or Netbeans, then you can add it to the classpath by adding the JAR file as Libraryto the Build Pathin project's properties.
If you're doing it "plain vanilla" in the command console, then you need to specify the path to the JAR file in the -cpor -classpathargument when executing your Java application.
```
java -cp .;/path/to/mysql-connector.jar com.example.YourClass
```
The .is just there to add the currentdirectory to the classpath as well so that it can locate com.example.YourClassand the ;is the classpath separator as it is in Windows. In Unix and clones :should be used.
Create a databasein MySQL. Let's create a database javabase. You of course want World Domination, so let's use UTF-8 as well.
```
CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
```
Create an userfor Java and grantit access. Simply because using rootis a bad practice.
```
CREATE USER 'java'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'password';
```
Yes, javais the username and passwordis the password here.
Determinethe JDBC URL. To connect the MySQL database using Java you need an JDBC URL in the following syntax:
```
jdbc:mysql://hostname:port/databasename
```
- hostname: The hostname where MySQL server is installed. If it's installed at the same machine where you run the Java code, then you can just use localhost. It can also be an IP address like 127.0.0.1. If you encounter connectivity problems and using 127.0.0.1instead of localhostsolved it, then you've a problem in your network/DNS/hosts config.
- port: The TCP/IP port where MySQL server listens on. This is by default 3306.
- databasename: The name of the database you'd like to connect to. That's javabase.
So the final URL should look like:
```
jdbc:mysql://localhost:3306/javabase
```
Test the connectionto MySQL using Java. Create a simple Java class with a main()method to test the connection.
```
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);
}
```
If you get a SQLException: No suitable driver, then it means that either the JDBC driver wasn't autoloaded at all or that the JDBC URL is wrong (i.e. it wasn't recognized by any of the loaded drivers). Normally, a JDBC 4.0 driver should be autoloaded when you just drop it in runtime classpath. To exclude one and other, you can always manually load it as below:
```
System.out.println("Loading driver...");

try {
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("Driver loaded!");
} catch (ClassNotFoundException e) {
    throw new IllegalStateException("Cannot find the driver in the classpath!", e);
}
```
Note that the newInstance()call is notneeded here. It's just to fix the old and buggy org.gjt.mm.mysql.Driver. Explanation here. If this line throws ClassNotFoundException, then the JAR file containing the JDBC driver class is simply not been placed in the classpath.
Note that you don't need to load the driver everytimebeforeconnecting. Just only once during application startup is enough.
If you get a SQLException: Connection refusedor Connection timed outor a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:
1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL.
4. DB server is down.
5. DB server doesn't accept TCP/IP connections.
6. DB server has run out of connections.
7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
To solve the one or the other, follow the following advices:
1. Verify and test them with ping.
2. Refresh DNS or use IP address in JDBC URL instead.
3. Verify it based on my.cnfof MySQL DB.
4. Start the DB.
5. Verify if mysqld is started without the --skip-networking option.
6. Restart the DB and fix your code accordingly that it closes connections in finally.
7. Disable firewall and/or configure firewall/proxy to allow/forward the port.
Note that closing the Connectionis extremelyimportant. If you don't close connections and keep getting a lot of them in a short time, then the database may run out of connections and your application may break. Always acquire the Connectionin a try-with-resourcesstatement. Or if you're not on Java 7 yet, explicitly close it in finallyof a try-finallyblock. Closing in finallyis just to ensure that it get closed as well in case of an exception. This also applies to Statement, PreparedStatementand ResultSet.

下载并安装 MySQL 服务器。只需按照通常的方式进行即可。每当您更改端口号时，请记住它。它是默认的3306。
下载JDBC 驱动程序并放入 classpath，解压 ZIP 文件并将包含的 JAR 文件放入 classpath 中。特定于供应商的 JDBC 驱动程序是JDBC API的具体实现（此处为教程）。
如果您使用的是 Eclipse 或 Netbeans 等 IDE，则可以通过将 JAR 文件作为库添加到项目属性中的构建路径来将其添加到类路径中。
如果您在命令控制台中执行“普通”操作，那么您需要在执行 Java 应用程序时在-cpor-classpath参数中指定 JAR 文件的路径。
```
java -cp .;/path/to/mysql-connector.jar com.example.YourClass
```
的.是就在那里给添加当前目录到类路径中，以便它可以找到com.example.YourClass和;是类路径分隔符，因为它是在Windows中。在 Unix 和克隆中:应该使用。
在 MySQL 中创建一个数据库。让我们创建一个数据库javabase。你当然想要 World Domination，所以让我们也使用 UTF-8。
```
CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
```
为 Java创建一个用户并授予它访问权限。仅仅因为使用root是一种不好的做法。
```
CREATE USER 'java'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'password';
```
是的，这里java是用户名和password密码。
确定JDBC URL。要使用 Java 连接 MySQL 数据库，您需要使用以下语法的 JDBC URL：
```
jdbc:mysql://hostname:port/databasename
```
- hostname：安装 MySQL 服务器的主机名。如果它安装在运行 Java 代码的同一台机器上，那么您只需使用localhost. 它也可以是 IP 地址，例如127.0.0.1. 如果您遇到连接问题并使用127.0.0.1而不是localhost解决它，那么您的网络/DNS/主机配置存在问题。
- port：MySQL 服务器侦听的 TCP/IP 端口。这是默认的3306。
- databasename：您要连接的数据库的名称。那是javabase。
所以最终的 URL 应该是这样的：
```
jdbc:mysql://localhost:3306/javabase
```
使用 Java测试与MySQL的连接。使用main()方法创建一个简单的 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 suitable driver，则意味着 JDBC 驱动程序根本没有自动加载，或者 JDBC URL 错误（即，任何加载的驱动程序都无法识别它）。通常，当您将 JDBC 4.0 驱动程序放到运行时类路径中时，它应该会自动加载。要排除一个和另一个，您可以随时手动加载它，如下所示：
```
System.out.println("Loading driver...");

try {
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("Driver loaded!");
} catch (ClassNotFoundException e) {
    throw new IllegalStateException("Cannot find the driver in the classpath!", e);
}
```
需要注意的是，newInstance()在呼叫未在这里需要。这只是为了修复旧的和有问题的org.gjt.mm.mysql.Driver. 解释在这里。如果这一行抛出ClassNotFoundException，那么包含 JDBC 驱动程序类的 JAR 文件根本就没有放在类路径中。
请注意，您无需每次在连接前都加载驱动程序。在应用程序启动期间只需一次就足够了。
如果您得到一个SQLException: Connection refusedorConnection timed out或 MySQL 特定的CommunicationsException: Communications link failure，则意味着该数据库根本无法访问。这可能有以下一种或多种原因：
1. JDBC URL 中的 IP 地址或主机名错误。
2. 本地 DNS 服务器无法识别 JDBC URL 中的主机名。
3. JDBC URL 中的端口号丢失或错误。
4. 数据库服务器已关闭。
5. 数据库服务器不接受 TCP/IP 连接。
6. 数据库服务器的连接用完了。
7. Java 和 DB 之间的某些东西正在阻止连接，例如防火墙或代理。
要解决其中之一，请遵循以下建议：
1. 验证并测试它们ping。
2. 刷新 DNS 或改用 JDBC URL 中的 IP 地址。
3. 基于my.cnfMySQL DB 进行验证。
4. 启动数据库。
5. 验证 mysqld 是否在没有--skip-networking option.
6. 重新启动数据库并相应地修复您的代码，以关闭finally.
7. 禁用防火墙和/或配置防火墙/代理以允许/转发端口。
需要注意的是关闭Connection是极为重要的。如果您不关闭连接并在短时间内继续获取大量连接，那么数据库可能会耗尽连接并且您的应用程序可能会中断。始终Connection在try-with-resources语句中获取。或者，如果你不上的Java 7的是，明确地关闭它在finally一个的try-finally块。关闭finally只是为了确保它在发生异常时也被关闭。这也适用于Statement,PreparedStatement和ResultSet。

That was it as far the connectivity concerns. You can find herea more advanced tutorial how to load and store fullworthy Java model objects in a database with help of a basic DAO class.

就连接性而言，这就是它。您可以在此处找到更高级的教程，如何在基本 DAO 类的帮助下在数据库中加载和存储完整的 Java 模型对象。

Using a Singleton Pattern for the DB connection is a bad approach. See among other questions: http://stackoverflow.com/q/9428573/. This is a #1 starters mistake.

对数据库连接使用单例模式是一种糟糕的方法。查看其他问题：http: //stackoverflow.com/q/9428573/。这是一个＃1初学者错误。

Answer 5

回答by Jwalant

You can see all steps to connect MySQL database from Java application here. For other database, you just need to change the driver in first step only. Please make sure that you provide right path to database and correct username and password.

您可以在此处查看从 Java 应用程序连接 MySQL 数据库的所有步骤。对于其他数据库，您只需在第一步更改驱动程序即可。请确保您提供正确的数据库路径和正确的用户名和密码。

Visit http://apekshit.com/t/51/Steps-to-connect-Database-using-JAVA

访问http://apekshit.com/t/51/Steps-to-connect-Database-using-JAVA

Answer 6

回答by kapil das

you need to have mysql connector jar in your classpath.

您的类路径中需要有 mysql 连接器 jar。

in Java JDBC API makes everything with databases. using JDBC we can write Java applications to
1. Send queries or update SQL to DB(any relational Database) 2. Retrieve and process the results from DB

在 Java 中 JDBC API 使一切都与数据库有关。使用 JDBC 我们可以编写 Java 应用程序
1. 向 DB（任何关系数据库）发送查询或更新 SQL 2. 从 DB 检索和处理结果

with below three steps we can able to retrieve data from any Database

通过以下三个步骤，我们可以从任何数据库中检索数据

Connection con = DriverManager.getConnection(
                     "jdbc:myDriver:DatabaseName",
                     dBuserName,
                     dBuserPassword);

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table");

while (rs.next()) {
    int x = rs.getInt("a");
    String s = rs.getString("b");
    float f = rs.getFloat("c");
}

Answer 7

回答by xxxvodnikxxx

ConnectionI was using some time ago, it was looking like the easiest way, but also there were recommendation to make there ifstatement- exactly

Connection我前段时间使用过，它看起来是最简单的方法，但也有人建议在那里if发表声明 - 正是

Connection con = DriverManager.getConnection(
                     "jdbc:myDriver:DatabaseName",
                     dBuserName,
                     dBuserPassword);
if (con != null){
 //..handle your code there 
}

Or something like in that way :)

或类似的东西:)

Probably there's some case, while getConnectioncan return null:)

可能有一些情况，虽然getConnection可以返回null:)

Answer 8

回答by Madan Sapkota

Initialize database constants

初始化数据库常量

Create constant properties database username, password, URL and drivers, polling limit etc.

创建常量属性数据库用户名、密码、URL 和驱动程序、轮询限制等。

// init database constants
// com.mysql.jdbc.Driver
private static final String DATABASE_DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/database_name";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
private static final String MAX_POOL = "250"; // set your own limit

Initialize Connection and Properties

初始化连接和属性

Once the connection is established, it is better to store for reuse purpose.

建立连接后，最好将其存储起来以备重用。

// init connection object
private Connection connection;
// init properties object
private Properties properties;

Create Properties

创建属性

The properties object hold the connection information, check if it is already set.

属性对象保存连接信息，检查它是否已经设置。

// create properties
private Properties getProperties() {
    if (properties == null) {
        properties = new Properties();
        properties.setProperty("user", USERNAME);
        properties.setProperty("password", PASSWORD);
        properties.setProperty("MaxPooledStatements", MAX_POOL);
    }
    return properties;
}

Connect the Database

连接数据库

Now connect to database using the constants and properties initialized.

现在使用初始化的常量和属性连接到数据库。

// connect database
public Connection connect() {
    if (connection == null) {
        try {
            Class.forName(DATABASE_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, getProperties());
        } catch (ClassNotFoundException | SQLException e) {
            // Java 7+
            e.printStackTrace();
        }
    }
    return connection;
}

Disconnect the database

断开数据库

Once you are done with database operations, just close the connection.

完成数据库操作后，只需关闭连接即可。

// disconnect database
public void disconnect() {
    if (connection != null) {
        try {
            connection.close();
            connection = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Everything together

一切都在一起

Use this class MysqlConnectdirectly after changing database_name, username and password etc.

MysqlConnect更改database_name、用户名和密码等后直接使用该类。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class MysqlConnect {
    // init database constants
    private static final String DATABASE_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/database_name";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    private static final String MAX_POOL = "250";

    // init connection object
    private Connection connection;
    // init properties object
    private Properties properties;

    // create properties
    private Properties getProperties() {
        if (properties == null) {
            properties = new Properties();
            properties.setProperty("user", USERNAME);
            properties.setProperty("password", PASSWORD);
            properties.setProperty("MaxPooledStatements", MAX_POOL);
        }
        return properties;
    }

    // connect database
    public Connection connect() {
        if (connection == null) {
            try {
                Class.forName(DATABASE_DRIVER);
                connection = DriverManager.getConnection(DATABASE_URL, getProperties());
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    // disconnect database
    public void disconnect() {
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

How to Use?

如何使用？

Initialize the database class.

初始化数据库类。

// !_ note _! this is just init
// it will not create a connection
MysqlConnect mysqlConnect = new MysqlConnect();

Somewhere else in your code ...

您代码中的其他地方...

String sql = "SELECT * FROM `stackoverflow`";
try {
    PreparedStatement statement = mysqlConnect.connect().prepareStatement(sql);
    ... go on ...
    ... go on ...
    ... DONE ....
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    mysqlConnect.disconnect();
}

This is all :)If anything to improve edit it! Hope this is helpful.

这就是全部:)如果有什么要改进的编辑它！希望这是有帮助的。

Answer 9

回答by KhiLan PaTeL

Short and Sweet code.

简短而甜蜜的代码。

try {       
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("Driver Loaded");
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","");
    //Database Name - testDB, Username - "root", Password - ""
    System.out.println("Connected...");         
} catch(Exception e) {
    e.printStackTrace();
}

For SQL server 2012

对于 SQL Server 2012

try {
    String url = "jdbc:sqlserver://KHILAN:1433;databaseName=testDB;user=Khilan;password=Tuxedo123"; 
    //KHILAN is Host    and 1433 is port number     
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    System.out.println("Driver Loaded");
    conn = DriverManager.getConnection(url);
    System.out.println("Connected...");
} catch(Exception e) {
    e.printStackTrace();
}

Answer 10

回答by Sarat Chandra

MySql JDBC Connection:

MySql JDBC 连接：

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

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/DatabaseName","Username","Password");         
Statement stmt=con.createStatement();            
stmt = con.createStatement();
ResultSet rs=stmt.executeQuery("Select * from Table");

将 Java 连接到 MySQL 数据库

提问by abson

采纳答案by Sean Owen

回答by heffaklump

回答by Kilian Foth

回答by BalusC

回答by Jwalant

回答by kapil das

回答by xxxvodnikxxx

回答by Madan Sapkota

Initialize database constants

初始化数据库常量

Initialize Connection and Properties

初始化连接和属性

Create Properties

创建属性

Connect the Database

连接数据库

Disconnect the database

断开数据库

Everything together

一切都在一起

How to Use?

如何使用？

回答by KhiLan PaTeL

回答by Sarat Chandra

相关推荐

最近更新

标签

将 Java 连接到 MySQL 数据库

提问by abson

采纳答案by Sean Owen

回答by heffaklump

回答by Kilian Foth

回答by BalusC

回答by Jwalant

回答by kapil das

回答by xxxvodnikxxx

回答by Madan Sapkota

Initialize database constants

初始化数据库常量

Initialize Connection and Properties

初始化连接和属性

Create Properties

创建属性

Connect the Database

连接数据库

Disconnect the database

断开数据库

Everything together

一切都在一起

How to Use?

如何使用？

回答by KhiLan PaTeL

回答by Sarat Chandra

相关推荐

如何从 Java 方法返回 2 个值？

Java Try-Catch异常计算器

Java中的回车\换行

从 Java 添加映射到类型 - 我该怎么做？

相关推荐

最近更新

标签