Exporting your database to csv file in java

Exporting your database to a csv file. Sounds very trivial. However I did not find any straightforward solution to this. I am using mysql version 5.x.

I looked into the workbench , into the import export options , found sql dump commands, to store into the csv u have to go and do it for every table or may be write a sql script which does that.What I was looking for was a single click to store the database dump in csv file.

Being a java programmer, I always tend to go for java coding rather than writing any sql script(it takes me far less time doing things in java than doing it in sql).So, the first step was google out some pre existing java class which does this kind of job.Was not convinced with what I got so decided to go with my own program.I use hibernate for interaction with database, but hql for my queries were not very clear to me so I had to write the platform specific(mysql in my case),learnt a bit of jdbc also. You may need to update those according to your database.So here is the working code, I have tried to comment logically so that it can be easily interpreted on reading.

package com.xbrl.dao;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import jxl.write.Label;

import com.utility.HibernateUtil;

public class DB2CSV {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		 
		  //usual database connection part
		  Connection con = null;
		  String url = "jdbc:mysql://localhost:3306/";
		  String db = "db_name";
		  String driver = "com.mysql.jdbc.Driver";
		  String user = "username";
		  String pass = "password";
		  FileWriter fw ;
		  try{
		  Class.forName(driver);
		  con = DriverManager.getConnection(url+db, user, pass);
		  Statement st = con.createStatement();
		  
		  //this query gets all the tables in your database(put your db name in the query)
		  ResultSet res = st.executeQuery("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' ");
		  
		  //Preparing List of table Names
		  List <String> tableNameList = new ArrayList<String>();
		  while(res.next())
		  {
			  tableNameList.add(res.getString(1));
		  }
		  
		  //path to the folder where you will save your csv files
		  String filename = "D:/db2csv/";
		  
		  //star iterating on each table to fetch its data and save in a .csv file
		  for(String tableName:tableNameList)
			{
				int k=0;
				
				int j=1;
				
				System.out.println(tableName);
				
				List<String> columnsNameList  = new ArrayList<String>();
				
				//select all data from table
				res = st.executeQuery("select * from xcms."+tableName);
				
				//colunm count is necessay as the tables are dynamic and we need to figure out the numbers of columns
				int colunmCount = getColumnCount(res);
				
				 try {
					fw = new FileWriter(filename+""+tableName+".csv");
					
					
					//this loop is used to add column names at the top of file , if you do not need it just comment this loop
					for(int i=1 ; i<= colunmCount ;i++)
					{
						fw.append(res.getMetaData().getColumnName(i));
						fw.append(",");
			
					}
					
					fw.append(System.getProperty("line.separator"));
					
					while(res.next())
					{
						for(int i=1;i<=colunmCount;i++)
						{
							
							//you can update it here by using the column type but i am fine with the data so just converting 
							//everything to string first and then saving
							if(res.getObject(i)!=null)
							{
							String data= res.getObject(i).toString();
							fw.append(data) ;
							fw.append(",");
							}
							else
							{
								String data= "null";
								fw.append(data) ;
								fw.append(",");
							}
							
						}
						//new line entered after each row
						fw.append(System.getProperty("line.separator"));
					}
					
					 fw.flush();
					  fw.close();
					
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
				
		  con.close();
		  }
		  catch (ClassNotFoundException e){
		  System.err.println("Could not load JDBC driver");
		  e.printStackTrace();
		  }
		  catch(SQLException ex){
		  System.err.println("SQLException information");
		  }
 }
	
	//to get numbers of rows in a result set 
	public static int  getRowCount(ResultSet res) throws SQLException
	{
		  res.last();
		  int numberOfRows = res.getRow();
		  res.beforeFirst();
		  return numberOfRows;
	}

	//to get no of columns in result set
	
	public static int  getColumnCount(ResultSet res) throws SQLException
	{
		return res.getMetaData().getColumnCount();
	}
	

}

Hope this helps you!!

Thanks
Gaurav