1. Statement:一个接口
oracle定义:
The object used for executing a static SQL statement and returning the results it produces.
执行静态sql,返回结果给Statement对象
Statement xxx=(Connection) xx.createStatement();
用法:
Statement state=conn.createStatement();
state.execute(sql);
2. PreparedStatement:继承于Statement的接口
oracle定义:
An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
将SQL预编译存储在PreaparedStatement对象。
PreparedStatement xxx=(Connection) xx.preapareStatment(sql);
//接收sql字符串 预编译
用法:
String sql="insert into employee(name,email) values(?,?);";
PreparedStatement pstate=conn.prepareStatement(sql);
pstate.setString(1,name);
pstate.setString(2, email);
pstate.executeUpdate();
3. CallableStatement:继承于PreparedStatement的接口
oracle定义:
The interface used to execute SQL stored procedures.
接口用于执行SQL的存储过程。存储过程在数据库创建后,可以被call调用。
可接受输入参数/输入输出参数。
Callablestatement xxx=(Connection)xxx.prepareCall("{...}");
输入参数用法:
//static void query2(String name,String email)
CallableStatement cstate=conn.prepareCall("{call insertemployee(?,?)}");
cstate.setString(1, name);
cstate.setString(2, email);
cstate.executeUpdate();
输入输出参数用法:
//static void query3(int id)
CallableStatement cstate=conn.prepareCall("{call getnamebyid(?,?)}");
cstate.setInt(1, id);
cstate.registerOutParameter(2, Types.CHAR);
cstate.execute();
String name=cstate.getString(2);
4. 关于 execute(), executeQuery(), executeUpdate()
Statement.execute(String sql)/executeQuery(String sql)/executeUpdate(String sql)
execute(sql):执行sql,可能返回多个结果
boolean execute(String sql) throws SQLException
executeQuery(sql):执行sql,返回单一结果ResultSet
ResultSet executeQuery(String sql) throws SQLException
executeUpdate(sql):执行DML或者DDL (DML即update/insert/delete。DDL即create/alter/drop + database/tabel/view)
int executeUpdate(String sql)
throws SQLException
PreparedStatement.execute()/executeQuery()/executeUpdate()
PreparedStatement重写了Statement的3个方法,不接受参数。
由于在Connection.prepareStatement(sql)里接受了sql语句预编译,不需要参数。
execute() oracle定义:执行任何sql语句 (测试insert,代替executeUpdate()执行成功)
Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
executeQuery():用于接收一个ResultSet结果,和Statement.executeQuery(String sql)一样用法
executeUpdate():用于DML和DLL,和Statement.executeUpdate(String sql)一样用法
CallableStatement.execute()/executeQuery()/executeUpdate()
由于在Connection.prepareCall("{call procedureName()}")声明使用存储过程,所以不用参数,直接继承了PreparedStatement的3个方法。
5.关于使用Statement 或 PreparedStatement
PreparedStament oracle解释:
An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
当用PreparedStatement,因为预编译,所以执行同一语句多次的时候,相比Statement更有效率。