perl mysql dml_MySQL Connector执行SQL语句的三种方式

描述

当我们需要在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