本方案支持Oracle和MySQL
把数据库表资源导出成Excel共分几步?
3步:第一步,把表打开;第二步,一边查一边导、一边查一边导;第三步,关了那些流/连接
开玩笑的,实际上要好几步:
1.查询数据库信息、导出列名信息
2.获取jdbc连接
3.查询记录总数
4.设定页容量、起始第一页、总页数
5.组装分页查询的sql语句
6.分页查询，并写入Excel生成类里（在硬盘中缓存）
7.写入临时文件
一、前情提要
先贴出pom
<!-- Excel报表导出 easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
数据库信息和表名由人工填入，需要时在数据库里查询
表中列信息可以用以下方法查出后存储起来
"DatabaseMetaData类是java.sql包中的类，利用它可以获取我们连接到的数据库的结构、存储等很多信息"
/**
* 获取数据源信息
*
* @param driver 驱动
* @param url url
* @param username username
* @param password password
* @return DataSource
*/
private static DataSource getDataSource(String driver, String url, String username, String password) {
HikariDataSource dataSource = new HikariDataSource();
Properties props = new Properties();
// oracle 必须配置此参数.
props.setProperty("remarksReporting", "true");
dataSource.setDriverClassName(driver);
dataSource.setJdbcUrl(url);
dataSource.setDataSourceProperties(props);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}
/**
* 获取到表字段所有属性
*
* @param driver 驱动
* @param url url
* @param username username
* @param password password
* @param tableName tableName
* @return
*/
public static List<Metadata> buildTableMetadata(String driver,
String url, String username, String password, String tableName) throws SQLException {
DataSource dataSource = getDataSource(driver, url, username, password);
Connection conn = null;
List<Metadata> metadataList = new ArrayList<>();
try {
conn = dataSource.getConnection();
// 获取库名称
String catalog = conn.getCatalog();
// 获取数据库元数据
DatabaseMetaData metaData = conn.getMetaData();
// %表示所有.
ResultSet resultSet = metaData.getColumns(catalog, "%", tableName, "%");
ResultSet primaryKeyResultSet = metaData.getPrimaryKeys(catalog, metaData.getUserName().toUpperCase(), tableName);
List<String> primaryKeyList = new ArrayList<>();
while (primaryKeyResultSet.next()) {
String primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");
primaryKeyList.add(primaryKeyColumnName);
}
while (resultSet.next()) {
// 字段名称
String columnName = resultSet.getString("COLUMN_NAME");
// 字段类型
String columnType = resultSet.getString("TYPE_NAME");
// 字段长度
int columnSize = resultSet.getInt("COLUMN_SIZE");
// 字段注释信息
String remarks = resultSet.getString("REMARKS");
// 字段精度：小数部分的位数
// int digits = resultSet.getInt("DECIMAL_DIGITS");
// 是否为空（1：可以为空，0不可以为空）
int nullable = resultSet.getInt("NULLABLE");
Metadata metadata = new Metadata();
metadata.setColumnName(columnName);
metadata.setTypeName(columnType);
metadata.setColumnSize(columnSize);
metadata.setRemarks(remarks);
metadata.setNullable(nullable);
if (primaryKeyList.contains(columnName)) {
// 是否主键：0：是；1不是
metadata.setColumnPrimaryKey(0);
} else {
metadata.setColumnPrimaryKey(1);
}
if (!metadataList.contains(metadata)){
metadataList.add(metadata);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二、正文
/**
* 导出数据库表信息
* 1.查询数据库信息、导出列名信息
* 2.获取jdbc连接
* 3.查询记录总数
* 4.设定页容量、起始第一页、总页数
* 5.组装分页查询的sql语句
* 6.分页查询，并写入Excel生成类里（在硬盘中缓存）
* 7.写入临时文件
*
* @param mResource
* @param exportPath
* @return
*/
private String exportExcel(RegisterResourceVO mResource, String exportPath) throws Exception {
//获取数据库连接信息
MDataSourceVO mDataSource = dataSourceMapper.selectDataSourceDetailById(mResource.getMDataSourceId());
//获取要导出的列信息
List<RegisterResTableColumnVO> registerResTableColumnList = registerTableResourceMapper.selectColumnsByRegisterResourceId(mResource.getId());
//文件名称
String fileName = mResource.getResourceName()+"-"+BaseUtil.createUUID() + ".xlsx";
//获取数据库连接
Class.forName(mDataSource.getDriver());
java.sql.Connection sqlConnection = DriverManager.getConnection(mDataSource.getUrl(), mDataSource.getDbUser(), mDataSource.getDbPwd());
//获取总行数
long rowsCount = getRowsCount(mResource, sqlConnection);
//页容量,可在表r_config_dic中修改(需要重启)
long pageSize = Long.parseLong(SingletonConfigDic.getInstance().getMap("exportData").get("pageSize"));
//起始第一页
long pageNum = 1;
long pageCount = (rowsCount - 1) / pageSize + 1;
//生成查询用的sql
String sqlSelect = getSqlSelect(mResource, mDataSource, registerResTableColumnList);
//设定Excel列名
List<ExcelExportEntity> entityList = newAddExcel(registerResTableColumnList);
//每次查询的结果集
List<Map<String, Object>> datas = new ArrayList<>();
//分页查询，并写入Excel生成类里（在硬盘中缓存）
Workbook workbook = null;
workbook = getWorkbook(mResource, mDataSource, sqlConnection, pageSize, pageNum, pageCount, sqlSelect, entityList, datas, workbook);
//写一个文件输出流，把内存中的excel文件写出去
FileOutputStream fos = new FileOutputStream(exportPath + fileName);
workbook.write(fos);
fos.close();
return fileName;
}
三、高潮
/**
* //获取记录总数
*
* @param mResource
* @param sqlConnection
* @return
* @throws SQLException
*/
private long getRowsCount(RegisterResourceVO mResource, java.sql.Connection sqlConnection) throws SQLException {
//查询计数sql
String sqlCount = "select count(*) from " + mResource.getResourceName();
long rowsCount = 0L;
PreparedStatement ps = sqlConnection.prepareStatement(sqlCount);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
rowsCount = rs.getLong(1);
}
return rowsCount;
}
/**
* 生成查询sql
*
* @param mResource
* @param mDataSource
* @param registerResTableColumnList
* @return
* @throws Exception
*/
private String getSqlSelect(RegisterResourceVO mResource, MDataSourceVO mDataSource, List<RegisterResTableColumnVO> registerResTableColumnList) throws Exception {
//查询sql
String sqlSelect = "select ";
//拼接列名
for (RegisterResTableColumnVO column : registerResTableColumnList) {
sqlSelect += column.getColumnName() + ",";
}
//mysql和oracle的区别(分页)
switch (mDataSource.getDbType()) {
case "1":
//mysql
sqlSelect = sqlSelect.substring(0, sqlSelect.length() - 1);
sqlSelect += " from " + mResource.getResourceName() + " limit ?,?";
break;
case "2":
//oracle
sqlSelect = "select * from (" + sqlSelect + "rownum rn" + " from " + mResource.getResourceName() + " where rownum<=?) temp where temp.rn>?";
break;
default:
throw new Exception("数据库类型未知|");
}
return sqlSelect;
}
/**
* 设定导出时的列名
*
* @param registerResTableColumnList
* @return
*/
private List<ExcelExportEntity> newAddExcel(List<RegisterResTableColumnVO> registerResTableColumnList) {
List<ExcelExportEntity> entityList = new ArrayList<>();
for (RegisterResTableColumnVO registerResTableColumnVO : registerResTableColumnList) {
entityList.add(new ExcelExportEntity(registerResTableColumnVO.getColumnName(), registerResTableColumnVO.getColumnName()));
}
return entityList;
}
/**
* 分页查询，并写入Excel生成类里（在硬盘中缓存）
*
* @param mResource
* @param mDataSource
* @param sqlConnection
* @param pageSize
* @param pageNum
* @param pageCount
* @param sqlSelect
* @param entityList
* @param datas
* @param workbook
* @return
* @throws SQLException
*/
private Workbook getWorkbook(RegisterResourceVO mResource, MDataSourceVO mDataSource, java.sql.Connection sqlConnection, long pageSize, long pageNum, long pageCount, String sqlSelect, List<ExcelExportEntity> entityList, List<Map<String, Object>> datas, Workbook workbook) throws SQLException {
logger.info("开始分页查询");
while (pageNum <= pageCount) {
logger.info("====" + pageNum + "===========");
//分页查询数据
PreparedStatement ps = sqlConnection.prepareStatement(sqlSelect);
//添加参数
ps.setLong(1, "1".equals(mDataSource.getDbType()) ? ((pageNum - 1) * pageSize) : (pageNum * pageSize));
ps.setLong(2, "1".equals(mDataSource.getDbType()) ? pageSize : ((pageNum - 1) * pageSize));
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//结果集存入
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(i);
if (colValue == null) {
colValue = "";
}
map.put(colName, colValue);
}
datas.add(map);
}
//设定title,sheet名
ExportParams exportParams = new ExportParams(mResource.getResourceName(), mResource.getResourceName());
//循环分页查询数据,写入easypoi（在硬盘中缓存）
workbook = ExcelExportUtil.exportBigExcel(exportParams, entityList, datas);
datas.clear();
pageNum++;
}
return workbook;
}
网友评论