java - 为什么“prepareCall”会抛出 NumberFormatException？
问题描述
我正在用 Java 编写一个需要与数据库交互的软件（本地主机上的 MySQL Workbench）。在我的 DAO 类中，我检索了一个全局数据库连接，如果它还没有打开，它将被打开。当我的程序调用“prepareCall”时，会抛出 NumberFormatException。这种情况只发生在某些（大多数）查询中，但不会发生在其他查询中，即使代码没有改变。
我还想指出，查询在工作台中执行得很好。
道
public static User selectPersonalInfo(Integer id) throws SQLException {
CallableStatement stmt = null;
ResultSet res = null;
User user = null;
try {
Connection conn = ConnectionManager.getConnection();
stmt = conn.prepareCall(RoutinesIdentifier.GET_USER);
res = RoutinesManager.bindParametersAndExec(stmt, id);
if (res.first()){
user = new User(res.getString("email"), res.getString("pwd"), res.getString("first_name"), res.getString("last_name"));
user.setCity(res.getString("city"));
user.setBirth(res.getDate("birth").toInstant().atZone(ZoneId.systemDefault()).toLocalDate());
}
res.close();
} catch (SQLException e) {
throw new SQLException("An error occured while trying to retrieve personal information.");
}catch(NumberFormatException ne) {
ne.printStackTrace();
System.exit(0);
} finally {
if(stmt != null) {
stmt.close();
}
}
return user;
}
联系
public static Connection getConnection() throws SQLException{
if (conn == null) {
conn = DriverManager.getConnection(url, user, pass);
}
return conn;
}
例程标识符
public static final String GET_USER = "call wwj_db.retrieve_user(?);";
例程管理器
public static ResultSet bindParametersAndExec(CallableStatement stmt, int ... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
stmt.setInt(i+1, params[i]);
}
return executeStmt(stmt);
}
public static ResultSet executeStmt(CallableStatement stmt) throws SQLException {
if(stmt.execute()) {
return stmt.getResultSet();
}else {
return null;
}
}
MySQL
CREATE PROCEDURE `retrieve_user` (in var_id int)
BEGIN
SELECT `user`.email, `user`.pwd, `user`.first_name, `user`.last_name, `user`.city, `user`.birth
FROM `account` join `user` on `account`.user = `user`.email
WHERE `account`.id = var_id;
END
堆栈跟踪
java.lang.NumberFormatException: Invalid integer format for value 'PRIMARY'
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.getLong(MysqlTextValueDecoder.java:223)
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.getInt(MysqlTextValueDecoder.java:152)
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeInt2(MysqlTextValueDecoder.java:97)
at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:153)
at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241)
at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1296)
at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:797)
at com.mysql.cj.jdbc.CallableStatement$CallableStatementParamInfo.addParametersFromDBMD(CallableStatement.java:254)
at com.mysql.cj.jdbc.CallableStatement$CallableStatementParamInfo.<init>(CallableStatement.java:206)
at com.mysql.cj.jdbc.CallableStatement.convertGetProcedureColumnsToInternalDescriptors(CallableStatement.java:831)
at com.mysql.cj.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:805)
at com.mysql.cj.jdbc.CallableStatement.<init>(CallableStatement.java:586)
at com.mysql.cj.jdbc.CallableStatement.getInstance(CallableStatement.java:485)
at com.mysql.cj.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:1516)
at com.mysql.cj.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:1541)
at com.mysql.cj.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:1532)
at logic.persistence.dao.UserDAO.selectPersonalInfo(UserDAO.java:27)
at logic.domain.User.getPersonalInfoFromDB(User.java:85)
at logic.application.control.AccountControl.retrievePersonalInfo(AccountControl.java:72)
at logic.bean.UserBean.getPersonalInfo(UserBean.java:103)
at logic.presentation.control.PersonalInfoGraphic.initialize(PersonalInfoGraphic.java:78)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2548)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2441)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2409)
at logic.presentation.GraphicHandler.switchScreen(GraphicHandler.java:106)
at logic.presentation.GraphicHandler.openSection(GraphicHandler.java:119)
at logic.presentation.control.AccountGraphic.openPersonalInfo(AccountGraphic.java:104)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1771)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8411)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:432)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:410)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:937)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$4(WinApplication.java:187)
at java.lang.Thread.run(Thread.java:748)
解决方案
看起来数据库中的“PRIMARY”数据类型不是 INT 格式。因此，无论数据库中有什么，请在 Java 代码中使用相应的数据类型。
推荐阅读
- android - android webview 中的 Django csrf 验证失败。android webview 无法验证 django csrf 令牌
- python - Python - 区分相似的图像
- bison - 野牛移位/减少与平衡括号语法的冲突
- ssas - SSAS 计算成员中的 MDX 查询
- file-upload - 空帖子值：CodeIgniter
- java - 如何在Java中为字符串句子添加数字？
- python - 从 html 输入获取数据到 python 变量？
- go - 除了可移植性和安全性原因之外，为什么有人想通过 WebAssembly 在 Web 浏览器中运行他们现有的 go/rust/c++ 应用程序？
- java - 在非 Lagom 应用程序中嵌入 Lagom 读取端处理器
- python - 在 Pandas 上切片多索引