photobean.java

JDBC数据库高级编程源代码

package jdbcbook.photo;

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

import jdbcbook.pub.util.*;

/**
 * 处理Photo对象关于数据库的操作
 */
public class PhotoBean
{
	public static boolean updatePhoto( Photo photo, InputStream in, int size )
	{
		if( photo==null ) return false;

		Connection conn = null;
		PreparedStatement pst = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			if( photo.getPhotoid() <= 0 )
			{
				photo.setPhotoid( DatabaseBean.getMaxID( "photo" ) );
				// 增加照片信息到数据库
				String sql = "INSERT INTO photo"
					+ " ( photoid, title, content, pubdate, categoryid, photo, filetype, filename ) "
					+ " VALUES( ?, ?, ?, ?, ?, ?, ?, ? )"; 
				pst	= conn.prepareStatement( sql );
				pst.setInt( 1, photo.getPhotoid() );
				pst.setString( 2, photo.getTitle() );
				pst.setString( 3, photo.getContent() );
				pst.setTimestamp( 4, new Timestamp( System.currentTimeMillis() ) );
				pst.setInt( 5, photo.getCategoryid() );
				pst.setBinaryStream( 6, in, size );
				pst.setString( 7, photo.getFiletype() );
				pst.setString( 8, photo.getFilename() );
			}
			else if( in!=null && size > 0 )
			{
				// 修改照片
				String sql = "UPDATE photo SET filetype=?, filename=?, photo=? "
					+ "WHERE photoid = ? ";
				pst	= conn.prepareStatement( sql );
				pst.setString( 1, photo.getFiletype() );
				pst.setString( 2, photo.getFilename() );
				pst.setBinaryStream( 3, in, size );
				pst.setInt( 4, photo.getPhotoid() );
			}
			else
			{
				// 修改照片信息到数据库
				String sql = "UPDATE photo SET title = ?, content=?, categoryid=? "
					+ " WHERE photoid = ?";
				pst	= conn.prepareStatement( sql );
				pst.setString( 1, photo.getTitle() );
				pst.setString( 2, photo.getContent() );
				pst.setInt( 3, photo.getCategoryid() );
				pst.setInt( 4, photo.getPhotoid() );
			}

			int nResult = pst.executeUpdate( );
			return nResult == 1;
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
			return false;
		}
		finally
		{
			DatabaseBean.close( null, pst, conn );
		}
	}

	public static Vector getPhotos( int nCategoryID )
	{
		String sql = "SELECT photoid, title, content, pubdate, categoryid, filetype, filename "
			+ "FROM photo WHERE categoryid = " + nCategoryID;

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

	// 取得某个照片的详细信息
	public static Photo getPhoto( int nPhotoID )
	{
		String sql = "SELECT photoid, title, content, pubdate, categoryid, filetype, filename "
			+ "FROM photo WHERE photoid = " + nPhotoID;

		return (Photo)DatabaseBean.queryOne( sql, Photo.class );
	}

	// 输出和显示图片
	public static void outputFile( OutputStream out, int nPhotoID )
	{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();
			String sql = "SELECT photo FROM photo WHERE photoid = " + nPhotoID;
			rs = st.executeQuery( sql );
			if( rs.next() )
			{
				InputStream in = rs.getBinaryStream( 1 );
				if( in!=null )
				{
					byte[]  buf =   new byte[2048];
					while( true )
					{
					    int nRead   =   in.read( buf );
					    if( -1==nRead )
						        break;
					    out.write( buf, 0, nRead );
					}
					out.flush();
					out.close();
					in.close();
				}
			}
		}
		catch( Exception ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
	}

	// 删除照片
	public static boolean deletePhoto( int nPhotoID )
	{
		String sql = "DELETE FROM photo WHERE photoid = " + nPhotoID;
		return DatabaseBean.update( sql );
	}
}