How do I perform calculations in a SELECT statement?

By Wayan in Learning SQL 0 Comment

A SELECT statement is not limited to returning columns exactly as they are stored. You can compute new values on the fly — the total price of an order line, a discount, a tax amount, the age of a record, or the concatenation of a first and last name. These calculations happen inside the database and are returned as extra columns in your result set.

In this tutorial you will learn how to add computed columns to a SELECT statement using arithmetic operators, expressions on multiple columns, built-in functions, and aliases. You will also see how NULL affects the math, and which small differences to watch for between database systems.

Prerequisites

To follow along, you need:

  • A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
  • A client that can execute SQL statements.
  • Basic familiarity with the SELECT and WHERE clauses.

If you already followed the previous tutorials in this series, you can reuse the same sample tables.

Sample Database

We continue with the online bookstore schema used across the series so the examples stay consistent.

CREATE TABLE authors
(
    author_id   INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books
(
    book_id      INTEGER PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country)
VALUES (1, 'Jane Austen', 'United Kingdom'),
       (2, 'Haruki Murakami', 'Japan'),
       (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
       (4, 'Gabriel Garcia Marquez', 'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock,
                   published_on)
VALUES (1, 'Pride and Prejudice', 1, 'Classic', 12.50, 20, '1813-01-28'),
       (2, 'Emma', 1, 'Classic', 10.00, 0, '1815-12-23'),
       (3, 'Norwegian Wood', 2, 'Fiction', 15.75, 12, '1987-09-04'),
       (4, 'Kafka on the Shore', 2, 'Fiction', 18.20, 5, '2002-09-12'),
       (5, 'Half of a Yellow Sun', 3, 'Historical', 16.00, 8, '2006-08-11'),
       (6, 'Americanah', 3, 'Contemporary', 14.50, 3, '2013-05-14'),
       (7, 'One Hundred Years of Solitude', 4, 'Classic', 22.00, 25,
        '1967-05-30'),
       (8, 'Love in the Time of Cholera', 4, 'Classic', 19.99, 0, '1985-09-05'),
       (9, 'Unknown Title', 2, NULL, 13.00, 4, NULL);

Row 9 intentionally has NULL in category and published_on. We will use it to see how missing values behave in calculations.

Basic Syntax

Any expression that returns a value can appear in the SELECT list, not just a column name. You can combine literal values, columns, arithmetic operators, and function calls, and you can give the result a name with an alias.

SELECT
    column_name,
    expression        AS alias_name,
    function(column)  AS alias_name
FROM table_name
WHERE condition;
  • expression — anything the database can evaluate, such as price * stock or UPPER(title).
  • AS alias_name — a readable label for the computed column. The AS keyword is optional in most databases but recommended for clarity.
  • The calculation is executed per row, using values from that row.

Arithmetic Operators

SQL supports the standard arithmetic operators:

Operator Meaning Example
+ Addition price + 1.00
- Subtraction stock - 1
* Multiplication price * stock
/ Division price / 2
% Modulo (remainder), most engines stock % 2

The modulo operator % is supported by PostgreSQL, MySQL, MariaDB, SQL Server, and SQLite. Oracle Database uses the MOD(x, y) function instead. MOD(x, y) is portable and works on every engine listed above.

Practical Example

The bookstore manager wants a report of the total value of each book’s stock — that is, price × stock for every row.

SELECT
    book_id,
    title,
    price,
    stock,
    price * stock AS inventory_value
FROM books
ORDER BY inventory_value DESC;

Reading the query in logical order:

  1. FROM books — start with every row in books.
  2. SELECT ... — for each row, compute price * stock and label it inventory_value.
  3. ORDER BY inventory_value DESC — sort so that the most valuable inventory appears first.

Expected Result

book_id title price stock inventory_value
7 One Hundred Years of Solitude 22.00 25 550.00
1 Pride and Prejudice 12.50 20 250.00
3 Norwegian Wood 15.75 12 189.00
5 Half of a Yellow Sun 16.00 8 128.00
4 Kafka on the Shore 18.20 5 91.00
9 Unknown Title 13.00 4 52.00
6 Americanah 14.50 3 43.50
2 Emma 10.00 0 0.00
8 Love in the Time of Cholera 19.99 0 0.00

The calculation is performed row by row; nothing is aggregated across rows yet. That is a topic for a later tutorial on GROUP BY.

Additional Examples

1. Applying a Discount

Show each book with a 10% discount applied to its price.

SELECT
    title,
    price                 AS original_price,
    price * 0.90          AS discounted_price
FROM books
ORDER BY title;

The literal 0.90 is a numeric value, and the multiplication returns a numeric result. If you prefer to express the discount as a subtraction, price - (price * 0.10) produces the same value.

2. Rounding a Computed Value

Computed columns often have more decimal places than you want to display. Use ROUND(expression, digits) to control precision.

SELECT
    title,
    price,
    ROUND(price * 0.90, 2) AS discounted_price
FROM books
ORDER BY discounted_price DESC;

ROUND is available in every major database, though the exact rounding rules (half-up vs. banker’s rounding) can differ slightly. Always check the documentation if the last digit matters for financial reporting.

3. Building a Derived Column from Multiple Columns

The following query builds a compact line item description by concatenating text and formatting the price.

-- PostgreSQL, Oracle Database, SQLite
SELECT
    title || ' - $' || price AS line_item
FROM books
ORDER BY title;

|| is the ANSI string-concatenation operator. It is supported by PostgreSQL, Oracle Database, and SQLite. It is also supported by MySQL and MariaDB when PIPES_AS_CONCAT SQL mode is enabled, but it is not the default.

Portable alternative that works across engines:

-- Portable across PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, SQLite
SELECT
    CONCAT(title, ' - $', price) AS line_item
FROM books
ORDER BY title;

SQL Server uses + for string concatenation, but CONCAT is preferred because it handles NULL values without turning the whole result into NULL.

4. Integer Division and Numeric Types

Division behaves differently for integer and decimal operands. In PostgreSQL, Oracle Database, and SQL Server, dividing two integers truncates the fractional part:

SELECT 7 / 2 AS integer_division;

Result: 3 on PostgreSQL, SQL Server, and Oracle Database. MySQL, MariaDB, and SQLite return 3.5 because they promote the result to a floating-point value.

To make the intent explicit and portable, cast one operand to a numeric type:

SELECT
    stock,
    CAST(stock AS DECIMAL(10, 2)) / 2 AS half_stock
FROM books
WHERE stock > 0;

5. Using Built-in Functions

Databases provide a rich set of functions. A few commonly used ones in a SELECT list:

SELECT
    UPPER(title)         AS title_upper,
    LOWER(category)      AS category_lower,
    LENGTH(title)        AS title_length,
    ABS(stock - 10)      AS distance_from_ten,
    ROUND(price, 0)      AS price_rounded
FROM books
ORDER BY title;
  • UPPER / LOWER change case.
  • LENGTH returns the number of characters (in most databases; SQL Server uses LEN).
  • ABS returns the absolute value.
  • ROUND rounds a numeric value.

Each database ships its own set of scalar functions; consult your documentation for the full list.

6. Date Arithmetic

You can also compute values from date columns. The exact syntax depends on the database.

-- PostgreSQL: current date minus stored date returns an INTERVAL
SELECT
    title,
    published_on,
    CURRENT_DATE - published_on AS days_in_print
FROM books
WHERE published_on IS NOT NULL
ORDER BY days_in_print DESC;

-- MySQL / MariaDB
SELECT
    title,
    published_on,
    DATEDIFF(CURRENT_DATE, published_on) AS days_in_print
FROM books
WHERE published_on IS NOT NULL
ORDER BY days_in_print DESC;

-- SQL Server
SELECT
    title,
    published_on,
    DATEDIFF(DAY, published_on, CAST(GETDATE() AS DATE)) AS days_in_print
FROM books
WHERE published_on IS NOT NULL
ORDER BY days_in_print DESC;

Date functions vary substantially between databases. When portability matters, isolate them behind a helper view or in the application layer.

7. Using a Calculated Column in ORDER BY

Most databases accept an alias defined in the SELECT list inside the ORDER BY clause, because ORDER BY is logically evaluated after SELECT.

SELECT
    title,
    price,
    stock,
    price * stock AS inventory_value
FROM books
ORDER BY inventory_value DESC;

You can also repeat the expression instead of the alias — that always works, at the cost of some duplication.

8. Calculations in WHERE

You can filter by a computed value, but the expression must appear in the WHERE clause, not the alias, because WHERE is evaluated before SELECT.

Correct:

SELECT
    title,
    price * stock AS inventory_value
FROM books
WHERE price * stock > 100
ORDER BY inventory_value DESC;

Incorrect on most databases:

SELECT
    title,
    price * stock AS inventory_value
FROM books
WHERE inventory_value > 100;   -- error: alias not visible in WHERE

9. Calculations in Application Code

When your application reads a computed column, treat it the same as any other value. Use a PreparedStatement and parameters for any user-supplied inputs.

String sql = """
        SELECT
            book_id,
            title,
            price,
            stock,
            price * stock AS inventory_value
        FROM books
        WHERE category = ?
        ORDER BY inventory_value DESC
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, category);

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long bookId = resultSet.getLong("book_id");
            String title = resultSet.getString("title");
            BigDecimal price = resultSet.getBigDecimal("price");
            int stock = resultSet.getInt("stock");
            BigDecimal inventoryValue = resultSet.getBigDecimal("inventory_value");
            // process the row...
        }
    }
}

Use BigDecimal for monetary values to avoid the precision issues of binary floating point.

Common Mistakes

Forgetting That NULL Propagates Through Arithmetic

Any arithmetic expression that involves NULL returns NULL. If a nullable column participates in a calculation, expect NULL outputs.

Incorrect assumption:

SELECT
    title,
    price + NULL AS adjusted_price
FROM books;

Every row returns NULL for adjusted_price. To provide a default value, use COALESCE:

SELECT
    title,
    price + COALESCE(discount, 0) AS adjusted_price
FROM books;

COALESCE(expr1, expr2, ...) returns the first non-NULL argument and is supported by every major database.

Dividing by Zero

Dividing by zero raises an error in most databases (PostgreSQL, MySQL in strict mode, Oracle Database, SQL Server). Guard against it with a CASE expression or NULLIF.

SELECT
    title,
    price,
    stock,
    CASE WHEN stock = 0 THEN NULL
         ELSE price / stock
    END AS price_per_unit
FROM books;

Or, more compactly:

SELECT
    title,
    price / NULLIF(stock, 0) AS price_per_unit
FROM books;

NULLIF(a, b) returns NULL when a = b, and returns a otherwise. The result of the division becomes NULL instead of an error.

Referring to an Alias in WHERE

As shown earlier, aliases defined in the SELECT list are usually not visible in WHERE. Repeat the expression or wrap the query in a subquery / common table expression if the calculation is complex.

Assuming Integer Division Behaves Like Decimal Division

7 / 2 may return 3 on some databases and 3.5 on others. When the fractional part matters, cast at least one operand to a decimal or floating-point type.

Losing Precision with Floating-Point Types

FLOAT and DOUBLE PRECISION are approximate types. Use DECIMAL / NUMERIC for money, invoice totals, tax rates, and anything else where exact arithmetic is required.

Database Compatibility

Basic arithmetic (+, -, *, /), most standard scalar functions (ROUND, ABS, UPPER, LOWER, COALESCE, NULLIF, CAST), and column aliases are part of ANSI SQL and are supported by:

  • PostgreSQL
  • MySQL
  • MariaDB
  • SQL Server
  • Oracle Database
  • SQLite

Notable differences to be aware of:

  • String concatenation. || in PostgreSQL, Oracle Database, and SQLite. + in SQL Server. CONCAT(...) works everywhere.
  • Modulo. % works in PostgreSQL, MySQL, MariaDB, SQL Server, SQLite. Oracle Database uses MOD(x, y). MOD(x, y) is portable.
  • Integer division. PostgreSQL, Oracle Database, and SQL Server truncate on integer operands. MySQL, MariaDB, and SQLite return a floating-point result. Cast explicitly for portability.
  • String length. LENGTH on PostgreSQL, MySQL, MariaDB, SQLite, and Oracle Database. LEN on SQL Server. Oracle Database also has LENGTHB for byte length.
  • Date arithmetic. Every engine uses different function names (DATEDIFF, DATE_ADD, AGE, INTERVAL, etc.). Consult the documentation for your database and version.
  • Rounding rules. Different engines may implement half-up, half-even, or truncation for ROUND. Verify with your own test cases before using it for financial output.

When in doubt, consult the documentation for your database and version.

Best Practices

  • Always give computed columns an alias. Without one, the column name in the result set is engine-defined and unstable.
  • Use DECIMAL / NUMERIC for money. Binary floating-point types introduce rounding errors that are unacceptable in financial calculations.
  • Guard against division by zero with NULLIF or CASE.
  • Handle NULL explicitly with COALESCE when a nullable column participates in arithmetic.
  • Prefer portable functions (CONCAT, COALESCE, CAST, MOD) over engine-specific operators when the query may run on multiple databases.
  • Repeat expressions instead of relying on aliases in WHERE. Alternatively, use a common table expression or subquery so the calculation appears only once.
  • Use parameterized queries when constants in the calculation come from application input.
  • Avoid computing values in the application when the database can do it. The database can often use indexes and streaming, and less data is sent over the network.

Conclusion

You learned how to perform calculations in a SQL SELECT statement using arithmetic operators, string and date expressions, built-in functions, and aliases. You saw how NULL and division by zero require care, and how the same calculation can behave differently across database engines. Always use DECIMAL for money, guard against NULL and zero, and give every computed column a clear alias. Next, learn how to summarize rows with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX.

How do I retrieve data using a SELECT statement?

By Wayan in Learning SQL 0 Comment

The SELECT statement is the most fundamental SQL command — it lets you read (query) data from one or more tables in a relational database. Here’s a comprehensive guide to using it effectively.

1. The Basic Syntax

SELECT column1, column2, ...
FROM table_name;
  • SELECT — tells the database what columns you want.
  • FROM — tells it where (which table) to look.

Example: Get all names and emails from a users table

SELECT name, email
FROM users;

2. Selecting All Columns with *

Use the asterisk (*) to retrieve every column:

SELECT *
FROM users;

Tip: Avoid SELECT * in production code. It’s slower, returns unnecessary data, and breaks if the schema changes. Always list the columns you actually need.

3. Filtering Rows with WHERE

The WHERE clause restricts which rows are returned.

SELECT name, email
FROM users
WHERE age > 18;

Common WHERE operators

Operator Meaning Example
= Equal status = 'ACTIVE'
<> or != Not equal country <> 'US'
<, >, <=, >= Comparison age >= 21
BETWEEN Within a range age BETWEEN 18 AND 65
IN Matches any value in a list country IN ('US', 'UK', 'DE')
LIKE Pattern match name LIKE 'A%' (starts with “A”)
IS NULL Missing value deleted_at IS NULL

Combining conditions with AND / OR

SELECT id, name
FROM users
WHERE age >= 18 AND country = 'US';

4. Sorting Results with ORDER BY

SELECT name, age
FROM users
ORDER BY age DESC;
  • ASC — ascending (default)
  • DESC — descending

You can sort by multiple columns:

SELECT name, country, age
FROM users
ORDER BY country ASC, age DESC;

5. Limiting Results

Return only the first N rows (syntax varies by database):

-- PostgreSQL, MySQL, SQLite
SELECT name FROM users LIMIT 10;

-- SQL Server
SELECT TOP 10 name FROM users;

-- Oracle / Standard SQL
SELECT name FROM users FETCH FIRST 10 ROWS ONLY;

6. Removing Duplicates with DISTINCT

SELECT DISTINCT country
FROM users;

Returns each unique country only once.

7. Renaming Columns with Aliases (AS)

SELECT
    name       AS full_name,
    age * 12   AS age_in_months
FROM users;

Aliases make output more readable and are useful for computed columns.

8. Aggregating Data

Aggregate functions summarize multiple rows into one:

Function Purpose
COUNT() Number of rows
SUM() Total of a numeric col
AVG() Average
MIN() Smallest value
MAX() Largest value 
SELECT COUNT(*) AS total_users,
       AVG(age) AS average_age
FROM users;

Grouping with GROUP BY

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

Filtering groups with HAVING

WHERE filters rows before grouping; HAVING filters after.

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

9. Joining Multiple Tables

Retrieve data spread across related tables using JOIN.

SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100
ORDER BY o.created_at DESC;

Common join types:

  • INNER JOIN — only matching rows in both tables (default)
  • LEFT JOIN — all rows from the left table + matches from the right
  • RIGHT JOIN — the opposite of LEFT
  • FULL OUTER JOIN — all rows from both sides

10. The Logical Order of a SELECT

Even though you write clauses in this order:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

The database executes them in this order:

  1. FROM (and JOIN)
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Understanding this order helps explain why you can’t use a SELECT alias in WHERE, but you can in ORDER BY.

11. A Complete Realistic Example

SELECT
    u.country,
    COUNT(o.id)       AS order_count,
    SUM(o.total)      AS total_revenue,
    AVG(o.total)      AS avg_order_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'COMPLETED'
  AND o.created_at >= '2025-01-01'
GROUP BY u.country
HAVING SUM(o.total) > 10000
ORDER BY total_revenue DESC
LIMIT 20;

“Show me the top 20 countries by revenue in 2025, but only those exceeding $10,000 in completed orders.”

12. Using SELECT in Java (Spring Data JPA)

If you are using Spring Data JPA, you typically don’t execute raw SQL. Instead:

Derived query methods

List<User> findByAgeGreaterThan(int age);
List<User> findByCountryAndStatusOrderByCreatedAtDesc(String country, Status status);

JPQL with @Query

@Query("SELECT u FROM User u WHERE u.age > :minAge")
List<User> findAdults(@Param("minAge") int minAge);

Native SQL with @Query(nativeQuery = true)

@Query(value = "SELECT * FROM users WHERE age > :minAge", nativeQuery = true)
List<User> findAdultsNative(@Param("minAge") int minAge);

Projections (return only certain fields)

public interface UserSummary {
    String getName();
    String getEmail();
}

@Query("SELECT u.name AS name, u.email AS email FROM User u WHERE u.age > :minAge")
List<UserSummary> findAdultSummaries(@Param("minAge") int minAge);

13. Best Practices

  • List columns explicitly instead of SELECT *.
  • Use parameter binding (? placeholders or :named params) — never concatenate user input into SQL (prevents SQL injection).
  • Filter early with WHERE to reduce the working set.
  • Index columns used in WHERE, JOIN, and ORDER BY.
  • Use LIMIT when you only need a few rows.
  • Read the execution plan (EXPLAIN / EXPLAIN ANALYZE) when queries are slow.

Summary

Clause Purpose
SELECT Which columns to return
FROM Which table(s) to read from
JOIN Combine rows from related tables
WHERE Filter individual rows
GROUP BY Group rows sharing a value
HAVING Filter groups
ORDER BY Sort the results
LIMIT Restrict how many rows are returned

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 read-only MySQL user?

By Wayan in MySQL 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 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>

Maven Central Maven Central

How do I retrieve values from ResultSet?

By Wayan in JDBC 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>

Maven Central

How do I query records from a table?

By Wayan in JDBC 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>

Maven Central