readdata.java

javaBean + JSP + Servlet 三层架构 校友录模版

package mypackage;

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
//import java.util.Vector;
import java.util.Collection;
import java.util.ArrayList;
//import java.sql.ResultSetMetaData;

public class ReadData {
  private Connection con;
  private Statement st;
  private ResultSet rs;

  public ReadData() {
  }

  //打开数据库的方法
  public void openDatabase() {
  	String CLASSFORNAME="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	    String SERVANDDB="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=alumniDB";
	    String USER="sa";
	    String PWD="sa";
	    
	    try {
	    	Class.forName(CLASSFORNAME);
			con = DriverManager.getConnection(SERVANDDB,USER,PWD);
			st = con.createStatement();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	private void closeDatabase() {
		try {
		  if (null!=st)
		  st.close();
		  if (null!=con)
		  con.close();
	    } catch (SQLException ex) {
		  ex.printStackTrace();
		}
	}

  //查询结果返回集合类
  public Collection getClassInfo(int classNum) {
    this.openDatabase();
    //int claNumber = classNum;
    String sql ="SELECT * FROM StuClaSch AS a INNER JOIN UserInfo AS p ON a.UId = p.UId where CId=classNum" ;
    Collection userInfo = new ArrayList();
    try {
      rs = st.executeQuery(sql);
      
      while (rs.next()) {
        String uid = rs.getString("UId");
        //String password = rs.getString("UPassword");
        String name = rs.getString("UName");
        String sex = "";
        if (rs.getBoolean("USex")) {
        	sex = "男";
        } else {
        	sex = "女";
        }
        Date birthday = rs.getDate("UBirthday");
        //String userNative = rs.getString("UNative");
        String address = rs.getString("UAddress");
        String telephone = rs.getString("UTel");
        String qq = rs.getString("UQQ");
        String msn = rs.getString("UMsn");
        String email = rs.getString("UEmail");
        int eyeable = rs.getInt("Ueyeable");
        boolean state = rs.getBoolean("UState");
        
        UserBean ub = new UserBean();
        ub.setUId(uid);
        //ub.setUserPassword(password);
        ub.setUserName(name);
        ub.setUserSex(sex);
        ub.setUserBirthday(birthday);
        //ub.setUserNative(userNative);
        ub.setUserAddress(address);
        ub.setUserTelephone(telephone);
        ub.setUserQQ(qq);
        ub.setUserMsn(msn);
        ub.setUserEmail(email);
        ub.setUserEyeable(eyeable);
        ub.setUserState(state);
        this.closeDatabase();
        userInfo.add(ub);
      }
      return userInfo;
    } catch (SQLException ex) {
      ex.printStackTrace();
      this.closeDatabase();
      return userInfo;
    }
  }

  //更新数据库，包括插入、更新、删除数据
  public void upData(String strSQL) {
	this.openDatabase();
    String sql = strSQL;
    try {
      st.executeUpdate(sql);
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      this.closeDatabase();
    }
  }
}