java连接mysql数据库操作多个表的结果集,组装成json数组发送到前台，mysql用存储过程批量插入数据

2017-12-4 23:41:41 5,388 views

java操作数据库进行多个表的查询思路分析

大致思路是将每个表取出的数据按键值的形式放进HashMap

while (rsareaA.next()) {
        Map rowDataA = new HashMap();
        for (int i = 1; i <= columnCountA; i++) {
          rowDataA.put(mdA.getColumnName(i), rsareaA.getObject(i));
        }
        listA.add(rowDataA);// 将map中的值添加到List中
      }

 

然后将每个表对应的HashMap放进每个对应的list中

 listA.add(rowDataA);

 
最后将全部表存储的list统一放进一个List中

list.add(listA);

 
 

最后将list转换为json数组并发送给前台

JSONArray jsonArr = new JSONArray();
      jsonArr.add(list);// 将List中的值添加到json数组中
      out.print(jsonArr);

 
 

前台可以用ajax获取数据

$.ajax({
    url: "selectContent",
    type: "get",
    dataType: "json",
    success: function(data) {
      console.log(data[0]);//就可以获取7个表的所有数据
    },
    error: function() {
      alert("error!");
    }
  });

 
json数组

关于数据库数插入据量很大时，可以用存储过程来批量生成数据。

以下为Mysql的存储过程

delimiter $$                     #创建一个储存过程
create procedure insertt13()
begin
set @areaName="B1";     #区号
set @column=1;     #列号
set @row="十三";      #行号
set @dataName="";
set @tel="";
while @column<=36 do     #这里为要生成的数量
insert into areab1 values(@areaName,@row,@column,@dataName,@tel);
set @column=@column+1;
end while;
end$$
delimiter ;
call insertt13();

 
效果图
数据库命令行生成的数据

下面为java连接数据库的代码

