DatabaseTarget.java

package hu.sztaki.ilab.longneck.process.access;

import hu.sztaki.ilab.longneck.Record;
import hu.sztaki.ilab.longneck.util.DatabaseUtils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.metadata.TableMetaDataContext;
import org.springframework.jdbc.core.metadata.TableMetaDataProvider;
import org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory;
import org.springframework.jdbc.core.metadata.TableParameterMetaData;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

/**
 *
 * @author Molnár Péter <molnarp@sztaki.mta.hu>
 */
public class DatabaseTarget extends AbstractDatabaseAccessor implements Target {
    
    public final Logger log = Logger.getLogger(DatabaseTarget.class);
    /** The query to truncate the target table. */    
    private String truncateQuery;
    /** The query to insert record into target table. */
    private String insertQuery;
    /** Table column name to SQL type map. */
    private Map<String,Integer> typeMap = new HashMap<String,Integer>();
    /** Parameter source array. */
    private SqlParameterSourceRecordWrapper[] parameters;
    
    @Override
    public void truncate() {
        jdbcTemplate.update(truncateQuery);
    }

    @Override
    public void appendRecords(Collection<Record> records) {
        // Create jdbc template
        final NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate(jdbcTemplate);
        
        // Create array of parameter sources
        int len = records.size();
        if (parameters == null || parameters.length != len) {
            parameters =  new SqlParameterSourceRecordWrapper[records.size()];
        }
        
        // Wrap records with type mapper
        int i = 0;
        for (Record r : records) {
            if (parameters[i] == null || ! (parameters[i] instanceof SqlParameterSourceRecordWrapper)) {
                parameters[i] = new SqlParameterSourceRecordWrapper();
                parameters[i].setTypeMap(typeMap);
            }            
            parameters[i].setRecord(r);
            ++i;
        }
        
        // Batch update in transaction
        TransactionTemplate txTemplate = new TransactionTemplate(txManager);
        try {
            txTemplate.execute(new TransactionCallbackWithoutResult() {

                @Override
                public void doInTransactionWithoutResult(TransactionStatus ts) {
                    npjt.batchUpdate(insertQuery, parameters);
                }
            });
            
            return;
        } catch (RuntimeException ex) {
            log.warn(this, ex);
        }
        
        // Do line by line
        int insertCount = 0;
        for (i = 0; i < parameters.length; ++i) {
            try {
                npjt.update(insertQuery, parameters[i]);
                ++insertCount;
            } catch (RuntimeException ex) {
                log.warn(parameters[i].getRecord().toString(), ex);
            }
        }
        
        log.info(String.format("Inserted %1$d rows out of %2$d total.", 
                insertCount, parameters.length));
    }

    public String getInsertQuery() {
        return insertQuery;
    }

	public void setInsertQuery(String insertQuery) {
		this.insertQuery = DatabaseUtils.replaceQueryBindVariableDashes(insertQuery);
	}

    public String getTruncateQuery() {
        return truncateQuery;
    }

    public void setTruncateQuery(String truncateQuery) {
        this.truncateQuery = truncateQuery;
    }

	@Override
	public void close() {
		//NOPMD		
	}

    @Override
    public void init() {
        
        try {
            // Read type metadata from database connection
            Connection connection = this.dataSource.getConnection();
            DatabaseMetaData dbMetadata = connection.getMetaData();
            
            // Determine table name from insert query
            TableMetaDataContext context = new TableMetaDataContext();            
            String tableName = getTableNameFromInsertQuery(insertQuery);
            int i = tableName.lastIndexOf('.');
            if (i >= 0) {
                context.setSchemaName(tableName.substring(0, i));
                context.setTableName(tableName.substring(i+1));
            } else {
                context.setTableName(tableName);
            }
            context.setAccessTableColumnMetaData(true);
            
            // Try to parse column placeholders
            Map<String,String> placeholderMap = null;
            try {
                placeholderMap = getPlaceholderMap(insertQuery);
            } catch (QueryParseException ex) {
                // do nothing
            }
            
            // Read table metadata and initialize record wrapper data
            TableMetaDataProvider tableMetadata = 
                    TableMetaDataProviderFactory.createMetaDataProvider(dataSource, context);
            List<TableParameterMetaData> columns = tableMetadata.getTableParameterMetaData();
            
            // Set map with types
            for (TableParameterMetaData pm : columns) {
                
                if (placeholderMap != null) {
                    // If placeholder map is defined, exchange column names with corresponding
                    // bind variable
                    typeMap.put(placeholderMap.get(pm.getParameterName()), pm.getSqlType());
                    // Also define column name, if not defined in the placeholder mapping
                    if (! placeholderMap.containsKey(pm.getParameterName())) {
                        typeMap.put(pm.getParameterName(), pm.getSqlType());
                    }
                } else {
                    // Define column name as best guess
                    typeMap.put(pm.getParameterName(), pm.getSqlType());
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(this.getClass()).error(ex);
            throw new RuntimeException(ex);
        }
    }
    
    public static String getTableNameFromInsertQuery(String insertQuery) {
        Pattern pattern = Pattern.compile("insert\\s+into\\s+([\\w\\.]+)\\s+");
        Matcher m = pattern.matcher(insertQuery);
        
        if (m.find()) {
            return m.group(1);
        }
        
        // TODO replace with proper exception
        throw new RuntimeException(                
                String.format("Could not determine table name from insert query: %1$s", 
                insertQuery));
    }
    
    public static Map<String,String> getPlaceholderMap(String insertQuery) 
            throws QueryParseException {
        Pattern columnsPattern = Pattern.compile(
                "insert\\s+into\\s+(?:[\\w\\.]+)\\s+\\(((?:\\s*[\\w\\.]+\\s*,)*\\s*[\\w\\.]+\\s*)\\)", 
                Pattern.CASE_INSENSITIVE);
        Matcher m = columnsPattern.matcher(insertQuery);
        
        List<String> columns;
        if (m.find()) {
            columns = Arrays.asList(m.group(1).split(","));
        } else {
            throw new QueryParseException(
                    String.format("Could not determine column names from insert query: %1$s", 
                    insertQuery));
        }
        
        Pattern placeholdersPattern = Pattern.compile(
                "values\\s*\\(((?:\\s*\\:[\\w\\.]+\\s*,)*\\s*\\:[\\w\\.]+\\s*)\\)", 
                Pattern.CASE_INSENSITIVE);
        Matcher m2 = placeholdersPattern.matcher(insertQuery);

        Map<String,String> placeholderMap = new HashMap<String,String>(columns.size());
        List<String> placeholders;
        if (m2.find()) {
            placeholders = Arrays.asList(m2.group(1).split(","));            
        } else {
            throw new QueryParseException(
                    String.format("Could not determine placeholder names from insert query: %1$s", 
                    insertQuery));
        }
        
        if (columns.size() != placeholders.size()) {
            throw new QueryParseException(
                    String.format("Could not determine column-placeholder mapping because of differing token counts: %1$s", 
                    insertQuery));            
        }
        
        for (int i = 0; i < columns.size(); ++i) {
            placeholderMap.put(columns.get(i).trim(), placeholders.get(i).trim().substring(1));
        }
        
        return placeholderMap;
    }
}