How to store image in MS SQL Server database and retrieve it from your Android App?

This page shows steps to create an Android App which connects to MS SQL Server Database.

It uses the MSSQL database to store the images in bytes array format. The respective data type in database server is VARBINARY(MAX).

It uses a couple of images saved in the emulator phone to demonstrate this App.

It reads those image files and then converts them to Bitmap and then to bytes array to store (insert) it in the database.

In the fetch method, once it receives the value of the VARBINARY data column from the database, it converts it back to the bytes array. Then it converts the bytes array back to bitmap. It uses an image view widget to display the bitmap image on the App’s layout.

I hope you like this video. For any questions, suggestions or appreciation please contact us at: https://programmerworld.co/contact/ or email at: programmerworld1990@gmail.com

Source Code:

package com.programmerworld.imagemssqlapp;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;

import android.Manifest;
import android.content.pm.PackageManager;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Bundle;
import android.os.Environment;
import android.os.StrictMode;
import android.view.View;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.TextView;

import java.io.ByteArrayOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MainActivity extends AppCompatActivity {

    private static String ip = "192.168.43.66";
    private static String port = "1433";
    private static String Classes = "net.sourceforge.jtds.jdbc.Driver";
    private static String database = "testDatabase";
    private static String username = "test";
    private static String password = "test";
    private static String url = "jdbc:jtds:sqlserver://"+ip+":"+port+"/"+database;

    private Connection connection = null;

    private TextView textView;
    private EditText editTextIndex;
    private EditText editTextFileName;
    private ImageView imageView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        ActivityCompat.requestPermissions(this, new String[]{Manifest.permission.READ_EXTERNAL_STORAGE,
        Manifest.permission.WRITE_EXTERNAL_STORAGE}, PackageManager.PERMISSION_GRANTED);

        ActivityCompat.requestPermissions(this,new String[]{Manifest.permission.INTERNET}, PackageManager.PERMISSION_GRANTED);

        textView = findViewById(R.id.textViewStatus);
        editTextIndex = findViewById(R.id.editTextNumber);
        editTextFileName = findViewById(R.id.editTextFileName);
        imageView = findViewById(R.id.imageView);

        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        try {
            Class.forName(Classes);
            connection = DriverManager.getConnection(url, username,password);
            textView.setText("SUCCESS");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            textView.setText("ERROR");
        } catch (SQLException e) {
            e.printStackTrace();
            textView.setText("FAILURE");
        }
    }

    public void buttonInsert(View view){
        String stringFilePath = Environment.getExternalStorageDirectory().getPath()+"/Download/"+
                editTextFileName.getText().toString()+".jpeg";

        Bitmap bitmap = BitmapFactory.decodeFile(stringFilePath);

        imageView.setImageBitmap(bitmap);

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        bitmap.compress(Bitmap.CompressFormat.PNG, 0, byteArrayOutputStream);
        byte[] bytesImage = byteArrayOutputStream.toByteArray();

        try {
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO TEST_TABLE (C2, Image) VALUES (?,?)");
            preparedStatement.setInt(1, Integer.valueOf(editTextIndex.getText().toString()));
            preparedStatement.setBytes(2, bytesImage);

            preparedStatement.execute();
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }

    public void buttonFetch(View view){
        try {
            Statement statement = connection.createStatement();

            ResultSet resultSet = statement.executeQuery("SELECT Image from TEST_TABLE where C2="+editTextIndex.getText().toString()+";");
            resultSet.next();
            byte[] bytesImageDB = resultSet.getBytes(1);
            Bitmap bitmapImageDB = BitmapFactory.decodeByteArray(bytesImageDB, 0, bytesImageDB.length);

            imageView.setImageBitmap(bitmapImageDB);
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
}

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.programmerworld.imagemssqlapp">

    <uses-permission android:name="android.permission.INTERNET"/>
    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/Theme.ImageMSSQLApp">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

plugins {
    id 'com.android.application'
}

android {
    compileSdkVersion 30
    buildToolsVersion "29.0.3"

    defaultConfig {
        applicationId "com.programmerworld.imagemssqlapp"
        minSdkVersion 26
        targetSdkVersion 30
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }
    compileOptions {
        sourceCompatibility JavaVersion.VERSION_1_8
        targetCompatibility JavaVersion.VERSION_1_8
    }
}

dependencies {

    implementation 'androidx.appcompat:appcompat:1.2.0'
    implementation 'com.google.android.material:material:1.2.1'
    implementation 'androidx.constraintlayout:constraintlayout:2.0.4'
    testImplementation 'junit:junit:4.+'
    androidTestImplementation 'androidx.test.ext:junit:1.1.2'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.3.0'

    implementation 'net.sourceforge.jtds:jtds:1.3.1'
}

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:id="@+id/textViewStatus"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Hello World!"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintHorizontal_bias="0.486"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.352" />

    <EditText
        android:id="@+id/editTextNumber"
        android:layout_width="130dp"
        android:layout_height="42dp"
        android:layout_marginStart="38dp"
        android:layout_marginTop="53dp"
        android:ems="10"
        android:hint="@string/index"
        android:inputType="number"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        android:importantForAutofill="no" />

    <EditText
        android:id="@+id/editTextFileName"
        android:layout_width="170dp"
        android:layout_height="46dp"
        android:layout_marginStart="53dp"
        android:layout_marginTop="53dp"
        android:ems="10"
        android:hint="@string/file_name"
        android:inputType="textPersonName"
        app:layout_constraintStart_toEndOf="@+id/editTextNumber"
        app:layout_constraintTop_toTopOf="parent"
        android:autofillHints="" />

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="239dp"
        android:layout_marginTop="31dp"
        android:onClick="buttonInsert"
        android:text="@string/insert"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextFileName" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="63dp"
        android:layout_marginTop="39dp"
        android:onClick="buttonFetch"
        android:text="@string/fetch"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editTextNumber" />

    <ImageView
        android:id="@+id/imageView"
        android:layout_width="210dp"
        android:layout_height="196dp"
        android:layout_marginStart="100dp"
        android:layout_marginTop="98dp"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/textViewStatus"
        app:srcCompat="@drawable/ic_launcher_background"
        android:contentDescription="@string/todo" />

</androidx.constraintlayout.widget.ConstraintLayout>

Screenshots: