Java Veritabanı Projesi – Java ile Veritabanı Bağlantısı

Merhaba arkadaşlar bugün sizlere Veritabanı dersinde yapmış olduğum küçük çaplı bir programı paylaşacağım. Programın işlevleri kısaca şu şekildedir;
Programda JFrame kullanılmıştır. Veritabanına kişi eklemek eklenen kişiyi silmek ve kişinin bilgilerini güncellemek bu program ile mümkündür. Eklenen herbir kişi Jtable’da görünmektedir. Aynı şekilde herhangi bir güncelleme veya kişi silme işleminin ardından Jtable güncellecektir. Jtable’a veri gönderirken net.proteanit.sql.DbUtils; kütüphanesini kullandım. kullanımı ise
DbUtils.resultSetToTableModel(resultset) result sette sizin sql sonucunuz tutulmuş olması gerekiyor. örneğin: resultset = command.executeQuery(“select * from people”); bu komutta people tablosundaki bütün bilgiler resultset’e kopyalandı ve DbUtils.resultSetToTableModel(resultset) komutu ile birnevi bu querynin outputunu Jtable’a aktarmış oluyoruz. İşimizi oldukça kolaylaştıran bir kütüphane olduğunu söyleyebilirim.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.ComboBoxModel;
import javax.swing.DefaultComboBoxModel;
import javax.swing.DefaultListModel;
import javax.swing.JOptionPane;
import javax.swing.JTable;

import net.proteanit.sql.DbUtils;

/**
 *
 * @author Emre Bektas
 */

