The problem is the compatibility of older versions of MySQL Connector/J with MySQL 8. You need to upgrade to either MySQL Connector/J 5.1.46 or – better – 8.0.11 (or a higher version).
As the statement executed is not actually DML (eg UPDATE, INSERT or EXECUTE), but a piece of T-SQL which contains DML, I suspect it is not treated as an update-query. Section 13.1.2.3 of the JDBC 4.1 specification states something (rather hard to interpret btw): When the method execute returns true, the method getResultSet is called … Read more
The exact same page explains what a dirty read is: Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid … Read more
The JDBC driver has to be visible to the same classloader as the data source factory itself. The data source factory library is placed in Tomcat’s own /lib folder and thus loaded by Tomcat’s “common” classloader. Your problem sounds much like that you dropped the JDBC driver in webapp’s /WEB-INF/lib. The webapp’s /WEB-INF/lib is invisible … Read more
Spring provides a special DataSource that allows you to do this: SingleConnectionDataSource Changing your code to this should do the trick: SingleConnectionDataSource dataSource = new SingleConnectionDataSource(); …. // The rest stays as is For use in multi-threaded applications, you can make the code re-entrant by borrowing a new connection from the pool and wrapping it … Read more
You should use the setString() method to set the userID. This both ensures that the statement is formatted properly, and prevents SQL injection: statement =con.prepareStatement(“SELECT * from employee WHERE userID = ?”); statement.setString(1, userID); There is a nice tutorial on how to use PreparedStatements properly in the Java Tutorials.
Short answer: Use Statement.setFetchSize(1) before calling executeQuery(). Long answer: This depends very much on which JDBC driver you are using. You might want to take a look at this page, which describes the behavior of MySQL, Oracle, SQL Server, and DB2. Major take-aways: Each database (i.e. each JDBC driver) has its own default behavior. Some … Read more
Use PreparedStatement#setTimestamp() wherein you pass a java.sql.Timestamp which is constructed with System#currentTimeMillis(). preparedStatement.setTimestamp(index, new Timestamp(System.currentTimeMillis())); // … Alternativaly, if the DB supports it, you could also call a DB specific function to set it with the current timestamp. For example MySQL supports now() for this. E.g. String sql = “INSERT INTO user (email, creationdate) VALUES … Read more
You can get all the column names and the entire data from your table using the code below. writeToFile method will contain the logic to writing to file (if that was not obvious enough 🙂 ) ResultSetMetaData metadata = rs.getMetaData(); int columnCount = metadata.getColumnCount(); for (int i = 1; i <= columnCount; i++) { writeToFile(metadata.getColumnName(i) … Read more