一 创建表 ,添加字段, 新增数据
package org.jeecg.modules.template.service.impl;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.util.DateUtils;
import org.jeecg.common.util.dynamic.db.DynamicDBUtil;
import org.jeecg.modules.criteria.entity.MbQueryCriteria;
import org.jeecg.modules.criteria.mapper.MbQueryCriteriaMapper;
import org.jeecg.modules.elements.entity.MbFormElements;
import org.jeecg.modules.elements.mapper.MbFormElementsMapper;
import org.jeecg.modules.information.Vo.MbLoanInformationVo;
import org.jeecg.modules.template.Vo.MdDocumentTemplateVo;
import org.jeecg.modules.template.entity.MdDocumentTemplate;
import org.jeecg.modules.template.mapper.MdDocumentTemplateMapper;
import org.jeecg.modules.template.service.IMdDocumentTemplateService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.transaction.annotation.Transactional;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;
/**
* @Description: 文档模板
* @Author: yxg
* @Date: 2022-09-08
* @Version: V1.0
*/
@Service
public class MdDocumentTemplateServiceImpl extends ServiceImpl<MdDocumentTemplateMapper, MdDocumentTemplate> implements IMdDocumentTemplateService {
@Autowired
private MdDocumentTemplateMapper mdDocumentTemplateMapper;
@Autowired
private MbFormElementsMapper mbFormElementsMapper;
@Autowired
private MbQueryCriteriaMapper mbQueryCriteriaMapper;
/**
* 配置数据源
*/
DruidDataSource dataSource = DynamicDBUtil.getDbSourceByDbKey("dw_oracle");
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
@Override
@Transactional(rollbackFor = Exception.class)
public void updatelist(MdDocumentTemplateVo templateVo, List<MbFormElements> mbFormElementsList,
List<MbQueryCriteria> mbQueryCriteriaList) throws ClassNotFoundException, SQLException {
//1 根据前端传字段名创建表
//2 向表添加字段
//3 向表单元素表添加数据
//4 向查询元素表添加数据
MdDocumentTemplateVo documentTemplateVo=mdDocumentTemplateMapper.slee(templateVo.getId());
String number=documentTemplateVo.getNum();
number="SANS_BDZS_"+number;
//1
String sql1="select count(1) from all_tables where TABLE_NAME ='"+number+"'";
int total=jdbcTemplate.queryForObject(sql1,Integer.class);
if (total==1){
//有就删除表
// String sql2="drop table "+number +"";
// jdbcTemplate.execute(sql2);
}else {
String sql3="CREATE TABLE "+number+"(id varchar2(256) not null," +
" mb_document_template_id varchar2(256) not null, status varchar2(256) not null, " +
"create_by varchar2(256) not null, create_time varchar2(256) not null)";
jdbcTemplate.execute(sql3);
}
mdDocumentTemplateMapper.updatelist(templateVo);
String mbDocumentTemplateId=templateVo.getId();
//先查询表中的数据条数
List<MbFormElements> selec=mbFormElementsMapper.selectListid(mbDocumentTemplateId);
//3
if(mbFormElementsList!=null && mbFormElementsList.size()>selec.size() ){
for(MbFormElements entry:mbFormElementsList){
MbFormElements select=mbFormElementsMapper.selec(entry);
//以名字做出判断是新增还是更新
List<MbFormElements> name=mbFormElementsMapper.selecName(entry);
if(select!=null){
mbFormElementsMapper.updateById(entry);
}else {
String s = UUID.randomUUID().toString().replace("-", "");
entry.setId(s);
entry.setMbDocumentTemplateId(templateVo.getId());
entry.setElementId(entry.getElementId().toUpperCase());
System.out.println(entry.getElementId().toUpperCase());
String name1=entry.getElementId();
//2
String aqladd="ALTER TABLE "+number+" ADD "+name1+" varchar2(256) ";
jdbcTemplate.batchUpdate(aqladd);
mbFormElementsMapper.insert(entry);
}
}
}else {
//如果条数小于当前前端传来的,那么说明有删除的,就先删除数据库中相应数据再新增操作
mbFormElementsMapper.delect(mbDocumentTemplateId);
for(MbFormElements entry:mbFormElementsList) {
String s = UUID.randomUUID().toString().replace("-", "");
entry.setId(s);
entry.setMbDocumentTemplateId(templateVo.getId());
mbFormElementsMapper.insert(entry);
}
}
//先查询条数
List<MbQueryCriteria> selets=mbQueryCriteriaMapper.selectListid(mbDocumentTemplateId);
//4
if(mbQueryCriteriaList!=null && mbQueryCriteriaList.size()>selets.size() ){
for(MbQueryCriteria entry:mbQueryCriteriaList){
//先查询表有没有数据 有更新 无插入
MbQueryCriteria selectObj=mbQueryCriteriaMapper.selectlist(entry);
//以名字做出判断是新增还是更新
List<MbQueryCriteria> name=mbQueryCriteriaMapper.selecName(entry);
if(selectObj!=null){
mbQueryCriteriaMapper.updateById(entry);
}else {
String d = UUID.randomUUID().toString().replace("-", "");
entry.setId(d);
entry.setMbDocumentTemplateId(templateVo.getId());
mbQueryCriteriaMapper.insert(entry);
}
}
}else {
//如果条数小于当前前端传来的,那么说明有删除的,就先删除数据库中相应数据再新增操作
mbQueryCriteriaMapper.delect(mbDocumentTemplateId);
for(MbQueryCriteria entry:mbQueryCriteriaList){
String d = UUID.randomUUID().toString().replace("-", "");
entry.setId(d);
entry.setMbDocumentTemplateId(templateVo.getId());
mbQueryCriteriaMapper.insert(entry);
}
}
}
@Override
public List<MdDocumentTemplate> select(String id) {
return mdDocumentTemplateMapper.select(id);
}
@Override
public MdDocumentTemplate sele(String id) {
return mdDocumentTemplateMapper.seleect(id);
}
/**
* 新增
* @param mbLoanInformation
*/
@Override
public void add(MbLoanInformationVo mbLoanInformation) {
List<LinkedHashMap<Object,Object>> collect = (List) mbLoanInformation.getMbList().stream().limit(10).collect(Collectors.toList());
String column = "";
String values = "";
String id = UUID.randomUUID().toString().replace("-", "");
for (LinkedHashMap<Object,Object> item : collect ) {
String value = "";
//根据生成的表的名字和模板的id进行数据查询
String mbid=mbLoanInformation.getId();
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
String create= sysUser.getRealname();
String date= DateUtils.formatDateTime();
String stsua="1";
item.put("mb_document_template_id",mbid);
item.put("id",id);
item.put("status",stsua);
item.put("create_by",create);
item.put("create_time",date);
for(Object map_item : item.keySet()){
System.out.println(column.indexOf(map_item.toString()));
if(column.indexOf(map_item.toString()) == -1){
if(!column.equals("")){
column = column+","+map_item;
}else{
column = map_item.toString();
}
}
if(!value.equals("")){
value = value+",'"+item.get(map_item)+"'";
}else{
value = "'"+item.get(map_item).toString()+"'";
}
}
values =values+ "("+value+")";
}
//查询模板编号,用来查询表数据
MdDocumentTemplate pageList = mdDocumentTemplateMapper.seleect(mbLoanInformation.getId());
String table=pageList.getNum();
table="SANS_BDZS_"+table;
String sql="insert into "+table+"("+column+") values"+values;
//执行SQL语句
jdbcTemplate.batchUpdate(sql);
}
/**
* 编辑
* @param mbLoanInformation
*/
@Override
public void updata(MbLoanInformationVo mbLoanInformation) {
List<LinkedHashMap<Object,Object>> collect = (List) mbLoanInformation.getMbList().stream().limit(10).collect(Collectors.toList());
String column = "";
String values = "";
String updateString = "";
String name=null;
for (LinkedHashMap<Object,Object> item : collect ) {
String value = "";
for(Object map_item : item.keySet()){
System.out.println(column.indexOf(map_item.toString()));
if(column.indexOf(map_item.toString()) == -1){
if(!column.equals("")){
column = column+","+map_item;
}else{
column = map_item.toString();
}
}else{
String[] columns = column.split(",");
if(!Arrays.asList(columns).contains(map_item.toString())){
if (!column.equals("")) {
column = column + "," + map_item;
} else {
column = map_item.toString();
}
}
}
if(updateString.indexOf(map_item.toString()+"value("+map_item.toString()+")") == -1){
if(!updateString.equals("")){
updateString = updateString+","+map_item.toString()+"='"+item.get(map_item)+"'";
}else{
updateString = map_item.toString()+"='"+item.get(map_item)+"'".toString();
}
}
if(!value.equals("")){
value = value+",'"+item.get(map_item)+"'";
if(map_item.equals("XM")){
name= (String) item.get(map_item);
}
}else{
value = "'"+item.get(map_item).toString()+"'";
}
}
values =values+ "("+value+")";
}
//查询模板编号,用来查询表数据
MdDocumentTemplate pageList = mdDocumentTemplateMapper.seleect(mbLoanInformation.getId());
String table=pageList.getNum();
table="SANS_BDZS_"+table;
String sql="update "+table+" set "+updateString+" where XM='"+name+"'";
//执行SQL语句
jdbcTemplate.batchUpdate(sql);
}
}
四 查询数据
controller
/**
* 配置数据源
*/
DruidDataSource dataSource = DynamicDBUtil.getDbSourceByDbKey("dw_oracle");
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
/**
* 分页列表查询
*
* @param mbLoanInformation
* @return
*/
@AutoLog(value = "贷款信息-分页列表查询")
@ApiOperation(value = "贷款信息-分页列表查询", notes = "贷款信息-分页列表查询")
@GetMapping(value = "/list")
public Result<?> queryPageList(MbLoanInformation mbLoanInformation) throws ClassNotFoundException, SQLException {
//查询模板编号,用来查询表数据
MdDocumentTemplate pageList = mdDocumentTemplateService.sele(mbLoanInformation.getId());
String table=pageList.getNum();
table="SANS_BDZS_"+table;
//根据生成的表的名字和模板的id进行数据查询
String id=mbLoanInformation.getId();
String sql="select * from "+table+" where mb_document_template_id='"+id+"'";
//执行SQL语句
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
//从本地创建的表中查询
return Result.OK(list);
}