How do I update records in MS Access database?

By Wayan in JDBC, Microsoft Access 0 Comment

In this post, we will learn how to update records in a database using the Java Database Connectivity (JDBC) API. JDBC is a Java API which is used to connect and execute query in the database.

We will be working with an MS Access database file named musicdb.accdb for this example. Our goal is to update an album’s title and release date in our album table.

The following code snippet show you how to do it. The selectAlbum() method is just a helper method to show the album data before and after the data was updated.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.Month;

public class MSAccessUpdate {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection conn = DriverManager.getConnection(url)) {
            conn.setAutoCommit(false);
            selectAlbum(conn, 2L);

            String sql = """
                    update album
                        set title = ?,
                        release_date = ?
                    where id = ?
                    """;

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "A Hard Day's Night");
            ps.setDate(2, Date.valueOf(LocalDate.of(1964, Month.JULY, 10)));
            ps.setLong(3, 2);

            int rowCount = ps.executeUpdate();
            System.out.printf("%s (rows) updated.%n", rowCount);

            selectAlbum(conn, 2L);

            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Executes a SQL query to select an album from the database based on the
     * provided ID.
     *
     * @param conn the Connection object representing the database connection
     * @param id   the ID of the album to be selected
     * @throws SQLException if a database access error occurs or the SQL query
     * is invalid
     */
    private static void selectAlbum(Connection conn, Long id) throws SQLException {
        String sql = "select * from album where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.printf("id=%s, title=%s, released_date=%s%n",
                    rs.getLong("id"), rs.getString("title"),
                    rs.getDate("release_date"));
        }
    }
}

The following is an output produced by the code snippet above:

id=2, title=With the Beatles, released_date=1963-11-22
1 (rows) updated.
id=2, title=A Hard Day's Night, released_date=1964-07-10

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Maven Central

How do I insert a record into Microsoft Access database and get the generated key?

By Wayan in JDBC, Microsoft Access 0 Comment

In this tutorial, we’ll guide you through the process of inserting records into an MS Access Database. In the code snippet below we are using the UCanAccess JDBC driver, you can find the Maven dependencies at the end of this tutorial.

Setting Up The Connection

Firstly, we need to set up a connection to our MS Access database. This is done by specifying the path of your database file (.mdb or .accdb) in the JDBC URL and then getting a connection from DriverManager.

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
try (Connection connection = DriverManager.getConnection(url)) {
    ...
} catch (SQLException e) {
    e.printStackTrace();
}

Preparing the SQL Statement

Next step is to prepare our SQL insert statement. We use a PreparedStatement – it handles the tedious task of substituting the respective parameters into the placeholders ("?") in our SQL statement.

String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Setting Parameters and Executing the SQL Statement

After defining our SQL statement with placeholders, we replace these placeholders with actual values using the setter methods (setInt(), setString(), setDate(), etc.) provided by the PreparedStatement class. Once, the placeholders have been replaced with actual values, we call executeUpdate() method on the PreparedStatement object to execute the SQL statement.

ps.setString(1, "With the Beatles");
ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
int rowCount = ps.executeUpdate();
System.out.println("rowCount = " + rowCount);

In the code snippet above we set all placeholders with values we want to insert into the database, and then the statement is executed. The new record gets added to the database. We also keep the rowCount returned by the executeUpdate() method.

Get the Generated Key Value

When creating the PreparedStatement object we also passes the Statement.RETURN_GENERATED_KEYS parameter, this will allow us to get the generated key from the statement object after executing the query.

ResultSet generatedKeys = ps.getGeneratedKeys();
long generatedKey = 1L;
while (generatedKeys.next()) {
    generatedKey = generatedKeys.getLong(1);
}
System.out.println("Generated Key = " + generatedKey);

The Entire Code Snippet

Here is the entire Java class.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Month;

