英文:
How to display sum of columns from sql database into Java application?
问题
我有一个数据库，其中存储了我的项目记录，在一个名为'quantity'的列中，我想能够检索这个'quantity'数量并在我的Java应用程序中显示。如果您看到下面的代码，您会看到我正在尝试从我的数据库中打印出该数量，但它没有起作用。当我运行我的应用程序时，总是运行catch块，而不是try块。我不确定是否可能我没有正确编写SQL代码，但这只是我的猜测，如果您知道如何解决这个问题，请告诉我，谢谢。
以下是你的代码，我已经删除了不需要翻译的部分：
private void initComponents() {
private String sum;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost/restock", "root", "password");
String template = "SELECT SUM(quantity) as sumquantity FROM buysupply WHERE itemtype IN ('Disposable mask', 'Clothed mask', 'N95 mask')";
PreparedStatement pst = con1.prepareStatement(template);
ResultSet rs = pst.executeQuery();
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);
} catch (ClassNotFoundException ex) {
Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
}
}
关于错误信息：
加载类`com.mysql.jdbc.Driver'。这已经不推荐使用了。新的驱动程序类是`com.mysql.cj.jdbc.Driver'。驱动程序会通过SPI自动注册，手动加载驱动程序类通常是不必要的。
Aug 06, 2020 1:22:03 AM pleasework.stock initComponents
SEVERE: null
java.sql.SQLException: 在结果集开始之前
希望这可以帮助你解决问题。如果您有任何其他疑问，请随时提出。
英文:
I have a database which stores records of my project, in one of the columns which is called 'quantity', I want to be able to retrieve this 'quantity' amount and display in my java application. If you see my code below you can see that I am trying to print out that amount from my database but it is not working. When I run my application, it is always the catch that is running and not the try. I am unsure if maybe I did not do the SQL code correctly but that's just my guess, if you know of how to solve this problem do let me know thank you.
private void initComponents() {
private String sum;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost/restock", "root", "password");
String template = "SELECT SUM(quantity) as sumquantity FROM buysupply WHERE itemtype IN ('Disposable mask', 'Clothed mask', 'N95 mask')";
PreparedStatement pst = con1.prepareStatement(template);
ResultSet rs = pst.executeQuery();
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);
} catch (ClassNotFoundException ex) {
Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
}
}
For your info, the error is this:
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Aug 06, 2020 1:22:03 AM pleasework.stock initComponents
SEVERE: null
java.sql.SQLException: Before start of result set
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.result.ResultSetImpl.checkRowPos(ResultSetImpl.java:492)
at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:845)
at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:863)
at pleasework.stock.initComponents(stock.java:92)
at pleasework.stock.<init>(stock.java:30)
at pleasework.stock$16.run(stock.java:823)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
答案1
得分: 1
你需要在使用 rs.getString('...') 之前调用 rs.next() 将第一行加载到结果集中。
ResultSet rs = pst.executeQuery();
rs.next();
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);
你还应该使用 rs.next() 来检查是否收到了结果。例如：
ResultSet rs = pst.executeQuery();
if (rs.next()) {
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);
} else {
System.out.print("查询没有返回任何结果");
}
还有一件引人注目的事情：你使用 getString() 来获取数值。你可能想要使用 rs.getInt() 或 rs.getDouble() 来代替。
英文:
You have to call rs.next() to load the first row into your resultset before you can use rs.getString('...').
ResultSet rs = pst.executeQuery();
rs.next();
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);
You should also use rs.next() to check if you received a result. So for example:
ResultSet rs = pst.executeQuery();
if (rs.next()) {
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);
} else {
System.out.print("Query didn't return any results");
}
Another thing that attracts attention: You use getString() to get a numeric value. You probably want to use rs.getInt() or rs.getDouble() instead.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论