Connect android and mysql

Как работать с MySQL в Android? Часть 1

На днях мне пришлось подключится к внешней БД MySQL в Android приложении, которое бы использовало ресурсы готового сервиса, так вот в данном уроке я покажу как я это сделал.

В данном уроке мы с вами сделаем простое Android-приложения, который будет вызывать PHP скрипт для выполнения основных CRUD (Create, Read, Update, Delete) операций.

Android приложение будет вызывать PHP скрипт, который будет подключается к базе данных MySQL и выполнить какие то операции.

Что вам потребуется?

1) WEB-сервер для PHP. Будем использовать сборку DenWer.

2) MySQL база данных.

3) Android Emulator.

Шаг 1. Установка WEB-сервера.

Так как мы будем работать со сборкой Denwer, то для начала скачиваем её тут http://www.denwer.ru/

После того как вы его установили и проверили, что он работает, переходим к следующему шагу.

Шаг 2. Создание БД

Так как мы подняли WebServer Denwer в нем уже вшита MySQL база данных, давайте создадим базу и таблицу (products) в ней.

Создание таблиц в БД:

В конце видео создания БД я не зря показал локальный IP, так как я использую виртуальную ОС для удобства. В вашем случае вы будите обращаться к localhost или же 127.0.0.1 .

Шаг 3. Подключение к MySQL базе с помощью PHP

Давайте создадим PHP класс, который будет отвечать за подключение к БД. Основная цель этого класса – открытие и закрытие соединения с БД.

Но, для того что бы подключатся к БД нам нужно где то хранить требуемые параметры для подключения к БД для этого создадим [C:\WebServers\home\devserver.com\www\db_config.php] со следующим содержимым:

А теперь создадим в той же директории файл [C:\WebServers\home\devserver.com\www\db_connect.php] со следующим содержимым:

Шаг 4. Базовые CRUD операции в PHP

В этом уроке я покрываю основные CRUD (Create, Read, Update, Delete) операций для MySQL с помощью PHP.

Запись строки в БД

Создадим в нашем PHP проекте новый файл create_product.php который будет обеспечивать возможность записи продукта в БД.

Для приведенного выше кода JSON ответ будет примерно таким:

Когда POST параметр(ы) отсутствует:

Когда продукт успешно добавлен:

Когда ошибка при добавлении данных в БД:

Чтение строки с БД

Создадим новый PHP файл в нашем проекте и назовем его get_product_details.php со следующим содержанием:

Ответ JSON для вышеупомянутого файла будет следующим.

При успешном получении продукта:

Когда продукт не найден:

Получение всех продуктов с БД

Для того чтобы вывести весь список продуктов на устройство нам нужно получить все продукты с БД.

Создадим get_all_products.php со следующим содержимым:

JSON ответ выше приведенного кода будет таким:

Когда продукт не найден:

Обновление продукта в БД

Создайте файл с именем update_product.php со следующим содержимым:

Ответ JSON выше кода, когда продукт успешно обновлены будет следующим:

Удаление продукта с БД

Последняя операция удаления из базы данных. Создайте новый файл назовите delete_product.php и вставьте следующий код:

Когда продукт удачно будет удален то JSON ответ будет таким:

Когда продукт не найден, то таким:

Теперь наконец-то мы можем приступить к программированию на Android. То что мы описали выше на PHP будем использовать как API слой. И наше приложение будет общаться с MySQL сервером по средством JSON формата и GET, POST запросов.

Источник

Connect android and mysql

This is (as far as we are aware) the first and only native MySQL connector for Android. It connects directly to your database instead of relying on some sort of web service to act as a middleware between Android and the MySQL Server.

Using a webservice is still the recommended way of connecting to your database as it ensures only clients that are supposed to are connecting to your database and avoids the requirement of needing to expose your MySQL server to the Internet.

However, if you want, or have a need to connect directly to your database, then this is the library you need. This is a fairly basic MySQL connector compared to the official connectors that are available on other platforms. Therefore, below is a list of the known limitations of the library:

• Doesn’t support compression, if the MySQL server reports that it supports compression, the connector will turn it off
• Doesn’t support prepared statements
• Only supports UTF8 or Latin 1 encodings
• If authentication is required, then only mysql_native_password is supported

The library has been tested on the following MySQL Servers

We’ve so far only tested on the the highest minor version of each MySQL major version. You’ll notice that this doesn’t include MySQL 8.0. This is due to MySQL 8 changing the default authentication mechanism, this is something we plan on adding in the future. We haven’t yet tested with MariaDB however, the equivalent MeriaDB and MySQL version should be compatible and therefore should work in the same way.

Adding the library to your project couldn’t be simpler. Add the following to your apps build.gradle

The TAG above will be the tagged version number of the library.

Using the Library

Due to the way Android works, using the library is a little different compared to using the official connector on other platforms. This is due to to Android completely restricting any network activity on the main thread. Obviously you shouldn’t do any network activity on the main thread on any platform, but most platforms don’t stop you if you want to, however, Android will throw an exception if any network activity is done on the main thread. Therefore for each action you wish to take, such as connecting, switching databases, performing a query, the relevant method or constructor will take an interface which will get called once the action has completed which you can then handle.

Connecting to a MySQL Server

To connect to a MySQL server you first need to create the MySQL Connection object. You can do this as follows:

In the above example, is an optional parameter. By setting this, when the connection is established, the database name will be the default database used. The IConnectionInterface parameter handles connection specific events, such as successfully connected or exception handlers.

For example, when creating a new IConnectionInterface, you will need to provide the following methods: actionCompleted This means that the connection was successfully established and authenticated and the connection is ready for use

handleInvalidSQLException You shouldn’t really ever get this, but you might if you are connecting to a MySQL server that either is configured slightly differently as to what has bee tested or isn’t compatible with the connector, and has caused the connector to receive a network packet from the MySQL server that the connector wasn’t expecting.

