articlebean.java

一些数据库的java开发编程

package jdbcbook.msgboard;

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

import jdbcbook.pub.util.*;


public class ArticleBean
{
	// 修改留言信息
	public static boolean updateArticle( Article article )
	{
		if( article==null 
			|| StringUtil.isEmptyString( article.getTitle() )
			|| StringUtil.isEmptyString( article.getContent() )
			|| article.getUserID()<=0 ) return false;

		String sql = null;
		if( article.getArticleID() <= 0 )
		{
			Timestamp ts = new Timestamp( System.currentTimeMillis() );
			int nID = 0;
			try
			{
				nID = DatabaseBean.getMaxID( "article" );
			}
			catch( Exception ex )
			{
				return false;
			}
			// 增加留言信息到数据库
			sql = "INSERT INTO article"
				+ "( articleid, title, content, pubdate, userid, hostip, farticleid ) "
				+ " VALUES( " + nID + ",'" + article.getTitle() 
				+ "','" + article.getContent() + "', to_date('" 
				+ DateUtil.formatTimestamp( ts ) + "', 'YYYY-MM-DD HH24:MI:SS'),"
			   	+ article.getUserID() + ",'" + article.getHostIP() + "'," 
				+ ( article.getFarticleID() > 0 ? article.getFarticleID() : nID ) + " )";
		}
		else
		{
			// 修改留言信息到数据库
			sql = "UPDATE article SET title='" + article.getTitle() + "', content='"
				+ article.getContent() + "', hostip='" + article.getHostIP()
				+ "' WHERE articleid = " + article.getArticleID();
		}
		
		return DatabaseBean.update( sql );
	}

	// 得到留言
	public static Vector[] getArticleList( int start, int length )
	{
		Vector[] vt = new Vector[2];
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			vt[0] = new Vector();
			String sql = "SELECT a.articleid, a.title, a.content, a.pubdate, a.userid, a.hostip, u.nickname "
				+ " FROM ( "
					+ " SELECT articleid "
					+ " FROM ( "
						+ " SELECT articleid, rownum num "
						+ " FROM ( "
							+ " SELECT articleid "
							+ "	FROM article a "
							+ " WHERE a.articleid=a.farticleid "
							+ " ORDER BY pubdate desc "
							+ " ) "
						+ " ) "
					+ " WHERE num <= " + (start + length) + " AND num > " + start
				+ " ) b, article a, userinfo u "
				+ " WHERE b.articleid = a.articleid AND a.userid = u.userid ";

			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				Article article = new Article();
				article.setArticleID( rs.getInt( 1 ) );
				article.setTitle( rs.getString( 2 ) );
				article.setContent( rs.getString( 3 ) );
				article.setPubdate( rs.getTimestamp( 4 ) );
				article.setUserID( rs.getInt( 5 ) );
				article.setHostIP( rs.getString( 6 ) );
				article.setNickname( rs.getString( 7 ) );

				vt[0].add( article );
			}
			rs.close();

			vt[1] = new Vector();
			sql = "SELECT a.articleid, a.title, a.content, a.pubdate, a.userid, a.hostip, u.nickname,a.farticleid "
				+ " FROM ( "
					+ " SELECT articleid "
					+ " FROM ( "
						+ " SELECT articleid, rownum num "
						+ " FROM ( "
							+ " SELECT articleid "
							+ "	FROM article a "
							+ " WHERE a.articleid=a.farticleid "
							+ " ORDER BY pubdate desc "
							+ " ) "
						+ " ) "
					+ " WHERE num <= " + (start + length) + " AND num > " + start
				+ " ) b, article a, userinfo u "
				+ " WHERE b.articleid = a.farticleid AND a.articleid<>b.articleid AND a.userid = u.userid ";
			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				Article article = new Article();
				article.setArticleID( rs.getInt( 1 ) );
				article.setTitle( rs.getString( 2 ) );
				article.setContent( rs.getString( 3 ) );
				article.setPubdate( rs.getTimestamp( 4 ) );
				article.setUserID( rs.getInt( 5 ) );
				article.setHostIP( rs.getString( 6 ) );
				article.setNickname( rs.getString( 7 ) );
				article.setFarticleID( rs.getInt( 8 ) );

				vt[1].add( article );
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return vt;
	}

	// 得到留言数目
	public static int getArticleCount( )
	{
		int nCount = 0;
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			String sql = " SELECT COUNT( articleid ) "
						+ "	FROM article "
						+ " WHERE articleid=farticleid ";
			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				nCount = rs.getInt( 1 );
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return nCount;
	}

	// 得到留言
	public static Article getArticle( int articleid )
	{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn 	= DatabaseBean.getConnection();
			st 		= conn.createStatement();

			String sql = "SELECT a.articleid, a.title, a.content, a.pubdate, "
				+ " a.userid, a.hostip, u.nickname, a.farticleid "
				+ "	FROM article a, userinfo u "
				+ " WHERE a.userid=u.userid AND a.articleid=" + articleid;

			rs = st.executeQuery( sql );
			while( rs.next() )
			{
				Article article = new Article();
				article.setArticleID( rs.getInt( 1 ) );
				article.setTitle( rs.getString( 2 ) );
				article.setContent( rs.getString( 3 ) );
				article.setPubdate( rs.getTimestamp( 4 ) );
				article.setUserID( rs.getInt( 5 ) );
				article.setHostIP( rs.getString( 6 ) );
				article.setNickname( rs.getString( 7 ) );
				article.setFarticleID( rs.getInt( 8 ) );
				return article;
			}
		}
		catch( SQLException ex )
		{
			ex.printStackTrace( System.err );
		}
		finally
		{
			DatabaseBean.close( rs, st, conn );
		}
		return null;
	}

	// 删除留言
	public static boolean deleteArticle( int nArticleID )
	{
		String sql = "DELETE FROM article WHERE articleid = " + nArticleID;
		return DatabaseBean.update( sql );
	}
}