How do I get all table names from a database?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

import java.sql.*;

public class GettingTableListExample {
    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)) {

            // Gets the metadata of the database
            DatabaseMetaData metaData = connection.getMetaData();
            String[] types = {"TABLE"};

            ResultSet rs = metaData.getTables("kodejava", "kodejava", "%", types);
            while (rs.next()) {
                String tableCatalog = rs.getString(1);
                String tableSchema = rs.getString(2);
                String tableName = rs.getString(3);

                System.out.printf("%s - %s - %s%n",
                        tableCatalog, tableSchema, tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Here is the result:

kodejava - null - author
kodejava - null - book_author
kodejava - null - book

Maven Dependencies

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

Maven Central

How do I drop table from a database?

By Wayan in JDBC 0 Comment

This example is to show you how to delete or drop a table from your database. Basically we just send a DROP TABLE command and specify the table name to be deleted to the database. The example below show you how to do it in MySQL database.

package org.kodejava.jdbc;

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

public class DropTableExample {
    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)) {

            // To delete a table from database we use the DROP TABLE
            // command and specify the table name to be dropped
            String sql = "DROP TABLE IF EXISTS book";

            // Create a statement
            Statement statement = connection.createStatement();
            // Execute the statement to delete the table
            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 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 delete record from a table?

By Wayan in JDBC 0 Comment

In this example we are showing you how to delete a record from table in the database. We use a standard JDBC library for this purpose. For the database we use MySQL, you can use any type of database you want. All you need to do is to find the JDBC driver for the database and configure it accordingly.

So here is the code example for deleting records from a table in a database.

package org.kodejava.jdbc;

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

public class DeleteRecordExample {

    public static void main(String[] args) {
        // This is our connection url to MySQL database, where jdbc is the
        // prefix for all jdbc connection. The mysql sub telling that we
        // are using MySQL database. Localhost is where our database is
        // resided and kodejava is our database name.
        String url = "jdbc:mysql://localhost/kodejava";

        // To connect to a database we will need a username and a password
        // for the database server to allow us to manipulate its data.
        String username = "kodejava";
        String password = "s3cr*t";

        // Then we ask a connection from the DriverManager by passing
        // the connection URL, the username and the password.
        try (Connection connection =
                     DriverManager.getConnection(url, username, password)) {

            // To delete records from tables we create an SQL delete command.
            // The question mark that we used in the where clause will be the
            // holder of value that will be assigned by PreparedStatement
            // class.
            String sql = "DELETE FROM book WHERE isbn = ?";
            String isbn = "9781617294945";

            // Create a statement object. We use PreparedStatement here.
            PreparedStatement statement = connection.prepareStatement(sql);

            // Pass a value of an ISBN that will tell the database which
            // record in the database to be deleted. Remember that when
            // using a statement object the index parameter is start from
            // 1 not 0 as in the Java array data type index.
            statement.setString(1, isbn);

            // Tell the statement to execute the command. The executeUpdate()
            // method for a delete command returns number of records deleted
            // as the command executed in the database. If no records was
            // deleted it will simply return 0
            int rows = statement.executeUpdate();

            System.out.println(rows + " record(s) deleted.");
        } 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

How do I create a connection to database?

By Wayan in JDBC 0 Comment

This post is about an example for obtaining a connection to MySQL database. For connecting to other database all you have to do is change the url to match to url format for a particular database and of course you have to register a correct JDBC driver of the database you are using.

Here are the steps:

  • Define the JDBC url of your database. Below is the format of JDBC url for MySQL database. localhost is your database address and kodejava is the database name.
private static final String URL = "jdbc:mysql://localhost/kodejava";
  • Define the username and password for the connection.
private static final String USERNAME = "kodejava";
private static final String PASSWORD = "s3cr*t";
  • Register the database JDBC driver to be used by our program. Below is the driver for MySQL database.
Class.forName("com.mysql.cj.jdbc.Driver");

The driver registration step above is not required anymore for modern JDBC drivers (JDBC 4.0 / since JDK 6). The JDBC driver class will be located using the service provider mechanism. So you can remove the Class.forName() statement above and all you need to do is place the JDBC driver in your classpath, and the driver will be loaded automatically.

  • We can open a connection to the database.
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
  • Do any database operation such as select, insert, update and delete.
  • Finally don’t forget to close the Connection object. We usually do this in the finally block of the try-catch block`
if (connection != null) {
    connection.close();
}

But in the code snippet below instead of manually close the connection object we use the try-with-resource statement, this statement will automatically close the connection for us.

Here is the complete code snippet.

package org.kodejava.jdbc;

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

public class ConnectionSample {
    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)) {
            System.out.println("connection = " + connection);

            String sql = "SELECT isbn, title, published_year FROM book";
            PreparedStatement stmt = connection.prepareStatement(sql);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("isbn") + ", " +
                        rs.getString("title") + ", " +
                        rs.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