java - 这个 Java JDBC 程序有什么问题？
问题描述
这个 Java JDBC 程序有什么问题？
我写了一个方法，它接受一个 ResultSet 并打印它的所有记录。
我的 SQL 查询是：
Set @counter := 0, @counterQty := 0, @counterAvb := 0, @counterIss := 0, @counterRep := 0 ,@counterDes := 0;
Select *
From (SELECT
(Select (@counter := (@counter + 1) ) ) 'Sr.No.',
testt.BookName,
testt.BookQty,
testt.Code,
testt.Available,
testt.Issued,
testt.Repair,
testt.Destroyed,
(@counterQty := @counterQty + testt.BookQty ) TotalQty,
(@counterAvb := @counterAvb + testt.Available ) TotalAvb,
(@counterIss := @counterIss + testt.Issued ) TotalIss,
(@counterRep := @counterRep + testt.Repair ) TotalRep,
(@counterDes := @counterDes + testt.Destroyed ) TotalDest
From (Select a.b_name BookName, a.b_qty BookQty, a.b_acc_id Code,
SUM(case when b.status='A' then 1 else 0 end) as Available,
SUM(case when b.status='I' then 1 else 0 end) as Issued,
SUM(case when b.status='R' then 1 else 0 end) as Repair,
SUM(case when b.status='D' then 1 else 0 end) as Destroyed
From tbl_book_info a left join tbl_books b on a.b_acc_id = b.accid
GROUP BY a.b_name, a.b_qty, a.b_acc_id order by a.b_acc_id
)testt
)Main;
当我在 MySQL Workbench 中执行此查询时，它返回：
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
| Sr.No. | BookName | BookQty | Code | Available | Issued | Repair | Destroyed | TotalQty | TotalAvb | TotalIss | TotalRep | TotalDest |
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
| 1 | Java book | 3 | 10001 | 0 | 0 | 1 | 2 | 3 | 0 | 0 | 1 | 2 |
| 2 | Cpp Book | 5 | 10002 | 3 | 1 | 0 | 1 | 8 | 3 | 1 | 1 | 3 |
| 3 | Cpp 1.17 | 5 | 10003 | 3 | 1 | 0 | 1 | 13 | 6 | 2 | 1 | 4 |
| 4 | Visual Basic | 4 | 10004 | 4 | 0 | 0 | 0 | 17 | 10 | 2 | 1 | 4 |
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
4 rows in set (0.25 sec)
但是，当我在 Java 中执行此查询并打印 ResultSet 的所有数据时，它返回如下：
1 2 3 4 5 6 7 8 9 10 11 12 13
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
| Sr.No. | BookName | BookQty | Code | Available | Issued | Repair | Destroyed | TotalQty | TotalAvb | TotalIss | TotalRep | TotalDest |
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
0 Cpp 1.17 5 10003 3 1 0 1 0 0 0 0 0
0 Cpp Book 5 10002 3 1 0 1 0 0 0 0 0
0 Java book 3 10001 0 0 1 2 0 0 0 0 0
0 Visual Basic 4 10004 4 0 0 0 0 0 0 0 0
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
我的问题是：
为什么它为第 1、9、10、11、12 和 13 列给出零 (0)
表结构
create table if not exists tbl_book_info(
b_acc_id int(5) not null auto_increment,
b_name varchar(50) not null,
b_qty int(2) not null,
b_type varchar(30) not null,
b_auth1 varchar(50) not null,
b_auth2 varchar(50),
b_pub varchar(50) not null,
b_pages int(4) not null,
b_rack int(5) not null,
b_price Decimal(6,2) not null,
b_about text,
primary key(b_acc_id)
);
create table if not exists tbl_books(
accid int(5) references tbl_book_info.b_acc_id,
accno int(3),
status varchar(1) default "A",
primary key(accid,accno)
);
我的打印代码：
Connection con = getDbConnObj();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( sql );
printRsLast(rs);
public void printRsLast(ResultSet rs){
/* +---1----+-----2--------+----3----+--4----+-----5-----+----6---+---7----+-----8-----+-----9----+----10----+----11----+----12----+-----13----+
| Sr.No. | BookName | BookQty | Code | Available | Issued | Repair | Destroyed | TotalQty | TotalAvb | TotalIss | TotalRep | TotalDest |
rs= +--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+
| 1 | Java book | 3 | 10001 | 1 | 0 | 0 | 2 | 3 | 1 | 0 | 0 | 2 |
|...........................................................................................................................................|
|...........................................................................................................................................|
|...........................................................................................................................................|
+--------+--------------+---------+-------+-----------+--------+--------+-----------+----------+----------+----------+----------+-----------+ */
String separator = " ";
try{
rs.beforeFirst();
int n=0;
p("$$$ RS Attr. are : \n\n Sr.No. | BookName | BookQty | Code | Available | Issued | Repair | Destroyed | TotalQty | TotalAvb | TotalIss | TotalRep | TotalDest , Row are...\n");
while(rs.next()){
n++;
String nm , m;
m = rs.getInt(1)+ separator + rs.getString(2)+ separator + rs.getInt(3)+ separator + rs.getInt(4)+ separator + rs.getInt(5)+ separator + rs.getInt(6)+ separator + rs.getInt(7)+ separator + rs.getInt(8)+ separator + rs.getInt(9)+ separator +
rs.getInt(10)+ separator + rs.getInt(11)+ separator + rs.getInt(12)+ separator + rs.getInt(13);
p(m+"\n");
}
p("\nTotal Rows = "+n);
} catch(Exception e){
p("\n!!! Excep in 'printRsLast(ResultSet rs), msg = '"+e.getMessage());
}
}
public Connection getDbConnObj() {
// Creating 'Connection' class' Reference Variable ...
Connection con = null;
String url = "jdbc:mysql://localhost:3306/librarydb";
String dbUname = "root";
String dbPass = "";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection(url, dbUname, dbPass);
} catch (Exception e) {
con = null;
} finally {
return con;
}
}
我尝试了这两种东西......
1)
sql = "Set @counter := 0...";
rs = st.execute(sql); // Execute this 'Set @counter...' Stmt first Than
sql = "Select * From...";
rs = st.executeQuery(sql); // Executing this 'Select * from...' Stmt to get the Tabular Data...
printRsLast(rs);
2)
sql = "Set @counter := 0...
....
....
)Main;"
rs = st.executeQuery(sql); // Executing these Entire Stmt to get the Tabular Data...
printRsLast(rs);
但不幸的是，两者都没有为我工作......
解决方案
您还没有向我们展示您是如何sql在 Java 代码中构造字符串的，所以我将不得不猜测。
似乎它sql包含您Select *以后的查询，没有Set @counter := 0 .... sql如果我设置为这个，我能够重现你的输出。也许您也尝试过将该Set行放入查询中并且产生了错误，因此您将其取出？
您需要做的是执行该Set @counter := 0 ...行，然后运行查询。换句话说，替换行
Connection con = getDbConnObj();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( sql );
printRsLast(rs);
和
Connection con = getDbConnObj();
Statement st = con.createStatement();
st.execute("Set @counter := 0 ...");
ResultSet rs = st.executeQuery( sql );
printRsLast(rs);
我对您的代码进行了此修改，它生成了您想要的输出。
推荐阅读
- replaceall - 如何用下划线替换数据框中的所有空格？
- docker - Dockerized elasticsearch and fscrawler：未能创建elasticsearch客户端，禁用爬虫......连接被拒绝
- php - 在 PHP 中从 cURL 请求解析 XML
- windows - Firebird DB 日志文件位于 Windows 中的什么位置？
- java - 一个执行器中有多少并发任务，Spark 如何处理一个执行器中的任务之间的多线程？
- typescript - 打字稿：无效联合类型
- laravel - 使用存储在 redis 中的模型中的水合物来保留雄辩的关系
- momentjs - 如何根据行程预订时间查找 24 小时差异
- flutter - 如何自动更新文本小部件 - Flutter
- ios - SwiftUI：如果刚刚查看，则无法删除列表项