questiondaoimpl.java

简易的java问答系统

package cn.netjava.dao.impl;

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

import cn.netjava.dao.QuestionDAO;
import cn.netjava.dbc.*;
import cn.netjava.pojo.*;

/**
 * 问题的具体实现类 oracle 实现
 * 
 * @author XieChengmin
 * 
 */
public class QuestionDAOImpl implements QuestionDAO {

	// 添加操作
	public boolean add(Question q) {
		boolean inserted = false;
		String sql = "insert into question values(seq_questionId.nextval, ?, ?, ?)";
		Connection con = DBConnection.getConn();
		PreparedStatement pst = null;

		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, q.getQuestionContent());
			pst.setInt(2, q.getP().getId());
			pst.setString(3, q.getOther());
			// 执行插入语句
			pst.execute();
			inserted = true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("ADD 方法: 数据连接创建失败或ＳＱＬ语句有误");
		} finally {
			try {
				if (pst != null) {
					pst.close();
				}
				if (con != null) {
					// 放回连接池
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("数据连接关闭失败");
			}
		}
		return inserted;
	}

	// 查询全部问题
	public List<Question> queryAll() {
		List<Question> all = new ArrayList<Question>();
		String sql = "select * from question ";
		Connection con = DBConnection.getConn();
		PreparedStatement pst = null;

		try {
			pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Question q = new Question();
				q.setId(rs.getInt(1));
				q.setQuestionContent(rs.getString(2));
				Person p = new PersonDAOImpl().querybyId(rs.getInt(3));
				q.setP(p);
				q.setOther(rs.getString(4));
				all.add(q);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("ADD 方法: 数据连接创建失败或ＳＱＬ语句有误");
		} finally {
			try {
				if (pst != null) {
					pst.close();
				}
				if (con != null) {
					// 放回连接池
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("数据连接关闭失败");
			}
		}
		return all;
	}

	// 按ID查询
	public Question queryById(int id) {
		Question q = new Question();
		String sql = "select * from question where id = ?";
		Connection con = DBConnection.getConn();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql);
			// 执行查询语句
			pst.setInt(1, id);

			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				q.setId(rs.getInt(1));
				q.setQuestionContent(rs.getString(2));
				Person p = new PersonDAOImpl().querybyId(rs.getInt(3));
				q.setP(p );
				q.setOther(rs.getString(4));
				
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.out.println("数据连接创建失败或查询ＳＱＬ语句有误");
		} finally {
			try {
				if (pst != null) {
					pst.close();
				}
				if (con != null) {
					// 放回连接池
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("数据连接关闭失败");
			}
		}

		return q;
	}
}