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:

Download Folder containing Images

Insert

Fetch

Fetch

Leave a Reply

%d bloggers like this: