package com.sjsq.model;
import java.util.ArrayList;
import java.util.List;
/**
 * 学生实体类
 * @author shuijianshiqing
 *
 */
public class Student {
  // 学号
  private String sId;
  // 姓名
  private String sName;
  // 性别
  private String sSex;
  // 学院
  private String sCollege;
  private String sC;
  private String sMath;
  private String sEnglish;
  private String sChinese;
  private String sJava;
  public Student() {
    super();
  }
  public Student(String sId, String sName, String sSex, String sCollege, String sC, String sMath,
      String sEnglish, String sChinese, String sJava) {
    super();
    this.sId = sId;
    this.sName = sName;
    this.sSex = sSex;
    this.sCollege = sCollege;
    this.sC = sC;
    this.sMath = sMath;
    this.sEnglish = sEnglish;
    this.sChinese = sChinese;
    this.sJava = sJava;
  }
  public static List<Student> students=new ArrayList<Student>();
  public String getsId() {
    return sId;
  }
  public void setsId(String sId) {
    this.sId = sId;
  }
  public String getsName() {
    return sName;
  }
  public void setsName(String sName) {
    this.sName = sName;
  }
  public String getsSex() {
    return sSex;
  }
  public void setsSex(String sSex) {
    this.sSex = sSex;
  }
  public String getsCollege() {
    return sCollege;
  }
  public void setsCollege(String sCollege) {
    this.sCollege = sCollege;
  }
  public String getsC() {
    return sC;
  }
  public void setsC(String sC) {
    this.sC = sC;
  }
  public String getsMath() {
    return sMath;
  }
  public void setsMath(String sMath) {
    this.sMath = sMath;
  }
  public String getsEnglish() {
    return sEnglish;
  }
  public void setsEnglish(String sEnglish) {
    this.sEnglish = sEnglish;
  }
  public String getsChinese() {
    return sChinese;
  }
  public void setsChinese(String sChinese) {
    this.sChinese = sChinese;
  }
  public String getsJava() {
    return sJava;
  }
  public void setsJava(String sJava) {
    this.sJava = sJava;
  }
  @Override
  public String toString() {
    return "Student [sId=" + sId + ", sName=" + sName + ", sSex=" + sSex + ", sCollege=" + sCollege + ", sC="
        + sC + ", sMath=" + sMath + ", sEnglish=" + sEnglish + ", sChinese=" + sChinese + ", sJava="
        + sJava + "]";
  } 
}

