When should we use a PreparedStatement instead of a Statement?

1. Query is rewritten and compiled by the database server

   If you don't use a prepared statement, the database server will have to parse, and compute an execution plan for the statement each time you run it. If you find that you'll run the same statement multiple times (with different parameters) then its worth preparing the statement once and reusing that prepared statement. If you are querying the database adhoc then there is probably little benefit to this.

2. Protected against SQL injection

   This is an advantage you almost always want hence a good reason to use a PreparedStatement everytime. Its a consequence of having to parameterize the query but it does make running it a lot safer. The only time I can think of that this would not be useful is if you were allowing adhoc database queries; You might simply use the Statement object if you were prototyping the application and its quicker for you, or if the query contains no parameters.

Ask Tom's opinion:

The use of a Statement in JDBC should be 100% localized to being used for DDL (ALTER, CREATE, GRANT, etc) as these are the only statement types that cannot accept BIND VARIABLES.

PreparedStatements or CallableStatements should be used for EVERY OTHER type of statement (DML, Queries). As these are the statement types that accept bind variables.

This is a fact, a rule, a law -- use prepared statements EVERYWHERE. Use STATEMENTS almost no where.

He's specifically talking about Oracle but the same principle applies to any database that caches execution plans.

Database apps that scale and prevent SQL injection attacks at the same time? What's the downside?

I would turn this round: in a publicly distributed app, you should generally always use prepared statements unless you have a really compelling reason not to, and you should always supply parameters "properly" to the prepared statement, and not by splicing them into the query string.

Why? Well, basically because of the reasons you gave (or at least, the second one)...

You can always use PreparedStatement instead of Statment( select, insert , update, delete ). Better performance and protected against SQL injection.

But, don't use it with a dynamic request like a request with WHERE variable IN [ hundreds possibilities ] :

1. It's counter-productive, you lost performance and memory because you cache every time a new request, and PreparedStatement are not just for SQL injection, it's about performance. In this case, Statement will not be slower.

2. Your pool have a limit of PreparedStatment ( -1 defaut but you must limit it ), and you will reach this limit ! and if you have no limit or very large limit you have some risk of memory leak, and in extreme case OutofMemory errors. So if it's for your small personnal project used by 3 users it's not dramatic, but you don't want that if you're in a big company and that you're app is used by thousand people and million request.

Some reading. IBM : Periodical OutOfMemory errors with prepared statement caching

It's simply a Java DESIGN MISTAKE tie "prepared statement" with "parameterized query / bind variables".

Databases does have API to accept "bind variables" in SQL code that just run once time.

It's a big resource wasting force use "prepared statement" everywhere, just to protect from SQL injection. Why not Java just let developers use databases in correct way?

It could be as follows:
Statement Interface - Multiples commands could be run. Not accept bind variables. One execution of SQL command. No SQL injection protection.
PreparedStatement Interface - One command could be run. Accept bind variables. Multiple executions of SQL command. SQL injection protection.
(MISSING IN JAVA!) RunOnceStatement - One command could be run. Accept bind variables. One execution of SQL command. SQL injection protection.

For exemple, in Postgres performance could be better, by driver mapping to:
Statement Interface - PQExec()
PreparedStatement Interface - PQPrepare() / PQExecPrepare() / ...
(MISSING IN JAVA!) RunOnceStatement - PQExecParams()

Using prepared statement in SQL code that runs just once is a BIG performance problem: more processing in database, waste database memory, by maintaining plans that will not called later. Cache plans get so crowed that actual SQL commands that are executed multiple times could be deleted from cache.

