目录
一 创建表和前端动态传入字段名添加表字段--记得添加自己的数据库
一 创建表和前端动态传入字段名添加表字段--记得添加自己的数据库
(一) 传入id查询表中数据
// 根据前端传的字段和创建表
String driver="com.mysql.cj.jdbc.Driver";
//加载驱动
Class.forName(driver);
//数据库地址，本机、端口号3306、数据库名为test
String url="jdbc:mysql:///lhbank?useUnicode=true&useCharacter=utf8&useSSL=true";
//用户名
String user=" ";
//密码
String pwd=" ";
//连接数据库
Connection conn= DriverManager.getConnection(url,user,pwd);
//创建Statement对象
Statement statement=conn.createStatement();
//查询模板编号，用来做表名
MdDocumentTemplateVo documentTemplateVo=mdDocumentTemplateMapper.slee(templateVo.getId());
//从表获取名字当作创建新表的表名
String number=documentTemplateVo.getNumber();
//创建表
String sql="CREATE TABLE if not exists "+ number+
"(id varchar(256) not NULL, " +
"mb_document_template_id varchar(256), " +
"status varchar(256), " +
"create_by varchar(256), " +
"create_time datetime, " +
" PRIMARY KEY ( id ))";
statement.executeUpdate(sql);
System.out.println( statement.executeUpdate(sql));
mdDocumentTemplateMapper.updatelist(templateVo);
String mbDocumentTemplateId=templateVo.getId();
//先查询表中的数据条数
List<MbFormElements> selec=mbFormElementsMapper.selectListid(mbDocumentTemplateId);
//表单元素
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 if(select==null && name.size()==1){
mbFormElementsMapper.updateById(entry);
}else {
String s = UUID.randomUUID().toString().replace("-", "");
entry.setId(s);
entry.setMbDocumentTemplateId(templateVo.getId());
String name1=entry.getElementId();
//添加字段这里是循环添加
String aqladd="ALTER TABLE "+number+" ADD "+name1+" varchar(256)";
//运行sql字段添加进表
statement.executeUpdate(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);
}
}二 前端传应该集合，向表添加数据
/**
* 添加
*
* @param mbLoanInformation
* @return
*/
@AutoLog(value = "贷款信息-添加")
@ApiOperation(value = "贷款信息-添加", notes = "贷款信息-添加")
@PostMapping(value = "/add")
public Result<?> add(@RequestBody MbLoanInformationVo mbLoanInformation) throws ClassNotFoundException, SQLException {
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+")";
}
System.out.println(column);
System.out.println(values);
// 根据前端传的字段和创建表
String driver="com.mysql.cj.jdbc.Driver";
//加载驱动
Class.forName(driver);
//数据库地址，本机、端口号3306、数据库名为test
String url="jdbc:mysql:///lhbank?useUnicode=true&useCharacter=utf8&useSSL=true";
//用户名
String user=" ";
//密码
String pwd=" ";
//连接数据库
Connection conn= DriverManager.getConnection(url,user,pwd);
//创建Statement对象
Statement statement=conn.createStatement();
//查询模板编号，用来查询表数据
MdDocumentTemplate pageList = mdDocumentTemplateService.sele(mbLoanInformation.getId());
String table=pageList.getNumber();
String sql="insert into "+table+"("+column+") values"+values;
System.out.println(sql);
//执行SQL语句
statement.executeUpdate(sql);
//关闭流
statement.close();
conn.close();
//有数据表
for(MbLoanInformation entry:mbLoanInformation.getMdList()){
String mbi=mbLoanInformation.getId();
entry.setId(id);
entry.setMbDocumentTemplateId(mbi);
entry.setStatus("1");
mbLoanInformationService.save(entry);
}
return Result.OK("添加成功！");
}三 传入id进行查询数据
/**
* 分页列表查询
*
* @param mbLoanInformation
* @return
*/
@AutoLog(value = "贷款信息-分页列表查询")
@ApiOperation(value = "贷款信息-分页列表查询", notes = "贷款信息-分页列表查询")
@GetMapping(value = "/list")
public Result<?> queryPageList(MbLoanInformation mbLoanInformation) throws ClassNotFoundException, SQLException {
String driver="com.mysql.cj.jdbc.Driver";
//加载驱动
Class.forName(driver);
//数据库地址，本机、端口号3306、数据库名为test
String url="jdbc:mysql:///lhbank?useUnicode=true&useCharacter=utf8&useSSL=true";
//用户名
String user=" ";
//密码
String pwd=" ";
//连接数据库
Connection conn= DriverManager.getConnection(url,user,pwd);
//创建Statement对象
Statement statement=conn.createStatement();
//查询模板编号，用来查询表数据
MdDocumentTemplate pageList = mdDocumentTemplateService.sele(mbLoanInformation.getId());
String table=pageList.getNumber();
//根据生成的表的名字和模板的id进行数据查询
String id=mbLoanInformation.getId();
String sql="select * from "+table+" where mb_document_template_id='"+id+"'";
System.out.println(sql);
//执行SQL语句
ResultSet rs=statement.executeQuery(sql);
//把查询到的数据存入list，传给前端
List list = new ArrayList();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while (rs.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
//关闭流
statement.close();
conn.close();
//从本地创建的表中查询
List<MbLoanInformation> pageLis = mbLoanInformationService.sel(mbLoanInformation);
return Result.OK(list);
}四，编辑修改数据
/**
* 编辑
*
* @param mbLoanInformation
* @return
*/
@AutoLog(value = "贷款信息-编辑")
@ApiOperation(value = "贷款信息-编辑", notes = "贷款信息-编辑")
@PutMapping(value = "/edit")
public Result<?> edit(@RequestBody MbLoanInformationVo mbLoanInformation) throws ClassNotFoundException, SQLException {
List<LinkedHashMap<Object,Object>> collect = (List) mbLoanInformation.getMbList().stream().limit(10).collect(Collectors.toList());
String column = "";
String values = "";
String updateString = "";
String date= DateUtils.formatDateTime();
for (LinkedHashMap<Object,Object> item : collect ) {
String value = "";
for(Object map_item : item.keySet()){
System.out.println(column.indexOf(map_item.toString()));
//循环获取字段名
item.put("create_time",date);
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()+" =values("+map_item.toString()+")";
}else{
updateString = map_item.toString()+" =values("+map_item.toString()+")".toString();
}
}
if(!value.equals("")){
value = value+",'"+item.get(map_item)+"'";
}else{
value = "'"+item.get(map_item).toString()+"'";
}
}
values =values+ "("+value+")";
}
System.out.println(column);
System.out.println(values);
// 根据前端传的字段和创建表
String driver="com.mysql.cj.jdbc.Driver";
//加载驱动
Class.forName(driver);
//数据库地址，本机、端口号3306、数据库名为test
String url="jdbc:mysql:///lhbank?useUnicode=true&useCharacter=utf8&useSSL=true";
//用户名
String user=" ";
//密码
String pwd=" ";
//连接数据库
Connection conn= DriverManager.getConnection(url,user,pwd);
//创建Statement对象
Statement statement=conn.createStatement();
//查询模板编号，用来查询表数据
MdDocumentTemplate pageList = mdDocumentTemplateService.sele(mbLoanInformation.getId());
String table=pageList.getNumber();
//修改表数据 有进行修改 没有进行新增
String sql="insert into "+table+"("+column+") values "+values+" on DUPLICATE KEY UPDATE "+updateString ;
System.out.println(sql);
//执行SQL语句
statement.executeUpdate(sql);
//关闭流
statement.close();
conn.close();
//向本地手动创建的表添加数据进行备份
for(MbLoanInformation entry:mbLoanInformation.getMdList()){
entry.setCreateTime(date);
mbLoanInformationService.updateById(entry);
}
return Result.OK("编辑成功!");
}五 进行数据文档下载
模板例子
代码如下
/**
* 下载文档
* @throws IOException
* @return
*/
@RequestMapping(value = "/export")
public void replace(HttpServletResponse request, MbLoanInformationVo mbLoanInformation) throws IOException {
//首先制作一个下载文档的模板
String path="D:\\opt\\upFiles\\"+mbLoanInformation.getDocumentTemplate();
//根据id去表获取要导出的值
MbLoanInformationVo mbLoanInformation1=mbLoanInformationService.select(mbLoanInformation.getId());
String name=mbLoanInformation1.getXm();
String data= DateUtils.formatDate();
String datedser=data.replace("-","");
String outPath = "temp\\"+name+datedser+".xlsx";
//替换的字符串 {name} -> 小明
String phone=null;
String idCard=null;
String LoanTerm=null;
String LoanAmount=null;
String Guarantee=null;
String Address=null;
if(mbLoanInformation1.getSjhm()==null){
phone="";
}else {
phone=mbLoanInformation1.getSjhm();
}
if(mbLoanInformation1.getSfz()==null){
idCard="";
}else {
idCard=mbLoanInformation1.getSfz();
}
if(mbLoanInformation1.getJkqx()==null){
LoanTerm="";
}else {
LoanTerm=mbLoanInformation1.getJkqx();
}
if(mbLoanInformation1.getJkje()==null){
LoanAmount="";
}else {
LoanAmount=mbLoanInformation1.getJkje();
}
if(mbLoanInformation1.getDbr()==null){
Guarantee="";
}else {
Guarantee=mbLoanInformation1.getDbr();
}
if(mbLoanInformation1.getDz()==null){
Address="";
}else {
Address=mbLoanInformation1.getDz();
}
Map<String, Object> params = new HashMap<String, Object>();
//进行值替换
params.put("姓名", name);
params.put("手机号码", phone);
params.put("身份证号" ,idCard);
params.put("借款期限", LoanTerm);
params.put("借款金额", LoanAmount);
params.put("担保人姓名" ,Guarantee);
params.put("借款人地址", Address);
replaceExcel(request,path,outPath,params);
}
public void replaceExcel(HttpServletResponse request,String inPath, String outPath, Map params) throws IOException, InvalidFormatException {
InputStream is = new FileInputStream(inPath);
Workbook wb = WorkbookFactory.create(is);
Sheet sheet = wb.getSheetAt(0);//获取Excel的工作表sheet，下标从0开始。
//int trLength = sheet.getLastRowNum();//获取Excel的行数
int trLength =sheet.getPhysicalNumberOfRows();//获得总行数
for (int i = 0; i < trLength; i++) {
Row row = sheet.getRow(i);//获取Excel的行，下标从0开始
if (row == null) {//若行为空，则遍历下一行
continue;
}
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
Cell cell = row.getCell(colIx);//获取指定单元格，单元格从左到右下标从0开始
//String runText = cell.getStringCellValue();
String runText="";
if (cell.getCellType().equals(CellType.STRING)){
runText = cell.getStringCellValue();
}
if (cell.getCellType().equals(CellType.NUMERIC)){
runText =String.valueOf(cell.getNumericCellValue());
}
//NUMERIC
if (runText.equals("")){
continue;
}
System.out.println(cell);
Matcher matcher = this.matcher(runText);
if (matcher.find()) {
while ((matcher = this.matcher(runText)).find()) {
runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));
}
cell.setCellValue(runText);
}
}
}
ServletOutputStream out = request.getOutputStream();
wb.write(out);
is.close();
out.close();
}
/**
* 正则匹配字符串
* @param str
* @return
*/
private Matcher matcher(String str) {
Pattern pattern = Pattern.compile("\\{(.+?)\\}", Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(str);
return matcher;
}
302
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
