notedaoimpl.java

留言管理系统,用JAVA JSP + MVC + DAO完成

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.shx.note.dao.impl;

import com.shx.note.DBConn.DBConnection;
import com.shx.note.dao.NoteDAO;
import com.shx.note.vo.Note;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Administrator
 */
public class NoteDAOImpl implements NoteDAO {

    //增加操作
    public void insert(Note note) throws Exception {
        String sql = "INSERT INTO note(title,author,content) VALUES(?,?,?)";
        PreparedStatement pstmt = null;
        //实例化数据库连接对象
        DBConnection dbconn = new DBConnection();
        Connection conn = dbconn.getConnection();

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, note.getTitle());
            pstmt.setString(2, note.getAuthor());
            pstmt.setString(3, note.getContent());
            pstmt.executeUpdate();
            //关闭连接
            pstmt.close();
        } catch (SQLException sQLException) {
            throw new SQLException("操作中出现错误！！！");
        } finally {
            //关闭数据库连接对象
            conn.close();
        }
    }

    //修改操作
    public void update(Note note) throws Exception {
        String sql = "UPDATE note SET title = ?,author = ?,content = ? WHERE n_id = ?";
        PreparedStatement pstmt = null;
        //实例化数据库连接对象
        DBConnection dbconn = new DBConnection();
        Connection conn = dbconn.getConnection();

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, note.getTitle());
            pstmt.setString(2, note.getAuthor());
            pstmt.setString(3, note.getContent());
            pstmt.setInt(4, note.getN_id());
            pstmt.executeUpdate();
            //关闭连接
            pstmt.close();
        } catch (SQLException sQLException) {
            throw new SQLException("操作中出现错误！！！");
        } finally {
            //关闭数据库连接对象
            conn.close();
        }
    }

    //删除操作
    public void delete(int n_id) throws Exception {
        String sql = "DELETE FROM note WHERE n_id = ?";
        PreparedStatement pstmt = null;
        //实例化数据库连接对象
        DBConnection dbconn = new DBConnection();
        Connection conn = dbconn.getConnection();

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, n_id);
            pstmt.executeUpdate();
            //关闭连接
            pstmt.close();
        } catch (SQLException sQLException) {
            throw new SQLException("操作中出现错误！！！");
        } finally {
            //关闭数据库连接对象
            conn.close();
        }
    }

    // 按ID查询，主要为更新使用
    public Note quaryById(int n_id) throws Exception {
        Note note = null;

        String sql = "SELECT n_id,title,author,content FROM note WHERE n_id = ?";
        PreparedStatement pstmt = null;
        //实例化数据库连接对象
        DBConnection dbconn = new DBConnection();
        Connection conn = dbconn.getConnection();

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, n_id);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                note = new Note();
                note.setN_id(rs.getInt(1));
                note.setTitle(rs.getString(2));
                note.setAuthor(rs.getString(3));
                note.setContent(rs.getString(4));
            }
            //关闭连接
            rs.close();
            pstmt.close();
        } catch (SQLException sQLException) {
            throw new SQLException("操作中出现错误！！！");
        } finally {
            //关闭数据库连接对象
            conn.close();
        }
        return note;
    }

    // 查询全部
    public List quaryAll() throws Exception {
        List all = new ArrayList();

        String sql = "SELECT n_id,title,author,content FROM note";
        PreparedStatement pstmt = null;
        //实例化数据库连接对象
        DBConnection dbconn = new DBConnection();
        Connection conn = dbconn.getConnection();

        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Note note = new Note();
                note.setN_id(rs.getInt(1));
                note.setTitle(rs.getString(2));
                note.setAuthor(rs.getString(3));
                note.setContent(rs.getString(4));
                all.add(note);
            }
            //关闭连接
            rs.close();
            pstmt.close();
        } catch (SQLException sQLException) {
            throw new SQLException("操作中出现错误！！！");
        } finally {
            //关闭数据库连接对象
            conn.close();
        }
        return all;
    }

    // 模糊查询
    public List quaryByLike(String cond) throws Exception {
        List all = new ArrayList();

        String sql = "SELECT n_id,title,author,content FROM note WHERE title LIKE ? or author LIKE ? or content LIKE ?";
        PreparedStatement pstmt = null;
        //实例化数据库连接对象
        DBConnection dbconn = new DBConnection();
        Connection conn = dbconn.getConnection();

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%"+cond+"%");
            pstmt.setString(2, "%"+cond+"%");
            pstmt.setString(3, "%"+cond+"%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Note note = new Note();
                note.setN_id(rs.getInt(1));
                note.setTitle(rs.getString(2));
                note.setAuthor(rs.getString(3));
                note.setContent(rs.getString(4));
                all.add(note);
            }
            //关闭连接
            rs.close();
            pstmt.close();
        } catch (SQLException sQLException) {
            throw new SQLException("操作中出现错误！！！");
        } finally {
            //关闭数据库连接对象
            conn.close();
        }
        return all;
    }
}