firstleveltitledbopreation.java

JSP初学者

package xinwen;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class FirstLevelTitleDbOpreation {
	    
	public  List getAllFirstLevelTitleList() {
		ArrayList list = new ArrayList();
		Connection dbConnection = null;
		PreparedStatement pStatement = null;
		ResultSet res = null;
		try {
			dbConnection = ConnectionManager.getConnection();
			String strSql = "select * from FirstLevelTitle order by CreateTime desc";
			pStatement = dbConnection.prepareStatement(strSql);
			res = pStatement.executeQuery();
			while (res.next()) {
				int id = res.getInt("id");
				String title = res.getString("TitleName");
				String creater = res.getString("Creator");
				Date time = res.getDate("CreateTime");
				FirstLevelTitle fTitle = new FirstLevelTitle(id, title,creater, time);
				list.add(fTitle);
			}
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		} finally {
			ConnectionManager.closeResultSet(res);
			ConnectionManager.closeStatement(pStatement);
			ConnectionManager.closeConnection(dbConnection);
		}
		return list;
	}
	 /**
	   *
	   */
	public int insertOneRecord(FirstLevelTitle fTitle) {
		int result = 0;
		Connection con = null;
		PreparedStatement pStatement = null;

		try {
			SimpleDateFormat HMFromat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			String strCurrentTime = HMFromat.format( new Date());
			con = ConnectionManager.getConnection();
			String strSql =
				"insert into FirstLevelTitle values(?,?,?,?)";
			pStatement = con.prepareStatement(strSql);
			pStatement.setInt(1, getNewId());
			pStatement.setString(2, fTitle.getTitleName());
			pStatement.setString(3, fTitle.getCreator());
			pStatement.setString(4, strCurrentTime);
			result = pStatement.executeUpdate();
		}  catch (SQLException sqlE) {
			sqlE.printStackTrace();
		}  finally {
			ConnectionManager.closeStatement(pStatement);
			ConnectionManager.closeConnection(con);
		}
		return result;
	}
	/**
	
	 */
	private int getNewId() {
		int id = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet resSet = null;
	    try {
	      con = ConnectionManager.getConnection();
	      String sqlStr = "select max(id) from FirstLevelTitle ";
	      pstmt = con.prepareStatement(sqlStr);
	      resSet = pstmt.executeQuery();
	      if (resSet.next()) {
	        id = resSet.getInt(1);
	      }
	    }catch (Exception e) {
	      e.printStackTrace();
	    }finally {
	      ConnectionManager.closeResultSet(resSet);
	      ConnectionManager.closeStatement(pstmt);
	      ConnectionManager.closeConnection(con);
	    }
	    return id + 1;
	  }
	/**
	 * 删除一级标题 同时也删除 二级标题中对应的内容
	 * @param firstTitleId
	 */
	public void deleteFirstLevelTitle(int firstTitleId ){
		Connection dbConnection = null;
		PreparedStatement pStatementS = null;
		PreparedStatement pStatementF = null;
		//ResultSet res = null;
		try {
			dbConnection = ConnectionManager.getConnection();
			String delSecondSql = "delete from SecondLevelTitle where ParentTitle=?";
			pStatementS = dbConnection.prepareStatement(delSecondSql);
			pStatementS.setInt(1, firstTitleId);
			pStatementS.executeUpdate();
			String delFirstSql="delete from FirstLevelTitle where id=?";
			pStatementF = dbConnection.prepareStatement(delFirstSql);
			pStatementF.setInt(1, firstTitleId);
			pStatementF.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}