BPP Programming: sql

Showing posts with label sql. Show all posts

Saturday, November 15, 2014

Alter Table PostgreSQL : Menambahkan Kolom Di Sebuah Tabel

kepingin melakukan alter table terhadap sebuah table di server PostgreSQL milik gue. alasannya kepingin menambah kolom dengan type data : Date . Jadinya baca-baca tutorial disini:

http://www.postgresql.org/docs/9.1/static/sql-altertable.html

http://www.postgresql.org/docs/8.1/static/ddl-alter.html

terlihat di situ ada perintah: Add Column.

Perintah untuk melihat spesifikasi sebuah Tabel:

\d namatabel

Contoh:

pdam=> \d pengguna
                                       Table "public.pengguna"
    Column    |         Type          |                           Modifiers
--------------+-----------------------+---------------------------------------------------------------
idpengguna   | integer               | not null default nextval('pengguna_idpengguna_seq'::regclass)
namalengkap | character varying(40) |
namapengguna | character varying(40) |
katakunci    | character varying(40) |
Indexes:
    "pengguna_pkey" PRIMARY KEY, btree (idpengguna)

Akhirnya ketemu perintah yg tepat buat alter tablenya setelah membaca tulisan ini:

http://www.postgresqltutorial.com/postgresql-insert/

perintahnya adalah sebagai berikut:

pdam=> ALTER TABLE pengguna ADD COLUMN tanggalmulai DATE;

kemudian kita jalankan lagi perintah ini untuk melihat struktur tabelnya setelah dijalankan perintah ALTER TABLE:

pdam=> \d pengguna

Maka hasilnya adalah:

                                      Table "public.pengguna"
    Column    |         Type          |                           Modifiers
--------------+-----------------------+---------------------------------------------------------------
idpengguna   | integer               | not null default nextval('pengguna_idpengguna_seq'::regclass)
namalengkap | character varying(40) |
namapengguna | character varying(40) |
katakunci    | character varying(40) |
tanggalmulai | date                  |
Indexes:
    "pengguna_pkey" PRIMARY KEY, btree (idpengguna)

ok udah berhasil. tinggal tambah satu kolom lagi yaitu: tanggalberakhir , dan type datanya juga DATE.

Pakai perintah:

pdam=> ALTER TABLE pengguna ADD COLUMN tanggalberakhir DATE;

Maka hasilnya adalah:

                                         Table "public.pengguna"
     Column      |         Type          |                           Modifiers
-----------------+-----------------------+---------------------------------------------------------------
idpengguna      | integer               | not null default nextval('pengguna_idpengguna_seq'::regclass)
namalengkap     | character varying(40) |
namapengguna    | character varying(40) |
katakunci       | character varying(40) |
tanggalmulai    | date                  |
tanggalberakhir | date                  |
Indexes:
    "pengguna_pkey" PRIMARY KEY, btree (idpengguna)

Kode Java SE Untuk Menyimpan Tanggal Ke PostgreSQL

Kode Java SE yg berhasil dijalankan untuk menyimpan data tanggal dari jDateChooser ke tabel PostgreSQL di Debian 7:

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package latihan6;

import com.toedter.calendar.JDateChooser;

import javax.swing.*;

import java.awt.*;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Date;

import java.sql.*;

/**
*
* @author steven
*/
public class Frame4 extends javax.swing.JFrame {

