statement preparestatement CallableStatement

大家都知道Statement、PrepareStatement 和CallableStatement 对象，其实它们是interface，为什么JDBC2.0中要提供这三个对象呢？对于Statement就是为了实现简单的SQL语句，但是 PrepareStatement和CallableStatement是为了：

　　1) Prevent SQL inject attack

　　2) Catch of DB overflow

　　3) Readable and maintained of code

　　4) Efficiency

　　当然对于效率而言环境的模拟是不适合的，所以我们只能依据编译的过程来看，只是适合某些情况PrepareStatement可以在create时就compiled，然后invoke setXXX(num,value);

　　传递参数，当然预编译的SQL要使用"?"来做占位符；

　　Batch我想是PrepareStatement的又一大特点，如果你要batch update some data，so you should choosePreparesStatement;

　　For Example:

　　PreparedStatement st=null;

　　String sql = "update BOOKS " +"set bookid = ? where authorlike ?";

　　st = con.prepareStatement(sql);

　　int [] salesForWeek = {11, 24, 43, 67, 85};

　　String [] author= {"Colom", "French", "Blom", "Decaf", "Qop"};

　　int len = author.length;

　　for(int i = 0; i < len; i++) {

　　st.setInt(1, salesForWeek[i]);

　　st.setString(2, authoe[i]);

　　st.executeUpdate();

　　}

　　PreparedStatement stmt = conn.prepareStatement(

　　"insert into client values(?,?, ?)");

　　User[] user = new User();

　　for(int i=0; i<user.length; i++) {

　　stmt.setInt(1, user[i].getID());

　　stmt.setString(2, user[i].getName());

　　stmt.setString(3, user[i].getPassword());

　　stmt.addBatch( );

　　}

　　stmt.executeBatch();

　　Statement是最基础的JDBC对象，由Connection的createStatement()生成，主要作用于数据的CRUD；缺点在于参数的拼接可能导致SQL inject；

　　CallableStatement是继承Statement和PreparedStatement的，所以可以处理参数和有预编译的功能；参数的处理有三种：in、out、inout；初级的鸟可能用的比较少，它主要用于调

　　用storedprocedure：

　　DatabaseMetaData dbmd=conn.getMetadata();

　　if (dbmd.supportsNamedParameters()==true){

　　CallableStatement st= con.prepareCall("{call procedure_name(?, ?)}");

　　st.registerOutParameter(1, java.sql.Types.TINYINT);

　　st.registerOutParameter(2, java.sql.Types.DECIMAL,3);

　　cstmt.executeQuery();

　　byte x = cstmt.getByte(1);

　　java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);

　　}

　　else{ ...... }

　　PrepareStatement和CallableStatement是实际开发中用的最多的JDBC对象，无论从安全性和效率上讲都是不错的.

　　Statement对象用于将 SQL 语句发送到数据库中，执行对数据库的数据的检索或者更新。它有2个子类，CallableStatement, PreparedStatement（确切的说是接口。）现在就大体说说他们的用法。

　　Statement 对象用于执行不带参数的简单 SQL 语句；PreparedStatement 对象用于执行带或不带 IN 参数的预编译 SQL 语句；CallableStatement 对象用于执行对数据库已存储过程的调用。

　　1.数据库的连接

　　和其他的语言一样，java的数据库连接需要如下参数。

　　数据库的驱动程序，数据库的host,端口和数据库名(URL)，数据库登陆用户名，数据库登陆password。

　　以oracle数据库为例：

　　一个数据库连接如下建立

　　Class.forName("oracle.jdbc.driver.OracleDriver");/ /取得Oracle的数据驱动，应用不同的数据驱动可以用不同的class路径。

　　connection = DriverManager.getConnection(url,username,password) ;//取得数据库连接

　　取得Statement对象：

　　statement = connection.createStatement();

　　connection取得statement还有别的方法，比如

　　createStatement(int resultSetType, int resultSetConcurrency)

　　createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)

　　可以根据不同的需要传参数取得相应特征的statement

　　用法：（关于statement的用法只是按照个人使用时的感觉来描述的，实际的用法肯定没有这么狭隘）

　　对于where条件不确定的sql查询或者登陆语句可以用这个statement对象。

　　比如，对数据库只访问一次，实现对于一个用户信息进行一次的检索操作。用户信息包括如下字段：用户名，性别，住址，用户类别等等

　　业务逻辑在进行检索的时候，要根据用户名，性别，住址，用户类别等进行检索，但是用户的检索条件不确定。可能只根据其中的几项进行检索。

　　这个时候sql无法确定，需要根据某个字段的值是否入力来确定是否要这个检索条件。用statement来执行相应的sql就可以了。

　　当然，用Statement执行其他类别的检索也可以。

　　取得 PreparedStatement对象：

　　preparedStatement = connection.prepareStatement(String sql);

　　PreparedStatement继承了Statement类，所以取得statement也有别的方法

　　用法：

　　对于数据库的检索或更新1条记录，并有批量数据（针对单条记录）要进行操作。

　　比如，对数据库只访问一次，要插入一条用户信息。用户信息包括用户名，性别，住址，用户类别等等。

　　如果用statement类，需要频繁的拼写sql文，而且容易出错。

　　如果用PreparedStatement，可以如下操作。

　　预先取得要插入数据库的各个变量。

　　sql = "INSERT INTO TABLENAME (A,B,C,D,E,F,G...)VALUES(?,?,?,?,?,?,?,...)" 调用preparedStatement.setString(int parameterIndex, String x)方法，依次设定要插入的参数。

　　PreparedStatement还包括针对数据库字段各种类型的set方法。当然也可以利用sql函数来实现对于数据类型的转换。

　　比如对于Date型的字段，可以直接用PreparedStatement的setDate(int parameterIndex, Date x, Calendar cal) 方法来设定字段值，也可以在sql文中写入 TO_DATE(?,’yyyy/mm/dd’)，直接调用setString方法传入一个是yyyy/mm/dd格式的字符串就可以了。

　　用这个类的好处就是将变量的取得和sql文的拼写区分开来。比较清晰。

　　取得CallableStatement对象：

　　callableStatement = connection.prepareCall(String sql);

　　CallableStatement继承了Statement类，所以取得statement也有别的方法

　　用法：

　　CallableStatement对象可以返回参数，所以可以返回某次数据库操作的结果。这个我一般用于对数据库需要根据数据的不同和存在与否进行多次数据库访问的操作。并将自定义的操作结果返回给java程序。一般会写一个存储过程，将数据库的复杂操作封装在这个存储过程中。

　　比如，简单的说，在插入一条用户数据的时候，首先要判断用户是否存在，存在的话只做更新，不存在的话插入。而更新操作后还要更新数据库里别的对应的表。

　　这个时候就把这些操作都写在存储过程中，比如PLSQL。然后通过

　　CallableStatement执行这个调用存储过程的SQL文。比如，PLSQL的package名为userManager,调用的过程为update_user_info()，其中这个过程的参数有in参数和out参数。

　　sql=”call userManager.update_user_info(…)”

　　取得CallableStatement对象后，要设定IN参数（同PreparedStatement）。同时要注册输出参数。

　　用如下方法注册输出参数CallableStatement.registerOutParameter(int parameterIndex, int sqlType)

　　然后执行这次数据库操作后，就完成了。

　　以上就是个人对java中Statement的一点看法，如有错误，请指正讨论。

　　以上没有考虑对SqlException异常的捕捉。

　　Statement ─ 由方法 createStatement 所创建。createStatement不会初始化，没有预处理，没次都是从0开始执行SQLStatement 对象用于发送简单的 SQL 语句。

　　PreparedStatement ─ 由方法 prepareStatement 所创建。会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率.PreparedStatement 对象用于发送带有一个或多个输入参数（ IN 参数）的 SQL 语句。PreparedStatement 拥有一组方法，用于设置 IN 参数的值。执行语句时，这些 IN 参数将被送到数据库中。PreparedStatement 的实例扩展了 Statement ，因此它们都包括了 Statement 的方法。PreparedStatement 对象有可能比 Statement 对象的效率更高，因为它已被预编译过并存放在那以供将来使用.prepareStatement解决有关特殊字符插入到数据库的问题。如 (',",),?)

　　CallableStatement ─ 由方法 prepareCall 所创建。CallableStatement 对象用于执行 SQL 储存程序 ─ 一组可通过名称来调用（就象函数的调用那样）的 SQL 语句。CallableStatement 对象从 PreparedStatement 中继承了用于处理 IN 参数的方法，而且还增加了用于处理 OUT 参数和 INOUT 参数的方法。

　　Statement 接口提供了执行语句和获取结果的基本方法。PreparedStatement 接口添加了处理 IN 参数的方法；而 CallableStatement 添加了处理 OUT 参数的方法。

　　PreparedStatement:对于同一条语句的多次执行,Statement每次都要把SQL语句发送给数据

　　库,这样做效率明显不高,而如果数据库支持预编译,PreparedStatement可以先把要执行的语句一次发给它,然后每次执行而不必发送相同的语句,效率当然提高,当然如果数据库不支持预编译,

　　PreparedStatement会象Statement一样工作,只是效率不高而不需要用户工手干预.另外PreparedStatement还支持接收参数.在预编译后只要传输不同的参数就可以执行,大大提高了性能.

　　CallableStatement:是PreparedStatement的子类,它只是用来执行存储过程的.

　　prepareCall()方法错误地放到某个循环体中

　　Connection conn = null;

　　PreparedStatement stmt = null;

　　CallableStatement cs = null;

　　String sql = null;

　　try {

　　InitialContext ic = new InitialContext();

　　DataSource ds = (DataSource)ic.lookup("ECSN/DS/SMSCenter");

　　conn = ds.getConnection();

　　for(int i = 0; i < 1000; i++) {

　　String sql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?) }";

　　cs = conn.prepareCall(sql); // 一般prepareCall()方法不应该放到循环体中

　　cs.registerOutParameter(1, OracleTypes.NUMBER);

　　cs.setString(2, "13701163936");

　　cs.setString(3, "短信内容");

　　cs.execute();

　　int oks = cs.getInt(1);

　　// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中

　　// 执行到第299次时，抛异常ORA-01000: maximum open cursors exceeded

　　// 注：连接的数据库为oracle10g，open_cursors = 300
Statement、PreparedStatement、CallableStatement(2)

　　}

　　}catch(Exception e){

　　System.out.println(e.getMessage());

　　}finally{

　　// 把ResultSet，Statement，Connection的close()调用，放在finally块中

　　// 即使try块中程序抛了未catch的异常（如RuntimeException或Error），也能确保被执行

　　// 从而避免了连接长时间不关闭，导致连接池泄漏（leakage）

　　if (cs != null) try { cs.close(); } catch (Exception e) { }

　　if (conn != null) try { conn.close(); } catch (Exception e) { }

　　}

　　Connection conn = null;

　　PreparedStatement stmt = null;

　　CallableStatement cs = null;

　　String sql = null;

　　try {

　　InitialContext ic = new InitialContext();

　　DataSource ds = (DataSource)ic.lookup("ECSN/DS/SMSCenter");

　　conn = ds.getConnection();

　　for(int i = 0; i < 1000; i++) {

　　String sql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?) }";

　　cs = conn.prepareCall(sql); // 一般prepareCall()方法不应该放到循环体中

　　cs.registerOutParameter(1, OracleTypes.NUMBER);

　　cs.setString(2, "13701163936");

　　cs.setString(3, "短信内容");

　　cs.execute();

　　int oks = cs.getInt(1);

　　// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中

　　// 执行到第299次时，抛异常ORA-01000: maximum open cursors exceeded

　　// 注：连接的数据库为oracle10g，open_cursors = 300

　　}

　　}catch(Exception e){

　　System.out.println(e.getMessage());

　　}finally{

　　// 把ResultSet，Statement，Connection的close()调用，放在finally块中

　　// 即使try块中程序抛了未catch的异常（如RuntimeException或Error），也能确保被执行

　　// 从而避免了连接长时间不关闭，导致连接池泄漏（leakage）

　　if (cs != null) try { cs.close(); } catch (Exception e) { }

　　if (conn != null) try { conn.close(); } catch (Exception e) { }

　　}

　　原因：上述代码将prepareCall()函数放在了循环体中，而且语句执行完后又不关闭，导致oralce会话打开游标数超过最大上限 open_cursors。一般来说，preparedStatement()和prepareCall()函数每调用一次，即打开一个新的游标。

　　解决方法：将prepareCall()挪到循环体外，即可解决此问题。当然，也可以循环体内每次执行完后，调用cs.close()。但这样的话jvm负担加重，因为cs对象不断被创建抛弃。

　　String sql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?) }";

　　cs = conn.prepareCall(sql); // 挪到循环体外，避免游标打开过多

　　cs.registerOutParameter(1, OracleTypes.NUMBER);

