javasql-serverjdbcnodesbatch-insert

ExecuteBatch() is not updating


So i'm trying to insert multiple rows in a single update. After doing some googling the rewriteBatch was the one that most people advised.

Basicly when i try to execute a batch it doesn't update my db. The db is localhost, i can read from the db using the same URL and there aren't any Exceptions (Exception or SQLException).

public void insert(Node[] nodes) {  
    try { 
        conn = (Connection) DriverManager.getConnection(url);
        System.out.println(conn.getMetaData().supportsBatchUpdates());
        conn.setAutoCommit(false);
        for (int i = 0; i < nodes.length; i++) {
            if(nodes[i].next!=null){
       pstmt=conn.prepareStatement(StatementUtils.createInsertStatement(i));
                addToBatch(nodes[i].next);
                int [] res=pstmt.executeBatch();
                System.out.print("has "+res.length+" elements. Status:[");
                for (int j = 0; j < res.length; j++) {
                    System.out.print(res[j]+" ");
                }
                System.out.println("]");
                conn.commit();
                pstmt.close();
            }
        }
    }
    catch(Exception e){
        try {
            conn.rollback();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }
    finally { 
        if (conn != null) {
        try { conn.close(); }
        catch (SQLException e) {
            printSQLException(e); 
            } 
        }
    }
}

private void addToBatch(Node n) throws SQLException{
    if(n!=null){
        pstmt.setString(1, n.can.getCar());
        pstmt.setInt(2, n.can.getID());
        pstmt.setTimestamp(3, n.can.getDate());
        pstmt.setInt(4, n.can.getLength());
        for (int i = 1; i <= n.can.getLength(); i++) {
            pstmt.setInt(i+4, n.can.getData(i-1));
        }
        pstmt.addBatch();
        addToBatch(n.next);
    }
}

public static String createInsertStatement(int length){
        String s="INSERT into CanData (carName,systemID,dataDate,size";
        for (int i = 0; i < length; i++) 
                s+=",d"+i;
        s+= ")values(?,?,?,?";
        for (int i = 0; i <length; i++)
                s+=",?";
        s+=")";
        return s;
    }

Even though there is a for on insert(), for tests purposes for now the array of nodes only has 1 position.

Also this is my output:

true
has 18 elements. Status:[1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 ]
true
has 14 elements. Status:[1 1 1 1 1 1 1 1 1 1 1 1 1 1 ]
true
has 15 elements. Status:[1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 ]
true
has 18 elements. Status:[1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 ]
true
has 14 elements. Status:[1 1 1 1 1 1 1 1 1 1 1 1 1 1 ]
true
has 11 elements. Status:[1 1 1 1 1 1 1 1 1 1 1 ]

Why does it not update/insert to my db?

Btw if you read until here thank you for your patience xD


Solution

  • First of all thank you to everyone who tried to help me and invested the time to search and trying to answer my question.

    The problem was my URL connection. It was:

    url="jdbc:sqlserver://localhost;rewriteBatchUpdates=true;instanceName=MSSQLSERVER;integratedSecurity=true;";

    but there was missing tha database name and the port number. It seems when you are trying to insert multiple rows at the same time you need to define your database and port number, like so:

    url = "jdbc:sqlserver://localhost:1433;database=IFS_DB;rewriteBatchUpdates=true;instanceName=MSSQLSERVER;integratedSecurity=true;";
    

    Also i am sorry for not posting the URL. If i did maybe this could have been fixed along time ago.