Basic CRUD Application in Java JDBC and MySQL

In this article I would like to share with you a collection of routine to implement CRUD application in Java using JDBC and MySQL.

I am currently accepting programming work, it project, school programming projects , thesis and capstone projects, IT consulting work and web development work kindly contact me in the following email address for further details. Thank you.

My email address are the following jakerpomperada@gmail.com and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is  +63 (034) 4335675.

Program Listing

Jdbc.java

package com.jdbc.demo;

import java.sql.*;

public class Jdbc {

    private static Connection connection;

    // create table script

    // create table item(id int primary key auto_increment, name varchar(20), price double);

    public static void main(String[] args) throws Exception {

        String dbURL = "jdbc:mysql://localhost:3306/product";

        String username = "root";

        String password = "";

        try {

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            connection = DriverManager.getConnection(dbURL, username, password);

            if (connection != null) {

                System.out.println("Connected");

            }

            // insert record

            //insertItem("Pencil",5.50);

            //insertItem("Ballpen",9.00);

            //updateItem("Pencil",6.00);

            deleteItem("Pencil");

            // select records

            selectItems();

            connection.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    private static void deleteItem(String name) throws Exception {

        String sql = "DELETE FROM Item WHERE name=?";

        PreparedStatement statement = connection.prepareStatement(sql);

        statement.setString(1, name);

        int rowsDeleted = statement.executeUpdate();

        if (rowsDeleted > 0) {

            System.out.println("An item was deleted successfully!");

        }

    }

    private static void updateItem(String name, Double price) throws Exception {

        String sql = "UPDATE Item SET price=? WHERE name=?";

        PreparedStatement statement = connection.prepareStatement(sql);

        statement.setDouble(1, price);

        statement.setString(2, name);

        int rowsUpdated = statement.executeUpdate();

        if (rowsUpdated > 0) {

            System.out.println("An existing item was updated successfully!");

        }

    }

    private static void selectItems() throws Exception {

        String sql = "SELECT * FROM Item";

        Statement statement = connection.createStatement();

        ResultSet result = statement.executeQuery(sql);

        int count = 0;

        while (result.next()){

            String name = result.getString(2);

            Double price = result.getDouble(3);

            String output = "Item #%d: %s - %s";

            System.out.println(String.format(output, ++count, name, price));

        }

    }

    private static void insertItem(String name, Double price) throws Exception {

        String sql = "INSERT INTO Item (name, price) VALUES (?, ?)";

        PreparedStatement statement = connection.prepareStatement(sql);

        statement.setString(1, name);

        statement.setDouble(2, price);

        int rowsInserted = statement.executeUpdate();

        if (rowsInserted > 0) {

            System.out.println("A new item was inserted successfully!");

        }

    }

}