How do I drop table from database?

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.example.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 = "root";
    private static final String PASSWORD = "";

    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 books";

            // Create a statement
            Statement statement = connection.createStatement();
            // Execute the statement to delete the table
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I create a table in database?

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

package org.kodejava.example.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 = "root";
    private static final String PASSWORD = "";

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

            String sql = "CREATE TABLE books (" +
                "  isbn varchar(50) NOT NULL, " +
                "  title varchar(100) DEFAULT NULL, " +
                "  published_year int(11) DEFAULT NULL, " +
                "  price decimal(10,2) DEFAULT NULL, " +
                "  PRIMARY KEY (isbn) " +
                ")";

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

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I delete record from table?

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 database.

package org.kodejava.example.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
        // reside and kodejava is our database name.
        String url = "jdbc:mysql://localhost/kodejava";

        // To connect to a database we will need a user and password for
        // the database server to allow us to manipulate its database.
        String username = "root";
        String password = "";

        // Then we ask a connection from the DriverManager by passing
        // the connection URL 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 books WHERE isbn = ?";
            String isbn = "9781617294945";

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

            // Pass a value of a isbn that will tell the database which
            // records 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

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I create a connection to database?

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 = "root";
private static final String PASSWORD = "";
  • 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");

But 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.example.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 = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            System.out.println("connection = " + connection);

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

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getLong("isbn") + ", " +
                    rs.getString("title") + ", " +
                    rs.getInt("published_year"));
            }
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central