Online bookstore eclipse---Java+SQL Server+swing

Posted by seanlyons on Mon, 17 Jan 2022 22:00:39 +0100

About database operations:

1. Design background:

With the development of information technology, computers have entered thousands of households. People's communication has become convenient and convenient for the life of Internet users! In addition, with the rise of excellence, Jingdong, Taobao and other online shopping platforms, online shopping has also been recognized by netizens. First, online bookstores facilitate the life of netizens and are more convenient. You can buy favorite books without leaving home; Secondly, buying books online reduces many intermediate sales agents, so the price will be relatively low, which is very obvious to the majority of Internet users; Moreover, online bookstores are information-based shopping, and basically all operations are completed automatically through computer programs, which greatly reduces the consumption of labor; Finally, the combination of e-commerce and publishing and distribution industry makes the development of online bookstores and the study of online bookstores have dual significance. Fully combining the characteristics of Internet sales, exploring new channels and models of book distribution is not only the enrichment and supplement of e-commerce science, but also the development and innovation of publishing and distribution channels and markets.

The significance of database for a system is very important. It is related to the overall process of the whole project and whether the later maintenance of the whole website is convenient or not. From the long-term interests, the design of database is very important.

2. Business description:

The online bookstore system being developed is to enable the member users of the bookstore to buy the books they need in the online bookstore. Tourists refer to the users who are not registered in the online bookstore system. Visitors can apply for registration as member users through this system and enjoy the function of purchasing books by member users. Visitors and members can browse all the books in the system through the system, and use the query conditions provided by the system to conveniently query the books they need. Member users can view or change their member information at any time through certain channels provided by the system. Each order submitted by member users will be stored in the background database of the system, and member users can view the order information at any time.

All books of the system will be stored in the background database, and each book is added into the system by employees. Employees can change the information of each book to update the book information at any time, and the information of each employee will be stored in the background database of the system.

The online bookstore management information system needs the following information:

System management table: administrator number, administrator password, administrator name, administrator gender, administrator age, information maintenance rights;

Book information table: book number, book name, book price, author, publishing house, publishing time;

Customer information table: user number, user password, customer name, customer age, customer telephone, customer postcode, customer address;

Order form: order number, user number, book number, administrator number, order date, total price;

Requirements: the same book can only be purchased by one user, and one user can purchase multiple books. A book corresponds to a number, and a number corresponds to a book. A user number corresponds to the information of a Book buyer, and each book buyer has only one user number.

One order number corresponds to one order, and one order corresponds to one order number One order number corresponds to one manager number, and one manager number corresponds to multiple order numbers

3. Data analysis:

 4. Logical structure:

System management: (administrator number (PK), administrator password, administrator name, administrator gender, administrator age, information maintenance authority,);

Book information: (book number (PK), book name, book price, author, publishing house, publishing time);

Customer information: (user number (PK), user password, customer name, customer age, customer telephone, customer zip code, customer address);

Order (Order No. (PK), book No. (FK), administrator No. (FK), user No. (FK), order date, total price)

 5. Physical structure analysis:

Determine the storage structure of data: use relational two-dimensional tables

Data storage path: D: \ database course design

Determine the storage location of the database: D: \ database settings

Database configuration: sql sever 2019

6. Database implementation:

6.1 establishment of book information table

Create table Book

(no  varchar(10)  primary key,

Book_Name varchar(10),

Book_Price varchar(10),

Book_Writer varchar(10),

Book_Publisher varchar(50),

Book_Time varchar(10),)

6.2 establishment of customer information table

Create table Buyer

(Buyer_no  varchar(10)  primary key,

Buyer_Password  varchar(10)  not null,

Buyer_Name varchar(10),

Buyer_Sex varchar(10),

Buyer_Age varchar(10),

Buyer_Tel varchar(50),

Buyer_Email varchar(50),

Buyer_Add varchar(50),)

6.3 establishment of system management table

Create table User

(no varchar(10) primary key,

User_Password varchar(50) not null,

User_Name varchar(10),

User_Sex varchar(10),

User_Age varchar(10),

User_Power varchar(50),)

6.4 establishment of order table

Create table Order

(no varchar(10) primary key,

Buyer_no varchar(10),

Book_no varchar(10),

User_no varchar(10),

Order_time datetime,

Order_price varchar(10),

Foreign key(Buyer_no) references Buyer(Buyer_no)

Foreign key(Book_no) references Book(Book_no)

Foreign key(User_no) references User(User_no))

6.5 overall structure of the table

The above operations on the database are basically completed

Some operations and source code of java:

1. About using jdbc to connect to the database:

public class DbUtil {
	private String dbUrl="jdbc:sqlserver://localhost:1433;databaseName=OnlineBS";
	private String dbUserName="root";
	private String dbPassword="root";
	private String jdbcName="com.microsoft.sqlserver.jdbc.SQLServerDriver";

