Программирование на Java часть 2
.pdfps.setInt(4, ob.getTotal_cost()); ps.execute();
}
@Override
public void update(Orders ob) throws SQLException {
PreparedStatement ps = (PreparedStatement) db.getCn().prepareStatement(
"update " + ob.getClass().getSimpleName()
+ " set payment=? where orders_id=" + ob.getOrders_id()); ps.setString(1, ob.getPayment());
ps.execute();
}
@Override
public void softDelete(Orders ob) throws SQLException { db.update("delete from " + ob.getClass().getSimpleName()
+ " where orders_id=" + ob.getOrders_id());
}
@Override
public void delete(Orders ob) throws SQLException { db.update("update " + ob.getClass().getSimpleName()
+ " set delete_status=1 where orders_id=" + ob.getOrders_id());
}
public int getLastInsertId() throws SQLException {
ResultSet rs = db.query("SELECT LAST_INSERT_ID() FROM
orders");
rs.next();
return rs.getInt(1);
}
}
Тема 4.5 Методы для создания и удаления базы данных
Используя класс DB и Dao-классы создадим методы create и delete для создания и удаления базы данных.
Листинг 4.5
package by.belhard.mysql;
131
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData; import com.mysql.jdbc.Statement;
import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException;
public class DB {
private Connection cn; private Statement st;
public Connection getCn() { return cn;
}
public DB(String url, String user, String password) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
cn = (Connection) DriverManager.getConnection(url, user, password); st = (Statement) cn.createStatement();
}
public void update(String sql) throws SQLException { st.executeUpdate(sql);
}
public ResultSet query(String sql) throws SQLException { ResultSet rs = null;
rs = st.executeQuery(sql); return rs;
}
public void showResultSet(ResultSet rs) throws SQLException { ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnName(i) + "\t");
}
while (rs.next()) { System.out.println();
for (int i = 1; i <= rsmd.getColumnCount(); i++) { System.out.print(rs.getString(i) + "\t");
}
132
}
}
public void close() throws SQLException { st.close();
cn.close();
}
}
package by.belhard.mysql;
import by.belhard.dao.DaoGoods; import by.belhard.dao.DaoOrders; import by.belhard.dao.DaoUsers; import by.belhard.entity.Goods; import by.belhard.entity.Orders; import by.belhard.entity.Users;
import java.io.UnsupportedEncodingException; import java.security.NoSuchAlgorithmException; import java.sql.SQLException;
public class WorkDB {
public final static String NAME_DB = "shop";
public static void create(String url, String user, String password) throws SQLException, ClassNotFoundException, NoSuchAlgorithmException, UnsupportedEncodingException {
DB db = new DB(url, user, password);
db.update("create database " + NAME_DB + " DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci");
db.update("use " + NAME_DB); db.update("CREATE table users ("
+"users_id int auto_increment primary key,"
+" login varchar(30) not null unique, "
+"pass varchar(128) not null, "
+"role int(1),"
+"balance int,"
+"block_status int(1),"
+"delete_status int(1))");
DaoUsers du = new DaoUsers(db);
du.insert(new Users("user1", "1111", 1, 0, 0, 5000)); du.insert(new Users("user2", "2222", 1, 0, 0, 3000)); du.insert(new Users("admin", "1234", 0, 0, 0, 10000));
133
db.update("CREATE table goods ("
+ "goods_id int auto_increment primary key,"
+" name varchar(30), "
+"image_path text, "
+"price int, "
+"description text, "
+"delete_status int(1))"); DaoGoods dg = new DaoGoods(db);
dg.insert(new Goods(1, "Computer", "good1.jpg", 1000, "Very big
computer", 0));
dg.insert(new Goods(2, "Mouse", "good2.jpg", 10, "mad skills mouse",
0));
dg.insert(new Goods(3, "Keyboard", "good3.jpg", 15, "defender keyboard", 0));
db.update("CREATE table orders ("
+"orders_id int auto_increment primary key,"
+"users_id int, "
+"payment ENUM('processing','reject','payed'), "
+"delete_status int(1),"
+"total_cost int,"
+"foreign key(users_id) references users(users_id))"); DaoOrders dor = new DaoOrders(db);
dor.insert(new Orders(1, 1, "processing", 0, 50)); dor.insert(new Orders(2, 2, "processing", 0, 3000)); dor.insert(new Orders(3, 1, "reject", 0, 150)); db.update("CREATE table goods_in_orders ("
+"orders_id int,"
+"goods_id int, "
+"count int,"
+"foreign key(orders_id) references orders(orders_id),"
+"foreign key(goods_id) references goods(goods_id))"); db.update("insert into goods_in_orders values(1,2,5)"); db.update("insert into goods_in_orders values(2,1,3)"); db.update("insert into goods_in_orders values(3,3,10)"); db.close();
}
public static void delete(String url, String user, String password) throws SQLException, ClassNotFoundException {
DB db = new DB(url, user, password); db.update("drop database " + NAME_DB); db.close();
}
}
134
Тема 4.6 Разработка пользовательского интерфейса
Используя знания из главы 3 создадим StartFrame. В StartFrame по нажатию на create будет вызываться метод WorkDB.create, по нажатию на delete – метод WorkDB.delete. Нажатие на кнопку connect приведет к открытию нового фрейма.
Листинг 4.6
package by.belhard.user_interface;
import by.belhard.mysql.DB; import by.belhard.mysql.WorkDB; import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.UnsupportedEncodingException; import java.security.NoSuchAlgorithmException; import java.sql.SQLException;
import javax.swing.*;
public class StartFrame extends JFrame {
private JPanel panel;
private JLabel labelUrl, labelLogin, labelPass; private JTextField tfUrl, tfLogin;
private JPasswordField tfPass;
private JButton create, delete, connect;
public StartFrame() {
setSize(250, 230);// размеры фрейма setTitle("StartFrame"); //заголовок setLocationRelativeTo(null);//центрирование
setDefaultCloseOperation(EXIT_ON_CLOSE); //закрытие окна initComponents(); //создание и добавление всех компонент action(); //обработка событий
setResizable(false); setVisible(true); //прорисовка
}
private void initComponents() { panel = new JPanel();
labelUrl = new JLabel("URL"); labelLogin = new JLabel("Login"); labelPass = new JLabel("Password"); tfUrl = new JTextField("localhost", 20);
135
tfLogin = new JTextField("root", 20); tfPass = new JPasswordField(20); create = new JButton("Create"); delete = new JButton("Delete"); connect = new JButton("Connect"); panel.add(labelUrl); panel.add(tfUrl); panel.add(labelLogin); panel.add(tfLogin); panel.add(labelPass); panel.add(tfPass);
panel.add(create);
panel.add(delete);
panel.add(connect);
add(panel);
}
private void action() { create.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) { try {
try {
WorkDB.create("jdbc:mysql://" + tfUrl.getText() + "/", tfLogin.getText(), String.valueOf(tfPass.getPassword()));
}catch (NoSuchAlgorithmException ex) {
}catch (UnsupportedEncodingException ex) {
JOptionPane.showMessageDialog(panel, "Database installing successfully",
"Message", JOptionPane.INFORMATION_MESSAGE);
}catch (ClassNotFoundException ex) {
System.out.println(ex);
JOptionPane.showMessageDialog(panel,
"Error installing database \n" + ex,
"Error", JOptionPane.ERROR_MESSAGE);
}catch (SQLException ex) { System.out.println(ex); JOptionPane.showMessageDialog(panel,
"Error installing database \n" + ex,
"Error", JOptionPane.ERROR_MESSAGE);
}
}
136
});
delete.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) { try {
WorkDB.delete("jdbc:mysql://" + tfUrl.getText() + "/", tfLogin.getText(), tfPass.getText());
JOptionPane.showMessageDialog(panel, "Database drop successfully", "Message", JOptionPane.INFORMATION_MESSAGE);
}catch (ClassNotFoundException ex) { JOptionPane.showMessageDialog(panel,
"Error drop database\n" + ex, "Error", JOptionPane.ERROR_MESSAGE);
}catch (SQLException ex) { JOptionPane.showMessageDialog(panel,
"Error drop database\n" + ex, "Error", JOptionPane.ERROR_MESSAGE);
}
}
});
connect.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) { try {
DB db = new DB("jdbc:mysql://" + tfUrl.getText()
+ "/" + WorkDB.NAME_DB, tfLogin.getText(), tfPass.getText());
new LoginFrame(db); dispose();
}catch (ClassNotFoundException ex) { JOptionPane.showMessageDialog(panel,
"The database is not installed\n" + ex, "Error", JOptionPane.ERROR_MESSAGE);
}catch (SQLException ex) { JOptionPane.showMessageDialog(panel,
"The database is not installed\n" + ex,
"Error", JOptionPane.ERROR_MESSAGE);
}
}
});
137
}
}
package by.belhard.main;
import by.belhard.user_interface.StartFrame;
public class MySql {
public static void main(String[] args) { new StartFrame();
}
}
После установки базы данных переходим в LoginFrame.
Если пользователь уже есть в базе данных, то он вводит логин и пароль и в зависимости от роли открывается AdminFrame или UseFrame. Если пользователя еще нет, то он жмет на registration.
Листинг 4.7
package by.belhard.user_interface;
import by.belhard.dao.DaoUsers; import by.belhard.entity.Users;
import by.belhard.helpers.Sha1Encryption; import by.belhard.mysql.DB;
import java.awt.event.ActionEvent; import java.awt.event.ActionListener;
import java.io.UnsupportedEncodingException; import java.security.NoSuchAlgorithmException; import java.sql.ResultSet;
import java.sql.SQLException; import javax.swing.*;
public class LoginFrame extends JFrame {
private JPanel panel;
private JLabel labelLogin, labelPass; private JTextField tfLogin;
private JPasswordField tfPass; private JButton enter, registration; private DB db;
private LoginFrame login;
138
public LoginFrame(DB db) { this.db = db;
setSize(250, 230); setTitle("LoginFrame"); setLocationRelativeTo(null);
setDefaultCloseOperation(EXIT_ON_CLOSE); initComponents();
action();
setResizable(false);
setVisible(true); login = this;
}
private void initComponents() { panel = new JPanel();
labelLogin = new JLabel("Login"); labelPass = new JLabel("Password"); tfLogin = new JTextField("admin", 20); tfPass = new JPasswordField("1234", 20); enter = new JButton("Enter");
registration = new JButton("Registration"); panel.add(labelLogin); panel.add(tfLogin);
panel.add(labelPass);
panel.add(tfPass);
panel.add(enter);
panel.add(registration);
add(panel);
}
private void action() { enter.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) { try {
ResultSet rs = db.query("select * from users where login='" + tfLogin.getText() + "' and delete_status=0");
if (rs.next()) { try {
if (Sha1Encryption.encryptPassword( String.valueOf(tfPass.getPassword())).equals(
rs.getString("pass"))) { DaoUsers du = new DaoUsers(db); if (rs.getInt("role") == 1) {
139
new UserFrame(db, du.getUser(rs), login); dispose();
}else if (rs.getInt("role") == 0) { new AdminFrame(db);
dispose();
}
}else { JOptionPane.showMessageDialog(panel,
"Incorrect password", "Error", JOptionPane.ERROR_MESSAGE);
}
}catch (NoSuchAlgorithmException ex) {
}catch (UnsupportedEncodingException ex) {
}else {
JOptionPane.showMessageDialog(panel, "Incorrect login", "Error", JOptionPane.ERROR_MESSAGE);
}
}catch (SQLException ex) { JOptionPane.showMessageDialog(panel,
"Error in database\n" + ex, "Error", JOptionPane.ERROR_MESSAGE);
}
}
});
registration.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) { new RegistrationFrame(db);
dispose();
}
});
}
}
Для регистрации пользователя предназначен RegistrtionFrame.
Листинг 4.8
package by.belhard.user_interface;
import by.belhard.dao.DaoUsers; import by.belhard.entity.Users; import by.belhard.mysql.DB;
140