musiccommenddao.java

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

package com.singnet.music.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.music.MusicCommendInfo;
import com.singnet.util.Pager;

public class MusicCommendDao {
	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;
	}

	public boolean addMusicCommendInfo(MusicCommendInfo musicCommendInfo)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = " insert into musiccommend (name,zjid,title,content,addtime,ip,uid) values ('"
				+ musicCommendInfo.getName()
				+ "',"
				+ musicCommendInfo.getZjid()
				+ ",'"
				+ musicCommendInfo.getTitle()
				+ "','"
				+ musicCommendInfo.getContent()
				+ "','"
				+ musicCommendInfo.getAddtime()
				+ "','"
				+ musicCommendInfo.getIp()
				+ "','"
				+ musicCommendInfo.getUid()
				+ "') ";
		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;
	}

	public boolean editMusicCommendInfo(MusicCommendInfo musicCommendInfo)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = " update musiccommend set name='"
				+ musicCommendInfo.getName() + "',zjid="
				+ musicCommendInfo.getZjid() + ",title='"
				+ musicCommendInfo.getTitle() + "',content='"
				+ musicCommendInfo.getContent() + "',addtime='"
				+ musicCommendInfo.getAddtime() + "',ip'"
				+ musicCommendInfo.getIp() + "',uid='"
				+ musicCommendInfo.getUid() + "' where id="
				+ musicCommendInfo.getId() + "";
		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;
	}

	public boolean deleteMusicCommendInfo(String id) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = "  delete from musiccommend 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;
	}

	public MusicCommendInfo queryMusicCommendInfoById(String id)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		MusicCommendInfo re = new MusicCommendInfo();
		String str_sql = " select * from musiccommend where id=" + id + "  ";
		ResultSet rs = null;
		System.out.print(str_sql);
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(str_sql);
			if (rs.next()) {
				re.setAddtime(rs.getString("addtime"));
				re.setContent(rs.getString("content"));
				re.setId(rs.getString("id"));
				re.setIp(rs.getString("ip"));
				re.setName(rs.getString("name"));
				re.setTitle(rs.getString("title"));
				re.setUid(rs.getString("uid"));
				re.setZjid(rs.getString("zjid"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception sqlEx) {
				}
			}
		}
		return re;
	}

	public ArrayList queryMusicCommendInfo(Pager pager) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		ArrayList re = new ArrayList();
		ResultSet rs = null;

		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(pager.getQueryCase());
			while (rs.next()) {
				MusicCommendInfo m = new MusicCommendInfo();

				m.setAddtime(rs.getString("addtime"));
				m.setContent(rs.getString("content"));
				m.setId(rs.getString("id"));
				m.setIp(rs.getString("ip"));
				m.setName(rs.getString("name"));
				m.setTitle(rs.getString("title"));
				m.setUid(rs.getString("uid"));
				m.setZjid(rs.getString("zjid"));
				re.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception sqlEx) {
				}
			}
		}
		return re;
	}

	public int getTotal(Pager pager) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		int re = 0;
		ResultSet rs = null;

		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(pager.getQueryTotal());
			if (rs.next()) {
				re = rs.getInt(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception sqlEx) {
				}
			}
		}
		return re;
	}

}