英文:
Tomcat doesn't sync with the mysql database
问题
我目前正在进行大学项目的工作,正在创建一个非常简单的电子商务风格网站。
我正在使用JDBC驱动程序管理器和连接池来连接数据库,同时使用Tomcat 9.0作为容器。
问题是:当我通过网站修改一些产品(例如,可用数量),网站并不总是反映出更改,而在MySQL Workbench中我始终可以正确地看到数据。
实际上,对于相同的查询,它只在两次尝试中的一次起作用:
- 我在更改后第一次运行查询 -> 它显示旧值
- 我在更改后第二次运行查询 -> 它显示新值
- 我在更改后第三次运行查询 -> 它显示旧值
依此类推。
我已经尝试过关闭缓存(通过查询,使用SQL_NO_CACHE),但似乎没有解决问题,我尝试过使用数据源(Datasource),但它会导致其他问题,我很可能没有时间解决。
这是连接池文件,我认为可能是问题所在,但我不太确定:
public class DriverManagerConnectionPool {
private static List<Connection> freeDbConnections;
static {
freeDbConnections = new LinkedList<Connection>();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("DB driver not found:" + e.getMessage());
}
}
private static synchronized Connection createDBConnection() throws SQLException {
Connection newConnection = null;
String ip = "localhost";
String port = "3306";
String db = "storage";
String username = "root";
String password = "1234";
newConnection = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + port + "/" + db + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", username, password);
newConnection.setAutoCommit(false);
return newConnection;
}
public static synchronized Connection getConnection() throws SQLException {
Connection connection;
if (!freeDbConnections.isEmpty()) {
connection = (Connection) freeDbConnections.get(0);
freeDbConnections.remove(0);
try {
if (connection.isClosed())
connection = getConnection();
} catch (SQLException e) {
connection.close();
connection = getConnection();
}
} else {
connection = createDBConnection();
}
return connection;
}
public static synchronized void releaseConnection(Connection connection) throws SQLException {
if(connection != null) freeDbConnections.add(connection);
}
}
我真的希望您能帮助我,我在网上没有找到任何解决方案!
英文:
I'm currently working on a college project, and I'm creating a very simple e-commerce style website.
I'm using JDBC driver manager and connection pool for the connection to the db, while using Tomcat 9.0 as the container.
The problem is: when I modify some product through the website (let's say the amount available for example), the website doesn't always reflect the changes, while I can always see the data correctly in MySql Workbench.
It actually works one time out of two on the same query:
- I run the query for the first time after the changes -> it shows the old value
- I run the query for the second time after the changes -> it shows the new value
- I run the query for the third time after the changes -> it shows the old value
And so on.
I've already tried to set caching off (from the query, using the SQL_NO_CACHE), but it didn't seem to solve the problem, I've tried to use Datasource instead, but it causes other problems that most likely I won't have the time to solve.
This is the connection pool file, which I think might be problem, I'm not that sure tho:
public class DriverManagerConnectionPool {
private static List<Connection> freeDbConnections;
static {
freeDbConnections = new LinkedList<Connection>();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("DB driver not found:"+ e.getMessage());
}
}
private static synchronized Connection createDBConnection() throws SQLException {
Connection newConnection = null;
String ip = "localhost";
String port = "3306";
String db = "storage";
String username = "root";
String password = "1234";
newConnection = DriverManager.getConnection("jdbc:mysql://"+ ip+":"+ port+"/"+db+"?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", username, password);
newConnection.setAutoCommit(false);
return newConnection;
}
public static synchronized Connection getConnection() throws SQLException {
Connection connection;
if (!freeDbConnections.isEmpty()) {
connection = (Connection) freeDbConnections.get(0);
freeDbConnections.remove(0);
try {
if (connection.isClosed())
connection = getConnection();
} catch (SQLException e) {
connection.close();
connection = getConnection();
}
} else {
connection = createDBConnection();
}
return connection;
}
public static synchronized void releaseConnection(Connection connection) throws SQLException {
if(connection != null) freeDbConnections.add(connection);
}
}
I really hope you can help me, I haven't found any solution online!
答案1
得分: 0
我猜这是因为自动提交被禁用了。请尝试使用 @Transactional 或将自动提交设置为 true。你也可以在每条语句后尝试使用 db.commit。
英文:
I guess it is because of auto-commit is disabled. Please try using @Transactional or set auto-commit to true. You can also try to use db.commit after each statement.
答案2
得分: 0
根据您的连接池实现,池中的所有连接似乎都未自动提交。<br>请检查在执行查询后是否已正确提交了连接。<br>因此,可能情况是,在对同一连接进行更改后执行查询时,会反映出先前进行的更改,而在其他连接上,旧值可能会被返回。
英文:
As per your connection pool implementation, all connection in your pool seems to be auto committed false. <br>
Please check you have properly committed the connection after executing the query or not. <br>
So it might be the case that, when executing the query after changes with same connection it reflects those changes, done earlier and on other connections, old values are might get returned.
专注分享java语言的经验与见解,让所有开发者获益!
评论