一、前言
UReport2是一款基于架构在Spring之上纯Java的高性能报表引擎,通过迭代单元格可以实现任意复杂的中国式报表。 在UReport2中,提供了全新的基于网页的报表设计器,可以在Chrome、Firefox、Edge等各种主流浏览器运行(IE浏览器除外)。使用UReport2,打开浏览器即可完成各种复杂报表的设计制作。 UReport2是第一款基于Apache-2.0开源协议的中式报表引擎,Github地址: https://github.com/youseries/ureport
二、Ureport2使用
这里过多介绍Spring Boot中整合Ureport2,使用具体不多做介绍,具体可以参考 《UReport2 教程》
三、Spring Boot整合Ureport2
3.1 添加依赖
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency>
<dependency> <groupId>com.bstek.ureport</groupId> <artifactId>ureport2-console</artifactId> <version>2.2.9</version> </dependency>
<dependency> <groupId>com.bstek.ureport</groupId> <artifactId>ureport2-core</artifactId> <version>2.2.9</version> </dependency>
|
3.2 servlet注册
1 2 3 4 5 6 7 8 9 10 11
| @Configuration @ImportResource("classpath:ureport-console-context.xml") public class WebConfig { @Bean public ServletRegistrationBean ureportServlrt() { ServletRegistrationBean bean = new ServletRegistrationBean(new UReportServlet()); bean.addUrlMappings("/ureport/*"); return bean; } }
|
注意
: 在这个 servlet 配置当中,值为“/ureport/*”的 url-pattern 是一定不能变的,否则系统将无法运行。
启动项目,访问 http://localhost:8080/ureport/designer ,显示Ureport2设计界面,说明整合成功。
四、内置数据源整合
4.1 引入依赖
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency>
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.22</version> </dependency> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>6.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency>
|
由于这里使用druid作为数据源,并且多数据源,所以引入了oracle、mysql、druid等。
4.2 修改application.yml
配置多数据源,具体参考 《Spring Boot JdbcTemplate配置Druid多数据源》
4.3 注册DataSource
创建DataSourceConfig.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| @Configuration public class DataSourceConfig { @Primary @Bean(name = "dataSourceKfReport") @ConfigurationProperties("spring.datasource.druid.kfreport") public DataSource dataSourceKfReport(){ return DruidDataSourceBuilder.create().build(); }
@Bean(name = "dataSourceKfProd") @ConfigurationProperties("spring.datasource.druid.kfprod") public DataSource dataSourceKfProd(){ return DruidDataSourceBuilder.create().build(); }
@Bean(name = "kfReportJdbcTemplate") public JdbcTemplate primaryJdbcTemplate( @Qualifier("dataSourceKfReport") DataSource dataSource) { return new JdbcTemplate(dataSource); }
@Bean(name = "kfProdJdbcTemplate") public JdbcTemplate secondaryJdbcTemplate( @Qualifier("dataSourceKfProd") DataSource dataSource) { return new JdbcTemplate(dataSource); } }
|
这里同时也注册了数据源对应的JdbcTemplate,后续报表文件保存到数据库会用到。
4.4 注册Ureport2内置数据源
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| @Configuration public class KfProdDatasource implements BuildinDatasource {
@Autowired @Qualifier("dataSourceKfProd") DataSource dataSource;
@Override public String name() { return "kfprod"; }
@Override public Connection getConnection() { try { Connection conn =dataSource.getConnection(); return conn; } catch (SQLException e) { e.printStackTrace(); } return null; } }
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| @Configuration public class KfReportDatasource implements BuildinDatasource {
@Autowired @Qualifier("dataSourceKfReport") DataSource dataSource;
@Override public String name() { return "kfreport"; }
@Override public Connection getConnection() { try { Connection conn =dataSource.getConnection(); return conn; } catch (SQLException e) { e.printStackTrace(); } return null; } }
|
这里注册了两个内置数据源,如下图:
五、修改内置服务器文件系统存储位置
如果您的项目在Eclipse的开发环境运行时,采用的是jetty(比如run-jetty-run插件),那么就可以在项目的WEB-INF目录下发现一个名为“ureportfiles”目录。但如果你采用tomcat运行项目,那么在WEB-INF目录下就没有一个名为“ureportfiles”目录,原因是在Eclipse中运行tomcat,tomcat需要创建一个临时的工作目录(该目录一般位于workspace.metadata.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\下),所以采用tomcat运行项目,则需要到这个临时的工作目录下找到对应的项目,再到这个项目的WEB-INF目录下找到对应的“ureportfiles”目录。
5.1 修改application.yml
新增以下内容:
1 2 3 4 5
| ureport: disableHttpSessionReportCache: false disableFileProvider: false fileStoreDir: E:/workspace/file debug: true
|
fileStoreDir:文件服务器存储路径
5.2 yml配置转换为Property
1 2 3 4 5 6 7 8 9
| public class MyUReportPropertyConfig extends UReportPropertyPlaceholderConfigurer {
public MyUReportPropertyConfig(String path) { YamlPropertiesFactoryBean yaml = new YamlPropertiesFactoryBean(); yaml.setResources(new ClassPathResource(path)); this.setProperties(yaml.getObject()); } }
|
5.3 注册配置
将上面WebConfig改造成下面代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| @Configuration @ImportResource("classpath:ureport-console-context.xml") public class WebConfig implements EnvironmentAware {
private Environment environment;
@Override public void setEnvironment(Environment environment) { this.environment = environment; }
@Bean public MyUReportPropertyConfig propertyrConfigurer(){ String activeProfile = environment.getProperty("spring.profiles.active"); if(StringUtils.isNotBlank(activeProfile)){ activeProfile = "-" + activeProfile; }else if(activeProfile==null){ activeProfile = ""; } return new MyUReportPropertyConfig("application" + activeProfile + ".yml"); } @Bean public ServletRegistrationBean ureportServlrt() { ServletRegistrationBean bean = new ServletRegistrationBean(new UReportServlet()); bean.addUrlMappings("/ureport/*"); return bean; }
}
|
5.4 测试验证
创建一个报表保存到服务器文件系统,如下所示:
查看本地路径E:/workspace/file:
六、新增数据库文件系统
6.1 在数据库创建记录表
1 2 3 4 5 6 7 8
| CREATE TABLE `wcsdb`.`ureport2file` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `content` mediumblob, `create_time` timestamp(0) DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
|
6.2 创建对应实体
1 2 3 4 5 6 7 8 9 10
| @Data public class UreportFileEntity {
private Long id; private String name; private byte[] content; private Date createTime; private Date updateTime;
}
|
6.3 实体操作service
ReportFileService.java
1 2 3 4 5 6 7
| public interface ReportFileService { UreportFileEntity queryUreportFileEntityByName(String name); void deleteReportFileByName(String name); List<UreportFileEntity> queryReportFileList(); void insertReportFile(UreportFileEntity file); void updateReportFile(UreportFileEntity file); }
|
具体实现类:
ReportFileServiceImpl.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| @Service @Slf4j public class ReportFileServiceImpl implements ReportFileService {
@Autowired @Qualifier("kfReportJdbcTemplate") private JdbcTemplate jdbcTemplate;
@Override public UreportFileEntity queryUreportFileEntityByName(String name) { String sql = "select * from ureport2file t where t.name = ? "; Object[] args = new Object[] {name}; log.info("getRunFaultCount-sql:{}", sql); List<UreportFileEntity> query = this.jdbcTemplate.query(sql,args,new RowMapper<UreportFileEntity>(){ @Override public UreportFileEntity mapRow(ResultSet rs, int rowNum) throws SQLException { UreportFileEntity uf = new UreportFileEntity(); uf.setId(rs.getLong("id")); uf.setName(rs.getString("name")); uf.setContent(rs.getBytes("content")); uf.setCreateTime(rs.getDate("create_time")); uf.setUpdateTime(rs.getDate("update_time")); return uf; } });
return query.get(0); }
@Override public void deleteReportFileByName(String name) { String sql = "delete from ureport2file where name = ? "; Object[] args = new Object[] {name}; log.info("getRunFaultCount-sql:{}", sql); this.jdbcTemplate.update(sql, args); }
@Override public List<UreportFileEntity> queryReportFileList() { String sql = "select * from ureport2file t ";
List<UreportFileEntity> query = this.jdbcTemplate.query(sql,new RowMapper<UreportFileEntity>(){
@Override public UreportFileEntity mapRow(ResultSet rs, int rowNum) throws SQLException { UreportFileEntity uf = new UreportFileEntity(); uf.setId(rs.getLong("id")); uf.setName(rs.getString("name")); uf.setContent(rs.getBytes("content")); uf.setCreateTime(rs.getDate("create_time")); uf.setUpdateTime(rs.getDate("update_time")); return uf; } }); if(query.size()==0) return null; return query; }
@Override public void insertReportFile(UreportFileEntity file) { String sql = "INSERT INTO ureport2file (name,content,create_time) VALUES (?,?,NOW())"; Object[] args = new Object[] {file.getName(),file.getContent()}; this.jdbcTemplate.update(sql,args); }
@Override public void updateReportFile(UreportFileEntity file) { String sql = "update ureport2file t set t.name = ?, t.content = ?, t.update_time = now() where t.id = ? "; Object[] args = new Object[] {file.getName(),file.getContent(),file.getId()}; this.jdbcTemplate.update(sql,args); }
}
|
6.4 实现ReportProvider
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
| @Component public class SqlProvider implements ReportProvider {
private static final String name = "青海客服报表系统";
private boolean disable = false;
private String prefix = "qh-ics-";
@Autowired private ReportFileService reportFileService;
/** * 根据报表名加载报表文件 * @param file 报表名称 * @return 返回的InputStream */ @Override public InputStream loadReport(String file) { String realName = getNoCorrectName(file); UreportFileEntity ureportFileEntity = reportFileService.queryUreportFileEntityByName(realName); byte[] content = new byte[0]; if (ureportFileEntity != null) { content = ureportFileEntity.getContent(); } return new ByteArrayInputStream(content); }
/** * 根据报表名删除报表文件 * @param file 报表名称 */ @Override public void deleteReport(String file) { if (file != null){ file = getNoCorrectName(file); reportFileService.deleteReportFileByName(file); } }
/** * 获取所有的报表文件 * @return 返回报表文件列表 */ @Override public List<ReportFile> getReportFiles() { List<UreportFileEntity> ureportFileEntities = reportFileService.queryReportFileList(); List<ReportFile> reportFiles = new ArrayList<ReportFile>(); for (UreportFileEntity ufe : ureportFileEntities){ reportFiles.add(new ReportFile(ufe.getName(), ufe.getUpdateTime())); } return reportFiles; }
/** * 保存报表文件 * @param file 报表名称 * @param content 报表的XML内容 */ @Override public void saveReport(String file, String content) { file = getNoCorrectName(file); UreportFileEntity ureportFileEntity = reportFileService.queryUreportFileEntityByName(file); Date date = new Date(); if (ureportFileEntity == null){ ureportFileEntity = new UreportFileEntity(); ureportFileEntity.setContent(content.getBytes()); ureportFileEntity.setUpdateTime(date); ureportFileEntity.setCreateTime(date); ureportFileEntity.setName(file); reportFileService.insertReportFile(ureportFileEntity); }else { ureportFileEntity.setContent(content.getBytes()); ureportFileEntity.setUpdateTime(date); reportFileService.updateReportFile(ureportFileEntity); } }
/** * @return 返回存储器名称 */ @Override public String getName() { return this.name; }
/** * @return 返回是否禁用 */ @Override public boolean disabled() { return this.disable; }
/** * @return 返回报表文件名前缀 */ @Override public String getPrefix() { return this.prefix; }
private String getNoCorrectName(String name){ if(name.startsWith(prefix)){ name = name.substring(prefix.length(), name.length()); } return name; } }
|
6.5 测试验证
打开另存为,或者打开,发现已经有新增的数据库文件系统。
新增一个报表试一下
重新打开删除一切测试ok。
七、Ureport2常用路径
7.1 报表设计
http://localhost:8080/ureport/designer
7.2 报表访问
文件系统报表路径
http://localhost:8080/ureport/preview?_u=file:{filename}
filename:报表文件名字
示例: http://localhost:8080/ureport/preview?_u=file:t_user.ureport.xml&_i=1&
数据库系统或者三方系统路径
http://localhost:8080/ureport/preview?_u={prefix}{filename}.ureport.xml
prefix:新增文件系统前缀,具体为配置类中的getPrefix()方法返回值
filename:报表文件名字
示例: http://localhost:8080/ureport/preview?_u=qh-ics-wf_participant.ureport.xml&_i=1&