Install MySQL 8 on Ubuntu/Linux Mint

Ubuntu official software repository provides MySQL 5.x which can be installed by following the article: Install MySQL with phpMyAdmin on Ubuntu. However the latest release of MySQL: 8.x, requires you to manually add the software repository into your system which makes the installation process little tricky. This article walks you through the end-to-end installation process of MySQL 8.

Read More

Presto SQL: Join Algorithms

Presto is a distributed big data SQL engine initially developed by Facebook and later open-sourced and being led by the community. The last article Presto SQL: Types of Joins covers the fundamentals of join operators available in Presto and how they can be used in SQL queries. With that knowledge, you can now learn the internals of Presto and how it executes join operations internally. This article presents how Presto executes join operations and the algorithms used to join tables.

Read More

Presto SQL: Types of Joins

SQL Join is one of the most important and expensive SQL operation and require deep understanding from database engineers to write efficient SQL queries. From database engineers' perspective, understanding how join operation works help them to optimize them for efficient execution. This article, explains the join operations supported in the open source distributed computing engine: Presto SQL. This article is based on now archived prestodb.rocks blog which I referred to learn the Join algorithms of Presto.

Read More

Access MySQL from Android through RESTful Web Service

Finally, I found some time to write about accessing MySQL or any database that you can think about from an Android application. Android offers a built-in database known as SQLite which is supposed to be used for local data storage. For example, if you want to store your recent transactions locally in the device, you have to go with SQLite. I have already written a few articles on how to use SQLite database in Android. However, enterprise mobile applications often depend on client/server databases such as MySQL, Oracle DB or MongoDB. Whatever the database, you have chosen, the way to access them from an Android application is pretty same. In this article, I explain how to access a MySQL database from an Android application written in Kotlin.

I will use Kotlin for all upcoming Android tutorials because it is the future of Android programming. Kotlin is a JVM based language and you can easily convert a Kotlin project into Java and vice versa.

An Android application is not supposed to directly access a database deployed in a server. I do not recommend implementing JDBC connections in an Android application due to the tight coupling introduced between the application and the database. The industrial best practice requires you to implement a web service between the database and the Android application. Having a web service layer reduces the complexity of the Android application and also reduces the dependency on database specific operations. Therefore, the problem of accessing a client/server database like MySQL from an Android application can be defined as the problem of consuming a web service hosted somewhere.

Having said that, as an Android developer, you don't need to care about what is behind the web service. It may be a MySQL database, MongoDB database, a Social Media network or even a Weather Network API. What you need is the API endpoints exposed by the web service. In this article, I only explain how to connect to a web service hosted on your local machine from an Android application. However, the sample web service used in this article provides API endpoints to CRUD operations on a MySQL database. Please follow the RESTful CRUD With Java and MySQL in Minutes article and develop the web service to access a MySQL database. In the following section, you will learn how to access the web service you have created for accessing a database.

Read More

Install MySQL with phpMyAdmin on Ubuntu

There are hundreds of articles on how to install phpMyAdmin on Ubuntu. However, none of them showed me an error-free way of installing phpMyAdmin in my system. This article is for those who want to install phpMyAdmin and MySQL without any errors.

phpMyAdmin depends on Apache Server, PHP, and MySQL. Therefore, I recommend to install them before installing phpMyAdmin.

Read More

Deploy and Upgrade Android Database From External Directory

After seeing the huge response for the Import and Use External Database in Android article, I have realized the importance of deploying Android database from external sources. The library used in the above article allows you to import database only from the assets directory. However, recently one of my readers, asked for a way to import and upgrade the database from SD card. As a solution for his use case, I have developed a new library named "externalsqliteimporter" which allows you to import database either from assets directory or from SD card. This article explains the application of this Android library using a sample application.

The ExternalSQLiteImporter library allows you to build your SQLite database on your desktop computer, and to import and use it in your Android application. This library has two separate ways to maintain your database.

This library is still under development. Deploying and upgrading  the database from an external directory is not secure as it is publicly available for third party applications as well. Use that feature with caution. 

Read More

RESTful CRUD With Java and MySQL in Minutes

I keep receiving so many requests for an article on how to access MySQL database from Android application. Even though Android applications can have direct access to MySQL database server, that is not preferred due to security issues and high complexity. The easy and best solution is developing a REST web service to receive the requests from whatever the client (including Android applications) and execute them on the database connected with the web service.

There are so many articles already available on the Internet about how to create a web service to perform Create, Read, Update & Delete (CRUD) operations on a database and how to connect from an Android client. However, I wondered why most of them are using PHP to develop the web service! I believe developers find PHP a less painful language to develop web services than Java. In this article, I am going to show how to develop a RESTful CRUD Web service with Java in minutes. As a matured language Java has enough frameworks to make your task easy. I guarantee that you will never look back for a different language once you know the tools.

Prerequisite:

• Eclipse IDE for Java EE or any Java IDE with Apache Maven support. (I recommend IntelliJ IDEA)
• MySQL 5.x
• PhpMyAdmin or any other clients to access MySQL database
• Postman or any other REST Clients

Read More

JPA - Hello, World! using Hibernate 5

The article JPA - Hello, World! using Hibernate explains how to use Hibernate v4.x. This tutorial introduces using JPA with the latest Hibernate v5.2.3. Same as the previous article, it also targets beginners who are new to Hibernate and JPA.

You need Java 1.8, Eclipse for Java EE developers and MySQL server in your system in-order to try this tutorial.

Step 1:

Create a database “javahelps” and a table “student” in MySQL.

CREATE DATABASE IF NOT EXISTS javahelps;

CREATE  TABLE javahelps.student ( 
student_id INT NOT NULL ,
student_name VARCHAR(45) NOT NULL ,
student_age INT NOT NULL ,
PRIMARY KEY (student_id) );

Read More

Import Database with Images in Android

The article Import and Use External Database in Android explains, how to distribute an Android application along with the data stored in SQLite database. This tutorial explains, how to store images in such a database and pack it in your application. In other words, we are going to create a database with images from our desktop computer and import it into an Android application.

Step 1:

Create a database "images.db" using your favorite SQLite database application (I use Sqliteman because it allows inserting images into the database. You can use any software or script to insert the images into the database). Create a table 'places' with a two columns 'name' and 'image'. Here the data type of 'name' is TEXT and data type of 'image' is BLOB. Insert the name and image of  some places into the database. In this example, I have inserted the names of the seven wonders of the world along with a compressed (Using TinyPng) picture of them. To download the images used in this project, Click Here.

Read More