Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Міністерство освіти і науки, молоді та спорту України
Тернопільський національний технічний університет
імені Івана Пулюя
Кафедра програмної інженерії
Лабораторна робота №10
з дисципліни: Бази даних
тема: Розробка програми на мові Java
для роботи з MySql
Підготувала:
студентка групи СП-31
Євчин Ольга
Тернопіль-2011
Мета роботи: набути навичок по написанню програм на мові Java, які взаємодіють з сервером MySQL при допомозі JDBC.
Завдання:
Розробити для БД клієнтську програму з графічним інтерфейсом (GUI), яка підключатиметься до сервера БД і на вимогу користувача, може здійснює вибірку даних з БД та додавати/видаляти/змінювати записи.
Теоретичні відомості
JDBC (Java DataBase Connectivity) - це стандартний інтерфейс Java, призначений для взаємодії з базами даних. Він підтримує роботу як з реляційними базами даних, так і з простими табличними даними. Інтерфейсами JDBC користуються як прикладні програмісти, так і розробники драйверів баз даних.
Код класу-сутності Employee:
@Entity
@Table(name = "employees", catalog = "classicmodels", schema = "")
@NamedQueries({
@NamedQuery(name = "Employees.findAll", query = "SELECT e FROM Employees e"),
@NamedQuery(name = "Employees.findByEmployeeNumber", query = "SELECT e FROM Employees e WHERE e.employeeNumber = :employeeNumber"),
@NamedQuery(name = "Employees.findByLastName", query = "SELECT e FROM Employees e WHERE e.lastName = :lastName"),
@NamedQuery(name = "Employees.findByFirstName", query = "SELECT e FROM Employees e WHERE e.firstName = :firstName"),
@NamedQuery(name = "Employees.findByExtension", query = "SELECT e FROM Employees e WHERE e.extension = :extension"),
@NamedQuery(name = "Employees.findByEmail", query = "SELECT e FROM Employees e WHERE e.email = :email"),
@NamedQuery(name = "Employees.findByOfficeCode", query = "SELECT e FROM Employees e WHERE e.officeCode = :officeCode"),
@NamedQuery(name = "Employees.findByReportsTo", query = "SELECT e FROM Employees e WHERE e.reportsTo = :reportsTo"),
@NamedQuery(name = "Employees.findByJobTitle", query = "SELECT e FROM Employees e WHERE e.jobTitle = :jobTitle")})
public class Employees implements Serializable {
@Transient
private PropertyChangeSupport changeSupport = new PropertyChangeSupport(this);
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name = "employeeNumber")
private Integer employeeNumber;
@Basic(optional = false)
@Column(name = "lastName")
private String lastName;
@Basic(optional = false)
@Column(name = "firstName")
private String firstName;
@Basic(optional = false)
@Column(name = "extension")
private String extension;
@Basic(optional = false)
@Column(name = "email")
private String email;
@Basic(optional = false)
@Column(name = "officeCode")
private String officeCode;
@Column(name = "reportsTo")
private Integer reportsTo;
@Basic(optional = false)
@Column(name = "jobTitle")
private String jobTitle;
public Employees() {
}
public Employees(Integer employeeNumber) {
this.employeeNumber = employeeNumber;
}
public Employees(Integer employeeNumber, String lastName, String firstName, String extension, String email, String officeCode, String jobTitle) {
this.employeeNumber = employeeNumber;
this.lastName = lastName;
this.firstName = firstName;
this.extension = extension;
this.email = email;
this.officeCode = officeCode;
this.jobTitle = jobTitle;
}
public Integer getEmployeeNumber() {
return employeeNumber;
}
public void setEmployeeNumber(Integer employeeNumber) {
Integer oldEmployeeNumber = this.employeeNumber;
this.employeeNumber = employeeNumber;
changeSupport.firePropertyChange("employeeNumber", oldEmployeeNumber, employeeNumber);
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
String oldLastName = this.lastName;
this.lastName = lastName;
changeSupport.firePropertyChange("lastName", oldLastName, lastName);
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
String oldFirstName = this.firstName;
this.firstName = firstName;
changeSupport.firePropertyChange("firstName", oldFirstName, firstName);
}
public String getExtension() {
return extension;
}
public void setExtension(String extension) {
String oldExtension = this.extension;
this.extension = extension;
changeSupport.firePropertyChange("extension", oldExtension, extension);
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
String oldEmail = this.email;
this.email = email;
changeSupport.firePropertyChange("email", oldEmail, email);
}
public String getOfficeCode() {
return officeCode;
}
public void setOfficeCode(String officeCode) {
String oldOfficeCode = this.officeCode;
this.officeCode = officeCode;
changeSupport.firePropertyChange("officeCode", oldOfficeCode, officeCode);
}
public Integer getReportsTo() {
return reportsTo;
}
public void setReportsTo(Integer reportsTo) {
Integer oldReportsTo = this.reportsTo;
this.reportsTo = reportsTo;
changeSupport.firePropertyChange("reportsTo", oldReportsTo, reportsTo);
}
public String getJobTitle() {
return jobTitle;
}
public void setJobTitle(String jobTitle) {
String oldJobTitle = this.jobTitle;
this.jobTitle = jobTitle;
changeSupport.firePropertyChange("jobTitle", oldJobTitle, jobTitle);
}
@Override
public int hashCode() {
int hash = 0;
hash += (employeeNumber != null ? employeeNumber.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Employees)) {
return false;
}
Employees other = (Employees) object;
if ((this.employeeNumber == null && other.employeeNumber != null) || (this.employeeNumber != null && !this.employeeNumber.equals(other.employeeNumber))) {
return false;
}
return true;
}
@Override
public String toString() {
return "db10.Employees[ employeeNumber=" + employeeNumber + " ]";
}
public void addPropertyChangeListener(PropertyChangeListener listener) {
changeSupport.addPropertyChangeListener(listener);
}
public void removePropertyChangeListener(PropertyChangeListener listener) {
changeSupport.removePropertyChangeListener(listener);
}
}
Код головної форми:
public class DB10View extends FrameView {
public DB10View(SingleFrameApplication app) {
super(app);
initComponents();
// status bar initialization - message timeout, idle icon and busy animation, etc
ResourceMap resourceMap = getResourceMap();
int messageTimeout = resourceMap.getInteger("StatusBar.messageTimeout");
messageTimer = new Timer(messageTimeout, new ActionListener() {
public void actionPerformed(ActionEvent e) {
statusMessageLabel.setText("");
}
});
messageTimer.setRepeats(false);
int busyAnimationRate = resourceMap.getInteger("StatusBar.busyAnimationRate");
for (int i = 0; i < busyIcons.length; i++) {
busyIcons[i] = resourceMap.getIcon("StatusBar.busyIcons[" + i + "]");
}
busyIconTimer = new Timer(busyAnimationRate, new ActionListener() {
public void actionPerformed(ActionEvent e) {
busyIconIndex = (busyIconIndex + 1) % busyIcons.length;
statusAnimationLabel.setIcon(busyIcons[busyIconIndex]);
}
});
idleIcon = resourceMap.getIcon("StatusBar.idleIcon");
statusAnimationLabel.setIcon(idleIcon);
progressBar.setVisible(false);
// connecting action tasks to status bar via TaskMonitor
TaskMonitor taskMonitor = new TaskMonitor(getApplication().getContext());
taskMonitor.addPropertyChangeListener(new java.beans.PropertyChangeListener() {
public void propertyChange(java.beans.PropertyChangeEvent evt) {
String propertyName = evt.getPropertyName();
if ("started".equals(propertyName)) {
if (!busyIconTimer.isRunning()) {
statusAnimationLabel.setIcon(busyIcons[0]);
busyIconIndex = 0;
busyIconTimer.start();
}
progressBar.setVisible(true);
progressBar.setIndeterminate(true);
} else if ("done".equals(propertyName)) {
busyIconTimer.stop();
statusAnimationLabel.setIcon(idleIcon);
progressBar.setVisible(false);
progressBar.setValue(0);
} else if ("message".equals(propertyName)) {
String text = (String)(evt.getNewValue());
statusMessageLabel.setText((text == null) ? "" : text);
messageTimer.restart();
} else if ("progress".equals(propertyName)) {
int value = (Integer)(evt.getNewValue());
progressBar.setVisible(true);
progressBar.setIndeterminate(false);
progressBar.setValue(value);
}
}
});
// tracking table selection
masterTable.getSelectionModel().addListSelectionListener(
new ListSelectionListener() {
public void valueChanged(ListSelectionEvent e) {
firePropertyChange("recordSelected", !isRecordSelected(), isRecordSelected());
}
});
// tracking changes to save
bindingGroup.addBindingListener(new AbstractBindingListener() {
@Override
public void targetChanged(Binding binding, PropertyStateEvent event) {
// save action observes saveNeeded property
setSaveNeeded(true);
}
});
// have a transaction started
entityManager.getTransaction().begin();
//tracking find button
findField.addKeyListener(new KeyListener() {
public void keyTyped(KeyEvent e) {
//throw new UnsupportedOperationException("Not supported yet.");
}
public void keyPressed(KeyEvent e) {
//throw new UnsupportedOperationException("Not supported yet.");
}
public void keyReleased(KeyEvent e) {
if(findField.getText().isEmpty() == false)
setFindEnabled(true);
else
setFindEnabled(false);
}
});
}
public boolean isSaveNeeded() {
return saveNeeded;
}
private void setSaveNeeded(boolean saveNeeded) {
if (saveNeeded != this.saveNeeded) {
this.saveNeeded = saveNeeded;
firePropertyChange("saveNeeded", !saveNeeded, saveNeeded);
}
}
private void setFindEnabled(boolean findEnabled){
if (findEnabled != this.findEnabled) {
this.findEnabled = findEnabled;
firePropertyChange("findEnabled", !findEnabled, findEnabled);
}
}
public boolean isRecordSelected() {
return masterTable.getSelectedRow() != -1;
}
public boolean isFindEnabled() {
return findEnabled;
}
@Action
public void newRecord() {
db10.Employees e = new db10.Employees();
entityManager.persist(e);
list.add(e);
int row = list.size()-1;
masterTable.setRowSelectionInterval(row, row);
masterTable.scrollRectToVisible(masterTable.getCellRect(row, 0, true));
setSaveNeeded(true);
}
@Action(enabledProperty = "recordSelected")
public void deleteRecord() {
int[] selected = masterTable.getSelectedRows();
List<db10.Employees> toRemove = new ArrayList<db10.Employees>(selected.length);
for (int idx=0; idx<selected.length; idx++) {
db10.Employees e = list.get(masterTable.convertRowIndexToModel(selected[idx]));
toRemove.add(e);
entityManager.remove(e);
}
list.removeAll(toRemove);
setSaveNeeded(true);
}
@Action(enabledProperty = "saveNeeded")
public Task save() {
return new SaveTask(getApplication());
}
private class SaveTask extends Task {
SaveTask(org.jdesktop.application.Application app) {
super(app);
}
@Override protected Void doInBackground() {
try {
entityManager.getTransaction().commit();
entityManager.getTransaction().begin();
} catch (RollbackException rex) {
rex.printStackTrace();
entityManager.getTransaction().begin();
List<db10.Employees> merged = new ArrayList<db10.Employees>(list.size());
for (db10.Employees e : list) {
merged.add(entityManager.merge(e));
}
list.clear();
list.addAll(merged);
}
return null;
}
@Override protected void finished() {
setSaveNeeded(false);
}
}
@Action
public Task refresh() {
return new RefreshTask(getApplication());
}
private class RefreshTask extends Task {
RefreshTask(org.jdesktop.application.Application app) {
super(app);
}
@SuppressWarnings("unchecked")
@Override protected Void doInBackground() {
try {
setProgress(0, 0, 4);
setMessage("Rolling back the current changes...");
setProgress(1, 0, 4);
entityManager.getTransaction().rollback();
Thread.sleep(1000L); // remove for real app
setProgress(2, 0, 4);
setMessage("Starting a new transaction...");
entityManager.getTransaction().begin();
Thread.sleep(500L); // remove for real app
setProgress(3, 0, 4);
setMessage("Fetching new data...");
java.util.Collection data = query.getResultList();
for (Object entity : data) {
entityManager.refresh(entity);
}
Thread.sleep(1300L); // remove for real app
setProgress(4, 0, 4);
Thread.sleep(150L); // remove for real app
list.clear();
list.addAll(data);
} catch(InterruptedException ignore) { }
return null;
}
@Override protected void finished() {
setMessage("Done.");
setSaveNeeded(false);
}
}
@Action
public void showAboutBox() {
if (aboutBox == null) {
JFrame mainFrame = DB10App.getApplication().getMainFrame();
aboutBox = new DB10AboutBox(mainFrame);
aboutBox.setLocationRelativeTo(mainFrame);
}
DB10App.getApplication().show(aboutBox);
}
@Action
public void showJDBCBox() {
if (JDBCBox == null) {
JFrame mainFrame = DB10App.getApplication().getMainFrame();
JDBCBox = new DB10JDBCBox(mainFrame);
JDBCBox.setLocationRelativeTo(mainFrame);
}
DB10App.getApplication().show(JDBCBox);
}
@Action(enabledProperty = "findEnabled")
public Task find() {
return new FindTask(getApplication());
}
private class FindTask extends org.jdesktop.application.Task<Object, Void> {
FindTask(org.jdesktop.application.Application app) {
super(app);
}
@Override protected Object doInBackground() {
String findCriteria = findComboBox.getSelectedItem().toString();
String findValue = findField.getText();
java.util.Collection data = null;
if(findCriteria == "employee number")
data = entityManager.createNamedQuery("Employees.findByEmployeeNumber")
.setParameter("employeeNumber", Integer.parseInt(findValue)).getResultList();
else if(findCriteria == "last name")
data = entityManager.createNamedQuery("Employees.findByLastName")
.setParameter("lastName", findValue).getResultList();
else if(findCriteria == "first name")
data = entityManager.createNamedQuery("Employees.findByFirstName")
.setParameter("firstName", findValue).getResultList();
else if(findCriteria == "extension")
data = entityManager.createNamedQuery("Employees.findByExtension")
.setParameter("extension", findValue).getResultList();
else if(findCriteria == "email")
data = entityManager.createNamedQuery("Employees.findByEmail")
.setParameter("email", findValue).getResultList();
else if(findCriteria == "office code")
data = entityManager.createNamedQuery("Employees.findByOfficeCode")
.setParameter("officeCode", findValue).getResultList();
else if(findCriteria == "reports to")
data = entityManager.createNamedQuery("Employees.findByReportsTo")
.setParameter("reportsTo", Integer.parseInt(findValue)).getResultList();
else if(findCriteria == "job title")
data = entityManager.createNamedQuery("Employees.findByJobTitle")
.setParameter("jobTitle", findValue).getResultList();
list.clear();
list.addAll(data);
return null; // return your result
}
@Override protected void succeeded(Object result) {
// Runs on the EDT. Update the GUI based on
// the result computed by doInBackground().
}
}
private final Timer messageTimer;
private final Timer busyIconTimer;
private final Icon idleIcon;
private final Icon[] busyIcons = new Icon[15];
private int busyIconIndex = 0;
private JDialog aboutBox;
private JDialog JDBCBox;
private boolean saveNeeded;
private boolean findEnabled;
Код JDBC форми:
public class DB10JDBCBox extends javax.swing.JDialog {
public DB10JDBCBox(java.awt.Frame parent) {
super(parent);
initComponents();
getRootPane().setDefaultButton(closeButton);
}
@Action public void closeAboutBox() {
dispose();
}
@Action
public void executeQuery() {
try {
// спробувати завантажити драйвер
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
// у випадку невдачі, друкуємо інформацію про виключну ситуацію
ex.printStackTrace();
}
// формуємо адресу БД
String url = "jdbc:mysql://localhost:3307/classicmodels";
String login = "root";
String passwd = "";
try {
// з’єднуємося із сервером БД
Connection conn = DriverManager.getConnection(url, login, passwd);
System.out.println("Connection established.");
try {
// Створюємо об’єкт Statement
Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// виконуємо запит до сервера
//ResultSet res = stm.executeQuery("SELECT * FROM employees");
ResultSet res = stm.executeQuery(queryField.getText());
DefaultTableModel model = (DefaultTableModel) masterTable.getModel();
model.getDataVector().removeAllElements();
// обробляємо отримані результати
while (res.next()) {
int employeeNumber = res.getInt("employeeNumber");
String lastName = res.getString("lastName");
String firstName = res.getString("firstName");
String extension = res.getString("extension");
String email = res.getString("email");
String officeCode = res.getString("officeCode");
int reportsTo = res.getInt("reportsTo");
String jobtitle = res.getString("jobtitle");
model.addRow(new Object[]{employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobtitle});
}
// видаляємо об’єкт Statement
stm.close();
} catch (SQLException ex) {
// виводимо інформацію про виключну ситуацію
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
// завершуємо сеанс роботи з БД
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
Приклади взаємодії:
Початковий стан.
Після натистення New водимо дані в поля, вони одразу з’являютсья в таблиці. Натискаємо Save.
Віднаходимо наш новий запис за допомогою «Find» та видаляємо його за допомогою «Delete».
Відкриваємо JDBC форму для клієнтсьокого запиту.
Вводимо та виконуємо запит.