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

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!");

}

});

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

以下为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);

}

}