<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
Test for Prepared Statement INSERT..ON DUPLICATE KEY with TIMESTAMP
<%@ page language="java" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Timestamp" %>
<%@ page import="java.lang.StringBuilder" %>
<%
Connection conn = null;
StringBuilder sb=new StringBuilder();
try
{
sb.append("OPEN DATABASE
");
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost/******", "root", "******");
sb.append("... database open
");
sb.append("SHOW TIMESTAMP
");
String showQuery="Select ts from `test`.`tsonuptest` WHERE `id`='1';";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(showQuery);
rs.next();
Timestamp ts = rs.getTimestamp(1);
sb.append("... timestamp set to "+ts.toString()+"
");
// Test UPDATE to same values using statement and prepared statement
String psQuery="UPDATE `test`.`tsonuptest` SET `txt`='b' WHERE `id`='1';";
sb.append("STATEMENT: UPDATE record to same values.
");
sb.append("... query is \""+psQuery+"\"
");
int returnint = st.executeUpdate(psQuery);
rs = st.executeQuery(showQuery);
rs.next();
ts = rs.getTimestamp(1);
sb.append("... st.executeUpdate returned "+returnint+", timestamp set to "+ts.toString()+"
");
%><%
sb.append("PREPARED STATEMENT: UPDATE record to same values.
");
sb.append("... query is \""+psQuery+"\"
");
sb.append("... build prepared statement
");
PreparedStatement ps=ps = conn.prepareStatement(psQuery);
returnint = ps.executeUpdate();
rs = st.executeQuery(showQuery);
rs.next();
ts = rs.getTimestamp(1);
sb.append("... st.executeUpdate returned "+returnint+", timestamp set to "+ts.toString()+"
");
%><%
// Now try INSERT ... ON DUPLICATE KEY UPDATE ...
psQuery="INSERT `test`.`tsonuptest` SET `txt`='b' ON DUPLICATE KEY UPDATE `txt`='b';";
sb.append("STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
");
sb.append("... query is \""+psQuery+"\"
");
returnint = st.executeUpdate(psQuery);
rs = st.executeQuery(showQuery);
rs.next();
ts = rs.getTimestamp(1);
sb.append("... st.executeUpdate returned "+returnint+", timestamp set to "+ts.toString()+"
");
sb.append("PREPARED STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
");
sb.append("... query is \""+psQuery+"\"
");
ps=ps = conn.prepareStatement(psQuery);
returnint = ps.executeUpdate();
rs = st.executeQuery(showQuery);
rs.next();
ts = rs.getTimestamp(1);
sb.append("... st.executeUpdate returned "+returnint+", timestamp set to "+ts.toString()+"
");
%><%
// Now try INSERT ... ON DUPLICATE KEY UPDATE ...
psQuery="INSERT `test`.`tsonuptest` SET `txt`='b',ts=NOW() ON DUPLICATE KEY UPDATE `txt`='b',ts=NOW();";
sb.append("WORKAROUND: Set the timestamp value before the call");
sb.append("STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
");
sb.append("... query is \""+psQuery+"\"
");
returnint = st.executeUpdate(psQuery);
rs = st.executeQuery(showQuery);
rs.next();
ts = rs.getTimestamp(1);
sb.append("... st.executeUpdate returned "+returnint+", timestamp set to "+ts.toString()+"
");
// wait a second to force new value for NOW()
Thread.sleep(1000);
sb.append("PREPARED STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
");
sb.append("... query is \""+psQuery+"\"
");
ps=ps = conn.prepareStatement(psQuery);
returnint = ps.executeUpdate();
rs = st.executeQuery(showQuery);
rs.next();
ts = rs.getTimestamp(1);
sb.append("... st.executeUpdate returned "+returnint+", timestamp set to "+ts.toString()+"
");
%><%
}
catch (java.sql.SQLException ex)
{
sb.append("SQLException: " + ex.getMessage());
}
finally
{
conn.close();
}
%>
Test for Prepared Statement INSERT..ON DUPLICATE KEY with TIMESTAMP
Uses existing table with single record:
CREATE TABLE `tsonuptest` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`txt` VARCHAR(10) DEFAULT NULL,
`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `tsonuptest` (txt) VALUES ('b');
alter table `test`.`tsonuptest` add unique `txt` (`txt`);
Results from processing...
<%=sb.toString()%>