API tools faq
paste
Advertisement
ojoakua

DBInsertData.java

ojoakua
Dec 14th, 2017
206
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 13.93 KB | None | 0 0
raw download clone embed print report
  1. /*
  2.  * To change this license header, choose License Headers in Project Properties.
  3.  * To change this template file, choose Tools | Templates
  4.  * and open the template in the editor.
  5.  */
  6. package db.kursus;
  7. import java.awt.HeadlessException;
  8. import java.sql.*;
  9. import java.util.*;
  10. import javax.swing.*;
  11. import java.text.*;
  12. import java.awt.event.*;
  13.  
  14. /**
  15.  *
  16.  * @author Aditya Pramana
  17.  */
  18. public class DBInsertData {
  19.     private String url;
  20.     private String username;
  21.     private String password;
  22.     private String[] listSiswa;
  23.     private String[] listKelas;
  24.     private String[] listTanggal;
  25.     private final String[] listBulan = new String[]{"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"};
  26.     private final String[] listTahun = new String[]{"2017", "2016", "2015", "2014", "2013"};
  27.    
  28.     private JFrame frame;
  29.     private JLabel lblSiswa;
  30.     private JLabel lblKelas;
  31.     private JLabel lblTanggal;
  32.     private JLabel lblNilai;
  33.     private JComboBox selectSiswa;
  34.     private JComboBox selectKelas;
  35.     private JComboBox selectTanggal;
  36.     private JComboBox selectBulan;
  37.     private JComboBox selectTahun;
  38.     private JCheckBox checkSelesai;
  39.     private JTextField txtNilai;
  40.     private JButton btnAdd;
  41.     private JButton btnDone;
  42.    
  43.     public DBInsertData(String url, String username, String password){
  44.         this.url = url;
  45.         this.username = username;
  46.         this.password = password;
  47.         listSiswa = getSiswa();
  48.         listKelas = getKelas();
  49.         listTanggal = new String[31];
  50.         for(int i = 0; i < 31; i++) listTanggal[i] = String.format("%02d", i+1);
  51.         initializeComponent();
  52.     }
  53.    
  54.     private void initializeComponent(){
  55.         frame = new JFrame("[DBAdmin] Data Edior || Logged in as " + username);
  56.         lblSiswa = new JLabel("Pilih nama siswa:");
  57.         selectSiswa = new JComboBox(listSiswa);
  58.         lblKelas = new JLabel("Pilih kelas:");
  59.         selectKelas = new JComboBox(listKelas);
  60.         lblTanggal = new JLabel("Masukkan Tanggal:");
  61.         selectTanggal = new JComboBox(listTanggal);
  62.         selectBulan = new JComboBox(listBulan);
  63.         selectTahun = new JComboBox(listTahun);
  64.         checkSelesai = new JCheckBox("Belum Selesai");
  65.         lblNilai = new JLabel("Masukkan nilai (1-100):");
  66.         txtNilai = new JTextField(20);
  67.         btnAdd = new JButton("Add Data");
  68.         btnDone = new JButton("Done");
  69.        
  70.         btnAdd.addActionListener(new btnAddListener());
  71.         btnDone.addActionListener(new btnDoneListener());
  72.         frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  73.         frame.setSize(540, 320);
  74.         frame.setResizable(false);
  75.        
  76.         //<editor-fold defaultstate="collapsed" desc=" A very long auto-generated code ">
  77.         GroupLayout layout = new GroupLayout(frame.getContentPane());
  78.         frame.getContentPane().setLayout(layout);
  79.         layout.setHorizontalGroup(
  80.             layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  81.             .addGroup(layout.createSequentialGroup()
  82.                 .addContainerGap()
  83.                 .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  84.                     .addComponent(selectSiswa, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  85.                     .addComponent(selectKelas, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  86.                     .addGroup(layout.createSequentialGroup()
  87.                         .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  88.                             .addComponent(lblSiswa)
  89.                             .addComponent(lblKelas)
  90.                             .addComponent(lblTanggal)
  91.                             .addGroup(layout.createSequentialGroup()
  92.                                 .addComponent(selectTanggal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  93.                                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
  94.                                 .addComponent(selectBulan, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  95.                                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
  96.                                 .addComponent(selectTahun, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  97.                                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
  98.                                 .addComponent(checkSelesai))
  99.                             .addComponent(lblNilai))
  100.                         .addGap(0, 175, Short.MAX_VALUE))
  101.                     .addComponent(txtNilai)
  102.                     .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
  103.                         .addGap(16, 16, Short.MAX_VALUE)
  104.                         .addComponent(btnAdd)
  105.                         .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  106.                         .addComponent(btnDone)))
  107.                 .addContainerGap())
  108.         );
  109.         layout.setVerticalGroup(
  110.             layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  111.             .addGroup(layout.createSequentialGroup()
  112.                 .addContainerGap()
  113.                 .addComponent(lblSiswa)
  114.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  115.                 .addComponent(selectSiswa, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  116.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  117.                 .addComponent(lblKelas)
  118.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  119.                 .addComponent(selectKelas, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  120.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  121.                 .addComponent(lblTanggal)
  122.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  123.                 .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
  124.                     .addComponent(selectTanggal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  125.                     .addComponent(selectBulan, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  126.                     .addComponent(selectTahun, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  127.                     .addComponent(checkSelesai))
  128.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  129.                 .addComponent(lblNilai)
  130.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  131.                 .addComponent(txtNilai, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
  132.                 .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 21, Short.MAX_VALUE)
  133.                 .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
  134.                     .addGap(16, 16, Short.MAX_VALUE)
  135.                     .addComponent(btnAdd)
  136.                     .addComponent(btnDone)))
  137.         );
  138.         //</editor-fold>
  139.        
  140.         frame.setVisible(true);
  141.     }
  142.    
  143.     public class btnAddListener implements ActionListener{
  144.         @Override
  145.         public void actionPerformed(ActionEvent e){
  146.             int cont = JOptionPane.showConfirmDialog(null, "Are you sure to add this data?", "Confirmation", JOptionPane.YES_NO_OPTION);
  147.             if (cont == 1) return;
  148.             try {
  149.                 int selectedSiswa = selectSiswa.getSelectedIndex();
  150.                 int selectedKelas = selectKelas.getSelectedIndex();
  151.                 String d = listTanggal[selectTanggal.getSelectedIndex()];
  152.                 String m = listBulan[selectBulan.getSelectedIndex()];
  153.                 String y = listTahun[selectTahun.getSelectedIndex()];
  154.                 boolean isSelesai = !checkSelesai.isSelected();
  155.                 String sNilai = txtNilai.getText();
  156.                 Integer nilai;
  157.                 String query;
  158.                
  159.                 if (sNilai.equals("") || !isSelesai) nilai = null;
  160.                 else nilai = Integer.parseInt(sNilai);
  161.                
  162.                 if (!dateCheck(d,m,y))
  163.                     throw new IllegalArgumentException("Wrong Date Format");
  164.                
  165.                 if (isSelesai) {
  166.                     if (nilai == null)
  167.                         throw new IllegalArgumentException("'Nilai' cannot be null if 'Belum selesai' is unchecked");
  168.                     query = String.format("INSERT INTO detil_kursus VALUES ('S%04d','K%04d'," +
  169.                         nilai + "," + isSelesai +  ",'%s-%s-%s');", selectedSiswa, selectedKelas, y, m, d);
  170.                 }
  171.                 else {
  172.                     String sDate = null;
  173.                     try (Connection connection = DriverManager.getConnection(url, username, password)){
  174.                         Statement stmt = connection.createStatement();
  175.                         String q = String.format("select pk.PK_TGLSELESAI from kelas_kursus kk, paket_kursus pk\n" +
  176.                                 "where kk.ID_PK=pk.ID_PK and kk.ID_KELAS='K%04d';", selectedKelas);
  177.                         ResultSet rs = stmt.executeQuery(q);
  178.                         rs.next();
  179.                         java.util.Date date = rs.getDate(1);
  180.                         SimpleDateFormat fr = new SimpleDateFormat("yyyy-MM-dd");
  181.                         sDate = String.format("'%s'", fr.format(date));
  182.                         connection.close();
  183.                     }
  184.                     catch(SQLException ex){
  185.                         JOptionPane.showMessageDialog(null, "SQL: An Error Occured");
  186.                     }
  187.                     query = String.format("INSERT INTO detil_kursus VALUES ('S%04d','K%04d'," +
  188.                         nilai + "," + isSelesai + "," + sDate + ");", selectedSiswa, selectedKelas);
  189.                 }
  190.                
  191.                 try (Connection connection = DriverManager.getConnection(url, username, password)) {
  192.                     Statement stmt = connection.createStatement();
  193.                     stmt.execute(query);
  194.                     connection.close();
  195.                 }
  196.                 catch (SQLException ex) {
  197.                     JOptionPane.showMessageDialog(null, "SQL: An Error Occured\n" + ex.getMessage());
  198.                 }
  199.             }
  200.             catch (HeadlessException | IllegalArgumentException ex){
  201.                 JOptionPane.showMessageDialog(null, "An error has occured\n" + ex.toString());
  202.             }
  203.         }
  204.        
  205.         boolean dateCheck(String day, String month, String year){
  206.             int d = Integer.parseInt(day);
  207.             int m = Integer.parseInt(month);
  208.             int y = Integer.parseInt(year);
  209.             switch(m){
  210.                 case 2:
  211.                     if(y % 4 == 0)
  212.                         return (d < 30);
  213.                     else return (d < 29);
  214.                 case 4:
  215.                 case 6:
  216.                 case 9:
  217.                 case 11:
  218.                     return (d < 31);
  219.                 default: return true;
  220.             }
  221.         }
  222.     }
  223.    
  224.     public class btnDoneListener implements ActionListener{
  225.         @Override
  226.         public void actionPerformed(ActionEvent e) {
  227.             System.exit(0);
  228.         }
  229.     }
  230.    
  231.     private String[] getKelas(){
  232.         java.util.List<String> kelas;
  233.         kelas = new ArrayList<>();
  234.         try (Connection connection = DriverManager.getConnection(url, username, password)){
  235.             Statement stmt = connection.createStatement();
  236.             ResultSet rs = stmt.executeQuery("select kk.ID_KELAS, p.P_NAMA, c.C_NAMA, pk.PK_NAMA, kk.daya_tampung \n"
  237.                     + "from kelas_kursus kk, pengajar p, cabang c, paket_kursus pk  \n"
  238.                     + "where kk.ID_PK = pk.ID_PK \n"
  239.                     + "and kk.ID_CABANG = c.ID_CABANG \n"
  240.                     + "and kk.ID_PENGAJAR = p.ID_PENGAJAR\n"
  241.                     + "order by kk.ID_KELAS;");
  242.             kelas.add("");
  243.             if (!rs.next()) return null;
  244.             else{
  245.                 do kelas.add(rs.getString(1) + " || " + rs.getString(2) + " || " +
  246.                         rs.getString(3) + " || " + rs.getString(4) + " || " + rs.getInt(5));
  247.                 while (rs.next());
  248.             }
  249.             connection.close();
  250.             return kelas.toArray(new String[kelas.size()]);
  251.         }
  252.         catch (SQLException ex){
  253.             JOptionPane.showMessageDialog(null, "A database-related error has occured");
  254.             return null;
  255.         }
  256.     }
  257.    
  258.     private String[] getSiswa(){
  259.         java.util.List<String> siswa;
  260.         siswa = new ArrayList<>();
  261.         try (Connection connection = DriverManager.getConnection(url, username, password)){
  262.             Statement stmt = connection.createStatement();
  263.             ResultSet rs = stmt.executeQuery("select no_siswa, s_nama from siswa;");
  264.             siswa.add("");
  265.             if (!rs.next()) return null;
  266.             else{
  267.                 do siswa.add(rs.getString(1) + " || " + rs.getString(2));
  268.                 while (rs.next());
  269.             }
  270.             connection.close();
  271.             return siswa.toArray(new String[siswa.size()]);
  272.         }
  273.         catch (SQLException ex){
  274.             JOptionPane.showMessageDialog(null, "A database-related error has occured");
  275.             return null;
  276.         }
  277.     }
  278. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!