Tuesday, September 14, 2010

Download database records as csv format in jsp

Download database records as csv format in jsp




<%@page contentType="application/octet-stream"%>
<%@page pageEncoding="UTF-8"%>

<%@ page import="java.sql.*" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.text.*" %>
<%@ page import="java.io.*" %>

<%
String userName = "root",password = "root",url = "jdbc:mysql://localhost:3306/test",driver = "com.mysql.jdbc.Driver";
String siteUrl="http://localhost:9090/Test/";
Connection conn=null;
try {
    Class.forName(driver).newInstance();
    conn = DriverManager.getConnection(url, userName,password);
}catch(Exception ee) {
    out.print("There are some problems");
    out.print(ee.toString());
}



String sSql="select *from transaction order by id";
String datas=new String("");
PreparedStatement pstTrans = conn.prepareStatement(sSql);
ResultSet rsTrans=pstTrans.executeQuery();


response.reset();
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition","attachment;filename=transaction .csv");

while (rsTrans.next()){
    datas=rsTrans.getString("user_num")+","+rsTrans.getString("user_id")+","+rsTrans.getString("email")+"\r\n";
    byte datasBytes[] = datas.getBytes();
    ByteArrayInputStream bais = new ByteArrayInputStream(datasBytes);   
    byte[] buff = new byte[1024];
    int len;
    while ((len = bais.read(buff)) > 0)
        response.getOutputStream().write(buff, 0, len);
    bais.close();
}

rsTrans.close();
rsTrans=null;
pstTrans.close();
pstTrans=null;
conn.close();
conn=null;
response.getOutputStream().flush();

%>

No comments:

Post a Comment