JDBC连接MySQL

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

首先引入驱动包：

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>
</dependencies>

View Code

 

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

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("成功关闭资源");
        }
        
    }

}

View Code

 

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

成功加载驱动
成功获取连接
1
成功操作数据库
成功关闭资源

View Code

 

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;
    }
    
}

View Code 

成功加载驱动
成功获取连接
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}]
成功关闭资源

View Code

 

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;
    }
    
}

View Code 

成功加载驱动
成功获取连接
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)

View Code

 

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;
    }
    
}

View Code 

成功加载驱动
成功获取连接
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)

View Code

 

最简单的工具类

封装个简单的查询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;
    }
    
}

 

版权声明：本文为nick-huang原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/nick-huang/p/5628779.html