videoareadao.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.VideoAreaInfo;

public class VideoAreaDao {
    public VideoAreaDao() {
    }

    /**
     *
     * @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 addVideoArea(VideoAreaInfo videoareainfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql =" insert into videoarea (areaname) values ('"+videoareainfo.getAreaname()+"')";
        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 videoareainfo VideoAreaInfo
     * @return boolean
     * @throws Exception
     */
    public boolean editVideoArea(VideoAreaInfo videoareainfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = " update videoarea set areaname='"+videoareainfo.getAreaname()+"' where id ="+videoareainfo.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 deleteVideoArea(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = "delete  from videoarea where id=" + id + "";
        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 VideoAreaInfo
     * @throws Exception
     */
    public VideoAreaInfo getVideoAreaById(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoAreaInfo videoareainfo = new VideoAreaInfo();
        ResultSet rs = null;
        if(id==null||id.equals("")||id.length()<1||!Format.isNumber(id))
        {
        	id="0";
        }
        String str_sql = " select * from videoarea where id="+id+"";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	videoareainfo.setAreaname(rs.getString("areaname"));
            	videoareainfo.setId(rs.getString("id"));
            	
                 
            }
        } catch (SQLException e) {
            e.printStackTrace();

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

    public VideoAreaInfo getVideoAreaByAreaname(String areaname) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoAreaInfo videoareainfo = new VideoAreaInfo();
        ResultSet rs = null;
        if(areaname==null||areaname.equals("")||areaname.length()<1)
        {
        	areaname="0";
        }
        String str_sql = " select * from videoarea where areaname='"+areaname+"'";
        
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            
            if (rs.next()) {
            	videoareainfo.setAreaname(rs.getString("areaname"));
            	videoareainfo.setId(rs.getString("id"));
            	
                 
            }else
            {
            	videoareainfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

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


    public ArrayList queryVideoAreaInfo(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()) {
                VideoAreaInfo videoareainfo = new VideoAreaInfo();
                
                videoareainfo.setAreaname(rs.getString("areaname"));
            	videoareainfo.setId(rs.getString("id"));
            	
                list.add(videoareainfo);

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

}