videodao.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.VideoInfo;

public class VideoDao {
	public VideoDao() {
	}

	/**
	 *
	 * @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 videoinfo VideoTypeInfo
	 * @return boolean
	 * @throws Exception
	 */
	public boolean addVideo(VideoInfo videoinfo) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;

		String str_sql = " insert into video (Videotypeid , Videoclassid , videoareaid ," +
				" name , intro , smallpic , bigpic , filepath , addtime , ip , uid , " +
				"ifview , ifdown , ifshow , Moneyview , Moneydown,hits,views,downs,size,minute) values ("
				+videoinfo.getVideotypeid()+","
				+videoinfo.getVideoclassid()+","
				+videoinfo.getVideoareaid()+",'"
				+videoinfo.getName()+"','"
				+videoinfo.getIntro()+"','"
				+videoinfo.getSmallpic()+"','"
				+videoinfo.getBigpic()+"','"
				+videoinfo.getFilepath()+"','"
				+videoinfo.getAddtime()+"','"
				+videoinfo.getIp()+"',"
				+videoinfo.getUid()+","
				+videoinfo.getIfview()+","
				+videoinfo.getIfdown()+","
				+videoinfo.getIfshow()+","
				+videoinfo.getMoneyview()+","
				+videoinfo.getMoneydown()+",0,0,0,'"+videoinfo.getSize()+"','"+videoinfo.getMinute()+"')";
		System.out.print(str_sql);
		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 videoinfo VideoInfo
	 * @return boolean
	 * @throws Exception
	 */
	public boolean editVideo(VideoInfo videoinfo) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = " update video set videotypeid="
				+videoinfo.getVideotypeid()+",videoclassid="
				+videoinfo.getVideoclassid()+",videoareaid="
				+videoinfo.getVideoareaid()+",name='"
				+videoinfo.getName()+"',intro='"
				+videoinfo.getIntro()+"',smallpic='"
				+videoinfo.getSmallpic()+"',bigpic='"
				+videoinfo.getBigpic()+"',filepath='"
				+videoinfo.getFilepath()+"',addtime='"
				+videoinfo.getAddtime()+"',ip='"
				+videoinfo.getIp()+"',uid="
				+videoinfo.getUid()+",ifview="
				+videoinfo.getIfview()+",ifdown="
				+videoinfo.getIfdown()+",ifshow="
				+videoinfo.getIfshow()+",moneyview="
				+videoinfo.getMoneyview()+",moneydown="
				+videoinfo.getMoneydown()+",hits="
				+videoinfo.getHits()+",views="
				+videoinfo.getViews()+",downs="
				+videoinfo.getDowns()+",size='"+videoinfo.getSize()+"',minute='"+videoinfo.getMinute()+"' where id="
				+videoinfo.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 deleteVideo(String id) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = "delete  from video 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 VideoInfo
	 * @throws Exception
	 */
	public VideoInfo getVideoByid(String id) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		VideoInfo videoinfo = new VideoInfo();
		ResultSet rs = null;
		if (id == null || id.equals("") || id.length() < 1
				|| !Format.isNumber(id)) {
			id = "0";
		}
		String str_sql = " select * from video where id="+id+"";
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(str_sql);
			if (rs.next()) {
				videoinfo.setAddtime(rs.getString("addtime"));
				videoinfo.setBigpic(rs.getString("bigpic"));
				videoinfo.setDowns(rs.getString("downs"));
				videoinfo.setFilepath(rs.getString("filepath"));
				videoinfo.setHits(rs.getString("hits"));
				videoinfo.setId(rs.getString("id"));
				videoinfo.setIfdown(rs.getString("ifdown"));
				videoinfo.setIfshow(rs.getString("ifshow"));
				videoinfo.setIfview(rs.getString("ifview"));
				videoinfo.setIntro(rs.getString("intro"));
				videoinfo.setIp(rs.getString("ip"));
				videoinfo.setMoneydown(rs.getString("moneydown"));
				videoinfo.setMoneyview(rs.getString("moneyview"));
				videoinfo.setName(rs.getString("name"));
				videoinfo.setSmallpic(rs.getString("smallpic"));
				videoinfo.setUid(rs.getString("uid"));
				videoinfo.setVideoareaid(rs.getString("videoareaid"));
				videoinfo.setVideoclassid(rs.getString("videoclassid"));
				videoinfo.setVideotypeid(rs.getString("videotypeid"));
				videoinfo.setViews(rs.getString("views"));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();

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

	public ArrayList queryVideoInfo(Pager pager) throws Exception {
		ArrayList list = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		//System.out.print(pager.getQueryCase());
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(pager.getQueryCase());
			list = new ArrayList();
			while (rs.next()) {
				VideoInfo videoinfo = new VideoInfo();

				videoinfo.setAddtime(rs.getString("addtime"));
				videoinfo.setBigpic(rs.getString("bigpic"));
				videoinfo.setDowns(rs.getString("downs"));
				videoinfo.setFilepath(rs.getString("filepath"));
				videoinfo.setHits(rs.getString("hits"));
				videoinfo.setId(rs.getString("id"));
				videoinfo.setIfdown(rs.getString("ifdown"));
				videoinfo.setIfshow(rs.getString("ifshow"));
				videoinfo.setIfview(rs.getString("ifview"));
				videoinfo.setIntro(rs.getString("intro"));
				videoinfo.setIp(rs.getString("ip"));
				videoinfo.setMoneydown(rs.getString("moneydown"));
				videoinfo.setMoneyview(rs.getString("moneyview"));
				videoinfo.setName(rs.getString("name"));
				videoinfo.setSmallpic(rs.getString("smallpic"));
				videoinfo.setUid(rs.getString("uid"));
				videoinfo.setVideoareaid(rs.getString("videoareaid"));
				videoinfo.setVideoclassid(rs.getString("videoclassid"));
				videoinfo.setVideotypeid(rs.getString("videotypeid"));
				videoinfo.setViews(rs.getString("views"));
				videoinfo.setAreaname(rs.getString("areaname"));
				videoinfo.setTypename(rs.getString("typename"));
				videoinfo.setClassname(rs.getString("classname"));
				
				
				list.add(videoinfo);

			}
		} 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;
		//System.out.print(pager.getQueryTotal());
		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;
	}

}