📄 persondaoimpl.java
字号:
try
{
pstm = dbc.getConnection().prepareStatement(sql);
ResultSet rs = pstm.executeQuery();
while(rs.next())
{
PersonvoUser personvouser =new PersonvoUser();
personvouser.setTid(rs.getInt(1));
personvouser.setName(rs.getString(2));
personvouser.setWorkno(rs.getString(3));
all.add(personvouser) ;
}
rs.close() ;
pstm.close();
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
return all ;
}
//查询老师用户信息
public List queryAlltch() throws Exception
{
List all = new ArrayList() ;
String sql = "select tid,name,workno from user_table where type='老师'";
PreparedStatement pstm = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstm = dbc.getConnection().prepareStatement(sql);
ResultSet rs = pstm.executeQuery();
while(rs.next())
{
PersonvoUser personvouser =new PersonvoUser();
personvouser.setTid(rs.getInt(1));
personvouser.setName(rs.getString(2));
personvouser.setWorkno(rs.getString(3));
all.add(personvouser) ;
}
rs.close() ;
pstm.close();
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
return all ;
}
//取得指定用户信息
public void selectuserstu(PersonvoUser personuser) throws Exception
{
String sql="select name,type,workno,text from user_table where tid=? ";
String sql2="select classname from class_table where classno=(select classno from user_class where workno=?)";
String sql3="select sum(price)as prc from lesson_table where lessonno=any(select lessonno from user_lesson where workno=?)";
String sql4="select sum(cost)as cst from cost_table where costno=any(select costno from user_cost where workno=?)";
PreparedStatement pstm = null;
PreparedStatement pstm2 = null;
PreparedStatement pstm3 = null;
PreparedStatement pstm4 = null;
DateBaseConnection dbc = null;
dbc = new DateBaseConnection();
try
{
pstm = dbc.getConnection().prepareStatement(sql);
pstm.setInt(1, personuser.getTid());
ResultSet rs = pstm.executeQuery();
if(rs.next())
{
personuser.setName(rs.getString(1));
personuser.setType(rs.getString(2));
personuser.setWorkno(rs.getString(3));
personuser.setText(rs.getString(4));
//取得所在的班级
pstm2 = dbc.getConnection().prepareStatement(sql2);
pstm2.setString(1, rs.getString(3));
ResultSet rs2 = pstm2.executeQuery();
if(rs2.next())
{
personuser.setClassname(rs2.getString(1));
}
//取得该用户要缴的费用
pstm3 = dbc.getConnection().prepareStatement(sql3);
pstm3.setString(1, rs.getString(3));
ResultSet rs3 = pstm3.executeQuery();
if(rs3.next())
{
personuser.setPrice(rs3.getString("prc"));
}
//取得已经缴的费用
pstm4 = dbc.getConnection().prepareStatement(sql4);
pstm4.setString(1, rs.getString(3));
ResultSet rs4 = pstm4.executeQuery();
if(rs4.next())
{
personuser.setCost(rs4.getInt("cst"));
}
rs2.close() ;
rs3.close() ;
rs4.close() ;
}
rs.close() ;
pstm.close() ;
pstm2.close() ;
pstm3.close() ;
pstm4.close() ;
}
catch (Exception e)
{
//throw new Exception("操作出现错误！！！") ;
e.printStackTrace();
}
finally
{
dbc.close() ;
}
}
//修改学生意见
public void updatetext(PersonvoUser personuser) throws Exception
{
String sql = "Update user_table set text=? where tid=?" ;
PreparedStatement pstmt = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstmt = dbc.getConnection().prepareStatement(sql) ;
pstmt.setString(1,personuser.getText()) ;
pstmt.setInt(2,personuser.getTid()) ;
pstmt.executeUpdate() ;
pstmt.close() ;
}
catch (Exception e)
{
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
}
//查询全部课程
public List queryAlllesson() throws Exception
{
List all = new ArrayList() ;
String sql = "select lessonno,lessonname,tid,price from lesson_table";
PreparedStatement pstm = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstm = dbc.getConnection().prepareStatement(sql);
ResultSet rs = pstm.executeQuery();
while(rs.next())
{
PersonvoUser personvouser =new PersonvoUser();
personvouser.setLessonno(rs.getString(1));
personvouser.setLessonname(rs.getString(2));
personvouser.setTid(rs.getInt(3));
personvouser.setPrice(rs.getString(4));
all.add(personvouser);
}
rs.close() ;
pstm.close();
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
return all ;
}
//所有课程汇总
public void countlesson(PersonvoUser personuser) throws Exception
{
String sql = "SELECT count(*)as pagecount FROM lesson_table";
PreparedStatement pstmt = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstmt = dbc.getConnection().prepareStatement(sql) ;
ResultSet rs = pstmt.executeQuery() ;
while(rs.next())
{
//PersonvoUser person = new PersonvoUser() ;
personuser.setPagecount(rs.getInt("pagecount")) ;
}
rs.close() ;
pstmt.close() ;
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
}
//返回学生选课信息
public List selectstulesson(PersonvoUser personuser) throws Exception
{
List all = new ArrayList() ;
String sql = "SELECT lessonno,lessonname from lesson_table where lessonno=any(select lessonno from user_lesson where workno=(select workno from user_table where tid=?))";
PreparedStatement pstm = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstm = dbc.getConnection().prepareStatement(sql);
pstm.setInt(1,personuser.getTid()) ;
ResultSet rs = pstm.executeQuery();
while(rs.next())
{
PersonvoUser peruser = new PersonvoUser();
peruser.setLessonno(rs.getString(1));
peruser.setLessonname(rs.getString(2));
all.add(peruser);
}
rs.close() ;
pstm.close();
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
return all ;
}
//用户选课插入数据
public void lessonchole(PersonvoUser personuser) throws Exception
{
String sql = "SELECT workno FROM user_table where tid=?";
String sql2 = "INSERT INTO user_lesson(tid,lessonno,workno) values((select max(tid) from user_lesson)+1,?,?)";
PreparedStatement pstmt = null ;
PreparedStatement pstmt2 = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstmt = dbc.getConnection().prepareStatement(sql) ;
pstmt.setInt(1,personuser.getTid()) ;
ResultSet rs = pstmt.executeQuery() ;
while(rs.next())
{
personuser.setWorkno(rs.getString(1)) ;
pstmt2 = dbc.getConnection().prepareStatement(sql2) ;
for(int j=0;j<personuser.getCkx().length;j++)
{
String ckx[]=personuser.getCkx();
pstmt2.setString(1,ckx[j]);
pstmt2.setString(2,rs.getString(1)) ;
pstmt2.executeUpdate() ;
}
}
rs.close() ;
pstmt.close() ;
pstmt2.close() ;
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
}
//取得学生信息
public List selectAllstu( ) throws Exception
{
List all =new ArrayList();
String sql="select tid,name,workno from user_table where type='学生' ";
PreparedStatement pstm = null;
DateBaseConnection dbc = null;
dbc = new DateBaseConnection();
try
{
pstm = dbc.getConnection().prepareStatement(sql);
ResultSet rs = pstm.executeQuery();
while(rs.next())
{
PersonvoUser personuser = new PersonvoUser();
personuser.setTid(rs.getInt(1));
personuser.setName(rs.getString(2));
personuser.setWorkno(rs.getString(3));
all.add(personuser);
}
rs.close() ;
pstm.close() ;
}
catch (Exception e)
{
//throw new Exception("操作出现错误！！！") ;
e.printStackTrace();
}
finally
{
dbc.close() ;
}
return all ;
}
//取得学生缴费信息
public void selectAllstucost(PersonvoUser puser) throws Exception
{
String sql2="select classname from class_table where classno=(select classno from user_class where workno=?)";
String sql3="select sum(price)as prc from lesson_table where lessonno=any(select lessonno from user_lesson where workno=?)";
String sql4="select sum(cost)as cst from cost_table where costno=any(select costno from user_cost where workno=?)";
PreparedStatement pstm2 = null;
PreparedStatement pstm3 = null;
PreparedStatement pstm4 = null;
DateBaseConnection dbc = null;
dbc = new DateBaseConnection();
try
{
//取得所在的班级
pstm2 = dbc.getConnection().prepareStatement(sql2);
pstm2.setString(1, puser.getWorkno());
ResultSet rs2 = pstm2.executeQuery();
if(rs2.next())
{
puser.setClassname(rs2.getString(1));
}
//取得该用户要缴的费用
pstm3 = dbc.getConnection().prepareStatement(sql3);
pstm3.setString(1, puser.getWorkno());
ResultSet rs3 = pstm3.executeQuery();
if(rs3.next())
{
puser.setPrice(rs3.getString("prc"));
}
//取得已经缴的费用
pstm4 = dbc.getConnection().prepareStatement(sql4);
pstm4.setString(1, puser.getWorkno());
ResultSet rs4 = pstm4.executeQuery();
if(rs4.next())
{
puser.setCost(rs4.getInt("cst"));
}
rs2.close() ;
rs3.close() ;
rs4.close() ;
pstm2.close() ;
pstm3.close() ;
pstm4.close() ;
}
catch (Exception e)
{
//throw new Exception("操作出现错误！！！") ;
e.printStackTrace();
}
finally
{
dbc.close() ;
}
}
//删除用户
public void deleteuser(PersonvoUser personuser) throws Exception
{
String sql = "DELETE FROM user_table WHERE tid=?" ;
PreparedStatement pstmt = null ;
DateBaseConnection dbc = null ;
dbc = new DateBaseConnection() ;
try
{
pstmt = dbc.getConnection().prepareStatement(sql) ;
pstmt.setInt(1,personuser.getTid()) ;
pstmt.executeUpdate() ;
pstmt.close() ;
}
catch (Exception e)
{
throw new Exception("操作中出现错误！！！") ;
}
finally
{
dbc.close() ;
}
}
//取得指定用户缴费信息
public List selectstucost(PersonvoUser personuser) throws Exception
{
List all =new ArrayList();
String sql="select costno,cost,time from cost_table where costno=any(select costno from user_cost where workno=(select workno from user_table where tid=?)) ";
PreparedStatement pstm = null;
DateBaseConnection dbc = null;
dbc = new DateBaseConnection();
try
{
pstm = dbc.getConnection().prepareStatement(sql);
pstm.setInt(1, personuser.getTid());
ResultSet rs = pstm.executeQuery();
while(rs.next())
{
PersonvoUser puser = new PersonvoUser();
puser.setCostno(rs.getString(1));
puser.setCost(rs.getInt(2));
puser.setTime(rs.getString(3));
all.add(puser);
}
rs.close() ;
pstm.close() ;
}
catch (Exception e)
{
//throw new Exception("操作出现错误！！！") ;
e.printStackTrace();
}
finally
{
dbc.close() ;
}
return all ;
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -