Use the front and back end to separate and search the database data of the book management system

The user login page displays the renderings:

The previous js+JQuery code shows:

<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title></title>
    <script src="jquery.js"></script>
    <style>
        body{
            background-image: url(t01f6feb7e1ef3f0e74.jpg);
            background-size: cover;
        }
        div{
            width: 500px;
            height: 296px;
            margin: 300px auto;
        }
        td,input{
            font-size: 25px;
            color: bisque;
        }
        caption{
            font-size: 25px;
        }

    </style>
</head>
<body>
<div>
<table>
    <caption>Book management landing page</caption>
    <tr>
    <td>User name:</td>
    <td><input type="text" name="name"></td>
</tr>
    <tr>
        <td>Password:</td>
        <td><input type="password" name="pwd"></td>
    </tr>
    <tr>
        <td>Gender:</td>
        <td><input type="text" name="sex"></td>
    </tr>
    <tr>
        <td>Age:</td>
        <td><input type="text" name="age"></td>
    </tr>
    <tr>
        <td>Cell-phone number:</td>
        <td><input type="text" name="phone"></td>
    </tr>
    <tr id="qq">
        <td colspan="2" align="center"><input type="button" name="denglu" value="Sign in" style="background-color: red"></td>
    </tr>
</table>
    </div>
<script>
    $(function(){
        $.ajax({
            url:"userServlet",
            type:"post",
            data:{

            },
            dateType:"json",
            success:function(data){
                $.each(data,function(i){
                    var Stringstr="";
                    Stringstr = "<tr><td>"+data[i].name+"</td><td>"+data[i].pwd+"</td><td>"+data[i].sex+"</td><td>"+data[i].age+"</td><td>"+data[i].phone+"</td></tr>";
                $("tboby").appeng(Stringstr);
                });
            }
        });
    });


</script>
</body>
</html>

The mvc background frame shows:

Book code:

package com.hnpi.bean;
public class Book {
    private int id;
    private String bookName;
    private String bookAuthor;
    private String bookIsbn;
    private String bookPublish;
    private int classifyId;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public String getBookIsbn() {
        return bookIsbn;
    }

    public void setBookIsbn(String bookIsbn) {
        this.bookIsbn = bookIsbn;
    }

    public String getBookPublish() {
        return bookPublish;
    }

    public void setBookPublish(String bookPublish) {
        this.bookPublish = bookPublish;
    }

    public int getClassifyId() {
        return classifyId;
    }

    public void setClassifyId(int classifyId) {
        this.classifyId = classifyId;
    }

    public Book() {
        super();

    }
    public Book(Integer id, String bookName, String bookAuthor, String bookIsbn, String bookPublish, Integer classifyId) {
        super();
        this.id = id;
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookIsbn = bookIsbn;
        this.bookPublish = bookPublish;
        this.classifyId = classifyId;

    }
}

Interface BookService code display:

package com.hnpi.service.impl;

import com.hnpi.bean.Book;

import java.util.List;

public interface BookService {

    List<Book>selectAll();
    boolean delBook(Book book);
    boolean addBook(Book book);
    boolean updateBook(Book book);
}

Implementation interface BookServiceImpl code display:

package com.hnpi.service.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hnpi.bean.Book;

import com.hnpi.util.DBUtil;
public class BookServiceImpl implements BookService{

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public List<Book> selectAll(){
        conn = DBUtil.getConn();
        String sql = "select * from book";
        List<Book> bookList = new ArrayList<Book>();
        try{
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Book book = new Book();
                book.setId(rs.getInt(1));
                book.setBookName(rs.getString(2));
                book.setBookAuthor(rs.getString(3));
                book.setBookIsbn(rs.getString(4));
                book.setBookPublish(rs.getString(5));
                book.setClassifyId(rs.getInt(6));
                bookList.add(book);
            }

        }catch(SQLException E){
            E.printStackTrace();
        }finally{
            DBUtil.closeConn(conn, ps, rs);
        }
        return bookList;
    }

    public boolean delBook(Book book){
        conn = DBUtil.getConn();
        String sql = "delete from book where id = '"+book.getId()+"'";
        int count = 0;
        try{
            ps = conn.prepareStatement(sql);
            count = ps.executeUpdate();

        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn, ps, null);
        }
        if(count>0)
            return true;
        else
            return false;
    }
    public boolean addBook(Book book){
        conn = DBUtil.getConn();
        String sql = "insert into book(id,book_name,book_author,book_isbn,"+"book_publish,classify_id)values(?,?,?,?,?,?";
        int count = 0;
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,book.getId());
            ps.setString(2,book.getBookName());
            ps.setString(3,book.getBookAuthor());
            ps.setString(4,book.getBookIsbn());
            ps.setString(5,book.getBookPublish());
            ps.setInt(6,book.getClassifyId());
            count = ps.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn, ps, null);

        }if(count>0)
            return true;
        else
            return false;
    }

    public boolean updateBook(Book book){
        try{
            conn = DBUtil.getConn();
            String sql = "update book set book_name=?,book_author=?,"+"book_isbn=?,book_publish=?,classify_id=?where id = '"+book.getId()+"'";
            ps = conn.prepareStatement(sql);

            ps.setString(1,book.getBookName());
            ps.setString(2,book.getBookAuthor());
            ps.setString(3,book.getBookIsbn());
            ps.setString(4,book.getBookPublish());
            ps.setInt(5,book.getClassifyId());
            int result = ps.executeUpdate();
            if(result>0){
                return true;

            }else{
                return false;
            }
        }catch(SQLException sqlException){
            sqlException.printStackTrace();
            return false;
        }finally{
            DBUtil.closeConn(conn, ps, null);
        }
    }
}

In the LoginServlet interface, call the select() method to obtain the data in the array, put the data into JSON, and return the data to the HTML interface through JSON (this is incomplete):

package com.hnpi.servlet;

import com.hnpi.bean.Book;
import com.hnpi.service.impl.BookService;
import com.hnpi.service.impl.BookServiceImpl;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

public class LoginServlet extends javax.servlet.http.HttpServlet {
    protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {

        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html");
       //Cross domain allowed
        response.setHeader("Accsee-Control-Allow-Origin","*");

        PrintWriter out = response.getWriter();
        //Instantiate BookService interface
        BookService bookService = new BookServiceImpl();
      //Get the select() method in the interface
        List<Book> list = bookService.selectAll();
      //Put the queried data into JSON
        String bookStr = JSON.toJSONString(list);

        out.println(bookStr);
        out.flush();
        out.close();

    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
     doPost(request,response);
    }
}

DBUtil code and database connection:

package com.hnpi.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DBUtil {
    public static Connection getConn(){
        String url = "jdbc:sqlserver://localhost:1433;databaseName=MyDB";
        String user = "sa";
        String pwd = "1";
        Connection conn = null;
        try{
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            conn = DriverManager.getConnection(url,user,pwd);

        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
    public static void closeConn(Connection conn,PreparedStatement ps,ResultSet rs){
        try{
            if(conn!=null){
                conn.close();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        try{
            if(ps!=null){
                ps.close();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        try{
            if(rs!=null){
                rs.close();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    }

At present, this demo is incomplete, and there are many transformations that haven't implemented servlet s, including addition, deletion, modification, query, not writing database, temporarily not uploading and sorting. After that, the complete project code and effect will be published. Thank you!

Posted by Nuser on Wed, 30 Oct 2019 09:16:46 -0700

Programmer Group

Use the front and back end to separate and search the database data of the book management system

Hot Keywords