Avoiding JDBC call

The scenario is like this:

for loop // runs say 200000 times
{
    // here, i do a select from a database, fetching few rows which are expected to increase with every new iteration of for loop
    // currently i am doing this select using simple JDBC call (using JDBC only is NOT a requirement)

    // then i do some string matching stuff and then i either insert or update a particular row (in 95% cases i will insert)
    // this insert or update is being done using Hibernate (using Hibernate over here is a requirement)
}

So the problem is, in every loop, I have to consider the each and every previously inserted/updated row. Due to this requirement, I have to do a JDBC call in each and every loop. And this JDBC call is taking the maximum time, bringing down the performance.

I want to know, is there any method using which I do not have to make a JDBC call in each iteration, but still I will be able to consider all the records including the one in the just previous insert/update? Anything like caching or some in-memory data structure or something like that?

Here is the code:

for loop // runs say 2000 times
{
    String query = pdi.selectAllPatients(patientInfo);
    Statement st = conn.createStatement();
    ResultSet patientRs = st.executeQuery(query);

    while (patientRs.hasNext())
    {
        // some string ops
    }

    // Create session for DB No.2
    Session sessionEmpi = sessionFactoryEmpi.getCurrentSession();
    sessionEmpi.beginTransaction();

    if(some condition)
        patientDao.insertPatient(patientInfo, sessionEmpi);
    else
        patientDao.insertref(patientInfo.getref(), sessionEmpi);

    co开发者_如何学Cnn.commit();
}

public int insertPatient(PatientInfo input, Session session) throws SQLException {

    try {

        session.save(input.getPatient());
        session.flush();
        session.save(input.getref());
        session.getTransaction().commit();

        return 1;

    } catch (Exception ex) {
        session.getTransaction().rollback();
        ex.printStackTrace();
        return 0;
    }
}

---

Is the performance of the SELECT consistent? Unless your data is fairly small, you'll likely have trouble caching all your changes in memory. You may also be able to batch the SELECTs, effectively unrolling the loop.

---

You can use the PreparedStatement interface instead of Statement interface as it avoids the unnecessary calls for firing the query to the database you just have to bind the data in for loop this will help you to improve performance!!

example:

PreparedStatement s =con.prepareStatement("select * from student_master where stu_id = ?");

for()
{
   s.setString(1,"s002");
   ResultSet rs = s.executeQuery();
}