JavaWorkshop--(2)PreparedStatement

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

  首先，公布一下第一期的答案：

  JPC_JDBCSample1.java

  //+++(1)ImporttheJDBCpackage

  importjava.sql.*;

  classJPC_JDBCSample1{

  publicstaticvoidmain(Stringargs[]){

  Connectioncon=null;

  Statementstmt=null;

  Stringsql=null;

  ResultSetrs=null;

  try{

  //Loadthedriver

  Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

  //SetJDBCURLinavariable

  //TheURLisjdbc:db2:dbname

  Stringurl="jdbc:db2:sample";

  //+++(2)ConnecttotheDBspecifiedbytheJDBCURL

  //+++withtheuserID(db2admin)andthepassword(password).

  con=DriverManager.getConnection(url,"db2admin","password");

  //+++(3)CreateaStatementobject

  stmt=con.createStatement();

  //+++(4)SpecifySQLstatement

  sql="SELECTDEPTNUMB,DEPTNAMEFROMorg";

  //+++(5)Executeaquery

  rs=stmt.executeQuery(sql);

  //RetrievetheresultfromResultSetanddisplayit

  //+++(6)Movethecursordownonerowfromitscurrentposition

  while(rs.next()){

  //+++(7)Getadepartmentno,specifyingacolumnname

  shortdno=rs.getShort("DEPTNUMB");

  //+++(8)Obtainadepartmentnamebyspecifyingacolumnno

  Stringdname=rs.getString(2);

  System.out.print("NO="+dno);

  System.out.println(":"+dname);

  }

  }catch(ClassNotFoundExceptione){

  e.printStackTrace();

  }catch(SQLExceptione){

  while(e!=null){

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

  e=e.getNextException();

  }

  }finally{

  try{

  //Releaseresources

  rs.close();

  stmt.close();

  con.close();

  }catch(SQLExceptione){

  e.printStackTrace();

  }

  }

  }

  }

  你对了吗？下面我们开始第二个PreparedStatement，这里注释比较全，如果大家需要中文注释，请告诉我

  2.PreparedStatement

  在这个练习里面，你将用PreparedStatement对象来开发一个程序传送sql到数据库中。

  这里程序从外部的命令行里接受了一个参数。根据这个参数作为查询条件来从数据库中查询到相应的纪录。我给除了运行后的结果。

  Database:sample

  Schema:db2admin

  Table:org

  UserID:db2admin

  Password:password

  DEPTNUMBDEPTNAMEMANAGERDIVISIONLOCATION

  10HeadOffice160CorporateNewYork

  15NewEngland50EasternBoston

  20MidAtlantic10EasternWashington

  38SouthAtlantic30EasternAtlanta

  42GreekLakes100MidwestChicago

  51Plains140MidwestDallas

  66Pacific270WesternSanFrancisco

  84Mountain290WesternDenver

  JPC_JDBCSample2.java

  Fillin[1]-[5]andcompletetheapplication.

  importjava.sql.*;

  classJPC_JDBCSample2{

  publicstaticvoidmain(Stringargs[]){

  Connectioncon=null;

  PreparedStatementpstmt=null;

  Stringsql=null;

  ResultSetrs=null;

  Stringdiv=null;//Division

  shorttotal=0;//Totalamountofmanagers

  //SetaStringinputincommandlineasinthedivisionvariable

  if(args.length>0){

  div=args[0];

  }else{

  System.out.println("Pleaseinputadivision");

  return;

  }

  try{

  //Loadthedriver

  Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

  //SetJDBCURLinavariable

  //TheURLisjdbc:db2:dbname

  Stringurl="jdbc:db2:sample";

  //ConnecttotheDBspecifiedbytheJDBCURL

  //withtheuserID(db2admin)andthepassword(password)

  con=DriverManager.getConnection(url,"db2admin","password");

  //+++(1)SpecifyaSQLstatement

  sql="[1]";

  //+++(2)CreateaPreparedStatementobject

  pstmt=con.[2];

  //+++(3)SetaparameterinthePreparedStatementobject

  pstmt.[3];

  //+++(4)Executeaquery

  rs=pstmt.[4];

  //+++(5)(6)RetrievetheresultfromtheResultSet

  //+++objectanddisplayit

  System.out.println("*****"+div+"*****");

  while(rs.next()){

  Stringdname=[5];

  shortmanager=[6];

  System.out.print(dname+":");

  System.out.println(manager);

  total+=manager;

  }

  System.out.println("Total:"+total);

  }catch(ClassNotFoundExceptione){

  e.printStackTrace();

  }catch(SQLExceptione){

  e.printStackTrace();

  }finally{

  try{

  //Releaseresources

  rs.close();

  pstmt.close();

  con.close();

  }catch(SQLExceptione){

  e.printStackTrace();

  }

  }

  }

  }

  Instruction

  Openacommandpromptwindowandchangetothelabdirectory(d:/JPC/Ex/JDBC/).Compileyourapplication.

  javacJPC_JDBCSample2.java

  Runyourapplicationwithadivision(Western,Eastern,etc.).

  javaJPC_JDBCSample2Western

  Demonstration

  >javaSample2Western

  *****Western*****

  Pacific:270

  Mountain:290

  Total:560

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>