    /**
     * Creates new form Frame4
     */
    public Frame4() {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        jDateChooser1 = new com.toedter.calendar.JDateChooser();
        jLabel2 = new javax.swing.JLabel();
        jDateChooser2 = new com.toedter.calendar.JDateChooser();
        jButton1 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Input Tanggal");

        jLabel1.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N
        jLabel1.setText("Tanggal Mulai:");

        jLabel2.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N
        jLabel2.setText("Tanggal Berakhir:");

        jButton1.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N
        jButton1.setText("Test Koneksi");
        jButton1.setToolTipText("");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jButton2.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N
        jButton2.setText("Simpan Tanggal");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                        .addComponent(jLabel1)
                        .addComponent(jDateChooser1, javax.swing.GroupLayout.DEFAULT_SIZE, 330, Short.MAX_VALUE)
                        .addComponent(jLabel2)
                        .addComponent(jDateChooser2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jButton1)
                        .addGap(18, 18, 18)
                        .addComponent(jButton2)))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 24, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jDateChooser1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 24, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jDateChooser2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 44, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 46, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(94, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
        // TODO add your handling code here:


        // Membuat perintah untuk tombol test koneksi


        Connection Koneksi = null;

        try {

            String url = "jdbc:postgresql://localhost:5432/pdam";

            String user = "steven";

            String password = "kucing";


            Koneksi = DriverManager.getConnection(url,user,password);


            if (Koneksi != null) {

                JOptionPane.showMessageDialog(this, "Sudah Berhasil Terkoneksi", "Koneksi Ke Server", JOptionPane.INFORMATION_MESSAGE);


                Koneksi.close();


            }

        } catch (SQLException ex) {

            System.out.println();


            ex.printStackTrace();
            }

    }

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
        // TODO add your handling code here:


      java.sql.Date tanggalMulai = new java.sql.Date(jDateChooser1.getDate().getTime());

      java.sql.Date tanggalBerakhir = new java.sql.Date(jDateChooser2.getDate().getTime());



        Connection Koneksi = null;

        PreparedStatement PSInsert = null;

        String masukanData = "INSERT INTO penanggalan" + "(tanggalmulai,tanggalberakhir) VALUES" + "(?,?)";


        try {

            String url = "jdbc:postgresql://localhost:5432/pdam";

            String user = "steven";

            String password = "kucing";


            Koneksi = DriverManager.getConnection(url, user, password);


            Koneksi.setAutoCommit(false);

            if (Koneksi != null) {


                PSInsert = Koneksi.prepareStatement(masukanData);


                PSInsert.setDate(1, tanggalMulai);

                PSInsert.setDate(2, tanggalBerakhir);

                PSInsert.executeUpdate();

                Koneksi.commit();

                Koneksi.close();

            }


        } catch (SQLException ex) {


            System.out.println();

            ex.printStackTrace();




            }












    }

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(Frame4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(Frame4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(Frame4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Frame4.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new Frame4().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private com.toedter.calendar.JDateChooser jDateChooser1;
    private com.toedter.calendar.JDateChooser jDateChooser2;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    // End of variables declaration
}

Penampakan di pgAdmin3 sebagai bukti data berhasil disimpan:

Konversi java.util.Date Ke java.sql.Date

tengah malam ketemu pemecahan masalah konversi nilai tanggal dari java.util.Date ke java.sql.Date . hasil merangkai dari berbagai tulisan disini:

http://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date

http://www.java2s.com/Tutorial/Java/0040__Data-Type/ConvertfromajavautilDateObjecttoajavasqlDateObject.htm

http://stackoverflow.com/questions/23564363/getting-value-from-jdatechooser-and-saving-to-ms-sql-db

http://www.java2s.com/Code/PostgreSQL/Insert-Delete-Update/Insertdatedatatotable.htm

http://stackoverflow.com/questions/23447694/problems-using-jdatechooser-with-a-database

http://www.java2s.com/Code/PostgreSQL/Insert-Delete-Update/Insertdatedatatotable.htm

hasil akhir formulasi codingnya:

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package latihan6;

import com.toedter.calendar.JDateChooser;

import javax.swing.*;

import java.awt.*;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Date;

import java.sql.*;

/**
*
* @author steven
*/
public class Frame5 extends javax.swing.JFrame {

    /**
     * Creates new form Frame5
     */
    public Frame5() {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    private void initComponents() {

        jDateChooser1 = new com.toedter.calendar.JDateChooser();
        jButton1 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jDateChooser1.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N

        jButton1.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N
        jButton1.setText("Print java.util.Date");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jButton2.setFont(new java.awt.Font("Cantarell", 1, 17)); // NOI18N
        jButton2.setText("Print java.sql.Date");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(20, 20, 20)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jButton1)
                        .addGap(18, 18, 18)
                        .addComponent(jButton2))
                    .addComponent(jDateChooser1, javax.swing.GroupLayout.PREFERRED_SIZE, 357, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jDateChooser1, javax.swing.GroupLayout.DEFAULT_SIZE, 41, Short.MAX_VALUE)
                .addGap(18, 18, 18)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 54, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 54, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(30, 30, 30))
        );

        pack();
    }// </editor-fold>

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
        // TODO add your handling code here:

        Date tanggal = jDateChooser1.getDate();

        System.out.print(tanggal);


    }

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
        // TODO add your handling code here:


        // Date tanggal = jDateChooser1.getDate;

        java.sql.Date tanggal2 = new java.sql.Date(jDateChooser1.getDate().getTime());

        System.out.print(tanggal2);






    }

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(Frame5.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(Frame5.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(Frame5.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Frame5.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new Frame5().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private com.toedter.calendar.JDateChooser jDateChooser1;
    // End of variables declaration
}

Penampakan Program Kecilnya Ketika Dijalankan:

kalau menjalankan tombol Print java.util.Date maka format tanggal yg keluar adalah :

Mon Nov 03 00:00:00 WITA 2014

kalau menjalankan tombol Print java.sql.Date maka format tanggal yg keluar adalah :

2014-11-03

Format tanggal 2014-11-03 inilah yg katanya bisa dimasukan ke kolom tabel PostgreSQL yg memiliki type data Date.

Bolak balik nyari cara supaya outputnya JDateChooser adalah 2014-11-03 . ternyata format defaultnya java.sql.Date udah seperti itu. yah udah cocok lah. dan ternyata output standardnya JDateChooser adalah java.util.Date , jadi kalau outputnya mau dimasukan ke database harus di konversi dulu ke java.sql.Date