//查询初始化DAO
/*
* 项目名 :北京五岳管理系统
* 副系统名 :教学模板
* class名 :StudentInfoSearchInitDAO.java
* version :1.0
* 日期 :2005/1/6
* 著作权 :
*/
package admit.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import admit.common.DBConnection;
import admit.db.util.StudentsSearchInitInfo;
public class StudentInfoSearchInitDAO extends AdmitDAO {
/**
*
*
*/
public StudentInfoSearchInitDAO(){
}
/**
* @param Form ClassInfoForm
* @return list
* @throws Exception
*/
public ArrayList getClassInfo() throws Exception{
DBConnection dbc = null;
Connection conn= null;
Statement stat= null;
ResultSet result = null;
try {
//数据库连接
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
if (conn == null) {
throw new Exception("Connection is null");
}
stat = conn.createStatement();
String sql = "SELECT CLASS_NO FROM CLASS_TABLE ";
log.debug("ClassInfoSQL = " + sql);
//sql语句的执行
System.out.println("*****"+sql);
result = stat.executeQuery(sql);
System.out.println("sql end :"+sql);
//将JavaBean添加到list
ArrayList list = new ArrayList();
while (result.next()) {
StudentsSearchInitInfo obj = new StudentsSearchInitInfo();
obj.setClass_no(result.getString("CLASS_NO"));
list.add(obj);
}
// DB
if (result != null)
result.close();
if (stat != null)
stat.close();
log.debug("-= end =-");
return list;
} catch (Exception e) {
throw e;
} finally {
if (conn != null)
dbc.freeConnection(conn);
}
}
public ArrayList getIdCardInfo() throws Exception{
DBConnection dbc = null;
Connection conn= null;
Statement stat= null;
ResultSet result = null;
try {
//数据库连接
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
if (conn == null) {
throw new Exception("Connection is null");
}
stat = conn.createStatement();
String sql = "SELECT STUDENTS_BAS_INFO.IDCARD_NO FROM STUDENTS_BAS_INFO ";
System.out.print("aaaaaaa"+sql);
//sql语句的执行
result = stat.executeQuery(sql);
//将JavaBean添加到list
ArrayList list = new ArrayList();
while (result.next()) {
StudentsSearchInitInfo obj = new StudentsSearchInitInfo();
obj.setIdcard_no(result.getString("IDCARD_NO"));
list.add(obj);
}
// DB
if (result != null)
result.close();
if (stat != null)
stat.close();
log.debug("-= end =-");
return list;
} catch (Exception e) {
log.error(e);
throw e;
} finally {
if (conn != null)
dbc.freeConnection(conn);
}
}
}
//查询DAO
package admit.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import admit.common.DBConnection;
import admit.db.util.StudentsSearchInitInfo;
import admit.form.StudentInitForm;
public class StudentInfoSearchDAO extends AdmitDAO {
/**
* 构造函数
*/
public StudentInfoSearchDAO (){
}
/**
*
* @param form
* @return
* @throws Exception
*/
public ArrayList getSearchStuInfo(StudentInitForm form) throws Exception {
DBConnection dbc = null;
Connection conn = null;
Statement stat = null;
ResultSet result = null;
// 连接数据库
try {
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
stat = conn.createStatement();
String classId = form.getLst_class();
System.out.println("idcardId*********" + classId);
String idCardId = form.getLst_idCard();
System.out.println("classId**********" + idCardId);
String sql = "SELECT STUDENTS_BAS_INFO.IDCARD_NO, CLASS_TABLE.CLASS_NO," +
" STUDENTS_BAS_INFO.SEX, STUDENTS_BAS_INFO.SPECIALIZED,STUDENTS_BAS_INFO.TEL,STUDENTS_BAS_INFO.NAME," +
" STUDENTS_BAS_INFO.ADRESSFAMILY, STUDENTS_BAS_INFO.PRO_TEST, STUDENTS_BAS_INFO.ARRANGE_COMPANY" +
" FROM STUDENTS_BAS_INFO, CLASS_TABLE WHERE STUDENTS_BAS_INFO.IDCARD_NO = CLASS_TABLE.IDCARD_NO" ;
StringBuffer sb = new StringBuffer();
processCondition("CLASS_TABLE.CLASS_NO", classId, sb);
processCondition("STUDENTS_BAS_INFO.IDCARD_NO", idCardId, sb);
//拼成SQL文
if (sb.length() > 0) {
sql = sql + " AND " + sb.toString();
}
System.out.print("start *** sql" + sql);
//执行sql语句
result = stat.executeQuery(sql);
System.out.println("@@@@@@@@@@@@@@");
ArrayList list = new ArrayList();
while ( result.next()) {
StudentsSearchInitInfo obj = new StudentsSearchInitInfo();
obj.setClass_no(result.getString("CLASS_NO"));
obj.setIdcard_no(result.getString("IDCARD_NO"));
obj.setName(result.getString("NAME"));
obj.setArrange_company(result.getString("ARRANGE_COMPANY"));
obj.setPro_test(result.getString("PRO_TEST"));
obj.setSpecialized(result.getString("SPECIALIZED"));
obj.setAdressfamily(result.getString("ADRESSFAMILY"));
obj.setTel(result.getString("TEL"));
list.add(obj);
}
if (result != null) {
result.close();
}
if (stat !=null) {
stat.close();
}
return list;
} catch (Exception e) {
throw e;
}
finally {
if (conn != null)
dbc.freeConnection(conn);
}
}
}
//修改DAO
package admit.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import admit.common.DBConnection;
import admit.db.util.StudentChangeInfo;
public class StudentChangeDAO extends AdmitDAO {
public StudentChangeDAO (){
super();
}
public StudentChangeInfo getObject(String studentIdCard) throws Exception {
DBConnection dbc = null;
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try {
//数据库的连接
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
stat = conn.createStatement();
if (conn == null) {
throw new Exception("Connection is null");
}
String sql = "SELECT * FROM STUDENTS_BAS_INFO WHERE IDCARD_NO ='" + studentIdCard.trim()+"'";
System.out.println("############################" + sql);
result = stat.executeQuery(sql);
StudentChangeInfo obj = new StudentChangeInfo();
while (result.next()) {
obj.setName(result.getString("NAME"));
System.out.print("kkkkkkkkkkkk");
// obj.setSex(result.getString("SEX"));
obj.setBirthday(result.getString("BIRTHDAY"));
obj.setCollage(result.getString("COLLAGE"));
obj.setSpecialized(result.getString("SPECIALIZED"));
obj.setTel(result.getString("TEL"));
obj.setAdressfamily(result.getString("ADRESSFAMILY"));
obj.setPro_test(result.getString("PRO_TEST"));
obj.setArrange(result.getString("ARRANGE"));
}
System.out.print("kkkkkkkkkkkk");
System.out.println( obj.getName());
System.out.println( obj.getSex());
System.out.println( obj.getBirthday());
System.out.println( obj.getCollage());
System.out.println( obj.getSpecialized());
System.out.println( obj.getTel());
System.out.println( obj.getAdressfamily());
System.out.println( obj.getPro_test());
System.out.println( obj.getArrange());
// DB
if (result != null)
result.close();
if (stat != null)
stat.close();
return obj;
} catch (Exception e) {
throw e;
} finally {
if (conn != null)
dbc.freeConnection(conn);
}
}
/*
public ArrayList getList() throws Exception {
DBConnection dbc = null;
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try {
//数据库的连接
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
if (conn == null) {
throw new Exception("Connection is null");
}
stat = conn.createStatement();
String sql = "SELECT DISTINCT TEACHER_ID, TEACHER_NAME FROM CLASS_INFO";
//执行sql语句
result = stat.executeQuery(sql);
//将javaBean添加到list
ArrayList list = new ArrayList();
while (result.next()) {
StudentChangeInfo obj = new StudentChangeInfo();
(result.getString("TEACHER_ID").trim() + " " + result.getString("TEACHER_NAME").trim());
list.add(obj);
}
// DB
if (result != null)
result.close();
if (stat != null)
stat.close();
return list;
} catch (Exception e) {
throw e;
} finally {
if (conn != null)
dbc.freeConnection(conn);
}
}*/
}
//保存DAO
package admit.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import admit.common.DBConnection;
import admit.db.util.StudentInputInfo;
import admit.form.StudentNewForm;
public class StudentInputDAO extends AdmitDAO {
/**
* 构造函数
*/
public StudentInputDAO (){
}
/**
*
* @param form
* @return
* @throws Exception
*/
public ArrayList getStudentInputInfo() throws Exception {
DBConnection dbc = null;
Connection conn = null;
Statement stat = null;
ResultSet result = null;
// 连接数据库
try {
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
if (conn == null)
throw new Exception("conection is null");
stat = conn.createStatement();
String sql = "SELECT CLASS_NO FROM CLASS_TABLE";
//执行sql语句
result = stat.executeQuery(sql);
System.out.println("@@@@@@@@@@@@@@");
ArrayList list = new ArrayList();
while ( result.next()) {
StudentInputInfo obj = new StudentInputInfo();
obj.setClass1_no(result.getString("CLASS_NO"));
obj.setClass2_no(result.getString("CLASS_NO"));
list.add(obj);
}
if (result != null) {
result.close();
}
if (stat !=null) {
stat.close();
}
return list;
} catch (Exception e) {
log.error(e);
throw e;
}
finally {
if (conn != null)
dbc.freeConnection(conn);
}
}
public void getStudentSave(StudentNewForm form) throws Exception {
log.debug("-=start=-");
DBConnection dbc = null;
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try {
dbc = DBConnection.getInstance();
conn = dbc.getConnection();
if (conn == null) {
throw new Exception("Connection is null");
}
//取得学员的信息
stat = conn.createStatement();
String NAME = form.getTxt_studentName();
String SEX = form.getLst_studentSex();
String IDCARD_NO = form.getTxt_studentIdCardNo();
String BIRTHDAY = form.getTxt_studentBirth();
String COLLAGE = form.getTxt_studentSchool();
String SPECIALIZED = form.getTxt_studentSpec();
String TEL = form.getTxt_studentTel();
String ADRESSFAMILY = form.getTxt_studentAdd();
String PRO_TEST = form.getTxt_studentPTest();
String ARRANGE = form.getTxt_studentArrange();
//String ARR_COM = form.getTxt_company();
String sql1 = "INSERT INTO STUDENTS_BAS_INFO ( NAME,SEX,IDCARD_NO,BIRTHDAY,"
+ "COLLAGE,SPECIALIZED,TEL,ADRESSFAMILY,PRO_TEST,ARRANGE,ARRANGE_COMPANY )"
+ "VALUES('"
+ NAME
+ "','"
+ SEX
+ "','"
+ IDCARD_NO
+ "','"
+ BIRTHDAY
+ "','"
+ COLLAGE
+ "','"
+ SPECIALIZED
+ "','"
+ TEL
+ "','"
+ ADRESSFAMILY
+ "','"
+ PRO_TEST
+ "','"
+ ARRANGE
+ "','"
+ "ARR_COM" +"')";
result = stat.executeQuery(sql1);
System.out.print(sql1);
String classOne = form.getLst_studentClass1();
String classTwo = form.getLst_studentClass2();
//声明一个数组同时把两个班号放置数组中
String[] classno = new String[2];
classno[0] = classOne;
classno[1] = classTwo;
//如果课程班号1和课程班号2都不为空
if (!classOne.equals("") && !classTwo.equals("")) {
for (int i = 0; i < 2; i++) {
String sql = "INSERT INTO CLASS_TABLE(IDCARD_NO,CLASS_NO)"
+ "VALUES('" + IDCARD_NO + "','" + classno[i]
+ "')";
result = stat.executeQuery(sql);
System.out.print(sql);
}
}
//如果课程班号1不为空并且课程班号2为空时
if (!classOne.equals("") && classTwo.equals("")) {
String sql = "INSERT INTO CLASS_TABLE(IDCARD_NO,CLASS_NO)"
+ "VALUES('" + IDCARD_NO + "','" + classOne + "')";
result = stat.executeQuery(sql);
System.out.print(sql);
}
//如果课程班号1为空并且课程班号2不为空
if (classOne.equals("") && !classTwo.equals("")) {
String sql = "INSERT INTO CLASS_TABLE(IDCARD_NO,CLASS_NO)"
+ "VALUES('" + IDCARD_NO + "','" + classTwo + "')";
result = stat.executeQuery(sql);
System.out.print(sql);
}
//关闭数据库
if (result != null)
result.close();
if (stat != null)
stat.close();
log.debug("-= end =-");
//s return 1;
} catch (Exception e) {
throw e;
} finally {
if (conn != null) {
dbc.freeConnection(conn);
}
}
}
}