Java JDBC – PreparedStatement executeUpdate() always returns 1

Currently I’m working on a code in java that retrieves data from XML files located in various folders and then uploads the file itself and the data retrieved to a SQL-server Database. I don’t want to upload any repeated XML file to database but since the files can have random names I’m checking using the Hash from each file I’m about to upload, I’m uploading the files to the following table:

XMLFiles

CREATE TABLE [dbo].[XMLFiles](
    [PathID] [int] NOT NULL,
    [FileID] [int] IDENTITY(1,1) NOT NULL,
    [XMLFileName] [nvarchar](100) NULL,
    [FileSize] [int] NULL,
    [FileData] [varbinary](max) NULL,
    [ModDate] [datetime2](7) NULL,
    [FileHash] [nvarchar](100) NULL,
 CONSTRAINT [PK_XMLFiles] PRIMARY KEY CLUSTERED 
(
    [FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

the code I’m using to upload the files is the following:

public int UploadFile

public int UploadFile(String Path,int pathID) throws SQLException, SAXException, IOException {
        
                 int ID=-1;
                 String hash;
                 int len,rowCount=0;
                 String query;
                 PreparedStatement pstmt;
                    try {
                    File file = new File(Path);
                    hash=XMLRead.getFileChecksum(file);

                    FileInputStream fis = new FileInputStream(file);
                    len = (int) file.length();
                    query = (" IF NOT EXISTS "
                            + " (SELECT 1"
                            + " FROM XMLFiles"
                            + " WHERE FileSize="+len+" AND FileHash='"+hash+"')"
                            + " BEGIN"
                            + " INSERT INTO XMLFiles (PathID,XMLFileName,FileSize,FileData,ModDate,FileHash) "
                            + " VALUES(?,?,?,?,GETDATE(),?)"
                            + " END;");
                    pstmt = Con.prepareStatement(query);
                    pstmt.setInt(1, pathID);
                    pstmt.setString(2, file.getName());
                    pstmt.setInt(3, len);
                    pstmt.setBinaryStream(4, fis, len);
                    pstmt.setString(5, hash);
                    rowCount=pstmt.executeUpdate();
                    System.out.println("ROWS AFFECTED:-"+rowCount);

                    if (rowCount==0){
                            System.out.println("THE FILE: "+file.getName()+"ALREADY EXISTS IN THE SERVER WITH THE NAME: ");
                            System.out.println(GetFilename(hash));
                    }

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

I’m executing the program with 28 files in which 4 of them are repeated files but with different names, I know the code is working fine because at the end of each execution only the 24 unique files are uploaded, the problem is that I’m using the rowCount to check if the file was uploaded or not, and if the file wasn’t uploaded because it was a repeated file I’m not uploading the data of that file to the database neither, like so (the following code is a fragment to illustrate the comprobation I’m doing):

int rowCount=UploadFile(Path,pathID);                    
  if (rowCount==1){
       //UPLOAD DATA
  }

the problem is that the executeUpdate() in the method UploadFile always returns 1 even when no rows in the database where affected, Is there something I’m missing here?, I can’t find anything wrong with my code, is it the IF NOT EXISTS comprobation that I’m doing the one that returns 1?

Answer

The update count returned by a SQL statement is only well-defined for a normal DML statement (INSERT, UPDATE, or DELETE).

It is not defined for a SQL script.

The value is whatever the server chooses to return for a script. For MS SQL Server, it is likely the value of @@ROWCOUNT at the end of the statement / script:

Set @@ROWCOUNT to the number of rows affected or read.

Since you’re executing a SELECT statement, it sets the @@ROWCOUNT value. If zero, you then execute the INSERT statement, which will override the @@ROWCOUNT value.

Assuming there will never be more than one row with that size/hash, you will always get a count of 1 back.

Advertisement

Answer