?? dbconnmanager.java~195~
字號:
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 getSum(String aclass, String aschool, String grade, float first, float last) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
Vector resultdate = new Vector();
String sqlquery = null;
try {
//全校成績統計
if (!aschool.equals("")) {
if (grade.equals("chinese"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,chinese,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where chinese between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
"UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,chinese,stuinf_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where chinese between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
"UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,chinese,stuinf_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 where chinese " +
"between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else if (grade.equals("math"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,math,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where math between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
"UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,math,stuinf_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where math between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
"UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,math,stuinf_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 where math " +
"between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else if (grade.equals("english"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,english,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where english between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
"UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,english,stuinf_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where english between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
"UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,english,stuinf_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 where english " +
"between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else if (grade.equals("composite"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,composite,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where composite between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
"UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,composite,stuinf_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where composite between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
"UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,composite,stuinf_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 where composite " +
"between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else {
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,total,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where total between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id " +
"UNION SELECT stugrade_A2242.stu_id,stuinf_A2242.name,total,stuinf_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where total between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id " +
"UNION SELECT stugrade_A2243.stu_id,stuinf_A2243.name,total,stuinf_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 where total " +
"between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
}
}
if (!aclass.equals("")) { //班級成績統計
if (aclass.equals("A2241")) {
if (grade.equals("chinese"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,chinese,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where chinese between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
else if (grade.equals("math"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,math,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where math between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
else if (grade.equals("english"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,english,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where english between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
else if (grade.equals("composite"))
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,composite,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where composite between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
else {
sqlquery =
"SELECT stugrade_A2241.stu_id,stuinf_A2241.name,total,stugrade_A2241.class " +
"FROM stugrade_A2241,stuinf_A2241 " +
"where total between ? and ? and stuinf_A2241.stu_id = stugrade_A2241.stu_id";
}
}
else if (aclass.equals("A2242")) {
if (grade.equals("chinese"))
sqlquery =
"SELECT stugrade_A2242.stu_id,stuinf_A2242.name,chinese,stugrade_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where chinese between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
else if (grade.equals("math"))
sqlquery =
"SELECT stugrade_A2242.stu_id,stuinf_A2242.name,math,stugrade_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where math between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
else if (grade.equals("english"))
sqlquery =
"SELECT stugrade_A2242.stu_id,stuinf_A2242.name,english,stugrade_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where english between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
else if (grade.equals("composite"))
sqlquery =
"SELECT stugrade_A2242.stu_id,stuinf_A2242.name,composite,stugrade_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 "+
"where composite between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
else {
sqlquery =
"SELECT stugrade_A2242.stu_id,stuinf_A2242.name,total,stugrade_A2242.class " +
"FROM stugrade_A2242,stuinf_A2242 " +
"where total between ? and ? and stuinf_A2242.stu_id = stugrade_A2242.stu_id";
}
}
else if (aclass.equals("A2243")) {
if (grade.equals("chinese"))
sqlquery =
"SELECT stugrade_A2243.stu_id,stuinf_A2243.name,chinese,stugrade_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 " +
"where chinese between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else if (grade.equals("math"))
sqlquery =
"SELECT stugrade_A2243.stu_id,stuinf_A2243.name,math,stugrade_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 " +
"where math between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else if (grade.equals("english"))
sqlquery =
"SELECT stugrade_A2243.stu_id,stuinf_A2243.name,english,stugrade_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 " +
"where english between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else if (grade.equals("composite"))
sqlquery =
"SELECT stugrade_A2243.stu_id,stuinf_A2243.name,composite,stugrade_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 " +
"where composite between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
else {
sqlquery =
"SELECT stugrade_A2243.stu_id,stuinf_A2243.name,total,stugrade_A2243.class " +
"FROM stugrade_A2243,stuinf_A2243 " +
"where total between ? and ? and stuinf_A2243.stu_id = stugrade_A2243.stu_id";
}
}
}
con = getConnection("Access");
ps = con.prepareStatement(sqlquery);
if (aschool.equals("全校"))
{
ps.setFloat(1, first);
ps.setFloat(2, last);
ps.setFloat(3, first);
ps.setFloat(4, last);
ps.setFloat(5, first);
ps.setFloat(6, last);
}else{
ps.setFloat(1, first);
ps.setFloat(2, last);
}
rs = ps.executeQuery();
while (rs.next()) {
resultdate.add(rs.getString("stu_id"));
resultdate.add(rs.getString("name"));
resultdate.add(rs.getString(grade));
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 getpeopleSum(String aclass, String aschool, String sumstr,String aethnic, String range1, String range2) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
Vector resultdate = new Vector();
String sqlquery = null;
try {
//全校學生統計
if (aschool.equals("全校")){
if (sumstr.equals("男"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
"where sex = '男' " +
"UNION SELECT stu_id,name,sex,class FROM stuinf_A2242 "+
"where sex = '男' " +
"UNION SELECT stu_id,name,sex,class FROM stuinf_A2243 "+
"where sex = '男'";
else if (sumstr.equals("女"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
"where sex = '女' " +
"UNION SELECT stu_id,name,sex,class FROM stuinf_A2242 "+
"where sex = '女' " +
"UNION SELECT stu_id,name,sex,class FROM stuinf_A2243 "+
"where sex = '女'";
else if (sumstr.equals("民族"))
sqlquery =
"SELECT stu_id,name,ethnic,class FROM stuinf_A2241 " +
"where ethnic LIKE '"+aethnic+"' "+
" UNION SELECT stu_id,name,ethnic,class FROM stuinf_A2242 "+
"where ethnic LIKE '"+aethnic+"' " +
"UNION SELECT stu_id,name,ethnic,class FROM stuinf_A2243 "+
"where ethnic LIKE '"+aethnic+"'";
else if (sumstr.equals("出生日期"))
sqlquery =
"SELECT stu_id,name,birthday,class FROM stuinf_A2241 " +
"where birthday between "+range1+" and "+range2+
" order by birthday ASC "+
" UNION SELECT stu_id,name,birthday,class FROM stuinf_A2242 "+
"where birthday between "+range1+" and "+range2+
" order by birthday ASC "+
" UNION SELECT stu_id,name,birthday,class FROM stuinf_A2243 "+
"where birthday between "+range1+ " and "+range2+" order by birthday ASC";
}
if (!aclass.equals("")) { //班級成績統計
if (aclass.equals("A2241")) {
if (sumstr.equals("男"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
"where sex = '男' ";
else if (sumstr.equals("女"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2241 " +
"where sex = '女' ";
else if (sumstr.equals("民族"))
sqlquery =
"SELECT stu_id,name,ethnic,class FROM stuinf_A2241 " +
"where ethnic LIKE '"+aethnic+"'";
else if (sumstr.equals("出生日期"))
sqlquery =
"SELECT stu_id,name,birthday,class FROM stuinf_A2241 " +
"where birthday between " +range1+ " and "+range2+" order by birthday ASC";
}
else if (aclass.equals("A2242")) {
if (sumstr.equals("男"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
"where sex = '男' ";
else if (sumstr.equals("女"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
"where sex = '女' ";
else if (sumstr.equals("民族"))
sqlquery =
"SELECT stu_id,name,ethnic,class FROM stuinf_A2242 " +
"where ethnic LIKE '"+aethnic+"'";
else if (sumstr.equals("出生日期"))
sqlquery =
"SELECT stu_id,name,birthday,class FROM stuinf_A2242 " +
"where birthday between "+range1+" and "+range2+
" order by birthday ASC";
}
else if (aclass.equals("A2243")) {
if (sumstr.equals("男"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
"where sex = '男' ";
else if (sumstr.equals("女"))
sqlquery =
"SELECT stu_id,name,sex,class FROM stuinf_A2242 " +
"where sex = '女' ";
else if (sumstr.equals("民族"))
sqlquery =
"SELECT stu_id,name,ethnic,class FROM stuinf_A2242 " +
"where ethnic LIKE '"+aethnic+"'";
else if (sumstr.equals("出生日期"))
sqlquery =
"SELECT stu_id,name,birthday,class FROM stuinf_A2242 " +
"where birthday between "+range1+" and "+range2+
" order by birthday ASC";
}
}
con = getConnection("Access");
ps = con.prepareStatement(sqlquery);
rs = ps.executeQuery();
while (rs.next()) {
resultdate.add(rs.getString("stu_id"));
resultdate.add(rs.getString("name"));
resultdate.addElement(rs.getString(3));
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 boolean checkClass(String aclass)
{
boolean checkflag = true;
if(!aclass.equals("A2241")&&!aclass.equals("A2242")&&!aclass.equals("A2243"))
checkflag = false;
else checkflag = true;
return checkflag;
}
//檢驗輸入的學號是否合法
public boolean checkStuid(String userid) {
boolean checkflag = true;
Pattern pattern;
pattern = Pattern.compile("[0-9]");
for (int i = 0; i < userid.length(); i++) {
String str = userid.substring(i, i + 1);
Matcher matcher = pattern.matcher(str);
if (!matcher.matches()) {
checkflag = false;
break;
}
else checkflag = true;
}
return checkflag;
}
/*關閉所有連接*/
public synchronized void closeConns() {
Enumeration allPools = connPools.elements();
while (allPools.hasMoreElements()) {
DBConnPool pool = (DBConnPool) allPools.nextElement();
pool.closeConn();
}
}
/*創建連接池*/
private void createPools() {
for (int i = 0; i < poolnames.size(); i++) {
String poolname = poolnames.elementAt(i).toString();
String drivername = drivernames.elementAt(i).toString();
String dbid = dbids.elementAt(i).toString();
String username = usernames.elementAt(i).toString();
String passwd = passwds.elementAt(i).toString();
int maxconn = 0;
try {
maxconn = Integer.parseInt(maxconns.elementAt(i).toString());
}
catch (NumberFormatException e) {
e.printStackTrace();
}
DBConnPool pool = new DBConnPool(poolname, drivername, dbid, username,
passwd, maxconn);
connPools.put(poolname, pool);
}
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -