Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:com.jabyftw.lobstercraft.world.CityStructure.java

/**
 * Create a house for the citizens.<br>
 * Note: this should run asynchronously.
 *
 * @param blockLocation house location given by the city manager
 * @return a response for the CommandSender
 *//*from w ww.  j  a v  a 2  s  . co  m*/
public HouseCreationResponse createHouse(@NotNull final BlockLocation blockLocation) throws SQLException {
    // Check if there are more houses than current number of citizens + 1 (if level isn't the maximum)
    if (cityHouses.size() >= getMaximumNumberOfCitizens() + (cityLevel == MAXIMUM_CITY_LEVEL ? 0 : 2))
        return HouseCreationResponse.TOO_MANY_HOUSES_REGISTERED;

    // Check minimum height
    if (blockLocation.getY()
            - BlockProtectionType.CITY_HOUSES.getProtectionDistance() < WorldService.MINIMUM_PROTECTION_HEIGHT)
        return HouseCreationResponse.HOUSE_COORDINATE_Y_TOO_LOW;

    // Check minimum and maximum distance between city center
    // Note: this should use the protection distance of the CITY_BLOCKS because this would make the center on the "same height" as the block if checkY is enabled on
    // CITY_HOUSES but not on CITY_BLOCKS
    // Note: the corner of the house should be the corner of current protection range
    if (BlockProtectionType.CITY_BLOCKS.protectionDistanceSquared(blockLocation,
            centerLocation) < getProtectionRangeSquared()
                    - BlockProtectionType.CITY_HOUSES.getProtectionDistanceSquared())
        return HouseCreationResponse.TOO_FAR_FROM_CENTER;
    else if (BlockProtectionType.CITY_BLOCKS.protectionDistanceSquared(blockLocation,
            centerLocation) < BlockProtectionType.CITY_HOUSES.getProtectionDistanceSquared())
        return HouseCreationResponse.TOO_CLOSE_TO_THE_CENTER;

    // Check minimum distance between other houses
    for (BlockLocation existingBlockLocation : cityHouses.keySet())
        if (BlockProtectionType.CITY_HOUSES.protectionDistanceSquared(blockLocation,
                existingBlockLocation) <= BlockProtectionType.CITY_HOUSES.getProtectionDistanceSquared())
            return HouseCreationResponse.TOO_CLOSE_TO_OTHER_HOUSE;

    int houseId;
    // Insert to database
    {
        Connection connection = LobsterCraft.dataSource.getConnection();
        // Prepare statement
        PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO `minecraft`.`city_house_locations` (`city_cityId`, `worlds_worldId`, `centerChunkX`, `centerChunkZ`, `centerX`, `centerY`, `centerZ`) "
                        + "VALUES (?, ?, ?, ?, ?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);

        // Set variables
        preparedStatement.setInt(1, cityId);
        preparedStatement.setByte(2, blockLocation.getChunkLocation().getWorldId());
        preparedStatement.setInt(3, blockLocation.getChunkLocation().getChunkX());
        preparedStatement.setInt(4, blockLocation.getChunkLocation().getChunkZ());
        preparedStatement.setByte(5, blockLocation.getRelativeX());
        preparedStatement.setShort(6, blockLocation.getY());
        preparedStatement.setByte(7, blockLocation.getRelativeZ());

        // Execute statement, get generated key
        preparedStatement.execute();
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();

        // Check if id exists
        if (!generatedKeys.next())
            throw new SQLException("Generated key not generated!");

        // Get house key
        houseId = generatedKeys.getInt("houseId");
        if (houseId <= 0)
            throw new SQLException("House id must be greater than 0");
    }

    // Create variable
    CityHouse cityHouse = new CityHouse(houseId, cityId, blockLocation);

    // Insert house and return
    cityHouses.put(cityHouse, cityHouse);
    return HouseCreationResponse.SUCCESSFULLY_CREATED_HOUSE;
}

From source file:com.wso2telco.dep.ratecardservice.dao.OperationRateDAO.java

