当我尝试在我的数据库中插入一些数据(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.更多推荐
发布评论