op_resourse.java

JSP_在线教学系统,本系统采用三层结构设计

package grad.util.course;
import java.util.*;
import java.sql.*;
import grad.util.DataBase;
import grad.util.course.resourse;
import java.io.*;

public class op_resourse {
  public op_resourse() {
  }
  /**
   * 查询指定课程编号的所有可下载课程资源 type=2
   * @param sourseid－课程编号
   * @return
   */
public ArrayList getAllResourseByCourse(String sourseid)
  {
   Connection conn=null;
   Statement st=null;
   ResultSet rs=null;
   String sql ="select * from t_resource where course_id='"+sourseid+"' and type=2";
   ArrayList al=new ArrayList();
   try{
     DataBase ds = new DataBase();
     conn = ds.conn;
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     while (rs.next()) {
       resourse cs=new resourse();
        cs.setid(rs.getInt(1));
        cs.settitle(rs.getString(2));
        cs.setcourse_id(rs.getString(3));
        cs.setlink(rs.getString(4));
        cs.setsdate(rs.getString(5));
        cs.setext(rs.getString(6));
        cs.setfilesize(rs.getString(7));
        al.add(cs);
     }
     return al;
   }catch(Exception e){System.out.println(e.getMessage());return null;}
  }
  /**
   * 按扩展名和课程编号，查询课程资源，方便学生界面按ppt,word等分类展示
   * @param sourseid
   * @param ext
   * @return
   */
  public ArrayList getAllResourseByCourseAndExt(String sourseid,String ext)
    {
     Connection conn=null;
     Statement st=null;
     ResultSet rs=null;
     String sql ="select * from t_resource where course_id='"+sourseid+"' and ext='"+ext+"'";
     ArrayList al=new ArrayList();
     try{
       DataBase ds = new DataBase();
       conn = ds.conn;
       st = conn.createStatement();
       rs = st.executeQuery(sql);
       while (rs.next()) {
         resourse cs=new resourse();
          cs.setid(rs.getInt(1));
          cs.settitle(rs.getString(2));
          cs.setcourse_id(rs.getString(3));
          cs.setlink(rs.getString(4));
          cs.setsdate(rs.getString(5));
          cs.setext(rs.getString(6));
          cs.setfilesize(rs.getString(7));
          al.add(cs);
       }
       return al;
     }catch(Exception e){System.out.println(e.getMessage());return null;}

    }
    /**
     * 添加课程资源
     * @param rs
     */
    public void addResourse(resourse rs)
    {
     Connection conn=null;
     Statement st=null;
     String sql="insert into t_resource(title,course_id,link,date,ext,filesize,type)values("+
                " '"+rs.gettitle()+"',"+
                " '"+rs.getcourse_id()+"',"+
                " '"+rs.getlink()+"',"+
                " getdate(),"+
                " '"+rs.getext()+"',"+
                " '"+rs.getfilesize()+"',"+
                " '"+rs.getpower()+"')";
            System.out.println(sql);
     try{
       DataBase ds = new DataBase();
       conn = ds.conn;
       st = conn.createStatement();
       st.executeUpdate(sql);

     }catch(Exception e){System.out.println(e.getMessage());return ;}
    }
    /**
     * 删除课程资源
     * @param id
     */
    public void delResourse(String id)
    {
    Connection conn=null;
    Statement st=null;
    String sql="delete from t_resource where id='"+id+"'";
           System.out.println(sql);
    try{
      DataBase ds = new DataBase();
      conn = ds.conn;
      st = conn.createStatement();
      st.executeUpdate(sql);
    }catch(Exception e){System.out.println(e.getMessage());return ;}
    }
    /**
     * 取出资源编号最大值。资源上传后是按这个编号加扩展名莱存储的
     * 前台浏览和下载都是按编号+扩展名去寻找资源的
     * @return
     */
    public int getMaxId()
      {
      Connection conn=null;
      Statement st=null;
      ResultSet rs=null;
      int id=0;
      String sql="select max(id) from t_resource ";
             System.out.println(sql);
      try{
        DataBase ds = new DataBase();
        conn = ds.conn;
        st = conn.createStatement();
        rs = st.executeQuery(sql);
      while (rs.next()) {
         id=rs.getInt(1);
      }
          return id+1;
      }catch(Exception e){System.out.println(e.getMessage());return 0;}
      }
}