【新手入门】JDBC+Servlet+jsp【新闻系统的增删改查】:持续学习,持续优化,争取做一个真正的新闻系统出来!
新闻的增删改查
学习了一些博客知识【结尾会贴上,大家可以参考】在借鉴的基础上又进行了代码的优化,从而实现了CRUD,下面分享我的代码过程:
包结构,是为了方便管理,所以我们要这样进行设计:
在数据库进行表结构的插入和设计:(目前小辣鸡制作了新闻的crud)
接着我们进行类的实现,即完善domain类
public class NoteEntity { private int id; private String type; private String text; public NoteEntity() { } public NoteEntity(int id, String type, String text) { this.id = id; this.type = type; this.text = text; } @Override public String toString() { return "NoteEntity{" + "id='" + id + '\'' + ", type='" + type + '\'' + ", text='" + text + '\'' + '}'; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getText() { return text; } public void setText(String text) { this.text = text; } }
实现了类的实现,接着进行数据库的连接(根据我查到的资料,应该是封闭在一个jdbc.Properties文件中是最好的,但我还没做)
public class jdbcUtil { public static final String url = "jdbc:mysql://localhost:3306/*?useSSL=false&serverTimezone=UTC"; public static final String user = "******"; public static final String password = "******"; public static final String DRIVER = "com.mysql.cj.jdbc.Driver"; /** * 获取Connection对象 * * @return */ static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() { try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); } return null; } // /** // * 释放资源 // */ public static void ReleaseResource(Connection connection, PreparedStatement pstm, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstm != null) { try { pstm.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
然后进行crud代码的实现
(建议写一个接口类,会方便很多)
public Connection conn1=null; public ResultSet rs=null; public PreparedStatement ps=null; //查询所有的数据 @Override public List<NoteEntity> queryAllData() { conn1=jdbcUtil.getConnection();//连接数据库 List<NoteEntity> list=new ArrayList<NoteEntity>(); try{ String sql="select * from note";//查询多条数据 ps=conn1.prepareStatement(sql); rs=ps.executeQuery(); NoteEntity note=null; while(rs.next()){ note =new NoteEntity(); note.setId(Integer.parseInt(rs.getString("id"))); note.setType(rs.getString("type")); note.setText(rs.getString("text")); list.add(note); } }catch (SQLException e){ e.printStackTrace(); }finally { jdbcUtil.ReleaseResource(conn1,ps,rs); } return list; } //新增 @Override public int insertData(NoteEntity note) { conn1=jdbcUtil.getConnection(); int i=0; try{ String sql="insert into note(type,text) values(?,?)"; ps=conn1.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS); // ps.setObject(1,note.getId()); ps.setObject(1,note.getType()); ps.setObject(2,note.getText()); ps.executeUpdate(); rs=ps.getGeneratedKeys();//得到最新的ID if(rs.next()){ i=rs.getInt(1); } }catch (Exception e){ e.printStackTrace(); }finally { jdbcUtil.ReleaseResource(conn1,ps,rs); } return i; } //修改 @Override public int updateData(NoteEntity note) { conn1=jdbcUtil.getConnection(); int i=0; try{ String sql="update note set type=?,text=? where id=?"; ps=conn1.prepareStatement(sql); ps.setObject(1,note.getType()); ps.setObject(2,note.getText()); ps.setInt(3,note.getId()); i=ps.executeUpdate(); }catch (Exception e){ e.printStackTrace(); }finally { jdbcUtil.ReleaseResource(null,ps,rs); } return i; } @Override public int deleteData(int id) { conn1=jdbcUtil.getConnection(); int i=0; try{ String sql="delete from note where id=?"; ps=conn1.prepareStatement(sql); ps.setInt(1,id); i=ps.executeUpdate(); if(i==1){ return i; } }catch (Exception e){ e.printStackTrace(); }finally { jdbcUtil.ReleaseResource(null,ps,rs); } System.out.println("未找到数据!"); return i; } //查询一条数据通过ID @Override public NoteEntity queryDataById(int id) { conn1=jdbcUtil.getConnection(); NoteEntity note=null; if(id>0) { try { String sql = "select * from note where id=?"; ps = conn1.prepareStatement(sql); ps.setObject(1, id); rs = ps.executeQuery(); if (rs.next()) { note = new NoteEntity(); note.setId(rs.getInt("id")); note.setType(rs.getString("type")); note.setText(rs.getString("text")); } } catch (Exception e) { e.printStackTrace(); } finally { jdbcUtil.ReleaseResource(null, ps, rs); } } return note;
接着做service层和servlet层
public Connection conn=null; public ResultSet rs=null; public PreparedStatement ps=null; boolean b=false; // 这里service层要想与dao层建立联系那么必须创建dao层的对象 NoteDao note=new NoteDaoImpl(); // 查询所有的数据 @Override public List<NoteEntity> queryAllData() { return note.queryAllData(); } // 新增 @Override public Boolean insertData(NoteEntity no) { if(no!=null) { note.insertData(no); b = true; } return b; } // 修改 @Override public Boolean updateData(NoteEntity no) { if(no!=null) { note.updateData(no); b=true; } return b; } // 删除 @Override public Boolean deleteData(int id) { if(id!=0) { note.deleteData(id); b=true; } return b; } // 查询一条数据 @Override public NoteEntity queryDataById(int id) { if (id != 0) { return note.queryDataById(id); } else { return null; } }
private static final long serialVersionUID = 1l; // 建立一个service对象,为了与service层建立联系 NoteService note = new NoteServiceImpl(); @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doPost(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("UTF-8");//防止乱码 String type = req.getParameter("action"); // 新增 if ("Insert".equals(type)) { insert(req, resp); } else if ("Update".equals(type)) { update(req, resp); } else if ("QueryById".equals(type)) { queryById(req, resp); } else if ("Delete".equals(type)) { delete(req, resp); } else if ("QueryAll".equals(type)) { queryAll(req, resp); } } public void insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // Integer id = Integer.valueOf(request.getParameter("id")); String type = request.getParameter("type"); String text = request.getParameter("text"); // 把这些获取的值放到note里面 NoteEntity no = new NoteEntity(); // no.setId(id); no.setType(type); no.setText(text); // 最后调用服务来添加 String msg = null; if (note.insertData(no) == true) { queryAll(request, response); } else { msg = "新增失败!!!"; request.getRequestDispatcher("/index.jsp").forward(request, response); } } public void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // Integer id = Integer.valueOf(request.getParameter("id")); int id= Integer.parseInt(request.getParameter("id")); String type = request.getParameter("type"); String text = request.getParameter("text"); NoteEntity no = new NoteEntity(); no.setId(id); no.setType(type); no.setText(text); boolean b = note.updateData(no); if (b) { queryAll(request, response); } else { request.setAttribute("msg", "修改失败!!"); request.getRequestDispatcher("/index.jsp").forward(request, response); } } public void queryById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { NoteEntity no = null; //Integer id= Integer.valueOf(request.getParameter("id")); int id= Integer.parseInt(request.getParameter("id")); System.out.println(id); no = note.queryDataById(id); request.setAttribute("note", no); request.getRequestDispatcher("/note.jsp").forward(request, response); } public void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // Integer id = Integer.valueOf(request.getParameter("id")); // System.out.println(id); String id=request.getParameter("id"); // boolean message=note.deleteData(id); boolean message= note.deleteData(Integer.parseInt(id)); if(message==true){ queryAll(request,response); }else { request.setAttribute("msg","删除失败!!!"); request.getRequestDispatcher("/index.jsp").forward(request,response); } } public void queryAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List<NoteEntity> list = note.queryAllData(); request.setAttribute("list", list); request.getRequestDispatcher("note.jsp").forward(request, response); }
这样就实现了最基本的增删改查了!
那么记下来我想要优化的目标有:
1、实现css的样式美化
2、优化数据库连接代码
3、功能更加完善(创建多个页面实现页面之间的跳转)
4、为了更好的管理jar包,学会maven
5、创建baseDao和baseServlet类
持续更新中...