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();
    }

}

9 comments:

  1. Databases are the technological terms used for the storage of million of records and fields. By learn these type of term papers australia, we can always solve its problems which is quite difficult to handle.

    ReplyDelete
  2. Web facilitating is somewhat new universe of the innovation. this is extremely something imperative to know. This would be helpful in papersplanet advance work. this blog is about dependable site facilitating. it is a vital component of web marketable strategy that is great.

    ReplyDelete
  3. My name is Laurense. I am 22 years old. I live and study in Hamburg. Recently, I also had problems with writing a dissertation and my friend told me about paper with writing, where professionals help with writing. Fortunately, I made sure that professionals work there and they helped me.

    ReplyDelete
  4. It is a real skill to put your thoughts on paper in a beautiful and high-quality manner. Which is completely mastered by all our authors college essay writers. As each of them has many years of experience and knowledge in a wide variety of industries.

    ReplyDelete
  5. Thanks to the author for writing the post, it was quite necessary for me and liked it. I wrote a note on the https://ukbestessays.org/15writers-review/ about this. I will be happy if you read it and accept it. Thank you for your concern.

    ReplyDelete
  6. good morning. have you already solved your problem? I can recommend you writers from https://grademiners.com/college-paper who will prepare material for you on this topic and you will figure it out. I give you my word. they are high-level professionals. I myself order term papers and essays from them

    ReplyDelete
  7. This is it! If some of you guys really necessity assistance with essay or homework, you can as this guy's for help! Don be shy and text to them! Stop waste your money or tie and this one capstone research paper ! Excellent luck and have fun!

    ReplyDelete
  8. Service write my paper has developed a system of cumulative discounts. The essence of the discounts is based on the frequency of your orders: the more often you apply to order a work, the more discount you will receive when you again want to buy a term paper to order

    ReplyDelete
  9. Coursework to order is often a necessary measure, and specialists www.essaywriter.org/cheap-research-papers are well aware of the importance and urgency of this work for every modern student. The most simplified ordering system, minimum advance payment and guaranteed quality of work - all this allows us to count on the fact that you will become our regular customer

    ReplyDelete

Blogroll

Popular Posts