一个JDBC示例,向您展示如何使用JDBC驱动程序连接到MySQL数据库。
经过测试:
- Java 8
- MySQL 5.7
- MySQL JDBC驱动程序
mysql-connector-java:8.0.16
1.下载MySQL JDBC驱动程序
访问https://dev.mysql.com/downloads/connector/j/以下载最新的MySQL JDBC驱动程序。
![mysql连接器](https://i-blog.csdnimg.cn/blog_migrate/ced8183accf91432e94dce5a48e291e7.png)
2. JDBC连接
2.1建立与MySQL数据库的连接。
JDBCExample.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCExample {
public static void main(String[] args) {
// https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html#package.description
// auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName
// register JDBC driver, optional since java 1.6
/*try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}*/
// auto close connection
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/test", "root", "password")) {
if (conn != null) {
System.out.println("Connected to the database!");
} else {
System.out.println("Failed to make connection!");
}
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
测试:
# compile
> javac JDBCExample.java
# run
> java JDBCExample
SQL State: 08001
No suitable driver found for jdbc:mysql://127.0.0.1:3306/test
要使用java
命令运行它,我们需要手动加载MySQL JDBC驱动程序。 假设所有内容都存储在c:\test
文件夹中,请使用此-cp
选项再次运行。
![项目布局](https://i-blog.csdnimg.cn/blog_migrate/4d724baeea05cde39255b51e6a0372a5.png)
> java -cp "c:\test\mysql-connector-java-8.0.16.jar;c:\test" JDBCExample
Connected to the database!
3. Maven项目
3.1 Maven中央存储库中提供了MySQL JDBC驱动程序。
pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
3.2一个简单的JDBC选择示例。
JDBCExample2.java
import com.mkyong.jdbc.model.Employee;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCExample2 {
public static void main(String[] args) {
System.out.println("MySQL JDBC Connection Testing ~");
List<Employee> result = new ArrayList<>();
String SQL_SELECT = "Select * from EMPLOYEE";
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/test", "root", "password");
PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong("ID");
String name = resultSet.getString("NAME");
BigDecimal salary = resultSet.getBigDecimal("SALARY");
Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");
Employee obj = new Employee();
obj.setId(id);
obj.setName(name);
obj.setSalary(salary);
// Timestamp -> LocalDateTime
obj.setCreatedDate(createdDate.toLocalDateTime());
result.add(obj);
}
result.forEach(x -> System.out.println(x));
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Employee.java
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class Employee {
private Long id;
private String name;
private BigDecimal salary;
private LocalDateTime createdDate;
//...
}
表定义。
CREATE TABLE EMPLOYEE
(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
SALARY DECIMAL(15, 2) NOT NULL,
CREATED_DATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ID)
);
下载源代码
$ git clone https://github.com/mkyong/java-jdbc.git
参考文献
翻译自: https://mkyong.com/jdbc/how-to-connect-to-mysql-with-jdbc-driver-java/