videoactordao.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.VideoActorInfo;

public class VideoActorDao {
    public VideoActorDao() {
    }

    /**
     *
     * @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 videoactorinfo VideoTypeInfo
     * @return boolean
     * @throws Exception
     */
    public boolean addVideoActor(VideoActorInfo videoactorinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql =" insert into videoactor (sex,name,videoareaid,intro) values ('" 
        		+videoactorinfo.getSex()+"','"
        		+videoactorinfo.getName()+"',"
        		+videoactorinfo.getVideoareaid()+",'"
        		+videoactorinfo.getIntro()+"')";
         
        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 videoactorinfo VideoActorInfo
     * @return boolean
     * @throws Exception
     */
    public boolean editVideoActor(VideoActorInfo videoactorinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = " update videoactor set sex='" 
        		+videoactorinfo.getSex()+"',name='"
        		+videoactorinfo.getName()+"',videoareaid="
        		+videoactorinfo.getVideoareaid()+",intro='"
        		+videoactorinfo.getIntro()+"' ";

        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 deleteVideoActor(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = "delete  from videoactor 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 VideoActorInfo
     * @throws Exception
     */
    public VideoActorInfo getVideoActorById(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoActorInfo videoactorinfo = new VideoActorInfo();
        ResultSet rs = null;
        if(id==null||id.equals("")||id.length()<1||!Format.isNumber(id))
        {
        	id="0";
        }
        String str_sql = " select * from videoactor where id ="+id+"";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	
            	videoactorinfo.setId(rs.getString("id"));
            	videoactorinfo.setIntro(rs.getString("intro"));
            	videoactorinfo.setName(rs.getString("name"));
            	videoactorinfo.setSex(rs.getString("sex"));
            	videoactorinfo.setVideoareaid(rs.getString("videoareaid"));
            	
            }else
            {
            	videoactorinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

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

    public VideoActorInfo getVideoActorByName(String name) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        VideoActorInfo videoactorinfo = new VideoActorInfo();
        ResultSet rs = null;
        if(name==null||name.equals("")||name.length()<1)
        {
        	name="0";
        }
        String str_sql = " select * from videoactor where name ='"+name+"'";
       // System.out.print(str_sql);
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	
            	videoactorinfo.setId(rs.getString("id"));
            	videoactorinfo.setIntro(rs.getString("intro"));
            	videoactorinfo.setName(rs.getString("name"));
            	videoactorinfo.setSex(rs.getString("sex"));
            	videoactorinfo.setVideoareaid(rs.getString("videoareaid"));
            	
            }else
            {
            	videoactorinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

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


    public ArrayList queryVideoActorInfo(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()) {
                VideoActorInfo videoactorinfo = new VideoActorInfo();

            	videoactorinfo.setId(rs.getString("id"));
            	videoactorinfo.setIntro(rs.getString("intro"));
            	videoactorinfo.setName(rs.getString("name"));
            	videoactorinfo.setSex(rs.getString("sex"));
            	videoactorinfo.setVideoareaid(rs.getString("videoareaid"));
            	videoactorinfo.setAreaname(rs.getString("areaname"));
                list.add(videoactorinfo);

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

}