List of all column names that are updated.

Question

What I have is below the table.

+----+--------+--------+--------+--------+ | id | val1 | val2 | val3 | val4 | +====+========+========+========+========+ | 1 | data1 | data2 | data3 | data4 | +----+--------+--------+--------+--------+ 

Now I run a query with the details below, say

 UPDATE tableName SET val1='newVal1', val2='data2', val3='data3', val4='data4' WHERE id=1 

Note: This is the same request. In fact, I implement editing from four text fields. So I would use JSF and Java bean as shown below.

 PreparedStatement psmt = conn.prepareStatement(" UPDATE tableName SET val1=?, val2=?, val3=?, val4=? WHERE id=1 "); psmt.setString(1, myValue1); psmt.setString(2, myValue2); psmt.setString(3, myValue3); psmt.setString(4, myValue4); psmt.execute(); 

I want to use the above query which columns are updated. In the above case, the column val1 is updated.

Basically, I want this for the purpose of the magazine, where I found out who did the editing and what kind of editing was done. Any idea / suggestion how to do this?

Note. I do not want to create any other table as indicated in the meewoK answer. Just using a query, I would like to know the names of the columns that are being updated.

---

I will explain what I want with an example.

Let's say userA logs in. Now he has access to user lists. Now let's say that he edited for user B. What he changed is User B, email id, and full name. Now in the magazine what I want is two entries.

 ================================================================= whoDid whatDid forWhom whichField whichSection whatTime ================================================================= userA edit userB emailId User time_whenDid userA edit userB fullName User time_whenDid ================================================================= 

Answer 1

How to skip something like the following query to create a log entry just before :

 PreparedStatement psmt = conn.prepareStatement(" SELECT CASE WHEN val1 = ? THEN '' ELSE 'val1 changed from ' + val1 + ' to ' + ? + '\n' END CASE + CASE WHEN val2 = ? THEN '' ELSE 'val2 changed from ' + val2 + ' to ' + ? + '\n' END CASE + CASE WHEN val3 = ? THEN '' ELSE 'val3 changed from ' + val3 + ' to ' + ? + '\n' END CASE + CASE WHEN val4 = ? THEN '' ELSE 'val4 changed from ' + val4 + ' to ' + ? + '\n' END CASE AS logentry FROM tableName WHERE id=1 "); psmt.setString(1, myValue1); psmt.setString(2, myValue1); psmt.setString(3, myValue2); psmt.setString(4, myValue2); psmt.setString(5, myValue3); psmt.setString(6, myValue3); psmt.setString(7, myValue4); psmt.setString(8, myValue4); ResultSet rs = psmt.executeQuery(); String logentry = rs.getString("logentry"); 

Disclaimer: This does not work, and I'm completely untested, so you may need a little tweaking - should be enough for you to go.

Possible problem: Even if you execute the request immediately before the update, there may have been random data changes between them. Depending on various factors, this may or may not be a problem in practice, but it is worth mentioning.

Answer 2

