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
