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;
Programın kodları hakkında aklınıza takılan herşeyi sorabilirsiniz.