package com.content.servlet;
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
public class selectContent extends HttpServlet {
  private static final long serialVersionUID = 1L;
  Connection con;
  Statement sqlA, sqlB1, sqlB2, sqlC1, sqlC2, sqlD1, sqlD2;
  @Override
  public void init(ServletConfig config) throws ServletException {
    // TODO Auto-generated method stub
    super.init(config);
  }
  @Override
  protected void doPost(HttpServletRequest request,
      HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    try {
      Class.forName("com.mysql.jdbc.Driver");
    } catch (Exception e) {
    }
    request.setCharacterEncoding("utf-8");
    response.setContentType("text/html;charset=utf-8");
    try {
      PrintWriter out = response.getWriter();
      List list = new ArrayList(), listA = new ArrayList(), // 创建列表A
      listB1 = new ArrayList(), // 创建列表B1
      listB2 = new ArrayList(), // 创建列表B2
      listC1 = new ArrayList(), // 创建列表C1
      listC2 = new ArrayList(), // 创建列表C2
      listD1 = new ArrayList(), // 创建列表D1
      listD2 = new ArrayList();// 创建列表D2
      String uri = "jdbc:mysql://127.0.0.1/temple?"
          + "user=bdm242626210&password=n7mwvrDVcj5gd&characterEncoding=utf-8";
      con = DriverManager.getConnection(uri);
      sqlA = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      sqlB1 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      sqlB2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      sqlC1 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      sqlC2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      sqlD1 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      sqlD2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      String areaA = "select * from areaa", areaB1 = "select * from areab1", areaB2 = "select * from areab2", areaC1 = "select * from areac1", areaC2 = "select * from areac2", areaD1 = "select * from aread1", areaD2 = "select * from aread2";
      sqlA = con.createStatement();// 创建sql语句
      sqlB1 = con.createStatement();// 创建sql语句
      sqlB2 = con.createStatement();// 创建sql语句
      sqlC1 = con.createStatement();// 创建sql语句
      sqlC2 = con.createStatement();// 创建sql语句
      sqlD1 = con.createStatement();// 创建sql语句
      sqlD2 = con.createStatement();// 创建sql语句
      ResultSet rsareaA = sqlA.executeQuery(areaA), // 执行sql语句
      rsareaB1 = sqlB1.executeQuery(areaB1), // 执行sql语句
      rsareaB2 = sqlB2.executeQuery(areaB2), // 执行sql语句
      rsareaC1 = sqlC1.executeQuery(areaC1), // 执行sql语句
      rsareaC2 = sqlC2.executeQuery(areaC2), // 执行sql语句
      rsareaD1 = sqlD1.executeQuery(areaD1), // 执行sql语句
      rsareaD2 = sqlD2.executeQuery(areaD2);// 执行sql语句
      ResultSetMetaData mdA = rsareaA.getMetaData(), // 获得A的结果集结构信息,元数据
      mdB1 = rsareaB1.getMetaData(), mdB2 = rsareaB2.getMetaData(), mdC1 = rsareaC1
          .getMetaData(), mdC2 = rsareaC2.getMetaData(), mdD1 = rsareaD1
          .getMetaData(), mdD2 = rsareaD2.getMetaData();
      int columnCountA = mdA.getColumnCount(), // 获得A的列数
      columnCountB1 = mdB1.getColumnCount(), columnCountB2 = mdB2
          .getColumnCount(), columnCountC1 = mdC1.getColumnCount(), columnCountC2 = mdC2
          .getColumnCount(), columnCountD1 = mdD1.getColumnCount(), columnCountD2 = mdD2
          .getColumnCount();
      // ---------------------A start
      while (rsareaA.next()) {
        Map rowDataA = new HashMap();
        for (int i = 1; i <= columnCountA; i++) {
          rowDataA.put(mdA.getColumnName(i), rsareaA.getObject(i));
        }
        listA.add(rowDataA);// 将map中的值添加到List中
      }
      // ---------------------A end
      // ---------------------B1 start
      while (rsareaB1.next()) {
        Map rowDataB1 = new HashMap();
        for (int i = 1; i <= columnCountB1; i++) {
          rowDataB1.put(mdB1.getColumnName(i), rsareaB1.getObject(i));
        }
        listB1.add(rowDataB1);// 将map中的值添加到List中
      }
      // ---------------------B1 end
      // ---------------------B2 start
      while (rsareaB2.next()) {
        Map rowDataB2 = new HashMap();
        for (int i = 1; i <= columnCountB2; i++) {
          rowDataB2.put(mdB2.getColumnName(i), rsareaB2.getObject(i));
        }
        listB2.add(rowDataB2);// 将map中的值添加到List中
      }
      // ---------------------B2 end
      // ---------------------C1 start
      while (rsareaC1.next()) {
        Map rowDataC1 = new HashMap();
        for (int i = 1; i <= columnCountC1; i++) {
          rowDataC1.put(mdC1.getColumnName(i), rsareaC1.getObject(i));
        }
        listC1.add(rowDataC1);// 将map中的值添加到List中
      }
      // ---------------------C1 end
      // ---------------------C2 start
      while (rsareaC2.next()) {
        Map rowDataC2 = new HashMap();
        for (int i = 1; i <= columnCountC2; i++) {
          rowDataC2.put(mdC2.getColumnName(i), rsareaC2.getObject(i));
        }
        listC2.add(rowDataC2);// 将map中的值添加到List中
      }
      // ---------------------C2 end
      // ---------------------D1 start
      while (rsareaD1.next()) {
        Map rowDataD1 = new HashMap();
        for (int i = 1; i <= columnCountD1; i++) {
          rowDataD1.put(mdD1.getColumnName(i), rsareaD1.getObject(i));
        }
        listD1.add(rowDataD1);// 将map中的值添加到List中
      }
      // ---------------------D1 end
      // ---------------------D2 start
      while (rsareaD2.next()) {
        Map rowDataD2 = new HashMap();
        for (int i = 1; i <= columnCountD2; i++) {
          rowDataD2.put(mdD2.getColumnName(i), rsareaD2.getObject(i));
        }
        listD2.add(rowDataD2);// 将map中的值添加到List中
      }
      // ---------------------D2 end
      list.add(listA);
      list.add(listB1);
      list.add(listB2);
      list.add(listC1);
      list.add(listC2);
      list.add(listD1);
      list.add(listD2);
      JSONArray jsonArr = new JSONArray();
      jsonArr.add(list);// 将List中的值添加到json数组中
      out.print(jsonArr);
      con.close();
    } catch (SQLException e) {
      System.out.println(e);
    }
  }
  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    // TODO Auto-generated method stub
    doPost(req, resp);
  }
}

 

0
Tags: , ,
分
享
到

分享到微信朋友圈

打开微信，点击底部的“发现”，
使用“扫一扫”即可将网页分享至朋友圈。