public class DbProjectGui extends javax.swing.JFrame {
  protected Connection conn = null; //Bağlantı nesnemiz
    protected Statement command = null;// Komut nesnemiz
    protected String url = "jdbc:mysql://localhost:3306/";//veritabanının adresi ve portu
    protected String dbName = "test"; //veritabanının ismi   
    protected String driver = "com.mysql.jdbc.Driver";//MySQL-Java bağlantısını sağlayan JDBC sürücüsü
    protected String userName = "root"; //veritabanı için kullanıcı adı
    protected String password = ""; //kullanıcı şifresi
    protected ResultSet res; // sorgulardan dönecek kayıtlar (sonuç kümesi) bu nesne içerisinde tutulacak
    private PreparedStatement Statement1;
    /**
     * Creates new form DbProjectGui
     */
    public DbProjectGui() {
        initComponents();
    }
private void baglantiAc() throws Exception {

        Class.forName(driver).newInstance();
        conn = DriverManager.getConnection(url + dbName, userName, password);//bağlantı açılıyor       
        command = conn.createStatement();
    }
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        jTextFieldName = new javax.swing.JTextField();
        jTextFieldLName = new javax.swing.JTextField();
        jTextFieldAge = new javax.swing.JTextField();
        jButtonFetchData = new javax.swing.JButton();
        jButtonInsert = new javax.swing.JButton();
        jButton2Update = new javax.swing.JButton();
        jButtonDelete = new javax.swing.JButton();
        jComboBox1 = new javax.swing.JComboBox();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        jTable1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jTable1MouseClicked(evt);
            }
        });
        jScrollPane1.setViewportView(jTable1);

        jLabel2.setText("Name:");

        jLabel3.setText("LName:");

        jLabel4.setText("Age:");

        jLabel5.setText("Sex:");

        jTextFieldLName.setToolTipText("");

        jButtonFetchData.setText("Fetch Data");
        jButtonFetchData.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonFetchDataActionPerformed(evt);
            }
        });

        jButtonInsert.setText("Insert");
        jButtonInsert.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonInsertActionPerformed(evt);
            }
        });

        jButton2Update.setText("Update");
        jButton2Update.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2UpdateActionPerformed(evt);
            }
        });

        jButtonDelete.setText("Delete");
        jButtonDelete.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonDeleteActionPerformed(evt);
            }
        });

        jComboBox1.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Male", "Female" }));

        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(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                .addGroup(layout.createSequentialGroup()
                                    .addComponent(jLabel2)
                                    .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                    .addComponent(jTextFieldName, javax.swing.GroupLayout.PREFERRED_SIZE, 80, javax.swing.GroupLayout.PREFERRED_SIZE))
                                .addGroup(layout.createSequentialGroup()
                                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                        .addComponent(jLabel4)
                                        .addComponent(jLabel3))
                                    .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                        .addComponent(jTextFieldLName)
                                        .addComponent(jTextFieldAge))))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel5)
                                .addGap(18, 18, 18)
                                .addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jButton2Update, javax.swing.GroupLayout.Alignment.TRAILING)
                            .addComponent(jButtonInsert)
                            .addComponent(jButtonDelete)))
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, 452, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(66, 66, 66)
                .addComponent(jButtonFetchData)
                .addContainerGap(71, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 183, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButtonFetchData))
                .addGap(11, 11, 11)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jButtonInsert)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton2Update)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonDelete))
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel2)
                            .addComponent(jTextFieldName, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel3)
                            .addComponent(jTextFieldLName, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel4)
                            .addComponent(jTextFieldAge, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel5)
                            .addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))))
                .addContainerGap(57, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButtonFetchDataActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonFetchDataActionPerformed
        filltable();
    }//GEN-LAST:event_jButtonFetchDataActionPerformed
    private void jTable1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable1MouseClicked
        // TODO add your handling code here:
        String select = jTable1.getValueAt(jTable1.getSelectedRow(), 0).toString();
        try {
            baglantiAc();
        } catch (Exception ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
              
           Statement1 = conn.prepareStatement("select * from people where id=?");
           Statement1.setString(1, select);
           ResultSet res1 = Statement1.executeQuery();
             if (res1.next()) {
                    jTextFieldName.setText(res1.getString("name"));
                    jTextFieldLName.setText(res1.getString("Lname"));
                    jTextFieldAge.setText(res1.getString("age"));
                    if (res1.getInt("sex")==0) {
                     jComboBox1.setSelectedItem("Male");
                 }else{  
                    jComboBox1.setSelectedItem("Female");
                    }
             }
        } catch (SQLException ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
    }//GEN-LAST:event_jTable1MouseClicked

    private void jButtonDeleteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonDeleteActionPerformed
                  try {
            baglantiAc();
        } catch (Exception ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
           String select = jTable1.getValueAt(jTable1.getSelectedRow(), 0).toString();
           Statement1 = conn.prepareStatement("DELETE FROM test.people WHERE id=?");
           Statement1.setString(1, select);
           Statement1.execute();
        } catch (Exception ex) {
 String Error="Row is not selected for deleting operation.Please select data on the table.";
JOptionPane.showMessageDialog(null,Error);
        }
        filltable();
        jTextFieldName.setText("");
        jTextFieldLName.setText("");
        jTextFieldAge.setText("");
        
    }//GEN-LAST:event_jButtonDeleteActionPerformed

    private void jButton2UpdateActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2UpdateActionPerformed
            try {
                
            baglantiAc();
        } catch (Exception ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //burada tablodan seçilen değerin değerini string olarak aldık.
           String select = jTable1.getValueAt(jTable1.getSelectedRow(), 0).toString();
           Statement1 = conn.prepareStatement("Update people SET name=?,Lname=?,age=?,sex=? where id=?");
           // burada sql e join yapmak için parametre yolladık. (id)
           Statement1.setString(5, select);
           Statement1.setString(1, jTextFieldName.getText());
           Statement1.setString(2, jTextFieldLName.getText());
           Statement1.setString(3, jTextFieldAge.getText());
           int comboboxselectedindex = jComboBox1.getSelectedIndex();
           Statement1.setString(4, String.valueOf(comboboxselectedindex));
           Statement1.execute();
        } catch (Exception ex) {
             String Error="Data is not selected for updating operation.Please select data on the table.";
JOptionPane.showMessageDialog(null,Error);
        }   
                filltable();
        
        
    }//GEN-LAST:event_jButton2UpdateActionPerformed

    private void jButtonInsertActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonInsertActionPerformed
 try {
            baglantiAc();
        } catch (Exception ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
           Statement1 = conn.prepareStatement("INSERT INTO people (`ID`, `name`, `Lname`, `age`, `sex`) VALUES (NULL,?,?,?,?)");
          // sql ifadesine parametreleri ekliyoruz.
           Statement1.setString(1, jTextFieldName.getText());
           Statement1.setString(2, jTextFieldLName.getText());
           Statement1.setString(3, jTextFieldAge.getText());
           int comboboxselectedindex = jComboBox1.getSelectedIndex();
           Statement1.setString(4, String.valueOf(comboboxselectedindex));
           Statement1.execute();
        }catch (Exception ex) {
 String Error="Please Fill empty TextField area.";
JOptionPane.showMessageDialog(null,Error);
        }  
        
                filltable();
    
    }//GEN-LAST:event_jButtonInsertActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        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 | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(DbProjectGui.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 DbProjectGui().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton2Update;
    private javax.swing.JButton jButtonDelete;
    private javax.swing.JButton jButtonFetchData;
    private javax.swing.JButton jButtonInsert;
    private javax.swing.JComboBox jComboBox1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable jTable1;
    private javax.swing.JTextField jTextFieldAge;
    private javax.swing.JTextField jTextFieldLName;
    private javax.swing.JTextField jTextFieldName;
    // End of variables declaration//GEN-END:variables

    private void filltable() { 
    try {
            baglantiAc();
        } catch (Exception ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            res = command.executeQuery("select * from people");
        } catch (SQLException ex) {
            Logger.getLogger(DbProjectGui.class.getName()).log(Level.SEVERE, null, ex);
        }
        
                 jTable1.setModel(DbUtils.resultSetToTableModel(res));
    }

}

 

