Upload, download files as mysql blobs with Spring

Spring boot app based on Spring MVC, Spring Data JPA, MySQL and Liquibase which can store (download) file content in MySQL database as blobs and upload them and their metadata back

app-0-content-as-byte-array

This app stores data as a kotlin byte-array, but mysql longblob

@Entity
@Table(name = "report_items")
data class ReportItem(

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(nullable = false, updatable = false)
    val id: Long? = null,

    // @Lob // Use columnDefinition if you want string
    // @Column(nullable = false, updatable = true, columnDefinition="LONGBLOB NOT NULL")
    // val content: String = "",

    @Lob // No needs to use columnDefinition when using byte array
    @Suppress("ArrayInDataClass")
    @Column(nullable = false, updatable = true)
    val content: ByteArray = ByteArray(0),
    
    // skipped...
)

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
docker run --rm --name mysql --platform linux/x86_64 \
           --health-cmd='mysqladmin ping -h 127.0.0.1 -u $MYSQL_USER --password=$MYSQL_PASSWORD || exit 1' \
           --health-start-period=1s --health-retries=1111 --health-interval=1s --health-timeout=5s \
           -e MYSQL_USER=user -e MYSQL_PASSWORD=password \
           -e MYSQL_DATABASE=database -e MYSQL_ROOT_PASSWORD=password \
           -p 3306:3306 \
           -d mysql:8.0.24
while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
docker logs -f mysql &

./mvnw -f apps/app-0-content-as-byte-array compile spring-boot:start

content=`cat README.md`
http :8000 name=README.md content=$content
http :8000

./mvnw -f apps/app-0-content-as-byte-array spring-boot:stop
docker stop mysql

app-1-content-as-string

This app stores data as a kotlin text (string), but mysql longblob

@Entity
@Table(name = "report_items")
data class ReportItem(

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(nullable = false, updatable = false)
    val id: Long? = null,

    @Lob // Use columnDefinition if you want to serialize mysql blob as kotlin string
    @Column(nullable = false, updatable = true, columnDefinition="LONGBLOB NOT NULL")
    val content: String = "",

    // @Lob // No needs to use columnDefinition with bytes
    // @Suppress("ArrayInDataClass")
    // @Column(nullable = false, updatable = true)
    // val content: ByteArray = ByteArray(0),

    // skipped..
)

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &
while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'

./mvnw -f apps/app-1-content-as-string compile spring-boot:start

http :8001
content=`cat README.md` ; http :8001 name=README.md content=$content
http :8001

./mvnw -f apps/app-1-content-as-string spring-boot:stop
docker rm -f -v `docker ps -aq`

app-2-upload-file

This app implements upload functionality

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &
while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'

./mvnw -f apps/app-2-upload-file compile spring-boot:start

content=`cat README.md`
http -f post :8002/upload file@README.md
http -f post :8002/upload file@$PWD/README.md
http -f post :8002/upload file@`pwd`/README.md
http     get :8002

./mvnw -f apps/app-2-upload-file spring-boot:stop
docker rm -f -v `docker ps -aq`

app-3-download-file

This app implements download functionality

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &
while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'

./mvnw -f apps/app-3-download-file compile spring-boot:start

http --form --multipart --boundary=xoxo post :8003/upload file@README.md
http -f                                 post :8003/upload file@$PWD/pom.xml
http                                     get :8003

cd /tmp ; http --download get :8003/download\?id=2 ; ls -lah . | grep pom.xml
cd /tmp ; http -f         get :8003/download\?id=1 > README.md ; ls -lah . | grep README.md

./mvnw -f apps/app-3-download-file spring-boot:stop
docker rm -f -v `docker ps -aq`

app-4-binary-files

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &
while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'

./mvnw -f apps/app-4-binary-files compile spring-boot:start

http --form --multipart --boundary=xoxo post :8004/upload file@README.md
http -f                                 post :8004/upload file@$PWD/pom.xml
http                                     get :8004

cd /tmp ; http --download get :8004/download\?id=2 ; ls -lah . | grep pom.xml
cd /tmp ; http -f         get :8004/download\?id=1 > README.md ; ls -lah . | grep README.md

./mvnw -f apps/app-4-binary-files spring-boot:stop
docker rm -f -v `docker ps -aq`

app

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &
while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'

./mvnw -f apps/app compile spring-boot:start

http --form --multipart --boundary=xoxo post :8080/upload file@README.md
http -f                                 post :8080/upload file@$PWD/pom.xml
http                                     get :8080

cd /tmp ; http --download get :8080/download\?id=2 ; ls -lah . | grep pom.xml
cd /tmp ; http -f         get :8080/download\?id=1 > README.md ; ls -lah . | grep README.md

./mvnw -f apps/app spring-boot:stop
docker rm -f -v `docker ps -aq`

reactive-app-0-content-as-byte-array

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &

while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
./mvnw -f apps/reactive-app-0-content-as-byte-array clean compile \
  liquibase:update \
    -Dliquibase.url='jdbc:mysql://127.0.0.1:3306/database' \
    -Dliquibase.username=user \
    -Dliquibase.password=password

