musicsingerdao.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.MusicSingerInfo;

public class MusicSingerDao {
	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 addMusicSingerInfo(MusicSingerInfo musicSingerInfo)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = " insert into musicsingnet (name,pic,areaid,sort,country,sex,word) values ('"
				+ musicSingerInfo.getName()
				+ "','"
				+ musicSingerInfo.getPic()
				+ "',"
				+ musicSingerInfo.getAreaid()
				+ ","
				+ musicSingerInfo.getSort()
				+ ",'"
				+ musicSingerInfo.getCountry()
				+ "','"
				+ musicSingerInfo.getSex()
				+ "','"
				+ musicSingerInfo.getWord()
				+ "')";
		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 editMusicSingerInfo(MusicSingerInfo musicSingerInfo)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = " update musicsingnet set name='"
				+ musicSingerInfo.getName() + "',pic='"
				+ musicSingerInfo.getPic() + "',areaid="
				+ musicSingerInfo.getAreaid() + ",sort="
				+ musicSingerInfo.getSort() + ",country='"
				+ musicSingerInfo.getCountry() + "',sex='"
				+ musicSingerInfo.getSex() + "',word='"
				+ musicSingerInfo.getWord() + "') where id="
				+ musicSingerInfo.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 deleteMusicSingerInfo(String id) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean re = false;
		String str_sql = " delete from musicsinger 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 MusicSingerInfo queryMusicSingerInfoById(String id) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		MusicSingerInfo re = new MusicSingerInfo();
		String str_sql = " select * from musicsinger 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.setAreaid(rs.getString("areaid"));
				re.setCountry(rs.getString("country"));
				re.setId(rs.getString("id"));
				re.setName(rs.getString("name"));
				re.setPic(rs.getString("pic"));
				re.setSex(rs.getString("sex"));
				re.setSort(rs.getString("sort"));
				re.setWord(rs.getString("word"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception sqlEx) {
				}
			}
		}
		return re;
	}
	public MusicSingerInfo queryMusicSingerInfoByName(String name) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		MusicSingerInfo re = new MusicSingerInfo();
		String str_sql = " select * from musicsinger where name=" + name + "  ";
		ResultSet rs = null;
		System.out.print(str_sql);
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(str_sql);
			if (rs.next()) {
				re.setAreaid(rs.getString("areaid"));
				re.setCountry(rs.getString("country"));
				re.setId(rs.getString("id"));
				re.setName(rs.getString("name"));
				re.setPic(rs.getString("pic"));
				re.setSex(rs.getString("sex"));
				re.setSort(rs.getString("sort"));
				re.setWord(rs.getString("word"));

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

	public ArrayList queryMusicSingerInfo() throws Exception {
		Connection conn = null;
		Statement stmt = null;
		ArrayList re = new ArrayList();
		String str_sql = " select * from musicarea";
		ResultSet rs = null;
		System.out.print(str_sql);
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(str_sql);
			while (rs.next()) {
				MusicSingerInfo m = new MusicSingerInfo();
				m.setAreaid(rs.getString("areaid"));
				m.setCountry(rs.getString("country"));
				m.setId(rs.getString("id"));
				m.setName(rs.getString("name"));
				m.setPic(rs.getString("pic"));
				m.setSex(rs.getString("sex"));
				m.setSort(rs.getString("sort"));
				m.setWord(rs.getString("word"));

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

}