Ajax链接输出数据库

使用Ajax链接数据库并且获取数据库里的内容显示在页面

两大步骤：

1. 设计并实现数据库
2. 进行数据库链接并获取数据库内容显示

先看效果图
没有查询并显示数据之前效果

点击查询按钮之后获取数据库内容显示在页面

下面进行程序的讲解

一 数据库的设计及实现

1. 新建一个名为Manager的数据库
2. 建一个名为users的表
   表的数据格式为
   
3. 插入数据
   
   数据库的设计及实现完毕

二 链接获取显示程序

先看一下程序列表结构

注意： 在写程序之前一定要导入fastjson-1.2.62.jar和sqlijdbc.jar架包

下面开始程序的演示

1.util包里的DBUtil.java(链接数据库)

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=Manager";
		String user = "sa";
		String pwd = "1";
		
		Connection conn = null;

		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
		
	}
	
	public static void closeConn(Connection conn, PreparedStatement ps, ResultSet rs){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}

2 . bean包里的User.java(User类)

package com.hnpi.bean;

public class User {

	private int id;
	private String name;
	private String sex;
	private String pwd;
	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 String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	public User(int id, String name, String sex, String pwd) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.pwd = pwd;
	}
	
	
	
	
	
	
}

3 . servlet包里的AjaxServlet.java(调用数据库链接并获取内容)

package com.hnpi.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.hnpi.bean.User;
import com.hnpi.util.DBUtil;

public class AjaxServlet extends HttpServlet {


	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		doPost(request, response);
	}


	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		
		
		
		
		
		
		
		List<User> users = new ArrayList<User>();
		
		//链接数据库
		Connection conn = DBUtil.getConn();
		String sql = "select * from users";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setSex(rs.getString(3));
				user.setPwd(rs.getString(4));
				
				users.add(user);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeConn(conn, ps, rs);
		}

		
		
		String userStr = JSON.toJSONString(users);
		
		
		out.println(userStr);
		out.flush();
		out.close();
	}

}

4 . index.jsp(进行数据的显示和样式的设计)

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<script src="jquery.js"></script>
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <style>
		table,table{
			text-align: center;
			border: 2px solid red;
			border-collapse: collapse;
		}
		tr td{
			width: 150px;
			height: 20px;
			border-collapse: collapse;
			border: 2px solid red;
		}
		input{
			margin: 30px 260px;
			width: 100px;
			height: 30px;
			border-radius: 10px;
			border: 2px solid greenyellow;
			text-align: center;
		}
	</style>
  
  <body>
    <table>
    	<thead>
    		<tr><td>id</td><td>姓名</td><td>性别</td><td>密码</td></tr>
    	</thead>
    	<tbody></tbody>
    </table>
    <input type="button" value="查询信息">
  </body>
  
  <script>
  	$(function(){
			$(":button").on("click",function(){
				$.ajax({
					url:"test",
					type:"post",
					dataType:"json",
					success:function(data){
						console.log(data);
						$.each(data,function(i){
							var trstring="";
							trstring = "<tr><td>"+data[i].id+"</td><td>"+data[i].name+"</td><td>"+data[i].sex+"</td><td>"+data[i].pwd+"</td></tr>";
							$("tbody").append(trstring);
						});
					}
				});
			});
		});
  </script>
  
</html>

相信上述程序已经能解决你的问题了，获取更多内容关注我呦！！！