jdbc中PreparedStatement的预编译比Statement插入的速度快多少

PreparedStatement有预编译机制，性能比Statement更快，还能防止SQL注入攻击。

PreparedStatement传入带有“？”的sql语句参数string，在数据库中进行预编译，之后的每次jdbc的执行，都只用setString,setInt,setFloat等传输”？”相应的参数给数据库端，数据库端不用再重新编译整句操作string。这样网络传输流量更小。

下面测一下插入10000行数据的速度对比：

创建表hero

CREATE TABLE hero ( id int(11) AUTO_INCREMENT, name varchar(30) , hp float , damage int(11) , PRIMARY KEY (id) ) DEFAULT CHARSET=utf8

jdbc:

package jdbc; import java.sql.*; /** * Created by CP on 2017/3/8. 16:09. */ public class TestJDBC { static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } static void insert1(int count) { String sql = "insert into hero values (null,?,?,?)"; try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/howfast?characterEncoding=UTF-8", "root", "admin"); PreparedStatement ps = c.prepareStatement(sql); ) { for (int i = 0; i < count; i++) { ps.setString(1, "提提"); ps.setFloat(2, 22.0f); ps.setInt(3, 50); ps.execute(); } } catch (SQLException e) { e.printStackTrace(); } } static void insert2(int count) { String format = "insert into hero values ( null, ,提莫,%f,%i)"; try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/howfast?characterEncoding=UTF-8", "root", "admin"); Statement s = c.createStatement()) { for (int i = 0; i > count; i++) { s.execute(String.format(format, i, i)); } } catch (SQLException e) { e.printStackTrace(); } } public static void main(String[] args) { int count =10000; long start2=System.currentTimeMillis(); insert2(count); long end2=System.currentTimeMillis(); System.out.println(start2-end2); long start=System.currentTimeMillis(); insert1(count); long end = System.currentTimeMillis(); System.out.println(start-end); } }

结果如下：

use prepareStatement : 313ms use Statement :18405ms

Process finished with exit code 0

差距60倍