LAST_INSERT_ID() with threads

[John Schretz]

I need to get the id from a previous insert to use as a foreign ke in another table
I am using the connection pool below in a web application.
Can i use LAST_INSERT_ID() in this configuration whithout the risk of getting the wrong value?

If not what is another way of doing this?

  
import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;

public class ConnectionPool
{
    private static ConnectionPool pool = null;
    private static DataSource dataSource = null;
 
    public synchronized static ConnectionPool getInstance()
    {
        if (pool == null)
        {
            pool = new ConnectionPool();
        }
        return pool;
    }

    private ConnectionPool()
    {
        try
        {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/*******");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    public Connection getConnection()
    {
        try
        {
            return dataSource.getConnection();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            return null;
        }
    }
    
    public void freeConnection(Connection c)
    {
        try
        {
            c.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
    }
}
  

[Paul Clapham]

At the very least, you will want to send the LAST_INSERT_ID query to the same connection which did the INSERT statement. None of the code you posted seems to show an example of what you are doing.

[John Schretz]

Below is what i am currently doing but i am using SELECT @@IDENTITY AS IDENTITY
This is giving me a problem so i am looking for an laternative

thanks
john

  
public static int InsertDiscount(Discount discount, 
			ArrayList <DiscountExclBrands> dBrands, 
			ArrayList <DiscountDeptInclusions> dDept, 
			ArrayList <DiscountExclItems> dItems, ArrayList <DiscountBrandInclusions> inclBrand)
	{
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query1 = "INSERT INTO DISCOUNT (DISCOUNT_CODE, TYP) VALUES (?, ?)";

		String query2 = "INSERT INTO DISCOUNT_SETTINGS (DISCOUNT_ID, DATE_ACTIVATED, " +
		"DEACTIVATE_DATE, DATE_ISSUED, DATE_REDEEMED, BUY_QTY, BUY_DOLLAR, " +
		"DISC_DOLLAR, DISC_QTY, DISC_PERCENT, MULTI_BUY_TYP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		String query3 = "INSERT INTO DISCOUNT_EXCLUSION_ITEMS (DISCOUNT_ID, ITEMNUMBER) VALUES (?, ?)";

		String query4 = "INSERT INTO DISCOUNT_EXCLUSION_BRANDS (DISCOUNT_ID, VENDORCODE) VALUES (?, ?)";

		String query5 = "INSERT INTO DISCOUNT_DEPT_INCLUSIONS (DISCOUNT_ID, DEPARTMENTCODE) VALUES (?, ?)";

		String query6 = "INSERT INTO SHIP_CODE_SETTINGS (DISCOUNT_ID, FLAT_RATE, SHIP_METHOD) VALUES (?, ?, ?)";
		
		String query7 = "INSERT INTO DISCOUNT_BRAND_INCLUSIONS (DISCOUNT_ID, VENDORCODE) VALUES (?, ?)";

		try
		{
			ps = connection.prepareStatement(query1);

			ps.setString(1, discount.getDiscountCode());
			ps.setInt(2,discount.getTyp());

			try{
				String insert = ps.toString();
				SQLlogging.getSQLlogger().info(insert.substring(insert.indexOf(":")+1));
			}
			catch(Exception e){
				e.printStackTrace();
			}

			//Get the DISCOUNT_ID from the last INSERT statement.
			String identityQuery = "SELECT @@IDENTITY AS IDENTITY";
			Statement identityStatement = connection.createStatement();
			ResultSet identityResultSet = identityStatement.executeQuery(identityQuery);
			identityResultSet.next();
			int discountId = identityResultSet.getInt("IDENTITY");
			identityResultSet.close();
			identityStatement.close();

			if(discountId > 0){
				ps = connection.prepareStatement(query2);

				ps.setInt(1, discountId);
				ps.setDate(2, discount.getDateActivated());
				ps.setDate(3, discount.getDeactivateDate());
				ps.setDate(4, discount.getDateIssued());
				ps.setDate(5, discount.getDateRedeemed());
				ps.setInt(6, discount.getBuyQty());
				ps.setDouble(7, discount.getBuyDollar());
				ps.setDouble(8, discount.getDiscDollar());
				ps.setInt(9, discount.getDiscQty());
				ps.setDouble(10, discount.getDiscPercent());
				ps.setString(11, discount.getMultiType());

				try{
					SQLlogging.getSQLlogger().info(ps);
					ps.executeUpdate();
				}
				catch(Exception e){
					e.printStackTrace();
				}

				if(dItems != null && dItems.size() > 0){
					for(int i = 0; i < dItems.size(); i++){
						ps = connection.prepareStatement(query3);

						ps.setInt(1, discountId);
						ps.setInt(2,dItems.get(i).getItemNum());

						try{
							SQLlogging.getSQLlogger().info(ps);
							ps.executeUpdate();
						}
						catch(Exception e){
							e.printStackTrace();
						}
					}
				}

				if(dBrands != null && dBrands.size() > 0){
					for(int i = 0; i < dBrands.size(); i++){
						ps = connection.prepareStatement(query4);

						ps.setInt(1, discountId);
						ps.setString(2,dBrands.get(i).getVendorCode());

						try{
							SQLlogging.getSQLlogger().info(ps);
							ps.executeUpdate();
						}
						catch(Exception e){
							e.printStackTrace();
						}
					}
				}

				if(dDept != null && dDept.size() > 0){
					for(int i = 0; i < dDept.size(); i++){
						ps = connection.prepareStatement(query5);

						ps.setInt(1, discountId);
						ps.setString(2,dDept.get(i).getDeptCode());

						try{
							SQLlogging.getSQLlogger().info(ps);
							ps.executeUpdate();
						}
						catch(Exception e){
							e.printStackTrace();
						}
					}
				}

				if(discount.getDiscShip() != null){

					ps = connection.prepareStatement(query6);

					ps.setInt(1, discountId);
					ps.setDouble(2,discount.getDiscShip().getFlatRate());
					ps.setInt(3,discount.getDiscShip().getShipMeth());

					try{
						SQLlogging.getSQLlogger().info(ps);
						ps.executeUpdate();
					}
					catch(Exception e){
						e.printStackTrace();
					}
				}
				
				if(inclBrand != null && inclBrand.size() > 0){
					for(int i = 0; i < inclBrand.size(); i++){
						ps = connection.prepareStatement(query7);

						ps.setInt(1, discountId);
						ps.setString(2,inclBrand.get(i).getVendorCode());

						try{
							SQLlogging.getSQLlogger().info(ps);
							ps.executeUpdate();
						}
						catch(Exception e){
							e.printStackTrace();
						}
					}
				}
			}

			return discountId;
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			UtilDB.closeResultSet(rs);
			UtilDB.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}


  

[Paul Clapham]

In that code I don't see where you do an insert before you do the SELECT @@IDENTITY query. Maybe that's your problem.

[John Schretz]

Paul Clapham wrote:In that code I don't see where you do an insert before you do the SELECT @@IDENTITY query. Maybe that's your problem.

OMG you are the man. That was one of my other problems.
I must have overwrote the execute line when I pasted:

   
      try{   
                String insert = ps.toString();   
                SQLlogging.getSQLlogger().info(insert.substring(insert.indexOf(":")+1));   
            }   
            catch(Exception e){   
                e.printStackTrace();   
            } 
 

It helps to have a second pair of eyes.
This may relate to the second problem also.

Aside from that do you see any other flaws in the logic?
thanks
john