package com.course.dao;
import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.course.bean.Course;
import com.course.bean.CourseResult;
public class CourseDao {
PreparedStatement ps = null;
ResultSet rs = null;
DBSql mydb = null;
public CourseDao() {
mydb = new DBSql();
}
public List<Course> findVerifyCourse(String type, int firstRecord,
int lastRecord) {
List<Course> list = new ArrayList<Course>();
Course course = null;
// 1 2 3 4 5
String sql = "select tt.cid,tt.cname,t.trealname,tt.cstatus,tt.cyear,"
+
// 6 7 8 9 0 1 2 3 4 15
" cterm,ccharacter,cmark,chour,cmethod,cinstitute,cbook,cexcuse,cdate,t.tid "
+ "from (select *,row_number() over(order by cid) rn from course";
if (type == null || type.equals(""))
return null;
else if (type.equals("1"))
sql += " where cstatus='" + 1 + "' ";
else if (type.equals("2"))
sql += " where cstatus='" + 2 + "' ";
else
return null;
sql+=") tt "
+ "left join teacher t on t.tid = tt.tid "
+ " where rn between " + firstRecord + " and " + lastRecord;
try {
ps = mydb.getCon().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
course = new Course();
course.setCname(rs.getString(2));
course.setTid(rs.getInt(15));
course.setCyear(rs.getString(5));
course.setCterm(rs.getString(6));
course.setCcharacter(rs.getString(7));
course.setCmark(rs.getString(8));
course.setChour(rs.getString(9));
course.setCmethod(rs.getString(10));
course.setCinstitute(rs.getString(11));
course.setCbook(rs.getString(12));
course.setCexcuse(rs.getString(13));
course.setCdate(rs.getString(14));
course.setCstatus(rs.getString(4));
course.setCid(rs.getInt(1));
course.setTrealname(rs.getString(3));
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
mydb.close();
}
return list;
}
public int findVerifyCounts(String type) {
int count = 0;
String sql = "select count(*) from course where 1=1 ";
if (type == null || type.equals(""))
return 0;
else if (type.equals("1"))
sql += " and cstatus='" + 1 + "' ";
else if (type.equals("2"))
sql += " and cstatus='" + 2 + "' ";
try {
ps = mydb.getCon().prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
mydb.close();
}
return count;
}
public int deleteCourseById(int id) {
String sql = "delete from course where cid = ?";
int i = 0;
try {
ps = mydb.getCon().prepareStatement(sql);
ps.setInt(1, id);
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
mydb.close();
}
return i;
}
public List<Course> findAllCourse(int firstRecord, int lastRecord) {
List<Course> list = new ArrayList<Course>();
Course course = null;
// 1 2 3 4 5
String sql = "select tt.cid,tt.cname,t.trealname,tt.cstatus,tt.cyear,"
+
// 6 7 8 9 0 1 2 3 4 5
" cterm,ccharacter,cmark,chour,cmethod,cinstitute,cbook,cexcuse,cdate,t.tid from "
+ "(select *,row_number() over(order by cname) rn from course where cstatus =3 ) tt "
+ " left join teacher t on t.tid = tt.tid where rn between "
+ firstRecord + " and " + lastRecord;
try {
ps = mydb.getCon().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
course = new Course();
course.setCname(rs.getString(2));
course.setTid(rs.getInt(15));
course.setCyear(rs.getString(5));
course.setCterm(rs.getString(6));
course.setCcharacter(rs.getString(7));
course.setCmark(rs.getString(8));
course.setChour(rs.getString(9));
course.setCmethod(rs.getString(10));
course.setCinstitute(rs.getString(11));
course.setCbook(rs.getString(12));
course.setCexcuse(rs.getString(13));
course.setCdate(rs.getString(14));
course.setCstatus(rs.getString(4));
course.setCid(rs.getInt(1));
course.setTrealname(rs.getString(3));
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
mydb.close();
}
return list;
}
public List<Course> findMyCourse(int tid,int firstRecord, int lastRecord) {
List<Course> list = new ArrayList<Course>();
Course course = null;
// 1 2 3 4 5
String sql = "select tt.cid,tt.cname,t.trealname,tt.cstatus,tt.cyear,"
+
// 6 7 8 9 0 1 2 3 4 5
" cterm,ccharacter,cmark,chour,cmethod,cinstitute,cbook,cexcuse,cdate,t.tid from "
+ "(select *,row_number() over(order by cname) rn from course where tid="+tid+") tt "
+ " left join teacher t on t.tid = tt.tid where rn between "
+ firstRecord + " and " + lastRecord ;
try {
ps = mydb.getCon().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
course = new Course();
course.setCname(rs.getString(2));
course.setTid(rs.getInt(15));
course.setCyear(rs.getString(5));
course.setCterm(rs.getString(6));
course.setCcharacter(rs.getString(7));
course.setCmark(rs.getString(8));
course.setChour(rs.getString(9));
course.setCmethod(rs.getString(10));
course.setCinstitute(rs.getString(11));
course.setCbook(rs.getString(12));
course.setCexcuse(rs.getString(13));
course.setCdate(rs.getString(14));
course.setCstatus(rs.getString(4));
course.setCid(rs.getInt(1));
course.setTrealname(rs.getString(3));
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
mydb.close();
}
return list;
}
public List<Course> findMyCourseess(int tid,int firstRecord, int lastRecord) {
List<Course> list = new ArrayList<Course>();
Course course = null;
// 1 2 3 4 5
String sql = "select tt.cid,tt.cname,t.trealname,tt.cstatus,tt.cyear,"
+
// 6 7 8 9 0 1 2 3 4 5
" cterm,ccharacter,cmark,chour,cmethod,cinstitute,cbook,cexcuse,cdate,t.tid from "
+ "(select *,row_number() over(order by cname) rn from course where tid = "+tid+") tt "
+ " left join teacher t on t.tid = tt.tid where rn between "
+ firstRecord + " and " + lastRecord +" and tt.cstatus = 3";
try {
ps = mydb.getCon().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
course = new Course();
course.setCname(rs.getString(2));
course.setTid(rs.getInt(15));
course.setCyear(rs.getString(5));
course.setCterm(rs.
