jdbc-programing example code

ConnectionWithDatabase.java

package org.ksug;

import org.h2.jdbcx.JdbcDataSource;

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

public class ConnectionWithDatabase {

    public void usingDriverManager(String url, String username, String password) throws ClassNotFoundException, SQLException {
        System.out.println("ConnectionWithDatabase.usingDriverManager");
        System.out.println("url = [" + url + "], username = [" + username + "], password = [" + password + "]");

        Class.forName("org.h2.Driver");

        Connection connection = DriverManager.getConnection(url, username, password);
        printDatabaseInfo(connection);

        connection.close();
    }

    public void usingDataSource(String url, String username, String password) throws SQLException {
        System.out.println("ConnectionWithDatabase.usingDataSource");
        System.out.println("url = [" + url + "], username = [" + username + "], password = [" + password + "]");

        JdbcDataSource dataSource = new JdbcDataSource();
        dataSource.setUrl(url);
        dataSource.setUser(username);
        dataSource.setPassword(password);

        Connection connection = dataSource.getConnection();
        printDatabaseInfo(connection);

        connection.close();
    }

    private void printDatabaseInfo(Connection connection) throws SQLException {
        System.out.println(String.format("%s - %s", connection.getMetaData().getDatabaseProductName(), connection.getMetaData().getDatabaseProductVersion()));
    }


    public static void main(String[] args) throws Exception {
        ConnectionWithDatabase cwd = new ConnectionWithDatabase();

        String url = "jdbc:h2:tcp://localhost/~/jdbc;MVCC=TRUE";
        String username = "sa";
        String password = "";

        cwd.usingDriverManager(url, username, password);
        cwd.usingDataSource(url, username, password);
    }

}

db.sql

create table varietal ( id bigint auto_increment, name varchar(255) );
alter table varietal add primary key (id);

insert into varietal (id, name) values(1, 'Amarone');
insert into varietal (id, name) values(2, 'Banylus');
insert into varietal (id, name) values(3, 'Cava');
insert into varietal (id, name) values(4, 'Dolcetto');
insert into varietal (id, name) values(5, 'Marsala');


create table winetype ( id bigint auto_increment, name varchar(255) );
alter table winetype add primary key (id);

insert into winetype (id, name) values(1, 'Red');
insert into winetype (id, name) values(2, 'White');
insert into winetype (id, name) values(3, 'Rose');
insert into winetype (id, name) values(4, 'Bubbles');
insert into winetype (id, name) values(5, 'Other');

create table wine ( id bigint auto_increment
                  , name varchar(255) not null
                  , winetype_id bigint not null
                  , varietal_id biging not null
                  , vineyard varchar(255)
                  , vintage int
                  , rating double
                  , dateTasted timestamp
                  , memo text );
alter table wine add primary key (id);
alter table wine add foreign key (winetype_id) references winetype (id);
alter table wine add foreign key (varietal_id) references varietal (id);

Varietal.java

package org.ksug;

public class Varietal {

    private long id;
    private String name;

    public Varietal(long id, String name) {
        this.id = id;
        this.name = name;
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

}

Wine.java

package org.ksug;

import java.util.Date;

public class Wine {

    private long id;
    private String name;
    private WineType wineType;
    private Varietal varietal;
    private String vineyard;
    private int vintage;
    private double rating;
    private Date dateTasted;
    private String memo;


    public Wine(long id, String name, WineType wineType, Varietal varietal, String vineyard, int vintage, double rating, Date dateTasted, String memo) {
        this.id = id;
        this.name = name;
        this.wineType = wineType;
        this.varietal = varietal;
        this.vineyard = vineyard;
        this.vintage = vintage;
        this.rating = rating;
        this.dateTasted = dateTasted;
        this.memo = memo;
    }


    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public WineType getWineType() {
        return wineType;
    }

    public Varietal getVarietal() {
        return varietal;
    }

    public String getVineyard() {
        return vineyard;
    }

    public int getVintage() {
        return vintage;
    }

    public double getRating() {
        return rating;
    }

    public Date getDateTasted() {
        return dateTasted;
    }

    public String getMemo() {
        return memo;
    }

