论坛管理系统（Struts2）

package dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import org.apache.struts2.ServletActionContext; import tip.Tip; import user.User; public class NewsDaoImp implements NewsDao { String driver="org.hsqldb.jdbc.JDBCDriver"; String uri="jdbc:hsqldb:hsql://localhost:9001/"; String user="SA"; String pass=""; public List<String[]> getAllTips(String boardName) throws Exception { String sql="select * from tip where boardName='"+boardName+"'"; List<String[]> tips=new ArrayList<>(); Class.forName(driver); try(Connection con = DriverManager.getConnection(uri, user, pass); PreparedStatement prstm = con.prepareStatement(sql); ResultSet rs = prstm.executeQuery();){ while(rs.next()){ String[] r=new String[5]; r[0]=rs.getString("tipId"); r[1]=rs.getString("title"); r[2]=rs.getString("publishTime"); r[3]=rs.getString("userName"); r[4]=rs.getString("boardName"); tips.add(r); } } return tips; } @Override public ResultSet select(int id) { String condition="SELECT * From board where boardId="+ id; Statement st = null; ResultSet rs = null; try { Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } try { rs = st.executeQuery(condition); } catch (SQLException e) { e.printStackTrace(); } return rs; } @Override public boolean add(String title, String cont, String type) throws Exception { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); String date=df.format(new Date()); HttpServletRequest request=ServletActionContext.getRequest(); HttpSession session=request.getSession(); String userName=(String) session.getAttribute("name"); String condition ="INSERT INTO tip VALUES(?,?,?,?,?)"; int row=0; Tip tip=null; Class.forName(driver); try (Connection con = DriverManager.getConnection(uri, user, pass); PreparedStatement prstm = con.prepareStatement(condition);) { tip=new Tip(); prstm.setString(1,title); prstm.setString(2,cont); prstm.setString(3,date); prstm.setString(4,userName); prstm.setString(5,type); tip.setMessage("发布成功"); row=prstm.executeUpdate(); }catch(SQLException e){} return row > 0 ? true : false; } @Override public Tip show(int tipId) throws Exception { String sql="select * from tip where tipId='"+tipId+"'"; Tip tips=null; Class.forName(driver); try (Connection con = DriverManager.getConnection(uri, user, pass); PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery();) { if(rs.next()){ tips=new Tip(); tips.setTipId(rs.getInt(1)); tips.setCont(rs.getString(3)); } return tips; } } @Override public List<String[]> getTips(String key,int type) throws Exception { HttpServletRequest request=ServletActionContext.getRequest(); HttpSession session=request.getSession(); String userName=(String) session.getAttribute("name"); List<String[]> tips=new ArrayList<>(); String condition=null; if(type==1){ condition="select * from tip where title='"+key+"' and userName='"+userName+"'"; } else if(type==2){ condition="select * from tip where userName='"+userName+"'"; } Class.forName(driver); try(Connection con = DriverManager.getConnection(uri, user, pass); PreparedStatement prstm = con.prepareStatement(condition); ResultSet rs = prstm.executeQuery();){ while(rs.next()){ String[] row=new String[5]; row[0]=rs.getString("tipId"); row[1]=rs.getString("title"); row[2]=rs.getString("publishTime"); row[3]=rs.getString("userName"); row[4]=rs.getString("boardName"); tips.add(row); } } return tips; } @Override public boolean update(Tip tip) throws Exception{ Class.forName(driver); int id=tip.getTipId(); String condition="update tip set detail=? where tipId=?"; int row=0; Tip tips=new Tip(); try (Connection con = DriverManager.getConnection(uri, user, pass); PreparedStatement prstm= con.prepareStatement(condition); ) { prstm.setString(1,tip.getCont()); prstm.setInt(2,id); tip.setMessage("修改成功！"); row=prstm.executeUpdate(); return row > 0 ? true : false; } } @Override public Tip query(int tipId) throws Exception { Tip tip=null; String condition="select * from tip where tipId='"+tipId+"'"; Class.forName(driver); try(Connection con=DriverManager.getConnection(uri, user, pass); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);){ ResultSet rs=st.executeQuery(condition); if(rs.next()){ tip=new Tip(); tip.setTipId(tipId); tip.setCont(rs.getString(3)); } } return tip; } @Override public boolean delById(int tipId) throws Exception { String sql = "delete from tip where tipId=?"; int row = 0; Tip tip=new Tip(); Class.forName(driver); try (Connection con = DriverManager.getConnection(uri, user, pass); PreparedStatement pstmt = con.prepareStatement(sql);) { pstmt.setInt(1,tipId); row = pstmt.executeUpdate(); tip.setMessage("删除成功！"); } return row > 0 ? true : false; } }