JasperReport-查询数据库生成动态的报表
1、使用iReport建立模版
1 <?xml version="1.0" encoding="UTF-8"?> 2 <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="mcOrderReport" language="groovy" pageWidth="623" pageHeight="396" orientation="Landscape" columnWidth="595" leftMargin="14" rightMargin="14" topMargin="14" bottomMargin="14" uuid="b61e5406-4ec2-4222-83bf-8cd59cfda8e8"> 3 <property name="ireport.zoom" value="3.0"/> 4 <property name="ireport.x" value="0"/> 5 <property name="ireport.y" value="0"/> 6 <subDataset name="New Dataset 1" uuid="d4c4aea0-d329-4ad0-a5c1-cc228958ac9b"> 7 <parameter name="id" class="java.lang.String"/> 8 <queryString> 9 <![CDATA[select province as province, city as city,country as country from d_doctor where id = $P{id} or id = ${id} + 4]]> 10 </queryString> 11 <field name="province" class="java.lang.String"/> 12 <field name="city" class="java.lang.String"/> 13 <field name="country" class="java.lang.String"/> 14 </subDataset> 15 <parameter name="id" class="java.lang.String"/> 16 <queryString> 17 <![CDATA[select * from d_doctor where id = $P{id} or id = $P{id} + 4]]> 18 </queryString> 19 <field name="id" class="java.lang.String"> 20 <fieldDescription><![CDATA[id]]></fieldDescription> 21 </field> 22 <field name="province" class="java.lang.String"> 23 <fieldDescription><![CDATA[province]]></fieldDescription> 24 </field> 25 <field name="city" class="java.lang.String"> 26 <fieldDescription><![CDATA[city]]></fieldDescription> 27 </field> 28 <field name="country" class="java.lang.String"> 29 <fieldDescription><![CDATA[country]]></fieldDescription> 30 </field> 31 <background> 32 <band splitType="Stretch"/> 33 </background> 34 <title> 35 <band splitType="Stretch"/> 36 </title> 37 <pageHeader> 38 <band height="70" splitType="Stretch"> 39 <line> 40 <reportElement x="2" y="56" width="570" height="1" uuid="2c31ac2f-b3ee-4a13-b817-8f59d57494b4"/> 41 </line> 42 <staticText> 43 <reportElement x="3" y="57" width="249" height="12" uuid="a302e579-9117-466f-b498-9e0637079612"/> 44 <textElement textAlignment="Center"> 45 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 46 </textElement> 47 <text><![CDATA[省份号]]></text> 48 </staticText> 49 <line> 50 <reportElement x="2" y="69" width="570" height="1" uuid="0bb82c6b-4cf9-4abe-95d4-d9aa08c86729"/> 51 </line> 52 <line> 53 <reportElement x="463" y="56" width="1" height="13" uuid="75ff2c78-c753-4913-b656-e807371b64ca"/> 54 </line> 55 <line> 56 <reportElement x="252" y="56" width="1" height="13" uuid="21e66f87-54b5-4f4a-ab44-d158db895c6a"/> 57 </line> 58 <line> 59 <reportElement x="2" y="57" width="1" height="12" uuid="01c3fa5e-1cb0-4df4-a4ee-d9f3d617eb1c"/> 60 </line> 61 <staticText> 62 <reportElement x="254" y="57" width="40" height="12" uuid="a859627c-6381-46f2-9868-d67b3196ab28"/> 63 <textElement textAlignment="Center"> 64 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 65 </textElement> 66 <text><![CDATA[城市号]]></text> 67 </staticText> 68 <line> 69 <reportElement x="296" y="56" width="1" height="13" uuid="dcd28325-01fd-495b-8d51-a12a4f25a98b"/> 70 </line> 71 <staticText> 72 <reportElement x="340" y="57" width="60" height="12" uuid="b6908079-bc51-4190-a54c-de53c8f9c27f"/> 73 <textElement textAlignment="Center" verticalAlignment="Top"> 74 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 75 </textElement> 76 <text><![CDATA[单价]]></text> 77 </staticText> 78 <line> 79 <reportElement x="338" y="57" width="1" height="12" uuid="b52c8f2a-54c5-4075-ad72-6714fa977e1a"/> 80 </line> 81 <staticText> 82 <reportElement x="403" y="57" width="60" height="12" uuid="d10c2fcb-3b5f-4b0b-bb80-194aa4260e46"/> 83 <textElement textAlignment="Center"> 84 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 85 </textElement> 86 <text><![CDATA[优惠方式]]></text> 87 </staticText> 88 <line> 89 <reportElement x="401" y="57" width="1" height="12" uuid="bd6b257f-b1c0-4ed6-a1cf-9e5c26d5c566"/> 90 </line> 91 <staticText> 92 <reportElement x="526" y="57" width="46" height="12" uuid="972638b9-6e6e-44d0-af9f-e6c446ffae2f"/> 93 <textElement textAlignment="Center"> 94 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 95 </textElement> 96 <text><![CDATA[备注]]></text> 97 </staticText> 98 <staticText> 99 <reportElement x="3" y="18" width="41" height="12" uuid="8b205b2f-4151-4ab4-8f8a-763f25ec80e4"/> 100 <textElement> 101 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 102 </textElement> 103 <text><![CDATA[医生编号]]></text> 104 </staticText> 105 <staticText> 106 <reportElement x="208" y="18" width="63" height="12" uuid="f024c743-13bc-4ec5-a3d1-07ac2e0b37cc"/> 107 <textElement> 108 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 109 </textElement> 110 <text><![CDATA[订单金额]]></text> 111 </staticText> 112 <staticText> 113 <reportElement x="3" y="30" width="41" height="12" uuid="5f6a1789-6546-457f-86ed-d2e8a2e13d5b"/> 114 <textElement> 115 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 116 </textElement> 117 <text><![CDATA[下单时间]]></text> 118 </staticText> 119 <staticText> 120 <reportElement x="3" y="42" width="41" height="12" uuid="0c8f51b5-7948-4990-b5e8-20e5c4a0d50b"/> 121 <textElement> 122 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 123 </textElement> 124 <text><![CDATA[门店名称]]></text> 125 </staticText> 126 <textField> 127 <reportElement x="3" y="0" width="569" height="17" uuid="92ba8587-c49b-42c2-b59c-f826c1748283"/> 128 <textElement textAlignment="Center" markup="none"> 129 <font fontName="新宋体" size="12" isBold="true" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 130 </textElement> 131 </textField> 132 <textField> 133 <reportElement x="51" y="18" width="151" height="12" uuid="c9b3dc52-d1ff-47f9-a2e4-5ebf01d05675"/> 134 <textElement> 135 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 136 </textElement> 137 <textFieldExpression><![CDATA[$P{id}]]></textFieldExpression> 138 </textField> 139 <textField> 140 <reportElement x="273" y="18" width="125" height="12" uuid="c24f24dd-5bea-4866-8639-79f9871560a8"/> 141 <textElement> 142 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 143 </textElement> 144 </textField> 145 <textField> 146 <reportElement x="51" y="30" width="151" height="12" uuid="33dd35af-3c7b-45ce-a770-7b5191eef598"/> 147 <textElement> 148 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 149 </textElement> 150 </textField> 151 <textField> 152 <reportElement x="51" y="42" width="151" height="12" uuid="dc0495d6-be69-48ff-826a-4ba3cf47d037"/> 153 <textElement> 154 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 155 </textElement> 156 </textField> 157 <textField> 158 <reportElement x="208" y="42" width="63" height="12" uuid="c48ffb0e-9285-499f-82ab-5aeb0d98bbf6"/> 159 <textElement> 160 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 161 </textElement> 162 </textField> 163 <staticText> 164 <reportElement x="413" y="18" width="41" height="12" uuid="0abead11-fc66-46a9-826b-6e59629d35d7"/> 165 <textElement> 166 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 167 </textElement> 168 <text><![CDATA[支付方式]]></text> 169 </staticText> 170 <textField> 171 <reportElement x="456" y="18" width="116" height="12" uuid="e9b0d297-f8a6-437d-9091-968d7f22a62d"/> 172 <textElement> 173 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 174 </textElement> 175 <textFieldExpression><![CDATA[]]></textFieldExpression> 176 </textField> 177 <staticText> 178 <reportElement x="208" y="30" width="63" height="12" uuid="85d7941f-0194-4737-9638-d88d059d0904"/> 179 <textElement> 180 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 181 </textElement> 182 <text><![CDATA[送货员工]]></text> 183 </staticText> 184 <textField> 185 <reportElement x="273" y="30" width="125" height="12" uuid="811eb5e5-582c-4e3a-bf26-723a67770669"/> 186 <textElement> 187 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 188 </textElement> 189 </textField> 190 <staticText> 191 <reportElement x="297" y="57" width="40" height="12" uuid="d5bac62f-958e-4142-b4ae-9475f7cbf6d0"/> 192 <textElement textAlignment="Center"> 193 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 194 </textElement> 195 <text><![CDATA[县镇号]]></text> 196 </staticText> 197 <line> 198 <reportElement x="572" y="56" width="1" height="14" uuid="eeb1072f-a162-4906-a943-41eef70d307b"/> 199 </line> 200 <textField> 201 <reportElement x="271" y="42" width="301" height="12" uuid="7a13580f-791a-470b-a5e6-3f0b5bab9e04"/> 202 <textElement> 203 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 204 </textElement> 205 </textField> 206 <line> 207 <reportElement x="525" y="57" width="1" height="12" uuid="8f2c02b6-d3ab-4ae5-8b2e-f0d2b11b4243"/> 208 </line> 209 <staticText> 210 <reportElement x="465" y="57" width="60" height="12" uuid="141e20d9-4803-4afa-a1eb-45bec248fe50"/> 211 <textElement textAlignment="Center"> 212 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 213 </textElement> 214 <text><![CDATA[小计]]></text> 215 </staticText> 216 </band> 217 </pageHeader> 218 <detail> 219 <band height="13" splitType="Stretch"> 220 <line> 221 <reportElement x="2" y="-1" width="1" height="13" uuid="acf19d0f-20e1-4193-adec-2df290300480"/> 222 </line> 223 <textField> 224 <reportElement x="254" y="0" width="40" height="12" uuid="4409e74b-a023-455c-95c5-883103050a1c"/> 225 <textElement textAlignment="Center"> 226 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 227 </textElement> 228 <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression> 229 </textField> 230 <textField> 231 <reportElement x="297" y="0" width="40" height="12" uuid="edd7680d-fdbe-4e92-b6f4-c238a2b4c739"/> 232 <textElement textAlignment="Center"> 233 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 234 </textElement> 235 <textFieldExpression><![CDATA[$F{country}]]></textFieldExpression> 236 </textField> 237 <textField> 238 <reportElement x="340" y="0" width="60" height="12" uuid="61ad7ab8-62a0-43cb-8135-54ade9f98e93"/> 239 <textElement textAlignment="Center"> 240 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 241 </textElement> 242 </textField> 243 <line> 244 <reportElement x="338" y="0" width="1" height="12" uuid="f0d7806d-4302-4c41-aa31-1a31cdd25365"/> 245 </line> 246 <textField> 247 <reportElement x="403" y="0" width="60" height="12" uuid="8895c53f-742c-45d4-ab43-ec08daf00d4f"/> 248 <textElement textAlignment="Center"> 249 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 250 </textElement> 251 <textFieldExpression><![CDATA[]]></textFieldExpression> 252 </textField> 253 <line> 254 <reportElement x="401" y="0" width="1" height="12" uuid="9efa8bc0-851f-4374-a68b-f5c13fea0595"/> 255 </line> 256 <textField> 257 <reportElement x="465" y="0" width="60" height="12" uuid="2ee6ccc9-0954-40ba-9843-0847832180d0"/> 258 <textElement textAlignment="Center"> 259 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 260 </textElement> 261 <textFieldExpression><![CDATA[]]></textFieldExpression> 262 </textField> 263 <line> 264 <reportElement x="2" y="12" width="571" height="1" uuid="5930b04c-3f37-4225-ad65-6b5291bb9ffa"/> 265 </line> 266 <textField> 267 <reportElement x="6" y="0" width="15" height="12" uuid="28829cd2-12fb-4472-b351-a33a7ce41dce"/> 268 <textElement> 269 <font fontName="新宋体" size="8"/> 270 </textElement> 271 </textField> 272 <line> 273 <reportElement x="572" y="0" width="1" height="12" uuid="9b737c67-28cf-4cae-9424-b744a7cecacd"/> 274 </line> 275 <textField> 276 <reportElement x="21" y="0" width="231" height="12" uuid="992e0d0c-75a1-43b3-949f-16d03cdcdf71"/> 277 <textElement> 278 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 279 </textElement> 280 <textFieldExpression><![CDATA[$F{province}]]></textFieldExpression> 281 </textField> 282 <line> 283 <reportElement x="252" y="0" width="1" height="12" uuid="0327fe36-18b9-4018-bf8a-39a87aa68abc"/> 284 </line> 285 <line> 286 <reportElement x="296" y="0" width="1" height="12" uuid="12556171-4ec5-4d5c-a773-7605d6809253"/> 287 </line> 288 <line> 289 <reportElement x="525" y="0" width="1" height="12" uuid="7625677e-4f2c-4629-8fcb-5c9bf5348c76"/> 290 </line> 291 <line> 292 <reportElement x="463" y="0" width="1" height="12" uuid="6439e322-33b4-46e8-9fc1-1fd80d068c42"/> 293 </line> 294 </band> 295 </detail> 296 <pageFooter> 297 <band/> 298 </pageFooter> 299 <summary> 300 <band height="15"> 301 <line> 302 <reportElement x="2" y="13" width="571" height="1" uuid="de33dda4-b0e9-4fc1-a5ac-35c6cb31d2a9"/> 303 </line> 304 <staticText> 305 <reportElement x="6" y="1" width="24" height="12" uuid="1ba3c283-f943-49b5-b50a-c474e8c74e3b"/> 306 <textElement> 307 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 308 </textElement> 309 <text><![CDATA[合计:]]></text> 310 </staticText> 311 <staticText> 312 <reportElement x="214" y="1" width="57" height="12" uuid="b81742a8-df49-4b2d-aaef-c52c12ba29c3"/> 313 <textElement> 314 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 315 </textElement> 316 <text><![CDATA[ 减免金额合计:]]></text> 317 </staticText> 318 <staticText> 319 <reportElement x="331" y="1" width="58" height="12" uuid="cf8d16f8-35da-4226-b123-7cd40b0357b8"/> 320 <textElement> 321 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 322 </textElement> 323 <text><![CDATA[实际金额合计:]]></text> 324 </staticText> 325 <textField> 326 <reportElement x="33" y="1" width="175" height="12" uuid="4774c656-fefe-4f46-b003-1764227080de"/> 327 <textElement> 328 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 329 </textElement> 330 </textField> 331 <textField> 332 <reportElement x="271" y="1" width="60" height="12" uuid="43a013d2-b695-4489-8b03-3e56e8d2d405"/> 333 <textElement> 334 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 335 </textElement> 336 </textField> 337 <textField> 338 <reportElement x="390" y="1" width="74" height="12" uuid="1c4b9418-0769-4eb5-8d4a-14c941580d4c"/> 339 <textElement> 340 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 341 </textElement> 342 <textFieldExpression><![CDATA[]]></textFieldExpression> 343 </textField> 344 <staticText> 345 <reportElement x="465" y="1" width="36" height="12" uuid="9259c923-872d-4277-891c-0139b6d3b056"/> 346 <textElement> 347 <font fontName="新宋体" size="8" pdfFontName="STSong-Light" pdfEncoding="UniGB-UCS2-H" isPdfEmbedded="true"/> 348 </textElement> 349 <text><![CDATA[客户签名:]]></text> 350 </staticText> 351 <line> 352 <reportElement x="2" y="-1" width="1" height="14" uuid="9287bb3c-6fc9-403f-ba48-17d07708ad8e"/> 353 </line> 354 <line> 355 <reportElement x="2" y="0" width="570" height="1" uuid="a8ee48a3-5a8e-4605-a0d6-e1b15f483d8e"/> 356 </line> 357 <line> 358 <reportElement x="572" y="0" width="1" height="13" uuid="fb41ac72-a0ce-4a49-9df7-9570d4a9bb72"/> 359 </line> 360 </band> 361 </summary> 362 </jasperReport>
2.直接在iReport中把上面的JRXML文件编译成JASPER文件，方便在WEB里面的调用；
3、函数中调用 ，即可输出符合条件的报表
1 package com.thinkgem.jeesite.test; 2 3 import java.io.FileInputStream; 4 import java.io.FileOutputStream; 5 import java.sql.DriverManager; 6 import java.util.HashMap; 7 import java.util.Map; 8 9 import com.mysql.jdbc.Connection; 10 11 import net.sf.jasperreports.engine.JasperPrint; 12 import net.sf.jasperreports.engine.JasperReport; 13 import net.sf.jasperreports.engine.JasperRunManager; 14 15 //@RunWith(SpringJUnit4ClassRunner.class) // 表示继承了SpringJUnit4ClassRunner类 16 //@ContextConfiguration(locations = { "classpath:spring-mybatis.xml" }) 17 public class Test 18 { 19 public static void main(String[] args) 20 { 21 JasperReport jasperReport; 22 JasperPrint jasperPrint; 23 System.out.println("1111"); 24 // jasperReport = 25 // JasperCompileManager.compileReport("mcOrderReport.jrxml"); 26 System.out.println("2222"); 27 // jasperReport.setProperty("billAmount", "102"); 28 // JRBeanCollectionDataSource dataSource = new 29 // JRBeanCollectionDataSource(); 30 Map map = new HashMap(); 31 map.put("id", 102);// 报表主参数，直接传值！ 32 33 // jasperPrint = JasperFillManager.fillReport(jasperReport, map, new 34 // JREmptyDataSource()); 35 // JasperExportManager.exportReportToPdfFile(jasperPrint, 36 // "forms/1.pdf"); 37 // JasperExportManager.exportReportToHtmlFile(jasperPrint, 38 // "forms/1.html"); 39 40 Connection connection = null; 41 try 42 { 43 // 获得输出流 44 FileOutputStream outputStream = new FileOutputStream("forms/2.pdf"); 45 // 获得输入流,一定要知道你的文件的地方 46 FileInputStream is = new FileInputStream("mcOrderReport.jasper"); 47 // 连结JDBC 48 Class.forName("com.mysql.jdbc.Driver"); 49 connection = (Connection) DriverManager.getConnection( 50 "jdbc:mysql://123.207.229.247:3306/palm_doctor?useUnicode=true&characterEncoding=utf-8", "root", 51 "root");// 建立数据库连接 52 JasperRunManager.runReportToPdfStream(is, outputStream, map, connection); 53 54 outputStream.flush(); 55 outputStream.close(); 56 connection.close(); 57 } catch (Exception e) 58 { 59 e.printStackTrace(); 60 } 61 } 62 }