	/**
	 * Get database connection
	 * @return
	 * @throws Exception
	 */
	public Connection getCon()throws Exception{
		Class.forName(jdbcName);
		Connection con=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
		return con;
	}
	
	/**
	 * Close database connection
	 */
	public void closeCon(Connection con)throws Exception{
		if(con!=null) {
			con.close();
		}
	}
	public static void main(String[] args) {
		DbUtil dbutil=new DbUtil();
		try {
			dbutil.getCon();
			System.out.println("Database connection succeeded!");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("Database connection failed!");
		}
	}
}

About adding, deleting, modifying and querying the database:

    //General addition, deletion and modification
    public static void update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = sqlOperation.getCon();

            ps = connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            sqlOperation.closeResource(connection, ps);
        }
    }
    //General search
    public static <T> List query(Class<T> clazz, String sql, Object... args)  {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list=new ArrayList<>();
        try {
            con = sqlOperation.getCon();
            ps = con.prepareStatement(sql);
            int index=1;
            for (int i = 0; i < args.length; ++i) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();
            while (rs.next()) {
               T  t = clazz.newInstance();
                for (int i = 0; i < col; ++i) {
                    Object colValues = rs.getObject(i + 1);
                    String name = rsmd.getColumnLabel(i + 1);
                    Field declaredField = t.getClass().getDeclaredField(name);
                    declaredField.setAccessible(true);
                    declaredField.set(t, colValues);
                }
                list.add(t);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            sqlOperation.closeResource(con, ps, rs);
        }
        return null;
    }

2. Partial source code of java swing:

login interface:

public LogOnFrame() {
		setResizable(false);
		setIconImage(Toolkit.getDefaultToolkit().getImage(LogOnFrame.class.getResource("/images/\u4E66\u7C4D2.png")));
		setTitle("\u7F51\u4E0A\u4E66\u5E97");
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 529, 458);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		JLabel lblNewLabel = new JLabel("\u7F51\u4E0A\u4E66\u5E97\u7BA1\u7406");
		lblNewLabel.setFont(new Font("Song typeface", Font.BOLD, 21));
		lblNewLabel.setIcon(new ImageIcon(LogOnFrame.class.getResource("/images/\u4E66\u7C4D2.png")));
		JLabel lblNewLabel_1 = new JLabel("\u7528\u6237\u540D\uFF1A");
		lblNewLabel_1.setFont(new Font("Song typeface", Font.PLAIN, 14));
		JLabel lblNewLabel_2 = new JLabel("\u5BC6   \u7801\uFF1A");
		userNameTxt = new JTextField();
		userNameTxt.setColumns(10);
		passwordTxt = new JPasswordField();
		JButton btnNewButton = new JButton("\u767B  \u5F55");
		btnNewButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				loginActionPerformed(e);
			}
		});
		JButton btnNewButton_1 = new JButton("\u91CD \u7F6E");
		btnNewButton_1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				resetActionPerformed(e);
			}
		});
		JButton btnNewButton_2 = new JButton("\u7528\u6237\u6CE8\u518C");
		btnNewButton_2.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				RegActionPerformed(e);
			}
			private void RegActionPerformed(ActionEvent e) {
				// TODO Auto-generated method stub
				dispose();
				new ReFrm1().setVisible(true);			
			}
		});
		JLabel lblNewLabel_3 = new JLabel("\u6743   \u9650");
		JComboBox UsesrBoxTxt = new JComboBox();
		UsesrBoxTxt.setModel(new DefaultComboBoxModel(new String[] {"\u7528\u6237", "\u7BA1\u7406\u5458"}));
		GroupLayout gl_contentPane = new GroupLayout(contentPane);
		gl_contentPane.setHorizontalGroup(
			gl_contentPane.createParallelGroup(Alignment.TRAILING)
				.addGroup(gl_contentPane.createSequentialGroup()
					.addGroup(gl_contentPane.createParallelGroup(Alignment.LEADING)
						.addGroup(gl_contentPane.createSequentialGroup()
							.addGap(68)
							.addComponent(lblNewLabel, GroupLayout.PREFERRED_SIZE, 364, GroupLayout.PREFERRED_SIZE))
						.addGroup(gl_contentPane.createSequentialGroup()
							.addGap(84)
							.addGroup(gl_contentPane.createParallelGroup(Alignment.LEADING)
								.addGroup(gl_contentPane.createSequentialGroup()
									.addComponent(btnNewButton, GroupLayout.PREFERRED_SIZE, 97, GroupLayout.PREFERRED_SIZE)
									.addGap(18)
									.addComponent(btnNewButton_2, GroupLayout.PREFERRED_SIZE, 97, GroupLayout.PREFERRED_SIZE)
									.addGap(18)
									.addComponent(btnNewButton_1, GroupLayout.PREFERRED_SIZE, 97, GroupLayout.PREFERRED_SIZE))
								.addGroup(gl_contentPane.createParallelGroup(Alignment.LEADING, false)
									.addGroup(gl_contentPane.createSequentialGroup()
										.addComponent(lblNewLabel_1, GroupLayout.PREFERRED_SIZE, 58, GroupLayout.PREFERRED_SIZE)
										.addPreferredGap(ComponentPlacement.RELATED)
										.addComponent(userNameTxt, GroupLayout.PREFERRED_SIZE, 175, GroupLayout.PREFERRED_SIZE))
									.addGroup(gl_contentPane.createSequentialGroup()
										.addComponent(lblNewLabel_2, GroupLayout.PREFERRED_SIZE, 58, GroupLayout.PREFERRED_SIZE)
										.addPreferredGap(ComponentPlacement.RELATED)
										.addComponent(passwordTxt))
									.addGroup(gl_contentPane.createSequentialGroup()
										.addComponent(lblNewLabel_3, GroupLayout.PREFERRED_SIZE, 58, GroupLayout.PREFERRED_SIZE)
										.addPreferredGap(ComponentPlacement.RELATED)
										.addComponent(UsesrBoxTxt, GroupLayout.PREFERRED_SIZE, 77, GroupLayout.PREFERRED_SIZE))))))
					.addContainerGap(83, Short.MAX_VALUE))
		);
		gl_contentPane.setVerticalGroup(
			gl_contentPane.createParallelGroup(Alignment.LEADING)
				.addGroup(gl_contentPane.createSequentialGroup()
					.addGap(73)
					.addComponent(lblNewLabel, GroupLayout.PREFERRED_SIZE, 88, GroupLayout.PREFERRED_SIZE)
					.addGap(54)
					.addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
						.addComponent(lblNewLabel_1)
						.addComponent(userNameTxt, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
					.addGap(18)
					.addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
						.addComponent(lblNewLabel_2)
						.addComponent(passwordTxt, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
					.addGap(18)
					.addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
						.addComponent(lblNewLabel_3)
						.addComponent(UsesrBoxTxt, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
					.addPreferredGap(ComponentPlacement.RELATED, 38, Short.MAX_VALUE)
					.addGroup(gl_contentPane.createParallelGroup(Alignment.BASELINE)
						.addComponent(btnNewButton)
						.addComponent(btnNewButton_2)
						.addComponent(btnNewButton_1))
					.addGap(51))
		);
		contentPane.setLayout(gl_contentPane);
		//Set the window to center
		this.setLocationRelativeTo(null);
		this.UsesrBoxTxt=UsesrBoxTxt;
	}
	//User login operation
	private void loginActionPerformed(ActionEvent evt) {
		// TODO Auto-generated method stub
		String userName=this.userNameTxt.getText();
		String passWord=new String(this.passwordTxt.getPassword());
		if(StringUtil.isEmpty(userName)) {
			JOptionPane.showMessageDialog(null, "User name cannot be empty");
			return;
		}
		if(StringUtil.isEmpty(passWord)) {
			JOptionPane.showMessageDialog(null, "Password cannot be empty");
			return;
		}
		//Login permission settings
		String AuthorityName=this.UsesrBoxTxt.getSelectedItem().toString();
		if(AuthorityName.equals("user")) {  //User login
			Buyer buyer=new Buyer(userName,passWord);		
			Connection con=null;
			try{
				con=dbUtil.getCon();
				Buyer currentUser=userDao.LoginBuyer(con, buyer);			
				if(currentUser!=null) {
					dispose();
					new UserFrame().setVisible(true);
//					JOptionPane.showMessageDialog(null, "login succeeded!");
				}else {
					JOptionPane.showMessageDialog(null, "Wrong user name or password!");
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}else {//Administrator login
			Management mana=new Management(userName,passWord);
			Connection con=null;
			try{
				con=dbUtil.getCon();
				Management currentUser=userDao.Login(con, mana);			
				if(currentUser!=null) {
					dispose();
					new MainFrame().setVisible(true);
//					JOptionPane.showMessageDialog(null, "login succeeded!");
				}else {
					JOptionPane.showMessageDialog(null, "Wrong user name or password!");
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
	
	//Reset operation
	private void resetActionPerformed(ActionEvent evt) {
		// TODO Auto-generated method stub
		this.userNameTxt.setText("");
		this.passwordTxt.setText("");
	}

User interface:

public class UserFrame extends JFrame {

	private JPanel contentPane;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					UserFrame frame = new UserFrame();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public UserFrame() {
		setResizable(false);
		setTitle("\u7528\u6237\u4E3B\u754C\u9762");
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 475, 334);
		
		JMenuBar menuBar = new JMenuBar();
		setJMenuBar(menuBar);
		
		JMenu mnNewMenu = new JMenu("\u7528\u6237\u64CD\u4F5C");
		menuBar.add(mnNewMenu);
		
		JMenuItem mntmNewMenuItem = new JMenuItem("\u6309\u4E66\u540D\u67E5\u627E");
		mntmNewMenuItem.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {//Find by title
				SelectBookname(e);
			}
		});
		mnNewMenu.add(mntmNewMenuItem);
		
		JMenuItem mntmNewMenuItem_1 = new JMenuItem("\u4EF7\u683C\u964D\u5E8F\u6392\u5217");
		mntmNewMenuItem_1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {   //Price descending order
				PriceDesc(e);
			}
		});
		
		JMenuItem mntmNewMenuItem_3 = new JMenuItem("\u67E5\u770B\u56FE\u4E66\u4FE1\u606F");
		mntmNewMenuItem_3.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				CheckBooks2(e);
			}
		});
		
		JMenuItem mntmNewMenuItem_4 = new JMenuItem("\u8D2D\u4E70\u56FE\u4E66");
		mntmNewMenuItem_4.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				BuyBooks(e);
			}
		});
		mnNewMenu.add(mntmNewMenuItem_4);
		mnNewMenu.add(mntmNewMenuItem_3);
		mnNewMenu.add(mntmNewMenuItem_1);
		
		JMenuItem mntmNewMenuItem_2 = new JMenuItem("\u4EF7\u683C\u5347\u5E8F\u6392\u5217");
		mntmNewMenuItem_2.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {    //Price ascending order
				PriceIncrease(e);
			}
		});
		mnNewMenu.add(mntmNewMenuItem_2);
		
		JMenuItem mntmNewMenuItem_5 = new JMenuItem("\u4FEE\u6539\u5BC6\u7801");
		mntmNewMenuItem_5.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {  //Change Password
				ChangeCode(e);
			}
		});
		mnNewMenu.add(mntmNewMenuItem_5);
		
		JMenuItem mntmNewMenuItem_6 = new JMenuItem("\u5B89\u5168\u9000\u51FA");
		mntmNewMenuItem_6.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				//Safely exit to the login interface
				dispose();
				new LogOnFrame().setVisible(true);
			}
		});
		mnNewMenu.add(mntmNewMenuItem_6);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		
		JLabel lblNewLabel = new JLabel("\u6B22\u8FCE\u60A8\u4F7F\u7528\u672C\u7CFB\u7EDF");
		GroupLayout gl_contentPane = new GroupLayout(contentPane);
		gl_contentPane.setHorizontalGroup(
			gl_contentPane.createParallelGroup(Alignment.LEADING)
				.addGroup(gl_contentPane.createSequentialGroup()
					.addGap(170)
					.addComponent(lblNewLabel, GroupLayout.PREFERRED_SIZE, 123, GroupLayout.PREFERRED_SIZE)
					.addContainerGap(168, Short.MAX_VALUE))
		);
		gl_contentPane.setVerticalGroup(
			gl_contentPane.createParallelGroup(Alignment.LEADING)
				.addGroup(gl_contentPane.createSequentialGroup()
					.addGap(122)
					.addComponent(lblNewLabel, GroupLayout.PREFERRED_SIZE, 46, GroupLayout.PREFERRED_SIZE)
					.addContainerGap(105, Short.MAX_VALUE))
		);
		contentPane.setLayout(gl_contentPane);
		
		//Center display
		this.setLocationRelativeTo(null);
	}

	protected void ChangeCode(ActionEvent e) {
		// Todo auto generated method stub
		dispose();
		new ChangePassword().setVisible(true);
	}

	protected void BuyBooks(ActionEvent e) {
		// Todo auto generated method stub
		dispose();
		new BuyFrame().setVisible(true);
		
	}

	protected void CheckBooks2(ActionEvent e) {
		// Todo auto generated method stub viewing books
		dispose();
		new ShowBooksUser().setVisible(true);
	}

	protected void PriceIncrease(ActionEvent e) {
		// Todo auto generated method stub price ascending
		dispose();
		new PriceIncrese().setVisible(true);
	}

	protected void PriceDesc(ActionEvent e) {
		// Todo auto generated method stub price descending order
		dispose();
		new PriceDesc().setVisible(true);
	}

	protected void SelectBookname(ActionEvent e) {
		// Todo auto generated method stub go to the find page
		dispose();
		new SelectBook().setVisible(true);	
	}
}

Program instructions and screenshots:

The user or administrator enters the system interface

User interface

Administrator operation interface

User query books, user operation and purchase interface

1. User registration interface

2. User query book interface

3. The user selects the purchase book interface

 4. Books viewed by users

 7. User change password

Administrator management and library management system interface

1. Add books

 

 2. Delete book

 

 3. View order information

 4. View book information

Topics: Java Database SQL Server Eclipse swing