petinfodao.java

网上电子宠物系统

package com.zc.PetProject.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.zc.PetProject.DAO.Base.BaseDAO;
import com.zc.PetProject.VO.PetInfoVO;
import com.zc.struts.form.PetActionForm;

public class PetInfoDAO extends BaseDAO {

	Connection con = null;

	Statement st = null;

	ResultSet re = null;

	// 领养宠物的方法
	public boolean addPetInfo(PetActionForm pet) {

		String sql = "insert into petinfo values('" + pet.getPet_name() + "','"
				+ pet.getPet_sex() + "'," + pet.getPet_strength() + ","
				+ pet.getPet_cute() + "," + pet.getPet_love() + ",'"
				+ pet.getPet_intro() + "','" + pet.getPet_owner_name() + "','"
				+ pet.getPet_owner_email() + "','" + pet.getPet_password()
				+ "','" + pet.getPet_pic() + "','" + pet.getPet_type() + "')";
		try {
			con = this.getConnection();
			st = con.createStatement();
			if (st.executeUpdate(sql) > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.cloceConnection(con);
		}
		return false;
	}

	// 宠物登录的方法
	public PetInfoVO checkPet(String petname, String password) {
		String sql = "select * from PetInfo where pet_name='" + petname
				+ "' and pet_password='" + password + "'";
		int i = 1;
		con = this.getConnection();
		try {
			st = con.createStatement();
			re = st.executeQuery(sql);
			if (re.next()) {
				PetInfoVO pet = new PetInfoVO();
				pet.setPet_id(re.getInt(i++));
				pet.setPet_name(re.getString(i++));
				pet.setPet_sex(re.getString(i++));
				pet.setPet_strength(re.getInt(i++));
				pet.setPet_cute(re.getInt(i++));
				pet.setPet_love(re.getInt(i++));
				pet.setPet_intro(re.getString(i++));
				pet.setPet_owner_name(re.getString(i++));
				pet.setPet_owner_email(re.getString(i++));
				pet.setPet_password(re.getString(i++));
				pet.setPet_pic(re.getString(i++));
				pet.setPet_type(re.getString(i++));
				return pet;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			cloceConnection(con);
		}
		return null;
	}

	// 根据宠物ID来获取一个宠物的实例
	public PetInfoVO getPetInfoById(int id) {
		String sql = "select * from PetInfo where pet_id=" + id;
		int i = 1;
		con = this.getConnection();
		try {
			st = con.createStatement();
			re = st.executeQuery(sql);
			if (re.next()) {
				PetInfoVO pet = new PetInfoVO();
				pet.setPet_id(re.getInt(i++));
				pet.setPet_name(re.getString(i++));
				pet.setPet_sex(re.getString(i++));
				pet.setPet_strength(re.getInt(i++));
				pet.setPet_cute(re.getInt(i++));
				pet.setPet_love(re.getInt(i++));
				pet.setPet_intro(re.getString(i++));
				pet.setPet_owner_name(re.getString(i++));
				pet.setPet_owner_email(re.getString(i++));
				pet.setPet_password(re.getString(i++));
				pet.setPet_pic(re.getString(i++));
				pet.setPet_type(re.getString(i++));
				return pet;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			cloceConnection(con);
		}
		return null;
	}

	// 修改宠物的方法
	public boolean updatePetInfo(PetActionForm pet) {

		String sql = "update PetInfo set pet_name='" + pet.getPet_name()
				+ "',pet_sex='" + pet.getPet_sex() + "',pet_intro='"
				+ pet.getPet_intro() + "',pet_owner_name='"
				+ pet.getPet_owner_name() + "',pet_owner_email='"
				+ pet.getPet_owner_email() + "',pet_password='"
				+ pet.getPet_password() + "' where pet_id=" + pet.getPet_id();
		try {
			con = this.getConnection();
			st = con.createStatement();
			if (st.executeUpdate(sql) > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.cloceConnection(con);
		}
		return false;
	}

	
	//获取日记标题的方法
	public List findPetByTableColunm(String tableColunm) {
		List list = new ArrayList();
		PetInfoVO pet = null;
		if (tableColunm.equals("all")) {
			String sql = "select top 10 sum(pet_love+pet_cute+pet_strength) as 'all', pet_name, pet_id,pet_type, pet_owner_name from petinfo group by pet_name, pet_id, pet_type, pet_owner_name order by sum(pet_love+pet_cute+pet_strength) desc";
			try {
				con = this.getConnection();
				st = con.createStatement();
				re = st.executeQuery(sql);
				while (re.next()) {
					pet = new PetInfoVO();
					pet.setAll(re.getInt("all"));
					pet.setPet_id(re.getInt("pet_id"));
					pet.setPet_name(re.getString("pet_name"));
					pet.setPet_type(re.getString("pet_type"));
					pet.setPet_owner_name(re.getString("pet_owner_name"));
					list.add(pet);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				this.cloceConnection(con);
			}
		} else {
			String sql = "select top 10 pet_id," + tableColunm
					+ ", pet_name from petinfo order by " + tableColunm
					+ " desc";
			try {
				con=this.getConnection();
				Statement stat = con.createStatement();
				re = stat.executeQuery(sql);

				while (re.next()) {
					pet = new PetInfoVO();
					pet.setPet_id(re.getInt("pet_id"));
					pet.setPet_name(re.getString("pet_name"));
					if (tableColunm.equals("pet_love")) {
						pet.setPet_love(re.getInt(tableColunm));
					} else if (tableColunm.equals("pet_strength")) {
						pet.setPet_strength(re.getInt(tableColunm));
					} else if (tableColunm.equals("pet_cute")) {
						pet.setPet_cute(re.getInt(tableColunm));
					}
					list.add(pet);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				this.cloceConnection(con);
			}
		}
		return list;
	}
	
	public PetInfoVO findPetById(int petid) {
		String sql = "select * from PetInfo where pet_id="+petid;
		int i = 1;
		con = this.getConnection();
		try {
			st = con.createStatement();
			re = st.executeQuery(sql);
			if (re.next()) {
				PetInfoVO pet = new PetInfoVO();
				pet.setPet_id(re.getInt(i++));
				pet.setPet_name(re.getString(i++));
				pet.setPet_sex(re.getString(i++));
				pet.setPet_strength(re.getInt(i++));
				pet.setPet_cute(re.getInt(i++));
				pet.setPet_love(re.getInt(i++));
				pet.setPet_intro(re.getString(i++));
				pet.setPet_owner_name(re.getString(i++));
				pet.setPet_owner_email(re.getString(i++));
				pet.setPet_password(re.getString(i++));
				pet.setPet_pic(re.getString(i++));
				pet.setPet_type(re.getString(i++));
				return pet;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.cloceConnection(con);
		}
		return null;
	}
	

}