Example usage for java.sql Connection setAutoCommit

List of usage examples for java.sql Connection setAutoCommit

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. setAutoCommit

Introduction

In this page you can find the example usage for java.sql Connection setAutoCommit.

Prototype

void setAutoCommit(boolean autoCommit) throws SQLException;

Source Link

Document

Sets this connection's auto-commit mode to the given state.

Usage

From source file:com.hangum.tadpole.importdb.core.dialog.importdb.sql.SQLToDBImportDialog.java

/**
 * select? execute  ./*from w  w w  .  j  a v a 2 s  . co m*/
 * 
 * @param listQuery
 * @throws Exception
 */
private int runSQLExecuteBatch(List<String> listQuery) throws Exception {
    java.sql.Connection conn = null;
    Statement statement = null;
    int result = 0;

    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        conn = client.getDataSource().getConnection();
        conn.setAutoCommit(false);
        statement = conn.createStatement();
        int count = 0;

        for (String strQuery : listQuery) {
            if ("".equals(StringUtils.trimToEmpty(strQuery))) //$NON-NLS-1$
                continue;

            statement.addBatch(strQuery);
            if (++count % batchSize == 0) {
                try {
                    statement.executeBatch();
                } catch (SQLException e) {
                    logger.error("Execute Batch error", e); //$NON-NLS-1$
                    bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$

                    SQLException ne = e.getNextException();
                    while (ne != null) {
                        logger.error("NEXT SQLException is ", ne);//$NON-NLS-1$
                        bufferBatchResult.append(ne.getMessage() + "\n");
                        ne = ne.getNextException();
                    }

                    if (btnIgnore.getSelection()) {
                        conn.commit();
                        continue;
                    } else {
                        conn.rollback();
                        result = -1;
                        break;
                    }
                }
            }
        }

        statement.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

        if (result < 0 && !"".equals(bufferBatchResult.toString())) { //$NON-NLS-1$
            MessageDialog.openError(null, Messages.CsvToRDBImportDialog_4, bufferBatchResult.toString());
        }
    } catch (SQLException e) {
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        if (btnIgnore.getSelection()) {
            conn.commit();
        } else {
            conn.rollback();
        }

        SQLException ne = e.getNextException();
        while (ne != null) {
            logger.error("Execute Batch error", e); //$NON-NLS-1$
            bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
            ne = ne.getNextException();
        }
    } catch (Exception e) {
        result = -1;
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        conn.rollback();
        throw e;
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
    return result;
}

From source file:com.amazon.carbonado.repo.jdbc.JDBCRepository.java

/**
 * Called by JDBCTransactionManager.//w w  w .  j a  va 2s.  c o m
 */
Connection getConnectionForTxn(IsolationLevel level) throws FetchException {
    try {
        if (mOpenConnections == null) {
            throw new FetchException("Repository is closed");
        }

        // Get connection outside lock section since it may block.
        Connection con = mDataSource.getConnection();

        if (level == IsolationLevel.NONE) {
            con.setAutoCommit(true);
        } else {
            con.setAutoCommit(false);
            if (level != mDefaultIsolationLevel) {
                con.setTransactionIsolation(mapIsolationLevelToJdbc(level));
            }
        }

        mOpenConnectionsLock.lock();
        try {
            if (mOpenConnections == null) {
                con.close();
                throw new FetchException("Repository is closed");
            }
            mOpenConnections.put(con, null);
        } finally {
            mOpenConnectionsLock.unlock();
        }

        return con;
    } catch (Exception e) {
        throw toFetchException(e);
    }
}

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#removeService(java.lang.String)
 *///  www . ja  va  2s  .com
public synchronized boolean removeService(final String datacenter) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#removeService(final String datacenter) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", datacenter);
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL removeServiceData(?)}");
        stmt.setString(1, datacenter);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return isComplete;
}

From source file:com.hangum.tadpole.importexport.core.dialogs.SQLToDBImportDialog.java

/**
 * select? execute  ./*from w w w.j a  va2  s.c om*/
 * 
 * @param listQuery
 * @throws Exception
 */
