servlet+jdbc+html+jquery+Mysql数据库（基于MVC的简单查询系统）

104 阅读 0 评论 69 点赞

我是靠谱客的博主震动面包，最近开发中收集的这篇文章主要介绍servlet+jdbc+html+jquery+Mysql数据库（基于MVC的简单查询系统），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本系统是利用最基础的JavaWeb技术完成对数据库的全表查询操作，适合读者比作者更白的小白

主要使用技术：jdbc, servlet, jquery, html, ajax, json

文件结构：

实体类:

/**
* 用户信息实体类
* @author yaochi
*
*/
public class UserInfo {
   private String id;
   private String username;
   private int age;
   private String sex;
   private String xgsj;

   public UserInfo() {
       // TODO Auto-generated constructor stub
   }

   public UserInfo(String id, String username, int age, String sex, String xgsj) {
       super();
       this.id = id;
       this.username = username;
       this.age = age;
       this.sex = sex;
       this.xgsj = xgsj;
   }

   public String getId() {
       return id;
   }
   public void setId(String id) {
       this.id = id;
   }
   public String getUsername() {
       return username;
   }
   public void setUsername(String username) {
       this.username = username;
   }
   public int getAge() {
       return age;
   }
   public void setAge(int age) {
       this.age = age;
   }
   public String getSex() {
       return sex;
   }
   public void setSex(String sex) {
       this.sex = sex;
   }
   public String getXgsj() {
       return xgsj;
   }
   public void setXgsj(String xgsj) {
       this.xgsj = xgsj;
   }

    @Override
   public String toString() {
       // TODO Auto-generated method stub
       return "{"id": "" + this.id + "","username": "" + this.username
               + "","age": " + this.age + ","sex": "" + this.sex + "","xgsj": ""
               + this.xgsj + ""}";
   }

dao层:

/**
*
* @author yaochi
*
*/
public class UserInfoDao {

