Java web从入门到放弃（4）

来源：互联网发布：知了为什么是这个知编辑：程序博客网时间：2024/05/21 07:24

第十章、JDBC操作

一开始就遇到了一个难题，配置数据库，一开始我下载的是mysql-connector-java-5.1.42-bin.jar这个配置文件，死活不行，

后来发现由于本人使用的是eclipse for javaee，里面的DataBase Connections的属性是这样的（下图）

他需要的是mysql-connector-java-5.1.0-bin.jar，在网上重新下载了这个版本然后加入了buildpath之后就好了

文件路径如下图。把文件放在这个位置然后右键buildpath ->add to build path就好了

捣鼓了一晚上，可算好了，明天再更后面的内容。下面贴上连接数据库的测试界面（username和password还有url根据自己的数据库设置稍作更改）

<%@page import="java.sql.DriverManager"%><%@page import="java.sql.*"%><%@page import="com.mysql.jdbc.Driver"%><%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>index</title></head><body><%try {Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/test";String username = "root";String password = "";Connection conn = DriverManager.getConnection(url, username,password);if (conn != null) {out.println("数据库连接成功");conn.close();} else {out.println("数据库连接失败");}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}%><h1>test</h1></body></html>

JDBC操作数据库

样例，对书籍的增删改查

首先，先建立书籍的Book类

package com.song.bean;public class Book {private int id;private String name;private double price;private int bookCount;private String author;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public double getPrice() {return price;}public void setPrice(double price) {this.price = price;}public int getBookCount() {return bookCount;}public void setBookCount(int bookCount) {this.bookCount = bookCount;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}}

然后是界面部分

（界面1）填写书籍信息的index.jsp页面

<%@page import="java.sql.DriverManager"%><%@page import="java.sql.*"%><%@page import="com.mysql.jdbc.Driver"%><%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>index</title></head><body><form action="AddServlet" method="post" onsubmit="return check(this);"><table align="center" width="450"><tr><td align="center" colspan="2"><h2>添加图书信息</h2><hr></td></tr><tr><td align="right">图书名称:</td><td><input type="text" name="name"/></td></tr><tr><td align="right">价格:</td><td><input type="text" name="price"/></td></tr><tr><td align="right">数量:</td><td><input type="text" name="bookCount"/></td></tr><tr><td align="right">作者:</td><td><input type="text" name="author"/></td></tr><tr><td align="center" colspan="2"><input type="submit" value="添加"></td></tr><tr><td align="center" colspan="2"><a href="FindServlet">查看所有书籍</a></td></tr></table></form></body></html>

（界面2）书籍列表以及对书籍的简单修改以及删除界面

<%@ page import="java.util.List"%><%@ page import="com.song.bean.Book"%><%@ page language="java" contentType="text/html; charset=utf-8"pageEncoding="utf-8"%><html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>book_list</title></head><body><table align="center" width="550" border="1"><tr><td align="center" colspan="7"><h2>所有图书信息</h2></td></tr><tr align="center"><td><b>ID</b></td><td><b>图书名称</b></td><td><b>价格</b></td><td><b>数量</b></td><td><b>作者</b></td><td><b>修改数量</b></td><td><b>删除</b></td></tr><%//获取图书信息的集合List<Book> list = (List<Book>) request.getAttribute("list");//判断是否有效if (list == null || list.size() < 1) {out.print("没有数据");} else {for (Book book : list) {%><tr align="center"><td><%=book.getId()%></td><td><%=book.getName()%></td><td><%=book.getPrice()%></td><td><%=book.getBookCount()%></td><td><%=book.getAuthor()%></td><td><form action="UpdateServlet" method="post"onsubmit="return check(this);"><input type="hidden" name="id" value="<%=book.getId()%>"> <inputtype="text" name="bookCount" size="3"> <inputtype="submit" value="修改"></form></td><td><a href="DeleteServlet?id=<%=book.getId()%>">删除</a></td></tr><%}}%></table><div align="center"><a href="index.jsp">添加书籍</a></div></body></html>

最后是针对于增删改查的四个Servlet

（1）增加书籍的AddServlet

package com.song.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class AddServlet extends HttpServlet {private static final long serialVersionUID = 1L;public AddServlet() {super();// TODO Auto-generated constructor stub}protected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stub}protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {String name = request.getParameter("name");double price = Double.valueOf(request.getParameter("price"));int bookCount = Integer.valueOf(request.getParameter("bookCount"));String author = request.getParameter("author");try {Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/test";String username = "root";String password = "";Connection conn = DriverManager.getConnection(url, username,password);String sqladd = "insert into tb_books(name,price,bookCount,author)values(?,?,?,?)";// 添加图书馆信息的SQL语句PreparedStatement ps = conn.prepareStatement(sqladd);ps.setString(1, name);ps.setDouble(2, price);ps.setInt(3, bookCount);ps.setString(4, author);ps.executeUpdate();ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}response.sendRedirect("FindServlet");}}

（2）查找书籍的FindServlet

package com.song.servlet;import java.io.IOException;import java.sql.*;import java.util.*;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.song.bean.Book;public class FindServlet extends HttpServlet {private static final long serialVersionUID = 1L;    public FindServlet() {        super();        // TODO Auto-generated constructor stub    }protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {try{Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/test";String username = "root";String password = "";Connection conn = DriverManager.getConnection(url, username,password);Statement stmt = conn.createStatement();String sql_select = "select*from tb_books";ResultSet rs = stmt.executeQuery(sql_select);List<Book> list = new ArrayList<Book>();while (rs.next()){Book book = new Book();book.setId(rs.getInt("id"));book.setName(rs.getString("name"));book.setPrice(rs.getDouble("price"));book.setBookCount(rs.getInt("bookCount"));book.setAuthor(rs.getString("author"));list.add(book);}request.setAttribute("list", list);rs.close();stmt.close();conn.close();}catch(ClassNotFoundException e){e.printStackTrace();}catch(SQLException e){e.printStackTrace();}//将请求发送到book_list.jsprequest.getRequestDispatcher("book_list.jsp").forward(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stub}}

（3）修改书籍的UpdateServlet

package com.song.servlet;import java.io.IOException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.sql.*;public class UpdateServlet extends HttpServlet {private static final long serialVersionUID = 1L;           public UpdateServlet() {        super();    }protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {int id = Integer.valueOf(request.getParameter("id"));int bookCount = Integer.valueOf(request.getParameter("bookCount"));try{Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/test";String username = "root";String password = "";Connection conn = DriverManager.getConnection(url, username,password);String sql_update = "update tb_books set bookCount=? where id=?";PreparedStatement ps = conn.prepareStatement(sql_update);ps.setInt(1, bookCount);ps.setInt(2, id);ps.executeUpdate();ps.close();conn.close();}catch(Exception e){e.printStackTrace();}response.sendRedirect("FindServlet");}}

（4）最后是删除书籍的DeleteServlet

package com.song.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class DeleteServlet extends HttpServlet {private static final long serialVersionUID = 1L;    public DeleteServlet() {        super();        // TODO Auto-generated constructor stub    }protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {int id = Integer.valueOf(request.getParameter("id"));try{Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/test";String username = "root";String password = "";Connection conn = DriverManager.getConnection(url, username,password);String sql_delete = "delete from tb_books where id=?";PreparedStatement ps = conn.prepareStatement(sql_delete);ps.setInt(1,id);ps.executeUpdate();ps.close();conn.close();}catch(Exception e){e.printStackTrace();}response.sendRedirect("FindServlet");}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {}}

在操作数据库的图中遇到了编码问题，即使将我mysql的标中的编码改为utf-8之后，录入表中的数据还全是“？”。
解决方法参考以下连接 https://www.cnblogs.com/houqi/p/5713176.html

以上代码是更新之后的个人感觉还不错的对于数据库的增删改查的操作代码。在实现的过程中基本遵循了MVC设计模式，将逻辑操作与视图分离开。
1、两个jsp文件就是负责显示界面以及post信息给servlet。
2、几个servlet文件负责调用java类（好像没有用到在bean中定义的Book类，这点后面尽量修改一下）和一些方法，完成和数据库的交互操作。同时也负责jsp页面之间的跳转。
这个例子的缺点就是对于数据库的操作细节也放在了Servlet中，其实应该写在JavaBean中，然后再Servlet中调用就好
在后面的学习中如果有其他感想还会及时修正
最后上一个自己画的MVC的小图图

------------------------------------------------------后面待更新一个分页显示------------------------------------------------------------

阅读全文

0 0