productbean.java

一些数据库的java开发编程

package jdbcbook.ch08;

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

import jdbcbook.pub.util.*;

/**
 * 商品处理Bean
 */
public class ProductBean
{
	// 增加商品信息
	public boolean addProduct( Product product )
	{
		if( product==null 
			|| StringUtil.isEmptyString( product.getName() )
		 	|| StringUtil.isEmptyString( product.getManufacturer() )
			|| product.getCategoryID()<=0 ) return false;

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

			// 增加商品信息到数据库
			String sql = "INSERT INTO product "
				+ "( productid, name, manufacturer, description, categoryid ) "
				+ " VALUES( " + DatabaseBean.getMaxID( "product" ) + ",'" 
				+ product.getName() + "','" + product.getManufacturer()
			   	+ "','" + product.getDescription() + "', " + product.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 Vector queryProduct( Product product )
	{
		Vector vt = new Vector();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			String sql = "SELECT productid, name, manufacturer, description, categoryid FROM product ";
			// 如果用户输入了商品名称，就使用模糊匹配。否则查询所有的。
			if( product!=null && !StringUtil.isEmptyString( product.getName() ) )
			{
				sql = sql + "WHERE name like '%" + product.getName() + "%'";
			}
			if( product!=null && product.getCategoryID() > 0 )
			{
				if( sql.indexOf( "WHERE" ) > 0 )
					sql = sql + "AND categoryid = " + product.getCategoryID();
				else
					sql = sql + "WHERE categoryid = " + product.getCategoryID();
			}
			// 增加排序规则
			sql = sql + " ORDER BY name ASC";

			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				Product p = new Product();
				p.setProductID( rs.getInt( 1 ) );
				p.setName( rs.getString( 2 ) );
				p.setManufacturer( rs.getString( 3 ) );
				p.setDescription( rs.getString( 4 ) );
				p.setCategoryID( rs.getInt( 5 ) );
				vt.add( p );
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return vt;
	}

	// 查询商品信息
	public Product getProduct( Product product )
	{
		if( product.getProductID() <=0 ) return null;

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

			String sql = "SELECT productid, name, manufacturer, description, categoryid"
				+ " FROM product WHERE productid = "
				+ product.getProductID() ;

			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				p = new Product();
				p.setProductID( rs.getInt( 1 ) );
				p.setName( rs.getString( 2 ) );
				p.setManufacturer( rs.getString( 3 ) );
				p.setDescription( rs.getString( 4 ) );
				p.setCategoryID( rs.getInt( 5 ) );
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return p;
	}
	
	// 修改商品信息
	public boolean updateProduct( Product product )
	{
		if( product==null 
			|| StringUtil.isEmptyString( product.getName() ) 
			|| StringUtil.isEmptyString( product.getManufacturer() )
			|| product.getCategoryID() <=0 
			|| product.getProductID() <=0 ) return false;

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

			// 修改商品信息到数据库
			String sql = "UPDATE product SET name='" + product.getName() 
				+ "', manufacturer = '" + product.getManufacturer() 
				+ "', description = '" + product.getDescription()
				+ "', categoryid = " + product.getCategoryID()
				+ " WHERE productid = " + product.getProductID();
			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 delProduct( Product product )
	{
		if( product.getProductID() <= 0 ) return false;

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

			// 删除商品信息SQL语句
			String sql = "DELETE FROM product WHERE productid = " + product.getProductID();
			int nResult = st.executeUpdate( sql );
			return nResult == 1;
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
			return false;
		}
		finally
		{
			DatabaseBean.close( null, st, conn );
		}
	}
}