videoclassdao.java

本程序是作者开发的一个宽带娱乐系统的一个模块.

package com.singnet.video.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.singnet.util.Format;
import com.singnet.util.Pager;
import com.singnet.video.VideoClassInfo;

public class VideoClassDao {
    public VideoClassDao() {
    }

    /**
     *
     * @return Connection
     * @throws Exception
     */
    public Connection getConnection() throws Exception {
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:/MySqlDS");
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ds.getConnection();
        } catch (SQLException sqlEx) {
            System.out.println("Error connect to pool.");
        }
        return conn;
    }

    /**
     *
     * @param videoareainfo VideoTypeInfo
     * @return boolean
     * @throws Exception
     */
    public boolean addVideoClass(VideoClassInfo videoclassinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql =" insert into videoclass (classname,orderid,videotypeid,ifshow) values ('"
        	+videoclassinfo.getClassname()+"',"
        	+videoclassinfo.getOrderid()+","
        	+videoclassinfo.getVideotypeid()+","
        	+videoclassinfo.getIfshow()+")";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql);
            re = true;
        } catch (SQLException e) {
            e.printStackTrace();
            re = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return re;
    }

    /**
     *
     * @param videoclassinfo VideoClassInfo
     * @return boolean
     * @throws Exception
     */
    public boolean editVideoClass(VideoClassInfo videoclassinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = " update videoclass set classname='"
        	+videoclassinfo.getClassname()+"',orderid="
        +videoclassinfo.getOrderid()+",videotypeid="
        +videoclassinfo.getVideotypeid()+",ifshow="
        +videoclassinfo.getIfshow()+" where id="+videoclassinfo.getId()+"";

        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql);
            re = true;
        } catch (SQLException e) {
            e.printStackTrace();
            re = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return re;
    }

    /**
     *
     * @param id String
     * @return boolean
     * @throws Exception
     */
    public boolean deleteVideoClass(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = "delete  from videoclass where id=" + id + "";
        String str_sql1 = "delete  from video where videoclassid=" + id + "";
        
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql);
            stmt.executeUpdate(str_sql1);
            
            re = true;
        } catch (SQLException e) {
            e.printStackTrace();
            re = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return re;
    }

    /**
     *
     * @param id String
     * @return VideoClassInfo
     * @throws Exception
     */
    public VideoClassInfo getVideoClassById(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoClassInfo videoclassinfo = new VideoClassInfo();
        ResultSet rs = null;
        if(id==null||id.equals("")||id.length()<1||!Format.isNumber(id))
        {
        	id="0";
        }
        String str_sql = " select * from videoclass where id="+id+"";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	
            	videoclassinfo.setClassname(rs.getString("classname"));
            	videoclassinfo.setId(rs.getString("id"));
            	videoclassinfo.setIfshow(rs.getString("ifshow"));
            	videoclassinfo.setOrderid(rs.getString("orderid"));
            	videoclassinfo.setVideotypeid(rs.getString("videotypeid"));
            	
            }else
            {
            	videoclassinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return videoclassinfo;
    }
    public VideoClassInfo getVideoClassByClassname(String classname) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoClassInfo videoclassinfo = new VideoClassInfo();
        ResultSet rs = null;
        if(classname==null||classname.equals("")||classname.length()<1)
        {
        	classname="0";
        }
        String str_sql = " select * from videoclass where classname='"+classname+"'";
      
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	
            	videoclassinfo.setClassname(rs.getString("classname"));
            	videoclassinfo.setId(rs.getString("id"));
            	videoclassinfo.setIfshow(rs.getString("ifshow"));
            	videoclassinfo.setOrderid(rs.getString("orderid"));
            	videoclassinfo.setVideotypeid(rs.getString("videotypeid"));
            	
            }else
            {
            	videoclassinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return videoclassinfo;
    }

    public ArrayList queryVideoClassInfo(Pager pager) throws Exception {
        ArrayList list = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(pager.getQueryCase());
            list = new ArrayList();
            while (rs.next()) {
                VideoClassInfo videoclassinfo = new VideoClassInfo();
                
            	videoclassinfo.setClassname(rs.getString("classname"));
            	videoclassinfo.setId(rs.getString("id"));
            	videoclassinfo.setIfshow(rs.getString("ifshow"));
            	videoclassinfo.setOrderid(rs.getString("orderid"));
            	videoclassinfo.setVideotypeid(rs.getString("videotypeid"));
            	videoclassinfo.setTypename(rs.getString("typename"));
                  
                list.add(videoclassinfo);

            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return list;
    }
    /**
     *
     * @param pager Pager
     * @return int
     * @throws Exception
     */
    public int getTotal(Pager pager) throws Exception {
           Connection conn = null;
           Statement stmt = null;
           ResultSet rs = null;
           int total = 0;
           try {
               conn = getConnection();
               stmt = conn.createStatement();
               rs = stmt.executeQuery(pager.getQueryTotal());
               if (rs.next()) {
                   total = rs.getInt("t");
               }
           } catch (SQLException e) {
               e.printStackTrace();
               total = 0;

           } finally {
               if (conn != null) {
                   try {
                       conn.close();
                   } catch (Exception sqlEx) {
                   }
               }
           }
           return total;
       }

}