Here I have written this program for recreating table data for testing purpose. So if you got any recreation or testing scenarios, this program will help you. Here I have used MySQL database. The below program will read table data and convert them into Insert SQL Statements. Those Statements will be saved to text file

Java Program

Observe below highlighted code. Give your table name and directory path over there
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class CreateSqlStatements {
    public static void main(String[] args) throws IOException {
        Connection conn = null;
        Statement stmt = null;
        String table = "table Name Here";
        File file = new File("directory path" + File.separator + table + ".txt");

        try {

            conn = DBConn.getConnection();
            stmt = conn.createStatement();
            FileOutputStream fout = new FileOutputStream(file);
            BufferedWriter fw = new BufferedWriter(new OutputStreamWriter(fout));

            String sql = "select * from " + table;

            ResultSet res = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = res.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (res != null) {
                String insert_string = "INSERT INTO " + table + "(";
                int i = 1;
                boolean[] typeArr = new boolean[columnCount + 1];
                for (; i < columnCount + 1; i++) {
                    String column = rsmd.getColumnName(i);
                    switch (rsmd.getColumnType(i)) {
                    case Types.INTEGER:
                    case Types.BIGINT:
                    case Types.BIT:
                    case Types.BOOLEAN:
                    case Types.DECIMAL:
                    case Types.DOUBLE:
                    case Types.FLOAT:
                    case Types.NULL:
                    case Types.NUMERIC:
                    case Types.SMALLINT:
                    case Types.TINYINT:
                        typeArr[i] = true;
                        break;
                    default:
                        typeArr[i] = false;
                        break;
                    }

                    if (i < columnCount) {
                        insert_string += column + ", ";
                    } else {
                        insert_string += column;
                    }

                }
                insert_string += ") VALUES (";

                while (res.next()) {
                    String str = getQuery(res, columnCount, typeArr,
                            insert_string) + ");";
                    ;
                    fw.write(str);
                    fw.newLine();
                    System.out.println(str);
                }

            }
            fw.flush();
            fw.close();

        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    conn.close();
            } catch (SQLException se) {
            }// do nothing
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }// end finally try
        }// end try
        System.out.println("Goodbye!");
    }// end main

    // to get formatted query
    public static String getQuery(ResultSet res, int columnCount,
            boolean[] typeArr, String insert_string) throws SQLException {
        for (int k = 1; k < columnCount + 1; k++) {
            String temp = res.getString(k);
            if (temp != null) {
                temp = temp.trim();
            }
            if (typeArr[k]) {
                insert_string += "" + temp + "";
            } else {
                insert_string += "'" + temp + "'";
            }
            if (k < columnCount) {
                insert_string += ", ";
            }
        }
        return insert_string;
    }
}

DBConn.java

Here I have used MySQL database. Observe below highlighted code. Give username and password over there
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author srinivas
 * This file is to create Connections and Closing connections and statements.
 */
public class DBConn {
    Connection con = null;
    static String className = "class com.sl.DBConn";

    /**
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     * To Create Connections
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = null;
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/world", "root", "mypassword");
        return connection;
    }

    /**
     * @param con
     * @param stmt
     * @param rs
     * To close statements, result sets and Connection 
     */
    public static void close(Connection con, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param con
     * @param stmt
     * To close statements and Connection
     */
    public static void close(Connection con, Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param con
     * @param pstmt
     * @param rs
     * To Close PreparedStatement and Connection
     */
    public static void close(Connection con, PreparedStatement pstmt,
            ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param con
     * To close connection
     */
    public static void closeConnection(Connection con) {
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param conn
     * @throws SQLException
     * To Commit and Close the connection
     */
    public static void commitAndClose(Connection conn) throws SQLException {
        conn.commit();
        conn.close();
    }

}

0 comments:

Blogroll

Follow this blog by Email

Popular Posts