jdbc连接实例

现在的项目开发一般都是使用既有框架下的数据库连接方式，都有比较良好的数据操作的封装。比如曾经比较流行的hibernate和现下的mybatis(ibatis)等。对于基础的jdbc连接的方式了解反而少了，了解和测试jdbc的代码，再在这些基础的代码上去增加一些数据优化处理，对于了解数据处理框架的封装，问题的解决都是比较有意义的。
下面是一个简单的jdbc的示例，根据示例可以创建一个简单的数据库连接。

maven依赖包

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.18</version>
</dependency>

数据库连接信息

    private static final String url = "jdbc:mysql://localhost:3306/test";
    private static final String name = "com.mysql.jdbc.Driver";
    private static final String username = "user";
    private static final String password = "password";

创建数据库链接

Class.forName("com.mysql.jdbc.Driver")
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "user", "password")

预处理对象PreparedStatement,这里也可以使用Statement，但是强烈建议不要使用Statement，防sql注入

PreparedStatement preparedStatement = connection.prepareStatement("sql");

执行查询sql

ResultSet resultSet = dbManager.preparedStatement.executeQuery();
while(resultSet.next()) {
    System.out.println(resultSet.getInt(0));
    System.out.println(resultSet.getString(1));
}

执行更新sql

boolean result = dbManager.preparedStatement.execute();

每次执行完sql命令后都必须关闭链接

dbManager.connection.close();
dbManager.preparedStatement.close();

完整示例：

package com.jdbc.test.dbhelper;

import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName DBManager
 * @Description
 * @Date 2020/1/6 8:29 PM
 */
public class DBManager {
    private static final String url = "jdbc:mysql://localhost:3306/test";
    private static final String name = "com.mysql.jdbc.Driver";
    private static final String username = "user";
    private static final String password = "password";

    private Connection connection = null;
    private PreparedStatement preparedStatement = null;

    private DBManager(String sql) {
        try {
            Class.forName(name);
            connection = DriverManager.getConnection(url, username, password);
            preparedStatement = connection.prepareStatement(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void close() {
        try {
            this.connection.close();
            this.preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void query(String sql) {
        DBManager dbManager = new DBManager(sql);
        try {
            ResultSet resultSet = dbManager.preparedStatement.executeQuery();
            while (resultSet.next()) {
                    System.out.println(resultSet.getInt(0));
                    System.out.println(resultSet.getString(1));
                });
            }
            resultSet.close();
            dbManager.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static boolean execute(String sql) {
        DBManager dbManager = new DBManager(sql);
        try {
            boolean result = dbManager.preparedStatement.execute();
            dbManager.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}