secondleveltitledbopreation.java

JSP初学者

package xinwen;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
public class SecondLevelTitleDbOpreation {
	public List getSecondLevelTitleListByFirstLevelTitle(int parentTitleId) {
		List list = new ArrayList();
		Connection dbConnection = null;
		PreparedStatement pStatement = null;
		ResultSet res = null;
		try {
			dbConnection = ConnectionManager.getConnection();
			String strSql = "select * from SecondLevelTitle "
					+ " where ParentTitle=(?) order by CreateTime desc";
			if (dbConnection != null) {
				System.out.println(dbConnection != null);
			}
			pStatement = dbConnection.prepareStatement(strSql);
			pStatement.setInt(1, parentTitleId);
			res = pStatement.executeQuery();
			while (res.next()) {
				SecondLevelTitle sTitle = new SecondLevelTitle();
				sTitle.setId(res.getInt("id"));
				sTitle.setTitleName(res.getString("TitleName"));
				sTitle.setFilePath(res.getString("FilePath"));
				sTitle.setCreator(res.getString("Creator"));
				sTitle.setCreateTime(res.getDate("CreateTime"));
				sTitle.setParentTitleId(res.getInt("ParentTitle"));
				list.add(sTitle);
			}
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		} finally {
			ConnectionManager.closeResultSet(res);
			ConnectionManager.closeStatement(pStatement);
			ConnectionManager.closeConnection(dbConnection);
		}
		return list;
	}

	/**
	 * 
	 */
	public int insertOneRecord(SecondLevelTitle sTitle) {
		int result = 0;
		Connection con = null;
		PreparedStatement pStatement = null;
		try {
			Date currentTime = new Date();
			SimpleDateFormat HMFromat = new SimpleDateFormat(
					"yyyy-MM-dd hh:mm:ss");
			String strCurrentTime = HMFromat.format(currentTime);

			con = ConnectionManager.getConnection();
			String strSql = "insert into SecondLevelTitle(Id,TitleName,FilePath,Creator,"
					+ "CreateTime,ParentTitle) values(?,?,?,?,?,?)";
			pStatement = con.prepareStatement(strSql);
			pStatement.setInt(1, getNewId());
			pStatement.setString(2, sTitle.getTitleName());
			pStatement.setString(3, sTitle.getFilePath());
			pStatement.setString(4, sTitle.getCreator());
			pStatement.setString(5, strCurrentTime);
			pStatement.setInt(6, sTitle.getParentTitleId());
			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 SecondLevelTitle ";
			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 id
	 */
	public void delSecondTitle(int id){
		
		Connection dbConnection = null;
		PreparedStatement pStatementS = null;
		PreparedStatement pStatementF = null;
		
		try {
			dbConnection = ConnectionManager.getConnection();
			String delSecondSql = "delete from SecondLevelTitle where Id=?";
			pStatementS = dbConnection.prepareStatement(delSecondSql);
			pStatementS.setInt(1, id);
			pStatementS.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}