Besides preventing SQL injection, formatting portability (which you can't get from Statement), performance is the obvious reason. However, PreparedStatement doesn't come without any penalty. For example, it is generally slower than Statement if running only once, as there is some overhead. So the general idea is PreparedStatement should be used when you are performing the same query many many times. However, how much overhead is very database server implementation-specific, so exactly when to choose PreparedStatement over Statement, from performance consideration, should really be based on your actual experience/experiments of a specific database server.

I was facing the same issue ,Then I break the query into multiple common table expression (cte) and now it's working fine for me.

SELECT DISTINCT 
            1 AS RecordSource, --CMD
            EA.EmployerKey,
            EA.AccountID, 
            ISNULL(EA.SourceGroupNumber,'NA') AS SourceGroupNumber,
            EA.SourceSubGroupNumber,
            EA.PurchaserOrgNumber,
            ISNULL(EA.GroupName,'NA') AS GroupName, 
            EA.MemberSourceCode,
            CASE WHEN ESA.EmailAddress = '' OR ESA.EmailAddress IS NULL THEN 'UNKNOWN@UNKNOWN.COM' 
            ELSE ESA.EmailAddress END AS EmailAddress,
            CASE WHEN ESA.FirstName='' OR ESA.FirstName IS NULL THEN 'NA' ELSE ESA.FirstName END AS FirstName,
            CASE WHEN ESA.MiddleName='' OR ESA.MiddleName IS NULL THEN 'NA' ELSE  ESA.MiddleName END AS MiddleInitial,
            CASE WHEN ESA.LastName='' OR ESA.LastName IS NULL THEN 'NA' ELSE ESA.LastName END AS LastName,
            CASE WHEN ESA.ContactName='' OR ESA.ContactName IS NULL THEN 'NA' ELSE ESA.ContactName END AS ContactName,
            ISNULL(ESA.PhoneNumber,'NA') As PhoneNumber,
            'NA' AS MobilePhoneNumber,
            'GROUP ADMINISTRATOR' AS ContactType,
            ESA.StateCode
            
        INTO DCB_A1.Temp_abc

        FROM
            (SELECT  FE.EmployerKey, DE.AccountID, DE.SourceGroupNumber, DE.SourceSubGroupNumber,DE.PurchaserOrgNumber,
            DE.GroupName, DE.MemberSourceCode
            
            FROM DCB_A1.FctESF FE 
            
            INNER JOIN DCB_A1.DimESF DE 
            ON FE.EmployerKey = DE.EmployerKey

            LEFT JOIN DCB_A1.DimMKBU DM 
            ON FE.MBUKey = DM.MarketingBusinessUnitKey
                
            where DE.IsActiveVersion = 1 AND DE.PurchaserOrgTerminationReasonCode <> '171'
            --and (MBUStateCoverage <> '' and MBUStateCoverage <> 'UNK')
            
            GROUP BY FE.EmployerKey, DE.AccountID, DE.SourceGroupNumber,DE.SourceSubGroupNumber,DE.PurchaserOrgNumber,
             DE.GroupName, DE.MemberSourceCode
            )EA
            INNER JOIN
            (SELECT  DE.AccountID,DE.SourceGroupNumber,
            DE.MemberSourceCode,DE.GroupName,
            ISNULL(DC.ContactValue,'') AS EmailAddress, 
            ISNULL(DN.ContactText2,'') AS FirstName,
            ISNULL(DN.ContactText3,'') AS MiddleName,
            ISNULL(DN.ContactText4,'') AS LastName,
            ISNULL(DN.ContactValue,'') AS ContactName,
            ISNULL(DP.ContactValue,'') AS PhoneNumber,
            SUBSTRING(MBUStateCoverage, 1, 2) AS StateCode 
            
            FROM DCB_A1.FctESF FE 
            
            INNER JOIN DCB_A1.DimESF DE 
            ON FE.EmployerKey = DE.EmployerKey
            
            LEFT JOIN DCA_A1.DimECSF DC 
            ON DE.MemberSourceCode = DC.MemberSourceCode
            AND DE.PurchaserOrgNumber = DC.PurchaserOrgNumber
            AND DE.PurchaserOrgTypeCode = DC.PurchaserOrgTypeCode  
            AND DC.ContactType = 3  
            --and DC.ContactValue <> '' -- Do we need this?
            
            LEFT JOIN DCA_A1.DimECSF DN 
            ON DE.MemberSourceCode = DN.MemberSourceCode
            AND DE.PurchaserOrgNumber = DN.PurchaserOrgNumber
            AND DE.PurchaserOrgTypeCode = DN.PurchaserOrgTypeCode  
            AND DN.ContactType = 4
            AND DN.IsActiveVersion = 1
            
            LEFT JOIN DCA_A1.DimECSF DP 
            ON DE.MemberSourceCode = DP.MemberSourceCode
            AND DE.PurchaserOrgNumber = DP.PurchaserOrgNumber
            AND DE.PurchaserOrgTypeCode = DP.PurchaserOrgTypeCode  
            AND DP.ContactType = 1  
            
            LEFT JOIN DCB_A1.DimMKBU DM 
            ON FE.MBUKey = DM.MarketingBusinessUnitKey
                
            WHERE DE.IsActiveVersion = 2
            --and DE.AccountID =2
            --AND (DE.CustomerStatusCode = 'A' 
            -- OR DE.IsEmployerActive = 0)
            ----and CustomerStatusCode = 'A'
            --AND (MBUStateCoverage <> '' and MBUStateCoverage <> 'UNK')
            
            GROUP BY DE.AccountID,DE.SourceGroupNumber,
            DE.MemberSourceCode,DE.GroupName,
            ISNULL(DC.ContactValue,''), ISNULL(DN.ContactValue,''),
            ISNULL(DN.ContactText2,''),ISNULL(DN.ContactText3,''),
            ISNULL(DN.ContactText4,''),ISNULL(DP.ContactValue,'') ,SUBSTRING(MBUStateCoverage, 1, 2)
            )ESA

            ON EA.AccountID = ESA.AccountID ;

[Amazon][JDBC](11220) Parameters cannot be used with normal Statement objects, use PreparedStatements instead.`

****Below check what I done with this code and it iss now working fine.****


    with ESA AS
(SELECT  DE.AccountID,DE.SourceGroupNumber,
            DE.MemberSourceCode,DE.GroupName,
            ISNULL(DC.ContactValue,'') AS EmailAddress, 
            ISNULL(DN.ContactText2,'') AS FirstName,
            ISNULL(DN.ContactText3,'') AS MiddleName,
            ISNULL(DN.ContactText4,'') AS LastName,
            ISNULL(DN.ContactValue,'') AS ContactName,
            ISNULL(DP.ContactValue,'') AS PhoneNumber,
            SUBSTRING(MBUStateCoverage, 1, 2) AS StateCode 
            
            FROM DCB_A1.FctESF FE 
            
            INNER JOIN DCB_A1.DimESF DE 
            ON FE.EmployerKey = DE.EmployerKey
            
            LEFT JOIN DCA_A1.DimECSF DC 
            ON DE.MemberSourceCode = DC.MemberSourceCode
            AND DE.PurchaserOrgNumber = DC.PurchaserOrgNumber
            AND DE.PurchaserOrgTypeCode = DC.PurchaserOrgTypeCode  
            AND DC.ContactType = 3  
            --and DC.ContactValue <> '' -- Do we need this?
            
            LEFT JOIN DCA_A1.DimECSF DN 
            ON DE.MemberSourceCode = DN.MemberSourceCode
            AND DE.PurchaserOrgNumber = DN.PurchaserOrgNumber
            AND DE.PurchaserOrgTypeCode = DN.PurchaserOrgTypeCode  
            AND DN.ContactType = 4
            AND DN.IsActiveVersion = 1
            
            LEFT JOIN DCA_A1.DimECSF DP 
            ON DE.MemberSourceCode = DP.MemberSourceCode
            AND DE.PurchaserOrgNumber = DP.PurchaserOrgNumber
            AND DE.PurchaserOrgTypeCode = DP.PurchaserOrgTypeCode  
            AND DP.ContactType = 1  
            
            LEFT JOIN DCB_A1.DimMKBU DM 
            ON FE.MBUKey = DM.MarketingBusinessUnitKey
                
            WHERE DE.IsActiveVersion = 2
            --and DE.AccountID =2
            --AND (DE.CustomerStatusCode = 'A' 
            -- OR DE.IsEmployerActive = 0)
            ----and CustomerStatusCode = 'A'
            --AND (MBUStateCoverage <> '' and MBUStateCoverage <> 'UNK')
            
            GROUP BY DE.AccountID,DE.SourceGroupNumber,
            DE.MemberSourceCode,DE.GroupName,
            ISNULL(DC.ContactValue,''), ISNULL(DN.ContactValue,''),
            ISNULL(DN.ContactText2,''),ISNULL(DN.ContactText3,''),
            ISNULL(DN.ContactText4,''),ISNULL(DP.ContactValue,'') ,SUBSTRING(MBUStateCoverage, 1, 2)
),  EMK AS (
       SELECT DISTINCT 
            1 AS RecordSource, --CMD
            EA.EmployerKey,
            EA.AccountID, 
            ISNULL(EA.SourceGroupNumber,'NA') AS SourceGroupNumber,
            EA.SourceSubGroupNumber,
            EA.PurchaserOrgNumber,
            ISNULL(EA.GroupName,'NA') AS GroupName, 
            EA.MemberSourceCode
        FROM
            (SELECT  FE.EmployerKey, DE.AccountID, DE.SourceGroupNumber, DE.SourceSubGroupNumber,DE.PurchaserOrgNumber,
            DE.GroupName, DE.MemberSourceCode
            
            FROM DCB_A1.FctESF FE 
            
            INNER JOIN DCB_A1.DimESF DE 
            ON FE.EmployerKey = DE.EmployerKey

            LEFT JOIN DCB_A1.DimMKBU DM 
            ON FE.MBUKey = DM.MarketingBusinessUnitKey
                
            where DE.IsActiveVersion = 1 AND DE.PurchaserOrgTerminationReasonCode <> '171'
            --and (MBUStateCoverage <> '' and MBUStateCoverage <> 'UNK')
            
            GROUP BY FE.EmployerKey, DE.AccountID, DE.SourceGroupNumber,DE.SourceSubGroupNumber,DE.PurchaserOrgNumber,
             DE.GroupName, DE.MemberSourceCode)EA
    )
    Select Distinct 
    RecordSource
    ,EMK.EmployerKey
    ,EMK.AccountID
    ,EMK.SourceGroupNumber
    ,EMK.SourceSubGroupNumber
    ,EMK.PurchaserOrgNumber
    ,EMK.GroupName
    ,EMK.MemberSourceCode
    ,CASE WHEN ESA.EmailAddress = '' OR ESA.EmailAddress IS NULL THEN 'UNKNOWN@UNKNOWN.COM' 
            ELSE ESA.EmailAddress END AS EmailAddress
    ,CASE WHEN ESA.FirstName='' OR ESA.FirstName IS NULL THEN 'NA' ELSE ESA.FirstName END AS FirstName
    ,CASE WHEN ESA.MiddleName='' OR ESA.MiddleName IS NULL THEN 'NA' ELSE  ESA.MiddleName END AS MiddleInitial
    ,CASE WHEN ESA.LastName='' OR ESA.LastName IS NULL THEN 'NA' ELSE ESA.LastName END AS LastName
    ,CASE WHEN ESA.ContactName='' OR ESA.ContactName IS NULL THEN 'NA' ELSE ESA.ContactName END AS ContactName
    ,ISNULL(ESA.PhoneNumber,'NA') As PhoneNumber
    ,'NA' AS MobilePhoneNumber
    ,'GROUP ADMINISTRATOR' AS ContactType
    ,ESA.StateCode
    INTO DCB_A1.Temp_abc
    FROM EMK 
    INNER JOIN ESA
    ON EMK.AccountID = ESA.AccountID ;