Sometimes this problem comes due to size of system RAM.May be you are inserting the data using buffer through RAM. To get out of this problem.
- Set the Auto commit disable before inserting the data.
- insert some amount of data appropriate to your System RAM (not the
whole).
- Commit the query.
- Do the steps 2 and 3 again until the whole insertion will not be
done.
You can understand this by the following code.
public static void main(string args[])
{
Connection con = null;
Statement stm = null;
int i;
float ratio;
ratio=1.0f;
try
{
Class.forName("com.mysql.jdbc.Driver");
// Connecting to the database
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/demo",
"ashish", "impetus");
File f = new File("filler"); // taking the random text data from the file
// filler.txt and inserting that string
// in filler field of the relations
RandomAccessFile r = new RandomAccessFile(f,"r");
Random randomGenerator = new Random();
String strLine=new String();
int r1,flag=0;
stm = con.createStatement();
con.setAutoCommit(false) ;
int k=0;
i=0;
long sum2=0;
while(k%50000==0)
{
final long start = currentTimeMillis();
final StringBuilder builder =
new StringBuilder("INSERT INTO accounts
(aid, bid,abalance,filler) VALUES ");
while(i!=naccounts*ratio*scale )
{
int j=i+1;
for(int l=0;l<40;l++)
{
strLine+=((char)r.read());
r.skipBytes(0);
}
r1=randomGenerator.nextInt(1500);
if(strLine.equals(""))
{
flag=1;
}
if(flag!=1)
{
strLine=strLine.replaceAll("\\s","");
strLine=strLine.replaceAll("\\t","");
}
flag=0;
if (i%50000!=0)
{
builder.append(",");
}
builder.append(format("(%s, %s, %s, '%s')", j,
i/naccounts+1, 0, strLine));
strLine="";
r.seek(r1);
i++;
if(i%50000==0||i>=naccounts*ratio*scale)
{
final String query = builder.toString();
final PreparedStatement statement1 = con.prepareStatement(query);
statement1.execute();
con.commit();
final long stop= currentTimeMillis();
sum2=sum2+(stop-start);
statement1.close();
}
if(i%50000==0||i>=naccounts*ratio*scale)
{
break;
}
}
k=k+50000;
if(k>naccounts*ratio*scale)
{
break;
}
}
System.out.println(i+" rows inserted accounts table ");
System.out.println("time taken = "+sum2+" milliseconds");
}
catch (SQLException e)
{
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
catch (Exception e)
{
e.printStackTrace();
}
}
