Connecting to MariaDB Server via Different programming Languages

In order to store or access the data inside a MariaDB database, you first need to connect to the MariaDB database server. We will show you the sample codes to connect your MariaDB via JAVA, Python, and PHP.

Connecting via JAVA

Before you start, you need to add MariaDB connector package to your Java project. The download link for MariaDB connector is https://mariadb.com/kb/en/about-mariadb-connector-j/.

package mariadb_connection;

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

public class MariadbConnection {

    public static void main(String[] args) {
        Connection conn = null;
        String dbServer = "mariadb-xxxxx-0.cloudclusters.net"; // change it to your database server name
        int dbPort = 4229; // change it to your database server port
        String dbName = "your database name";
        String userName = "your database user name";
        String password = "your database password";
        String url = String.format("jdbc:mariadb://%s:%d/%s?user=%s&password=%s", 
                                    dbServer, dbPort, dbName, userName, password);
        try {

            conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();

            // create table
            String sql = "create table teacher(NO char(20), name varchar(20),primary key(NO))";
            int result = stmt.executeUpdate(sql);

            // insert data
            if (result != -1) {
                sql = "insert into teacher(NO,name) values('202001','ben')";
                result = stmt.executeUpdate(sql);
                sql = "insert into teacher(NO,name) values('202002','ethan')";
                result = stmt.executeUpdate(sql);
            }

            // query data
            sql = "select * from teacher";
            ResultSet rs = stmt.executeQuery(sql);
            System.out.println("No.\tName");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        } catch (SQLException e) {
            System.out.println("MariaDB connection had an exception");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Connecting via Python

Install pymysql

pip install pymysql

Connect to your server

import pymysql


class MariadbConnection(object):
    def __init__(self):
        self.host = 'connect host'
        self.port = 4100  # your port
        self.user = 'your user'
        self.passwd = 'user password'
        self.db = 'your connect database name'

    def connect_mariadb(self):
        return pymysql.Connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd)

    def operate_database(self):
        # example select mysql version
        connect = self.connect_mariadb()
        cursor = connect.cursor()
        cursor.execute("SELECT VERSION()")
        # data = cursor.fetchone()
        print(cursor.fetchone()[0].split('-')[0][0:4])
        connect.close()


if _name_ == '__main__':
    MariadbConnection().operate_database()

Connecting via PHP

PHP offers two different ways to connect to MariaDB server: MySQLi (Improved MySQL) and PDO (PHP Data Objects) extensions.

1. MySQLi Object-oriented

<?php
$servername = "host";
$username = "username";
$password = "password";
$dbname   = "databasename";
$dbServerPort = "port";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $dbServerPort);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>

2. MySQLi Procedural

<?php
$servername = "host";
$username = "username";
$password = "password";
$dbname   = "databasename";
$dbServerPort = "port";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $dbServerPort);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

3. PDO(PHP Data Objects)

<?php

$host = 'mariadb-xxxxx-0.cloudclusters.net';
$db   = 'your database name';
$user = 'your database user name';
$pass = 'your password';
$port = "port";
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
try {
     $pdo = new \PDO($dsn, $user, $pass, $options);
     echo "Connected successfully";
} catch (\PDOException $e) {
     echo "Connection failed: " . $e->getMessage();
}

?>