JSON查询数据库（图书管理）JDBCTemplate---idea

JSON实现网页不刷新与数据库的交互

我所写的这个与数据库交互的工具类是Druid数据库连接池方法
这里面所使用的的技术jar包有兴趣的可以关注我私聊我，我会把这些东西发给你
先看一下我做的效果图

下面来看代码：
为了以后再公司能更好的养成习惯，我用了编程所遵循的MVC三层架构

数据库里的内容

还是先来看一下我写的用Druid连接池方法写的DBUtil工具类，可能会给你有所帮助：

package com.hnpi.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * Druid连接池的工具类
 */
public class DBUtil {
        //定义成员变量 DataSource
        private static DataSource ds;
    static{

        try {
            //1：加载配置文件
            Properties pro = new Properties();
            pro.load(DBUtil.class.getClassLoader().getResourceAsStream("info.properties"));
            //2：获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    /**
     * 释放资源
     */

    public static void close(Statement stmt, Connection conn){
        close(null,stmt,conn);
    }

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

    /**
     * 获取连接池方法
     */
    public static DataSource getDataSource(){
        return ds;
    }
}

下面来看javaBean

package com.hnpi.domain;

public class Book {
    private Integer id;
    private String name;
    private String author;
    private Integer isbn;
    private String publisn;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Integer getIsbn() {
        return isbn;
    }

    public void setIsbn(Integer isbn) {
        this.isbn = isbn;
    }

    public String getPublisn() {
        return publisn;
    }

    public void setPublisn(String publisn) {
        this.publisn = publisn;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + author + '\'' +
                ", isbn=" + isbn +
                ", publisn='" + publisn + '\'' +
                '}';
    }
}

接下来是Service的方法和实现类

package com.hnpi.service;

import com.hnpi.domain.Book;

import java.util.List;

public interface BookService {
    List<Book> FindAll();
}

实现类

package com.hnpi.service.Impl;

import com.hnpi.domain.Book;
import com.hnpi.service.BookService;
import com.hnpi.util.DBUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class BookServiceImpl implements BookService {
    JdbcTemplate template = new JdbcTemplate(DBUtil.getDataSource());
    @Override
    public List<Book> FindAll() {
        String sql = "select * from TestBook";
        List<Book> list = template.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
        return list;
    }
}

下面是servlet代码：

package com.hnpi.servlet;

import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.databind.JsonNode;
import com.hnpi.domain.Book;
import com.hnpi.service.BookService;
import com.hnpi.service.Impl.BookServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/JsonServlet")
public class JsonServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setContentType("text/html;chatset = utf-8");
        response.setCharacterEncoding("utf-8");
        PrintWriter out = response.getWriter();

        BookService bookService = new BookServiceImpl();

        List list = bookService.FindAll();
        String json = JSON.toJSONString(list);
        out.print(json);

    }

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

接下来是最后的前端页面：

<%--
  Created by IntelliJ IDEA.
  User: Lenovo
  Date: 2019/10/27
  Time: 13:56
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>显示图书页面</title>
    <script src="js/jquery-3.1.1.min.js"></script>
  </head>
  <body>
  <center>
  <input type="button" value="点击显示图书列表"><br>
    <table>
      <thead style="background-color: #cccccc">
      <tr>
        <td>id</td>
        <td>名称</td>
        <td>作者</td>
        <td>销量</td>
        <td>出版社</td>
      </tr>
      </thead>
      <tbody id="tbody">

      </tbody>
    </table>

  </center>
  <script>
    $(function () {
        $(":button").click(function () {
            $.ajax({
                url: "/JsonServlet",//地址
                type: "post",        //提交方式
                dataType:"json",    //指定服务器返回的类型
                success:function (data) {
                    $("#tbody").empty();
                    var str = "";
                    for (var i = 0;i < data.length;i++) {
                        str = "<tr><td>"+data[i].id+"</td><td>"+data[i].name+"</td><td>"+data[i].author+"</td><td>"+data[i].isbn+"</td><td>"+data[i].publisn+"</td></td>";
                        $("#tbody").show().append(str);
                    }
                },
                error :function () {
                    alert("请求失败!!");
            }
            });
        });
    });
  </script>
  </body>
</html>

好了，这个关于JSON的与数据库交互的功能，我只做了一个简单的查询，希望对读者有所帮助。如有需要改善的地方，欢迎私聊我，我会继续努力！！！