Mysql practical exercises simple library management system
1. Sorting function 1. Can represent book information, for each book, serial number, title, author, price, type.
2. Prepare the databaseCreate user table and book table
create database if not exists java100_bookmanager;
use java100_bookmanager;
drop table if exists book;
//Set id as auto-increment primary key create table book(id int primary key auto_increment,name varchar(20),author varchar(20),price int,type varchar(20),isBorrowed int);
drop table if exists user;
//Also set userid as the auto-increment primary key and make sure user names are unique create table user(
userId int primary key auto_increment,
username varchar(20) unique,
password varchar(20),
isAdmin int
);
-- Insert some books insert into book values(null,'Journey to the West','Wu Cheng'en',10000,'Classical Novels',0);
insert into book values(null,'Romance of the Three Kingdoms','Luo Guanzhong',10000,'Classical Novels',0);
insert into book values(null,'Water Margin','Shi Naian',10000,'Classical Novels',0);
insert into book values(null,'Jin Ping Mei','Lanling Xiao Xiao Sheng',10000,'Classical Novels',0);
--Insert some users insert into user values(null,'admin','123',1);
insert into user values(null,'zhangsan','123',0);
3. Constructing entity classes related to the databasebooks
public class Books {
private int bookId;//Book number private String name;//Book title private String author;//Author private int price;//Price private String type;//Type private boolean isBorrowed;//Whether it is borrowed//set get method public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public boolean isBorrowed() {
return isBorrowed;
}
public void setBorrowed(boolean borrowed) {
isBorrowed = borrowed;
}
@Override
public String toString() {
return "Book{" +
"bookId=" + bookId +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
", type='" + type + '\'' +
", isBorrowed=" + isBorrowed +
'}';
}
userThere are two types of users, one is a normal user and the other is an administrator. The menus seen by administrators and normal users are different, and the class methods of administrators and normal users are also different. First, define an abstract class User and let the normal user NoramlUser and the administrator class Admin inherit the User class.
abstract public class user {
private int userId;
private String userName;
private String passWord;
IOperation[] operations; //Method array, indicating the methods contained in the user class abstract int menu(); //Subclasses need to override the menu method because the two subclasses see different menus public void doOperation(int choice) { //This method is used to perform some operations, such as borrowing and returning books, etc. operations[choice].work();
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
@Override
public String toString() {
return "user{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
'}';
}
}
NormalUser Class
public class NormalUser extends user{
public NormalUser(){
this.operations = new IOperation[]{//Open a separate package to store and implement these methods new ExitOperation(), //Exit the system new DisplayOperation(), //View the book list new FindOperation(), //Find a book new BorrowOperation(), //Borrow a book new ReturnOperation(), //Return a book};
}
@Override
public int menu() {//Override parent class menu method System.out.println("========================");
System.out.println("Welcome, "+this.getUserName()+"!");
System.out.println("1. View the book list");
System.out.println("2. Find the specified book");
System.out.println("3. Borrowing books");
System.out.println("4. Return books");
System.out.println("0. Exit the system");
System.out.println("========================");
System.out.println("Please enter options");
Scanner sc = new Scanner (System.in);
int choice=sc.nextInt();
return choice;
}
}
Admin Class
public class Admin extends user {
public Admin(){
this.operations = new IOperation[]{
new ExitOperation(), //Exit the system new DisplayOperation(), //View the book list new FindOperation(), //Find a book new AddOperation(), //Add a book new DelOperation(), //Delete a book };
}
@Override
public int menu() {
System.out.println("========================");
System.out.println("Welcome,"+this.getUserName()+"You are an administrator!");
System.out.println("1. View the book list");
System.out.println("2. Find the specified book");
System.out.println("3. New books");
System.out.println("4. Delete books");
System.out.println("0. Exit the system");
System.out.println("========================");
System.out.println("Please enter options");
Scanner sc = new Scanner (System.in);
int choice=sc.nextInt();
return choice;
}
}
4. Encapsulation of database related operations
Database link operations
//Encapsulate the database connection operation here public class DBUtil {
//Set the url account and password according to personal settings private static final String URL="jdbc:mysql://127.0.0.1:3306/java100_bookmanager?characterEncoding=utf8&&useSSL=false";
private static final String USERNAME="root";
private static final String PASSWORD="q986681563";
//Hungry Man Mode//The static code block will be called for instantiation during the class loading phase/*private static DataSource dataSource=new MysqlDataSource();
static{
((MysqlDataSource)dataSource).setUrl(URL);
((MysqlDataSource)dataSource).setUser(USERNAME);
((MysqlDataSource)dataSource).setPassword(PASSWORD);
}*/
//Lazy mode //The getDataSource method will only be instantiated for the first time private static DataSource dataSource=null;
public static DataSource getDataSource() {
if(dataSource==null){
dataSource=new MysqlDataSource();
((MysqlDataSource)dataSource).setUrl(URL);
((MysqlDataSource)dataSource).setUser(USERNAME);
((MysqlDataSource)dataSource).setPassword(PASSWORD);
}
return dataSource;
}
public static Connection getConnection() throws SQLException {
return getDataSource().getConnection();
}
public static void close(ResultSet resultSet, PreparedStatement statement,Connection connection){//Release resources//The commented-out method is safer/*if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}*/
try {
if(resultSet!=null) resultSet.close();
if(statement!=null) statement.close();
if(connection!=null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Operations on the book table
//DAO Data Access Object data access object public class BookDAO {
//1. Add new books public boolean add(Books book){
Connection connection=null;
PreparedStatement statement=null;
try {
connection = DBUtil.getConnection();
String sql="insert into book values(null,?,?,?,?,?)";
statement=connection.prepareStatement(sql);
statement.setString(1,book.getName());
statement.setString(2,book.getAuthor());
statement.setInt(3,book.getPrice());
statement.setString(4,book.getType());
statement.setInt(5,book.isBorrowed()?1:0);
int ret = statement.executeUpdate();
if(ret!=1) return false;
return true;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(null,statement,connection);
}
return false;
}
//2. View all books public List<Books> selectAll(){
List<Books> list=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {
connection=DBUtil.getConnection();
String sql="select*from book";
statement=connection.prepareStatement(sql);
resultSet=statement.executeQuery();
while(resultSet.next()){
Books book = new Books();
book.setBookId(resultSet.getInt("id"));
book.setName(resultSet.getString("name"));
book.setAuthor(resultSet.getString("author"));
book.setPrice(resultSet.getInt("price"));
book.setType(resultSet.getString("type"));
book.setBorrowed(resultSet.getInt("isBorrowed")==1);
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(resultSet,statement,connection);
}
return list;
}
//3. Find books by name public List<Books> selectByName(String name) {
List<Books> list=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {
connection=DBUtil.getConnection();
String sql="select* from book where name=?";
statement=connection.prepareStatement(sql);
statement.setString(1,name);
resultSet=statement.executeQuery();
while(resultSet.next()){
Books book = new Books();
book.setBookId(resultSet.getInt("Id"));
book.setName(resultSet.getString("name"));
book.setAuthor(resultSet.getString("author"));
book.setType(resultSet.getString("type"));
book.setPrice(resultSet.getInt("price"));
book.setBorrowed(resultSet.getInt("isBorrowed")==1);
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(resultSet,statement,connection);
}
return list;
}
//4. Delete books public boolean delete(int bookId){
Connection connection=null;
PreparedStatement statement=null;
try {
connection=DBUtil.getConnection();
String sql="delete from book where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(1,bookId);
int ret = statement.executeUpdate();
if(ret!=1) return false;
return true;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(null,statement,connection);
}
return false;
}
//5. Borrow a book public boolean borrowBook(int bookId){
Connection connection=null;
PreparedStatement statement=null;
PreparedStatement statement2=null;
ResultSet resultSet=null;
try {
connection=DBUtil.getConnection();
String sql="select * from book where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(1,bookId);
resultSet=statement.executeQuery();
if(resultSet.next()){
boolean isBorrowed=(resultSet.getInt("isBorrowed")==1);
if(isBorrowed){
System.out.println("The book has been borrowed and cannot be borrowed again! bookId="+bookId);
return false;
}
}else{
System.out.println("Book does not exist bookId="+bookId);
return false;
}
sql="update book set isBorrowed=1 where id=?";
statement2 = connection.prepareStatement(sql);
statement2.setInt(1,bookId);
int ret = statement2.executeUpdate();
if(ret!=1) {
System.out.println("Borrow failed");
return false;
}
System.out.println("Borrowing successful");
return true;
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(resultSet!=null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement2!=null) {
try {
statement2.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return false;
}
//6. Return public boolean returnBook(int bookId){
Connection connection=null;
PreparedStatement statement=null;
PreparedStatement statement2=null;
ResultSet resultSet=null;
try {
connection=DBUtil.getConnection();
String sql="select* from book where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(1,bookId);
resultSet = statement.executeQuery();
if(resultSet.next()){
boolean isBorrowed=(resultSet.getInt("isBorrowed")==1);
if(!isBorrowed){
System.out.println("The book has not been borrowed and does not need to be returned bookId="+bookId);
return false;
}
}else{
System.out.println("There is no such book! bookId="+bookId);
return false;
}
sql="update book set isBorrowed=0 where id=?";
statement2 = connection.prepareStatement(sql);
statement2.setInt(1,bookId);
int ret = statement2.executeUpdate();
if(ret!=1) return false;
return true;
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(resultSet!=null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement2!=null) {
try {
statement2.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return false;
}
}
Operations on the user table
public class UserDao {
//Logic for finding passwords based on usernames //username is a unique constraint public user selectByName(String name){
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {
connection=DBUtil.getConnection();
String sql="select* from user where username=?";
statement=connection.prepareStatement(sql);
statement.setString(1,name);
resultSet = statement.executeQuery();
if(resultSet.next()){
boolean isAdmin=(resultSet.getInt("isAdmin")==1);
user users=null;
if(isAdmin){
users = new Admin();
}else users=new NormalUser();
users.setPassWord(resultSet.getString("password"));
users.setUserId(resultSet.getInt("userId"));
users.setUserName(resultSet.getString("username"));
return users;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(resultSet,statement,connection);
}
return null;
}
}
Write the main logic (main method and login method)
public class Main {
public static void main(String[] args) {
user users=login();
while(true){
int choice=users.menu();
users.doOperation(choice);
}
}
private static user login(){
Scanner sc = new Scanner (System.in);
System.out.println("Please enter your user name");
String name=sc.next();
System.out.println("Please enter your password");
String password=sc.next();
UserDao userDao=new UserDao();
user users=userDao.selectByName(name);
if(users==null){
System.out.println("Login failed!");
System.exit(0);
}
if(!users.getPassWord().equals(password)){
System.out.println("Wrong password");
System.exit(0);
}
return users;
}
}
Write various details of the operation Put all operations in a package, define an interface operations, and all operations implement this interface and override methods
public interface IOperation {
void work();
}
Add Book Action
public class AddOperation implements IOperation{
@Override
public void work() {
System.out.println("New books!");
Scanner sc = new Scanner (System.in);
System.out.println("Please enter the title of the book");
String name=sc.next();
System.out.println("Please enter the author");
String author = sc.next();
System.out.println("Please enter the price");
int price=sc.nextInt();
System.out.println("Please enter the category");
String type=sc.next();
Books book = new Books();
book.setName(name);
book.setPrice(price);
book.setType(type);
book.setAuthor(author);
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.add(book);
if(ret) System.out.println("Added successfully");
else System.out.println("Addition failed");
}
}
Book borrowing operation
public class BorrowOperation implements IOperation {
@Override
public void work() {
System.out.println("Borrowed books");
System.out.println("Please enter the book ID you want to borrow");
Scanner sc = new Scanner (System.in);
int id=sc.nextInt();
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.borrowBook(id);
}
}
Delete Book Operation
public class DelOperation implements IOperation{
@Override
public void work() {
System.out.println("Delete books!");
Scanner sc = new Scanner (System.in);
System.out.println("Please enter the ID of the book to be deleted");
int id=sc.nextInt();
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.delete(id);
if(ret) System.out.println("Deleted successfully");
else System.out.println("deletion failed");
}
}
View Book List Operation
public class DisplayOperation implements IOperation {
@Override
public void work() {
System.out.println("Show all books");
BookDAO bookdao=new BookDAO();
List<Books> list=bookdao.selectAll();
for(Books book:list){
System.out.println(book);
}
System.out.println("Book display completed");
}
}
Exit system operation
public class ExitOperation implements IOperation{
@Override
public void work() {
System.out.println("Exit program");
System.exit(0);
}
}
Find Books Operation
public class FindOperation implements IOperation{
@Override
public void work() {
System.out.println("Search for books by name");
System.out.println("Please enter the title of the book");
Scanner sc = new Scanner (System.in);
String name=sc.next();
BookDAO bookDAO=new BookDAO();
List<Books> books = bookDAO.selectByName(name);
for(Books book:books){
System.out.println(book);
}
System.out.println("Finding books by name completed");
}
}
Book return operation
public class ReturnOperation implements IOperation{
@Override
public void work() {
System.out.println("Return the books!");
System.out.println("Please enter the ID of the book you want to return");
Scanner sc = new Scanner (System.in);
int id=sc.nextInt();
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.returnBook(id);
if(ret){
System.out.println("Return successful");
}else{
System.out.println("Return failed");
}
}
}
Summary: A simple library management system. Through practice, you can master simple JDBC syntax and API, and it can also help you understand concepts such as polymorphic inheritance in Java. This is the end of this article about MySQL practical exercises and simple library management system. For more relevant MySQL library management system content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future! You may also be interested in:
|
<<: Some tips for writing high-performance HTML applications
>>: CSS--overflow:hidden in project examples
Recommend
How to redraw Button as a circle in XAML
When using XAML layout, sometimes in order to make...
How to configure anti-hotlinking for nginx website service (recommended)
1. Principle of Hotlinking 1.1 Web page preparati...
The most detailed method to install docker on CentOS 8
Install Docker on CentOS 8 Official documentation...
Linux dual network card binding script method example
In Linux operation and configuration work, dual n...
Summary of Form Design Techniques in Web Design
“Inputs should be divided into logical groups so ...
The use of textarea in html and common problems and case analysis
The textarea tag is an HTML tag that we often use....
Detailed explanation of the complete usage example of developing hyperf under Docker
Hyperf official website Hyperf official documenta...
A brief discussion on several ways to implement front-end JS sandbox
Table of contents Preface iframe implements sandb...
Vue implements 3 ways to switch tabs and switch to maintain data status
3 ways to implement tab switching in Vue 1. v-sho...
Detailed explanation of native Javascript inheritance methods and their advantages and disadvantages
Table of contents Preface Prototypal inheritance ...
How to add configuration options to Discuz! Forum
Discuz! Forum has many configuration options in th...
JavaScript article will show you how to play with web forms
1. Introduction Earlier we introduced the rapid d...
How to configure /var/log/messages in Ubuntu system log
1. Problem Description Today I need to check the ...
Detailed explanation of the solution to keep the content within the container in flex layout
On the mobile side, flex layout is very useful. I...
Solve the problem of MySql client exiting in seconds (my.ini not found)
Problem description (environment: windows7, MySql...