secondleveltitlelogicoperation.java

新闻发布系统

package logic;
import java.sql.*;
import java.util.*;
import java.text.SimpleDateFormat;
import manager.ConnectionManager;
import beans.SecondLevelTitle;

public class SecondLevelTitleLogicOperation {
	/**
	 * insert a row into SecondLevelTitle table.
	 * @param sTitle SecondLevelTitle
	 * @return int
	 */
	public int insertOneRecord(SecondLevelTitle sTitle) {
		int result = 0;
		Connection con = null;
	    PreparedStatement pstmt = null;
	
	    try {
	      java.util.Date currentTime = new java.util.Date();
	      SimpleDateFormat HMFromat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	      String strCurrentTime = HMFromat.format(currentTime);
	
	      con = ConnectionManager.getConnction();
	      String sqlstr =
	          "insert into SecondLevelTitle values(?,?,?,?,?,?)";
	      pstmt = con.prepareStatement(sqlstr);
	      pstmt.setInt(1, getNewId());
	      pstmt.setString(2, sTitle.getTitleName());
	      pstmt.setString(3, sTitle.getFilePath());
	      pstmt.setString(4, sTitle.getCreater());
	      pstmt.setString(5, strCurrentTime);
	      pstmt.setInt(6, sTitle.getParentTitle());
	  //    System.out.println(strSql);
	      result = pstmt.executeUpdate();
	    } catch (SQLException sqlE) {
	      sqlE.printStackTrace();
	    } finally {
	      ConnectionManager.closeStatement(pstmt);
	      ConnectionManager.closeConnection(con);
	    }
	    return result;
	}

	/**
	 * create a new  id of SecondLevelTitle.
	 * @return int
	 */
	private int getNewId() {
		int id = 0;
	    Connection con = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    try {
	      con = ConnectionManager.getConnction();
	      // 查询数据SQL语句
	      String sqlstr = "select max(id) from SecondLevelTitle";
	      //查询操作
	      pstmt = con.prepareStatement(sqlstr);
	      rs = pstmt.executeQuery();
	      if (rs.next()) {
	        id = rs.getInt(1);
	      }
	    } catch (Exception e) {
	      e.printStackTrace();
	    } finally {
	      ConnectionManager.closeResultSet(rs);
	      ConnectionManager.closeStatement(pstmt);
	      ConnectionManager.closeConnection(con);
	    }
	    return id + 1;
	}
	/**
	 * get second level title list by parent level title id.
	 * @param parentTitleId int
	 * @return ArrayList
	 */
	public ArrayList getAllSecondLevelTitleList(int parentTitleId){
		ArrayList list = new ArrayList();
		Connection con = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;

	    try {
	      con = ConnectionManager.getConnction();
	      // 查询数据SQL语句
	      String sqlstr = "select * from SecondLevelTitle "
	          + "where ParentTitle=(?) order by CreateTime desc";
	      //查询操作
	      pstmt = con.prepareStatement(sqlstr);
	      pstmt.setInt(1, parentTitleId);
	      rs = pstmt.executeQuery();
	      while (rs.next()) {
	        SecondLevelTitle sTitle = new SecondLevelTitle();
	        sTitle.setId(rs.getInt("id"));
	        sTitle.setTitleName(rs.getString("TitleName"));
	        sTitle.setFilePath(rs.getString("FilePath"));
	        sTitle.setCreater(rs.getString("Creater"));
	        sTitle.setCreateTime(rs.getDate("CreateTime"));
	        sTitle.setParentTitle(rs.getInt("ParentTitle"));
	        list.add(sTitle);
	      }
	    } catch (SQLException sqlE) {
	      sqlE.printStackTrace();
	    } finally {
	      ConnectionManager.closeResultSet(rs);
	      ConnectionManager.closeStatement(pstmt);
	      ConnectionManager.closeConnection(con);
	    }
		return list;
	}
}