/**
JDBC工具类
**/
package com.hl.util;
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.HashMap;
import java.util.List;
import java.util.Map;
/**
*
* @author marver
*
*/
public class JdbcUtil {
public static String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
public static String username="orcl";
public static String password="orcl";
public static Connection conn;
public static PreparedStatement pstm;
public static ResultSet rs;
public static Statement st;
//连接数据库的方法
public static Connection getConnection(){
//加载驱动包
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
if(conn==null||conn.isClosed()){
conn=DriverManager.getConnection(url,username, password);
System.out.println("获取连接成功");
}
} catch (Exception e) {
System.out.println("加载驱动失败。"+e);
}
return conn;
}
//查询方法
public static List query(String sql){
List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
conn=getConnection();
try {
st=(Statement)conn.createStatement(); //创建执行sql的statement对象
ResultSet rs=st.executeQuery(sql); //执行sql语句
ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
int columnCount = md.getColumnCount(); //获得列数
while(rs.next()){
Map<String,Object> rowData = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/*
* 关闭连接
*/
public static void closeAll(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//测试能否与oracle数据库连接成功
public static void main(String[] args) {
getConnection();
}
}
/**
查询数据导出到Excel
**/
package com.hl;
import java.io.File;
import java.io.FileOutputStream;
import java.io.Reader;
import java.sql.Clob;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.hl.util.JdbcUtil;
public class PoiExportExcel {
//定义Excel存放目录
public static String outputFile="D:\\marver";
public static String fileName="test.xls";
public static void exportExcel() {
try {
//创建工作薄
HSSFWorkbook workBook=new HSSFWorkbook();
//创建一工作表
HSSFSheet sheet=workBook.createSheet("通过POI创建的工作表");
List<Map<String, Object>> list=queryDispose();
for(int i=0;i<list.size();i++){
Map<String, Object> map=list.get(i);
//创建行
HSSFRow row=sheet.createRow(i);
int j=0;
for(String name:map.keySet()){
Object obj= map.get(name);
//创建列
HSSFCell cell=row.createCell(j);
j++;
cell.setCellValue(String.valueOf(mClob(obj)));//赋值
}
}
//新建一个文件输出流
File file=new File(outputFile);
if(!file.exists()){
file.mkdirs();
}
FileOutputStream fOut=new FileOutputStream(outputFile+"\\"+fileName);
workBook.write(fOut); //存盘
fOut.flush();
fOut.close(); //关闭
System.out.println("文件生成成功");
} catch (Exception e) {
System.out.println("写入Excel文件失败 "+e);
}finally{
JdbcUtil.closeAll();
}
}
/**
* 调用jdbc查询
* @return
*/
public static List<Map<String, Object>> queryDispose(){
String sql="select * from user where create_time>=to_date('2015-12-20','yyyy-mm-dd')";
List<Map<String, Object>> list=JdbcUtil.query(sql);
return list;
}
/**
* 处理clob字段
* @param clob
* @return
* @throws Exception
*/
public static String mClob(Object clob) throws Exception {
if (clob == null) {
return "";
}
StringBuffer clobString = new StringBuffer();
if (clob instanceof Clob) {
int y;
char ac[] = new char[4096];
Reader reader = ((Clob) clob).getCharacterStream();
while ((y = reader.read(ac, 0, 4096)) != -1) {
clobString.append(new String(ac, 0, y));
}
} else {
clobString.append(clob.toString());
}
return clobString.toString();
}
public static void main(String[] args) {
exportExcel();
}
}
转载于:https://my.oschina.net/mavers/blog/549155