package jdbcbook.photo;

import java.sql.*;
import java.util.*;

import jdbcbook.pub.util.*;

/**
 * 照片分類處理Bean
 */
public class CategoryBean
{
	// 維護照片分類信息
	public static boolean updateCategory( Category category )
	{
		if( category==null 
			|| StringUtil.isEmptyString( category.getName() ) ) return false;

		Connection conn = null;
		Statement st = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			// 判斷照片分類的名稱是否重復(fù)
			boolean exists = false;
			String sql = "SELECT categoryid FROM category WHERE name='" + category.getName() + "'";
			ResultSet rs = st.executeQuery( sql );
			while( rs.next() )
			{
				exists = ( rs.getInt( 1 ) != category.getCategoryid() ) ;
			}
			rs.close();
			if( exists ) return false;

			if( category.getCategoryid() <= 0 )
			{
				category.setCategoryid( DatabaseBean.getMaxID( "category" ) );
				// 增加照片分類信息到數(shù)據(jù)庫
				sql = "INSERT INTO category( categoryid, name ) "
					+ " VALUES( " + category.getCategoryid() + ",'" 
					+ category.getName() + "' )";
			}
			else
			{
				// 修改照片分類信息到數(shù)據(jù)庫
				sql = "UPDATE category SET name='" + category.getName() 
					+ "' WHERE categoryid = " + category.getCategoryid();
			}
			int nResult = st.executeUpdate( sql );
			return nResult == 1;
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
			return false;
		}
		finally
		{
			DatabaseBean.close( null, st, conn );
		}
	}

	// 查詢照片分類信息
	public static Vector queryCategory( Category category )
	{
		String sql = "SELECT categoryid, name FROM category ";
		// 如果用戶輸入了分類名稱，就使用模糊匹配。否則查詢所有的。
		if( category!=null && !StringUtil.isEmptyString( category.getName() ) )
			sql = sql + "WHERE name like %'" + category.getName() + "%'";
		// 增加排序規(guī)則
		sql = sql + " ORDER BY name ASC";

		return DatabaseBean.query( sql, Category.class );
	}

	// 查詢照片分類信息
	public static Category getCategory( Category category )
	{
		if( category.getCategoryid() <= 0 ) return null;

		String sql = "SELECT categoryid, name FROM category WHERE categoryid = "
			+ category.getCategoryid() ;

		return (Category)DatabaseBean.queryOne( sql, Category.class );
	}
	
	// 刪除照片分類信息
	public static boolean deleteCategory( Category category )
	{
		if( category.getCategoryid() <= 0 ) return false;

		Connection conn = null;
		Statement st = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			// 刪除照片分類信息SQL語句
			String sql = "DELETE FROM category WHERE categoryid = " + category.getCategoryid();
			int nResult = st.executeUpdate( sql );
			return nResult == 1;
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
			return false;
		}
		finally
		{
			DatabaseBean.close( null, st, conn );
		}
	}
}