Experiment 4 JDBC Technology
1, Objective:
1. Familiar with MySQL installation and configuration method; familiar with JDBC basic programming method; master the method of adding, deleting, modifying and querying database in web program;
2. Understand how to program database in JSP+JavaBean+Servlet integrated application.
2, Experimental principle:
< slightly >
3, Experiment content:
1. Design persistent JavaBean class, design database table structure and store the JavaBean object. And write the CRUD process of the user interface to realize the data.
2. User login (JSP+JavaBean+MySQL)
Encapsulating the code of User name and password verification in JavaBean greatly reduces the amount of Java code in JSP file, and realizes the separation of business logic and representation. The system consists of User table User, User class User and three JSP pages (User login page input.jsp, User login page login.jsp, welcome page welcome.jsp)
Code:
bean
package Experiment four.bean;
public class User {
private Integer uid;
private String username;
private String password;
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
dao
package Experiment four.dao;
import Experiment four.bean.User;
import java.sql.*;
public class UserDao {
private Connection conn = null;
public UserDao(){
}
private void initConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cms";
this.conn = DriverManager.getConnection(url, "root", "root");
}
public User selectUserbyusername(String username) throws Exception {
User users = null;
this.initConnection();
String sql = "select username,password from t_user1" +
" where username=?";
PreparedStatement ps = this.conn.prepareStatement(sql);
ps.setString(1,username);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
users = new User();
users.setUsername(rs.getString("username"));
users.setPassword(rs.getString("password"));
}
this.conn.close();
return users;
}
}
input.jsp
<%@ page import="Experiment four.dao.UserDao" %>
<%@ page import="Experiment four.bean.User" %><%--
Created by IntelliJ IDEA.
User: lenovo
Date: 2019/12/12
Time: 21:58
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
request.setCharacterEncoding("UTF-8");
String uname=request.getParameter("username");
String password = request.getParameter("password");
UserDao userDao = new UserDao();
User user = userDao.selectUserbyusername(uname);
if(user!=null&&user.getUsername().equals(uname)&&
user.getPassword().equals(password)){
/* out.print(uname);
out.print(password);*/
%>
<%-- <jsp:forward page="welcome.jsp"></jsp:forward>--%>
//Log in successfully < a href = "welcome. JSP" > forward to the success interface</a>
<%
}else{
%>
//Login failed, < a href = "login1. JSP" > return to login page</a>
<%
}
%>
</body>
</html>
<%--
Created by IntelliJ IDEA.
User: lenovo
Date: 2019/12/12
Time: 21:59
To change this template use File | Settings | File Templates.
--%>
login.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>User login</title>
</head>
<body>
<h3>User login</h3>
<form action="input.jsp" method="post">
<%--Please enter user id: <input type="text" name="uid"><br>--%>
//Please enter user name: < input type = "text" name = "username" > < br >
//Please enter the user password: < input type = "text" name = "password" > < br >
<input type="submit" value="Sign in">
<input type="reset" value="Reset">
</form>
</body>
</html>
welcome.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<h2>Welcome, login success!</h2>
</body>
</html>
3. Shopping cart (JSP+JavaBean+MySQL)
Item of commodity list. The item class corresponding to the item table is defined in Item.java, the parent class Database of all classes accessing the Database is defined in Database.java, the class ItemDao used to access the Database's item table is defined in ItemDao.java, which is a subclass of the Database class, and the shopping Cart class Cart is defined in Cart.java. shopping.jsp is the product list page, and cart.jsp is the shopping Cart page.
4. Design shopping cart in accordance with MVC design mode. (JSP+JavaBean+Servlet+MySQL)
Book:
package Experiment four.bean;
public class Book {
private String id;
private String bookname;
private double bookprice;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getBookname() {
return bookname;
}
public void setBookname(String bookname) {
this.bookname = bookname;
}
public double getBookprice() {
return bookprice;
}
public void setBookprice(double bookprice) {
this.bookprice = bookprice;
}
}
Bookdao:
package Experiment four.dao;
import bean.Book;
import java.sql.*;
import java.util.ArrayList;
public class BookDao {
public ArrayList<Book> queryBooksByName(String bookname) throws Exception {
Connection conn = null;
ArrayList<Book> books= new ArrayList<Book>();
try {
//Get connection
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/cms";
conn = DriverManager.getConnection(url, "root", "root");
// Run SQL statement
String sql = "SELECT ID,BOOK_NAME,BOOK_PRICE from T_BOOK WHERE BOOK_NAME LIKE '%" + bookname + "%' ";
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(sql);
while (rs.next()) {
//Instantiation of VO
Book book = new Book();
book.setId(rs.getString("ID"));
book.setBookname(rs.getString("BOOK_NAME"));
book.setBookprice(rs.getDouble("BOOK_PRICE"));
books.add(book);
}
rs.close();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {// Close connection
if (conn != null) {
conn.close();
conn = null;
}
} catch (Exception ex) {
}
}
return books;
}
}
Addbookservlet:
package Experiment four.servlet;
import bean.Book;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
public class AddBookServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
HttpSession session = request.getSession();
HashMap books = (HashMap) session.getAttribute("books");
if(books == null){
books = new HashMap();
}
String id = request.getParameter("id");
String bookname = request.getParameter("bookname");
double bookprice = Double.valueOf(request.getParameter("bookprice"));
Book book = new Book();
book.setId(id);
book.setBookname(bookname);
book.setBookprice(bookprice);
books.put(id, book);
session.setAttribute("books", books);
// ServletContext application = this.getServletContext();
// RequestDispatcher rd = application.getRequestDispatcher("/servlet/QueryServlet");
// rd.forward(request, response);
response.sendRedirect("/query");
}
}
Queryservlet:
package Experiment four.servlet;
import bean.Book;
import dao.BookDao;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
public class QueryServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//PrintWriter out = response.getWriter();
String bookname=request.getParameter("name");
BookDao bookDao = new BookDao();
try {
ArrayList<Book> books = bookDao.queryBooksByName(bookname);
request.setAttribute("books", books);
request.getRequestDispatcher("/addbook.jsp").forward(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
}
Removeservlet:
package Experiment four.servlet;
import bean.Book;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
public class RemoveServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
String id = request.getParameter("id");
HttpSession session = request.getSession();
HashMap books = (HashMap)session.getAttribute("books");
Book book = (Book)books.get(id);
//Remove the corresponding book
books.remove(id);
response.sendRedirect("/query");
}
}
9_1.jsp:
<%@ page import="java.util.ArrayList" %>
<%@ page import="bean.Book" %><%--
Created by IntelliJ IDEA.
User: lenovo
Date: 2019/11/13
Time: 12:30
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form name="loginForm" action="query" method="post">
//Please enter the name of the book you want to query: < input type = "text" name = "name" / >
<input type="submit" value="query">
</form>
<hr>
<%
ArrayList books = (ArrayList) request.getAttribute("books");
if(books!=null&&books.size()>0){
%>
<table >
<tr>
<h3>Book information found:</h3>
</tr>
<tr >
<td>Title of book</td>
<td>Book price</td>
<td>operation</td>
</tr>
<tr>
<td>
<%
for(int i=0;i<books.size();i++){
Book book = (Book) books.get(i);
%>
<tr>
<td><%=book.getBookname()%></td>
<td><%=book.getBookprice()%></td>
<td><a href="add?id=<%=book.getId()%>&bookname=<%=book.getBookname()%>&bookprice=<%=book.getBookprice()%>">Add to cart</a>
</td>
</tr>
<%
}
%>
</table>
<%
}
%>
<a href="/9_2.jsp">View Cart</a>
</body>
</html>
9_2.jsp:
<%@ page import="java.util.Set" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="bean.Book" %>
<%@ page import="java.util.HashMap" %><%--
Created by IntelliJ IDEA.
User: lenovo
Date: 2019/11/13
Time: 12:58
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<table border="1">
<tr bgcolor="pink">
<td>Name of book</td>
<td>Book price</td>
<td>Book number</td>
<td>delete</td>
</tr>
<%
HashMap books = (HashMap) session.getAttribute("books");
Set set = books.keySet();
Iterator ite = set.iterator();
Book book = new Book();
double count =0;
while (ite.hasNext()) {
String id = (String) ite.next();
book = (Book) books.get(id);
%>
<tr bgcolor="yellow">
<td><%=book.getBookname()%></td>
<td><%=book.getBookprice()%></td>
<td><%=book.getId()%></td>
<td><a href="/remove?id=<%=book.getId()%>">delete</a>
</td>
</tr>
<%
count +=book.getBookprice();
}
%>
//Total: <% = count% > yuan
</table>
<a href="9_1.jsp">Keep buying books</a>
</body>
</html>
4, Experimental requirements:
1. Carefully implement each step and make records.
2. Make a brief record of the experimental results and form an experimental report, which includes:
Experiment purpose;
Experiment content (brief record);
Problems and solutions in the experiment; (key points)
Experiment experience (key points)
5, Experimental hours: 2 hours
91 original articles published, 46 praised, 6661 visited