Java SQL Execute executeInsert(Connection conn, String targetTable, String destinationTable)

Description

execute Insert

License

Apache License

Declaration

public static void executeInsert(Connection conn, String targetTable, String destinationTable)
            throws SQLException 

Method Source Code

//package com.java2s;
//License from project: Apache License 

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class Main {
    public static void executeInsert(Connection conn, String targetTable, String destinationTable)
            throws SQLException {
        System.out.println("Attempting bulk insert using st_pointFromWKB");

        PreparedStatement p_stmt = null;
        int count = 0;
        String query = "INSERT INTO " + destinationTable
                + " (objectid, shape) VALUES (?, sde.st_pointFromWKB(?, 2230))";
        Map<Integer, byte[]> results = getShapeText(conn, targetTable);

        try {//from  ww w.jav  a2  s . co m
            conn.setAutoCommit(true);
            p_stmt = conn.prepareStatement(query);

            for (Map.Entry<Integer, byte[]> e : results.entrySet()) {
                count++;
                p_stmt.setInt(1, e.getKey().intValue());
                p_stmt.setBytes(2, e.getValue());
                p_stmt.executeUpdate();
            }
            System.out.println("Inserted " + count + " rows.");
        } catch (SQLException ex) {
            System.out.println(ex.getErrorCode() + "\n" + ex.getMessage());
        }
    }

    private static Map<Integer, byte[]> getShapeText(Connection conn, String tableName) throws SQLException {
        Map<Integer, byte[]> shpText = new HashMap<>();
        int count = 0;
        Statement stmt = null;
        String query = "SELECT sde.st_asbinary(shape) as shp FROM " + tableName;

        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                count++;
                //String shp = rs.getString("shp");
                byte[] shp = rs.getBytes("shp");
                shpText.put(count, shp);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        return shpText;
    }
}

Related

1. executeCreateDB(String className, String URL, String userName, String password, String dbName)
2. executeDDL(final Connection connection, final String sql, final int... ignoreErrors)
3. executeDDL(String ddl)
4. executeImmediate(String sql)
5. executeInsert(Connection conn, String sql, boolean hasAutoGeneratedKey, Object... parameters)
6. executeInsertSQL(String sql)
7. executeQueries(Connection conn, InputStream stream)
8. executeResource(Connection con, String resource)
9. executeSetArgs(PreparedStatement stmt, Object... args)