You can try this code. It stores the journal in a storage class. You can change the storage to suit your requirements. This code only works if db is accessed using a single jvm. I have not tested this code, so I welcome the fix.

 public static void main(String[] args) { PreparedStatement psmt = new LoggingPreparedStatement(conn.prepareStatement( " UPDATE tableName SET val1 =?,val2 =?,val3 =?,val4 =? WHERE id = 1 ")); psmt.setString(1, myValue1); psmt.setString(2, myValue2); psmt.setString(3, myValue3); psmt.setString(4, myValue4); psmt.execute(); for (Trail trail : Storage.INSTANCE.getTrails()) { System.out.println(trail); } } public static class Trail { private final int fieldIdx; private final String prevValue; private final String currentValue; private Trail(int fieldIdx, String prevValue, String currentValue) { this.fieldIdx = fieldIdx; this.prevValue = prevValue; this.currentValue = currentValue; } public int getFieldIdx() { return fieldIdx; } public String getPrevValue() { return prevValue; } public String getCurrentValue() { return currentValue; } @Override public String toString() { return "Trail{" + "fieldIdx=" + fieldIdx + ", prevValue='" + prevValue + '\'' + ", currentValue='" + currentValue + '\'' + '}'; } } public enum Storage { INSTANCE; private final ConcurrentMap<Integer, String> lastValues = new ConcurrentHashMap<Integer, String>(); private final Queue<Trail> trails = new ConcurrentLinkedQueue<Trail>(); private final Object lock = new Object(); public void putTrailIfNecessary(int idx, String newValue) { String lastValue = lastValues.get(idx); if (lastValue == null ? newValue != null : !lastValue.equals(newValue)) { synchronized (lock) { String lastValue1 = lastValues.get(idx); if (lastValue1 == null ? newValue != null : !lastValue1.equals(newValue)) { lastValues.put(idx, newValue); trails.add(new Trail(idx, lastValue1, newValue)); } } } } public List<Trail> getTrails() { return new ArrayList<Trail>(trails); } } public static class LoggingPreparedStatement implements PreparedStatement { private final PreparedStatement delegate; public LoggingPreparedStatement(PreparedStatement delegate) { this.delegate = delegate; } // this we put audit trail @Override public void setString(int parameterIndex, String x) throws SQLException { Storage.INSTANCE.putTrailIfNecessary(parameterIndex, x); delegate.setString(parameterIndex, x); } @Override public ResultSet executeQuery() throws SQLException {return delegate.executeQuery();} @Override public int executeUpdate() throws SQLException {return delegate.executeUpdate();} @Override public void setNull(int parameterIndex, int sqlType) throws SQLException {delegate.setNull(parameterIndex, sqlType);} @Override public void setBoolean(int parameterIndex, boolean x) throws SQLException {delegate.setBoolean(parameterIndex, x);} @Override public void setByte(int parameterIndex, byte x) throws SQLException {delegate.setByte(parameterIndex, x);} @Override public void setShort(int parameterIndex, short x) throws SQLException {delegate.setShort(parameterIndex, x);} @Override public void setInt(int parameterIndex, int x) throws SQLException {delegate.setInt(parameterIndex, x);} @Override public void setLong(int parameterIndex, long x) throws SQLException {delegate.setLong(parameterIndex, x);} @Override public void setFloat(int parameterIndex, float x) throws SQLException {delegate.setFloat(parameterIndex, x);} @Override public void setDouble(int parameterIndex, double x) throws SQLException {delegate.setDouble(parameterIndex, x);} @Override public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException {delegate.setBigDecimal(parameterIndex, x);} @Override public void setBytes(int parameterIndex, byte[] x) throws SQLException {delegate.setBytes(parameterIndex, x);} @Override public void setDate(int parameterIndex, Date x) throws SQLException {delegate.setDate(parameterIndex, x);} @Override public void setTime(int parameterIndex, Time x) throws SQLException {delegate.setTime(parameterIndex, x);} @Override public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {delegate.setTimestamp(parameterIndex, x);} @Override public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException {delegate.setAsciiStream(parameterIndex, x, length);} @Override public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException {delegate.setUnicodeStream(parameterIndex, x, length);} @Override public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException {delegate.setBinaryStream(parameterIndex, x, length);} @Override public void clearParameters() throws SQLException {delegate.clearParameters();} @Override public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException {delegate.setObject(parameterIndex, x, targetSqlType);} @Override public void setObject(int parameterIndex, Object x) throws SQLException {delegate.setObject(parameterIndex, x);} @Override public boolean execute() throws SQLException {return delegate.execute();} @Override public void addBatch() throws SQLException {delegate.addBatch();} @Override public void setCharacterStream(int parameterIndex, Reader reader, int length) throws SQLException {delegate.setCharacterStream(parameterIndex, reader, length);} @Override public void setRef(int parameterIndex, Ref x) throws SQLException {delegate.setRef(parameterIndex, x);} @Override public void setBlob(int parameterIndex, Blob x) throws SQLException {delegate.setBlob(parameterIndex, x);} @Override public void setClob(int parameterIndex, Clob x) throws SQLException {delegate.setClob(parameterIndex, x);} @Override public void setArray(int parameterIndex, Array x) throws SQLException {delegate.setArray(parameterIndex, x);} @Override public ResultSetMetaData getMetaData() throws SQLException {return delegate.getMetaData();} @Override public void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException {delegate.setDate(parameterIndex, x, cal);} @Override public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {delegate.setTime(parameterIndex, x, cal);} @Override public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException {delegate.setTimestamp(parameterIndex, x, cal);} @Override public void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException {delegate.setNull(parameterIndex, sqlType, typeName);} @Override public void setURL(int parameterIndex, URL x) throws SQLException {delegate.setURL(parameterIndex, x);} @Override public ParameterMetaData getParameterMetaData() throws SQLException {return delegate.getParameterMetaData();} @Override public void setRowId(int parameterIndex, RowId x) throws SQLException {delegate.setRowId(parameterIndex, x);} @Override public void setNString(int parameterIndex, String value) throws SQLException {delegate.setNString(parameterIndex, value);} @Override public void setNCharacterStream(int parameterIndex, Reader value, long length) throws SQLException {delegate.setNCharacterStream(parameterIndex, value, length);} @Override public void setNClob(int parameterIndex, NClob value) throws SQLException {delegate.setNClob(parameterIndex, value);} @Override public void setClob(int parameterIndex, Reader reader, long length) throws SQLException {delegate.setClob(parameterIndex, reader, length);} @Override public void setBlob(int parameterIndex, InputStream inputStream, long length) throws SQLException {delegate.setBlob(parameterIndex, inputStream, length);} @Override public void setNClob(int parameterIndex, Reader reader, long length) throws SQLException {delegate.setNClob(parameterIndex, reader, length);} @Override public void setSQLXML(int parameterIndex, SQLXML xmlObject) throws SQLException {delegate.setSQLXML(parameterIndex, xmlObject);} @Override public void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) throws SQLException {delegate.setObject(parameterIndex, x, targetSqlType, scaleOrLength);} @Override public void setAsciiStream(int parameterIndex, InputStream x, long length) throws SQLException {delegate.setAsciiStream(parameterIndex, x, length);} @Override public void setBinaryStream(int parameterIndex, InputStream x, long length) throws SQLException {delegate.setBinaryStream(parameterIndex, x, length);} @Override public void setCharacterStream(int parameterIndex, Reader reader, long length) throws SQLException {delegate.setCharacterStream(parameterIndex, reader, length);} @Override public void setAsciiStream(int parameterIndex, InputStream x) throws SQLException {delegate.setAsciiStream(parameterIndex, x);} @Override public void setBinaryStream(int parameterIndex, InputStream x) throws SQLException {delegate.setBinaryStream(parameterIndex, x);} @Override public void setCharacterStream(int parameterIndex, Reader reader) throws SQLException {delegate.setCharacterStream(parameterIndex, reader);} @Override public void setNCharacterStream(int parameterIndex, Reader value) throws SQLException {delegate.setNCharacterStream(parameterIndex, value);} @Override public void setClob(int parameterIndex, Reader reader) throws SQLException {delegate.setClob(parameterIndex, reader);} @Override public void setBlob(int parameterIndex, InputStream inputStream) throws SQLException {delegate.setBlob(parameterIndex, inputStream);} @Override public void setNClob(int parameterIndex, Reader reader) throws SQLException {delegate.setNClob(parameterIndex, reader);} @Override public ResultSet executeQuery(String sql) throws SQLException {return delegate.executeQuery(sql);} @Override public int executeUpdate(String sql) throws SQLException {return delegate.executeUpdate(sql);} @Override public void close() throws SQLException {delegate.close();} @Override public int getMaxFieldSize() throws SQLException {return delegate.getMaxFieldSize();} @Override public void setMaxFieldSize(int max) throws SQLException {delegate.setMaxFieldSize(max);} @Override public int getMaxRows() throws SQLException {return delegate.getMaxRows();} @Override public void setMaxRows(int max) throws SQLException {delegate.setMaxRows(max);} @Override public void setEscapeProcessing(boolean enable) throws SQLException {delegate.setEscapeProcessing(enable);} @Override public int getQueryTimeout() throws SQLException {return delegate.getQueryTimeout();} @Override public void setQueryTimeout(int seconds) throws SQLException {delegate.setQueryTimeout(seconds);} @Override public void cancel() throws SQLException {delegate.cancel();} @Override public SQLWarning getWarnings() throws SQLException {return delegate.getWarnings();} @Override public void clearWarnings() throws SQLException {delegate.clearWarnings();} @Override public void setCursorName(String name) throws SQLException {delegate.setCursorName(name);} @Override public boolean execute(String sql) throws SQLException {return delegate.execute(sql);} @Override public ResultSet getResultSet() throws SQLException {return delegate.getResultSet();} @Override public int getUpdateCount() throws SQLException {return delegate.getUpdateCount();} @Override public boolean getMoreResults() throws SQLException {return delegate.getMoreResults();} @Override public void setFetchDirection(int direction) throws SQLException {delegate.setFetchDirection(direction);} @Override public int getFetchDirection() throws SQLException {return delegate.getFetchDirection();} @Override public void setFetchSize(int rows) throws SQLException {delegate.setFetchSize(rows);} @Override public int getFetchSize() throws SQLException {return delegate.getFetchSize();} @Override public int getResultSetConcurrency() throws SQLException {return delegate.getResultSetConcurrency();} @Override public int getResultSetType() throws SQLException {return delegate.getResultSetType();} @Override public void addBatch(String sql) throws SQLException {delegate.addBatch(sql);} @Override public void clearBatch() throws SQLException {delegate.clearBatch();} @Override public int[] executeBatch() throws SQLException {return delegate.executeBatch();} @Override public Connection getConnection() throws SQLException {return delegate.getConnection();} @Override public boolean getMoreResults(int current) throws SQLException {return delegate.getMoreResults(current);} @Override public ResultSet getGeneratedKeys() throws SQLException {return delegate.getGeneratedKeys();} @Override public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {return delegate.executeUpdate(sql, autoGeneratedKeys);} @Override public int executeUpdate(String sql, int[] columnIndexes) throws SQLException {return delegate.executeUpdate(sql, columnIndexes);} @Override public int executeUpdate(String sql, String[] columnNames) throws SQLException {return delegate.executeUpdate(sql, columnNames);} @Override public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {return delegate.execute(sql, autoGeneratedKeys);} @Override public boolean execute(String sql, int[] columnIndexes) throws SQLException {return delegate.execute(sql, columnIndexes);} @Override public boolean execute(String sql, String[] columnNames) throws SQLException {return delegate.execute(sql, columnNames);} @Override public int getResultSetHoldability() throws SQLException {return delegate.getResultSetHoldability();} @Override public boolean isClosed() throws SQLException {return delegate.isClosed();} @Override public void setPoolable(boolean poolable) throws SQLException {delegate.setPoolable(poolable);} @Override public boolean isPoolable() throws SQLException {return delegate.isPoolable();} @Override public <T> T unwrap(Class<T> iface) throws SQLException {return delegate.unwrap(iface);} @Override public boolean isWrapperFor(Class<?> iface) throws SQLException {return delegate.isWrapperFor(iface);} } 

Answer 3

You can do one of the following:

• Create a stored procedure in mysql that validates and updates the second table.
• Create an updatable view and use the MySQL update conditions in a single query (UPDATE, SET and CASE) , which will check if the update value is different than the current value and update flag.
• Check clients for initial values ​​and which fields in the client have been changed.

If you really want to do this in one query, one interesting approach is option 2 ...

Option 2

1) Create a second table called tableNamePrevious, which has the following fields:

 + id_prev + val1_prev + val2_prev + val3_prev + val4_prev ... 

