本文主要介绍Java中,通过Jdbc驱动连接Mysql数据库的方法。包括Jdbc驱动的下载配置,以及连接Mysql的代码。

1、MySQL JDBC驱动下载

下载地址https://dev.mysql.com/downloads/connector/j/

打开上面的下载地址,选择“Platform Independent”,然后下载tar.gz或zip格式的都行,之后解压出来得到Jdbc驱动(mysql-connector-java)的jar包,最后在项目中引用。

或者也可以Maven引用,Pom.xml中添加如下:

<!--mysql驱动包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>

2、创建示例数据库

1)创建数据库

create database feedback;
use feedback;

2)创建数据库中用户

CREATE USER sqluser IDENTIFIED BY 'sqluserpw';
grant usage on *.* to sqluser@localhost identified by 'sqluserpw';
grant all privileges on feedback.* to sqluser@localhost;

3)创建示例表和数据

CREATE TABLE comments (
id INT NOT NULL AUTO_INCREMENT,
MYUSER VARCHAR(30) NOT NULL,
EMAIL VARCHAR(30),
WEBPAGE VARCHAR(100) NOT NULL,
DATUM DATE NOT NULL,
SUMMARY VARCHAR(40) NOT NULL,
COMMENTS VARCHAR(400) NOT NULL,
PRIMARY KEY (ID)
);
INSERT INTO comments values (default, 'lars', 'myemail@gmail.com','https://www.cjavapy.com/', '2009-09-14 10:33:11', 'Summary','My first comment' );

3、通过JDBC驱动连接Mysql的示例代码

创建以下类以连接到MySQL数据库并执行查询,插入和删除。打印查询结果的元数据(表名,列名)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class MySQLAccess {
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    public void readDataBase() throws Exception {
        try {
            //将加载MySQL驱动程序,每个DB都有自己的驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //设置与数据库的连接
            connect = DriverManager
                    .getConnection("jdbc:mysql://localhost/feedback?"
                            + "user=sqluser&password=sqluserpw");
            // Statements allow to issue SQL queries to the database
            statement = connect.createStatement();
            // Result set get the result of the SQL query
            resultSet = statement
                    .executeQuery("select * from feedback.comments");
            writeResultSet(resultSet);
            // PreparedStatements can use variables and are more efficient
            preparedStatement = connect
                    .prepareStatement("insert into  feedback.comments values (default, ?, ?, ?, ? , ?, ?)");
            // "myuser, webpage, datum, summary, COMMENTS from feedback.comments");
            // Parameters start with 1
            preparedStatement.setString(1, "Test");
            preparedStatement.setString(2, "TestEmail");
            preparedStatement.setString(3, "TestWebpage");
            preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
            preparedStatement.setString(5, "TestSummary");
            preparedStatement.setString(6, "TestComment");
            preparedStatement.executeUpdate();
            preparedStatement = connect
                    .prepareStatement("SELECT myuser, webpage, datum, summary, COMMENTS from feedback.comments");
            resultSet = preparedStatement.executeQuery();
            writeResultSet(resultSet);
            //再次删除插入注释
            preparedStatement = connect
            .prepareStatement("delete from feedback.comments where myuser= ? ; ");
            preparedStatement.setString(1, "Test");
            preparedStatement.executeUpdate();
            resultSet = statement
            .executeQuery("select * from feedback.comments");
            writeMetaData(resultSet);
        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }
    }
    private void writeMetaData(ResultSet resultSet) throws SQLException {
        //现在从数据库中获取一些元数据
        //结果集获取SQL查询的结果
        System.out.println("The columns in the table are: ");
        System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
        for  (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
            System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
        }
    }
    private void writeResultSet(ResultSet resultSet) throws SQLException {
        // ResultSet is initially before the first data set
        while (resultSet.next()) {
            //可以通过名称获取列
            //也可以通过列号获取列
            //从1点开始
            //例如resultSet.getSTring (2);
            String user = resultSet.getString("myuser");
            String website = resultSet.getString("webpage");
            String summary = resultSet.getString("summary");
            Date date = resultSet.getDate("datum");
            String comment = resultSet.getString("comments");
            System.out.println("User: " + user);
            System.out.println("Website: " + website);
            System.out.println("summary: " + summary);
            System.out.println("Date: " + date);
            System.out.println("Comment: " + comment);
        }
    }
    // You need to close the resultSet
    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) {
        }
    }
}

执行上面的示例代码

public class Main {
public static void main(String[] args) throws Exception {
MySQLAccess dao = new MySQLAccess();
dao.readDataBase();
}
}