sql:
create database mybooks
drop table if exists users;
create table `users`( id int(11) not null primary key auto_increment, username varchar(20) not null, password varchar(20) not null, role varchar(20) default 'user' )default charset=utf8;
drop table if exists books;
create table `books`(id int not null primary key auto_increment, isbn varchar(20) not null,title varchar(20) not null,author varchar(20) not null,unitprice decimal(10,0),num int default 0)default charset=utf8;
项目结构:
实体类:
package com.books.bean;
public class Books {
private int id;
private String isbn;
private String title;
private String author;
private Double unitprice;
private Integer num;
public Books() {
super();
}
public Books(int id, String isbn, String title, String author, Double unitprice, Integer num) {
super();
this.id = id;
this.isbn = isbn;
this.title = title;
this.author = author;
this.unitprice = unitprice;
this.num = num;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Double getUnitprice() {
return unitprice;
}
public void setUnitprice(Double unitprice) {
this.unitprice = unitprice;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
}
package com.books.bean;
/**
*实体类
*/
public class Users {
private Integer id;
private String username;
private String password;
private String role;
public Users() {
super();
}
public Users(Integer id, String username, String password, String role) {
super();
this.id = id;
this.username = username;
this.password = password;
this.role = role;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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;
}
public String getRole() {
return role;
}
public void setRole(String role) {
this.role = role;
}
}
常量池
package com.books.common;
public class Constants {
public static String GLOBLEROLE="user";
}
工具类
package com.books.util;
public class Tool {
/**
*传入要答应的信息
*/
public static void showMassage(String info){
System.out.println(info);
}
}
package com.books.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* jdbc
*/
public interface SqlHelper {
Connection getConnection() throws ClassNotFoundException, SQLException;
void closeAll() throws SQLException;
ResultSet queryMethod(String sql,Object[]param) throws ClassNotFoundException, SQLException;
Boolean updateOrDelete(String sql,Object[]param) throws ClassNotFoundException, SQLException;
}
package com.books.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SqlHelperImpl implements SqlHelper {
public static SqlHelperImpl instance;
private static final String url="jdbc:mysql://192.168.0.180:3306/mybooks?setCharacter=utf8";
private static final String user="yaya";
private static final String password="123qqq...A";
private static final String driverName="org.mariadb.jdbc.Driver";
private Connection conn=null;
private PreparedStatement pstmt=null;
private ResultSet rs=null;
private SqlHelperImpl() {
super();
}
public static SqlHelperImpl getInstance(){
if(null==instance){
synchronized (SqlHelperImpl.class) {
if(null==instance){
instance = new SqlHelperImpl();
}
return instance;
}
}
return instance;
}
@Override
public Connection getConnection() throws ClassNotFoundException, SQLException {
//Class.forName(driverName);
DriverManager.registerDriver(new org.mariadb.jdbc.Driver());
conn = DriverManager.getConnection(url, user, password);
return conn;
}
@Override
public void closeAll() throws SQLException {
if(rs!=null)rs.close();
if(pstmt!=null)pstmt.close();
if(conn!=null)conn.close();
}
@Override
public ResultSet queryMethod(String sql, Object[] param) throws ClassNotFoundException, SQLException {
getConnection();
pstmt = conn.prepareStatement(sql);
if(pstmt!=null){
for(int i=0;i<param.length;i++){
pstmt.setObject(i+1, param[i]);
}
rs=pstmt.executeQuery();
}
return rs;
}
@Override
public Boolean updateOrDelete(String sql, Object[] param) throws ClassNotFoundException, SQLException{
getConnection();
int j=-1;
pstmt=conn.prepareStatement(sql);
if(pstmt!=null){
for(int i=0;i<param.length;i++){
pstmt.setObject(i+1, param[i]);
}
j=pstmt.executeUpdate();
}
if(j>=0){
return true;
}else{
return false;
}
}
}
逻辑层:
package com.books.biz;
import java.util.List;
import com.books.bean.Books;
import com.books.dao.Ibooks;
import com.books.dao.impl.IbooksImpl;
public class BookManager {
Ibooks ibooks = new IbooksImpl();
/**
* 添加
*/
public boolean enterBook(Books books){
return ibooks.insertBooks(books);
}
/**
* 删除
*/
public boolean outBook(String isbn){
Books books =new Books();
books.setIsbn(isbn);
books = ibooks.getBookByIsbn(books);
if(books!=null){
return ibooks.deleteBooks(books);
}else{
return false;
}
}
public Books select(String isbn){
Books books =new Books();
books.setIsbn(isbn);
books = ibooks.getBookByIsbn(books);
return books;
}
/**
*查询
*/
public List<Books> getAllBooks(){
return ibooks.getAllBooks();
}
}
package com.books.biz;
import com.books.bean.Users;
import com.books.dao.Iusers;
import com.books.dao.impl.IusersImpl;
public class UserManager {
Iusers iusers =new IusersImpl();
public Users login(String username,String password){
Users users = new Users();
users.setUsername(username);
users = iusers.getUserByName(users);
if(password.equals(users.getPassword())){
return users;
}
return null;
}
}
dao层
package com.books.dao;
import java.util.List;
import com.books.bean.Books;
/**
*查询书籍
*/
public interface Ibooks {
public boolean insertBooks(Books books);
public boolean deleteBooks(Books books);
public void updateBooks(Books books);
public List<Books> getBooksByTitle(Books books);
public Books getBookByIsbn(Books books);
public List<Books> getAllBooks();
}
package com.books.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.books.bean.Books;
import com.books.dao.Ibooks;
import com.books.util.SqlHelper;
import com.books.util.SqlHelperImpl;
public class IbooksImpl implements Ibooks {
private SqlHelper sqlHelper=null;
public IbooksImpl() {
sqlHelper = SqlHelperImpl.getInstance();
}
@Override
public boolean insertBooks(Books books) {
String sql="insert into books (isbn,title,author,unitprice,num) values (?,?,?,?,?)";
Object [] param = {books.getIsbn(),books.getTitle(),books.getAuthor(),books.getUnitprice(),books.getNum()};
try {
if(sqlHelper.updateOrDelete(sql, param)){
System.out.println("插入成功");
return true;
}else{
System.out.println("插入失败");
return false;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
@Override
public boolean deleteBooks(Books books) {
String sql = String.format("delete from books where id=%d", books.getId());
Object [] param={};
try {
if(sqlHelper.updateOrDelete(sql, param)){
System.out.println("删除成功");
return true;
}else{
System.out.println("删除失败");
return false;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
@Override
public void updateBooks(Books books) {
String sql = String.format("update books set isbn=%s,title=%s,author=%s,unitprice=%d,num=%d where id=%d",
books.getIsbn(),books.getTitle(),books.getAuthor(),books.getUnitprice(),books.getNum(),books.getNum());
Object [] param={};
try {
if(sqlHelper.updateOrDelete(sql, param)){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public List<Books> getBooksByTitle(Books books) {
List<Books> li = new ArrayList<Books>();
String sql="select * from books where title=?";
Object [] param = {books.getTitle()};
try {
ResultSet rs = sqlHelper.queryMethod(sql, param);
if(rs!=null){
while(rs.next()){
Books bk= new Books();
bk.setId(rs.getInt(1));
bk.setIsbn(rs.getString(2));
bk.setTitle(rs.getString(3));
bk.setAuthor(rs.getString(4));
bk.setUnitprice(rs.getDouble(5));
bk.setNum(rs.getInt(6));
li.add(bk);
}
return li;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public Books getBookByIsbn(Books books) {
String sql = String.format("select * from books where isbn='%s'", books.getIsbn());
Object [] param = {};
try {
ResultSet rs = sqlHelper.queryMethod(sql, param);
if(rs!=null){
while(rs.next()){
books.setId(rs.getInt(1));
books.setIsbn(rs.getString(2));
books.setTitle(rs.getString(3));
books.setAuthor(rs.getString(4));
books.setUnitprice(rs.getDouble(5));
books.setNum(rs.getInt(6));
}
return books;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return books;
}
@Override
public List<Books> getAllBooks() {
List<Books> li = new ArrayList<Books>();
String sql="select * from books";
Object [] param = {};
try {
ResultSet rs = sqlHelper.queryMethod(sql, param);
if(rs!=null){
while(rs.next()){
Books bk= new Books();
bk.setId(rs.getInt(1));
bk.setIsbn(rs.getString(2));
bk.setTitle(rs.getString(3));
bk.setAuthor(rs.getString(4));
bk.setUnitprice(rs.getDouble(5));
bk.setNum(rs.getInt(6));
li.add(bk);
}
return li;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
package com.books.dao;
import com.books.bean.Users;
/**
*@author toshiba
*/
public interface Iusers {
/**
* 查询用户实体
*/
public Users getUserByName(Users users);
}
package com.books.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.books.bean.Users;
import com.books.dao.Iusers;
import com.books.util.SqlHelper;
import com.books.util.SqlHelperImpl;
public class IusersImpl implements Iusers{
private SqlHelper sqlHelper=null;
public IusersImpl() {
sqlHelper=SqlHelperImpl.getInstance();
}
@Override
public Users getUserByName(Users users) {
String sql = "select * from users where username=?";
Object [] param = {users.getUsername()};
try {
ResultSet rs =sqlHelper.queryMethod(sql, param);
if(rs!=null){
while(rs.next()){
users.setId(rs.getInt("id"));
users.setRole(rs.getString("role"));
users.setPassword(rs.getString("password"));
}
return users;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
sqlHelper.closeAll();
} catch (SQLException e) {
e.printStackTrace();
}
}
return users;
}
}
交互
package com.books.ui;
import java.util.List;
import java.util.Scanner;
import com.books.bean.Books;
import com.books.bean.Users;
import com.books.biz.BookManager;
import com.books.biz.UserManager;
import com.books.common.Constants;
import com.books.util.Tool;
public class Test {
private BookManager bookManager=null;
private UserManager userManager=null;
public Test() {
bookManager = new BookManager();
userManager = new UserManager();
}
public static void main(String[] args) {
new Test().start();
}
private void start(){
Users user = login();
Constants.GLOBLEROLE=user.getRole();
while(true){
showMenu();
Scanner scan = new Scanner(System.in);
int opl = scan.nextInt();
switch (opl) {
case 1:
enterBook();
break;
case 2:
boolean ot=bookManager.outBook("0110-2231-334400");
if(ot){
System.out.println("出库成功");
}else{
System.out.println("出库失败");
}
break;
case 3:
Tool.showMassage("请输入你要查询的isbn号:");
Scanner scand = new Scanner(System.in);
String aa =scand.next();
Books book=bookManager.select(aa);
if(book!=null){
Tool.showMassage("isbn:\t"+book.getIsbn()+"author:\t"+book.getAuthor()+"title:\t"+book.getTitle());
}
break;
case 4:
List<Books> li = bookManager.getAllBooks();
li.forEach(item->{
Tool.showMassage("isbn:\t"+item.getIsbn()+"author:\t"+item.getAuthor()+"title:\t"+item.getTitle());
});
break;
case 5:
Tool.showMassage(">>>>>退出<<<<<");
System.exit(0);
break;
default:
break;
}
}
}
private void enterBook(){
bookManager.enterBook(new Books(1,"0110-2231-334400","java","tom",66.85,20));
bookManager.enterBook(new Books(2,"0220-2232-334422","python","tos",35.85,10));
}
private void showMenu(){
if("admin".equals(Constants.GLOBLEROLE)){
Tool.showMassage("请选择你的操作:");
Tool.showMassage("1.图书入库\t2.图书出库\t3.图书查询\t4.查看所有\t5.退出系统");
}else{
Tool.showMassage("请选择你的操作:");
Tool.showMassage("3.图书查询\t4.查看所有\t5.退出系统");
}
}
private Users login(){
Users user=null;
while(true){
Scanner scan = new Scanner(System.in);
System.out.println("请输入用户名:");
String userName = scan.next();
System.out.println("请输入密码:");
String password = scan.next();
user=userManager.login(userName, password);
if(user==null){
System.out.println("账号名和密码有误,请重试");
}else{
System.out.println("恭喜你,登录成功");
break;
}
}
return user;
};
}
异常:
package com.books.exception;
public class BookShopException extends Exception{
/**
*
*/
private static final long serialVersionUID = 1L;
public BookShopException() {
super();
}
public BookShopException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
super(message, cause, enableSuppression, writableStackTrace);
}
public BookShopException(String message, Throwable cause) {
super(message, cause);
}
public BookShopException(String message) {
super(message);
}
public BookShopException(Throwable cause) {
super(cause);
}
}