SELECT - Learn Java by Examples

How do I select a record from Microsoft Access Database?

By Wayan in JDBC, Microsoft Access Last modified: July 15, 2024 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>

How to create a read-only MySQL user?

By Wayan in MySQL Last modified: July 11, 2024 3 Comments

Introduction

There are times when you need to create a user only to have read-only access to a database. The user can view or read the data in the database, but they cannot make any changes to the data or the database structure.

Creating a New User Account

To create a read-only database user account for MySQL do the following steps:

First, login as a MySQL administrator from your terminal / command prompt using the following command:

mysql -u root -p

You’ll be prompted to enter the password. Type the password for the root account.
Create a new MySQL user account.

CREATE USER 'report'@'%' IDENTIFIED BY 'secret';

The % in the command above means that user report can be used to connect from any host. You can limit the access by defining the host from where the user can connect. Omitting this information will only allow the user to connect from the same machine.

Grant the SELECT privilege to user.

GRANT SELECT ON kodejava.* TO 'report'@'%';

Execute the following command to make the privilege changes saved and take effect.

FLUSH PRIVILEGES;

Type quit to exit from the MySQL shell.

Test the New User Account

Now we can try the newly created user account. Start by login with the new user account and provide the corresponding password.

mysql -u report -p

Try executing the DELETE command:

mysql> USE kodejava;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM authors;
ERROR 1142 (42000): DELETE command denied to user 'report'@'localhost' for table 'authors'
mysql> UPDATE authors SET name = 'Wayan Saryada' WHERE id = 1;
ERROR 1142 (42000): UPDATE command denied to user 'report'@'localhost' for table 'authors'
mysql>

How do I use @Select annotation in MyBatis?

By Wayan in MyBatis Last modified: July 23, 2023 0 Comment

In the previous example How do I create MyBatis mapper? you’ve seen how to use a mapper to get a record from the database. In that example the select query is defined in the mapper xml file. For the same functionality MyBatis also offer a solution to use an annotation for the select query.

In this example we will use the @Select annotation to define the query. To map the query result we can use the @ResultMap annotation where the value passed to this annotation is the result map id that we’ve defined in the mapper xml file.

Let see an example of a mapper interface definition that use an annotation to get a record from database:

package org.kodejava.mybatis;

import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;
import org.kodejava.mybatis.support.Record;

public interface RecordMapper {
    /**
     * Get a single Record from the database based on the record
     * identified.
     *
     * @param id record identifier.
     * @return a record object.
     */
    @Select("SELECT * FROM records WHERE id = #{id}")
    @ResultMap("recordResultMap")
    Record getRecord(int id);
}

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.13</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.1.0</version>
    </dependency>
</dependencies>

How do I retrieve values from ResultSet?

By Wayan in JDBC Last modified: May 31, 2024 0 Comment

Here is another example on how to read data from a ResultSet returned by executing an SQL query in a database.

We start by creating a connection to the database. Creates a PreparedStatement to execute a query to get some data from the books table.

After executing the PreparedStatement we will have a ResultSet object. To iterate all the data in the ResultSet we call the next() method in a while-loop. When no more record to read the method return false. The ResultSet object also provides some methods to read value of the fields, the name of the method is corresponded to the type of data stored on each field of the table.

To read data using the ResultSet‘s methods (e.g. getString(), getInt(), getFloat(), etc) we can either use the column name, or the column index of the field read in the SQL statement.

Let’s see the complete code snippet below:

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.*;

public class ResultSetExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            String query = """
                    SELECT id,
                        isbn,
                        title,
                        published_year,
                        price
                    FROM book
                    """;

            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // Read values using column name
                Long id = rs.getLong("id");
                String isbn = rs.getString("isbn");
                String title = rs.getString("title");
                int publishedYear = rs.getInt("published_year");

                // Read values using column index
                BigDecimal price = rs.getBigDecimal(5);

                System.out.printf("%s, %s, %s, %d, %.2f\n", id, isbn, title,
                        publishedYear, price);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

As an example, running the code above will give us the following output:

1, 978-1491910771, Head First Java: A Brain-Friendly Guide, 2022, 45.49
2, 978-1617293566, Modern Java in Action, 2019, 54.99

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

How do I query records from a table?

By Wayan in JDBC Last modified: May 31, 2024 0 Comment

package org.kodejava.jdbc;

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

public class JdbcQueryExample {
    // Database connection information
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        // Get a connection to database.
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Create a statement object.
            Statement statement = connection.createStatement();

            // Executes a query command to select isbn and the book title
            // from books table. The execute query returns a ResultSet
            // object which is the result of our query execution.
            String query = "SELECT isbn, title, published_year FROM book";
            ResultSet books = statement.executeQuery(query);

            // To get the value returned by the statement.executeQuery we
            // need to iterate the books object until the last items.
            while (books.next()) {
                // To get the value from the ResultSet object we can call
                // a method that correspond to the data type of the column
                // in database table. In the example below we call
                // books.getString("isbn") to get the book's ISBN 
                // information.
                System.out.println(books.getString("isbn") + ", " +
                        books.getString("title") + ", " +
                        books.getInt("published_year"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>