private int runSQLExecuteBatch(List<String> listQuery) throws Exception {
    java.sql.Connection conn = null;
    Statement statement = null;
    int result = 0;

    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        conn = client.getDataSource().getConnection();
        conn.setAutoCommit(false);
        statement = conn.createStatement();
        int count = 0;

        for (String strQuery : listQuery) {
            if ("".equals(StringUtils.trimToEmpty(strQuery))) //$NON-NLS-1$
                continue;

            statement.addBatch(strQuery);
            if (++count % batchSize == 0) {
                try {
                    statement.executeBatch();
                } catch (SQLException e) {
                    logger.error("Execute Batch error", e); //$NON-NLS-1$
                    bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$

                    SQLException ne = e.getNextException();
                    while (ne != null) {
                        logger.error("NEXT SQLException is ", ne);//$NON-NLS-1$
                        bufferBatchResult.append(ne.getMessage() + "\n"); //$NON-NLS-1$
                        ne = ne.getNextException();
                    }

                    if (btnIgnore.getSelection()) {
                        conn.commit();
                        continue;
                    } else {
                        conn.rollback();
                        result = -1;
                        break;
                    }
                }
            }
        }

        statement.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

        if (result < 0 && !"".equals(bufferBatchResult.toString())) { //$NON-NLS-1$
            MessageDialog.openWarning(null, Messages.get().Warning, bufferBatchResult.toString());
        }
    } catch (SQLException e) {
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        if (btnIgnore.getSelection()) {
            conn.commit();
        } else {
            conn.rollback();
        }

        SQLException ne = e.getNextException();
        while (ne != null) {
            logger.error("Execute Batch error", e); //$NON-NLS-1$
            bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
            ne = ne.getNextException();
        }
    } catch (Exception e) {
        result = -1;
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        conn.rollback();
        throw e;
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
    return result;
}

From source file:com.iana.boesc.dao.BOESCDaoImpl.java

