Mysql实战练习之简单图书管理系统_Mysql_

 create database if not exists java100_bookmanager; use java100_bookmanager; drop table if exists book; //设置id为自增主键 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; //同样设置 userid为自增主键并且用户名字不重复 create table user( userId int primary key auto_increment, username varchar(20) unique, password varchar(20), isAdmin int ); -- 插入一些书籍 insert into book values(null,'西游记','吴承恩',10000,'古典小说',0); insert into book values(null,'三国演义','罗贯中',10000,'古典小说',0); insert into book values(null,'水浒传','施耐庵',10000,'古典小说',0); insert into book values(null,'金瓶梅','兰陵笑笑生',10000,'古典小说',0); --插入一些用户 insert into user values(null,'admin','123',1); insert into user values(null,'zhangsan','123',0); 

 public class Books { private int bookId;//书籍编号 private String name;//书名 private String author;//作者 private int price;//价格 private String type;//类型 private boolean isBorrowed;//是否被借阅 //set get方法 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 + '}'; } 

 abstract public class user { private int userId; private String userName; private String passWord; IOperation[] operations;//方法数组，表示user类所包含的方法 abstract int menu();//子类要重写menu方法，因为两个子类看到的menu不同 public void doOperation(int choice){//此方法来执行一些操作，如借书还书等 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 + '\'' + '}'; } } 

 public class NormalUser extends user{ public NormalUser(){ this.operations=new IOperation[]{//之后单独开辟一个包，包里存储和实现这些方法 new ExitOperation(),//退出系统 new DisplayOperation(),//查看书籍列表 new FindOperation(),//查找书籍 new BorrowOperation(),//借阅书籍 new ReturnOperation(),//还书 }; } @Override public int menu() {//重写父类menu方法 System.out.println("========================"); System.out.println("欢迎您，"+this.getUserName()+"!"); System.out.println("1.查看书籍列表"); System.out.println("2.查找指定书籍"); System.out.println("3.借阅书籍"); System.out.println("4.归还书籍"); System.out.println("0.退出系统"); System.out.println("========================"); System.out.println("请输入选项"); Scanner sc=new Scanner(System.in); int choice=sc.nextInt(); return choice; } } 

 public class Admin extends user { public Admin(){ this.operations=new IOperation[]{ new ExitOperation(),//退出系统 new DisplayOperation(),//查看书籍列表 new FindOperation(),//查找书籍 new AddOperation(),//添加书籍 new DelOperation(),//删除书籍 }; } @Override public int menu() { System.out.println("========================"); System.out.println("欢迎您，"+this.getUserName()+"您是管理员!"); System.out.println("1.查看书籍列表"); System.out.println("2.查找指定书籍"); System.out.println("3.新增书籍"); System.out.println("4.删除书籍"); System.out.println("0.退出系统"); System.out.println("========================"); System.out.println("请输入选项"); Scanner sc=new Scanner(System.in); int choice=sc.nextInt(); return choice; } } 

 //在这里封装数据库的连接操作 public class DBUtil { //设置url 账号密码 根据个人设置 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"; //饿汉模式 //类加载阶段就会调用静态代码块进行实例化 /*private static DataSource dataSource=new MysqlDataSource(); static{ ((MysqlDataSource)dataSource).setUrl(URL); ((MysqlDataSource)dataSource).setUser(USERNAME); ((MysqlDataSource)dataSource).setPassword(PASSWORD); }*/ //懒汉模式 //只有首次调用getDataSource方法 才会实例化 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){//释放资源 //注释掉的方式更安全 /*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(); } } } 

 //DAO Data Access Object 数据访问对象 public class BookDAO { //1.新增书籍 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.查看所有书籍 public List selectAll(){ List 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.根据名字找书籍 public List selectByName(String name) { List 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("nam
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：MySQL分库分表详情_Mysql_
                                                                
下一篇：MySQL深分页问题解决的实战记录_Mysql_