Overview of database technology and examples of implementation in various languages

Machine Learning Artificial Intelligence Search Technology Algorithm Digital Transformation Mathematics Algorithm Programming Technology ICT Technology Database Technology Navigation of this blog

Overview of Database Technology

Database technology refers to technology for efficiently managing, storing, retrieving, and processing data, and is intended to support data persistence and manipulation in information systems and applications, and to ensure data accuracy, consistency, availability, and security.

Database technology includes the following functions and concepts

Data modeling: logically models data and defines the structure of the database. Data modeling includes modeling techniques such as entity-relationship model (ERM) and object-relationship model (ORM).
Database Management System (DBMS): Refers to software used to manage databases, providing functions such as creating, updating, and deleting databases, storing and retrieving data, and backing up and restoring data. Typical DBMSs include Oracle, MySQL, Microsoft SQL Server, and PostgreSQL.
Query Language: A query language is used to retrieve data from a database; SQL (Structured Query Language) is a common query language and one of the central features of database technology.
Transaction Management: Transaction management is the ability to handle multiple data operations as a single unit of processing and to maintain data integrity. It performs operations such as transaction initiation, commit, and rollback.
Indexes: Index design and operation require functions to support fast retrieval of data. Indexes have a significant impact on database performance.
Data Security: Data security is the ability to protect the confidentiality and integrity of the data stored in the database, including access control, authentication and authorization, and data encryption.
Data Backup and Restoration: Databases need to have the ability to regularly back up the data in the database and properly recover it in the event of a failure.

The following sections describe implementations in various languages for actually handling these databases.

Implementation in Python

There are various database libraries available for database implementation in Python. The following are examples of database implementations using typical database libraries commonly used in Python.

For SQLite3:.

import sqlite3

# Database Connection
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Table Creation
c.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)''')

# data insertion
c.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))
c.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 30))
conn.commit()

# data acquisition
c.execute("SELECT * FROM users")
rows = c.fetchall()
for row in rows:
    print(row)

# Closing the database connection
conn.close()

For MySQL (when using the MySQL Connector/Python library): 1.

import mysql.connector

# Database Connection
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='example'
)
cursor = conn.cursor()

# Table Creation
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)''')

# data insertion
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Alice', 25))
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Bob', 30))
conn.commit()

# data acquisition
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Closing the database connection
cursor.close()
conn.close()

For PostgreSQL (when using the psycopg2 library): 1.

import psycopg2

# Database Connection
conn = psycopg2.connect(
    dbname='example',
    user='user',
    password='password',
    host='localhost',
    port='5432'
)
cursor = conn.cursor()

