categorybean.java

一些数据库的java开发编程

package jdbcbook.ch08;

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

import jdbcbook.pub.util.*;

/**
 * 商品分类处理Bean
 */
public class CategoryBean
{
	// 增加商品分类信息
	public boolean addCategory( 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 = true;
			}
			rs.close();
			if( exists ) return false;

			// 增加商品分类信息到数据库
			sql = "INSERT INTO category( categoryid, name ) "
				+ " VALUES( " + DatabaseBean.getMaxID( "category" ) + ",'" 
				+ category.getName() + "' )";
			int nResult = st.executeUpdate( sql );
			return nResult == 1;
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
			return false;
		}
		finally
		{
			DatabaseBean.close( null, st, conn );
		}
	}

	// 查询商品分类信息
	public Vector queryCategory( Category category )
	{
		Vector vt = new Vector();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			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";

			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				Category c = new Category();
				c.setCategoryID( rs.getInt( 1 ) );
				c.setName( rs.getString( 2 ) );
				vt.add( c );
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return vt;
	}

	// 查询商品分类信息
	public Category getCategory( Category category )
	{
		if( category.getCategoryID() <= 0 ) return null;

		Category cat = null;
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

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

			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				cat = new Category();
				cat.setCategoryID( rs.getInt( 1 ) );
				cat.setName( rs.getString( 2 ) );
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return cat;
	}
	
	// 修改商品分类信息
	public boolean updateCategory( Category category )
	{
		if( category==null 
			|| StringUtil.isEmptyString( category.getName() ) 
			|| category.getCategoryID() <= 0 ) 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 = ( category.getCategoryID() != rs.getInt( 1 ) );
			}
			rs.close();
			if( exists ) return false;

			// 修改商品分类信息到数据库
			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 boolean delCategory( 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 );
		}
	}
}