CONSTRAINT t_tc_ibfk_1 FOREIGN KEY (cno) REFERENCES t_course (cno) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT t_tc_ibfk_2 FOREIGN KEY (tno) REFERENCES t_teacher (tno) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
– Records of t_tc
INSERT INTO t_tc VALUES (1001, 1001);
INSERT INTO t_tc VALUES (1002, 1001);
INSERT INTO t_tc VALUES (1004, 1001);
INSERT INTO t_tc VALUES (1009, 1001);
INSERT INTO t_tc VALUES (1005, 1002);
INSERT INTO t_tc VALUES (1008, 1002);
INSERT INTO t_tc VALUES (1002, 1003);
INSERT INTO t_tc VALUES (1004, 1003);
INSERT INTO t_tc VALUES (1007, 1004);
INSERT INTO t_tc VALUES (1008, 1004);
INSERT INTO t_tc VALUES (1008, 1005);
INSERT INTO t_tc VALUES (1004, 1006);
– Table structure for t_teacher
DROP TABLE IF EXISTS t_teacher;
CREATE TABLE t_teacher (
tno int(4) NOT NULL AUTO_INCREMENT,
tname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
password varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
phone bigint(11) NULL DEFAULT NULL,
hiredate date NULL DEFAULT NULL,
remark varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (tno) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1006 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
– Records of t_teacher
INSERT INTO t_teacher VALUES (1001, ‘张志斌’, ‘123456’, 15788888888, ‘2017-07-20’, ‘张老师是一个超级幽默的老师，教学认真，态度友好，有自己独有的教学方法，深得学生喜爱’);
INSERT INTO t_teacher VALUES (1002, ‘白茹意’, ‘123456’, 15766666666, ‘2018-03-06’, ‘白老师工作认真负责，不推卸责任’);
INSERT INTO t_teacher VALUES (1003, ‘郭新峰’, ‘123456’, 15733333333, ‘2018-05-14’, ‘<span style=“font-family:Arial Black;”><span style=“color:#E53333;”><span style=“color:#E53333;”>郭老师很认真负责’);
INSERT INTO t_teacher VALUES (1004, ‘赵丽’, ‘123456’, 15722222222, ‘2018-04-03’, NULL);
INSERT INTO t_teacher VALUES (1005, ‘齐兴斌’, ‘123456’, 15711111111, ‘2004-05-28’, NULL);
INSERT INTO t_teacher VALUES (1006, ‘尹少平’, ‘123456’, 15777777777, ‘2014-06-11’, NULL);
SET FOREIGN_KEY_CHECKS = 1;
=====================================================================
=====================================================================
package com.bluehonour.sscs.dao.impl;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.bluehonour.sscs.dao.AdminDao;
import com.bluehonour.sscs.entity.Admin;
import com.bluehonour.sscs.util.DBUtils;
public class AdminDaoImpl implements AdminDao{
@Override
public Admin find(String userId, String password) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Admin admin = null;
try {
//建立连接
connection = DBUtils.getConnection();
//向数据库发送sql命令并得到结果
String sql = “select * from t_admin where userid = ? and password = ?”;
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, userId);
preparedStatement.setString(2, password);
rs = preparedStatement.executeQuery();
//处理返回结果
if(rs.next()) {
//取出结果集当前行各个字段的值
String userName = rs.getString(“username”);
int age = rs.getInt(“age”);
double score = rs.getDouble(“score”);
Date enterDate = rs.getDate(“enterdate”);
String introduction = rs.getString(“introduction”);
//封装成对象
admin = new Admin(userId, userName, password, age, score, enterDate, introduction);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭数据库资源
DBUtils.closeAll(rs, preparedStatement, connection);
}
return admin;
}
@Override
public int save(Admin admin) {
String sql = “insert into t_admin values(?,?,?,?,?,?,?)”;
Object[] params = {admin.getUserId(),admin.getUserName(),admin.getPassword(),admin.getAge(),
admin.getScore(),admin.getIntroduction(),admin.getEnterDate()};
return DBUtils.executeUpdate(sql, params);
}
}
package com.bluehonour.sscs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.bluehonour.sscs.dao.CourseDao;
import com.bluehonour.sscs.entity.Course;
import com.bluehonour.sscs.util.DBUtils;
public class CourseDaoImpl implements CourseDao{
@Override
public int save(Course course) {
String sql = "insert into t_course (name,credit,periodstart,periodend) values(?,?,?,?) ";
Object[] params = {course.getName(), course.getCredit(), course.getPeriodstart(), course.getPeriodend()};
return DBUtils.executeUpdate(sql, params);
}
@Override
public List findAll() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List list = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = “select * from t_course order by cno”;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行各个字段的值
int cno = rs.getInt(“cno”);
String name = rs.getString(“name”);
int credit = rs.getInt(“credit”);
Date periodstart = rs.getDate(“periodstart”);
Date periodend = rs.getDate(“periodend”);
// 封装成对象
Course course = new Course(cno,name, credit, periodstart, periodend);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
}
StudentCourseTeacherDaoImpl.java
package com.bluehonour.sscs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.bluehonour.sscs.dao.StudentCourseTeacherDao;
import com.bluehonour.sscs.entity.Course;
import com.bluehonour.sscs.entity.StudentCourse;
import com.bluehonour.sscs.entity.Teacher;
import com.bluehonour.sscs.util.DBUtils;
public class StudentCourseTeacherDaoImpl implements StudentCourseTeacherDao {
@Override
public int save(int sno, int cno, int tno) {
String sql = “insert into t_sc(sno,cno,tno) values(?,?,?)”;
Object[] params = {sno,cno,tno};
return DBUtils.executeUpdate(sql, params);
}
@Override
public List findSelectedCourse(int sno) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List list = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = “select * from t_course c”
-
" join t_sc sc"
-
" on (c.cno = sc.cno)"
-
" join t_teacher t"
-
" on (sc.tno = t.tno)"
-
" where sno = " + sno;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行课程各个字段的值
int cno = rs.getInt(“cno”);
String name = rs.getString(“name”);
int credit = rs.getInt(“credit”);
Date periodstart = rs.getDate(“periodstart”);
Date periodend = rs.getDate(“periodend”);
// 封装成课程对象
Course course = new Course(cno,name, credit, periodstart, periodend);
//取出结果集中教师各个字段的值
int tno = rs.getInt(“tno”);
String tname = rs.getString(“tname”);
String password = rs.getString(“password”);
long phone = rs.getLong(“phone”);
Date hiredate = rs.getDate(“hiredate”);
String remark = rs.getString(“remark”);
//封装成教师对象
Teacher teacher = new Teacher(tno,tname, password, phone, hiredate, remark);
//将教师加入课程
course.setTeacher(teacher);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
@Override
public List findSelectableCourse(int sno) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List list = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = "SELECT c., t. FROM t_tc a "
-
"LEFT JOIN t_course c "
-
"ON a.cno = c.cno "
-
"LEFT JOIN t_teacher t "
-
"ON a.tno = t.tno "
-
"WHERE (a.cno, a.tno) NOT IN "
-
"( SELECT cno,tno "
-
"FROM t_sc "
-
"WHERE sno = "
-
sno
+") ";
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行课程各个字段的值
int cno = rs.getInt(“cno”);
String name = rs.getString(“name”);
int credit = rs.getInt(“credit”);
Date periodstart = rs.getDate(“periodstart”);
Date periodend = rs.getDate(“periodend”);
// 封装成课程对象
Course course = new Course(cno,name, credit, periodstart, periodend);
//取出结果集中教师各个字段的值
int tno = rs.getInt(“tno”);
String tname = rs.getString(“tname”);
String password = rs.getString(“password”);
long phone = rs.getLong(“phone”);
Date hiredate = rs.getDate(“hiredate”);
String remark = rs.getString(“remark”);
//封装成教师对象
Teacher teacher = new Teacher(tno,tname, password, phone, hiredate, remark);
//将教师加入课程
course.setTeacher(teacher);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
@Override
public int removeStudentDistributedCourse(int sno, int cno, int tno) {
String sql = “delete from t_sc where sno = ? and cno = ? and tno = ?”;
Object[] params = {sno,cno,tno};
return DBUtils.executeUpdate(sql, params);
}
@Override
public List getSelectedStudentAndCourse(int tno) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List list = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = “SELECT” +
" s.sno," +
" s.sname," +
" s.classno," +
" clazz.cname," +
" c.cno," +
" c. NAME," +
" c.credit," +
" sc.score" +
" FROM" +
" t_student s" +
" LEFT JOIN t_class clazz ON clazz.classno = s.classno" +
" LEFT JOIN t_sc sc ON sc.sno = s.sno" +
" LEFT JOIN t_course c ON c.cno = sc.cno" +
" WHERE" +
" sc.tno = " + tno +
" ORDER BY" +
" c.cno," +
" s.sno";
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
int cno = rs.getInt(“cno”);
String name = rs.getString(“name”);
int credit = rs.getInt(“credit”);
int sno = rs.getInt(“sno”);
int classno = rs.getInt(“classno”);
String sname = rs.getString(“sname”);
String cname = rs.getString(“cname”);
double score = rs.getDouble(“score”);
//封装成教师对象
StudentCourse sc = new StudentCourse(sno, sname, classno, cname, cno, name, credit, score);
list.add(sc);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
@Override
public int courseRemark(int sno, int cno, int tno, double score) {
String sql = “update t_sc set score = ? where sno = ? and cno = ? and tno = ?”;
Object[] params = {score,sno,cno,tno};
return DBUtils.executeUpdate(sql, params);
}
}
package com.bluehonour.sscs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.bluehonour.sscs.dao.StudentDao;
import com.bluehonour.sscs.entity.ClassInfo;
import com.bluehonour.sscs.entity.CriteriaStudent;
import com.bluehonour.sscs.entity.Student;
import com.bluehonour.sscs.util.DBUtils;
public class StudentDaoImpl implements StudentDao {
@Override
public int save(Student stu) {
String sql = “insert into t_student(password,sname,phone,sex,birthday,classno,remark) values(?,?,?,?,?,?,?)”;
Object[] params = { stu.getPassword(), stu.getSname(), stu.getPhone(), stu.getSex(), stu.getBirthday(),
stu.getClassno(), stu.getRemark() };
return DBUtils.executeUpdate(sql, params);
}
@Override
public List findAll() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Student student = null;
List stuList = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = “select * from t_student”;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行各个字段的值
int sno = rs.getInt(“sno”);
String password = rs.getString(“password”);
String sname = rs.getString(“sname”);
long phone = rs.getLong(“phone”);
String sex = rs.getString(“sex”);
Date birthday = rs.getDate(“birthday”);
int classno = rs.getInt(“classno”);
String remark = rs.getString(“remark”);
// 封装成对象
student = new Student(sno,password, sname, phone, sex, birthday, classno, remark);
stuList.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return stuList;
}
@Override
public int del(int sno) {
String sql = “delete from t_student where sno = ?”;
Object[] params = {sno };
return DBUtils.executeUpdate(sql, params);
}
@Override
public Student findById(int sno) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Student student = null;
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = "select * from t_student where sno = " + sno;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
if (rs.next()) {
// 取出结果集当前行各个字段的值
String password = rs.getString(“password”);
String sname = rs.getString(“sname”);
long phone = rs.getLong(“phone”);
String sex = rs.getString(“sex”);
Date birthday = rs.getDate(“birthday”);
int classno = rs.getInt(“classno”);
String remark = rs.getString(“remark”);
// 封装成对象
student = new Student(sno,password, sname, phone, sex, birthday, classno, remark);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return student;
}
@Override
public int update(Student stu) {
String sql = “update t_student set sname=?,password=?,phone=?,birthday=?,sex=?,classno=?,remark=? where sno=?”;
Object[] params = { stu.getSname(),stu.getPassword(),stu.getPhone(),stu.getBirthday(),stu.getSex(),stu.getClassno(),
stu.getRemark(),stu.getSno() };
return DBUtils.executeUpdate(sql, params);
}
@Override
public Student find(String sno, String password) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Student student = null;
try {
//建立连接
connection = DBUtils.getConnection();
//向数据库发送sql命令并得到结果
String sql = “select * from t_student where sno = ? and password = ?”;
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, sno);
preparedStatement.setString(2, password);
rs = preparedStatement.executeQuery();
//处理返回结果
if(rs.next()) {
//取出结果集当前行各个字段的值
String sname = rs.getString(“sname”);
long phone = rs.getLong(“phone”);
String sex = rs.getString(“sex”);
Date birthday = rs.getDate(“birthday”);
int classno = rs.getInt(“classno”);
String remark = rs.getString(“remark”);
//封装成对象
student = new Student(Integer.parseInt(sno), password, sname, phone, sex, birthday, classno, remark);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭数据库资源
DBUtils.closeAll(rs, preparedStatement, connection);
}
return student;
}
@Override
public List getClassInfo() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
ClassInfo clazz = null;
List list = new ArrayList();
try {
//建立连接
connection = DBUtils.getConnection();
//向数据库发送sql命令并得到结果
String sql = “select * from t_class”;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
//处理返回结果
while(rs.next()) {
//取出结果集当前行各个字段的值
int classno = rs.getInt(“classno”);
String cname = rs.getString(“cname”);
String cteacher = rs.getString(“cteacher”);
String classroom = rs.getString(“classroom”);
//封装成对象
clazz = new ClassInfo(classno, cname, cteacher, classroom);
list.add(clazz);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭数据库资源
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
@Override
public List getForListWithCriteriaStudent(CriteriaStudent student) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List stuList = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
StringBuffer sql = new StringBuffer();
sql.append(“select * from t_student”);
if(!student.getSno().equals(“”)) {
sql.append(" and sno like ‘%“+ student.getSno() +”%’");
}
if(!student.getSname().equals(“”)) {
sql.append(" and sname like ‘%“+ student.getSname() +”%’");
}
if(!student.getSex().equals(“”)) {
sql.append(" and sex =‘“+ student.getSex() +”’");
}
if(!student.getClassno().equals(“”)) {
sql.append(" and classno like ‘%“+ student.getClassno() +”%’");
}
if(!student.getRemark().equals(“”)) {
sql.append(" and remark=‘“+ student.getRemark() +”’");
}
String SQL = sql.toString();
SQL = SQL.replaceFirst(“and”, “where”);
System.out.println(SQL);
preparedStatement = connection.prepareStatement(SQL);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行各个字段的值
int sno = rs.getInt(“sno”);
String password = rs.getString(“password”);
String sname = rs.getString(“sname”);
long phone = rs.getLong(“phone”);
String sex = rs.getString(“sex”);
Date birthday = rs.getDate(“birthday”);
int classno = rs.getInt(“classno”);
String remark = rs.getString(“remark”);
// 封装成对象
Student stu = new Student(sno,password, sname, phone, sex, birthday, classno, remark);
stuList.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return stuList;
}
}
package com.bluehonour.sscs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.bluehonour.sscs.dao.TeacherCourseDao;
import com.bluehonour.sscs.entity.Course;
import com.bluehonour.sscs.entity.Teacher;
import com.bluehonour.sscs.util.DBUtils;
public class TeacherCourseDaoImpl implements TeacherCourseDao {
@Override
public int save(int cno, int tno) {
String sql = “insert into t_tc values(?,?)”;
Object[] params = {cno,tno};
return DBUtils.executeUpdate(sql, params);
}
@Override
public int delete(int cno, int tno) {
String sql = “delete from t_tc where cno = ? and tno = ?”;
Object[] params = {cno,tno};
return DBUtils.executeUpdate(sql, params);
}
@Override
public List findAll() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List list = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = “select * from t_course c”
-
" join t_tc tc"
-
" on (c.cno = tc.cno)"
-
" join t_teacher t"
-
" on (tc.tno = t.tno)"
-
" order by c.cno";
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行课程各个字段的值
int cno = rs.getInt(“cno”);
String name = rs.getString(“name”);
int credit = rs.getInt(“credit”);
Date periodstart = rs.getDate(“periodstart”);
Date periodend = rs.getDate(“periodend”);
// 封装成课程对象
Course course = new Course(cno,name, credit, periodstart, periodend);
//取出结果集中教师各个字段的值
int tno = rs.getInt(“tno”);
String tname = rs.getString(“tname”);
String password = rs.getString(“password”);
long phone = rs.getLong(“phone”);
Date hiredate = rs.getDate(“hiredate”);
String remark = rs.getString(“remark”);
//封装成教师对象
Teacher teacher = new Teacher(tno,tname, password, phone, hiredate, remark);
//将教师加入课程
course.setTeacher(teacher);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
}
package com.bluehonour.sscs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.bluehonour.sscs.dao.TeacherDao;
import com.bluehonour.sscs.entity.Course;
import com.bluehonour.sscs.entity.Student;
import com.bluehonour.sscs.entity.Teacher;
import com.bluehonour.sscs.util.DBUtils;
public class TeacherDaoImpl implements TeacherDao {
@Override
public int save(Teacher teacher) {
String sql = "insert into t_teacher(tname,password,phone,hiredate,remark) values(?,?,?,?,?) ";
Object[] params = {teacher.getTname(),teacher.getPassword(),teacher.getPhone(),teacher.getHiredate(),teacher.getRemark()};
return DBUtils.executeUpdate(sql, params);
}
@Override
public List findAll() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
List list = new ArrayList();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = “select * from t_teacher order by tno”;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行各个字段的值
int tno = rs.getInt(“tno”);
String tname = rs.getString(“tname”);
String password = rs.getString(“password”);
long phone = rs.getLong(“phone”);
Date hiredate = rs.getDate(“hiredate”);
String remark = rs.getString(“remark”);
// 封装成对象
Teacher teacher = new Teacher(tno,tname, password, phone, hiredate, remark);
list.add(teacher);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
@Override
public int delete(int tno) {
String sql = “delete from t_teacher where tno = ?”;
Object[] params = {tno };
return DBUtils.executeUpdate(sql, params);
}
@Override
public Teacher findById(int tno) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Teacher teacher = null;
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = "select * from t_teacher where tno = " + tno;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
if (rs.next()) {
// 取出结果集当前行各个字段的值
String tname = rs.getString(“tname”);
String password = rs.getString(“password”);
long phone = rs.getLong(“phone”);
Date hiredate = rs.getDate(“hiredate”);
String remark = rs.getString(“remark”);
// 封装成对象
teacher = new Teacher(tno, tname, password, phone, hiredate, remark);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return teacher;
}
@Override
public int update(Teacher teacher) {
String sql = “update t_teacher set tname=?,password=?,phone=?,hiredate=?,remark=? where tno=?”;
Object[] params = {teacher.getTname(),teacher.getPassword(),teacher.getPhone(),teacher.getHiredate(),teacher.getRemark(),teacher.getTno()};
return DBUtils.executeUpdate(sql, params);
}
@Override
public List getAssumeCourse(int tno) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Course course = null;
List list = new ArrayList<>();
try {
// 建立连接
connection = DBUtils.getConnection();
// 向数据库发送sql命令并得到结果
String sql = "select c.* from t_tc tc " +
"LEFT JOIN t_teacher t on t.tno = tc.tno " +
"LEFT JOIN t_course c on c.cno = tc.cno " +
"where tc.tno = " + tno ;
preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
// 处理返回结果
while (rs.next()) {
// 取出结果集当前行各个字段的值
int cno = rs.getInt(“cno”);
String name = rs.getString(“name”);
int credit = rs.getInt(“credit”);
Date periodstart = rs.getDate(“periodstart”);
Date periodend = rs.getDate(“periodend”);
// 封装成对象
course = new Course(cno,name, credit, periodstart, periodend);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, preparedStatement, connection);
}
return list;
}
}
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8”%>
<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>
<%-- ${error }
<%
if(request.getAttribute(“error”) != null){
%>
<%
} else{
%>
<%
}
%>
<input type=“text” name=“userId” id=“userId”">请输入4-10位用户名
<input type=“text” name=“userName”
id=“userName” value=“”>请输入您的真实姓名
<input type=“password” name=“passWord”
id=“passWord” value=“” size=“20px”>密码为6-16位
<input type=“password” name=“rePassWord”
id=“rePassWord” value=“” size=“20px”>请再次输入密码
请输入年龄
请输入成绩
<input type=“text” name=“enterDate”
id=“enterDate” value=“” οnfοcus=“WdatePicker({highLineWeekDay:true,isShowToday:true,isShowWeek:true})”>请输入入职时间
扫一扫
936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