　　for(int i = 0; i < 1000; i++) {

　　cs.setString(2, "13701163936");

　　cs.setString(3, "短信内容");

　　cs.execute();

　　int oks = cs.getInt(1);

　　// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中

　　// 执行到第299次时，抛异常ORA-01000: maximum open cursors exceeded

　　// 注：连接的数据库为oracle10g，open_cursors = 300

　　}

　　String sql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?) }";

　　cs = conn.prepareCall(sql); // 挪到循环体外，避免游标打开过多

　　cs.registerOutParameter(1, OracleTypes.NUMBER);

　　for(int i = 0; i < 1000; i++) {

　　cs.setString(2, "13701163936");

　　cs.setString(3, "短信内容");

　　cs.execute();

　　int oks = cs.getInt(1);

　　// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中

　　// 执行到第299次时，抛异常ORA-01000: maximum open cursors exceeded

　　// 注：连接的数据库为oracle10g，open_cursors = 300

　　}

　　CallableStatement的批处理

　　PreparedStatement的批处理非常简单，网上范例也很多，此处不必赘言。而CallableStatement的批处理很少使用，可能是这样做的性能改善并不像PreparedStatement那么明显。因为CallableStatement是PreparedStatement的子类，所以也继承了executeBatch()方法，具有批次执行的功能。但限制条件也很苛刻：存储过程必须全是in参数，而不能有out或inout 参数。即只能调用空参，或参数全为IN的procedure，而不能是function。如果具有out参数，则一旦调用 cs.registerOutParameter()方法即报错。

　　在[1]的7.1.3的范例中，列举了CallableStatement的批处理写法：

　　view plaincopy to clipboardprint?

　　CallableStatement cstmt = con.prepareCall("{call updatePrices(?, ?)}");

　　cstmt.setString(1, "Colombian");

　　cstmt.setFloat(2, 8.49f);

　　cstmt.addBatch();

　　cstmt.setString(1, "Colombian_Decaf");

　　cstmt.setFloat(2, 9.49f);

　　cstmt.addBatch();

　　int [] updateCounts = cstmt.executeBatch();

　　CallableStatement cstmt = con.prepareCall("{call updatePrices(?, ?)}");

　　cstmt.setString(1, "Colombian");

　　cstmt.setFloat(2, 8.49f);

　　cstmt.addBatch();

　　cstmt.setString(1, "Colombian_Decaf");

　　cstmt.setFloat(2, 9.49f);

　　cstmt.addBatch();

　　int [] updateCounts = cstmt.executeBatch();

　　疑问：updateCount到底是通过什么方式返回的呢？在文档中，也只是泛泛地提到："... Further, the stored procedure must return an update count..."，因为批处理不能调用function，所以肯定不是通过函数返回值传递，那又通过什么方式呢？是不是通过SQL%ROWCOUNT来传递，试了好像不起作用。

　　通过setExecuteBatch()方法和标准的executeUpdate()方法实现批量处理更新和插入。

　　如果成批地处理插入和更新操作，就能够显著地减少它们所需要的时间。Oracle提供的Statement和 CallableStatement并不真正地支持批处理，只有PreparedStatement对象才真正地支持批处理。我们可以使用 addBatch()和executeBatch()方法选择标准的JDBC批处理，或者通过利用PreparedStatement对象的 setExecuteBatch()方法和标准的executeUpdate()方法选择速度更快的Oracle专有的方法。要使用Oracle专有的批处理机制，可以以如下所示的方式调用setExecuteBatch()：

　　PreparedStatement pstmt3D null;

　　try {

　　((OraclePreparedStatement)

　　pstmt).setExecuteBatch(30);

　　...

　　pstmt.executeUpdate();

　　}

　　Jdbc 和hibernate

　　一、Jdbc是java连接数据库的基础，无论那种框架,只要用到数据库都需要jdbc，所以掌握了jdbc的细节了才能用好更高级的框架，如hibernate。

　　以下总结了jdbc的性能和效率（主要从处理时间和内存消耗上做出比较），希望能给大家带了益处。

　　1． Statement和PreparedStatement

　　2. 插入数据（一次1万条）。

　　3. 读数据。

　　4. 脏数据的问题。

　　5. 锁表。

　　6. 索引

　　7. 索引和select

　　8. 索引和insert,update

　　1. Statement和PreparedStatement（后续有例子）

　　1) Statement

　　特点：statement不会初始化，没有预处理，每次都是从0开始执行SQL。