handleMySQLException This will happen if a generic MySQL exception occurs within the connector

handleIOException This is used if an internal socket error occurs within the connector, for example, if server aborted the connection but the library didn’t realise and tried to perform an operation on the MySQL socket which is now closed

handleMySQLConnException This will be an exception related to a connection error, such as authentication failure

handleException This is a generic exception handler if any of the above doesn’t match the exception

That’s it, you have successfully connected, now we can execute some queries. Remember though, keep your connection object available in your class as this will be required in order to run queries on the DB.

You can change the default database to use in your established MySQL Connection. To do this, do not execute a standard USE DATABASE query as it won’t work.

You need to use your connection object and call the method switchDatabase(db, new IConnectionInterface()) . db being a String of your database name, and again, pass in the IConnectionInterface. If it switched successfully you will receive a call back to the actionCompleted method.

Execute Statement (Such as INSERT or UPDATE where no resultset is returned)

First of all you need to create a statement object from your connection object. You can do this using the following code snippet

Then in order to execute your statement you then do the following

query is your statement that you want to execute such as an INSERT or UPDATE statement. Again the second parameter is the IConnectionInterface and if the statement execute successfully, then actionCompleted will be called.

Execute Query (Such as SELECT)

The execute query function allows you to execute a query such as SELECT or DESCRIBE, basically any query which return a result set. Same with executing a statement, you need to create the statement object from your connection object. This can be done using the following code snippet:

Then you need to call the executeQuery withn your statement object, but this time passing in a new IResultInterface as shown below:

The IResultInterface is pretty similar to the IConnectionInterface, you’ll have the same exception handlers, the main difference that if the query executes successfully, you’ll receive executionComplete which will have a parameter to the result set.

Processing a result set

When you execute a query such as SELECT or DESCRIBE, you will get a call back which will provide a result set object. This result set object contains all the information about what was returned such as the columns and the rows.

Get total number of rows returned

To get the total number of rows you can call resultset.getNumRows(); .

Get column definitions

The column definitions are stored in a List . You can get this using the following code snipper:

You can then loop over the list to get ColumnDefinition for each column returned in the result set. Within the column definition class, you can use the following methods: getDatabase The database name where the column was retrieved from

getTable The table name where the column was retrieved from

getColumnName The name of the column

getColumnType Returns an enum of type ColumnType

isPrimaryKey Returns a boolean as to whether or not the column is a primary key

Iterating through each row

You can iterate through each row, you first need to create an empty MySQLRow variable that can be used to be set within a while loop to get each row. You can call getNextRow() on the result set to get a MySQLRow. Once there are no rows left, null is returned. The following code snippet provides an example:

Once you have your MySQLRow you can then call the following methods to return the value of the field. Each of the following methods, take a String parameter which is the column name that should be retrieved.

• getString(String column)
• getInt(String column)
• getFloat(String column)
• getDouble(String column)
• getBlob(String column) (Returns a byte[] array)

When sending dynamic paraters in your MySQL query, the string should be escaped to avoid SQL injection attacks. This can be done by using the following code snippet:

Currently the connection object can’t be passed between Android activities, so if you do need to create a new Activity and perform database action, you should close the DB connection in your current activity, pass the connection details to your new activity, and then create a new connection object in your new activity.

Also, to avoid leaving the DB connection open for no reason, in your activities onPause and onDestroy methods you should close the DB connection and then create a new instance to restablish the connection in the onCreate and/or onResume

You can close the DB by calling connection.close() .

Below you will find some examples on some of the common actions you might do with the MySQL Connector.

Each of the below examples creates a Connection object called mysqlConnection. This can be done using the following:

Switching Default Database

When you need to change the default database, (if a default database is set you do not need to prepend the database with the table name). You cannot use the the statement USE new_database , you have to use the method switchDatabase within the connection object as follows:

Executing statement (SQL statements that do not return a result set, e.g. INSERT, UPDATE, DELETE, TRUNCATE, CREATE, DROP ALTER)

Executing query (Statement which returns a MySQL Result Set, e.g. SELECT, SHOW, DESCRIBE)

Performing a query to return a result set is pretty much the same as above, the only difference is that instead of passing an IConnectionInterface , you instead pass an IResultInterface() and the executionComplete method provides you with a ResultSet object

Cannot Resolve Symbol Error

If you have added the library and the gradle sync works successfully but you are getting errors like cannot resolve symbol make sure that the imports at the top of the class file have been referenced.

This can be done in one of two ways:

1. Put the cursor somewhere in the area of code which is showing red and hit Ctrl + Alt + Enter. Android Studio will provide tips on how to fix — one of them being to import the class. Select import and the error should go away.
2. You can add the following two lines:

which will import all available classes from the library. However, this tends to be bad practice as only what’s needed from the library by your class should be imported. You can get round this by adding the two lines above, then when your class is finished, you can then do Ctrl + Alt + O which will organise your imports into what ones are required instead of a wildcard import.

javax.net.ssl.SSLHandshakeException: Handshake failed

If you see this error then connect to your DB via command line and run the following query:

If it shows TLSv1 then this won’t be supported by the Android MySQL Connector library. TLS 1.0 is a deprecated version of TLS and Java and/or Android no longer supports this TLS version.

If MySQL supports it, you can add tls_version=TLSv1.1 or tls_version=TLSv1.2 (Check your mysql version documentation to determine the supported TLS) to your /etc/main.cf file and restart MySQL. This needs to be added under the mysqld section of the config file.

If you are using Amazon RDS then you will need to use at least MySQL 5.7.16. Previous version of Amazon RDS for MySQL only support TLS 1.0 so the library won’t be able to connect.

Источник