package cn.com.dao.chivementdao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.com.util.DBConnection;
import cn.com.util.DBSql;
import cn.com.vo.chivementvo.ChivementVo;
public class ExamDao {
private Connection conn = DBConnection.getConnectionOracle();
private ChivementVo examVo;
public ExamDao() {
}
public ExamDao(ChivementVo examVo) {
super();
this.examVo = examVo;
}
/**
* 全部查询
*/
public Object[][] selectAll() {
Object date[][] = null;
int max = 0;
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.SELECT_ALL);
rs = ps.executeQuery();
// 得到列数
max = rs.getMetaData().getColumnCount();
date = new Object[getnumberAll(DBSql.SELECT_ALL_COUNT)][max];
while (rs.next()) {
for (int j = 0; j < max; j++) {
date[i][j] = rs.getObject(j + 1);
}
i++;
}
// rs.close();
// ps.close();
// conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return date;
}
/**
* 根据学号查询
*/
public Object[][] selectBySid() {
Object date[][] = null;
int max = 0;
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.SELECT_BY_S_ID);
ps.setInt(1, examVo.getS_id());
rs = ps.executeQuery();
// 得到列数
max = rs.getMetaData().getColumnCount();
date = new Object[getnumber(DBSql.SELECT_BY_S_ID_COUNT, examVo
.getS_id())][max];
while (rs.next()) {
for (int j = 0; j < max; j++) {
date[i][j] = rs.getObject(j + 1);
}
i++;
}
// rs.close();
// ps.close();
// conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return date;
}
/**
* 根据组号查询
*/
public Object[][] selectByGid() {
Object date[][] = null;
int max = 0;
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.SELECT_BY_G_ID);
ps.setInt(1, examVo.getG_id());
rs = ps.executeQuery();
// 得到列数
max = rs.getMetaData().getColumnCount();
date = new Object[getnumber(DBSql.SELECT_BY_G_ID_COUNT, examVo
.getG_id())][max];
while (rs.next()) {
for (int j = 0; j < max; j++) {
date[i][j] = rs.getObject(j + 1);
}
i++;
}
// rs.close();
// ps.close();
// conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return date;
}
/**
* 根据课程号查询
*/
public Object[][] selectByCid() {
Object date[][] = null;
int max = 0;
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.SELECT_BY_C_ID);
ps.setInt(1, examVo.getC_id());
rs = ps.executeQuery();
// 得到列数
max = rs.getMetaData().getColumnCount();
date = new Object[getnumber(DBSql.SELECT_BY_C_ID_COUNT, examVo
.getC_id())][max];
while (rs.next()) {
for (int j = 0; j < max; j++) {
// System.out.println( examVo.getG_id());
date[i][j] = rs.getObject(j+1);
}
i++;
}
// rs.close();
// ps.close();
// conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return date;
}
/**
* 根据姓名模糊查询
*
* @return
*/
public Object[][] selectByName() {
Object date[][] = null;
int max = 0;
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.SELECT_BY_S_NAME);
ps.setString(1, examVo.getS_name());
ps.setString(2, "%" + examVo.getS_name() + "%");
ps.setString(3, "%" + examVo.getS_name());
ps.setString(4, examVo.getS_name() + "%");
rs = ps.executeQuery();
// 得到列数
max = rs.getMetaData().getColumnCount();
date = new Object[getnumberByName(DBSql.SELECT_BY_S_NAME_COUNT,
examVo.getS_name())][max];
while (rs.next()) {
for (int j = 0; j < max; j++) {
date[i][j] = rs.getObject(j + 1);
}
i++;
}
} catch (SQLException e) {
e.printStackTrace();
}
return date;
}
/**
* 根据课程名称模糊查询
*
* @return
*/
public Object[][] selectByClassName() {
Object date[][] = null;
int max = 0;
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.SELECT_BY_CLASS_NAME);
ps.setString(1, examVo.getClass_name());
ps.setString(2, "%" + examVo.getClass_name() + "%");
ps.setString(3, "%" + examVo.getClass_name());
ps.setString(4, examVo.getClass_name() + "%");
rs = ps.executeQuery();
// 得到列数
max = rs.getMetaData().getColumnCount();
date = new Object[getnumberByName(DBSql.SELECT_BY_CLASS_COUNT,
examVo.getClass_name())][max];
while (rs.next()) {
for (int j = 0; j < max; j++) {
date[i][j] = rs.getObject(j + 1);
}
i++;
}
} catch (SQLException e) {
e.printStackTrace();
}
return date;
}
/**
* 修改选中学生的成绩
*
*/
public void updatSelectClass() {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(DBSql.UPDATE_EXAM_BY_STUID);
ps.setInt(1, examVo.getClassExamChivement());
ps.setInt(2, examVo.getS_id());
ps.setInt(3, examVo.getC_id());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 得到所有课程号和课程名
*
* @return
*/
public String[] getClassNoName() {
String[] classNoName = null;
PreparedStatement ps = null;
ResultSet rs = null;
int j = 0;
try {
int i = getnumberAll(DBSql.SELECT_CLASS_NAME_COUNT);
classNoName = new String[i + i];
ps = conn.prepareStatement(DBSql.SELECT_CLASS_NAME);
rs = ps.executeQuery();
while (rs.next()) {
classNoName[j] = rs.getString(1);
classNoName[j + i] = rs.getString(2);
j++;
}
} catch (SQLException e) {
e.printStackTrace();
}
return classNoName;
}
/**
* 根据科目修改成绩 查询学号 姓名 成绩
*
*/
public void SelectClassStuName() {
int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
int j = getnumberBySelectClassName(
DBSql.SELECT_CLASS_STU_SNO_SNAME_EXAM_COUNT, examVo.getC_id());
int[] sNum = new int[j];
String[] sName = new String[j];
int[] classExam = new int[j];
try {
ps = conn.prepareStatement(DBSql.SELECT_CLASS_STU_SNO_SNAME_EXAM);
ps.setInt(1, examVo.getC_id());
rs = ps.executeQuery();
while (rs.next()) {
sNum[i] = rs.getInt(1);
sName[i] = rs.getString(2);
classExam[i] = rs.getInt(3);
i++;
}
} catch (SQLException e) {
e.printStackTrace();
}
examVo.setSid(sNum);
examVo.setSname(sName);
examVo.setClassExam(classExam);
}
/**
* 根据科目修改成绩 查询学号 姓名 成绩 后修改成绩
*
*/
public void UpdateClassStuName() {
// int i = 0;
PreparedStatement ps = null;
ResultSet rs = null;
int j = getnumberBySelectClassName(
DBSql.SELECT_CLASS_STU_SNO_SNAME_EXAM_COUNT, examVo.getC_id());
try {
ps = conn.prepareStatement(DBSql.UPDATE_CHIVEMENT_BY_CLASS);
for(int i =0;i<j;i++){
ps.setInt(1, examVo.getClassExam()[i]);
ps.setInt(2, examVo.getSid()[i]);
ps.setInt(3, examVo.getC_id());
ps.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 全部查询获得行数
*
* @return
*/
public int getnumberAll(String str) {
int number = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(str);
rs = ps.executeQuery();
rs.next();
number = rs.getInt(1);
// rs.close();
// ps.close();
// conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return number;
}
/**
* 根据学号 根据组号 根据课程号查询 获得行数
*
* @return
*/
public int getnumber(String str, int i) {
int number = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(str);
ps.setInt(1, i);
rs = ps.executeQuery();
rs.next();
number = rs.getInt(1);
// rs.close();
// ps.close();
// conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return number;
}
/**
* 根据姓名 课程名 查询 获得行数
*
*/
public int getnumberByName(String str, String i) {
int number = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(str);
ps.setString(1, i);
ps.setString(2, "%" + i + "%");
ps.setString(3, "%" + i);
ps.setString(4, i + "%");
rs = ps.executeQuery();
rs.next();
number = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
return number;
}
// /**
// * 根据课程名查询 获得行数
// *
// */
//
// public int getnumberByClassName(String str, String i) {
// int number = 0;
// PreparedStatement ps = null;
// ResultSet rs = null;
// try {
// ps = conn.prepareStatement(str);
// ps.setString(1, i);
// ps.setString(2, "%" + i + "%");
// ps.setString(3, "%" + i);
// ps.setString(4, i + "%");
// rs = ps.executeQuery();
// rs.next();
// number = rs.getInt(1);
// } catch (SQLException e) {
// e.printStackTrace();
// }
// return number;
// }
/**
*
* 根据课程名修改成绩获得行数
*
* @param str
* @param i
* @return
*/
public int getnumberBySelectClassName(String str, int i) {
int number = 0;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(str);
ps.setInt(1, i);
rs = ps.executeQuery();
rs.next();
number = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
return number;
}
}
JAVA论文成绩管理系统课程设计 
扫一扫
1016
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
