烟草纪律检查知识库系统

package com.rm.dao; import com.rm.pojo.RectQuestion; import com.rm.utlis.C3P0Utils; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.concurrent.atomic.AtomicInteger; public class RectQuestionDaoImpl implements RectQuestionDao{ @Override public int delete(int id) { DataSource dataSource = C3P0Utils.getDataSource(); Connection connection = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String sql = "delete from basic.rect_question where id = ?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, id); int i = preparedStatement.executeUpdate(); preparedStatement.close(); connection.close(); return i; } catch (SQLException e) { throw new RuntimeException(e); } } @Override public int update(RectQuestion rectQuestion) { DataSource dataSource = C3P0Utils.getDataSource(); Connection connection = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String sql = "update basic.rect_question set rq_content = ? ,basic_id = ? ,qt_id = ? ,qs_id = ? ,dept_id = ? where id = ?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, rectQuestion.getRq_content()); preparedStatement.setInt(2, rectQuestion.getBasic_id()); preparedStatement.setInt(3, rectQuestion.getQt_id()); preparedStatement.setInt(4, rectQuestion.getQs_id()); preparedStatement.setInt(5, rectQuestion.getDept_id()); preparedStatement.setInt(6, rectQuestion.getId()); int i = preparedStatement.executeUpdate(); preparedStatement.close(); connection.close(); return i; } catch (SQLException e) { throw new RuntimeException(e); } } @Override public int insert(RectQuestion rectQuestion) { DataSource dataSource = C3P0Utils.getDataSource(); Connection connection = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String sql = "insert into basic.rect_question(rq_content, basic_id, qt_id, qs_id, dept_id) values (?, ?, ?, ?, ?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, rectQuestion.getRq_content()); preparedStatement.setInt(2, rectQuestion.getBasic_id()); preparedStatement.setInt(3, rectQuestion.getQt_id()); preparedStatement.setInt(4, rectQuestion.getQs_id()); preparedStatement.setInt(5, rectQuestion.getDept_id()); int i = preparedStatement.executeUpdate(); preparedStatement.close(); connection.close(); return i; } catch (SQLException e) { throw new RuntimeException(e); } } @Override public List<RectQuestion> selectAll() { DataSource dataSource = C3P0Utils.getDataSource(); Connection connection = null; ResultSet rs = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String sql = "select * from basic.rect_question"; preparedStatement = connection.prepareStatement(sql); rs = preparedStatement.executeQuery(); List<RectQuestion> rectQuestions = new ArrayList<>(); while (rs.next()) { RectQuestion rectQuestion = new RectQuestion(); rectQuestion.setId(rs.getInt("id")); rectQuestion.setRq_content(rs.getString("rq_content")); rectQuestion.setBasic_id(rs.getInt("basic_id")); rectQuestion.setQt_id(rs.getInt("qt_id")); rectQuestion.setQs_id(rs.getInt("qs_id")); rectQuestion.setDept_id(rs.getInt("dept_id")); rectQuestions.add(rectQuestion); } rs.close(); preparedStatement.close(); connection.close(); return rectQuestions; } catch (SQLException e) { throw new RuntimeException(e); } } @Override public List<RectQuestion> queryRectQuestion(String rq_content, int basic_id, int qt_id, int qs_id) { DataSource dataSource = C3P0Utils.getDataSource(); Connection connection = null; ResultSet rs = null; PreparedStatement preparedStatement = null; try { connection = dataSource.getConnection(); String sql = "SELECT * FROM basic.rect_question WHERE 1=1"; if (rq_content!= null &&!rq_content.equals("")) { sql += " and rq_content like '%" + rq_content + "%'"; } if (basic_id!= 0) { sql += " and basic_id = " + basic_id; } if (qt_id!= 0) { sql += " and qt_id = " + qt_id; } if (qs_id!= 0) { sql += " and qs_id = " + qs_id; } preparedStatement = connection.prepareStatement(sql); rs = preparedStatement.executeQuery(); List<RectQuestion> rectQuestions = new ArrayList<>(); while (rs.next()) { RectQuestion rectQuestion = new RectQuestion(); rectQuestion.setId(rs.getInt("id")); rectQuestion.setRq_content(rs.getString("rq_content")); rectQuestion.setBasic_id(rs.getInt("basic_id")); rectQuestion.setQt_id(rs.getInt("qt_id")); rectQuestion.setQs_id(rs.getInt("qs_id")); rectQuestion.setDept_id(rs.getInt("dept_id")); rectQuestions.add(rectQuestion); } rs.close(); preparedStatement.close(); connection.close(); return rectQuestions; } catch (SQLException e) { throw new RuntimeException(e); } } }