　　优点：sql语句直观；对于只执行一次的sql（就是sql语句执行一次后就基本不再用了）存取的时侯， Statement比PreparedStatement 性能高。因为PreparedStatement需要预编译。

　　缺点：对于需要反复执行的sql语句，则效率远远低于PreparedStatement。

　　对于oracle，会缓存sql语句，如果以Statement方式多次提交的相同语句sql，其中仅参数不同，oracle也会作为不同的语句缓存起来，可以查看v$sqlarea.

　　这就是为什么叶飞要发邮件强调的“绑定变量问题”，垃圾sql多了。 呵呵，我们新业务部在这个问题上受到了表扬。

　　2）PreparedStatement

　　特点：prepareStatement会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率。

　　优点：当sql重复多次执行时，PreparedStatement将会大大降低运行时间，加快访问数据库的速度；参数可以有?表示，方便和易读; 安全和可靠:保证数据的合法性和有效性。较少的占用Oracle的缓存空间。

　　对于大数据量的操作可以用批处理的方式进行，大大提高了性能。

　　2. 插入数据：1）Statement插入2）PreparedStatement插入3）PreparedStatement. executeBatch()插入。4）CallableStatement。

　　注：1) 用CallableStatement存储过程插入数据比较少见，我这个存储过程仅仅传来一个数字参数，来告诉存储过程要插入多少数据，仅仅想看看在写入相同的数据时消耗在网络上时间有多少。

　　2）对于oracle, 一张表如果并发的insert和update比较频繁时可以设置in..参数。

　　有一个表t2(id ,name)，分别用各种方式单独写入1万条数据。

　　测试时间：晚12：30。环境 我自己的机器连接19的oracle服务器。

　　每种情况单独运行3次的平均值。

　　方式

　　记录数insert into t2(id,name)values(?,?)

　　处理时间(s)

　　内存消耗（M）

　　写入oracle最大数据量/秒

　　数据多时：

　　语句insert into t2(id,name)values(?,?)

　　Statement插入

　　写入1万条

　　(42.819+43.738+43.233)/3=43.26S

　　(0.046+0.046+0.036)/3=0.042M

　　231条/每秒

　　PreparedStatement插入

　　写入1万条

　　(8.178+8.364+8.38)/3=8.30S

　　0.096+0.096+0.090=0.094M

　　1204条/每秒

　　PreparedStatement. executeBatch()

　　写入1万条

　　(0.328+0.313+0.312)/3=0.32S

　　(0.164+0.165+0.167)/3=0.165M

　　31250条/每秒（经验证8万条数据才用时1S）

　　CallableStatement

　　写入1万条

　　0.812+1.078+0.797=0.90S

　　忽略（和我写的存储过程有关）

　　11111条/每秒

　　数据少时：

　　Statement插入

　　写入1条

　　0.078S

　　PreparedStatement插入

　　写入1条

　　0.172s

　　PreparedStatement. executeBatch()

　　写入1条

　　0.156s

　　CallableStatement

　　写入1条

　　0.203s

　　我的存储过程有返回参数，因此网络占据了一部分时间
可以看出在数据多时：PreparedStatement. executeBatch()的效率最高，Statement的效率最低。

　　在数据少时：Statement的效率最高，存储过程的效率最低。说明下，我的存储过程有返回参数，因此网络占据了一部分时间。

　　在使用PreparedStatement. executeBatch()插入数据时，8万条才用时1S，可见数据量越大性能越高。

　　3. 读数据

　　关于读数据可以说是

　　1）ResultSet.TYPE_FORWARD_ONLY：默认的游标方式 Statement、 PreparedStatement读和 CallableStatement读（取出游标）

　　2）ResultSet.TYPE_SCROLL_INSENSITIVE方式读

　　3) ResultSet.TYPE_SCROLL_SENSITIVE 方式读

　　方式

　　记录数

　　第一次处理时间(s)

　　第一次内存消耗（M）

　　第二次处理时间（S）

　　第二次内存消耗

　　写入oracle最大数据量/秒

　　TYPE_FORWARD_ONLY

　　Statemen读

　　读100万条

　　103.213+ 118.941 +111.929

　　11.97+11.97+11.97

　　106.384+ 118.255+101.745

　　10.70+10.70+10.70

　　TYPE_FORWARD_ONLY

　　PreparedStatement读

　　读100万条

　　90.343+83.767+83.611