这里我使用的是
JDBC + org.apache.commons.csv
组件
一、引入pom.xml依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency><!-- 添加oracle依赖 -->
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<scope>runtime</scope>
</dependency>
<dependency><!-- 添加Csv依赖 -->
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
二、DBUtils工具类
package com.example.demo;
import java.sql.*;
public class DBUtils {
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:database";
private static final String USER = "***";
private static final String PASSWORD = "***";
private static PreparedStatement sta = null;
private static ResultSet rs = null;
private static Connection conn = null;
/**
* 加载驱动程序
*/
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* @return 连接对象
*/
public static Connection getConn() {
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* @param sql sql语句 增加,删除,修改
* @param obj 参数
* @return
*/
public static int update(String sql, Object... obj) {
int count = 0;
conn = getConn();
try {
sta = conn.prepareStatement(sql);
if (obj != null) {
for (int i = 0; i < obj.length; i++) {
sta.setObject(i + 1, obj[i]);
}
}
count = sta.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally{
close();
}
return count;
}
/**
* @param sql sql语句
* @param obj 参数
* @return 数据集合
*/
public static ResultSet Query(String sql,Object...obj){
conn=getConn();
try {
sta=conn.prepareStatement(sql);
if(obj!=null){
for(int i=0;i<obj.length;i++){
sta.setObject(i+1, obj[i]);
}
}
rs=sta.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/**
* 关闭资源
*/
public static void close() {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (sta != null) {
sta.close();
}
} catch (SQLException e2) {
e2.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
三、创建Csv写入方法
/**
* 写入csv文件
* 列头 Object[] headers,
* @param data 数据内容
**/
public static void writeCsv(Object[] headers,List<String[]> data) throws IOException {
CSVFormat formator = CSVFormat.DEFAULT.withRecordSeparator("\n");
File file = new File("D:/csv/test.csv"); // 输出路径以及文件名称
System.out.println("---------开始传输文件---------");
// 输出流,并设置字符编码
OutputStreamWriter outputStreamWriter=new OutputStreamWriter(new FileOutputStream(file),"GBK");
BufferedWriter bw = new BufferedWriter(outputStreamWriter);
// 将输出流 和 CSV文件分隔符 放入CSVPrinter对象
CSVPrinter printer = new CSVPrinter(bw, formator);
printer.printRecord(headers); // 写入头信息
if (null != data) {
for (String[] lineData : data) { // 循环写入数据
printer.printRecord(lineData);
}
}
// 刷新
bw.flush();
// 关闭资源
printer.close();
bw.close();
System.out.println("---------结束传输文件---------");
}
四、创建查询数据方法,并调用Csv写入方法
/**
* 查询数据库数据,并调用CSV文件写入方法
*/
public static void selectBom() throws Exception {
List<String[]> list = new ArrayList<>();
String sql = "SELECT t1.FNUMBER code,t1.FNAME_L2 name FROM T_MM_Bom t1";
ResultSet rs = DBUtils.Query(sql, null);
String[] bom = null;
while (rs.next()) {
bom = new String[]{rs.getString("code"),rs.getString("name")};
list.add(bom);
}
DBUtils.close();
String[] str = {"编码","名称"}; // Csv文件头信息
writeCsv(str,list); // 调用Csv写入方法
}
五、调用 并 输出结果
public static void main(String[] args) throws Exception {
selectBom();
}
1、输出的文件默认是EXCEL表格方式打开,也可以用文本方式打开
2、org.apache.commons.csv
组件输出的CSV文件用文本方式打开的时候,输出的数据不带双引号
。