CODEWITHSUNDEEP
javamysql
sonal
sonal

Reputation: 11

What to insert data into 2 tables in a single hit

pstmt = conn.prepareStatement(queryBuilder.toString());
pstmt.setString(count++, commonDTO.getGroupName());
pstmt.setString(count++, commonDTO.getSubGrpNameHindi());
pstmt.setString(count++, commonDTO.getGroupCode());
pstmt.setInt(count++, commonDTO.getIsActive());
pstmt.executeUpdate();

StringBuilder queryBuilder = new StringBuilder();
queryBuilder = queryBuilder.append(SLCMQueryConstant.Get_SubGrp_Id);
pstmt.setString (1, commonDTO.getGroupName());
rs = pstmt.executeQuery();
int x = 0;
while(rs.next())
{
    x= rs.getInt("subject_Group_ID");
}

queryBuilder= queryBuilder.append(SLCMQueryConstant.MapSubject_to_SubGrp);
pstmt.setInt(count++, x);
pstmt.setInt(count++, commonDTO.getSubGrpID());
pstmt.setInt(count++, commonDTO.getSubjectMaxMark());
pstmt.setInt(count++, commonDTO.getSubjectMinMark());
pstmt.executeUpdate();
conn.commit();

I am trying to insert a new row into first table.then I want id of newly added row and insert into 2nd table but there is an exception.

Upvotes: 0

Views: 66

Answers (2)

Markus Mitterauer
Markus Mitterauer

Reputation: 1610

You have to create prepared statements from your built queries:

pstmt = conn.prepareStatement(queryBuilder.toString());
pstmt.setString(count++, commonDTO.getGroupName());
pstmt.setString(count++, commonDTO.getSubGrpNameHindi());
pstmt.setString(count++, commonDTO.getGroupCode());
pstmt.setInt(count++, commonDTO.getIsActive());
pstmt.executeUpdate();

StringBuilder queryBuilder = new StringBuilder();
queryBuilder = queryBuilder.append(SLCMQueryConstant.Get_SubGrp_Id);
pstmt = conn.prepareStatement(queryBuilder.toString());  // <<<<<<-----
pstmt.setString(1, commonDTO.getGroupName());
Recordset rs = pstmt.executeQuery();
int x = 0;
while(rs.next())
{
    x = rs.getInt("subject_Group_ID");
}

And you have to reset your prepared statement parameter counter:

queryBuilder= queryBuilder.append(SLCMQueryConstant.MapSubject_to_SubGrp);
pstmt = conn.prepareStatement(queryBuilder.toString());  // <<<<<<-----
count = 1; //<<<<<-----
pstmt.setInt(count++, x);
pstmt.setInt(count++, commonDTO.getSubGrpID());
pstmt.setInt(count++, commonDTO.getSubjectMaxMark());
pstmt.setInt(count++, commonDTO.getSubjectMinMark());
pstmt.executeUpdate();
conn.commit();

Upvotes: 0

Henning Koehler
Henning Koehler

Reputation: 2637

The error message tells you exactly what's wrong - executeQuery is for executing SELECT queries, not INSERTS/UPDATES. Use executeUpdate instead, as e.g. described here.

Seeing you have added your query above - to execute multiple queries, run multiple executeQuery / executeUpdate statements. As long as you don't have auto-commit set and commit the third query, all three queries will be executed as a single commit (i.e. cannot be interrupted).

It is not possible to run multiple queries with a single executeQuery / executeUpdate, separated by semicolon. This syntax is simply what many clients use to separate queries textually, but the clients will send these queries separately with multiple requests as well.

I'd guess that running three separate queries in a single transaction will be exactly what you need. However, if for some reason you absolutely want to do it with a single execute, you can use a stored procedure, which you prepare ahead of time.

Upvotes: 1

Related Questions