public class MSAccessInsert {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection connection = DriverManager.getConnection(url)) {
            String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, "With the Beatles");
            ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
            ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));

            int rowCount = ps.executeUpdate();
            System.out.println("rowCount = " + rowCount);

            ResultSet generatedKeys = ps.getGeneratedKeys();
            long generatedKey = 1L;
            while (generatedKeys.next()) {
                generatedKey = generatedKeys.getLong(1);
            }
            System.out.println("Generated Key = " + generatedKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The code above inserts a new record into the album table in the Access database. Remember to handle any SQLException that may be thrown while interacting with the database.

And that’s it! You have successfully inserted a record into an MS Access database using UCanAccess in Java. Whether you’re creating a small application or dealing with large-scale data management, UCanAccess is an easy, efficient way to work with MS Access databases in Java applications.

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Maven Central

How do I select a record from Microsoft Access Database?

By Wayan in JDBC, Microsoft Access 0 Comment

UCanAccess is a pure Java JDBC Driver implementation which allows Java developers and JDBC client programs to read and write Microsoft Access database files (.mdb and .accdb).

In this tutorial, we will demonstrate how to configure a project with UCanAccess and create a simple Java application to select data from an MS Access database.

Establishing a Connection

Before you can interact with a database, you need to establish a connection to it. The following is an example of how to establish a connection using UCanAccess:

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
try (Connection connection = DriverManager.getConnection(url)) {
    System.out.println("connection = " + connection);
    // Rest of the code goes here...
} catch (SQLException e) {
    e.printStackTrace();
}

In the code above, we create a connection URL using the absolute path to our Access database file. Then, we obtain a connection object by calling DriverManager.getConnection(url).

Fetching Data from the Database

Once the connection is established, we can execute SQL queries against the database. Here, let’s create a query to select some data:

String query = "select id, title, release_date from album where id = ?";

PreparedStatement ps = connection.prepareStatement(query);
ps.setLong(1, 1L);

ResultSet rs = ps.executeQuery();
while (rs.next()) {
    System.out.printf("id=%s, title=%s, released_date=%s%n",
            rs.getLong("id"), rs.getString("title"),
            rs.getDate("release_date"));
}

In this code, we create a PreparedStatement object, which enables us to execute parameterized SQL queries in a secure and efficient manner. Here, our SQL query includes a parameter, represented by the ? symbol, which we then set using setLong(). This would replace the ? with the value 1L.

We then execute our query by calling executeQuery() on the PreparedStatement object which returns a ResultSet object. This object represents the result set of the query.

We then loop through the result set and print each record using rs.next(), which is used to iterate through the ResultSet.

Full Code

package org.kodejava.jdbc;

import java.sql.*;

public class MSAccessSelect {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
        try (Connection connection = DriverManager.getConnection(url)) {
            System.out.println("connection = " + connection);

            String query = "select id, title, release_date from album where id = ?";

            PreparedStatement ps = connection.prepareStatement(query);
            ps.setLong(1, 1L);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.printf("id=%s, title=%s, released_date=%s%n",
                        rs.getLong("id"), rs.getString("title"),
                        rs.getDate("release_date"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Remember to handle SQL exceptions that might be thrown during the interaction with the database. And there you have it! Now you know how to work with MS Access Database using UCanAccess in Java. Happy coding!

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Maven Central

How to Create a Database in MySQL

By Rosa in Learning SQL 0 Comment

Introduction

When you build up an application, you need a database (db) to save your data. It could be about your order, member, or transactional data. It really depends on business needs from the application that you build. Another purpose is you can initiate improvements based on huge data that you’ve already saved.

Based on Wikipedia, a database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques Wikipedia.

There are many great databases these days, one of it is MySQL. In this section, we will learn from the beginning how to create a database, tables, and query data with MySQL.

Why MySQL:

  • It is open source. However, there are a personal and enterprise version.
  • Fast. Of course with the right indexes when you have huge amount of rows data.
  • Scalability, maintainability.
  • Suitable for web-based application. E-commerce, warehouse, logging, and many more.

Before we start, to create or manage your MySQL database, you need database client/IDE.

Three IDE options:

Personally, I find Sequel Pro is very helpful and powerful for my day-to-day use.

Start and Login to MySQL on your local machine (Mac OS X).

  1. Go to your System Preferences
  2. Find MySQL
  3. Choose to Start MySQL Server

After the MySQL database started, you can log in.

  1. Go to your database client, in this example I am using Sequel Pro.
  2. Connect to your localhost. You need to provide the username and password before login.
  3. Once you connect, you will be able to create your database.

Create new Database:

Create Database Statements
CREATE DATABASE database_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name];

Example:

CREATE DATABASE learning_mysql 
    CHARACTER SET utf8
    COLLATE utf8_general_ci;

Using Functionality Provided by IDE
  • Go to Database menu, select Add Database…

  • Then fill in the database name

For common cases and non latin, use UTF-8 for character set, and you can use utf8_general_ci for the collation.

Your database is now ready to use. Ensure you choose the right database that you want to manage. The second step is to prepare tables as per your business needs, to save the data from your application.

Happy exploring!

How do I backup MySQL databases in Ubuntu?

By Wayan in MySQL 0 Comment

What is MySQL

MySQL is an open-source RDBMS (Relational Database Management System). As the name implied it uses SQL (Structured Query Language) to access and manipulate data. MySQL has been widely used to store and manage data ranging from a simple web application to an enterprise class application.

The importance of data in every application require us to regularly back up the data to prevent data loss, for example caused by hardware crashes. In this post I will show you how to back up the database manually and using a script combined with a cron job to run the process automatically.

Using mysqldump

To create a database backup in MySQL we can use the mysqldump command. The example syntax of using this command is:

mysqldump -u username -p database_to_backup > backup_file_name.sql

If you need to restore the database you can use the following command:

mysql -u username -p database_to_restore < backup_file_name.sql

Before you can execute the command you might need to create the database if you don’t already have it.

saturn@ubuntu:~$ mysql -u root -p

CREATE DATABASE database_to_restore;

Creating Backup Script

To start let’s create MySQL user account that we are going to use to do the backup process. Login to MySQL using mysql -u root -p command. Type and execute the following command to create backupuser.

grant lock tables, select, show view on kodejava.* to 'backupuser'@'localhost' identified by 'backuppasswd';
flush privileges;

Exit from the MySQL using the exit command and create the following backup script called backup.sh using your favorite editor. For example, you can use nano or vim to create the file.

#!/bin/sh
BACKUP_HOME="/home/saturn/backup"

cd $BACKUP_HOME
directory="$(date +%Y%m%d)"

if [ ! -d "$directory" ]; then
    mkdir $directory
fi

backupdir="$BACKUP_HOME/$directory"
backup="kodejava-$(date +%Y%m%d%H%M%S)"

mysqldump -ubackupuser -pbackuppasswd --opt kodejava > $backupdir/$backup.sql

cd $directory
tar -czf $backup.tar.gz $backup.sql
rm $backup.sql

To make the backup.sh file executable you need to run the chmod +x backup.sh command.

Creating Scheduler Using Crontab

The crontab command is used to schedule commands to be executed periodically at a predetermined time. It will run as a background process without needing user intervention. These kinds of jobs are generally referred to as cron jobs and the jobs will run as the user who creates the cron jobs.

In the example below we register a cron job to execute the script at 12:00AM every day. To edit the cron jobs type crontab -e, this will open the crontab file.

saturn@ubuntu:~$ crontab -e
no crontab for saturn - using an empty one

Select an editor.  To change later, run 'select-editor'.
  1. /bin/ed
  2. /bin/nano        <---- easiest
  3. /usr/bin/vim.basic
  4. /usr/bin/vim.tiny

Choose 1-4 [2]:

Select an editor to edit the crontab, choose by entering the number of the editor. The easiest one is nano but you can also use vim if you comfortable with it.

And you will see an empty crontab file will the following commented messages:

# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').#
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command

Go to the end of the file and write the following entry to register a cron job. In the example below we register a cron job to execute the backup.sh script at 12:00M every day.

# m h  dom mon dow   command
  0 0   *   *   *    /home/saturn/backup.sh

After you save the file you can use the crontab -l command to list the registered cron job. If you want to know more about crontab you can visit crontab guru website.