java - 如何在 SQL Java 中将列表值作为参数传递
问题描述
我有一个 sql 查询(将值从一个表复制到同一数据库中的另一个表),它需要多个值作为参数,值来自列表。
下面是我尝试过的。虽然我没有收到任何错误,但它不起作用。我想将值插入数据库 List EmployeeIDList= new ArrayList(Arrays.asList(123,823,456,890)); List SalaryList= new ArrayList(Arrays.asList(14437,14297,13846,13441));
con1=DriverManager.getConnection(URL1,DB_UserName,DB_Password);
log.info("Data base connection is established");
for (int i=0,j=0;i<EmployeeIDList.size();i++,j++) {
stmt = con1.prepareStatement("Insert INTO Employee(Name, Year, EmployeeID, Tax, Salary)
Select Name, Year, EmployeeID, Tax, Salary
(Employee
when EmployeeID= ? then ? // 1st ? = EmployeeID,2nd ? = Salary
when EmployeeID = ? then ?
when EmployeeID = ? then ?
else ? // ? = Salary
end ) as Employee
"From Employee2 +
"Where EmployeeIDin (?)");
stmt.setInt(1,SalaryList.get(j));
stmt.setInt(2,EmployeeIDList.get(i));
System.out.println("first salry is " +SalaryList.get(j)) ;
System.out.println("first Employeeid is " +EmployeeIDList.get(i)) ;
rs = stmt.execute();
当我在 sql 中执行“select * from Employee”时,它不返回任何值。
注意:如果我在 sql 中执行上面的插入查询,它工作正常。只有通过 java 我无法插入值。
解决方案
我得到了解决方案。在setInt中编号位置有误。谢谢大家