JDBC实例 Statement,PreparedStatement,CallableStatement三个方法的实例

public void ListStudents() throws SQLException{ int i, NoofColumns; String StNo, StFName, StLName; //初始化并加载JDBC-ODBC驱动程序 Class.forName("jdbc.odbc.JdbcOdbcDriver"); //创建连接对象 Connection Ex1Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa"); //创建一个简单的Statement对象 Statement Ex1Stmt = Ex1Con.createStatement(); //创建SQL串，传送到DBMS并执行SQL语句 ResultSet Ex1rs = Ex1Stmt.executeQuery("SELECT StudentID, FirstName, LastName FROM Students"); //处理每一个数据行，直到不再有数据行 System.out.println("Student Number First Name Last Name"); while(Ex1rs.next()){ //将列值保存到java变量中 StNo = Ex1rs.getString(1); StFName = Ex1rs.getString(2); StLName = Ex1rs.getString(3); System.out.println(StNo, StFName, StLName); } } public void UpdateStudentName(String StFName, String StLName, String StNo) throws SQLException, ClassNotFoundException { int RetValue; //初始化并加载JDBC-ODBC驱动程序 Class.forName("jdbc.odbc.JdbcOdbcDriver"); //创建连接对象 Connection Ex1Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa"); //创建一个简单的Statement对象 Statement Ex1Stmt = Ex1Con.createStatement(); //创建SQL串，传送到DBMS并执行该SQL语句 String SQLBuffer = "UPDATE Students SET FirstName = " + StFName + ",LastName = " + StLName + "WHERE StudentNumber = " + StNo; RetValue = Ex1Stmt.executeUpdate(SQLBuffer); System.out.println("Updated" + RetValue + "rows in the Database."); } //使用PreparedStatement改进实例 //Declare class variables Connection Con; PreparedStatement PrepStmt; boolean Initialized = false; public void InitConnection() throws SQLException, ClassNotFoundException{ //Initialize and load the JDBC-ODBC driver. Class.forName("jdbc.odbc.JdbcOdbcDriver"); //Make the connection object. Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa"); //Create a prepared Statement object. PrepStmt = Con.prepareStatement("SELECT ClassName, Location, DaysAndTimes FROM Classes WHERE ClassName = ?"); Initialized = true; } public void ListOneClass(String ListClassName) throws SQLException, ClassNotFoundException{ int i, NoOfColumns; String ClassName, ClassLocation, ClassSchedule; if(!Initialized){ InitConnection(); } //Set the SQL parameter to the one passed into this method PrepStmt.setString(1, ListClassName); ResultSet Ex1rs = PrepStmt.executeQuery(); //Process each row until there are no more rows and display the results on the console. System.out.println("Class Location Schedule"); while(Ex1rs.next()){ ClassName = Ex1rs.getString(1); ClassLocation = Ex1rs.getString(2); ClassSchedule = Ex1rs.getString(3); System.out.println(ClassName,ClassLocation,ClassSchedule); } } //使用CallableStatement显示成绩 //预先定义好的存储过程的调用形式为：studentGrade = getStudentGrade(StudentID, ClassID) public void DisplayGrade(String StudentID, String ClassID) throws SQLException { int Grade; //Initialize and load the JDBC-ODBC dirver. Class.forName("jdbc.odbc.JdbcOdbcDriver"); //Make the connection object; Connection Con = DriverManager.getConnection("jdbc:odbc:studentDB";uid="admin";pw="sa"); //Create a Callable Statement object; CallableStatement CStmt = Con.prepareCall({? = call getStudentGrade[?,?]}); //Now tie the placeholders with actual parameters. //Register the return value from the stored procedure //as an integer type so that the driver knows how to handle it. //Note the type is defined in the java.sql.Types. CStmt.registerOutParameter(1,java.sql.Types.INTEGER); //Set the In parameters (which are inherited from the PreparedStatement class) CStmt.setString(1,StudentID); CStmt.setString(2,ClassID); //Now we are ready to call the stored procedure int RetVal = CStmt.excuteUpdate(); //Get the OUT Parameter from the registered parameter //Note that we get the result from the CallableStatement object Grade = CStmt.getInt(1); //And display the results on the console; System.out.println("The Grade is:" + Grade); }