package testvelosiped;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleTypes;
import static testvelosiped.Db.isNullOrClosed;
import static testvelosiped.Db.prepareStatement;

public class Velosiped {

    public final int id;
    private String modelName;
    private double height, length, width, weight;

    private Velosiped(ResultSet rs) throws SQLException {
        id        = rs.getInt   (1);
        modelName = rs.getString(2);
        height    = rs.getDouble(3);
        length    = rs.getDouble(4);
        width     = rs.getDouble(5);
        weight    = rs.getDouble(6);
    }

    //<editor-fold defaultstate="collapsed" desc="create">
    private static OraclePreparedStatement psCreate;
    public static Velosiped create(String modelName, double height, double length, double width, double weight) throws SQLException {
        if (isNullOrClosed(psCreate)) {
            psCreate = (OraclePreparedStatement) prepareStatement(
                    "INSERT INTO your_schema_name.velosipeds (model_name, height, length, width, weight) " +
                    "VALUES (?, ?, ?, ?, ?) " +
                    "RETURNING velosiped_id, model_name, height, length, width, weight INTO ?, ?, ?, ?, ?, ?");
            psCreate.registerReturnParameter(6,  OracleTypes.NUMBER);
            psCreate.registerReturnParameter(7,  OracleTypes.VARCHAR);
            psCreate.registerReturnParameter(8,  OracleTypes.NUMBER);
            psCreate.registerReturnParameter(9,  OracleTypes.NUMBER);
            psCreate.registerReturnParameter(10, OracleTypes.NUMBER);
            psCreate.registerReturnParameter(11, OracleTypes.NUMBER);
        }
        psCreate.setString(1, modelName);
        psCreate.setDouble(2, height);
        psCreate.setDouble(3, length);
        psCreate.setDouble(4, width);
        psCreate.setDouble(5, weight);
        if (psCreate.executeUpdate() > 0) {
            try (ResultSet rs = psCreate.getReturnResultSet()) {
                if (rs.next()) {
                    return new Velosiped(rs);
                }
            }
        }
        throw new SQLException("Не удалось вставить данные в таблицу velosipeds");
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="read">
    private static PreparedStatement psQueryAll;
    public static ArrayList<Velosiped> queryAll() throws SQLException {
        if (isNullOrClosed(psQueryAll)) {
            psQueryAll = prepareStatement(
                    "SELECT velosiped_id, model_name, height, length, width, weight " +
                    "FROM your_schema_name.velosipeds");
        }
        try (ResultSet resultSet = psQueryAll.executeQuery()) {
            ArrayList<Velosiped> list = new ArrayList<>(50);
            while (resultSet.next()) {
                list.add(new Velosiped(resultSet));
            }
            return list;
        }
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="update">
    private static OraclePreparedStatement psUpdateModelName;
    public void updateModelName(String modelName) throws SQLException {
        if (isNullOrClosed(psUpdateModelName)) {
            psUpdateModelName = (OraclePreparedStatement) prepareStatement(
                    "UPDATE your_schema_name.velosipeds " +
                    "SET model_name = ? " +
                    "WHERE velosiped_id = ? " +
                    "RETURNING model_name INTO ?");
            psUpdateModelName.registerReturnParameter(3, OracleTypes.VARCHAR);
        }
        psUpdateModelName.setString(1, modelName);
        psUpdateModelName.setInt(2, id);
        if (psUpdateModelName.executeUpdate() > 0) {
            try (ResultSet rs = psUpdateModelName.getReturnResultSet()) {
                if (rs.next()) {
                    this.modelName = rs.getString(1);
                    return;
                }
            }
        }
        throw new SQLException("Не удалось обновить данные в таблице velosipeds");
    }
    //
    private static OraclePreparedStatement psUpdateSize;
    public void updateSize(double height, double length, double width) throws SQLException {
        if (isNullOrClosed(psUpdateSize)) {
            psUpdateSize = (OraclePreparedStatement) prepareStatement(
                    "UPDATE your_schema_name.velosipeds " +
                    "SET height = ?, length = ?, width = ? " +
                    "WHERE velosiped_id = ? " +
                    "RETURNING height, length, width INTO ?, ?, ?");
            psUpdateSize.registerReturnParameter(5, OracleTypes.NUMBER);
            psUpdateSize.registerReturnParameter(6, OracleTypes.NUMBER);
            psUpdateSize.registerReturnParameter(7, OracleTypes.NUMBER);
        }
        psUpdateSize.setDouble(1, height);
        psUpdateSize.setDouble(2, length);
        psUpdateSize.setDouble(3, width);
        psUpdateSize.setInt(4, id);
        if (psUpdateSize.executeUpdate() > 0) {
            try (ResultSet rs = psUpdateSize.getReturnResultSet()) {
                if (rs.next()) {
                    this.height = rs.getDouble(1);
                    this.length = rs.getDouble(2);
                    this.width  = rs.getDouble(3);
                    return;
                }
            }
        }
        throw new SQLException("Не удалось обновить данные в таблице velosipeds");
    }
    //
    private static OraclePreparedStatement psUpdateWeight;
    public void updateWeight(double weight) throws SQLException {
        if (isNullOrClosed(psUpdateWeight)) {
            psUpdateWeight = (OraclePreparedStatement) prepareStatement(
                    "UPDATE your_schema_name.velosipeds " +
                    "SET weight = ? " +
                    "WHERE velosiped_id = ? " +
                    "RETURNING weight INTO ?");
            psUpdateWeight.registerReturnParameter(3, OracleTypes.NUMBER);
        }
        psUpdateWeight.setDouble(1, weight);
        psUpdateWeight.setInt(2, id);
        if (psUpdateWeight.executeUpdate() > 0) {
            try (ResultSet rs = psUpdateWeight.getReturnResultSet()) {
                if (rs.next()) {
                    this.weight = rs.getDouble(1);
                    return;
                }
            }
        }
        throw new SQLException("Не удалось обновить данные в таблице velosipeds");
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="delete">
    private static PreparedStatement psDelete;
    public void delete() throws SQLException {
        if (isNullOrClosed(psDelete)) {
            psDelete = prepareStatement(
                    "DELETE FROM your_schema_name.velosipeds " +
                    "WHERE velosiped_id = ?");
        }
        psDelete.setInt(1, id);
        psDelete.executeUpdate();
    }
    //</editor-fold>


    //<editor-fold defaultstate="collapsed" desc="getters">
    public String getModelName() {
        return modelName;
    }

    public double getHeight() {
        return height;
    }

    public double getLength() {
        return length;
    }

    public double getWidth() {
        return width;
    }

    public double getWeight() {
        return weight;
    }
    //</editor-fold>

    @Override public int hashCode() {
        return id;
    }

    @Override public boolean equals(Object another) {
        return another instanceof Velosiped && ((Velosiped) another).id == id;
    }

    @Override public String toString() {
        return "Велосипед id: " + id + " модель: " + modelName;
    }
}

package testvelosiped;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Locale;
import java.util.Properties;

public class Db {

    private static String
            CONNECTION_STRING = "jdbc:oracle:thin:@127.0.0.1:1521:XE",
            LOGIN = "velosipedist",
            PASSWORD = "velosiped"; //чтож ещё)))

    private static boolean driverLoaded;
    public static Connection openConnection() throws SQLException {
        if (!driverLoaded) {
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
            } catch (ClassNotFoundException ex) {
                throw new SQLException("Не найден драйвер подключения к базе данных Oracle", ex);
            }
            driverLoaded = true;
        }

        Properties properties = new Properties();
        properties.put("user", LOGIN);
        properties.put("password", PASSWORD);
        properties.put("v$session.program", "velosiped_analyzer");

        Locale prevLocale = Locale.getDefault();
        try {
            Locale.setDefault(Locale.ENGLISH);
            return DriverManager.getConnection(CONNECTION_STRING, properties);
        } finally {
            Locale.setDefault(prevLocale);
        }
    }

    private static Connection connection;
    public static Connection getConnection() throws SQLException {
        if (isNullOrClosed(connection)) {
            connection = openConnection();
        }
        return connection;
    }

    public static PreparedStatement prepareStatement(String sql) throws SQLException {
        return getConnection().prepareStatement(sql);
    }

    public static boolean isNullOrClosed(Connection connection) throws SQLException {
        return connection == null || connection.isClosed();
    }

    public static boolean isNullOrClosed(PreparedStatement ps) throws SQLException {
        return ps == null || ps.isClosed() || ps.getConnection().isClosed();
    }
}