askIT

Create table with Java database

document-save.png Download Java create table PostgreSQL code

Imageine you are running a coffe shop with products. You want them to be stored in a database, so they can be maintained easily.

Create a DbAdapter

The DbAdapter is in charge of making a connection to the database and disconnecting.

Create a package named dao. Create a class named DbAdapter inside the package.

package com.nettport.coffeshop.dao;

import java.sql.*;

public class DbAdapter {

    /* 01 Variables ---------------------------------------- */
    String jdbcUrl = "jdbc:postgresql://localhost:5432/coffe_shop";
    String username = "postgres";
    String password = "postgre";

    /* 02 Database variables ------------------------------- */
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    /* 03 Constructor for DbAdapter ------------------------ */
    public DbAdapter() {
    }

    /**
    * Connect to a database
    */

    public void connect() {
        try {
            // Step 2 - Open connection
            conn = DriverManager.getConnection(jdbcUrl, username, password);

            // Print connected
            System.out.println("DbAdapter: Connection to database established");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    } // connect

    /**
    * Disconnect from database
    */

    public void disconnect() {
        try {

            // Step 5 Close connection
            if (stmt != null) {
                stmt.close();
            }
            if (rs != null) {
                rs.close();
            }
            if (conn != null) {
                conn.close();
            }
            // Print connected
            System.out.println("DbAdapter: Connection to database closed");

        } catch (Exception e) {
            e.printStackTrace();
        }

    } // disconnect
}

Create class CoffeShopDbImpl

Now we want to make a class that creates the table. The class name will be CoffeShopDbImpl. Right click the dao package and create the class CoffeShopDbImpl. Your nw class should extend DbAdapter.

package com.nettport.coffeshop.dao;

import java.sql.SQLException;

public class CoffeShopDbImpl extends DbAdapter {

    public void createTables(){
        System.out.println("CoffeShopDbImpl: Create tables");
        try {
            stmt = conn.createStatement();
            String sql = "CREATE TABLE IF NOT EXISTS products " +
                    "(product_id SERIAL PRIMARY KEY NOT NULL," +
                    " product_name VARCHAR(100) NOT NULL, " +
                    " product_price REAL)";
            stmt.executeUpdate(sql);
            stmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    } // createTables
}

Use the class in Main

Go to your main class.

package com.nettport.coffeshop;

import com.nettport.coffeshop.dao.CoffeShopDbImpl;

public class Main {

    public static void main(String[] args) {
        // Connect
        CoffeShopDbImpl coffeShopDbImpl = new CoffeShopDbImpl();
        coffeShopDbImpl.connect();

        // Create tables
        coffeShopDbImpl.createTables();

        // Disconnect
        coffeShopDbImpl.disconnect();

    }
}

Browse database

Now head over to pgAdmin at http://127.0.0.1:55042/browser/#. Click Servers -> PostgreSQL -> coffe_shop -> Schemas -> Tables -> products.

Now right click on products and select View/Edit Data -> All Rows.

pgadmin_show_all_rows.png

You should now se a empty table with the columns

Add comment

avatar_blank_60.png

Comment:

Name:

Email:

Comments

No comments yet. You can be the first one to comment. Just write your comment in the form and click on the submit button.