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);
}