videotypedao.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.VideoTypeInfo;

public class VideoTypeDao {
    public VideoTypeDao() {
    }

    /**
     *
     * @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 videotypeinfo VideoTypeInfo
     * @return boolean
     * @throws Exception
     */
    public boolean addVideoType(VideoTypeInfo videotypeinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        
        String str_sql =" insert into videotype(typename,remark) values ('"
        	+videotypeinfo.getTypename()+"','"
        +videotypeinfo.getRemark()+"')";
        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 videotypeinfo VideoTypeInfo
     * @return boolean
     * @throws Exception
     */
    public boolean editVideoType(VideoTypeInfo videotypeinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = " update videotype set typename='"
        	+videotypeinfo.getTypename()+"',remark='"
        +videotypeinfo.getRemark()+"' where id="
        +videotypeinfo.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 deleteVideoType(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql1="delete from video where videotypeid="+id+"";
        String str_sql2 = "delete  from videoclass where videotypeid=" + id + "";
          String str_sql = "delete  from videotype where id=" + id + "";
      
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql1);//删除视频记录
            stmt.executeUpdate(str_sql);
            stmt.executeUpdate(str_sql2);
            
            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 VideoTypeInfo
     * @throws Exception
     */
    public VideoTypeInfo getVideoTypeByid(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoTypeInfo videotypeinfo = new VideoTypeInfo();
        ResultSet rs = null;
        if(id==null||id.equals("")||id.length()<1||!Format.isNumber(id))
        {
        	id="0";
        }
        String str_sql = " select * from videotype where id="+id+"";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	videotypeinfo.setId(rs.getString("id"));
            	videotypeinfo.setRemark(rs.getString("remark"));
            	videotypeinfo.setTypename(rs.getString("typename"));
            	
                 
            }else
            {
            	videotypeinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return videotypeinfo;
    }
    public VideoTypeInfo getVideoTypeByTypename(String typename) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoTypeInfo videotypeinfo = new VideoTypeInfo();
        ResultSet rs = null;
        if(typename==null||typename.equals("")||typename.length()<1)
        {
        	typename="0";
        }
        String str_sql = " select * from videotype where typename='"+typename+"'";
         
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	videotypeinfo.setId(rs.getString("id"));
            	videotypeinfo.setRemark(rs.getString("remark"));
            	videotypeinfo.setTypename(rs.getString("typename"));
            	
                 
            }else
            {
            	videotypeinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return videotypeinfo;
    }
    public ArrayList queryVideoTypeInfo(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()) {
                VideoTypeInfo videotypeinfo = new VideoTypeInfo();
                
                videotypeinfo.setId(rs.getString("id"));
            	videotypeinfo.setRemark(rs.getString("remark"));
            	videotypeinfo.setTypename(rs.getString("typename"));
            	
                list.add(videotypeinfo);

            }
        } 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;
       }

}