Java：使用Result Test在循环内插入INTO(Java: INSERT INTO inside a loop using Result Test)

Java：使用Result Test在循环内插入INTO(Java: INSERT INTO inside a loop using Result Test)

当我尝试在我的数据库中插入一些数据（MS-SQL）时，我遇到了一个小问题。 我正在使用这段代码（我只是复制粘贴所有这些代码，所以有人可能会理解我没有的东西）

try { Connection connection = null; Statement Statement = null; ResultSet ResultSet = null; String query = ""; // jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]] String host = "jdbc:sqlserver://server;databaseName=db"; String username = "user"; String password = "pass"; Statement = connection.createStatement(); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Calendar cal = Calendar.getInstance(); DateFormat dateFormat1 = new SimpleDateFormat("dd.MM.yyyy"); Calendar cal1 = Calendar.getInstance(); for (int k = 0; k < idArray.length ; k ++ ) { query = "INSERT INTO Companies (ID,Company,Company2,Company3,Country,ZIP,City,Street," + "Phone,Fax,Email,Internet,CustomerNo,AccountMngr,Icon,AddressSource," + "UserDefined1,UserDefined2,CreatedOn,CreatedBy,ColectedInformation) " + " VALUES ('" + UUID.randomUUID() + "','" + company_nameArray[k] + "','" + company_name2Array[k] + "','" + company_name3Array[k] + "','DE','" + zipArray[k] + "','" + cityArray[k] + "','" + streetArray[k] + "','" + phone_Array[k] + "','" + faxArray[k] + "','" + emailArray[k] + "','" + internetArray[k] + "','" + customer_noArray[k] + "','d','131','60','Baufinder Import','Import Datum " + dateFormat1.format(cal1.getTime()) + "','" + dateFormat1.format(cal1.getTime()) + "','d','" + "cxcxvcx" + "')"; ResultSet = Statement.executeQuery(query); } connection.close(); } catch(NullPointerException e) { System.out.println("NullPointerException"); } catch ( SQLException err ) { System.out.println( "SQL Exception:" ) ; // Loop through the SQL Exceptions while( err != null ) { System.out.println( "State : " + err.getSQLState() ) ; System.out.println( "Message: " + err.getMessage() ) ; System.out.println( "Error : " + err.getErrorCode() ) ; err = err.getNextException() ; } } catch( Exception e ) { System.out.println( "There is a problem with " + e ) ; } }

我收到此错误：

SQL异常：State：null消息：语句没有返回结果集。 错误：0

我不明白结果测试的问题。 我一直在使用它，因为我尝试从另一个数据库读取数据，现在我尝试将它们插入到另一个数据库中。 此外，所有这些表都不包含空值。

先谢谢你：）

I am having a small problem while I try to insert some data in my database (MS - SQL). I am using this code (I just copy-paste all of it, so someone might understand smth that I don't)

try { Connection connection = null; Statement Statement = null; ResultSet ResultSet = null; String query = ""; // jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]] String host = "jdbc:sqlserver://server;databaseName=db"; String username = "user"; String password = "pass"; Statement = connection.createStatement(); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Calendar cal = Calendar.getInstance(); DateFormat dateFormat1 = new SimpleDateFormat("dd.MM.yyyy"); Calendar cal1 = Calendar.getInstance(); for (int k = 0; k < idArray.length ; k ++ ) { query = "INSERT INTO Companies (ID,Company,Company2,Company3,Country,ZIP,City,Street," + "Phone,Fax,Email,Internet,CustomerNo,AccountMngr,Icon,AddressSource," + "UserDefined1,UserDefined2,CreatedOn,CreatedBy,ColectedInformation) " + " VALUES ('" + UUID.randomUUID() + "','" + company_nameArray[k] + "','" + company_name2Array[k] + "','" + company_name3Array[k] + "','DE','" + zipArray[k] + "','" + cityArray[k] + "','" + streetArray[k] + "','" + phone_Array[k] + "','" + faxArray[k] + "','" + emailArray[k] + "','" + internetArray[k] + "','" + customer_noArray[k] + "','d','131','60','Baufinder Import','Import Datum " + dateFormat1.format(cal1.getTime()) + "','" + dateFormat1.format(cal1.getTime()) + "','d','" + "cxcxvcx" + "')"; ResultSet = Statement.executeQuery(query); } connection.close(); } catch(NullPointerException e) { System.out.println("NullPointerException"); } catch ( SQLException err ) { System.out.println( "SQL Exception:" ) ; // Loop through the SQL Exceptions while( err != null ) { System.out.println( "State : " + err.getSQLState() ) ; System.out.println( "Message: " + err.getMessage() ) ; System.out.println( "Error : " + err.getErrorCode() ) ; err = err.getNextException() ; } } catch( Exception e ) { System.out.println( "There is a problem with " + e ) ; } }

and I am getting this error:

SQL Exception: State : null Message: There was no result set returned by the statement. Error : 0

I don't understand the problem with the result test. I have been using it a lot, since I try to read the data from another database and now I try to insert them to another. Furthermore, all of these tables do not include null values.

Thank you in advance:)

最满意答案

使用Statement＃executeUpdate（String）代替Statement＃execute（query）;

for (int k = 0; k < idArray.length ; k ++ ) { query = "INSERT INTO Companies (ID,Company,Company2,Company3,Country,ZIP,City,Street," + "Phone,Fax,Email,Internet,CustomerNo,AccountMngr,Icon,AddressSource," + "UserDefined1,UserDefined2,CreatedOn,CreatedBy,ColectedInformation) " + " VALUES ('" + UUID.randomUUID() + "','" + company_nameArray[k] + "','" + company_name2Array[k] + "','" + company_name3Array[k] + "','DE','" + zipArray[k] + "','" + cityArray[k] + "','" + streetArray[k] + "','" + phone_Array[k] + "','" + faxArray[k] + "','" + emailArray[k] + "','" + internetArray[k] + "','" + customer_noArray[k] + "','d','131','60','Baufinder Import','Import Datum " + dateFormat1.format(cal1.getTime()) + "','" + dateFormat1.format(cal1.getTime()) + "','d','" + "cxcxvcx" + "')"; int updatedRowCount = Statement.executeUpdate(query); }

笔记：

如果用户直接输入某些数据，请考虑使用PreparedStatement来防止SQL注入的可能性。 如果需要提高插入性能，请尝试使用批量插入。

Use Statement#executeUpdate(String) instead of Statement#execute(query);

for (int k = 0; k < idArray.length ; k ++ ) { query = "INSERT INTO Companies (ID,Company,Company2,Company3,Country,ZIP,City,Street," + "Phone,Fax,Email,Internet,CustomerNo,AccountMngr,Icon,AddressSource," + "UserDefined1,UserDefined2,CreatedOn,CreatedBy,ColectedInformation) " + " VALUES ('" + UUID.randomUUID() + "','" + company_nameArray[k] + "','" + company_name2Array[k] + "','" + company_name3Array[k] + "','DE','" + zipArray[k] + "','" + cityArray[k] + "','" + streetArray[k] + "','" + phone_Array[k] + "','" + faxArray[k] + "','" + emailArray[k] + "','" + internetArray[k] + "','" + customer_noArray[k] + "','d','131','60','Baufinder Import','Import Datum " + dateFormat1.format(cal1.getTime()) + "','" + dateFormat1.format(cal1.getTime()) + "','d','" + "cxcxvcx" + "')"; int updatedRowCount = Statement.executeUpdate(query); }

Notes:

Please consider using PreparedStatement to prevent possibility of SQL Injection if some data directly input by User. Try to use batch insert if you need to improve performance of inserting.