@Override
public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId,
        String userType, File file) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    QueryRunner qrun = new QueryRunner(getDataSource());
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    PreparedStatement pstmt = null;
    ResultSet rs = null;// w ww  .j  a  v a  2s.co  m

    try {

        StringBuilder sbQuery = new StringBuilder(
                "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,");
        sbQuery.append(
                " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID,  ");
        sbQuery.append(
                " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN,   ");
        sbQuery.append(
                " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09,  ");
        if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
            sbQuery.append(" IEP_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
            sbQuery.append(" MRV_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
            sbQuery.append(" FO_ID, STATUS ");
        }

        sbQuery.append(
                " ,CREATED_DATE ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        //get Additional Details from GIER DB
        GIERInfoDetails gierInfo = null;
        gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber());
        System.out.println("Before UIIA Datasource");
        UIIAInfoDetails uiiaInfo = null;
        uiiaInfo = getUIIAdetailsforDVIR(eb);

        //logger.info("gierInfo ::"+gierInfo);

        pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, eb.getIsaheader());
        pstmt.setString(2, eb.getGsHeader());
        pstmt.setString(3, eb.getSenderId());
        pstmt.setString(4, userType);
        pstmt.setString(5, eb.getIsaDateTime());
        pstmt.setString(6, eb.getGsControl());
        pstmt.setString(7, eb.getStControl());
        pstmt.setString(8, eb.getInspDate());
        pstmt.setString(9, eb.getInspTime());
        pstmt.setString(10, eb.getInspTimeZone());
        pstmt.setString(11, eb.getEqpInitial());
        pstmt.setString(12, eb.getEqpNumber());
        pstmt.setString(13, eb.getChassisId());
        pstmt.setString(14, gierInfo.getCompanySCACCode());
        pstmt.setString(15, eb.getPortQualifier());
        pstmt.setString(16, eb.getPortIdentifier());
        pstmt.setString(17, eb.getDrvState());
        pstmt.setString(18, eb.getDrvLicNo());
        pstmt.setString(19, uiiaInfo.getDrvName());
        pstmt.setString(20, eb.getMcScac());
        pstmt.setString(21, eb.getMcName());
        pstmt.setString(22, eb.getRcdInfo());
        pstmt.setString(23, gierInfo.getUsDotNumber());
        pstmt.setString(24, uiiaInfo.getMcEin());
        pstmt.setString(25, uiiaInfo.getMcDot());
        pstmt.setString(26, uiiaInfo.getIddPin());
        pstmt.setString(27, eb.getQ5Details());
        pstmt.setString(28, eb.getN7Details());
        pstmt.setString(29, eb.getR4Details());
        pstmt.setString(30, eb.getN1Details());
        pstmt.setString(31, eb.getN1DrDetails());
        pstmt.setInt(32, bean.getNoDefectsCount());
        pstmt.setInt(33, bean.getBrakesCount());
        pstmt.setInt(34, bean.getLightsCount());
        pstmt.setInt(35, bean.getWheelCount());
        pstmt.setInt(36, bean.getAirlineCount());
        pstmt.setInt(37, bean.getCouplingCount());
        pstmt.setInt(38, bean.getFrameCount());
        pstmt.setInt(39, bean.getBolsterCount());
        pstmt.setInt(40, bean.getFastenerCount());
        pstmt.setInt(41, bean.getSliderCount());
        pstmt.setString(42, boescUserId);
        pstmt.setString(43, GlobalVariables.STATUS_PENDING);
        pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp());

        int dbStat = 0;
        int dvirKey = 0;
        dbStat = pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (dbStat != 0) {
            if (rs != null) {
                while (rs.next()) {
                    dvirKey = rs.getInt(1);
                    logger.info("dvirKey: " + dvirKey);
                }
            }
        }
        if (dvirKey != 0) {
            conn.commit();
            //Update BOESC_UNIQUE_NO : using business logic
            String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? ";
            qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey });
            logger.info("Record Inserted successfully for DVIR..." + file.getName());
            return true;
        } else {
            conn.rollback();
            logger.error("Failure Data insertion in DVIR..");
        }
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex1) {
            logger.error(
                    "Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage());
            ex1.printStackTrace();
            throw ex1;
        } catch (Exception e) {
            logger.error("Caught SQL exception in finally block " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }
    return false;
}

From source file:de.mendelson.comm.as2.database.DBDriverManager.java

/**Returns a connection to the database
 * @param hostName Name of the database server to connect to. Use "localhost"
 * to connect to your local host/* w  ww. ja v  a  2 s  . c  om*/
 * @param DB_TYPE of the database that should be created, as defined in this class
 */
public static synchronized Connection getConnectionWithoutErrorHandling(final int DB_TYPE, String host)
        throws SQLException {
    Connection connection = null;
    if (DB_TYPE == DB_RUNTIME) {
        //no pooling
        if (dataSourceRuntime == null) {
            connection = DriverManager.getConnection(getConnectionURI(host, DB_TYPE), DB_USER_NAME,
                    DB_PASSWORD);
        } else {
            int maxConnections = dataSourceRuntime.getMaxActive();
            if (maxConnections < dataSourceRuntime.getNumActive() + 1) {
                dataSourceRuntime.setMaxActive(maxConnections + 1);
            }
            connection = dataSourceRuntime.getConnection();
        }
    } else if (DB_TYPE == DB_CONFIG) {
        //no pooling
        if (dataSourceConfig == null) {
            connection = DriverManager.getConnection(getConnectionURI(host, DB_TYPE), DB_USER_NAME,
                    DB_PASSWORD);
        } else {
            int maxConnections = dataSourceConfig.getMaxActive();
            if (maxConnections < dataSourceConfig.getNumActive() + 1) {
                dataSourceConfig.setMaxActive(maxConnections + 1);
            }
            connection = dataSourceConfig.getConnection();
        }
    } else if (DB_TYPE == DB_DEPRICATED) {
        //deprecated connection: no pooling
        connection = DriverManager.getConnection(getConnectionURI(host, DB_TYPE), DB_USER_NAME, DB_PASSWORD);
    } else {
        throw new RuntimeException("Requested invalid db type in getConnectionWithoutErrorHandling");
    }
    connection.setReadOnly(false);
    connection.setAutoCommit(true);
    return (connection);
}

From source file:com.webpagebytes.cms.local.WPBLocalDataStoreDao.java

public <T> void deleteRecord(Class<T> kind, String fieldName, Object keyValue)
        throws SQLException, WPBSerializerException {
    Connection connection = getConnection();
    PreparedStatement preparedStatement = null;
    try {/*from   www. j  a va 2s.  c om*/
        String sqlStatement = getSQLStringForDelete(kind, fieldName);
        connection.setAutoCommit(true);
        preparedStatement = connection.prepareStatement(sqlStatement);
        setPrepareStatementParameter(preparedStatement, 1, keyValue);

        preparedStatement.execute();
    } catch (SQLException e) {
        throw e;
    } finally {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        connection.close();
    }
}

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#addService(java.util.List)
 *///  ww w  . j  av  a2s .  c  o m
public synchronized boolean addService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#addService(final List<String> data) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL addNewService(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return isComplete;
}

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#updateService(java.util.List)
 *//*from  w w w  .j a  v a  2s  .c om*/
