java - Connect Azue SQL Database in Lnux Android Studio

Question

I want to develop Android Application which would connect to Microsoft Azure SQL Database in Linux (Version 14.04) Android Studio. Iam using latest version of Android Studio 4.1.2. I tried some java code for Database connection. But I am not able to database.

My problem is How To connect to SQL Database and How to fetch data from SQL databse to my Android Application. My data is stored in Azure SQL Database -> Stream Analytics (preview).

Java code :

 String Connect_String = "jdbc:sqlserver://sqlserver.database.windows.net:1433;database=iwave_db_1;user=username@sqlserver;password=*******;" +
                    "encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
       
 String Username = "username@sqlserver";
 String Password = "*******";
    try {
        System.out.println("Connecting to the Database");
        //Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        //Class.forName("com.sourceforge.jtds.jdbc.Driver").newInstance();

        Connection connection = DriverManager.getConnection(Connect_String, Username, Password);
        if (connection != null) {
            System.out.println("Done ........");
            System.out.println("Database connection success");
        }

Getting error in Connection connection = DriverManager.getConnection(Connect_String, Username, Password);this line. Application is crashing, and i not able to connect to SQL database.

Error Message :

E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.iwavesys.cloudazure, PID: 6864
    android.os.NetworkOnMainThreadException
        at android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode.java:1450)
        at java.net.Inet6AddressImpl.lookupHostByName(Inet6AddressImpl.java:102)
        at java.net.Inet6AddressImpl.lookupAllHostAddr(Inet6AddressImpl.java:90)
        at java.net.InetAddress.getLocalHost(InetAddress.java:851)
        at com.microsoft.sqlserver.jdbc.Util.lookupHostName(Util.java:652)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2690)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861)
        at java.sql.DriverManager.getConnection(DriverManager.java:569)
        at java.sql.DriverManager.getConnection(DriverManager.java:219)
        at com.iwavesys.cloudazure.ToDoActivity.Azure_Connection(ToDoActivity.java:222)
        at com.iwavesys.cloudazure.ToDoActivity.access$100(ToDoActivity.java:43)
        at com.iwavesys.cloudazure.ToDoActivity$2.onClick(ToDoActivity.java:154)
        at android.view.View.performClick(View.java:6294)
        at android.view.View$PerformClick.run(View.java:24770)
        at android.os.Handler.handleCallback(Handler.java:790)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:164)
        at android.app.ActivityThread.main(ActivityThread.java:6494)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)

My linking all the library file in my gradle file . build.Gradle :

apply plugin: 'com.android.application'
android {
    compileSdkVersion 30
    buildToolsVersion "30.0.2"

    defaultConfig {
        applicationId "com.iwavesys.cloudazure"
        minSdkVersion 19
        targetSdkVersion 30
        multiDexEnabled true
        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 fileTree(dir: "libs", include: ["*.jar"])
    implementation 'androidx.appcompat:appcompat:1.2.0'
    implementation 'androidx.constraintlayout:constraintlayout:2.0.4'
    implementation 'org.conscrypt:conscrypt-android:2.2.1'
    implementation 'org.apache.httpcomponents:httpclient-android:4.3.5'
    //implementation 'net.sourceforge.jtds:jtds:1.3.0'
    testImplementation 'junit:junit:4.12'
    androidTestImplementation 'androidx.test.ext:junit:1.1.2'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.3.0'
    implementation 'com.microsoft.azure:azure-mobile-android:3.1.0'
    implementation 'com.google.code.gson:gson:2.8.5'
    implementation 'com.google.guava:guava:24.1-jre'
    implementation 'com.android.support:support-v4:28.0.0'
    implementation 'com.microsoft.azure.android:azure-storage-android:2.0.0@aar'
}

I am using following version of .jar files for connection :

1. jtds-1.3.1.jar
2. mssql-jdbc-8.4.1.jre8.jar
3. mysql-connector-java-8.0.22.jar
4. Firewall setting in azure cloud check the below link. https://i.stack.imgur.com/q37Sj.png

Where i am missing?? My code is correct or not ?? Anyone knows please help me. Thank you in Advance.

question from:https://stackoverflow.com/questions/65879738/connect-azue-sql-database-in-lnux-android-studio

Answer 1

Because of the flavor of Java in Android, you would need jtds driver. Update your connection string to use jtds like below. Note the changes

• add "jtds" before "sqlserver"
• "DatabaseName" instead of "database"

jdbc:jtds:sqlserver://sqlserver.database.windows.net:1433;DatabaseName=iwave_db_1;user=username@sqlserver;password=*******;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Also, make sure you enable required permissions in manifest to allow connection to internet. And also check firewall setting of your SQL Azure instance to allow traffic from the required IPs.

Ref: https://www.youtube.com/watch?v=WJBs0zKGqH0

NOTE: It's not a good design to directly connect to db from the mobile app. I would rather have some server side API service in between.