【Java】JDBC连接MySQL

时间：2023-03-09 15:39:57
【Java】JDBC连接MySQL

JDBC连接MySQL

虽然在项目中通常用ORM的框架实现持久化。但经常因测试某些技术的需要，要写一个完整的JDBC查询数据库。写一个在这儿备份。

首先引入驱动包：

<dependencies>

    <dependency>

        <groupId>mysql</groupId>

        <artifactId>mysql-connector-java</artifactId>

        <version>5.1.38</version>

    </dependency>

</dependencies>

写一个简单的连接测试是否能查询数据：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class JDBCTester {

    public static void main(String[] args) throws Exception {

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

        System.out.println("成功加载驱动");

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        try {

            String url = "jdbc:mysql://localhost:3306/demo?user=root&password=123456&useUnicode=true&characterEncoding=UTF8";

            connection = DriverManager.getConnection(url);

            System.out.println("成功获取连接");

            statement = connection.createStatement();

            String sql = "select * from t_balance";

            resultSet = statement.executeQuery(sql);

            resultSet.beforeFirst();

            while (resultSet.next()) {

                System.out.println(resultSet.getString(1));

            }

            System.out.println("成功操作数据库");

        } catch(Throwable t) {

            // TODO 处理异常

            t.printStackTrace();

        } finally {

            if (resultSet != null) {

                resultSet.close();

            }

            if (statement != null) {

                statement.close();

            }

            if (connection != null) {

                connection.close();

            }

            System.out.println("成功关闭资源");

        }

    }

}

看到以下日志，说明程序是正确的：

成功加载驱动

成功获取连接

1

成功操作数据库

成功关闭资源

JDBC的Connection默认是自动提交

case AutoCommit 描述 结果
1 Default(true) 没有异常，没有显式commit 更新
2 Default(true) 有异常，没有显式commit 更新
3 Default(true) 有异常，没有显式commit，有显式rollback 更新，异常包含Can't call rollback when autocommit=true

case 1：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class JDBCTools {

    public static void main(String[] args) throws Exception {

        JDBCTools.query("select * from t_balance t");

        System.out.println();

        JDBCTools.execute("update t_balance t set t.balance = 20000 where t.user_id = 100");

        System.out.println();

        JDBCTools.query("select * from t_balance t");

    }

    public static String HOST = "localhost";

    public static String PORT = "3306";

    public static String DATABASE_NAME = "demo";

    public static String USER_NAME = "root";

    public static String PASSWORD = "123456";

    /**

     * 获取数据库连接

     * @return 数据库连接

     */

    public static Connection getConn() throws Exception {

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

        System.out.println("成功加载驱动");

        String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE_NAME + "?user=" + USER_NAME + "&password=" + PASSWORD + "&useUnicode=true&characterEncoding=UTF8";

        Connection connection = DriverManager.getConnection(url);

        System.out.println("成功获取连接");

        return connection;

    }

    /**

     * 关闭资源

     */

    public static void closeResource(Connection conn, Statement st, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (st != null) {

            try {

                st.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        System.out.println("成功关闭资源");

    }

    /**

     * 查询SQL

     * @param sql 查询语句

     * @return 数据集合

     * @throws SQLException

     */

    public static List<Map<String, String>> query(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        List<Map<String, String>> resultList = null;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            resultSet = statement.executeQuery(sql);

            System.out.println("SQL : " + sql);

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();

            String[] columnNames = new String[columnCount + 1];

            for (int i = 1; i <= columnCount; i++) {

                columnNames[i] = resultSetMetaData.getColumnName(i);

            }

            resultList = new ArrayList<Map<String, String>>();

            Map<String, String> resultMap = new HashMap<String, String>();

            resultSet.beforeFirst();

            while (resultSet.next()) {

                for (int i = 1; i <= columnCount; i++) {

                    resultMap.put(columnNames[i], resultSet.getString(i));

                }

                resultList.add(resultMap);

            }

            System.out.println("成功查询数据库，查得数据：" + resultList);

        } catch(Throwable t) {

            // TODO 处理异常

            t.printStackTrace();

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return resultList;

    }

    /**

     * 执行SQL

     * @param sql 执行的SQL

     * @return 操作条数

     */

    public static int execute(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        int num = 0;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            num = statement.executeUpdate(sql);

            System.out.println("SQL : " + sql);

            System.out.println("成功操作数据库，影响条数：" + num);

            // 模拟异常，用于测试事务

            /*

            if (1 == 1) {

                throw new RuntimeException();

            }

            */

        } catch(Exception e) {

            // 处理异常：回滚事务后抛出异常

            e.printStackTrace();

            // connection.rollback();

            System.out.println("事务回滚");

            throw e;

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return num;

    }

}
成功加载驱动

成功获取连接

SQL : select * from t_balance t

成功查询数据库，查得数据：[{id=1, balance=10000, user_id=100}]

成功关闭资源

成功加载驱动

成功获取连接

SQL : update t_balance t set t.balance = 20000 where t.user_id = 100

成功操作数据库，影响条数：1

成功关闭资源

成功加载驱动

成功获取连接

SQL : select * from t_balance t

成功查询数据库，查得数据：[{id=1, balance=20000, user_id=100}]

成功关闭资源

case 2 :

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class JDBCTools {

    public static void main(String[] args) throws Exception {

        JDBCTools.query("select * from t_balance t");

        System.out.println();

        JDBCTools.execute("update t_balance t set t.balance = 20000 where t.user_id = 100");

        System.out.println();

        JDBCTools.query("select * from t_balance t");

    }

    public static String HOST = "localhost";

    public static String PORT = "3306";

    public static String DATABASE_NAME = "demo";

    public static String USER_NAME = "root";

    public static String PASSWORD = "123456";

    /**

     * 获取数据库连接

     * @return 数据库连接

     */

    public static Connection getConn() throws Exception {

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

        System.out.println("成功加载驱动");

        String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE_NAME + "?user=" + USER_NAME + "&password=" + PASSWORD + "&useUnicode=true&characterEncoding=UTF8";

        Connection connection = DriverManager.getConnection(url);

        System.out.println("成功获取连接");

        return connection;

    }

    /**

     * 关闭资源

     */

    public static void closeResource(Connection conn, Statement st, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (st != null) {

            try {

                st.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        System.out.println("成功关闭资源");

    }

    /**

     * 查询SQL

     * @param sql 查询语句

     * @return 数据集合

     * @throws SQLException

     */

    public static List<Map<String, String>> query(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        List<Map<String, String>> resultList = null;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            resultSet = statement.executeQuery(sql);

            System.out.println("SQL : " + sql);

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();

            String[] columnNames = new String[columnCount + 1];

            for (int i = 1; i <= columnCount; i++) {

                columnNames[i] = resultSetMetaData.getColumnName(i);

            }

            resultList = new ArrayList<Map<String, String>>();

            Map<String, String> resultMap = new HashMap<String, String>();

            resultSet.beforeFirst();

            while (resultSet.next()) {

                for (int i = 1; i <= columnCount; i++) {

                    resultMap.put(columnNames[i], resultSet.getString(i));

                }

                resultList.add(resultMap);

            }

            System.out.println("成功查询数据库，查得数据：" + resultList);

        } catch(Throwable t) {

            // TODO 处理异常

            t.printStackTrace();

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return resultList;

    }

    /**

     * 执行SQL

     * @param sql 执行的SQL

     * @return 操作条数

     */

    public static int execute(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        int num = 0;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            num = statement.executeUpdate(sql);

            System.out.println("SQL : " + sql);

            System.out.println("成功操作数据库，影响条数：" + num);

            // 模拟异常，用于测试事务

            if (1 == 1) {

                throw new RuntimeException();

            }

        } catch(Exception e) {

            // 处理异常：回滚事务后抛出异常

            e.printStackTrace();

            // connection.rollback();

            System.out.println("事务回滚");

            throw e;

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return num;

    }

}
成功加载驱动

成功获取连接

SQL : select * from t_balance t

成功查询数据库，查得数据：[{id=1, balance=10000, user_id=100}]

成功关闭资源

成功加载驱动

成功获取连接

SQL : update t_balance t set t.balance = 20000 where t.user_id = 100

成功操作数据库，影响条数：1

事务回滚

java.lang.RuntimeException

    at JDBCTools.execute(JDBCTools.java:140)

    at JDBCTools.main(JDBCTools.java:17)

成功关闭资源

Exception in thread "main" java.lang.RuntimeException

    at JDBCTools.execute(JDBCTools.java:140)

    at JDBCTools.main(JDBCTools.java:17)

case 3 :

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class JDBCTools {

    public static void main(String[] args) throws Exception {

        JDBCTools.query("select * from t_balance t");

        System.out.println();

        JDBCTools.execute("update t_balance t set t.balance = 20000 where t.user_id = 100");

        System.out.println();

        JDBCTools.query("select * from t_balance t");

    }

    public static String HOST = "localhost";

    public static String PORT = "3306";

    public static String DATABASE_NAME = "demo";

    public static String USER_NAME = "root";

    public static String PASSWORD = "123456";

    /**

     * 获取数据库连接

     * @return 数据库连接

     */

    public static Connection getConn() throws Exception {

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

        System.out.println("成功加载驱动");

        String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE_NAME + "?user=" + USER_NAME + "&password=" + PASSWORD + "&useUnicode=true&characterEncoding=UTF8";

        Connection connection = DriverManager.getConnection(url);

        System.out.println("成功获取连接");

        return connection;

    }

    /**

     * 关闭资源

     */

    public static void closeResource(Connection conn, Statement st, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (st != null) {

            try {

                st.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        System.out.println("成功关闭资源");

    }

    /**

     * 查询SQL

     * @param sql 查询语句

     * @return 数据集合

     * @throws SQLException

     */

    public static List<Map<String, String>> query(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        List<Map<String, String>> resultList = null;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            resultSet = statement.executeQuery(sql);

            System.out.println("SQL : " + sql);

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();

            String[] columnNames = new String[columnCount + 1];

            for (int i = 1; i <= columnCount; i++) {

                columnNames[i] = resultSetMetaData.getColumnName(i);

            }

            resultList = new ArrayList<Map<String, String>>();

            Map<String, String> resultMap = new HashMap<String, String>();

            resultSet.beforeFirst();

            while (resultSet.next()) {

                for (int i = 1; i <= columnCount; i++) {

                    resultMap.put(columnNames[i], resultSet.getString(i));

                }

                resultList.add(resultMap);

            }

            System.out.println("成功查询数据库，查得数据：" + resultList);

        } catch(Throwable t) {

            // TODO 处理异常

            t.printStackTrace();

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return resultList;

    }

    /**

     * 执行SQL

     * @param sql 执行的SQL

     * @return 操作条数

     */

    public static int execute(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        int num = 0;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            num = statement.executeUpdate(sql);

            System.out.println("SQL : " + sql);

            System.out.println("成功操作数据库，影响条数：" + num);

            // 模拟异常，用于测试事务

            if (1 == 1) {

                throw new RuntimeException();

            }

        } catch(Exception e) {

            // 处理异常：回滚事务后抛出异常

            e.printStackTrace();

            connection.rollback();

            System.out.println("事务回滚");

            throw e;

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return num;

    }

}
成功加载驱动

成功获取连接

SQL : select * from t_balance t

成功查询数据库，查得数据：[{id=1, balance=10000, user_id=100}]

成功关闭资源

成功加载驱动

成功获取连接

SQL : update t_balance t set t.balance = 20000 where t.user_id = 100

成功操作数据库，影响条数：1

java.lang.RuntimeException

    at JDBCTools.execute(JDBCTools.java:140)

    at JDBCTools.main(JDBCTools.java:17)

成功关闭资源

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

    at com.mysql.jdbc.Util.getInstance(Util.java:387)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)

    at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4618)

    at JDBCTools.execute(JDBCTools.java:146)

    at JDBCTools.main(JDBCTools.java:17)

最简单的工具类

封装个简单的查询MySQL的工具类更方便使用。注：此实现非常简单，仅用于日常测试，不适合生产环境使用。

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class JDBCTools {

    public static void main(String[] args) throws Exception {

        JDBCTools.query("select * from t_balance t");

        JDBCTools.execute("update t_balance t set t.balance = 20000 where t.user_id = 100");

        JDBCTools.query("select * from t_balance t");

    }

    public static String HOST = "localhost";

    public static String PORT = "3306";

    public static String DATABASE_NAME = "demo";

    public static String USER_NAME = "root";

    public static String PASSWORD = "123456";

    /**

     * 获取数据库连接

     * @return 数据库连接

     */

    public static Connection getConn() throws Exception {

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

        System.out.println("成功加载驱动");

        String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE_NAME + "?user=" + USER_NAME + "&password=" + PASSWORD + "&useUnicode=true&characterEncoding=UTF8";

        Connection connection = DriverManager.getConnection(url);

        System.out.println("成功获取连接");

        return connection;

    }

    /**

     * 关闭资源

     */

    public static void closeResource(Connection conn, Statement st, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (st != null) {

            try {

                st.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                // TODO 处理异常

                e.printStackTrace();

            }

        }

        System.out.println("成功关闭资源");

    }

    /**

     * 查询SQL

     * @param sql 查询语句

     * @return 数据集合

     * @throws SQLException

     */

    public static List<Map<String, String>> query(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        List<Map<String, String>> resultList = null;

        try {

            connection = JDBCTools.getConn();

            statement = connection.createStatement();

            resultSet = statement.executeQuery(sql);

            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();

            String[] columnNames = new String[columnCount + 1];

            for (int i = 1; i <= columnCount; i++) {

                columnNames[i] = resultSetMetaData.getColumnName(i);

            }

            resultList = new ArrayList<Map<String, String>>();

            Map<String, String> resultMap = new HashMap<String, String>();

            resultSet.beforeFirst();

            while (resultSet.next()) {

                for (int i = 1; i <= columnCount; i++) {

                    resultMap.put(columnNames[i], resultSet.getString(i));

                }

                resultList.add(resultMap);

            }

            System.out.println("成功查询数据库，查得数据：" + resultList);

        } catch(Throwable t) {

            // TODO 处理异常

            t.printStackTrace();

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return resultList;

    }

    /**

     * 执行SQL

     * @param sql 执行的SQL

     * @return 操作条数

     */

    public static int execute(String sql) throws Exception {

        Connection connection = null;

        Statement statement = null;

        ResultSet resultSet = null;

        int num = 0;

        try {

            connection = JDBCTools.getConn();

            connection.setAutoCommit(false);

            statement = connection.createStatement();

            num = statement.executeUpdate(sql);

            System.out.println("成功操作数据库，影响条数：" + num);

            // 模拟异常，用于测试事务

            /*

            if (1 == 1) {

                throw new RuntimeException();

            }

            */

            connection.commit();

        } catch(Exception e) {

            // 处理异常：回滚事务后抛出异常

            e.printStackTrace();

            connection.rollback();

            System.out.println("事务回滚");

            throw e;

        } finally {

            JDBCTools.closeResource(connection, statement, resultSet);

        }

        return num;

    }

}

相关文章