Java的JDBC中Statement与CallableStatement对象实例
JDBCStatement对象实例
以下是利用以下三种查询以及打开和关闭说明的例子:
booleanexecute(StringSQL):返回一个布尔值true,如果ResultSet对象可以被检索,否则返回false。使用这个方法来执行SQLDDL语句,或当需要使用真正的动态SQL。
intexecuteUpdate(StringSQL):返回受影响的SQL语句执行的行数。使用此方法来执行,而希望得到一些受影响的行的SQL语句-例如,INSERT,UPDATE或DELETE语句。
ResultSetexecuteQuery(StringSQL):返回ResultSet对象。当希望得到一个结果集使用此方法,就像使用一个SELECT语句。
基于对环境和数据库安装在前面的章节中做此示例代码已被写入。
复制下面的例子中JDBCExample.java,编译并运行,如下所示:
//STEP1.Importrequiredpackages importjava.sql.*; publicclassJDBCExample{ //JDBCdrivernameanddatabaseURL staticfinalStringJDBC_DRIVER="com.mysql.jdbc.Driver"; staticfinalStringDB_URL="jdbc:mysql://localhost/EMP"; //Databasecredentials staticfinalStringUSER="username"; staticfinalStringPASS="password"; publicstaticvoidmain(String[]args){ Connectionconn=null; Statementstmt=null; try{ //STEP2:RegisterJDBCdriver Class.forName("com.mysql.jdbc.Driver"); //STEP3:Openaconnection System.out.println("Connectingtodatabase..."); conn=DriverManager.getConnection(DB_URL,USER,PASS); //STEP4:Executeaquery System.out.println("Creatingstatement..."); stmt=conn.createStatement(); Stringsql="UPDATEEmployeessetage=30WHEREid=103"; //LetuscheckifitreturnsatrueResultSetornot. Booleanret=stmt.execute(sql); System.out.println("Returnvalueis:"+ret.toString()); //LetusupdateageoftherecordwithID=103; introws=stmt.executeUpdate(sql); System.out.println("Rowsimpacted:"+rows); //Letusselectalltherecordsanddisplaythem. sql="SELECTid,first,last,ageFROMEmployees"; ResultSetrs=stmt.executeQuery(sql); //STEP5:Extractdatafromresultset while(rs.next()){ //Retrievebycolumnname intid=rs.getInt("id"); intage=rs.getInt("age"); Stringfirst=rs.getString("first"); Stringlast=rs.getString("last"); //Displayvalues System.out.print("ID:"+id); System.out.print(",Age:"+age); System.out.print(",First:"+first); System.out.println(",Last:"+last); } //STEP6:Clean-upenvironment rs.close(); stmt.close(); conn.close(); }catch(SQLExceptionse){ //HandleerrorsforJDBC se.printStackTrace(); }catch(Exceptione){ //HandleerrorsforClass.forName e.printStackTrace(); }finally{ //finallyblockusedtocloseresources try{ if(stmt!=null) stmt.close(); }catch(SQLExceptionse2){ }//nothingwecando try{ if(conn!=null) conn.close(); }catch(SQLExceptionse){ se.printStackTrace(); }//endfinallytry }//endtry System.out.println("Goodbye!"); }//endmain }//endJDBCExample
现在编译上面的例子如下:
C:>javacJDBCExample.java
当运行JDBCExample,它会产生以下结果:
C:>javaJDBCExample
Connectingtodatabase... Creatingstatement... Returnvalueis:false Rowsimpacted:1 ID:100,Age:18,First:Zara,Last:Ali ID:101,Age:25,First:Mahnaz,Last:Fatma ID:102,Age:30,First:Zaid,Last:Khan ID:103,Age:30,First:Sumit,Last:Mittal Goodbye!
JDBCCallableStatement对象实例
下面是利用CallableStatement连同下列getEmpName()的MySQL存储过程的例子:
请确定已经在EMP数据库中创建该存储过程。可以使用MySQL查询浏览器来完成它。
DELIMITER$$ DROPPROCEDUREIFEXISTS`EMP`.`getEmpName`$$ CREATEPROCEDURE`EMP`.`getEmpName` (INEMP_IDINT,OUTEMP_FIRSTVARCHAR(255)) BEGIN SELECTfirstINTOEMP_FIRST FROMEmployees WHEREID=EMP_ID; END$$ DELIMITER;
基于对环境和数据库安装在前面的章节中进行,这个范例程式码已被写入。
复制下面的例子中JDBCExample.java,编译并运行,如下所示:
//STEP1.Importrequiredpackages importjava.sql.*; publicclassJDBCExample{ //JDBCdrivernameanddatabaseURL staticfinalStringJDBC_DRIVER="com.mysql.jdbc.Driver"; staticfinalStringDB_URL="jdbc:mysql://localhost/EMP"; //Databasecredentials staticfinalStringUSER="username"; staticfinalStringPASS="password"; publicstaticvoidmain(String[]args){ Connectionconn=null; CallableStatementstmt=null; try{ //STEP2:RegisterJDBCdriver Class.forName("com.mysql.jdbc.Driver"); //STEP3:Openaconnection System.out.println("Connectingtodatabase..."); conn=DriverManager.getConnection(DB_URL,USER,PASS); //STEP4:Executeaquery System.out.println("Creatingstatement..."); Stringsql="{callgetEmpName(?,?)}"; stmt=conn.prepareCall(sql); //BindINparameterfirst,thenbindOUTparameter intempID=102; stmt.setInt(1,empID);//ThiswouldsetIDas102 //BecausesecondparameterisOUTsoregisterit stmt.registerOutParameter(2,java.sql.Types.VARCHAR); //Useexecutemethodtorunstoredprocedure. System.out.println("Executingstoredprocedure..."); stmt.execute(); //RetrieveemployeenamewithgetXXXmethod StringempName=stmt.getString(2); System.out.println("EmpNamewithID:"+ empID+"is"+empName); stmt.close(); conn.close(); }catch(SQLExceptionse){ //HandleerrorsforJDBC se.printStackTrace(); }catch(Exceptione){ //HandleerrorsforClass.forName e.printStackTrace(); }finally{ //finallyblockusedtocloseresources try{ if(stmt!=null) stmt.close(); }catch(SQLExceptionse2){ }//nothingwecando try{ if(conn!=null) conn.close(); }catch(SQLExceptionse){ se.printStackTrace(); }//endfinallytry }//endtry System.out.println("Goodbye!"); }//endmain }//endJDBCExample
现在编译上面的例子如下:
C:>javacJDBCExample.java
当运行JDBCExample,它会产生以下结果:
C:>javaJDBCExample
Connectingtodatabase... Creatingstatement... Executingstoredprocedure... EmpNamewithID:102isZaid Goodbye!