Insert Multiple data into Oracle db using Java

Question

Following is my code which retrieves Rally data.

public static void main(String[] args) throws URISyntaxException, IOException {

String host = "examplehost";
String username = "exampleuser";
String password = "password";
String projectRef   = "project";
String workspaceRef = "workspace"; 
String applicationName = "";
int queryLimit = 4000;
Connection conn        = null;
String propertiesFile  = "";
String projectValue = "";
String columnValue = "";
String returnValue = "";

    RallyRestApi restApi = new RallyRestApi(
            new URI(host),
            username,
            password);
    restApi.setApplicationName(applicationName); 
    System.out.println(restApi.getWsapiVersion()); 



    try{
         QueryRequest projectRequest = new QueryRequest("Project");
         projectRequest.setFetch(new Fetch("Name", "TeamMembers"));
         projectRequest.setWorkspace(workspaceRef);
             projectRequest.setProject(projectRef);
             projectRequest.setScopedDown(true);
         projectRequest.setQueryFilter(new QueryFilter("Name", "contains", "DT-"));  
         projectRequest.setLimit(queryLimit);
         QueryResponse projectQueryResponse = restApi.query(projectRequest);
         int count = projectQueryResponse.getResults().size();
         System.out.println(count);

         if(count > 0){
             for (int i=0;i<count;i++){
                 JsonObject projectObject = projectQueryResponse.getResults().get(i).getAsJsonObject();
                 JsonObject obj = projectObject.getAsJsonObject();                   
                 projectValue = JsonUtil.getJsonValue(obj, "_refObjectName");

                 System.out.println("Project: " + projectValue);
                 int numberOfTeamMembers = projectObject.getAsJsonObject("TeamMembers").get("Count").getAsInt();
                 if(numberOfTeamMembers > 0) {
                        QueryRequest teamRequest = new QueryRequest(projectObject.getAsJsonObject("TeamMembers"));
                        JsonArray teammates = restApi.query(teamRequest).getResults();
                        //JsonObject teammates = restApi.query(teamRequest).getResults();
                            if (teammates instanceof JsonArray) {
                                    JsonArray jsonarr = teammates.getAsJsonArray();
                                    //returnValue += squote;
                                    for (int j=0; j<jsonarr.size(); j++) {
                                        if (j>0) 
                                            returnValue += "\n";
                                        JsonObject tobj = jsonarr.get(j).getAsJsonObject();
                                        if (obj.has("Name"))
                                            columnValue = getJsonValue(tobj, "Name");
                                        if (obj.has("_refObjectName"))
                                            columnValue = JsonUtil.getJsonValue(tobj, "_refObjectName");
                                        returnValue += columnValue;
                                      System.out.println(columnValue);   
                                    }
                                    //returnValue += squote;
                                } 
                //columnValue = JsonUtil.getJsonString(teammates);      
                        //for (int j=0;j<numberOfTeamMembers;j++){
                            //JsonObject teamObject = teamQueryResponse.getResults().get(j).getAsJsonObject();


                            //System.out.println(teammates.get(j).getAsJsonObject().get("_refObjectName").getAsString());
                        //}
                    }
             }
         }

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

    } finally{
        restApi.close();
    }
}

I'm trying to insert Project ID and the associated team members into db. For one Project ID there may be 1- 10 members, The count may differ for each project. In DB, there would be only two Column holding Project ID and User name. Could anyone help me with this request?

Hope it helps.

If you need more details, please do reply

Thanks

Sreer

I'm new to Java :). So trying to update the existing code, so it can insert the values given by this code, into db. I even added the jdbc connection, not sure how to add the code to insert the values into the existing table. — user2335123, Apr 04 '14 at 12:05
`Statement st = (Statement) conn.createStatement(); st.executeUpdate("INSERT INTO CUST_RALLY_TEAM_MEMBER " + "VALUES (projectValue,columnValue)");` doesn't work to insert the String Variables. Any idea? — user2335123, Apr 04 '14 at 13:03
What does "doesn't work" mean? Are you getting an error, or just no data in the table? Please be specific. — OldProgrammer, Apr 04 '14 at 13:57
It threw run time errorjava.sql.SQLException: ORA-00984: column not allowed here and at the Insert statment there was hint showing ASSIGN RETURN VALUE TO NEW VARIABLE. — user2335123, Apr 04 '14 at 14:13

score 1 · Accepted Answer · answered Apr 04 '14 at 14:28

From your comment above, the actual line that is failing is:

st.executeUpdate("INSERT INTO CUST_RALLY_TEAM_MEMBER " + "VALUES projectValue,columnValue)");

Here, you are literally passing this string to Oracle:

INSERT INTO CUST_RALLY_TEAM_MEMBER VALUES projectValue,columnValue)

You need to pass the values of those variables (you're also missing an open parenthesis:

st.executeUpdate("INSERT INTO CUST_RALLY_TEAM_MEMBER VALUES ('" + projectValue + "','" + columnValue + "')");

This assumes that the cust_rally_team_member contains only these two columns, if not (and as a best practice, even if it does), you should be specifying the columns to be inserted into. Assuming the column names are also "projectvalue" and "columnvalue", then the statement would be:

st.executeUpdate("INSERT INTO CUST_RALLY_TEAM_MEMBER (projectvalue,columnvalue) VALUES ('" + projectValue + "','" + columnValue + "')");

Note, too, that prepared statements are a good approach for a process such as this. It eliminates the risk of SQL errors if the values contain a single quote, reduces the risk of SQL Injection attacks, and improves performance by compiling the statement only once.

Thanks Joe, it worked fine. But after inserting 999 records it threw error **java.sql.SQLException: ORA-01000: maximum open cursors exceeded**. — user2335123, Apr 04 '14 at 16:39
You're probably not closing your statements in the loop. See here for a ton of info: http://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded — Joe, Apr 04 '14 at 19:59

Insert Multiple data into Oracle db using Java

1 Answers1