categorybean.java

JDBC数据库高级编程源代码

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();

			// 判断照片分类的名称是否重复
			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" ) );
				// 增加照片分类信息到数据库
				sql = "INSERT INTO category( categoryid, name ) "
					+ " VALUES( " + category.getCategoryid() + ",'" 
					+ category.getName() + "' )";
			}
			else
			{
				// 修改照片分类信息到数据库
				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() + "%'";
		// 增加排序规则
		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 );
		}
	}
}