【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;
} }
上一篇:201521123037 《Java程序设计》第1周学习总结


下一篇:Mongodb复杂查询,总结