环境jdk1.7;测试连接:
import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.tomcat.util.bcel.classfile.Constant;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
import com.chinamobile.www.util.CSVUtils;
import com.csvreader.CsvWriter;
/**
* query data
*
*/
public class App {
static String JDBC_DRIVER = "com.cloudera.impala.jdbc41.Driver";
static String CONNECTION_URL = "jdbc:impala://10.163.170.90:21050/default";
//t_table(number String,column1 String,column2 String,column3 String,column4 String,column5 String)
public static void main(String[] args) throws IOException {
// if (args.length != 1) {
// System.out.println("Syntax: ClouderaImpalaJdbcExample
// \"<SQL_query>\"");
// System.exit(1);
// }
String sqlStatement = "select number,column1,column2,column3,column4,column5 from t_table";
System.out.println("\n=============================================");
System.out.println("Cloudera Impala JDBC Example");
System.out.println("Using Connection URL: " + CONNECTION_URL);
System.out.println("Running Query: " + sqlStatement);
Connection con = null;
try {
Class.forName(JDBC_DRIVER);
con = DriverManager.getConnection(CONNECTION_URL);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sqlStatement);
CSVUtils test = new CSVUtils();
String path = "c:/local_data/";
String fileName = "文件导出测试";
test.createCSVFile(rs, path, fileName);
System.out.println("\n== Begin Query Results ======================");
// print the results to the console
while (rs.next()) {
// the example query returns one String column
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
System.out.println(rs.getString(4));
System.out.println(rs.getString(5));
System.out.println(rs.getString(6));
}
System.out.println("== End Query Results =======================\n\n");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (Exception e) {
// swallow
}
}
}
}
转成csv文件类
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.apache.log4j.Logger;
public class CSVUtils {
private Logger LOGGER = Logger.getLogger(this.getClass().getName());
/**
* 生成为CVS文件
*
* @param exportData
* 源数据List
* @param map
* csv文件的列表头map
* @param outPutPath
* 文件路径
* @param fileName
* 文件名称
* @return
*/
public File createCSVFile(ResultSet resultSet, String outPutPath, String fileName) {
File csvFile = null;
BufferedWriter csvFileOutputStream = null;
try {
File file = new File(outPutPath);
if (!file.exists()) {
file.mkdir();
}
// 定义文件名格式并创建
csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath));
LOGGER.info("csvFile:" + csvFile);
// UTF-8使正确读取分隔符","
csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"),
1024);
// 写入文件头部
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
csvFileOutputStream.write(md.getColumnName(i));
csvFileOutputStream.write(AppConstant.COMMA);
}
csvFileOutputStream.newLine();
// 写入文件内容
while (resultSet.next()) {
// 依据列名获取各列值
for (int j = 1; j <= columnCount; j++) {
String value = resultSet.getString(j);
if (value == null || value.equals(""))
value = "";
// 创建列
csvFileOutputStream.write(value);
if (j != columnCount) {
csvFileOutputStream.write(AppConstant.COMMA);
}
}
csvFileOutputStream.newLine();
}
csvFileOutputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
csvFileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return csvFile;
}