public OperationRateDTO addOperationRate(OperationRateDTO operationRate) throws BusinessException {

    Connection con = null;// w  w w  .  ja v  a2s.  co  m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer operationRateId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.OPERATION_RATE.getTObject());
        query.append(" (operator_id, api_operationid, rate_defid, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addOperationRate : " + ps);

        Integer operatorId = operationRate.getOperator().getOperatorId();
        if (operatorId != null) {
            ps.setInt(1, operatorId);
        } else {
            ps.setNull(1, Types.INTEGER);
        }

        ps.setInt(2, operationRate.getApiOperation().getApiOperationId());
        ps.setInt(3, operationRate.getRateDefinition().getRateDefId());
        ps.setString(4, operationRate.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            operationRateId = rs.getInt(1);
        }

        operationRate.setOperationRateId(operationRateId);
    } catch (SQLException e) {

        log.error("database operation error in addOperationRate : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addOperationRate : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return operationRate;
}

From source file:com.xqdev.sql.MLSQL.java

protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
    res.setContentType("text/xml");

    Namespace sql = Namespace.getNamespace("sql", "http://xqdev.com/sql");
    Document responseDoc = new Document();
    Element root = new Element("result", sql);
    Element meta = new Element("meta", sql);
    responseDoc.setRootElement(root);/*from  w w w  . ja  va2s  . c om*/
    root.addContent(meta);

    Document requestDoc = null;
    try {
        // Normally the request comes via the post body,
        // but we let you bookmark w/ a query string
        String postbody = req.getParameter("postbody");
        if (postbody != null) {
            SAXBuilder builder = new SAXBuilder();
            requestDoc = builder.build(new StringReader(postbody));
        } else {
            InputStream in = req.getInputStream();
            SAXBuilder builder = new SAXBuilder();
            requestDoc = builder.build(in);
        }
    } catch (Exception e) {
        addExceptions(meta, e);
        // Now write the error and return
        OutputStream out = res.getOutputStream();
        new XMLOutputter().output(responseDoc, out);
        out.flush();
        return;
    }

    Connection con = null;
    try {
        Namespace[] namespaces = new Namespace[] { sql };
        XPathHelper xpath = new XPathHelper(requestDoc, namespaces);

        String type = xpath.getString("/sql:request/sql:type");
        String query = xpath.getString("/sql:request/sql:query");
        int maxRows = xpath.getInt("/sql:request/sql:execute-options/sql:max-rows", -1);
        int queryTimeout = xpath.getInt("/sql:request/sql:execute-options/sql:query-timeout", -1);
        int maxFieldSize = xpath.getInt("/sql:request/sql:execute-options/sql:max-field-size", -1);
        List<Element> params = xpath
                .getElements("/sql:request/sql:execute-options/sql:parameters/sql:parameter");

        con = pool.getConnection();

        PreparedStatement stmt = null;

        if (type.equalsIgnoreCase("procedure")) {
            stmt = con.prepareCall(query);
        } else {
            // Note this call depends on JDBC 3.0 (accompanying Java 1.4).
            // The call without the 2nd argument would work on earlier JVMs,
            // you just won't catch any generated keys.
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        }
        configureStatement(stmt, maxRows, queryTimeout, maxFieldSize);
        parameterizeStatement(stmt, params);

        if (type.equalsIgnoreCase("select")) {
            try {
                ResultSet rs = stmt.executeQuery();
                addWarnings(meta, stmt.getWarnings());
                addResultSet(root, rs);
            } catch (SQLException e) {
                addExceptions(meta, e);
                Log.log(e);
            }
        } else if (type.equalsIgnoreCase("update")) {
            try {
                int count = stmt.executeUpdate();
                addWarnings(meta, stmt.getWarnings());
                addUpdateCount(meta, count);
                try {
                    addGeneratedKeys(meta, stmt.getGeneratedKeys());
                } catch (SQLException e) {
                    // Generated keys are available on INSERT calls but not UPDATE calls
                    // So catch and eat the exception that Oracle (and maybe others) will throw
                }
            } catch (SQLException e) {
                addExceptions(meta, e);
            }
        } else if (type.equalsIgnoreCase("procedure")) {
            boolean isResultSet = stmt.execute();
            if (isResultSet) {
                addResultSet(root, stmt.getResultSet());
                addOutParam(root, stmt, params);
            } else {
                addOutParam(root, stmt, params);
            }
        } else {
            try {
                boolean isResultSet = stmt.execute();
                addWarnings(meta, stmt.getWarnings());
                if (isResultSet) {
                    addResultSet(root, stmt.getResultSet());
                } else {
                    addUpdateCount(meta, stmt.getUpdateCount());
                    addGeneratedKeys(meta, stmt.getGeneratedKeys());
                }
            } catch (SQLException e) {
                addExceptions(meta, e);
            }
        }
        // Close the statement holding the connection to the JDBC Server
        stmt.close();
    } catch (Exception e) {
        addExceptions(meta, e);
    } finally {
        if (con != null)
            pool.returnConnection(con);
    }

    OutputStream out = res.getOutputStream();
    new XMLOutputter().output(responseDoc, out);
    out.flush();
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCRatingsVersionDAO.java

/**
 * Method to persist a rating.//from w  w  w.  j  a va  2s .co m
 *
 * @param resourceImpl the resource
 * @param userID       the id of the user who added the rating.
 * @param rating       the rating to be persisted.
 *
 * @throws RegistryException if some error occurs while adding a rating
 */
public void addRating(ResourceImpl resourceImpl, String userID, int rating) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();

    PreparedStatement ps1 = null, ps2 = null, ps3 = null;
    ResultSet result = null;
    try {

        String sql1 = "INSERT INTO REG_RATING (REG_RATING, REG_USER_ID, REG_RATED_TIME, "
                + "REG_TENANT_ID) VALUES (?,?,?,?)";
        String sql2 = "SELECT MAX(REG_ID) FROM REG_RATING";
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps1 = conn.prepareStatement(sql1, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) });
        } else {
            ps1 = conn.prepareStatement(sql1);
        }
        ps1.setInt(1, rating);
        ps1.setString(2, userID);
        long now = System.currentTimeMillis();
        ps1.setDate(3, new Date(now));
        ps1.setInt(4, CurrentSession.getTenantId());
        if (returnsGeneratedKeys) {
            ps1.executeUpdate();
            result = ps1.getGeneratedKeys();
        } else {
            synchronized (ADD_RATING_LOCK) {
                ps1.executeUpdate();
                ps2 = conn.prepareStatement(sql2);
                result = ps2.executeQuery();
            }
        }
        if (result.next()) {
            int rateID = result.getInt(1);

            String sql3 = "INSERT INTO REG_RESOURCE_RATING (REG_RATING_ID, REG_VERSION, "
                    + "REG_TENANT_ID) VALUES(?,?,?)";
            ps3 = conn.prepareStatement(sql3);

            ps3.setInt(1, rateID);
            ps3.setLong(2, resourceImpl.getVersionNumber());
            ps3.setInt(3, CurrentSession.getTenantId());

            ps3.executeUpdate();

        }

    } catch (SQLException e) {

        String msg = "Failed to rate resource " + resourceImpl.getVersionNumber() + " with rating " + rating
                + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    try {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    } finally {
                        if (ps3 != null) {
                            ps3.close();
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:com.wso2telco.dep.ratecardservice.dao.RateCategoryDAO.java

public RateCategoryDTO addRateCategory(RateCategoryDTO rateCategory) throws BusinessException {

    Connection con = null;/*from w w w  .java2 s  .c o m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer rateCategoryId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.RATE_CATEGORY.getTObject());
        query.append(" (rate_defid, parentcategoryid, childcategoryid, tariffid, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addRateCategory : " + ps);

        ps.setInt(1, rateCategory.getRateDefinition().getRateDefId());
        ps.setInt(2, rateCategory.getCategory().getCategoryId());
        Integer subCategoryId = rateCategory.getSubCategory().getCategoryId();

        if (subCategoryId != null) {
            ps.setInt(3, subCategoryId);
        } else {
            ps.setNull(3, Types.INTEGER);
        }

        ps.setInt(4, rateCategory.getTariff().getTariffId());
        ps.setString(5, rateCategory.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            rateCategoryId = rs.getInt(1);
        }

        rateCategory.setRateCategoryId(rateCategoryId);
    } catch (SQLException e) {

        log.error("database operation error in addRateCategory : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addRateCategory : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return rateCategory;
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.AbstractApplicationDAOImpl.java

@Override
public int addApplication(Application application, int tenantId) throws DeviceManagementDAOException {
    Connection conn;//from   w w  w . j  a va2 s . c o m
    PreparedStatement stmt = null;
    ResultSet rs = null;
    ByteArrayOutputStream bao = null;
    ObjectOutputStream oos = null;
    int applicationId = -1;
    try {
        conn = this.getConnection();
        stmt = conn.prepareStatement("INSERT INTO DM_APPLICATION (NAME, PLATFORM, CATEGORY, "
                + "VERSION, TYPE, LOCATION_URL, IMAGE_URL, TENANT_ID, APP_PROPERTIES, APP_IDENTIFIER, MEMORY_USAGE, IS_ACTIVE) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        stmt.setString(1, application.getName());
        stmt.setString(2, application.getPlatform());
        stmt.setString(3, application.getCategory());
        stmt.setString(4, application.getVersion());
        stmt.setString(5, application.getType());
        stmt.setString(6, application.getLocationUrl());
        stmt.setString(7, application.getImageUrl());
        stmt.setInt(8, tenantId);

        bao = new ByteArrayOutputStream();
        oos = new ObjectOutputStream(bao);
        oos.writeObject(application.getAppProperties());
        stmt.setBytes(9, bao.toByteArray());

        stmt.setString(10, application.getApplicationIdentifier());
        stmt.setInt(11, application.getMemoryUsage());
        stmt.setBoolean(12, application.isActive());
        stmt.execute();

        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            applicationId = rs.getInt(1);
        }
        return applicationId;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException(
                "Error occurred while adding application '" + application.getName() + "'", e);
    } catch (IOException e) {
        throw new DeviceManagementDAOException("Error occurred while serializing application properties object",
                e);
    } finally {
        if (bao != null) {
            try {
                bao.close();
            } catch (IOException e) {
                log.error("Error occurred while closing ByteArrayOutputStream", e);
            }
        }
        if (oos != null) {
            try {
                oos.close();
            } catch (IOException e) {
                log.error("Error occurred while closing ObjectOutputStream", e);
            }
        }
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:com.adanac.module.blog.dao.MessageDao.java

public Integer save(final String visitorIp, final Date createDate, final String message, final String username,
        final Integer referenceMessageId) {
    return execute(new TransactionalOperation<Integer>() {
        @Override/*ww  w.j a v  a2  s .  c o  m*/
        public Integer doInConnection(Connection connection) {
            try {
                PreparedStatement statement = null;
                if (referenceMessageId == null) {
                    statement = connection.prepareStatement(
                            "insert into messages (visitor_ip,city,message,"
                                    + "create_date,username) values (?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                } else {
                    statement = connection.prepareStatement(
                            "insert into messages (visitor_ip,city,message,"
                                    + "create_date,username,reference_message_id) values (?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                }
                statement.setString(1, visitorIp);
                statement.setString(2,
                        Configuration.isProductEnv() ? HttpApiHelper.getCity(visitorIp) : "?");
                statement.setString(3, message);
                Date finalCommentDate = createDate;
                if (createDate == null) {
                    finalCommentDate = new Date();
                }
                statement.setTimestamp(4, new Timestamp(finalCommentDate.getTime()));
                statement.setString(5, username);
                if (referenceMessageId != null) {
                    statement.setInt(6, referenceMessageId);
                }
                int result = statement.executeUpdate();
                if (result > 0) {
                    ResultSet resultSet = statement.getGeneratedKeys();
                    if (resultSet.next()) {
                        return resultSet.getInt(1);
                    }
                }
            } catch (SQLException e) {
                error("save messages failed ...", e);
            }
            return null;
        }
    });
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.ApplicationDAOImpl.java

@Override
public int addApplication(Application application, int tenantId) throws DeviceManagementDAOException {
    Connection conn;/*  ww  w  .j av a  2 s.c  o  m*/
    PreparedStatement stmt = null;
    ResultSet rs = null;
    ByteArrayOutputStream bao = null;
    ObjectOutputStream oos = null;
    int applicationId = -1;
    try {
        conn = this.getConnection();
        stmt = conn.prepareStatement("INSERT INTO DM_APPLICATION (NAME, PLATFORM, CATEGORY, "
                + "VERSION, TYPE, LOCATION_URL, IMAGE_URL, TENANT_ID, APP_PROPERTIES, APP_IDENTIFIER, MEMORY_USAGE, IS_ACTIVE) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        stmt.setString(1, application.getName());
        stmt.setString(2, application.getPlatform());
        stmt.setString(3, application.getCategory());
        stmt.setString(4, application.getVersion());
        stmt.setString(5, application.getType());
        stmt.setString(6, application.getLocationUrl());
        stmt.setString(7, application.getImageUrl());
        stmt.setInt(8, tenantId);

        bao = new ByteArrayOutputStream();
        oos = new ObjectOutputStream(bao);
        oos.writeObject(application.getAppProperties());
        stmt.setBytes(9, bao.toByteArray());

        stmt.setString(10, application.getApplicationIdentifier());
        stmt.setInt(11, application.getMemoryUsage());
        stmt.setBoolean(12, application.isActive());
        stmt.execute();

        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            applicationId = rs.getInt(1);
        }
        return applicationId;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException(
                "Error occurred while adding application '" + application.getName() + "'", e);
    } catch (IOException e) {
        throw new DeviceManagementDAOException("Error occurred while serializing application properties object",
                e);
    } finally {
        if (bao != null) {
            try {
                bao.close();
            } catch (IOException e) {
                log.warn("Error occurred while closing ByteArrayOutputStream", e);
            }
        }
        if (oos != null) {
            try {
                oos.close();
            } catch (IOException e) {
                log.warn("Error occurred while closing ObjectOutputStream", e);
            }
        }
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:org.apache.sqoop.repository.derby.DerbyTestCase.java

/**
 *Run single, arbitrary insert query on derby memory repository.
 *
 *@param query/*from  w ww. jav  a 2  s. c  o m*/
 *         Query to execute
 *@return Long id of newly inserted row (-1 if none).
 *@throws Exception
 */
protected Long runInsertQuery(String query, Object... args) throws Exception {
    PreparedStatement stmt = null;
    try {
        stmt = getDerbyDatabaseConnection().prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

        for (int i = 0; i < args.length; ++i) {
            if (args[i] instanceof String) {
                stmt.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof Long) {
                stmt.setLong(i + 1, (Long) args[i]);
            } else {
                stmt.setString(i + 1, args[i].toString());
            }
        }

        if (!stmt.execute()) {
            ResultSet rs = stmt.getGeneratedKeys();
            rs.next();
            return rs.getLong(1);
        }
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }

    return -1L;
}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public DepositWithdrawal createDepositOrWithdrawal(int user_id, DepositWithdrawal.OperationType type,
        long amount) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    PreparedStatement statementAccount = null;

    DepositWithdrawal operation = new DepositWithdrawal();
    try {/* ww  w.j a va2s. c  om*/
        statement = connection.prepareStatement(CREATE_ACCOUNTOPERATIONS_STATEMENT);
        connection.setAutoCommit(false);

        statement.setInt(1, user_id);
        int typeOperation = ACCOUNT_OPERATION_DEPOSIT;
        if (type == OperationType.WITHDRAWAL) {
            typeOperation = ACCOUNT_OPERATION_WITHDRAWAL;
        }
        statement.setInt(2, typeOperation);

        statement.setLong(3, amount);
        Date now = new Date();
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(now.getTime());
        statement.setTimestamp(4, sqlDate);
        statement.setNull(5, Types.INTEGER);
        statement.setNull(6, Types.INTEGER);

        statement.execute();
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()) {
            operation.setId(rs.getInt(1));
            operation.setAmount(amount);
            operation.setDate(now);
            operation.setType(type);
        }
        Account account = getAccount(user_id);
        long balanceToSet = 0L;
        if (type == OperationType.DEPOSIT) {
            balanceToSet = account.getBalance() + amount;
        } else {
            balanceToSet = account.getBalance() - amount;
        }
        if (balanceToSet < 0) {
            throw new SQLException("Balance cannot become negative");
        }
        statementAccount = connection.prepareStatement(UPDATE_ACCOUNT_BY_ID_STATEMENT);
        statementAccount.setLong(1, balanceToSet);
        statementAccount.setInt(2, user_id);
        statementAccount.execute();

        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            connection.rollback();
        }
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
    return operation;
}