?? dbconnmanager.java~197~
字號:
conn.close();
}
catch (SQLException ignore) {}
finally {
releaseConnection("Access", conn);
}
}
return updflag;
}
//修改學生成績
public boolean updateGrade(String aclass, int astu_id, String amath,
String achinese,
String aenglish, String acomposite) {
Connection conn = null;
boolean updflag = false;
ResultSet rs = null;
float atotal = 0;
try {
conn = getConnection("Access");
//聲明可更新的結果集
Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
if (aclass.equals("A2241")) {
rs = stat.executeQuery("select * from stugrade_A2241 where stu_id = " +
astu_id);
}
else if (aclass.equals("A2242")) {
rs = stat.executeQuery("select * from stugrade_A2242 where stu_id = " +
astu_id);
}
else rs = stat.executeQuery(
"select * from stugrade_A2243 where stu_id = " +
astu_id);
while (rs.next()) {
if (!achinese.equals("")) {
float chinese = Float.parseFloat(achinese);
rs.updateFloat("chinese", chinese);
atotal = chinese;
rs.updateRow();
}
else atotal = rs.getFloat(3);
if (!amath.equals("")) {
float math = Float.parseFloat(amath);
atotal = atotal + math;
rs.updateFloat("math", math);
rs.updateRow();
}
else atotal = atotal + rs.getFloat(4);
if (!aenglish.equals("")) {
float english = Float.parseFloat(aenglish);
atotal = atotal + english;
rs.updateFloat("english", english);
rs.updateRow();
}
else atotal = atotal + rs.getFloat(5);
if (!acomposite.equals("")) {
float composite = Float.parseFloat(acomposite);
atotal = atotal + composite;
rs.updateFloat("composite", composite);
rs.updateRow();
}
else atotal = atotal + rs.getFloat(6);
rs.updateFloat("total", atotal);
rs.updateRow();
atotal = 0;
}
updflag = true;
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
if (rs != null)try {
rs.close();
}
if (conn != null)try {
conn.close();
}
catch (SQLException ignore) {}
finally {
releaseConnection("Access", conn);
}
}
return updflag;
}
//獲取學生排名的詳細信息
public Vector getRank(String aclass, String aschool, String bymath,
String bychinese, String byenglish, String bycomposite,
String bytotal) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
Vector resultdate = new Vector();
String sqlquery = null;
try {
//全校成績排名
if (!aschool.equals("")) {
if (!bychinese.equals("")) {
bygrade = "chinese";
sqlquery = "SELECT stu_id,chinese,class FROM stugrade_A2241 " +
"UNION SELECT stu_id,chinese,class FROM stugrade_A2242 " +
"UNION SELECT stu_id,chinese,class FROM stugrade_A2243 " +
"ORDER BY chinese DESC";
}
else if (!bymath.equals("")) {
bygrade = "math";
sqlquery = "SELECT stu_id,math,class FROM stugrade_A2241 " +
"UNION SELECT stu_id,math,class FROM stugrade_A2242 " +
"UNION SELECT stu_id,math,class FROM stugrade_A2243 " +
"ORDER BY math DESC";
}
else if (!byenglish.equals("")) {
bygrade = "english";
sqlquery = "SELECT stu_id,english,class FROM stugrade_A2241 " +
"UNION SELECT stu_id,english,class FROM stugrade_A2242 " +
"UNION SELECT stu_id,english,class FROM stugrade_A2243 " +
"ORDER BY english DESC";
}
else if (!bycomposite.equals("")) {
bygrade = "composite";
sqlquery = "SELECT stu_id,composite,class FROM stugrade_A2241 " +
"UNION SELECT stu_id,composite,class FROM stugrade_A2242 " +
"UNION SELECT stu_id,composite,class FROM stugrade_A2243 " +
"ORDER BY composite DESC";
}
else {
bygrade = "total";
sqlquery = "SELECT stu_id,total,class FROM stugrade_A2241 " +
"UNION SELECT stu_id,total,class FROM stugrade_A2242 " +
"UNION SELECT stu_id,total,class FROM stugrade_A2243 " +
"ORDER BY total DESC";
}
}
if (!aclass.equals("")) { //班級成績排名
if (aclass.equals("A2241")) {
if (!bychinese.equals("")) {
bygrade = "chinese";
sqlquery = "select stu_id, chinese, class from stugrade_A2241 " +
"ORDER BY chinese DESC";
}
else if (!bymath.equals("")) {
bygrade = "math";
sqlquery = "select stu_id, math, class from stugrade_A2241 " +
"ORDER BY math DESC";
}
else if (!byenglish.equals("")) {
bygrade = "english";
sqlquery = "select stu_id, english, class from stugrade_A2241 " +
"ORDER BY english DESC";
}
else if (!bycomposite.equals("")) {
bygrade = "composite";
sqlquery = "select stu_id, composite, class from stugrade_A2241 " +
"ORDER BY composite DESC";
}
else {
bygrade = "total";
sqlquery = "select stu_id, total, class from stugrade_A2241 " +
"ORDER BY total DESC";
}
}
else if (aclass.equals("A2242")) {
if (!bychinese.equals("")) {
bygrade = "chinese";
sqlquery = "select stu_id, chinese, class from stugrade_A2242 " +
"ORDER BY chinese DESC";
}
else if (!bymath.equals("")) {
bygrade = "math";
sqlquery = "select stu_id, math, class from stugrade_A2242 " +
"ORDER BY math DESC";
}
else if (!byenglish.equals("")) {
bygrade = "english";
sqlquery = "select stu_id, english, class from stugrade_A2242 " +
"ORDER BY english DESC";
}
else if (!bycomposite.equals("")) {
bygrade = "composite";
sqlquery = "select stu_id, composite, class from stugrade_A2242 " +
"ORDER BY composite DESC";
}
else {
bygrade = "total";
sqlquery = "select stu_id, total, class from stugrade_A2242 " +
"ORDER BY total DESC";
}
}
else if (aclass.equals("A2243")) {
if (!bychinese.equals("")) {
bygrade = "chinese";
sqlquery = "select stu_id, chinese, class from stugrade_A2243 " +
"ORDER BY chinese DESC";
}
else if (!bymath.equals("")) {
bygrade = "math";
sqlquery = "select stu_id, math, class from stugrade_A2243 " +
"ORDER BY math DESC";
}
else if (!byenglish.equals("")) {
bygrade = "english";
sqlquery = "select stu_id, english, class from stugrade_A2243 " +
"ORDER BY english DESC";
}
else if (!bycomposite.equals("")) {
bygrade = "composite";
sqlquery = "select stu_id, composite, class from stugrade_A2243 " +
"ORDER BY composite DESC";
}
else {
bygrade = "total";
sqlquery = "select stu_id, total, class from stugrade_A2243 " +
"ORDER BY total DESC";
}
}
}
con = getConnection("Access");
ps = con.prepareStatement(sqlquery);
rs = ps.executeQuery();
while (rs.next()) {
resultdate.add(rs.getString("stu_id"));
resultdate.add(rs.getString(bygrade));
resultdate.add(rs.getString("class"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
if (rs != null)try {
rs.close();
}
catch (SQLException ignore) {}
if (ps != null)try {
ps.close();
}
catch (SQLException ignore) {}
if (con != null)try {
con.close();
}
catch (SQLException ignore) {}
finally {
releaseConnection("Access", con);
}
}
return resultdate;
}
//查詢學生基本信息
public Vector stuinfQuery(int astuid, String aclass, String aname) {
Connection con = null;
ResultSet rs = null;
Statement statement = null;
String sqlquery = null;
Vector result = new Vector();
String sstuid = "";
try {
if ( astuid != 0 ) {
String stuid = String.valueOf(astuid);
if( stuid.length()<4 ) stuid = "11111";
sstuid = stuid.substring(0, 4);
if (sstuid.equals("2241"))
sqlquery = "select * from stuinf_A2241 where stu_id =" + astuid;
else if (sstuid.equals("2242"))
sqlquery = "select * from stuinf_A2242 where stu_id =" + astuid;
else sqlquery = "select * from stuinf_A2243 where stu_id =" + astuid;
}
else if (!aname.equals("")) {
if (!aclass.equals("全校")) {
if (aclass.equals("A2241")) sqlquery =
"select * from stuinf_A2241 where name ='"+aname+"'";
else if (aclass.equals("A2242")) sqlquery =
"select * from stuinf_A2242 where name ='"+aname+"'";
else if (aclass.equals("A2243")) sqlquery =
"select * from stuinf_A2243 where name ='"+aname+"'";
}
else sqlquery = "SELECT * FROM stuinf_A2241 where name ='"+aname+
"' UNION SELECT * FROM stuinf_A2242 where name ='"+aname+
"' UNION SELECT * FROM stuinf_A2243 where name ='"+aname+"'";
}
else if (!aclass.equals("")) {
if (aclass.equals("A2241")) sqlquery =
"select * from stuinf_A2241";
else if (aclass.equals("A2242")) sqlquery =
"select * from stuinf_A2242";
else if (aclass.equals("A2243")) sqlquery =
"select * from stuinf_A2243";
}
con = getConnection("Access");
statement = con.createStatement();
rs = statement.executeQuery(sqlquery);
while (rs.next()) {
result.add(rs.getString("stu_id"));
result.add(rs.getString("name"));
result.add(rs.getString("class"));
result.add(rs.getString("sex"));
result.add(rs.getString("birthday"));
result.add(rs.getString("ethnic"));
result.add(rs.getString("nativeplace"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
if (rs != null)try {
rs.close();
}
catch (SQLException ignore) {}
if (statement != null)try {
statement.close();
}
catch (SQLException ignore) {}
if (con != null)try {
con.close();
}
catch (SQLException ignore) {}
finally {
releaseConnection("Access", con);
}
}
return result;
}
//查詢學生成績信息
public Vector stuGradeQuery( int astuid, String aclass ) {
Connection con = null;
ResultSet rs = null;
Statement statement = null;
String sqlquery = null;
Vector result = new Vector();
String sstuid = "";
try {
if (astuid != 0) {
String stuid = String.valueOf(astuid);
if (stuid.length() >= 4) {
sstuid = stuid.substring(0, 4);
if (sstuid.equals("2241"))
sqlquery =
"select stugrade_A2241.stu_id, name, chinese, math, english, " +
"composite, total, stuinf_A2241.class from stuinf_A2241,stugrade_A2241 " +
"where stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
"and stugrade_A2241.stu_id =" + astuid;
else if (sstuid.equals("2242"))
sqlquery =
"select stugrade_A2242.stu_id, name, chinese, math, english, " +
"composite, total, stuinf_A2242.class from stuinf_A2242,stugrade_A2242 " +
"where stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
"and stugrade_A2242.stu_id =" + astuid;
else sqlquery =
"select stugrade_A2243.stu_id, name, chinese, math, english, " +
"composite, total, stuinf_A2243.class from stuinf_A2243,stugrade_A2243 " +
"where stuinf_A2243.stu_id = stugrade_A2243.stu_id " +
"and stugrade_A2243.stu_id =" + astuid;
}
}
else if (!aclass.equals("")) {
if (aclass.equals("A2241")) sqlquery =
"select stugrade_A2241.stu_id, name, chinese, math, english, " +
"composite, total, stuinf_A2241.class from stuinf_A2241,stugrade_A2241 " +
"where stuinf_A2241.stu_id = stugrade_A2241.stu_id ";
else if (aclass.equals("A2242")) sqlquery =
"select stugrade_A2242.stu_id, name, chinese, math, english, " +
"composite, total, stuinf_A2242.class from stuinf_A2242,stugrade_A2242 " +
"where stuinf_A2242.stu_id = stugrade_A2242.stu_id ";
else if (aclass.equals("A2243")) sqlquery =
"select stugrade_A2243.stu_id, name, chinese, math, english, " +
"composite, total, stuinf_A2243.class from stuinf_A2243,stugrade_A2243 " +
"where stuinf_A2243.stu_id = stugrade_A2243.stu_id ";
}
con = getConnection("Access");
statement = con.createStatement();
rs = statement.executeQuery(sqlquery);
while (rs.next()) {
result.add(rs.getString("stu_id"));
result.add(rs.getString("name"));
result.add(rs.getString("chinese"));
result.add(rs.getString("math"));
result.add(rs.getString("english"));
result.add(rs.getString("composite"));
result.add(rs.getString("total"));
result.add(rs.getString("class"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
if (rs != null)try {
rs.close();
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -