cartdao.java

自己写的一个网络日志(BLOG)系统

package blog;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
 * 
 * <p>Title: CArtDAO</p>
 * <p>Description:文章管理类 </p>
 * <p>Copyright: copyright (c) 2005-2005</p>
 * <p>Company: </p>
 * @author Administrator
 * @version 1.0.00_01
 */
public class CArtDAO {
	private Connection conn = null;
	private Statement stmt = null;
	private CallableStatement cstmt = null;
	private ResultSet rs = null;
	private static CArtDAO  artDao= null;
	/**
	 * 
	 * <p>Description: </p>
	 * @return
	 */
	public static CArtDAO newInstance(){
		if(artDao == null){
			artDao = new CArtDAO();
		}
		return artDao;
	}
	
	private CArtDAO(){
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=blogdata",
	        		"sa",
					"sa");
		} catch (Exception e) {
			System.err.println("blog.CArtDAO.CArtDAO()"+e.getMessage());
		}
	}
	
	public int executeInsert(CArticle article){
		int i = 0;
		try{
		String sql ="insert Tarticles values('"+article.getArtTitle()+"',"+
					article.getCataID()+","+article.getUserID()+","+
					article.getPostYear()+","+article.getPostMonth()+","+
					article.getPostDay()+",'"+article.getPostTime()+"','"+
					CStringEncoder.htmlTextEncoder(article.getArtContent())+"','"
					+CStringEncoder.htmlTextEncoder(article.getArtIntro())+"')";
	
			stmt = conn.createStatement();
			i = stmt.executeUpdate(sql);
		}catch(Exception e ){
			System.err.println("blog.CArtDAO.executeInsert()"+e.getMessage());
		}
		return i ;
	}
	
	public ArrayList executeQuery(){
		ArrayList artList = null;
		String sql = "select * from Tarticles order by artID desc";
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				artList = new ArrayList();
				do{
					CArticle article = new CArticle();
					article.setArtID(rs.getInt(1));
					article.setArtTitle(rs.getString(2));
					article.setCataID(rs.getInt(3));
					article.setUserID(rs.getInt(4));
					article.setPostYear(rs.getInt(5));
					article.setPostMonth(rs.getInt(6));
					article.setPostDay(rs.getInt(7));
					article.setPostTime(rs.getString(8));
					article.setArtContent(rs.getString(9));
					article.setArtIntro(rs.getString(10));
					
					artList.add(article);
				}while(rs.next());
			}
		}catch(Exception e ){
			System.err.println("blog.CCataDAO.executeQuery()"+e.getMessage());
		}
		return artList;
	}
	
	public CArticle queryByID(int id){
		CArticle article = null;
		String sql = "select * from Tarticles where artID="+id;
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				do{
					article = new CArticle();
					article.setArtID(rs.getInt(1));
					article.setArtTitle(rs.getString(2));
					article.setCataID(rs.getInt(3));
					article.setUserID(rs.getInt(4));
					article.setPostYear(rs.getInt(5));
					article.setPostMonth(rs.getInt(6));
					article.setPostDay(rs.getInt(7));
					article.setPostTime(rs.getString(8));
					article.setArtContent(rs.getString(9));
					article.setArtIntro(rs.getString(10));
				}while(rs.next());
			}
		}catch(Exception e ){
			System.err.println("blog.CCataDAO.queryByID()"+e.getMessage());
		}
		return article;
	}
	
	public ArrayList executeBySql(String sql){
		ArrayList artList = null;
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				artList = new ArrayList();
				do{
					CArticle article = new CArticle();
					article.setArtID(rs.getInt(1));
					article.setArtTitle(rs.getString(2));
					article.setCataID(rs.getInt(3));
					article.setUserID(rs.getInt(4));
					article.setPostYear(rs.getInt(5));
					article.setPostMonth(rs.getInt(6));
					article.setPostDay(rs.getInt(7));
					article.setPostTime(rs.getString(8));
					article.setArtContent(rs.getString(9));
					article.setArtIntro(rs.getString(10));
					
					artList.add(article);
				}while(rs.next());
			}
		}catch(Exception e ){
			System.err.println("blog.CCataDAO.executeBySql()"+e.getMessage());
		}
		return artList;
	}
	
	public ArrayList queryByDay(int year,int month,int day){
		ArrayList artList = null;
		String sql = "select artID from Tarticles where postYear="+year+" and postMonth="+month+"and postDay="+day;
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				artList = new ArrayList();
				do{
					CArticle article = new CArticle();
					article.setArtID(rs.getInt(1));
					article.setArtTitle(rs.getString(2));
					article.setCataID(rs.getInt(3));
					article.setUserID(rs.getInt(4));
					article.setPostYear(rs.getInt(5));
					article.setPostMonth(rs.getInt(6));
					article.setPostDay(rs.getInt(7));
					article.setPostTime(rs.getString(8));
					article.setArtContent(rs.getString(9));
					article.setArtIntro(rs.getString(10));
					
					artList.add(article);
				}while(rs.next());
			}
		}catch(Exception e ){
			System.err.println("blog.CCataDAO.executeQuery()"+e.getMessage());
		}
		return artList;
	}
	
	public int delArt(int artID){
		int i = 0;
		String sql = "delete Tarticles where artID="+artID;
		try {
			stmt = conn.createStatement();
			i = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			System.err.println("blog.CCataDAO.delArt()"+e.getMessage());
		}
		return i;
	}
	
	public int executeUpdate(CArticle article){
		int i = 0;
		String sql = "update Tarticles "+
		"set artTitle='"+article.getArtTitle()+"'"+
			",cataID="+article.getCataID()+
			",userID="+article.getUserID()+
			",postYear="+article.getPostYear()+
			",postMonth="+article.getPostMonth()+
			",postDay="+article.getPostDay()+
			",postTime='"+article.getPostTime()+"'"+
			",artContent='"+article.getArtContent()+"'"+
			",artIntro='"+article.getArtIntro()+"' "+
			" where artID="+article.getArtID();
		
		try {
			stmt = conn.createStatement();
			i = stmt.executeUpdate(sql);
		} catch (Exception e) {
			System.err.println("blog.CCataDAO.executeUpdate()"+e.getMessage());
		}
		return i;
	}

}