Kodun içerisinde bazı sql sorgularına parametre yollamamız gerekiyordu. Örneğin kişinin bilgilerini güncelleyeceksek, bu kişinin id bilgisi elimizde olması gerekiyorki database’e gönderirken o kişinin bilgilerini güncellediğimizden emin olalım.

String select = jTable1.getValueAt(jTable1.getSelectedRow(), 0).toString();           
Statement1 = conn.prepareStatement("select * from people where id=?");
           Statement1.setString(1, select);
           ResultSet res1 = Statement1.executeQuery();

örneğin bu kodda string olarak tanıttığımız selectin içerisine program üzerindeki tabloda hangi kişinin üzerine tıkladıysak o kişinin id bilgisini göndermiş olduk.
jTable1.getSelectedRow() satır sayısını verir 0 değeri ise sütün oluyor. dolayısınla ilk sütünde id bilgisi olduğundan hangi kişiye tıklarsak onun id bilgisi select değişkenine yerleşmiş oluyor.
res1 objesinede bu sorgunun sonucunu atmış olduk.
Bu sorgunun ardından result setin içerisinde sütün bazında kişinin ismi, soyismi, yaşı ve cinsiyet bilgisi yerleşti. If döngüsü ile bu bilgileri textfieldlara gönderme işlemide şu şekildedir;

if (res1.next()) {
                    jTextFieldName.setText(res1.getString("name"));
                    jTextFieldLName.setText(res1.getString("Lname"));
                    jTextFieldAge.setText(res1.getString("age"));
                    if (res1.getInt("sex")==0) {
                     jComboBox1.setSelectedItem("Male");
                 }else{  
                    jComboBox1.setSelectedItem("Female");
                    }
             }

Unutmadan veritabanına oluşturduğumuz people tablosunun şeması şu şekildedir;

create table people(
id int NOT NULL AUTO_INCREMENT,
name varchar(50),
Lname varchar(50),
age int,
sex int(10),
Primary key(id)
)

Program ile ilgili bazı görseller;

java ile veritabanı bağlantısı_1

java ile veritabanı bağlantısı_2

java ile veritabanı bağlantısı_3

java ile veritabanı bağlantısı_4

Programın kodları hakkında aklınıza takılan herşeyi sorabilirsiniz.

Emre Bektaş

Computer Engineer C# / Java Developer

You may also like...

Leave a Reply

Your email address will not be published.