    @Override
    public String toString() {
        final StringBuilder value = new StringBuilder("Wine { ");
        value.append("id=").append(id);
        value.append(", name='").append(name).append('\'');
        value.append(", wineType='").append(wineType.getName()).append('\'');
        value.append(", varietal='").append(varietal.getName()).append('\'');
        value.append(", vineyard='").append(vineyard).append('\'');
        value.append(", vintage=").append(vintage);
        value.append(", rating=").append(rating);
        value.append(", dateTasted=").append(dateTasted);
        value.append(", memo='").append(memo).append('\'');
        value.append(" }");
        return value.toString();
    }

}

WineCRUD.java

package org.ksug;

import org.h2.jdbcx.JdbcDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Objects;

public class WineCRUD {

    private DataSource dataSource;

    public WineCRUD(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Wine insert(Wine wine) throws SQLException {
        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(
                "insert into wine( name, winetype_id, varietal_id, vineyard, vintage, rating, dateTasted, memo ) " +
                "values (?, ?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setString(1, wine.getName());
        preparedStatement.setLong(2, wine.getWineType().getId());
        preparedStatement.setLong(3, wine.getVarietal().getId());
        preparedStatement.setString(4, wine.getVineyard());
        preparedStatement.setInt(5, wine.getVintage());
        preparedStatement.setDouble(6, wine.getRating());
        preparedStatement.setDate(7, new Date(wine.getDateTasted().getTime()));
        preparedStatement.setString(8, wine.getMemo());
        preparedStatement.executeUpdate();

        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if (Objects.nonNull(resultSet) && resultSet.next()) {
            wine.setId(resultSet.getLong(1));
        }

        preparedStatement.close();
        connection.close();

        return wine;
    }

    public Wine update(Wine wine) throws SQLException {
        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("update wine set name = ? where id = ?");
        preparedStatement.setString(1, wine.getName());
        preparedStatement.setLong(2, wine.getId());
        preparedStatement.executeUpdate();

        preparedStatement.close();
        connection.close();

        return wine;
    }

    public void delete(Wine wine) throws SQLException {
        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("delete from wine where id = ?");
        preparedStatement.setLong(1, wine.getId());
        preparedStatement.executeUpdate();

        preparedStatement.close();
        connection.close();
    }

    public Wine findOne(long id) throws SQLException {
        Wine wine = null;

        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(
                "select w.*, wt.name as winetype_name, v.name as varietal_name from wine w " +
                "inner join winetype wt on wt.id = w.winetype_id " +
                "inner join varietal v on v.id = w.varietal_id where w.id = ?");
        preparedStatement.setLong(1, id);

        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            String name = resultSet.getString("name");
            WineType wineType = new WineType(resultSet.getLong("winetype_id"), resultSet.getString("winetype_name"));
            Varietal varietal = new Varietal(resultSet.getLong("varietal_id"), resultSet.getString("varietal_name"));
            String vineyard = resultSet.getString("vineyard");
            int vintage = resultSet.getInt("vintage");
            double rating = resultSet.getDouble("rating");
            java.util.Date dateTasted = resultSet.getDate("dateTasted");
            String memo = resultSet.getString("memo");

            wine = new Wine(id, name, wineType, varietal, vineyard, vintage, rating, dateTasted, memo);
        }

        preparedStatement.close();
        connection.close();

        return wine;
    }



    public static void main(String[] args) throws Exception {
        String url = "jdbc:h2:tcp://localhost/~/jdbc;MVCC=TRUE";
        String username = "sa";
        String password = "";

        JdbcDataSource dataSource = new JdbcDataSource();
        dataSource.setUrl(url);
        dataSource.setUser(username);
        dataSource.setPassword(password);

        WineCRUD crud = new WineCRUD(dataSource);

        WineType wineType = new WineType(2, "White");
        Varietal varietal = new Varietal(5, "Marsala");
        Wine wine = new Wine(Long.MIN_VALUE, "이름모를와인", wineType, varietal, "우리동네포도밭", 2016, 5.0, new java.util.Date(), "대박 맛있다!!!");

        crud.insert(wine);
        Wine create = crud.findOne(wine.getId());
        System.out.println("create = " + create);

        wine.setName("슈퍼마켓 사장님 추천 와인");
        crud.update(wine);
        Wine update = crud.findOne(wine.getId());
        System.out.println("update = " + update);

        crud.delete(wine);
        Wine delete = crud.findOne(wine.getId());
        System.out.println("delete = " + delete);
    }

}

WineType.java

package org.ksug;

public class WineType {

    private long id;
    private String name;

    public WineType(long id, String name) {
        this.id = id;
        this.name = name;
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

}