# Table Creation
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255), age INT)''')

# data insertion
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Alice', 25))
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Bob', 30))
conn.commit()

# data acquisition
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Closing the database connection
cursor.close()
conn.close()

Next, an example implementation in Java is shown.

Implementation in Java

The Java Database Connectivity (JDBC) API is commonly used for database implementation in Java. The following are examples of database implementations in Java.

In the case of MySQL (using JDBC)

import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // Database Connection
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "password");

            // statement creation
            stmt = conn.createStatement();

            // Table Creation
            String createTableQuery = "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)";
            stmt.executeUpdate(createTableQuery);

            // data insertion
            String insertDataQuery1 = "INSERT INTO users (name, age) VALUES ('Alice', 25)";
            String insertDataQuery2 = "INSERT INTO users (name, age) VALUES ('Bob', 30)";
            stmt.executeUpdate(insertDataQuery1);
            stmt.executeUpdate(insertDataQuery2);

            // data acquisition
            String selectDataQuery = "SELECT * FROM users";
            rs = stmt.executeQuery(selectDataQuery);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Resource Release
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Implementation in Javascript

JavaScript is a client-side language that runs on the browser, and it is usually not possible to manipulate the database directly on the browser. However, there are ways to store data using browser functions, and by using them, web applications can be developed using JavaScript on the browser. The following are examples of database operations on the browser.

Web Storage (localStorage) Case:

// Data Storage
localStorage.setItem("name", "Alice");
localStorage.setItem("age", 25);

// Data Acquisition
var name = localStorage.getItem("name");
var age = localStorage.getItem("age");

console.log("Name: " + name + ", Age: " + age);

// Deletion of data
localStorage.removeItem("name");

If IndexedDB Case:

// Database Open
var request = window.indexedDB.open("example", 1);

// Process if database creation or version update is successful
request.onsuccess = function(event) {
    var db = event.target.result;

    // Transaction Initiation
    var transaction = db.transaction(["users"], "readwrite");
    var store = transaction.objectStore("users");

    // Adding Data
    var user = { id: 1, name: "Alice", age: 25 };
    var addUserRequest = store.add(user);

    addUserRequest.onsuccess = function(event) {
        console.log("User added successfully.");
    };

    addUserRequest.onerror = function(event) {
        console.error("Error adding user.");
    };

    // Data Acquisition
    var getUserRequest = store.get(1);

    getUserRequest.onsuccess = function(event) {
        var user = event.target.result;
        console.log("ID: " + user.id + ", Name: " + user.name + ", Age: " + user.age);
    };

    getUserRequest.onerror = function(event) {
        console.error("Error getting user.");
    };

    // Termination of Transaction
    transaction.oncomplete = function(event) {
        db.close();
    };
};

// What to do if database creation or version update fails
request.onerror = function(event) {
    console.error("Error opening database.");
};

Implementation in Clojure

Clojure is a Lisp functional programming language that runs on the Java Virtual Machine (JVM). This section describes an example of database implementation in Clojure using Java database libraries and Clojure libraries.

When using Java JDBC:.

(ns my-app.db
  (:import [java.sql DriverManager])
  (:import [java.sql Connection])
  (:import [java.sql Statement])
  (:import [java.sql ResultSet]))

;; Connect to database
(defn get-connection []
  (DriverManager/getConnection "jdbc:mysql://localhost/mydatabase" "username" "password"))

;; Data Acquisition
(defn get-users []
  (let [conn (get-connection)]
    (try
      (let [stmt (.createStatement conn)
            rs (.executeQuery stmt "SELECT * FROM users")]
        (loop [result []]
          (if (.next rs)
            (recur (conj result {:id (.getInt rs "id")
                                   :name (.getString rs "name")
                                   :age (.getInt rs "age")}))
            result))))
      (finally
        (.close conn)))))

;; Adding Data
(defn add-user [user]
  (let [conn (get-connection)]
    (try
      (let [stmt (.createStatement conn)
            sql (format "INSERT INTO users (name, age) VALUES ('%s', %d)"
                        (:name user)
                        (:age user))]
        (.execute stmt sql))
      (finally
        (.close conn)))))

To use HugSQL, Clojure’s database library:.

(ns my-app.db
  (:require [hugsql.core :as hugsql]))

;; Reading the hugsql file
(hugsql/def-db-fns "my-app/db/sql" :<< :>> :>)

;; Database Connection Settings
(def db-spec {:classname "com.mysql.jdbc.Driver"
              :subprotocol "mysql"
              :subname "//localhost/mydatabase"
              :user "username"
              :password "password"})

;; Data Acquisition
(defn get-users []
  (jdbc/query db-spec [:get-users]))

;; Adding Data
(defn add-user [user]
  (jdbc/execute! db-spec [:add-user user]))

Implementation using Laravel

Laravel is a popular PHP web application framework that provides useful features for easy database manipulation. Below is an example of a database implementation using Laravel.

Database Connection Configuration:.

In Laravel, it is common to set database connection information in .env files. For example, if MySQL is used, the configuration is as follows.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mydatabase
DB_USERNAME=username
DB_PASSWORD=password

Model Creation:.

Laravel uses models to operate with database tables. Models are defined using the Eloquent ORM. The following is an example of a User model.

<?php

namespace App;

use IlluminateDatabaseEloquentModel;

class User extends Model
{
    protected $table = 'users'; // table name
    protected $fillable = ['name', 'age']; // Attributes that can be assigned to the model
}

Retrieving Data:

To retrieve data from the database using a model, use the following method calls.

$users = User::all(); // Get all users
$user = User::find(1); // Get user with ID 1

Adding data:.

To add new data to the database using the model, create an instance, set attributes, and save it as follows.

$user = new User();
$user->name = 'John Doe';
$user->age = 30;
$user->save();

Data updates:

To update data in the database using a model, use the following method to obtain an instance of the model, set its attributes, and save it.

$user = User::find(1);
$user->name = 'Jane Doe';
$user->save();

Delete data:.

To delete data in the database using a model, obtain an instance of the model and call the delete method as follows

$user = User::find(1);
$user->delete();

As mentioned above, Laravel provides a convenient ORM that allows for easy database manipulation.

Implementation using Go

The Go language has a number of database manipulation libraries, a typical one being the “database/sql” package. Below is an example of a database implementation using the Go language.

Setting up a database connection:.

When using the “database/sql” package of the Go language, it is first necessary to set up a database connection. The following is an example of using MySQL.

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql" // MySQLドライバのインポート
)

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/mydatabase")
    if err != nil {
        panic(err)
    }
    defer db.Close()
}

Data acquisition:

To retrieve data from the database, execute an SQL query and obtain the results as follows

rows, err := db.Query("SELECT * FROM users")
if err != nil {
    panic(err)
}
defer rows.Close()

for rows.Next() {
    var id int
    var name string
    var age int
    err := rows.Scan(&id, &name, &age)
    if err != nil {
        panic(err)
    }
    // Processing of acquired data
}

Adding data:.

To add new data to the database, execute an SQL query as follows

result, err := db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "John Doe", 30)
if err != nil {
    panic(err)
}
lastInsertID, err := result.LastInsertId()
if err != nil {
    panic(err)
}

Data updates:

To update data in the database, execute an SQL query as follows

result, err := db.Exec("UPDATE users SET name = ? WHERE id = ?", "Jane Doe", 1)
if err != nil {
    panic(err)
}
affectedRows, err := result.RowsAffected()
if err != nil {
    panic(err)
}

Delete data:

To delete data from the database, execute an SQL query as follows

result, err := db.Exec("DELETE FROM users WHERE id = ?", 1)
if err != nil {
    panic(err)
}
affectedRows, err := result.RowsAffected()
if err != nil {
    panic(err)
}

As described above, the “database/sql” package allows for flexible database operations.