JDBC查询数据赋值麻烦问题

小咪咪 2022-01-28 22:51 192阅读 0赞

如果一次性查询很多字段，这样赋值的时候就会很麻烦，怎么解决这个问题呢，如果对字段没什么要求的话建议以下方法

StringBuffer sql=new StringBuffer("");

1：把查询sql复制到EXCEL中；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdndWFuZ25pdWJp_size_16_color_FFFFFF_t_70][]

在B列输入sql.append("

在D列输入");

复制粘贴到开发工具中

2:查询数据赋值

通过角标复制  第一个是1

while(rs.next()){
    				entity=new DBQueryIndex2Dto();
    				entity.setB1(rs.getString(1));
    				entity.setB2(rs.getString(2));
    				entity.setB3(rs.getString(3));
    				entity.setB4(rs.getString(4));
    				entity.setB5(rs.getString(5));
    				entity.setB6(rs.getString(6));
    				entity.setB7(rs.getString(7));
    				entity.setB8(rs.getString(8));
    				entity.setB9(rs.getDouble(9));
    				entity.setB10(rs.getDouble(10));
    				entity.setB11(rs.getDouble(11));
    				entity.setB12(rs.getDouble(12));
    				entity.setB13(rs.getDouble(13));
    				entity.setB14(rs.getDouble(14));
    				entity.setB15(rs.getDouble(15));
    				entity.setB16(rs.getDouble(16));
    				entity.setB17(rs.getDouble(17));
    				entity.setB18(rs.getDouble(18));
    				entity.setB19(rs.getDouble(19));
    				entity.setB20(rs.getDouble(20));
    				entity.setB21(rs.getDouble(21));
    				entity.setB22(rs.getDouble(22));
    				entity.setB23(rs.getDouble(23));
    				entity.setB24(rs.getDouble(24));
    				entity.setB25(rs.getDouble(25));
    				entity.setB26(rs.getDouble(26));
    				entity.setB27(rs.getDouble(27));
    				entity.setB28(rs.getDouble(28));
    				entity.setB29(rs.getDouble(29));
    				entity.setB30(rs.getDouble(30));
    				entity.setB31(rs.getDouble(31));
    				entity.setB32(rs.getDouble(32));
    				entity.setB33(rs.getDouble(33));
    				entity.setB34(rs.getDouble(34));
    				entity.setB35(rs.getDouble(35));
    				entity.setB36(rs.getDouble(36));
    				entity.setB37(rs.getDouble(37));
    				entity.setB38(rs.getDouble(38));
    				entity.setB39(rs.getDouble(39));
    				entity.setB40(rs.getDouble(40));
    				entity.setB41(rs.getDouble(41));
    				entity.setB42(rs.getDouble(42));
    				entity.setB43(rs.getDouble(43));
    				entity.setB44(rs.getDouble(44));
    				entity.setB45(rs.getDouble(45));
    				entity.setB46(rs.getDouble(46));
    				entity.setB47(rs.getDouble(47));
    				entity.setB48(rs.getDouble(48));
    				entity.setB49(rs.getDouble(49));
    				entity.setB50(rs.getDouble(50));
    				entity.setB51(rs.getDouble(51));
    				entity.setB52(rs.getDouble(52));
    				entity.setB53(rs.getDouble(53));
    				entity.setB54(rs.getDouble(54));
    				entity.setB55(rs.getDouble(55));
    				entity.setB56(rs.getDouble(56));
    				entity.setB57(rs.getDouble(57));
    				entity.setB58(rs.getDouble(58));
    				entity.setB59(rs.getDouble(59));
    				entity.setB60(rs.getDouble(60));
    				entity.setB61(rs.getDouble(61));
    				entity.setB62(rs.getDouble(62));
    				entity.setB63(rs.getDouble(63));
    				entity.setB64(rs.getDouble(64));
    				entity.setB65(rs.getDouble(65));
    				entity.setB66(rs.getDouble(66));
    				entity.setB67(rs.getDouble(67));
    				entity.setB68(rs.getDouble(68));
    				entity.setB69(rs.getDouble(69));
    				entity.setB70(rs.getDouble(70));
    				entity.setB71(rs.getDouble(71));
    				entity.setB72(rs.getDouble(72));
    				entity.setB73(rs.getDouble(73));
    				entity.setB74(rs.getDouble(74));
    				entity.setB75(rs.getDouble(75));
    				entity.setB76(rs.getDouble(76));
    				entity.setB77(rs.getDouble(77));
    				entity.setB78(rs.getDouble(78));
    				entity.setB79(rs.getDouble(79));
    				entity.setB80(rs.getDouble(80));
    				entity.setB81(rs.getDouble(81));
    				entity.setB82(rs.getDouble(82));
    				entity.setB83(rs.getDouble(83));
    				entity.setB84(rs.getDouble(84));
    				entity.setB85(rs.getDouble(85));
    				entity.setB86(rs.getDouble(86));
    				entity.setB87(rs.getDouble(87));
    				entity.setB88(rs.getDouble(88));
    				entity.setB89(rs.getDouble(89));
    				entity.setB90(rs.getDouble(90));
    				entity.setB91(rs.getDouble(91));
    				entity.setB92(rs.getDouble(92));
    				entity.setB93(rs.getDouble(93));
    				entity.setB94(rs.getDouble(94));
    				entity.setB95(rs.getDouble(95));
    				entity.setB96(rs.getDouble(96));
    				entity.setB97(rs.getDouble(97));
    				entity.setB98(rs.getDouble(98));
    				entity.setB99(rs.getDouble(99));
    				entity.setB100(rs.getDouble(100));
    				entity.setB101(rs.getDouble(101));
    				entity.setB102(rs.getDouble(102));
    				entity.setB103(rs.getDouble(103));
    				entity.setB104(rs.getDouble(104));
    				entity.setB105(rs.getDouble(105));
    				entity.setB106(rs.getDouble(106));
    				entity.setB107(rs.getDouble(107));
    				entity.setB108(rs.getDouble(108));
    				entity.setB109(rs.getDouble(109));
    				entity.setB110(rs.getDouble(110));
    				entity.setB111(rs.getDouble(111));
    				entity.setB112(rs.getDouble(112));
    				entity.setB113(rs.getDouble(113));
    				entity.setB114(rs.getDouble(114));
    				entity.setB115(rs.getDouble(115));
    				entity.setB116(rs.getDouble(116));
    				entity.setB117(rs.getDouble(117));
    				entity.setB118(rs.getDouble(118));
    				entity.setB119(rs.getDouble(119));
    				entity.setB120(rs.getDouble(120));
    				entity.setB121(rs.getDouble(121));
    				entity.setB122(rs.getDouble(122));
    				entity.setB123(rs.getDouble(123));
    				entity.setB124(rs.getDouble(124));
    				entity.setB125(rs.getDouble(125));
    				entity.setB126(rs.getDouble(126));
    				entity.setB127(rs.getDouble(127));
    				entity.setB128(rs.getDouble(128));
    				entity.setB129(rs.getDouble(129));
    				entity.setB130(rs.getDouble(130));
    				entity.setB131(rs.getDouble(131));
    				entity.setB132(rs.getDouble(132));
    				entity.setB133(rs.getDouble(133));
    				entity.setB134(rs.getDouble(134));
    				entity.setB135(rs.getDouble(135));
    				entity.setB136(rs.getDouble(136));
    				entity.setB137(rs.getDouble(137));
    				entity.setB138(rs.getDouble(138));
    				entity.setB139(rs.getDouble(139));
    				entity.setB140(rs.getDouble(140));
    				entity.setB141(rs.getDouble(141));
    				entity.setB142(rs.getDouble(142));
    				entity.setB143(rs.getDouble(143));
    				entity.setB144(rs.getDouble(144));
    				entity.setB145(rs.getDouble(145));
    				entity.setB146(rs.getDouble(146));
    				entity.setB147(rs.getDouble(147));
    				entity.setB148(rs.getDouble(148));
    				entity.setB149(rs.getDouble(149));
    				entity.setB150(rs.getDouble(150));
    				entity.setB151(rs.getDouble(151));
    				entity.setB152(rs.getDouble(152));
    				entity.setB153(rs.getDouble(153));
    				entity.setB154(rs.getDouble(154));
    				entity.setB155(rs.getDouble(155));
    				entity.setB156(rs.getDouble(156));
    				entity.setB157(rs.getDouble(157));
    				entity.setB158(rs.getDouble(158));
    				entity.setB159(rs.getDouble(159));
    				entity.setB160(rs.getDouble(160));
    				entity.setB161(rs.getDouble(161));
    				entity.setB162(rs.getDouble(162));
    				entity.setB163(rs.getDouble(163));
    				entity.setB164(rs.getDouble(164));
    				entity.setB165(rs.getDouble(165));
    				entity.setB166(rs.getDouble(166));
    				entity.setB167(rs.getDouble(167));
    				entity.setB168(rs.getDouble(168));
    				entity.setB169(rs.getDouble(169));
    				entity.setB170(rs.getDouble(170));
    				entity.setB171(rs.getDouble(171));
    				entity.setB172(rs.getDouble(172));
    				entity.setB173(rs.getDouble(173));
    				entity.setB174(rs.getDouble(174));
    				entity.setB175(rs.getDouble(175));
    				entity.setB176(rs.getDouble(176));
    				entity.setB177(rs.getDouble(177));
    				entity.setB178(rs.getDouble(178));
    				entity.setB179(rs.getDouble(179));
                    list.add(entity);
    			}

2：多个查询赋值

/**
    	 * 
    	 * @param startdate
    	 * @param enddate
    	 * @param datatype
    	 * @param path
    	 * @param fileName
    	 * @param modeFileName
    	 * @return
    	 */
    	public String makeExcel4(String startdate,String enddate,String datatype,String path,String fileName,String modeFileName){
    		path=path+File.separator+"DownLoad";
    		//导出excel路径
    		String newUrl=path+File.separator+fileName;
    		//下载路径
    		String pathUrl=File.separator+"DownLoad"+File.separator+fileName;
    		//模板路径
    		String filePath=path+File.separator+modeFileName;
    		XSSFWorkbook wb = null;  
    	    FileInputStream fis = null;
    	    File file = new File(filePath);
    	    try {  
                if (file != null) {  
                    fis = new FileInputStream(file);  
                    wb = new XSSFWorkbook(fis);  
                }  
            } catch (Exception e) {  
            	e.printStackTrace(); 
            } finally {  
                if (fis != null) {  
                    try {  
                        fis.close();  
                    } catch (IOException e) {  
                        e.printStackTrace();  
                    }  
                }  
            }
    	    DBManager dbManager=null;
    		//存放查询的sql数据
    		ResultSet rs=null;
    	    try{
    	    	dbManager=new DBManager();
    	    	dbManager.open(AppConfig.get("sysconst.reserveDataSource"));
    			XSSFSheet sheet=null;
    			sheet=wb.getSheetAt(5);
    			rs=queryACCSumpaid(startdate,dbManager,rs);
    			//单元格赋值方法  只需提供查询结果 开始行 写入的sheet
    			setCellValue(rs,1,sheet);
    			
    		    sheet.setForceFormulaRecalculation(true);
    			//
    			FileOutputStream fileOut;
    	        try {
    	            fileOut = new FileOutputStream(newUrl);  
    				wb.write(fileOut);
    				wb.setForceFormulaRecalculation(true);
    				fileOut.flush();
    				fileOut.close();
    				sheet=null;
    	        } catch (IOException e) {  
    	            e.printStackTrace();
    	        }
    	    }catch (Exception e) {
    		}finally{
    			try {
    				dbManager.close();
    				rs.close();
    			} catch (Exception e) {
    				e.printStackTrace();
    			}
    		}
    		return pathUrl;
    	}

/**
    	 * 赋值到EXCEL共用方法 
    	 * @param rs   查询的数据
    	 * @param startRow 开始赋值
    	 * @param sheet 要赋值的sheet页
    	 */
    	public void setCellValue(ResultSet rs,Integer startRow,XSSFSheet sheet){
    		try {
    			Row row = null;
    			Integer rowCount=0;//初始行数=rowCount+startRow rowCount每次加一
    			ResultSetMetaData rsmd = rs.getMetaData() ; //获取字段属性
    			int columnCount = rsmd.getColumnCount();//获取查询结果的总列数
    			String colTypeName="NUMBER";
    			int flag = -1;//确认字段类型是不是数字类型，不是-1表示是数字类型
    			//遍历查询的数据
    			while(rs.next()){
    				//创建行
    				row=sheet.createRow(rowCount+startRow);//EXCEL的开始行
    				for(int i=0;i<columnCount;i++){
    					colTypeName=rsmd.getColumnTypeName(i+1);//获取字段类型
    					flag = colTypeName.toUpperCase().indexOf("NUMBER");
    					if(flag==-1){//不是数字类型
    						//创建单元格并赋值
    						row.createCell(i).setCellValue(rs.getString(i+1));
    					}else{//数字类型
    						//创建单元格并赋值
    						row.createCell(i).setCellValue(rs.getDouble(i+1));
    					}					
    				}
    				rowCount++;
    			}
    			
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} 
    		
    	}

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdndWFuZ25pdWJp_size_16_color_FFFFFF_t_70]: /images/20220129/f83e96b4ce3b4150969546cae85d3ec4.png