描述
当我们需要在Java程序中与数据库进行交互,可能首先想到的是使用某个ORM框架,因为ORM框架封装了一些实现细节,在使用上非常方便,并且一定程度上可以提升代码稳定性。
在ORM框架中,都会依赖MySQL Connector包,因为真正与数据库进行交互的是在MySQL Connector包里面实现。
5.x版本maven依赖:
mysql
mysql-connector-java
5.1.47
三种方式
MySQL Connector执行SQL语句主要有executeQuery,executeUpdate,execute等三种方式。
executeQuery
此方法执行Selec查询语句,通过ResultSet返回结果集。
private static void executeQuery() throws Exception{
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
connection = DriverManager.getConnection(url);
statement = connection.createStatement();
statement.setFetchSize(Integer.MIN_VALUE);
String sql = "select * from user";
rs = statement.executeQuery(sql);
int count=0;
while (rs.next()){
count++;
}
System.out.println("executeQuery count: " + count);
} catch (Exception e){
e.printStackTrace();
} finally {
close(connection,statement,rs);
}
}
executeUpdate
此方法执行Insert,Update,Delete语句,返回变更影响的行数。
private static void executeUpdate() throws Exception{
Connection connection = null;
Statement statement = null;
int updateCount = 0;
try {
connection = DriverManager.getConnection(url);
statement = connection.createStatement();
String sql = "update user set name='啊啊啊' where id = " + new Random().nextInt(999999);
updateCount = statement.executeUpdate(sql);
System.out.println("executeUpdate count: " + updateCount);
} catch (Exception e){
e.printStackTrace();
} finally {
close(connection,statement,null);
}
}
execute
当我们不知道来源SQL是Select查询还是Insert/Update/Delete更新时,可以统一使用excute()方法来执行SQL语句,此方法返回一个boolean值,如果返回true,表示执行的SQL语句为Select查询语句,此时可以通过Statement#getResultSet()方法来获取结果集;如果返回false,表示执行的时Insert/Update/Delete语句,此时可以通过Statement#getUpdateCount()来返回此次SQL执行对数据库影响的行数。
private static void executeForSelect() throws Exception{
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
connection = DriverManager.getConnection(url);
statement = connection.createStatement();
statement.setFetchSize(Integer.MIN_VALUE);
String sql = "select * from user";
if (statement.execute(sql)){
rs = statement.getResultSet();
}
int count=0;
while (rs.next()){
count++;
}
System.out.println("executeForSelect count: " + count);
} catch (Exception e){
e.printStackTrace();
} finally {
close(connection,statement,rs);
}
}
private static void executeForUpdate() throws Exception{
Connection connection = null;
Statement statement = null;
int updateCount = 0;
try {
connection = DriverManager.getConnection(url);
statement = connection.createStatement();
String sql = "update user set name='啊啊啊' where id = " + new Random().nextInt(999999);
if (!statement.execute(sql)){
updateCount = statement.getUpdateCount();
}
System.out.println("executeForUpdate updateCount: " + updateCount);
} catch (Exception e){
e.printStackTrace();
} finally {
close(connection,statement,null);
}
}
验证
执行测试:
public static void main(String[] args) throws Exception {
Long start = System.currentTimeMillis();
executeForSelect();
System.out.println("executeForSelect 耗时: " + (System.currentTimeMillis() - start) + " ms \n");
start = System.currentTimeMillis();
executeForUpdate();
System.out.println("executeForUpdate 耗时: " + (System.currentTimeMillis() - start) + " ms \n");
start = System.currentTimeMillis();
executeQuery();
System.out.println("executeQuery 耗时: " + (System.currentTimeMillis() - start) + " ms \n");
start = System.currentTimeMillis();
executeUpdate();
System.out.println("executeUpdate 耗时: " + (System.currentTimeMillis() - start) + " ms \n");
}
private static void close(Connection connection, Statement statement, ResultSet rs) throws Exception{
if (rs != null){
rs.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
}
返回结果:
executeForSelect count: 4717924
executeForSelect 耗时: 4791 ms
executeForUpdate updateCount: 1
executeForUpdate 耗时: 6 ms
executeQuery count: 4717924
executeQuery 耗时: 4340 ms
executeUpdate count: 1
executeUpdate 耗时: 8 ms