JAVA实现图书管理系统（JDBC）

在之前的单纯用集合做了一个图书管理系统，这周刚学了JDBC，在之前的基础上用JDBC来实现图书管理系统。效果如图：


代码如下：
jdbc.properties

url=jdbc:mysql:///mysql
user=root
password=123456
driver=com.mysql.jdbc.Driver

DBUtils

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;



    public class DBUtils {
        private static String url;
        private static String user;
        private static String password;
        private static String driver;

        static{
            try {
                Properties properties = new Properties();
                ClassLoader classLoader = DBUtils.class.getClassLoader();
                URL res = classLoader.getResource("jdbc.properties");
                String path = res.getPath();
                properties.load(new FileReader(path));
                //获取数据
                url = properties.getProperty("url");
                user = properties.getProperty("user");
                password = properties.getProperty("password");
                driver = properties.getProperty("driver");
                Class.forName(driver);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        public static Connection getConn() throws SQLException {
            return DriverManager.getConnection(url, user, password);
        }

        public static void close(Statement stat, Connection conn, ResultSet res) {
            if( stat != null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if( conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if( res != null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

Book

public class Book {
    private String bookName;
    private int bookId;
    private String bookAut;
    private String bookNum;
    public Book(){

    }

    public Book(String bookName, int bookId, String bookAut, String bookNum) {
        super();
        this.bookName = bookName;
        this.bookId = bookId;
        this.bookAut = bookAut;
        this.bookNum = bookNum;
    }

    public String getBookName() {
        return bookName;
    }

    public int getBookId() {
        return bookId;
    }

    public String getBookAut() {
        return bookAut;
    }

    public String getBookNum() {
        return bookNum;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public void setBookAut(String bookAut) {
        this.bookAut = bookAut;
    }

    public void setBookNum(String bookNum) {
        this.bookNum = bookNum;
    }
}

BookDao

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet res;

    //添加图书
    public boolean addbook(Book book){
        String sql="insert into library(id,name,aut,num) values(?,?,?,?)";
        try {
            conn=DBUtils.getConn();
            pst=conn.prepareStatement(sql);
            pst.setInt(1,book.getBookId());
            pst.setString(2,book.getBookName());
            pst.setString(3,book.getBookAut());
            pst.setString(4,book.getBookNum());
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(pst,conn,res);
        }
        return true;
    }

    //查看图书
    public List<Book> list(){
        List<Book> list=new ArrayList<Book>();
        String sql="select * from library";
        try {
            conn=DBUtils.getConn();
            pst=conn.prepareStatement(sql);
            res=pst.executeQuery();
            while (res.next()){
                Book book=new Book();
                book.setBookId(res.getInt("id"));
                book.setBookName(res.getString("name"));
                book.setBookAut(res.getString("aut"));
                book.setBookNum(res.getString("num"));
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(pst,conn,res);
        }
        return list;
    }


    //修改图书
    public boolean updatebook(Book book){
        String sql="update library set id=?,name=?,aut=?,num=?";
        try {
            conn=DBUtils.getConn();
            pst=conn.prepareStatement(sql);
            pst.setInt(1,book.getBookId());
            pst.setString(2,book.getBookName());
            pst.setString(3,book.getBookAut());
            pst.setString(4,book.getBookNum());
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(pst,conn,res);
        }
        return true;
    }


    //删除图书
    public boolean delbook(int id){
        String sql="delete from library where id=?";
        try {
            conn=DBUtils.getConn();
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(pst,conn,res);
        }
        return true;
    }
}

Library

import java.util.List;
import java.util.Scanner;

public class Library {
    public void menu(){
        while (true){
        System.out.println("------高校图书管理系统------");
        System.out.println("*****  1.查看所有图书  *****");
        System.out.println("*****  2.添加图书信息  *****");
        System.out.println("*****  3.删除图书信息  *****");
        System.out.println("*****  4.修改图书信息  *****");
        System.out.println("*****  5.退出          *****");
        Scanner sc =new Scanner(System.in);
        System.out.println("*****  请输入你要进行的操作（1-5）：*****");

        String bookfunction = sc.nextLine();
        switch (bookfunction){
            case "1":
                findbook();
                break;
            case "2":
                addbook();
                break;
            case "3":
                deletebook();
                break;
            case "4":
                updatebook();
                break;
            case "5":
                System.out.println("成功退出图书管理系统！");
                System.exit(0);
                break;
            }

        }
    }




    //查看图书
    public static void findbook(){
        BookDao dao=new BookDao();
        List<Book> list= dao.list();
        System.out.println("编号\t书名\t作者\t数量");
        for (Book bookList:list){
            System.out.println(" "+bookList.getBookId()+"       "+bookList.getBookName()+"        "+bookList.getBookAut()+"        "+bookList.getBookNum());
        }
    }


    //添加图书
    public static void addbook(){
        Scanner sc=new Scanner(System.in);
        String name,aut,num;
        int id;
        System.out.println("请输入编号");
        id=sc.nextInt();
        System.out.println("请输入书名");
        Scanner sc1=new Scanner(System.in);
        name=sc1.nextLine();
        System.out.println("请输入作者");
        aut=sc1.nextLine();
        System.out.println("请输入数量");
        num=sc1.nextLine();
        Book book= new Book(name, id, aut, num);
        BookDao dao=new BookDao();
        boolean flag=dao.addbook(book);
        if (flag){
            System.out.println("保存成功！");
        }else {
            System.out.println("保存失败！");
        }
    }


    //删除图书
    public static void deletebook(){
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入删除图书的编号：");
        int delID =sc.nextInt();
        BookDao dao=new BookDao();
        boolean flag=dao.delbook(delID);
        if (flag){
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }
    }


    //修改图书
    public static void updatebook(){
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入修改图书的编号：");
        int updID=sc.nextInt();
        System.out.println("请输入书名");
        Scanner sc1=new Scanner(System.in);
        String name=sc1.nextLine();
        System.out.println("请输入作者");
        String aut=sc1.nextLine();
        System.out.println("请输入数量");
        String num=sc1.nextLine();
        Book book=new Book(name,updID,aut,num);
        BookDao dao=new BookDao();
        boolean flag=dao.updatebook(book);
        if (flag){
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }
    }
}

最后写一个测试类Test

public class Test {
    public static void main(String[] args) {
        Library l=new Library();
        l.menu();
    }
}

总结
前面对于JDBC的连接等都封装在工具类里面，设计好一个图书实体类，在BookDao方法类里面写好每一个需要用到的方法，作为图书数据管理访问对象，在最后的菜单选择实现并调用方法等。其中也要在Mysql数据库里面建好相对应的表和字段。