./mvnw -f apps/reactive-app-0-content-as-byte-array compile spring-boot:start

http get :8000
content=`cat README.md` ; http post :8000 name=README.md content=$content
http get :8000

./mvnw -f apps/reactive-app-0-content-as-byte-array spring-boot:stop
docker rm -f -v `docker ps -aq`

reactive-app-1-content-as-string

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &

while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
./mvnw -f apps/reactive-app-1-content-as-string clean compile \
  liquibase:update \
    -Dliquibase.url='jdbc:mysql://127.0.0.1:3306/database' \
    -Dliquibase.username=user \
    -Dliquibase.password=password

./mvnw -f apps/reactive-app-1-content-as-string compile spring-boot:start

http get :8001
content=`cat README.md` ; http post :8001 name=README.md content=$content
http get :8001

./mvnw -f apps/reactive-app-1-content-as-string spring-boot:stop
docker rm -f -v `docker ps -aq`

reactive-app-2-upload-file

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &

while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
./mvnw -f apps/reactive-app-2-upload-file clean compile \
  liquibase:update \
    -Dliquibase.url='jdbc:mysql://127.0.0.1:3306/database' \
    -Dliquibase.username=user \
    -Dliquibase.password=password

./mvnw -f apps/reactive-app-2-upload-file compile spring-boot:start

http get :8002
http --form --multipart --boundary=xoxo post :8002/upload file@README.md
http get :8002

./mvnw -f apps/reactive-app-2-upload-file spring-boot:stop
docker rm -f -v `docker ps -aq`

reactive-app-3-download-file

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &

while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
./mvnw -f apps/reactive-app-3-download-file clean compile \
  liquibase:update \
    -Dliquibase.url='jdbc:mysql://127.0.0.1:3306/database' \
    -Dliquibase.username=user \
    -Dliquibase.password=password

./mvnw -f apps/reactive-app-3-download-file compile spring-boot:start

http get :8003
http --form --multipart --boundary=xoxo post :8003/upload file@README.md

mkdir target
id=$(http get :8003 | jq '.[0].id')
http get :8080/download/$id > target/index.md

cat target/index.md 

./mvnw -f apps/reactive-app-3-download-file spring-boot:stop
docker rm -f -v `docker ps -aq`

reactive-app-4-binary-files

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &

while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
./mvnw -f apps/reactive-app clean compile \
  liquibase:update \
    -Dliquibase.url='jdbc:mysql://127.0.0.1:3306/database' \
    -Dliquibase.username=user \
    -Dliquibase.password=password

./mvnw -f apps/reactive-app-4-binary-files compile spring-boot:start

http get :8004
http --form --multipart --boundary=xoxo post :8004/upload file@README.md

mkdir target
id=$(http get :8004 | jq '.[0].id')
http get :8004/download/$id > target/index.md

cat target/index.md 

./mvnw -f apps/reactive-app-4-binary-files spring-boot:stop
docker rm -f -v `docker ps -aq`

reactive-app

test and build

./mvnw

run and verify

if [[ "" != `docker ps -aq` ]] ; then docker rm -f -v `docker ps -aq` ; fi
./mvnw -f docker -P down ; ./mvnw -f docker -P up ; ./mvnw -f docker -P logs &

while [[ $(docker ps -n 1 -q -f health=healthy -f status=running | wc -l) -lt 1 ]] ; do sleep 3 ; echo -n '.' ; done ; sleep 15; echo 'MySQL is ready.'
./mvnw -f apps/reactive-app clean compile \
  liquibase:update \
    -Dliquibase.url='jdbc:mysql://127.0.0.1:3306/database' \
    -Dliquibase.username=user \
    -Dliquibase.password=password

./mvnw -f apps/reactive-app compile spring-boot:start

http get :8080
http --form --multipart --boundary=xoxo post :8080/upload file@README.md

mkdir target
id=$(http get :8080 | jq '.[0].id')
http get :8080/download/$id > target/index.md

cat target/index.md 

./mvnw -f apps/reactive-app spring-boot:stop
docker rm -f -v `docker ps -aq`

RTFM

Upload files with IDEA HTTP requests REST client
HTTPie files upload / download
Official Apache Maven documentation
Spring Boot Maven Plugin Reference Guide
Create an OCI image
Spring Data JPA
Spring Web
Spring Configuration Processor
Accessing Data with JPA
Building a RESTful Web Service
Serving Web Content with Spring MVC
Building REST services with Spring
Accessing data with MySQL

Files

README.md

Latest commit

History

README.md

File metadata and controls

Upload, download files as mysql blobs with Spring

app-0-content-as-byte-array

test and build

run and verify

app-1-content-as-string

test and build

run and verify

app-2-upload-file

test and build

run and verify

app-3-download-file

test and build

run and verify

app-4-binary-files

test and build

run and verify

app

test and build

run and verify

reactive-app-0-content-as-byte-array

test and build

run and verify

reactive-app-1-content-as-string

test and build

run and verify

reactive-app-2-upload-file

test and build

run and verify

reactive-app-3-download-file

test and build

run and verify

reactive-app-4-binary-files

test and build

run and verify

reactive-app

test and build

run and verify

RTFM