   /**
   * 查询所有
   * @return
   * @throws SQLException
   */
   public List<UserInfo> getUserInfos() {
       String sql = "select * from t_userinfo";
       ResultSet rs = DBHelper.getJdbc().getResultSet(sql);
       List<UserInfo> userList = new ArrayList<UserInfo>();
       try {
           while(rs.next()){
               UserInfo po = new UserInfo();
               po.setId(rs.getString(1));
               po.setUsername(rs.getString(2));
               po.setAge(rs.getInt(3));
               po.setSex(rs.getString(4));
               po.setXgsj(rs.getString(5));
               userList.add(po);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return userList;
   }

   /**
      * 删除
      */
   public void delete(String id) {
       String sql = "delete from t_userinfo where id like "+id;
       int i =DBHelper.getJdbc().delete(sql);
       if(i==0) {
           System.out.println("删除成功");
       }
   }

}

数据库连接工具:

/**
* 数据库连接工具
* @author yaochi
*
*/
public class DBHelper {

      private static final String url = "jdbc:mysql://192.168.1.11/db_test_01";
        private static final String driver = "com.mysql.jdbc.Driver";
        private static final String user = "root";
        private static final String password = "lyc971705";
        public Connection conn = null;
        public PreparedStatement pst = null;
        private static DBHelper dbHelper = new DBHelper();

        private DBHelper() {
            try {
                Class.forName(driver);//指定连接类型
                conn = DriverManager.getConnection(url, user, password);//获取连接
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        public static DBHelper getJdbc() {
           if(dbHelper==null) {
               dbHelper = new DBHelper();
           }
           return dbHelper;
        }

        /**
         * 查询
         * @param sql
         * @return
         */
        public ResultSet getResultSet(String sql) {
           Connection con = DBHelper.getJdbc().conn;
           PreparedStatement pstmt;
           ResultSet rs;
           try {
               pstmt = con.prepareStatement(sql);
               rs = pstmt.executeQuery();

               return rs;
           } catch (SQLException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
           }
           return null;
        }

        /**
         * 删除
         * @param sql
         * @return
         */
        public int delete(String sql) {
            int i = 0;
            PreparedStatement pstmt;
            try {
                pstmt = DBHelper.getJdbc().conn.prepareStatement(sql);
                i = pstmt.executeUpdate();
                System.out.println("删除成功");
               DBHelper.getJdbc().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return i;
        }


        public void close() {
            try {
                this.conn.close();
                this.pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

}

控制层:

/**
*
*<pre><b><font color="blue">SelectFilesServlet</font></b></pre>
*
*<pre><b> --描述说明--</b></pre>
* <pre></pre>
* <pre>
* <b>--样例--</b>
*   SelectFilesServlet obj = new SelectFilesServlet();
*   obj.method();
* </pre>
* JDK版本：JDK1.6
* @author <b>姚驰</b>
*/
@SuppressWarnings("serial")
public class UserInfoSearchServlet extends HttpServlet{

   UserInfoDao dao = new UserInfoDao();

    @Override
   protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException ,IOException {
       this.doPost(req, resp);
    }

    /**
     *
     */
   @Override
   protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       List<UserInfo> voList = new ArrayList<UserInfo>();
       voList = dao.getUserInfos();
       String json = "{"data":"+voList+"}";
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/json");
        PrintWriter out = resp.getWriter();
        out.print(json);
        out.flush();
        out.close();
        System.out.println(json);
   }

}

html文件

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>用户列表</title>

<script src="./js/jquery-1.11.2.min.js"></script>
<script src="./js/index.js"></script>
</head>
<body>
       <div style="width: 600px;border:1px solid black">
           <div>
               <input type="button" value="查询" id="search" style="width:100px;height:30px"/>
           </div>
       </div>
       <div id="showDiv" style="width:600px">
               <table style="border:1px solid black;width:600px;">
                   <tr>
                       <th>id</th><th>姓名</th><th>年龄</th><th>性别</th><th>修改时间</th>
                   </tr>
               </table>
           </div>
</body>
</html>

index.js文件

$(function() {

   $("#search").on("click", function() {
       getData1();
   });

});

var getData1 = function() {
   $.ajax({
       type: "post",
       url: "userInfoSearchServlet.do",
       dataType: "json",
       success: function(result){
           $("#showDiv table").empty();
           $("#showDiv table").append(
               "<tr><th>id</th><th>姓名</th><th>年龄</th><th>性别</th><th>修改时间</th></tr>"
           );
           $.each(result.data,function(i,item) {
               $("#showDiv table").append(
                   "<tr><td>"+item.id+"</td><td>"+item.username+"</td><td>"+item.age+"</td><td>"+item.sex+"</td><td>"+item.xgsj+"</td></tr>"

               );
           });
       },
       error:function(e){

       }
   });
};

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>EXP</display-name>
<welcome-file-list>
    <welcome-file>index.html</welcome-file>
</welcome-file-list>
   <servlet>
   <servlet-name>userInfoSearchServlet</servlet-name>
   <servlet-class>com.yc.mian.web.UserInfoSearchServlet</servlet-class>
</servlet>
<servlet-mapping>
   <servlet-name>userInfoSearchServlet</servlet-name>
   <url-pattern>/userInfoSearchServlet.do</url-pattern>
   </servlet-mapping>
</web-app>

数据库脚本：

/*
SQLyog v10.2
MySQL - 5.0.67-community-nt
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `t_userinfo` (
   `id` varchar (96),
   `username` varchar (150),
   `age` int (50),
   `sex` varchar (15),
   `xgsj` varchar (90)
);
insert into `t_userinfo` (`id`, `username`, `age`, `sex`, `xgsj`) values('u-0009','婚纱','21','男','1021212');
insert into `t_userinfo` (`id`, `username`, `age`, `sex`, `xgsj`) values('u-001','张大千','20','男','20170721');

用Tomcat服务器发布即可：