Курсовой проект
по базам данных
Тема: «Книжные магазины»
Этапы выполнения курсового проекта:
1. Постановка цели и задачи
2. Создание модели базы данных в среде MySQL Workbench
3. Создание и заполнение базы данных в СУБД SQLite Manager
4. Разработка консольного приложения, выводящего запрашиваемую таблицу, в середе разработки NetBeans IDE
5. Разработка GUI приложения в среде NetBeans IDE
1. Цели и задачи БД
Цель: создание приложения для составления учета количества книг и покупателей.
Функционал:
Хранение и изменение информации о магазинах, книгах и покупателях
Упрощение организации работы магазинов с клиентами
Учет данных для составления дальнейшей статистики
Оперативная корректировка данных при возникновении необходимости в этом
2. Модель БД (выполнена в среде MySQL Workbench)
3. Скрипт создания и заполнения БД (в SQLite Manager)
PRAGMA foreign_keys=ON;
CREATE TABLE IF NOT EXISTS Sales (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES Books,
employee_id INTEGER NOT NULL REFERENCES Employees,
customer_id INTEGER NOT NULL REFERENCES Customers,
shop_id INTEGER NOT NULL REFERENCES Shops,
date_of_selling DATE
);
CREATE TABLE IF NOT EXISTS Books (
id INTEGER NOT NULL PRIMARY KEY,
title VARCHAR DEFAULT ‘’,
author_id INTEGER NOT NULL REFERENCES Authors,
year_of_publication INTEGER,
publishing_house VARCHAR DEFAULT ‘’,
place_of_publication VARCHAR DEFAULT ‘’,
number_of_pages INTEGER NOT NULL,
cover_type VARCHAR DEFAULT ‘’,
price DOUBLE CHECK(price0),
language VARCHAR DEFAULT ‘’,
isbn VARCHAR DEFAULT ‘’ UNIQUE
);
CREATE TABLE IF NOT EXISTS Authors (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR DEFAULT ‘’,
bday DATE
);
CREATE TABLE IF NOT EXISTS Employees (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR DEFAULT ‘’,
bday DATE,
shop_number INTEGER CHECK(1
);
CREATE TABLE IF NOT EXISTS Customers (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR DEFAULT ‘’,
bday DATE
);
CREATE TABLE IF NOT EXISTS Categories (
id INTEGER NOT NULL PRIMARY KEY,
category VARCHAR DEFAULT ‘’
);
CREATE TABLE IF NOT EXISTS Shops (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR DEFAULT ‘’,
address VARCHAR UNIQUE DEFAULT ‘’,
phone VARCHAR UNIQUE DEFAULT ‘’,
shopping_hours VARCHAR DEFAULT ‘’
);
CREATE TABLE IF NOT EXISTS BookCount (
id INTEGER NOT NULL PRIMARY KEY,
shop_id INTEGER NOT NULL REFERENCES Shops,
book_id INTEGER NOT NULL REFERENCES Books,
count INTEGER CHECK(count=0)
);
CREATE TABLE IF NOT EXISTS BookCategory (
book_id INTEGER NOT NULL REFERENCES Books,
category_id INTEGER NOT NULL REFERENCES Categories
);
INSERT INTO Authors ("id","name","bday")
VALUES("1","Пушкин Александр Сергеевич","06.06.1799");
INSERT INTO [Authors]
VALUES("2","Лермонтов Михаил Юрьевич","15.10.1814");
INSERT INTO [Authors]
VALUES("3","Достоевский Федор Михайлович","11.11.1821");
INSERT INTO [Authors]
VALUES("4","Шарлотта Бронте","21.04.1816");
INSERT INTO [Authors]
VALUES("5","Сомерсет Моэм","25.01.1874");
INSERT INTO [Authors]
VALUES("6","Волков Александр Мелентьевич","14.06.1891");
INSERT INTO [Authors]
VALUES("7","Драгунский Виктор Юзефович","30.11.1913");
INSERT INTO [Authors]
VALUES("8","Холодова Елена Александровна","21.03.1954");
INSERT INTO [Employees]
VALUES("1","Иванов","12.12.1976","1");
INSERT INTO [Employees]
VALUES("2","Петров","21.03.1983","1");
INSERT INTO [Employees]
VALUES("3","Сидоров","18.04.1974","1");
INSERT INTO [Employees]
VALUES("4","Башаров","29.02.1988","2");
INSERT INTO [Employees]
VALUES("5","Закирова","03.11.1993","2");
INSERT INTO [Employees]
VALUES("6","Михайлов","02.05.1994","3");
INSERT INTO [Employees]
VALUES("7","Шарафиева","19.01.1978","3");
INSERT INTO [Employees]
VALUES("8","Сафиуллин","17.07.1989","4");
INSERT INTO [Employees]
VALUES("9","Андреев","15.05.1977","4");
INSERT INTO [Employees]
VALUES("10","Михайлова","30.11.1967","4");
INSERT INTO [Customers]
VALUES("1","Семёнов","01.09.1953");
INSERT INTO [Customers]
VALUES("2","Серпокрылов","28.06.1970");
INSERT INTO [Customers]
VALUES("3","Давлетгараев","23.08.1988");
INSERT INTO [Customers]
VALUES("4","Файзрахманова","15.09.1993");
INSERT INTO [Customers]
VALUES("5","Петров","12.02.1986");
INSERT INTO [Customers]
VALUES("6","Мифтахова","23.08.1993");
INSERT INTO [Customers]
VALUES("7","Кузьмина","05.05.1995");
INSERT INTO [Customers]
VALUES("8","Колунчукова","09.09.1999");
INSERT INTO [Customers]
VALUES("9","Гараев","21.04.1978");
INSERT INTO [Customers]
VALUES("10","Сибгатуллина","14.11.1982");
INSERT INTO [Categories]
VALUES("1","Учебник");
INSERT INTO [Categories]
VALUES("2","Справочник");
INSERT INTO [Categories]
VALUES("3","Словарь");
INSERT INTO [Categories]
VALUES("4","Зарубежная литература");
INSERT INTO [Categories]
VALUES("5","Поэзия");
INSERT INTO [Categories]
VALUES("6","Русская литература");
INSERT INTO [Categories]
VALUES("7","Детская литература");
INSERT INTO [Categories]
VALUES("8","Кулинария");
INSERT INTO [Categories]
VALUES("9","Медицина");
INSERT INTO [Categories]
VALUES("10","Программирование");
INSERT INTO [Books]
VALUES("1","Дубровский","1","2011","Принт-Сервис","Москва","205","Твердый","266", "Русский","5-08-004074-2");
INSERT INTO [Books]
VALUES("2","Герой нашего времени", "2", "2000", "Детская литература", "Москва", "486", "Твердый", "513", "Русский", "5-08-003916-7");
INSERT INTO [Books]
VALUES("3", "Демон", "2", "2006", "Эксмо", "Санкт-Петербург", "187", "Мягкий", "256,75", "Русский", "5-699-17800-7");
INSERT INTO [Books]
VALUES("4", "Преступление и наказание", "3", "2011", "Азбука", "Казань", "399", "Твердый", "645,84", "Русский", "978-5-389-02279-9");
INSERT INTO [Books]
VALUES("5", "Джейн эйр", "4", "2001", "АСТ", "Москва", "682", "Мягкий", "876", "Английский", "5-17-007262-7");
INSERT INTO [Books]
VALUES("6", "Театр", "5", "2009", "АСТ", "Москва", "553", "Твердый", "314", "Перевод с английского на русский", "978-5-17-066013-1");
INSERT INTO [Books]
VALUES("7", "Волшебник изумрудного города", "6", "2001", "Мир искателя", "Москва", "172", "Твердый", "195,5", "Русский", "5-93833-097-1");
INSERT INTO [Books]
VALUES("8", "Денискины рассказы", "7", "2009", "Росмэн-Пресс", "Санкт-Петербург", "214", "Мягкий", "203", "Русский", "978-5-353-03868-9");
INSERT INTO [Books]
VALUES("9", "Дерматология", "8", "2013", "Принт16", "Казань", "814", "Твердый", "1052", "Русский", "978-5-271-28805-0");
INSERT INTO [Shops]
VALUES("1", "Казанский букинист", "Казань, ул. Карла Маркса, 53а", "+7 (843) 264-25-20", "Пн-Пт. 8.00-18.00 Сб. 10.00-17.00");
INSERT INTO [Shops]
VALUES("2", "Акцент", "Казань, ул. Восстания, 62", "+7 (843) 524-40-15", "Пн-Сб. 9.00-19.00");
INSERT INTO [Shops]
VALUES("3", "Книжный ряд", "Казань, ул. Мартына Межлаука, 6", "+7 (843) 292-07-77", "Пн-Сб. 9.00-19.00 Вс. 9.00-18.00");
INSERT INTO [Shops]
VALUES("4", "Любимый книжный", "Казань, ул. Московская, 44", "+7 (843) 292-05-04" , "Пн-Пт. 9.00-19.00 Сб-Вс. 9.00-18.00");
INSERT INTO [BookCount]
VALUES("1","1","1","12");
INSERT INTO [BookCount]
VALUES("2","1","2","0");
INSERT INTO [BookCount]
VALUES("3","1","3","25");
INSERT INTO [BookCount]
VALUES("4","1","4","16");
INSERT INTO [BookCount]
VALUES("5","1","5","7");
INSERT INTO [BookCount]
VALUES("6","1","6","2");
INSERT INTO [BookCount]
VALUES("7","1","7","54");
INSERT INTO [BookCount]
VALUES("8","1","8","28");
INSERT INTO [BookCount]
VALUES("9","1","9","3");
INSERT INTO [BookCount]
VALUES("10","2","1","10");
INSERT INTO [BookCount]
VALUES("11","2","2","13");
INSERT INTO [BookCount]
VALUES("12","2","3","4");
INSERT INTO [BookCount]
VALUES("13","2","4","18");
INSERT INTO [BookCount]
VALUES("14","2","5","6");
INSERT INTO [BookCount]
VALUES("15","2","6","9");
INSERT INTO [BookCount]
VALUES("16","2","7","23");
INSERT INTO [BookCount]
VALUES("17","2","8","0");
INSERT INTO [BookCount]
VALUES("18","2","9","15");
INSERT INTO [BookCount]
VALUES("19","3","1","0");
INSERT INTO [BookCount]
VALUES("20","3","2","60");
INSERT INTO [BookCount]
VALUES("21","3","3","29");
INSERT INTO [BookCount]
VALUES("22","3","4","19");
INSERT INTO [BookCount]
VALUES("23","3","5","13");
INSERT INTO [BookCount]
VALUES("24","3","6","12");
INSERT INTO [BookCount]
VALUES("25","3","7","25");
INSERT INTO [BookCount]
VALUES("26","3","8","11");
INSERT INTO [BookCount]
VALUES("27","3","9","21");
INSERT INTO [BookCount]
VALUES("28","4","1","5");
INSERT INTO [BookCount]
VALUES("29","4","2","11");
INSERT INTO [BookCount]
VALUES("30","4","3","0");
INSERT INTO [BookCount]
VALUES("31","4","4","32");
INSERT INTO [BookCount]
VALUES("32","4","5","20");
INSERT INTO [BookCount]
VALUES("33","4","6","16");
INSERT INTO [BookCount]
VALUES("34","4","7","12");
INSERT INTO [BookCount]
VALUES("35","4","8","18");
INSERT INTO [BookCount]
VALUES("36","4","9","15");
INSERT INTO [BookCategory]
VALUES("1","6");
INSERT INTO [BookCategory]
VALUES("2","6");
INSERT INTO [BookCategory]
VALUES("3","6");
INSERT INTO [BookCategory]
VALUES("4","6");
INSERT INTO [BookCategory]
VALUES("5","4");
INSERT INTO [BookCategory]
VALUES("6","4");
INSERT INTO [BookCategory]
VALUES("7","7");
INSERT INTO [BookCategory]
VALUES("8","7");
INSERT INTO [BookCategory]
VALUES("9","9");
INSERT INTO [BookCategory]
VALUES("9","2");
INSERT INTO [Sales]
VALUES("1","1","2","3","1","8.10.2013");
INSERT INTO [Sales]
VALUES("2","3","1","4","1","21.09.2013");
INSERT INTO [Sales]
VALUES("3","9","6","8","3","7.10.2013");
4. Разработка консольного приложения в среде IDE NetBeans
Приложение выводит в консоль пронумерованный список таблиц.
Пользователь вводит номер требуемой таблицы, и она выводится на экран.
/*JavaApplication1.java*/
package javaapplication1;
import java.sql.ResultSet;
import javax.swing.table.*;
import java.sql.*;
import java.util.*;
public class JavaApplication1 {
private static String dbURL = "jdbc:derby://localhost:1527/Sales_Books;create=true";
private static String tableName;
private static Connection conn = null;
private static Statement stmt = null;
public static void main(String[] args)
{
createConnection();
List list = new ArrayList();
getTables(list);
System.out.println("Введите номер:");
Scanner sc = new Scanner(System.in);
int num = sc.nextInt();
selectTable(num, list);
shutdown();
}
private static void createConnection()
{
try
{
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
conn = DriverManager.getConnection(dbURL);
}
catch (Exception except)
{
except.printStackTrace();
}
}
public static void getTables(List list)
{
try
{
DatabaseMetaData dbmd = conn.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", types);
int i = 1;
while (rs.next()) {
System.out.println(i++ + ". " + rs.getString(3));
list.add(rs.getString(3));
}
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
private static void selectTable (int num, List list)
{
try
{
stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("select * from " + list.get(num-1));
ResultSetMetaData rsmd = results.getMetaData();//?
int numberCols = rsmd.getColumnCount();
for (int i=1; i
{
//print Column Names
System.out.print(rsmd.getColumnLabel(i)+"\t\t");
}
System.out.println("\n-------------------------------------------------");
while(results.next()) {
for(int i=1;i
{
System.out.print(results.getString(i) + "\t\t" );
}
System.out.println();
}
results.close();
stmt.close();
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
private static void shutdown()
{
try
{
if (stmt != null)
{
stmt.close();
}
if (conn != null)
{
DriverManager.getConnection(dbURL + ";shutdown=true");
conn.close();
}
}
catch (SQLException sqlExcept)
{
}
}
}
Примеры выполнения (вывод списка таблиц и вывод требуемой таблицы):
5. Создание GUI приложения в среде NetBeans IDE
Стартовый вид приложения:
Выбрали таблицу «Authors»:
После нажатия кнопки «Редактировать» можем изменять данные и заносить изменения в БД с помощью кнопки «Сохранить»:
С помощью кнопки «Добавить новую запись» добавляем новую запись в БД:
Пример удаления строки с id=4
/*Реализация абстрактного класса Model*/
/*
* 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 javaapplication1;
import java.sql.ResultSet;
import javax.swing.table.*;
import java.sql.*;
import java.util.*;
//package com.porty.swing;
public class Model extends AbstractTableModel {
private ArrayList columnNames = new ArrayList(); // названия столбцов
private ArrayList columnTypes = new ArrayList(); // список типов столбцов
// хранилище для полученных данных из базы данных
private ArrayList data = new ArrayList();
private boolean editable; // признак редактирования таблицы
// конструктор позволяет задать возможность редактирования
public Model(boolean editable) {
this.editable = editable;
}
public int getRowCount() { // количество строк
return data.size();
}
public int getColumnCount() {// количество столбцов
return columnNames.size();
}
public Class getColumnClass(int column) {// тип данных столбца
return columnTypes.get(column);
}
public String getColumnName(int column) {// название столбца
return columnNames.get(column);
}
public Object getValueAt(int row, int column) {// данные в ячейке
return (data.get(row)).get(column);
}
public boolean isCellEditable(int row, int column) {// возможность редактирования ячейки
return editable;
}
public void setValueAt(// замена значения ячейки
Object value, int row, int column){
(data.get(row)).set(column, value);
}
public void setDataSource(ResultSet rs) throws Exception {// получение данных из объекта ResultSet
data.clear();
columnNames.clear();
columnTypes.clear();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for ( int i=0; i
columnNames.add(rsmd.getColumnName(i+1));
columnTypes.add(Class.forName(rsmd.getColumnClassName(i+1)));
}
// получаем данные
while ( rs.next() ) {
ArrayListObject row = new ArrayListObject();// здесь будем хранить ячейки одной строки
for ( int i=0; i
row.add(rs.getObject(i+1));
}
data.add(row);
}
// сообщаем об изменениях в структуре данных
fireTableStructureChanged();
}
}
/*MainFrame.java*/
/*
* 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 javaapplication1;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JScrollPane;
/*import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;*/
/**
*
* @author Aisina
*/
public class MainFrame extends javax.swing.JFrame {
/**
* Creates new form MainFrame
*/
static Connection conn;
static Statement stmt;
static String dbURL="jdbc:derby://localhost:1527/Sales_Books";
public void tableContentChange(String tableName, Boolean editable) {
try {
conn = DriverManager.getConnection(dbURL);
stmt = conn.createStatement();
ResultSet rs;
rs = stmt.executeQuery("select * from "+ tableName);
Model dbm = new Model(editable);
try {
dbm.setDataSource(rs);
}
catch (Exception ex) {
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
jTable1.setShowVerticalLines(rootPaneCheckingEnabled);
jTable1.setModel(dbm); //jTable1-таблица-туда set dbm
jTable1.setVisible(true);
}
catch (SQLException ex)
{
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
}
public MainFrame() {
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")
//
private void initComponents() {
jScrollPane1 = new javax.swing.JScrollPane();
jTable1 = new javax.swing.JTable();
jComboBox1 = new javax.swing.JComboBox();
jButton2 = new javax.swing.JButton();
jButton3 = new javax.swing.JButton();
jButton4 = new javax.swing.JButton();
jButton1 = new javax.swing.JButton();
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
jTable1.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 0, 0)));
jTable1.setFont(new java.awt.Font("Tahoma", 1, 11)); // NOI18N
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},
{null, null, null, null},
{null, null, null, null},
{null, null, null, null},
{null, null, null, null},
{null, null, null, null},
{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"
}
));
jScrollPane1.setViewportView(jTable1);
jComboBox1.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Выберите название таблицы", "AUTHORS", "BOOKCATEGORY", "BOOKCOUNT", "BOOKS", "CATEGORIES", "CUSTOMERS", "EMPLOYEES", "SALES", "SHOPS" }));
jComboBox1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jComboBox1ActionPerformed(evt);
}
});
jButton2.setText("Добавить новую запись");
jButton2.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jButton2ActionPerformed(evt);
}
});
jButton3.setText("Удалить запись");
jButton3.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jButton3ActionPerformed(evt);
}
});
jButton4.setText("Сохранить");
jButton4.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jButton4ActionPerformed(evt);
}
});
jButton1.setText("Редактировать");
jButton1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jButton1ActionPerformed(evt);
}
});
javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addGap(28, 28, 28)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jButton2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(jComboBox1, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(jButton3, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(jButton4, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
.addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 886, Short.MAX_VALUE)
.addContainerGap())
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(32, 32, 32)
.addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(30, 30, 30)
.addComponent(jButton2)
.addGap(18, 18, 18)
.addComponent(jButton1)
.addGap(16, 16, 16)
.addComponent(jButton4)
.addGap(18, 18, 18)
.addComponent(jButton3)
.addContainerGap(116, Short.MAX_VALUE))
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
);
pack();
}//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//ComboBox
private void jComboBox1ActionPerformed(java.awt.event.ActionEvent evt) {
tableContentChange(jComboBox1.getSelectedItem().toString(),false);
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Button2 – добавить
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
try { conn = DriverManager.getConnection(dbURL);
stmt=conn.createStatement();
String TableName = jComboBox1.getSelectedItem().toString();
ResultSet rs = stmt.executeQuery("select * from " + TableName );
ResultSetMetaData rsmd = rs.getMetaData();
javaapplication1.Model tm = new javaapplication1.Model(true);
try {
tm.setDataSource(rs);
} catch (Exception ex) {
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
int numberCols = rsmd.getColumnCount();
String s="INSERT INTO "+ TableName +" VALUES ( ";
s=s+((int)jTable1.getValueAt(jTable1.getRowCount()-1,0)+1);
for(int i=1; i
{
if((jTable1.getColumnClass(i)==Integer.class)) s=s+" , "+jTable1.getValueAt(jTable1.getRowCount()-1,i);
else s=s+" , '"+jTable1.getValueAt(jTable1.getRowCount()-1,i)+"'";
}
s=s+" )";
stmt.executeUpdate(s);
}
catch (SQLException ex) {
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
tableContentChange(jComboBox1.getSelectedItem().toString(),false);
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Button3 - удалить
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {
try {
conn = DriverManager.getConnection(dbURL);
stmt = conn.createStatement(); // выражение
ResultSet rs = stmt.executeQuery("select * from " + jComboBox1.getSelectedItem().toString() );
javaapplication1.Model tm = new javaapplication1.Model(false);
try {
tm.setDataSource(rs);
} catch (Exception ex) {
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
String n=tm.getValueAt(jTable1.getSelectedRow(), 0).toString();
stmt.executeUpdate("delete from " + jComboBox1.getSelectedItem().toString() + " where " + jTable1.getColumnName(0) + " = " + n);
tableContentChange(jComboBox1.getSelectedItem().toString(),false); //обновление
}
catch (SQLException ex) {
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Button4 - сохранить
private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {
try
{ conn = DriverManager.getConnection(dbURL);
stmt = conn.createStatement();
String TableName = jComboBox1.getSelectedItem().toString();
ResultSet rs;
rs = stmt.executeQuery("select * from " + TableName);
Model tm = new Model(false);
try {
tm.setDataSource(rs);
} catch (Exception ex) {
Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
}
int NumberOfString = jTable1.getSelectedRow();
String value = jTable1.getValueAt(NumberOfString, 0).toString();
stmt.executeUpdate(" UPDATE " + TableName + " SET " + jTable1.getColumnName(0) + "= " + value + " WHERE " + jTable1.getColumnName(0) + "= " +
tm.getValueAt(NumberOfString, 0).toString());
}
catch (Exception ex)
{
System.err.println(ex);
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Button1 - редактирование
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
tableContentChange(jComboBox1.getSelectedItem().toString(),true);
}
/**
* @param args the command line arguments
*/
public static void main(String args[]) { //выдает экранную форму????
/* Set the Nimbus look and feel */
//
/* 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(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
java.util.logging.Logger.getLogger(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
java.util.logging.Logger.getLogger(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {
java.util.logging.Logger.getLogger(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
}
//
/* Create and display the form */
//след. try - для интерфейса
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(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
java.util.logging.Logger.getLogger(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
java.util.logging.Logger.getLogger(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {
java.util.logging.Logger.getLogger(MainFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
}
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new MainFrame().setVisible(true);
}
});
}
// Variables declaration - do not modify
private javax.swing.JButton jButton1;
private javax.swing.JButton jButton2;
private javax.swing.JButton jButton3;
private javax.swing.JButton jButton4;
private javax.swing.JComboBox jComboBox1;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JTable jTable1;
// End of variables declaration
}
Получите свидетельство
Вход
Материал по информатике "IDE Net Beans: разработка приложения" (0.43 MB)
0
408
37
Нравится
0
