How do I create a table in a database?

By Wayan in JDBC 0 Comment

In this example you can see how to create a table in MySQL database. We create a table called book with the following fields, id, isbn, title, published_year and price. We start by creating a connection to the database and execute the create table query.

package org.kodejava.jdbc;

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

public class CreateTableExample {
    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 sql = """
                    CREATE TABLE book
                    (
                        id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                        isbn           varchar(50)         NOT NULL,
                        title          varchar(100)        NOT NULL,
                        published_year int(11)                      DEFAULT NULL,
                        price          decimal(10, 2)      NOT NULL DEFAULT '0.00',
                        PRIMARY KEY (id)
                    )
                    """;

            Statement statement = connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

Maven Central

How do I know the number of rows affected when updating data in database table?

By Wayan in JDBC 5 Comments

In this example you can see how to get number of rows or records affected when we update records in the database. The executeUpdate() method of Statement or PreparedStatement return an integer value which tell us how many records was affected by the executed command.

Note that when the return value for executeUpdate() method is 0, it can mean one of two things: (1) the statement executed was an update statement that affected zero row, or (2) the statement executed was a DDL statement such as statement to create a table in a database.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class HowManyRowExample {
    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 sql = "UPDATE book SET title = ?, published_year = ? WHERE id = ?";

            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "Java 8 in Action, First Edition");
            ps.setInt(2, 2014);
            ps.setLong(3, 1L);
            int rows = ps.executeUpdate();

            System.out.printf("%d row(s) updated!", rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The result of the code snippet above:

1 row(s) updated!

Maven Dependencies

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

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 know the minimum and maximum number in an array?

By Wayan in Core API, Util Package 0 Comment 
package org.kodejava.util;

import java.util.Arrays;
import java.util.Collections;

public class ArrayMinMax {
    public static void main(String[] args) {
        // Creates an array of integer numbers in it.
        Integer[] numbers = {8, 2, 6, 7, 0, 1, 4, 9, 5, 3};

        // To get the minimum or maximum value from the array we can
        // use the Collections.min() and Collections.max() methods.
        // But as this method requires a list type of data we need
        // to convert the array to list first.
        int min = Collections.min(Arrays.asList(numbers));
        int max = Collections.max(Arrays.asList(numbers));

        // Viola! we get the minimum and the maximum value from the
        // array.
        System.out.println("Min number: " + min);
        System.out.println("Max number: " + max);
    }
}

And here are the results:

Min number: 0
Max number: 9

How can I change file attribute to writable?

By Wayan in Core API, IO 0 Comment

Prior to Java 1.6 the java.io.File class doesn’t include a method to change a read only file attribute and make it writable. To do this on the old days we have to utilize or called operating system specific command. In Java 1.6 a new method named setWritable() was introduced to do exactly what the method name says.

package org.kodejava.io;

import java.io.File;

public class WritableExample {
    public static void main(String[] args) throws Exception {
        File file = new File("Writable.txt");

        // Create a file only if it doesn't exist.
        boolean created = file.createNewFile();

        // Set file attribute to read only so that it cannot be written
        boolean succeeded = file.setReadOnly();

        // We are using the canWrite() method to check whether we can
        // modified file content.
        if (file.canWrite()) {
            System.out.println("File is writable!");
        } else {
            System.out.println("File is in read only mode!");
        }

        // Now make our file writable
        succeeded = file.setWritable(true);

        // re-check the read-write status of file
        if (file.canWrite()) {
            System.out.println("File is writable!");
        } else {
            System.out.println("File is in read only mode!");
        }
    }
}

And here are the result of the code snippet above:

File is in read only mode!
File is writable!