public synchronized boolean updateService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#updateService(final List<String> data) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL updateServiceData(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return isComplete;
}

From source file:com.emr.utilities.CSVLoader.java

/**
* Parse CSV file using OpenCSV library and load in 
* given database table. //from  w w  w  .j av a2 s .  com
* @param csvFile {@link String} Input CSV file
* @param tableName {@link String} Database table name to import data
* @param truncateBeforeLoad {@link boolean} Truncate the table before inserting 
*          new records.
 * @param destinationColumns {@link String[]} Array containing the destination columns
*/
public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad, String[] destinationColumns,
        List columnsToBeMapped) throws Exception {
    CSVReader csvReader = null;
    if (null == this.connection) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace,
                "File Error", JOptionPane.ERROR_MESSAGE);
        throw new Exception("Error occured while executing file. " + stacktrace);
    }
    String[] headerRow = csvReader.readNext();

    if (null == headerRow) {
        throw new FileNotFoundException(
                "No columns defined in given CSV file." + "Please check the CSV file format.");
    }
    //Get indices of columns to be mapped
    List mapColumnsIndices = new ArrayList();
    for (Object o : columnsToBeMapped) {
        String column = (String) o;
        column = column.substring(column.lastIndexOf(".") + 1, column.length());
        int i;

        for (i = 0; i < headerRow.length; i++) {

            if (headerRow[i].equals(column)) {
                mapColumnsIndices.add(i);
            }
        }
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);
    questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    query = query.replaceFirst(KEYS_REGEX, StringUtils.join(destinationColumns, ","));
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    String log_query = query.substring(0, query.indexOf("VALUES("));

    String[] nextLine;
    Connection con = null;
    PreparedStatement ps = null;
    PreparedStatement ps2 = null;
    PreparedStatement reader = null;
    ResultSet rs = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        File file = new File("sqlite/db");
        if (!file.exists()) {
            file.createNewFile();
        }
        db = new SQLiteConnection(file);
        db.open(true);

        //if destination table==person, also add an entry in the table person_identifier
        //get column indices for the person_id and uuid columns
        int person_id_column_index = -1;
        int uuid_column_index = -1;
        int maxLength = 100;
        int firstname_index = -1;
        int middlename_index = -1;
        int lastname_index = -1;
        int clanname_index = -1;
        int othername_index = -1;
        if (tableName.equals("person")) {
            int i;
            ps2 = con.prepareStatement(
                    "insert ignore into person_identifier(person_id,identifier_type_id,identifier) values(?,?,?)");
            for (i = 0; i < headerRow.length; i++) {
                if (headerRow[i].equals("person_id")) {
                    person_id_column_index = i;
                }
                if (headerRow[i].equals("uuid")) {
                    uuid_column_index = i;
                }
                /*if(headerRow[i].equals("first_name")){
                    System.out.println("Found firstname index: " + i);
                    firstname_index=i;
                }
                if(headerRow[i].equals("middle_name")){
                    System.out.println("Found firstname index: " + i);
                    middlename_index=i;
                }
                if(headerRow[i].equals("last_name")){
                    System.out.println("Found firstname index: " + i);
                    lastname_index=i;
                }
                if(headerRow[i].equals("clan_name")){
                    System.out.println("Found firstname index: " + i);
                    clanname_index=i;
                }
                if(headerRow[i].equals("other_name")){
                    System.out.println("Found firstname index: " + i);
                    othername_index=i;
                }*/
            }
        }

        if (truncateBeforeLoad) {
            //delete data from table before loading csv
            try (Statement stmnt = con.createStatement()) {
                stmnt.execute("DELETE FROM " + tableName);
                stmnt.close();
            }
        }
        if (tableName.equals("person")) {
            try (Statement stmt2 = con.createStatement()) {
                stmt2.execute(
                        "ALTER TABLE person CHANGE COLUMN first_name first_name VARCHAR(50) NULL DEFAULT NULL AFTER person_guid,CHANGE COLUMN middle_name middle_name VARCHAR(50) NULL DEFAULT NULL AFTER first_name,CHANGE COLUMN last_name last_name VARCHAR(50) NULL DEFAULT NULL AFTER middle_name;");
                stmt2.close();
            }
        }
        final int batchSize = 1000;
        int count = 0;
        Date date = null;

        while ((nextLine = csvReader.readNext()) != null) {

            if (null != nextLine) {
                int index = 1;
                int person_id = -1;
                String uuid = "";
                int identifier_type_id = 3;
                if (tableName.equals("person")) {
                    reader = con.prepareStatement(
                            "select identifier_type_id from identifier_type where identifier_type_name='UUID'");
                    rs = reader.executeQuery();
                    if (!rs.isBeforeFirst()) {
                        //no uuid row
                        //insert it
                        Integer numero = 0;
                        Statement stmt = con.createStatement();
                        numero = stmt.executeUpdate(
                                "insert into identifier_type(identifier_type_id,identifier_type_name) values(50,'UUID')",
                                Statement.RETURN_GENERATED_KEYS);
                        ResultSet rs2 = stmt.getGeneratedKeys();
                        if (rs2.next()) {
                            identifier_type_id = rs2.getInt(1);
                        }
                        rs2.close();
                        stmt.close();
                    } else {
                        while (rs.next()) {
                            identifier_type_id = rs.getInt("identifier_type_id");
                        }
                    }

                }
                int counter = 1;
                String temp_log = log_query + "VALUES("; //string to be logged

                for (String string : nextLine) {
                    //if current index is in the list of columns to be mapped, we apply that mapping
                    for (Object o : mapColumnsIndices) {
                        int i = (int) o;
                        if (index == (i + 1)) {
                            //apply mapping to this column
                            string = applyDataMapping(string);
                        }
                    }
                    if (tableName.equals("person")) {
                        //get person_id and uuid

                        if (index == (person_id_column_index + 1)) {
                            person_id = Integer.parseInt(string);
                        }

                        if (index == (uuid_column_index + 1)) {
                            uuid = string;
                        }

                    }
                    //check if string is a date
                    if (string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4} \\d{2}:\\d{2}:\\d{2}")
                            || string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4}")) {
                        java.sql.Date dt = formatDate(string);
                        temp_log = temp_log + "'" + dt.toString() + "'";
                        ps.setDate(index++, dt);
                    } else {
                        if ("".equals(string)) {
                            temp_log = temp_log + "''";
                            ps.setNull(index++, Types.NULL);
                        } else {
                            temp_log = temp_log + "'" + string + "'";
                            ps.setString(index++, string);
                        }

                    }
                    if (counter < headerRow.length) {
                        temp_log = temp_log + ",";
                    } else {
                        temp_log = temp_log + ");";
                        System.out.println(temp_log);
                    }
                    counter++;
                }
                if (tableName.equals("person")) {
                    if (!"".equals(uuid) && person_id != -1) {
                        ps2.setInt(1, person_id);
                        ps2.setInt(2, identifier_type_id);
                        ps2.setString(3, uuid);

                        ps2.addBatch();
                    }
                }

                ps.addBatch();
            }
            if (++count % batchSize == 0) {
                ps.executeBatch();
                if (tableName.equals("person")) {
                    ps2.executeBatch();
                }
            }
        }
        ps.executeBatch(); // insert remaining records
        if (tableName.equals("person")) {
            ps2.executeBatch();
        }

        con.commit();
    } catch (Exception e) {
        if (con != null)
            con.rollback();
        if (db != null)
            db.dispose();
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace,
                "File Error", JOptionPane.ERROR_MESSAGE);
        throw new Exception("Error occured while executing file. " + stacktrace);
    } finally {
        if (null != reader)
            reader.close();
        if (null != ps)
            ps.close();
        if (null != ps2)
            ps2.close();
        if (null != con)
            con.close();

        csvReader.close();
    }
}