package com.sjsq.dao;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.sjsq.model.Student;
import com.sjsq.util.DBUtil;
public class StudentDao {
  public static int progress;
  // private final String FILE_PATH="d:\\student.xls";
  public static StudentDao getInstance() {
    StudentDao studentDao;
    return studentDao = new StudentDao();
  }
  // 查询所有学生的信息
  public ResultSet queryAll() {
    ResultSet rs = null;
    String sql = "select * from Student";
    System.out.println("------查询所有学生信息------");
    System.out.println("sql语句:" + sql);
    DBUtil db = new DBUtil();
    try {
      rs = db.executeQuery(sql);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return rs;
  }
  // 查询所有学生信息，并且返回List
  public List<Student> queryAllStudent() {
    List<Student> listStudent = new ArrayList<Student>();
    String sql = "select * from student";
    System.out.println("------查询所有学生信息------");
    System.out.println("sql语句:" + sql);
    DBUtil db = new DBUtil();
    ResultSet rs = null;
    try {
      rs = db.executeQuery(sql);
      while (rs.next()) {
        Student studentTmp = new Student();
        studentTmp.setsId(rs.getString("sId"));
        studentTmp.setsName(rs.getString("sName"));
        studentTmp.setsSex(rs.getString("sSex"));
        studentTmp.setsCollege(rs.getString("sCollege"));
        studentTmp.setsC(rs.getString("sC"));
        studentTmp.setsMath(rs.getString("sMath"));
        studentTmp.setsEnglish(rs.getString("sEnglish"));
        studentTmp.setsChinese(rs.getString("sChinese"));
        studentTmp.setsJava(rs.getString("sJava"));
        listStudent.add(studentTmp);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
      db.close();
    }
    return listStudent;
  }
  public static int getprogress() {
    return progress;
  }
  // 按照条件查询
  public ResultSet queryByCondition(String[] data) {
    ResultSet rs = null;
    String sql = "select * from student where 1=1";
    int i = 0;
    // 学号
    if (!(data[0].equals(""))) {
      sql += " and sid='" + data[0] + "' ";
    }
    // 姓名
    if (!(data[1].equals(""))) {
      sql += " and sname='" + data[1] + "' ";
    }
    // 省份
    if (!(data[2].equals(""))) {
      sql += " and senglish='" + data[2] + "' ";
    }
    // 性别
    if (!(data[3].equals(""))) {
      sql += " and ssex='" + data[3] + "' ";
    }
    System.out.println("------查询所有学生信息------");
    System.out.println("sql语句:" + sql);
    DBUtil db = new DBUtil();
    try {
      rs = db.executeQuery(sql);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return rs;
  }
  // 插入学生信息，并且返回插入结果
  public boolean insertStudentInformation(Student student) {
    DBUtil DBUtil = new DBUtil();
    // 通过student对象设置sid的值
    String sid = student.getsId();
    String sname = student.getsName();
    String ssex = student.getsSex();
    String scollege = student.getsCollege();
    String sc = student.getsC();
    String smath = student.getsMath();
    String senglish = student.getsEnglish();
    String schinese = student.getsChinese();
    String sjava = student.getsJava();
    String sql = "insert into student(sid,sname,ssex,scollege,sc,smath,senglish,schinese,sjava)" + 
    "values('" + sid + "','" + sname + "','" + ssex + "','" + scollege+ "','" + sc+ "','" 
        + smath + "','" + senglish + "','" + schinese + "','" + sjava + "')";
    System.out.println("------插入学生信息------");
    System.out.println("sql语句:" + sql);
    // 向数据库插入数据语句
    // 定义一个boolean型变量，用于判断插入数据是否成功
    boolean flag = false;
    try {
      // 更新数据库信息
      flag = DBUtil.executeUpdate(sql);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    DBUtil.close();
    // 返回执行结果
    return flag;
  }
  // 统计同一学号有多少人
  public int queryForsidinformation(String sid) {
    DBUtil db = new DBUtil();
    // 通过sid查询数据库中是否有相同sid数据
    String sql = "select * from student where sid=" + sid;
    System.out.println("------验证学生学号信息------");
    System.out.println("sql语句:" + sql);
    ResultSet rs = null;
    int count = 0;
    try {
      // 更新数据库保存到结果集里
      rs = (ResultSet) db.executeQuery(sql);
      // 找到相同的count++
      if (rs.next()) {
        count++;
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    // 关闭数据库连接
    db.close();
    // 返回count
    return count;
  }
  // 更新学生信息，这里学号不能更新
  public boolean updateStudentInformation(Student student) {
    DBUtil DBUtil = new DBUtil();
    // scollege,sc,smath,senglish,schinese,sjava
    String sql = "update student set sname=" + "'" + student.getsName() + "'" + " ," 
        + " ssex=" + "'" + student.getsSex() + "'" + " ,"
        + " scollege=" + "'" + student.getsCollege() + "' " + " ,"
        + " sc=" + "'" + student.getsC() + "' " + " ,"
        + " smath=" + "'" + student.getsMath() + "' " + " ,"
        + " senglish=" + "'" + student.getsEnglish() + "'" + "," 
        + " schinese=" + "'" + student.getsChinese() + "'" + " ," 
        + " sjava=" + "'" + student.getsJava()+ "'" 
        + " where" + " sid=" + "'"+ student.getsId() + "'";
    System.out.println("------更新学生信息------");
    System.out.println("sql语句:" + sql);
    boolean flag = false;
    try {
      flag = DBUtil.executeUpdate(sql);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return flag;
  }
  // 删除学生信息
  public boolean deleteStudentInfoBySid(String sid) {
    boolean flag = false;
    String sql = "delete from student where sid=" + "'" + sid + "'";
    System.out.println("------删除学生信息------");
    System.out.println("sql语句:" + sql);
    DBUtil DBUtil = new DBUtil();
    try {
      flag = DBUtil.executeUpdate(sql);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return flag;
  }
}