2

`

import java.sql.*; public class Match { public static void main(String args[]) throws Exception{ DBConnection1 con = new DBConnection1(); DBConnection con1 = new DBConnection(); Connection conn = null,conn1=null; conn = con.getConnection(); conn1 = con1.getConnection(); Statement st = null; PreparedStatement pst = null; ResultSet rs = null,rs1 = null; st = conn.createStatement(); String query1 = "SELECT Name FROM Employee WHERE Name=?"; pst = conn1.prepareStatement(query1); st.setFetchSize(Integer.MIN_VALUE); String query = "SELECT name FROM emp"; rs = st.executeQuery(query); String name = ""; int count = 0; while(rs.next()){ title = rs.getString("name"); pst.setString(1, title); rs1 = pst.executeQuery(); while(rs1.next()){ count++; if(count % 100 == 0) System.out.println(count); } } System.out.println(count); } }

`

I am selecting value from the very large database based on some value from other database . I am running my select query in a while loop. After running my java code after getting many result , i am getting 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure exception.

I have no idea why this is happening. If you guys have any idea please help

I already read the old questions based on this exception but none of them helps.

Improve this question
10
  • There's probably a limit to the number of consecutive queries you can perform on the server. Can you divide the task to that you don't reach this hypothetical limit? Commented Apr 29, 2014 at 11:18
  • It might be a good idea to count the number of queries you're performing. Another possibility is that there is a timeout period. If the query takes too long, you might be reaching it. Commented Apr 29, 2014 at 11:20
  • I am counting the number of times query is running. It fails after 2.8 million results Commented Apr 29, 2014 at 11:22
  • 1
    Does the query run many times or does it run for a long time? Commented Apr 29, 2014 at 11:23
  • 1
    Check the MySQL logs .... Commented Apr 29, 2014 at 11:42

2 Answers 2

Reset to default
2 
import java.sql.*; public class Match { public static void main(String args[]) throws Exception{ DBConnection1 con = new DBConnection1(); DBConnection con1 = new DBConnection(); Connection conn = null,conn1=null; conn = con.getConnection(); conn1 = con1.getConnection(); Statement st = null; PreparedStatement pst = null; ResultSet rs = null,rs1 = null; st = conn.createStatement(); st.setFetchSize(Integer.MIN_VALUE); String query = "SELECT name FROM emp"; rs = st.executeQuery(query); String title = "",query1=""; StringBuffer newQuery = new StringBuffer("SELECT Name FROM Employee WHERE "); int count = 0; long nameCount = 0L; while(rs.next()){ nameCount++; title = rs.getString("name"); query1 = "Name=? or"; pst = conn1.prepareStatement(query1); pst.setString(1, title); newQuery.append(pst.toString().substring(pst.toString().indexOf('N'), pst.toString().length())+" "); } if ( nameCount > 0 ){ String Query = newQuery.toString().substring(0,newQuery.toString().length() - 3); rs1 = conn1.createStatement().executeQuery(Query); while(rs1.next()){ count++; if(count % 50 == 0) System.out.println(count); } } } }

Using PreparedStatement solves the problem of SQL Injection attack. Now the code is working.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You can also use threading, if synchronization is not required
1

I think this code is optimized, but it may have some syntax error:

import java.sql.*; public class Match { public static void main(String args[]) throws Exception{ DBConnection1 con = new DBConnection1(); DBConnection con1 = new DBConnection(); Connection conn = null,conn1=null; conn = con.getConnection(); conn1 = con1.getConnection(); Statement st = null; ResultSet rs = null,rs1 = null; st = conn.createStatement(); //String query1 = "SELECT Name FROM Employee WHERE Name=?"; st.setFetchSize(Integer.MIN_VALUE); String query = "SELECT name FROM emp"; rs = st.executeQuery(query); String name = ""; StringBuffer newQuery = new StringBuffer("SELECT Name FROM Employee WHERE"); int count = 0; long nameCount = 0L; while(rs.next()){ nameCount++; title = rs.getString("name"); newQuery.append(" Name='" + title + "' or"); } if ( nameCount > 0 ){ newQuery = newQuery.subString( newQuery.length() - 3); rs1 = conn1.createStatement.executeQuery( newQuery ); while(rs1.next()){ count++; if(count % 100 == 0) System.out.println(count); } } } }

Link failure may be because of so many query execution. Hence I have made it to fire only one or two queries, and you will get all your results.

Improve this answer

7 Comments

The query will either contain no condition to be met (if there are 0 names) or will always have incorrect syntax (trailing or). If these errors were corrected, it could result in max_allowed_packet being reached.
That problem could be addressed, however, by fine tuning the maximum query length and the maximum number of queries to be executed to a good balance.
Problem of trailing or is already handled in the code, and max_allowed_packet size can also be increased, in order to avoid the error and improve the performance
There is a syntax error in query because of that 'or'
You can try newQuery = newQuery.subString( 0, newQuery.length() - 3); instead of newQuery = newQuery.subString( newQuery.length() - 3);
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.