Tutorial #75: Connect to MySQL database using Qt 5 framework

Hi everyone!

Just as any other cross platform framework Qt helps to access a database, create and open one or more database connections. Qt supports a list of databases thereby providing access to the API exposed by the underlying DBMS (Database Management System). Once connected, developers can query the database using the QSqlQuery class. It can be used to execute DML (data manipulation language) statements such as SELECT, INSERT etc as well as DDL (data definition language) statements such as CREATE TABLE.

In this tutorial, we will learn how to connect to a MySQL database using the Qt 5 framework. We will also write a simple query to obtain data from our existing table.

Pre-requisites: Qt Creator, MySQL database server, MinGW compiler (Win-x64)

Step 1: Create Qt project

Launch Qt creator and create a new console application project called TestMySQLExample. Choose the default Desktop kit.

Step 2: Add Qt code

Next, you need to make changes to your main.cpp file by adding the following code. Make sure the MySQL database on your machine is up and running.

main.cpp

#include <QCoreApplication>
#include <QApplication>
#include <QtSql/QSql>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlDriver>
#include <QtSql/QSqlQuery>
#include <QDebug>

bool createConnection();

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    if (!createConnection()){

        qDebug() << "Not connected!";
        return 1;
    }
    else{

        qDebug() << "Connected!";

        QSqlQuery query;
        query.exec("SELECT name FROM student");

        while (query.next()) {
            QString name = query.value(0).toString();
            qDebug() << "name:" << name;
        }

        return 0;
    }

    return app.exec();
}

bool createConnection(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("testdb");
    db.setUserName("root");
    db.setPassword("rootpassword");
    if (!db.open()) {
        qDebug() << "Database error occurred";
        return false;
    }
    return true;
}

Important: Before you compile and run the above file, you need to ensure that the libmysql.dll is present within the MySQL directory. Also check whether the path to the same has been added to your Environment Variables.

.pro file

QT       += core
QT       -= gui
QT       += webkit webkitwidgets
QT       += sql
TARGET = TestMySqlExample
CONFIG   += console
CONFIG   -= app_bundle

TEMPLATE = app

SOURCES += main.cpp

Save all changes. Build and run the application. If no errors occur then you should see the data from your table being displayed in the console. πŸ™‚

output

Reference: Connecting to databases in Qt

Connect to MySQL database using JDBC

Java DataBase Connectivity (JDBC) is basically a data access technology that provides an API to help define how a client may access a database. In addition, the API helps in loading appropriate Java packages with the JDBC Driver Manager. The DriverManager class is used as a connection factory for creating JDBC connections.

It mainly provides three types of statements:

1. Statement

It can be used for general-purpose access to the database. It is useful when you are using static SQL statements at runtime.

2. PreparedStatement

It can be used when you plan to use the same SQL statement many times. The PreparedStatement interface accepts input parameters at runtime.

3. CallableStatement

CallableStatement can be used when you want to access database stored procedures.

Pre-requisites: Eclipse IDE, Windows 7 OS (64 bit)

Through this post, we will learn how to create a connection to an existing MySQL database using JDBC. You need to first download and setup an instance of the latest version of the MySQL database. For that purpose, you need to install and configure MySQL on Windows.

Next, you need to obtain the MySQL Java connector for the appropriate database version and operating system from over here. Now, let’s create a new Java console application project named TestMySqlExample.

Create a new class called TestMySqlConnection with package name com.example and add the following code!

TestMySqlConnection.java

package com.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestMySqlConnection {

	public static void main(String[] argv) {

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return;
		}

		Connection connection = null;

		try {
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database_name","mysql_user", "mysql_password");

		} catch (SQLException e) {
			System.out.println("Connection Failed!");
			e.printStackTrace();
			return;
		}

		if (connection != null) {
			System.out.println("Connected to database!");
		} else {
			System.out.println("Connection Failed!");
		}
	}
}

Finally, save and run the application. If the connection is successful, you should get a message in the Console window in Eclipse.

mysql_jdbc_demo

Tutorial #29: Connect to MySQL database using ODI

Hello everyone!

One of the main reasons developers use the Oracle Data Integrator platform is basically to integrate data from heterogeneous databases. The data is usually of high volume containing information stored on multiple databases. In such cases one needs to retrieve data from different sources and assemble it in a proper manner. This is where Oracle Data Integrator plays an important role.

In this tutorial, I will demonstrate how to first create and then connect to a MySQL database using ODI

Pre-requisites: MySQL Client, Oracle Data Integrator 11g (version 11.1.1.7.0)

Step 1: Open MySQL command line client. Enter your password to login and create a new database

create database karan;
use karan;

Step 2: Create a new table called Employee

create table Employee(EmpId int primary key, EmpName varchar(30);

Inside some dummy data inside the table.

insert into Employee values(101,'karan');
insert into Employee values(102,'kedar');
insert into Employee values(103,'mahesh');

Step 3: Create new data server, physical and logical schemas for MySQL

Open ODI Studio and connect to your work repository. Under the Technologies tab, right click MySQL–>Create new data server

Create new MySQL data server

Create new MySQL data server

data_server_mysql_2

Note: Click on Test Connection button to ensure that the connection is successful. If the connection is successful you are connected to your MySQL database. If not, you need to check your credentials including port number, username and password.

Now, create physical and logical schemas for the MySQL data server.

Create physical and logical schemas

Create physical and logical schemas

physical_schema_mysql

Step 4: Create new Model project and reverse engineer Employee table

Create a new Model project named mysql_model. Right click and select new Model.

Create new Model project

Create new Model project

Reverse engineer your Employee table. Right click table and view the data. It should reflect the data you inserted before!

Reverse engineer table

Reverse engineer table

View data

View data

You can now use this model in your Interfaces for loading data into your target datastore. That’s it for this tutorial. Thanks! πŸ™‚