通过数据库查询拼接报表

1.主从表循环一次，通过标识符控制

/**
* ******表格的查询功能****** 组合表格
* 
* @param user_id
*            学生ID 7
* @param tableId
*            表格ID xtdrzb0001
* @return titleObj 组合成的表格(根据用户ID和表格ID查询)
*/
@Override
public List<FillTable> groupTable(String user_id, String tableId) {
// 创建一个list集合对象，用于存放表格
List<FillTable> titleObj = new ArrayList<FillTable>();
Record findById = Db.findById("hlcd_user", user_id);
// 通过表格ID获取基础能力表类型、题库里题目ID（类型为表格数据）、表格Id、表的标题、表的创建时间
String sql = "SELECT hba.table_style,hbaa.title_id,hba.id,hba.table_title,hba.create_time FROM hlcd_base_ability hba INNER JOIN hlcd_base_ability_appendix hbaa ON hba.id = hbaa.tability_id where hba.id = ?";
List<Record> title = Db.find(sql, tableId);
// 获取一次表的ID、表的标题、表的创建时间
int n = 1;
// 根据表格的标题进入表格
for (Record titleIds : title) {
FillTable filltable = new FillTable();
// 取一次表格的ID、表格的标题、表格的创建时间、基础能力表类型
if (n == 1) {
filltable.setUsername((findById.getStr("username")));
filltable.setAbility_id(titleIds.getStr("id"));
filltable.setTable_title(titleIds.getStr("table_title"));
filltable.setCreate_time(titleIds.getStr("create_time"));
filltable.setTable_style(titleIds.getStr("table_style"));
n--;
}
// 获取题目Id
String title_id = titleIds.getStr("title_id");
filltable.setTitle_id(title_id);


// 通过题目Id获取题目、题目序号、题目的答案内容、题目类型、答案类型、题目Id
String sql2 = "SELECT hta.id,hta.answer_type,ht.title_type,ht.title,ht.tilte_no,hta.answer_content FROM hlcd_title ht inner join hlcd_title_appendix hta on ht.id = hta.title_id where ht.id = ?";
List<Record> finds = Db.find(sql2, title_id);


// 创建一个list集合
// List<String> titList = new ArrayList<String>();


List<ContentId> contentId = new ArrayList<ContentId>();
// 获取一次题目类型、题目、题目序号、答案类型
int j = 1;
for (Record record : finds) {


// 创建存放题目答案ID、题目答案内容的对象
ContentId cid = new ContentId();


if (j == 1) {
// filltable.setId(record.getStr("id"));
filltable.setAnswer_type(Integer.valueOf(record
.getStr("answer_type")));
filltable.setTitle_type(record.getStr("title_type"));
filltable.setTitle(record.getStr("title"));
filltable.setTilte_no(record.getStr("tilte_no"));
j--;
}


// 题目答案ID
String id = record.getStr("id");
cid.setId(id);


// 获取题目的答案内容
String titleAnswercon = record.getStr("answer_content");
// titList.add(titleAnswercon);
cid.setCon(titleAnswercon);
contentId.add(cid);
}
// filltable.setAnswer(titList);
filltable.setContentId(contentId);

// 创建一个list集合
// List<String> useList = new ArrayList<String>();
List<CopyOfContentId> selectContentId = new ArrayList<CopyOfContentId>();
int jj = 1;


// 通过用户ID和题目ID获取用户答案内容、答案id
String sql3 = "SELECT huaa.answer_id,hua.id,huaa.user_answer_content,hua.create_time FROM hlcd_user_answer hua INNER JOIN hlcd_user_answer_appendix huaa ON hua.id = huaa.user_answer_id where hua.user_id = ? AND hua.title_id = ? and hua.del_status = 1 order by hua.create_time asc ";
List<Record> answerContent = Db.find(sql3, user_id, title_id);
for (Record answerConn : answerContent) {
// 创建存放用户答案ID、用户答案内容的对象
CopyOfContentId selectConId = new CopyOfContentId();
if (jj == 1) {
filltable.setUser_answer_id(answerConn.getStr("id"));
jj--;
}


selectConId.setUser_answer_id(answerConn.getStr("id"));


selectConId.setCon(answerConn.getStr("user_answer_content"));
// 答案Id
selectConId.setId(answerConn.getStr("answer_id"));
selectContentId.add(selectConId);
}
filltable.setSelectContentId(selectContentId);
//filltable.setContentId(contentId);


titleObj.add(filltable);
}
return titleObj;
}

实体：

package hlcd.evaluationsys.base.pojo;
import java.util.List;


/**
 * 这是一个填写记录的实体
 * 
 * @author Administrator
 * 
 */
public class FillTable {

private String task_id;

private String ability_id;// 基础能力表ID 

private String table_title; // 表的标题

private String table_style; //基础能力表类型

private String create_time; // 创建时间

//private List<TitleObj> titleObj; //题目对象列表 

private String title_id;// 题目ID 

private String tilte_no;// 题目表中的题目序号，用于表格中列的排序

private String title;// 题目

private List<String> answer;// 题目答案 

private List<String> select_answer;// 选择答案

private List<CopyOfContentId> selectContentId; //选择答案Id、题目内容、用户答案Id

private String title_type; //题目类型

private int answer_type; //答案类型(0/1/2)单选择，多选择，填空

private List<ContentId> contentId; // 题目答案ID和题目答案

private String user_answer_id; // 用户答案ID(暂时不用)

private String username; //用户姓名



public String getUsername() {
return username;
}


public void setUsername(String username) {
this.username = username;
}


public String getTitle_id() {
return title_id;
}


public void setTitle_id(String title_id) {
this.title_id = title_id;
}


public String getTilte_no() {
return tilte_no;
}


public void setTilte_no(String tilte_no) {
this.tilte_no = tilte_no;
}


public String getTitle() {
return title;
}


public void setTitle(String title) {
this.title = title;
}


public List<String> getAnswer() {
return answer;
}


public void setAnswer(List<String> answer) {
this.answer = answer;
}


public List<String> getSelect_answer() {
return select_answer;
}


public void setSelect_answer(List<String> select_answer) {
this.select_answer = select_answer;
}


public List<CopyOfContentId> getSelectContentId() {
return selectContentId;
}


public void setSelectContentId(List<CopyOfContentId> selectContentId) {
this.selectContentId = selectContentId;
}


public String getTitle_type() {
return title_type;
}


public void setTitle_type(String title_type) {
this.title_type = title_type;
}


public int getAnswer_type() {
return answer_type;
}


public void setAnswer_type(int answer_type) {
this.answer_type = answer_type;
}


public List<ContentId> getContentId() {
return contentId;
}


public void setContentId(List<ContentId> contentId) {
this.contentId = contentId;
}


public String getUser_answer_id() {
return user_answer_id;
}


public void setUser_answer_id(String user_answer_id) {
this.user_answer_id = user_answer_id;
}


public String getTask_id() {
return task_id;
}


public void setTask_id(String task_id) {
this.task_id = task_id;
}




public String getTable_style() {
return table_style;
}


public void setTable_style(String table_style) {
this.table_style = table_style;
}




public String getAbility_id() {
return ability_id;
}


public void setAbility_id(String ability_id) {
this.ability_id = ability_id;
}




public String getTable_title() {
return table_title;
}


public void setTable_title(String table_title) {
this.table_title = table_title;
}


public String getCreate_time() {
return create_time;
}


public void setCreate_time(String create_time) {
this.create_time = create_time;
}


}