android - Connect to mySQL database over LAN without using a webserver

Question

I want to have multiple clients that connect to a server over LAN and access/modify the mySQL database in the server.

How would i go about doing this? Can you guys provide some resources/links that i could research on the topic

Answer 1

To answer your question, you should be able to connect to a mysql database by adding the jdbc driver to your project as a jar file in Android Studio.

Now for a real app that you plan to distributed to thousands of users there are Security issues, Performance issues, and Scalability issues.

Security issues:

1. You expose your database directly to the internet by opening its port to public access for the apps to connect. A web app adds a layer in the middle, keeping the database access inside the intranet.

2. You expose your data directy to the public by providing at least one public account known by everybody (I assume this would be the way to access because managing one account per user wouldn't be realistic). A Web app isolates the user account from the database accounts.

3. By providing access this way, as android mobile devices can be rooted, you are potentially granting anonymous access to your data.

Performance Issues:

1. With a web app in the middle, it is the webapp who manages the connections to the database. This enables sharing connections amongst different users vs. one dedicated connection per user would have if the different devices estable separate connections.

2. For the same reason, you can't take advantage of connection pooling, which saves the overhed of establishing a connection to the database for each incoming request.

Scalability issues:

1. As connections are not shared the number of concurrent users will be bound to the number of connections you can open at the same time to the database.

EDIT 1

I am adding an alternative I thought of which involves using a web application but it is not implemented using a webserver. It is a java NIO framework that runs on its own. The limitations of this solution is you need shell access to the server and java, which is not common in traditional hostings. Checkout Netty.

Answer 2

There are 2 ways how to do perform your task. You can either add the JDBC driver in android studio, or better implement a REST API that connects to your database, and all the android clients can send HTTP requests to the server and the server will add the information for you. Here you can implement the create, update, delete methods. For HTTP requests you can use Retrofit or Volley libraries.

If you want to use JDBC, check out the answer here How to Mysql JDBC Driver to android studio

But the best and most correct solution for this type of problems would be a REST Service

Answer 3

In the long run, you really need a "client" application between "users" and the database. It is usually done via a webserver, plus PHP/Java/VB/.... Yes, it requires you learn yet another language, but that is not something to avoid in a serious application.

The client can help (and hurt, if done adequately) with security. The client can insulate users from database changes, which will eventually happen. The client should 'abstract' the interface to the DB so that the users do not have to be SQL-savvy. Etc.

Answer 4

You have might installed WAMP Server / XAMPP Server for mySQL Database Click on WAMP icon and select Apache, Open "httpd.conf" and find tag starts with

<Directory "c:/wamp/www"> ... </Directory>

and update the code as below

<Directory "c:/wamp/www"> AllowOverride All Require all granted Order Deny,Allow Deny from none Allow from all Allow from 127.0.0.1 Allow from ::1 Allow from localhost </Directory>

Now Create REST APIs in your local server as you might use PHP or whatever, and connect android app with apis with assigned IP Address to your computer in LAN.

Answer 5

It is not one single answer, but a group of answers. Firstly what you need is the concept of data forwarding through introspected tunnels over the network. At the end of the day, your database is always listening on certain port, that is local to your machine, meaning only you can access and modify the contents of the database. For example if you access PHPMyAdmin, you can go the MySQL address on your local machine.

What you need to do is make that access public to the internet, what you need is to broadcast your existence(internet protocol address) to the web. Thus making a public hub, in short, Local-tunnels allows you to easily share a web service on your local development machine without messing with DNS and firewall settings.

By tunneling your local machine to web , anyone with the assigned IP address can access your machine(database) over any connection,not just LAN or WI-FI. There are many options to choose from, There is ngrok,which exposes a local server behind a NAT or firewall to the internet.

Features are,

Don’t constantly redeploy your in-progress work to get feedback from clients. ngrok creates a secure public URL (https://yourapp.ngrok.io) to a local webserver on your machine. Iterate quickly with immediate feedback without interrupting flow.

Test mobile apps against a development backend running on your machine. Point ngrok at your local dev server and then configure your app to use the ngrok URL. It won't change, even when you change networks.

Building web hook integrations can be a pain: it requires a public address and a lot of set up to trigger hooks. Save yourself time and frustration with ngrok. Inspect the HTTP traffic flowing over your tunnel. Then, replay web-hook requests with one click to iterate quickly while staying in context.

Own your data. Host personal cloud services on your own private network. Run web-mail, file syncing, and more securely on your hardware with full end-to-end encryption.

Its really great, however there is a side note, because this procedure opens up your local machine and renders it IP accessible on the internet, many different security challenges arise, so it is only recommended for testing purposes with none sensitive data.

Hope this helps:)