Part 3. Interacting with databases with Flask and Spring Boot
Tags: webdev, beginners, programming, python
Published: 2023-08-08 04:18:29

Both Flask and Spring Boot have ways to interact with databases. Here's how they compare:

#FLASK:
1. SQLAlchemy: 
Flask is commonly paired with SQLAlchemy, an Object Relational Mapper(ORM) for Python

#app.py file:

```
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db'
db = SQLAlchemy(app)
```

#Models.py file:

```
class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email
        }

    def __repr__(self):
        return f'<User {self.username}>'
```

#CRUD Operations:


```
#Create (post request)

new_user = User(username='zina', email='zina@example.com')
db.session.add(new_user)
db.session.commit()

#Read (get request)

users = User.query.all()

#Update (patch (update specific fields) /put (replace entire resource with data) request)

user = User.query.get(1)
user.username = 'zina_in_tech'
db.session.commit()

#Delete

db.session.delete(user)
db.session.commit()

```


#SPRING BOOT
1. Spring Data JPA
Spring Boot can be integrated with the Jakarta Persistence API (JPA) and Hibernate using Spring Data JPA and this provides a way to interact with databases using ORM.

#Setup: 
In the build.gradle add dependencies for Spring Data JPA and your database (e.g H2, MySQL)

```
dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'com.github.javafaker:javafaker:1.0.2'
	runtimeOnly 'com.mysql:mysql-connector-j'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	
}
```

#Model:

```
import com.fasterxml.jackson.databind.annotation.JsonSerialize;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;

@Entity
@Table(name = "users")
@JsonSerialize(using = UserSerializer.class) //UserSerializer class not provided here
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "username", unique = true, nullable = false)
    private String username;

    @Column(name = "email")
    private String email;

    public User(Integer id, String username, String email) {
        this.id = id;
        this.username = username;
        this.email = email;
    }

    public User() {

    }

    public Integer getid() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getEmail() {
        return email;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String toString() {
        return "id:" + this.id + "username:" + this.username + "email:" + this.email;
    }
```

#Repository:
Spring Data JPA provides the concept of Repositories, which are interfaces that allow for easy CRUD operations without writing the actual SQL queries

```
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {

}

```

#CRUD Operations:

```
@Autowired
private UserRepository userRepository;

// Create (post request)
User user = new User("john", "john@example.com");
userRepository.save(user);

// Read (get request)
List<User> users = userRepository.findAll();

// Update (patch (update specific fields) /put (replace entire resource with data) request)

user.setUsername("john_doe");
userRepository.save(user);

// Delete
userRepository.delete(user);

```

Both frameworks allow developers to focus on the application logic rather than the intricacies of SQL and database management.