Autonomous Database streamlines your ORM world

December 7, 2023 | 6 minute read
Phani Turlapati
Outbound Product Manager - Autonomous Database on Dedicated Infrastructure
Chandrakanth Putha
Cloud Architect
Text Size 100%:

Object relational mapping (ORM) is a go-to framework for simplifying the interaction between databases and applications. It’s widely used with relational databases and object-oriented programming (OOP). It makes the life of a developer easy by eliminating the need for writing complex SQLs, database operations management, data validation, transaction management, and much more. This simplification is particularly important in enterprise application development where organizations are looking for rapid development with consistent frameworks.

In the spirit of talking about how developers’ lives are made easy with ORM, similarly, Oracle Autonomous Database makes developers’ lives easy by providing the best converged data platform that can host all types of data and so the developer doesn’t have to sacrifice flexibility or get bogged down by operational requirements or complex integrations. For enterprise application development when you need for high security, extreme isolation and access control, and a unified platform for consolidation, you can read about how an Autonomous Database on Dedicated Infrastructure (ADB-D) can meet those requirements in Why Autonomous Database Dedicated is the perfect home.

ORM for Autonomous Database

In this blog post, we explore how we can use some popular ORM tools with Autonomous Database Dedicated to build enterprise applications.

The market has many ORM tools for various development platforms, but we want to talk about two popular ORM tools for two development platforms: SQLAlchemy in Python and Hibernate in Java. So, here's a brief description of the architecture and code.

A graphic depicting the architecture for a deployment of Autonomous Database on Dedicated Infrastructure using ORM tools.

SQLAlchemy in Python

The example architectural is deployed in an enterprise development environment on Autonomous Database Dedicated. Python and Java applications are connecting to separate databases. With this architecture, you can deploy many of the database connecting to their own applications catering to various lines of businesses, user groups, and so on, providing you with a true converged data platform.

SQLAlchemy is a Python SQL toolkit and object relational mapper. In the following code, we connect the dedicated autonomous database using SQLAlchemy and select data from the EMPLOYEE table, insert data, and update data all using the tool.

 

from sqlalchemy.engine import create_engine
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# use this connection scheme for simplicity for the reader
# create Autonomous Database Engine
engine = create_engine(
    f'oracle+oracledb://:@',
    thick_mode=False,
    connect_args={
        "user": "SCHEMA_NAME",
        "password": "PASSWORD",
        "host": "ADB-D HOST NAME",
        "port": "1521",
        "service_name": "ADB-D SERVICE NAME"
    })

# Create a base class for your models
Base = declarative_base()

# Creating an ORM for Employee Table
class EmployeeDetails(Base):
    __tablename__ = 'EMPLOYEE_DETAILS'  
    employeeId = Column(Integer, primary_key=True)
    firstName = Column(String(100))
    lastName = Column(String(100))
    employeeDesignation = Column(String(100))

Session = sessionmaker(bind=engine)
session = Session()

# If the table doesn't exist, this will create the EMPLOYEE Table
Base.metadata.create_all(engine)

# Query to create the record
new_record = EmployeeDetails(employeeId=1, firstName='Brad', lastName='Pitt',
                             employeeDesignation='Senior Artist')
session.add(new_record)
session.commit()

# Query to update the record
record_to_update = session.query(EmployeeDetails).filter_by(employeeId=1).first()
if record_to_update:
    record_to_update.employeeDesignation = 'Manager'
session.commit()

# Query to delete the record
record_to_delete = session.query(EmployeeDetails).filter_by(employeeId=1).first()
record_to_delete:
    session.delete(record_to_delete)
session.commit()

# Close the session
session.close()

Hibernate in Java

Now let’s talk about the Hibernate, which is extensively used in enterprise application development in Java. In this code, we connect the dedicated autonomous database with Hibernate, using Spring JPA to insert data into the CUSTOMER table using the API, and select data from the same table using another API.

/**
   * Application to connect to Oracle Autonomous Database using SpringJPA and Hibernate 
*/

/** 
 * application.properties file: used to setup Database properties like URL, Username, Password, etc
*/ 

spring.main.banner-mode=off

// Oracle Autonomous Database settings

spring.datasource.url=jdbc:oracle:thin:@javaapp_medium?TNS_ADMIN=<WALLET_PATH>

spring.datasource.username=<USERNAME>

spring.datasource.password=<PASSWORD>

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver


spring.jpa.hibernate.ddl-auto=update

spring.jpa.show-sql=true

spring.jpa.properties.hibernate.format_sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect


//logging

logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n

logging.level.org.hibernate.SQL=debug

logging.level.org.hibernate.type.descriptor.sql=trace

logging.level.=error


/** 
 * DemoApplication.java file: consists of main class
*/ 

@SpringBootApplication

public class DemoApplication {

    public static void main(String[] args) {

        SpringApplication.run(DemoApplication.class, args);

    }

}


/** 
 * CustomerService.java file: consists of services like fetch and save that'll get from or insert/update the database
*/ 

@Service

public class CustomerService{

    @Autowired

    private CustomerRepository customerRepository;


    public Long save(CustomerDto customerDto) {

        Customer customer = new Customer();

        customer.setName(customerDto.getName());

        customer.setAddress(customerDto.getAddress());

        Customer saved = customerRepository.save(customer);

        return saved.getId();

    }

    public Optional<Customer> fetch(Long id) {

        Optional<Customer> optionalCustomer = customerRepository.findById(id);

        return optionalCustomer;

    }

}


@Repository

public interface CustomerRepository extends JpaRepository<Customer, Long>{

}


/** 
 * CustomerController.java file: consists of REST API path to get from or insert/update the database that leverages the services from CustomerService.java 
*/ 

@RestController()

public class CustomerController {

    @Autowired

    private CustomerService customerService;

    @PostMapping(path="/customer")

    ResponseEntity<Long> create(@RequestBody CustomerDto customerDto) {

        Long customerId = customerService.save(customerDto);

        return new ResponseEntity<Long>(customerId, HttpStatus.CREATED);

    }

    @GetMapping(path="/customer/{id}")

    ResponseEntity<Optional<Customer>> fetch(@PathVariable("id") Long id) {

        Optional<Customer> optCustomer = customerService.fetch(id);

        return new ResponseEntity<Optional<Customer>>(optCustomer, HttpStatus.OK);

    }

}


/**
 * Customer.java file: consists of constructor class required to match the database metadata
*/ 

@Entity

@Data

@NoArgsConstructor

@AllArgsConstructor

public class Customer {

    @Id

    @GeneratedValue(strategy = GenerationType.SEQUENCE)

    private Long id;

    private String name;

    private String address;

}

Get started

Start integrating dedicated autonomous databases with your ORM tools, and start building enterprise apps that are secure and highly available, all without developers sacrificing agility. Extracting maximum value out of the most valuable asset that your company has: The data. Get started with the Autonomous Database on Dedicated Infrastructure with Oracle LiveLabs, a way for you to get hands-on experience and build up your technical skills. When you feel ready to explore Oracle Cloud Infrastructure, get unlimited services like Autonomous Database with the Always Free Tier.

Phani Turlapati

Outbound Product Manager - Autonomous Database on Dedicated Infrastructure

Chandrakanth Putha

Cloud Architect


Previous Post

The critical value of OpenSSL and Oracle's contribution

Philip Wilkins | 4 min read

Next Post


Zero-trust interoperability for global defense alliances: 5 ways Oracle technology enables classified data integration

Greg Magram | 8 min read