The previous values ​​will be saved in this table when changes are made. If no changes are made (NULL), will be entered.

2) Create an UPDATABLE view with the following query (the following rules: http://dev.mysql.com/doc/refman//5.5/en/view-updatability.html )

 create view tableNameView as select * from tableName, tableNamePrevious where tableName.id = tableNamePrevious.id_prev; 

3) Run the update in the view using the update conditions:

 update tableNameView SET val1_prev = CASE WHEN val1 = INPUT_VAL1 THEN NULL WHEN NOT val1 = INPUT_VAL1 THEN val1 END, val2_prev = CASE WHEN val2 = INPUT_VAL2 THEN NULL WHEN NOT val2 = INPUT_VAL2 THEN val2 END, ... , val1='newVal1', val2='data2', ... , where id = INPUT_ID; 

So, when the new INPUT_VALUE does not match the existing one, the field in the second table will be old. Otherwise, it will be null.

Using the displayed view means that you are updating the table and the log table containing the changes.

Case statements mean that you can verify that the new input values ​​do not match the previous ones.

Answer 4

Have you considered using a trigger? This is based on the Wikipedia example:

 CREATE TRIGGER user_trigger BEFORE UPDATE ON user_table REFERENCING NEW ROW AS n, OLD ROW AS o FOR EACH ROW IF n.val1 <> o.val1 THEN INSERT INTO Log (whoDid, whatDid, ...) VALUES (..., 'edit', ...) END IF; ;