Java servlet cycle to insert 1 million oracle data problem, please help solve, in a hurry-CodePudding

Demand is this: the existing 1 million data needs to be inserted into the oracle database, data format for the XML format, the need to parse and extract the key data and written to the database, parsing, written to the database are no problem, but in the time of more than 2000 libraries fail, can't write the database, the code is as follows:

 
Public class DBhelp {
The static String driver;//oracle driver 
The static String url="JDBC: oracle: thin: @ 192.168.5.16:1521: noCard"; 
The static String user="card22";//user is the database user name 
The static String password="card11"; 

Public static Connection getconn () {//in order to facilitate the interpretation of the following, set up a special used for database Connection here a method 
The Connection conn=null; 
Try {
//first establish drive 
Class.forname ("... The oracle JDBC driver OracleDriver "); 
//after the success of the drive to connect 
Conn=DriverManager. GetConnection (url, user, password); 
} the catch (SQLException e) {
e.printStackTrace(); 
} the catch (Exception e) {
e.printStackTrace(); 
} 
Return the conn.//return a connection 
} 
Public static int the insert (String SQL) {
The Connection conn=null; 
The Statement st=null; 
Conn=getconn (); 
//get connection 
Int result; 
Try {
St=conn. CreateStatement (); 
//create the SQL insert 
//return a result, do this either success or failure, if the returned result & gt; 0 is success, and vice failure 
Result=st. executeUpdate (SQL); 
} the catch (SQLException e) {
//TODO Auto - generated the catch block, 
Result=0; 
e.printStackTrace(); 
} 
return result; 
} 
}

SQL statements:

 
Try {
String SQL="insert into JingDong (TRX_CODE) values ('" + TRX_CODE + "', ');" 
Logger. The info (" SQL: "+ SQL); 
Int aa=DBhelp. Insert (SQL); 
} the catch (JDOMException e) {
Logger. The info (" explain the error message, the message format is not correct "); 
} the catch (Exception e) {
Logger. The info (" error: "+ um participant etMessage ()); 
}

Um participant etMessage () - output is null

CodePudding user response:

The code posted all? Didn't see you close connection,

CodePudding user response:

reference 1st floor dkwuxiang response:

code stick full? Didn't see you close connection,

Are you saying that I didn't close connection

CodePudding user response:

reference 1st floor dkwuxiang response:

code stick full? Didn't see you close connection,

Like the millions of level of operation, whether to use the connection pool

CodePudding user response:

Etl kettle is a lot easier than writing code

CodePudding user response:

The error from the abnormal log, you should is a NullPointerException didn't play all because:

 
The catch (Exception e) {
Logger. The info (" error: "+ um participant etMessage ()); 
}

Should be written in this way can the exception stack full print:

 
The catch (Exception e) {
Logger. The info (" error: ", e); 
}

Or it

 
The catch (Exception e) {
e.printStackTrace(); 
}

CodePudding user response:

reference 1st floor dkwuxiang response:

code stick full? Didn't see you close connection,

 
Public static int the insert (String SQL) throws SQLException {
The Connection conn=null; 
The Statement st=null; 
Conn=getconn (); 
//get connection 
Int result; 
Try {
St=conn. CreateStatement (); 
//create the SQL insert 
//return a result, do this either success or failure, if the returned result & gt; 0 is success, and vice failure 
Result=st. executeUpdate (SQL); 
} the catch (SQLException e) {
//TODO Auto - generated the catch block, 
Result=0; 
e.printStackTrace(); 
} the finally 
{
if(null!=conn) 
{
conn.close(); 
Conn=null; 
} 
if(null!=st) 
{
St. close (); 
St=null; 
} 
} 
return result; 
}

Under such changed and close the connection, not at the above mistake, really is not close the connection, continued to write 300000 database data, the results are 10047 did not write, what the reason

CodePudding user response:

refer to 6th floor zhangfengyi response:

Quote: refer to 1st floor dkwuxiang response:

The code posted all? Didn't see you close connection,

Public static int the insert (String SQL) throws SQLException {
The Connection conn=null;
The Statement st=null;
Conn=getconn ();
//get connection
Int result;
Try {
St=conn. CreateStatement ();
//create the SQL insert
//return a result, do this either success or failure, if the returned result & gt; 0 is success, and vice failure
Result=st. executeUpdate (SQL);
} the catch (SQLException e) {
//TODO Auto - generated the catch block,
Result=0;
e.printStackTrace();
} the finally
{
if(null!=conn)
{
conn.close();
Conn=null;
}
if(null!=st)
{
St. close ();
St=null;
}
}
return result;
}

Under such changed and close the connection, not at the above mistake, really is not close the connection, continued to write 300000 database data, the result is 10047 didn't write, this the reason why

Insert the data code is what? Which part of the data didn't insert? Information is too little, bad judgment

CodePudding user response:

Before using the above methods to insert data, 24 milliseconds, a pen is now 100 milliseconds, insert the 1 million data, whether or not to use the connection pool

CodePudding user response:

refer to the eighth floor zhangfengyi response:

using the above methods to insert data, a 24 milliseconds before, now 100 milliseconds, insert the 1 million data, whether or not to use the connection pool

In what connection pool... You are a main thread, connection without closed, with a connection directly.

If you want to improve performance, multithreading + bulk insert

CodePudding user response:

Article 1 million the data,,,

Change a train of thought, to consider, can use PLSQL,

CodePudding user response:

Public Boolean insertDetailed (List

Page link：https//www.codepudding.com/Backend/35015.html

Prev:Mybatils puls for help, and the database field binding how to solve this problem

Next:Vue springboot back-end data transfer error Current request is not a multipart request