博客

Java+JSP+Oracle+Ajax前台显示数据库数据

在这个例子中，我们将使用Java查询Oracle数据库里面的数据并在前台JSP页面显示。


一、准备


Java连接Oracle数据库请参考 Java连接Oracle数据库.note

使用Ajax需要jQuery.js文件，下载：

http://www.jq22.com/jquery-info122


必要的连接Oracle的jar包。

所用数据库表数据：




二、创建工程


工程的完整目录为：

• 
  JdbcUtils.java
  

  JdbcUtils.java连接数据库

package com.cu.test;

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

public class JdbcUtils {

    private static final String driverUrl = "oracle.jdbc.driver.OracleDriver";

    private static final String url = "jdbc:oracle:thin:@59.71.244.71:1521:ORCL";

    private static final String username = "SXUSER";

    private static final String password = "1234";

    public static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driverUrl);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("连接成功！");
        return connection;
    }
}

• 
  DataInfo.java
  
  
  DataInfo用来保存由数据库传来的数据

package com.cu.test;

public class DataInfo {
    public int id,age;
    public String name,sex;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    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;
    }
}

• 
  DataSets.java
  
  
  DataSets类获取数据库数据，返回存有数据的list

package com.cu.test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DataSets {
    public static String sql="select * from ODBC_IMPORT t";
    public static Statement stmt = null;
    public static ResultSet res = null;
    public static Connection conn = null;
    public static ArrayList<DataInfo> list=new ArrayList<DataInfo>();

    public static ArrayList<DataInfo> getDataInfo(){
        try {
            conn=JdbcUtils.getConnection();
            stmt = conn.createStatement();
            res = stmt.executeQuery(sql);
            while(res.next())
            {
                DataInfo data=new DataInfo();
                data.setId(res.getInt("ID"));
                data.setName(res.getString("NAME"));
                data.setAge(res.getInt("AGE"));
                data.setSex(res.getString("SEX"));
                list.add(data);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }

    //将list转成json样式的字符串
    public static String dataJson(ArrayList<DataInfo> list){
        String json="{\"people\":[";
        for (DataInfo dataInfo : list) {
            String line="{\"name\":\""+dataInfo.getName()+
                    "\",\"id\":"+dataInfo.getId()+","+
                    "\"age\":"+dataInfo.getAge()+","+
                    "\"sex\":"+dataInfo.getSex()+"},"+
                    "\r\n";
            json=json+line;
        }
        json=json.substring(0, json.length()-3);
        json=json+"]}";
        return json;

    }
}

• 
  DataServlet.java
  
  
  新建DataServlet类，用于将获得的数据传往前台JSP页面

package com.cu.test;

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

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

public class DataServlet 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"); 
        response.setCharacterEncoding("utf-8"); 

        ArrayList<DataInfo> list=DataSets.getDataInfo();
        String json=DataSets.dataJson(list);
        PrintWriter out = response.getWriter();
        out.println(json);
        out.close();
    }
}

• 
  MyJsp.jsp
  
  
  前台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 'MyJsp.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">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->
    <script src="jquery-3.0.0.js" type="text/javascript"></script>
    <script type="text/javascript">
        function myFunction(){
            $.ajax({
                type:"post",            //传输方式为post，所以我们在servlet里面代码是写在doPost()函数中
                url:"DataServlet",      //这就是使用的servlet的url
                success:function(data){
                    document.getElementById("json").innerHTML=data;    
                }
            });
        }
    </script>
  </head>

  <body>
    <p id="json">数据</p><br>
    <button onclick="myFunction()">获取数据</button>
  </body>
</html>

运行MyJsp.jsp的结果：