Re: Update existent hibernate application with jasypt

I ran into this problem as well.  I wrote a class to batch encrypt a table column, below is my code.  Now if i could just figure out why I can't do a hibernate query on the encrypted field...  




import org.jasypt.hibernate.encryptor.HibernatePBEStringEncryptor;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.context.ApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * Used to encrypt a string database column.  It can either encrypt or decrypt a table column.  Set the properties and then call encrypt to execute
 * the operation
 *
 * @author Ryan Wexler
 * @date Jun 18, 2009
 */
public class EncryptStringTableColumn
    {
    /**
     * the encryptor to use to encrypt/decrypt the column
     */
    private HibernatePBEStringEncryptor encryptor;
    /**
     * data source to connect to
     */
    private DataSource dataSource;
    /**
     * table name
     */
    private String tableName;
    /**
     * column to encrypt
     */
    private String columnNameToEncrypt;
    /**
     * the integer unique identifier of the table
     */
    private String idColumnName;
    /**
     * if set to true then it encrypts the column, if set to false it decrypts the column by default true
     */
    private boolean encrypt = true;


    public void setEncryptor(HibernatePBEStringEncryptor encryptor)
        {
        this.encryptor = encryptor;
        }

    public void setDataSource(DataSource dataSource)
        {
        this.dataSource = dataSource;
        }

    public void setTableName(String tableName)
        {
        this.tableName = tableName;
        }

    public void setColumnNameToEncrypt(String columnNameToEncrypt)
        {
        this.columnNameToEncrypt = columnNameToEncrypt;
        }

    public void setIdColumnName(String idColumnName)
        {
        this.idColumnName = idColumnName;
        }


    public void setEncrypt(boolean encrypt)
        {
        this.encrypt = encrypt;
        }

    public void encrypt()
        {
        try
            {
            Connection connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement statement1 = connection.prepareStatement("select " + idColumnName + ", " + columnNameToEncrypt + "  from " + tableName);
            ResultSet result = statement1.executeQuery();
            while (result.next())
                {
                PreparedStatement statement2 = connection.prepareStatement("update " + tableName + " set " + columnNameToEncrypt + " = ? where " + idColumnName + "= ?");
                if (encrypt)
                    statement2.setString(1, encryptor.encrypt(result.getString(columnNameToEncrypt)));
                else
                    statement2.setString(1, encryptor.decrypt(result.getString(columnNameToEncrypt)));
                statement2.setInt(2, result.getInt(idColumnName));
                statement2.execute();
                statement2.close();
                }
            connection.commit();
            }
        catch (Exception x)
            {
            x.printStackTrace();
            }
        }

    /**
     * @param args the first argument should be the filename holding the spring context that holds the properties of the encryptor
     *             The bean names are as follows:
     *             encryptor
     *             dataSource
     *             columnNameToEncrypt
     *             idColumnName
     *             tableName
     *             encrypt
     */
    public static void main(String[] args)
        {
        final ApplicationContext context = new FileSystemXmlApplicationContext(args[0]);
        EncryptStringTableColumn encryptStringTableColumn = new EncryptStringTableColumn();
        encryptStringTableColumn.setEncryptor((HibernatePBEStringEncryptor) context.getBean("encryptor"));
        encryptStringTableColumn.setDataSource((DataSource) context.getBean("dataSource"));
        encryptStringTableColumn.setColumnNameToEncrypt((String) context.getBean("columnNameToEncrypt"));
        encryptStringTableColumn.setIdColumnName((String) context.getBean("idColumnName"));
        encryptStringTableColumn.setTableName((String) context.getBean("tableName"));
        encryptStringTableColumn.setEncrypt((Boolean) context.getBean("encrypt"));
        encryptStringTableColumn.encrypt();
        System.exit(0);
        }
    }