利用java和sqlserver建立簡易圖書管理系統(tǒng)的完整步驟

更新時間：2022年06月13日 11:02:31 作者：0°ic

圖書館管理系統(tǒng)是圖書館管理工作中不可缺少的部分,它對于圖書館的管理者和使用者都非常重要,下面這篇文章主要給大家介紹了關(guān)于利用java和sqlserver建立簡易圖書管理系統(tǒng)的完整步驟,需要的朋友可以參考下

1.所需功能：

游覽所有圖書、查找書籍、建立新圖書，借出和歸還圖書，刪除某一圖書

2.流程：

數(shù)據(jù)庫連接

選擇需要的操作
釋放連接

3.環(huán)境及其語言：

Idea，JDK13，驅(qū)動包Microsoft JDBC Driver 8.2.2 for SQL Server

4.數(shù)據(jù)庫建立

數(shù)據(jù)庫：sqlserver2019
Database：library
Table：Book
Book：書籍編號，書籍名稱，書籍作者，庫存量
管理員名：sa
密碼：123456

5.操作：

數(shù)據(jù)庫連接：sql.T3.Test1.java

官網(wǎng)下載驅(qū)動包Microsoft JDBC Driver 8.2.2 for SQL Server并加載到程序中。

//注冊驅(qū)動
	DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
//建立連接
String dbUL="jdbc:sqlserver://localhost:1433;DatabaseName=Library";
String Name="sa";
String Paw="123456";
conn=DriverManager.getConnection(dbUL,Name,Paw);

選擇需要的操作：sql.T3.Test1.java

1.游覽所有圖書

try{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    conn = DriverManager.getConnection(dbUL, Name, Paw);
    st = conn.createStatement();
    sql = "select * from Book";
    rs = st.executeQuery(sql);
    while (rs.next()){
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String aut = rs.getString("aut");
        int num = rs.getInt("num");
        System.out.println("id=" + id + " name=" + name + " aut:" + aut + " num:" + num);
    }
}catch (SQLException e){
    e.printStackTrace();
}finally{
    JDBCUtil.release(conn, st, rs);
}

2.查找書籍

try{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    conn = DriverManager.getConnection(dbUL, Name, Paw);
    st = conn.createStatement();
    System.out.println("查找方式:1.按編號查找;2.按書名查找;3.按作者查找;4.放棄操作;");
    b_h2 = sc.nextInt();
    f_l = true;
    switch (b_h2){
    case 1:
        System.out.println("輸入書籍編號:");
        book_num = sc.nextInt();
        sql1 = "select * from Book where id=" + book_num;
        break;
    case 2:
        System.out.println("輸入書籍名稱:");
        book_name = sc.next();
        sql1 = "select * from Book where name='" + book_name + "'";
        break;
    case 3:
        System.out.println("輸入書籍作者:");
        author = sc.next();
        sql1 = "select * from Book where id='" + author + "'";
        break;
    case 4:
        f_l = false;
        break;
    }
    if (f_l){
        sql = sql1;
        rs = st.executeQuery(sql);
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String aut = rs.getString("aut");
            int num = rs.getInt("num");
            System.out.println("id=" + id + " name=" + name + " aut:" + aut + " num:" + num);
        }
    }
    else
        System.out.println("對象已放棄操作");
}catch (SQLException e){
    e.printStackTrace();
}finally{
    JDBCUtil.release(conn, st, rs);
}

3.建立新圖書

try{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    conn = DriverManager.getConnection(dbUL, Name, Paw);
    st = conn.createStatement();
    System.out.println("書籍導(dǎo)入：請依次輸入書籍的編號、書名、作者和導(dǎo)入數(shù)量:");
    book_id = sc.nextInt();
    book_name = sc.next();
    author = sc.next();
    book_num = sc.nextInt();
    sql1 = "insert into Book values(" + book_id + ",'" + book_name + "','" + author + "'," + book_num + ")";
    st.executeUpdate(sql1);
}catch (SQLException e){
    e.printStackTrace();
}finally{
    JDBCUtil.release(conn, st);
}

4借出圖書

try{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    conn = DriverManager.getConnection(dbUL, Name, Paw);
    st = conn.createStatement();
    System.out.println("查找方式:1.按編號查找;2.按書名查找;3.按作者查找;4.放棄操作;");
    b_h2 = sc.nextInt();
    f_l = true;
    switch (b_h2){
    case 1:
        System.out.println("輸入書籍編號:");
        book_num = sc.nextInt();
        sql1 = "select * from Book where id=" + book_num;
        break;
    case 2:
        System.out.println("輸入書籍名稱:");
        book_name = sc.next();
        sql1 = "select * from Book where name='" + book_name + "'";
        break;
    case 3:
        System.out.println("輸入書籍作者:");
        author = sc.next();
        sql1 = "select * from Book where id='" + author + "'";
        break;
    case 4:
        f_l = false;
        break;
    }
    if (f_l){
        sql = sql1;
        rs = st.executeQuery(sql);
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String aut = rs.getString("aut");
            int num = rs.getInt("num");
            System.out.println("id=" + id + " name=" + name + " aut:" + aut + " num:" + num);
            b_h = num;
            book_id = id;
        }
        System.out.println("確認借出:確認:1,取消:2");
        b_h2 = sc.nextInt();
        if (b_h2 == 1){
            if (b_h == 0)
                System.out.println("館藏書目為0，無法借出");
            else{
                b_h--;
                sql1 = "UPDATE Book SET num=" + b_h + "WHERE id =" + book_id;
                st.executeUpdate(sql1);
            }
        }
        else
            System.out.println("你已取消操作");
    }
    else
        System.out.println("對象已放棄操作");
}catch (SQLException e)
{
    e.printStackTrace();
}finally{
    JDBCUtil.release(conn, st, rs);
}

5. 歸還圖書刪除某一圖書

try{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    conn = DriverManager.getConnection(dbUL, Name, Paw);
    st = conn.createStatement();
    System.out.println("輸入歸還書id:");
    book_id = sc.nextInt();
    sql = "select * from Book where id=" + book_id;
    rs = st.executeQuery(sql);
    while (rs.next()){
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String aut = rs.getString("aut");
        int num = rs.getInt("num");
        System.out.println("id=" + id + " name=" + name + " aut:" + aut + " num:" + num);
        b_h = num;
    }
    b_h++;
    sql1 = "UPDATE Book SET num=" + b_h + "WHERE id =" + book_id;
    st.executeUpdate(sql1);
}catch (SQLException e){
    e.printStackTrace();
}finally{
    JDBCUtil.release(conn, st, rs);
}

6.刪除某一圖書

try{
    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    conn = DriverManager.getConnection(dbUL, Name, Paw);
    st = conn.createStatement();
    System.out.println("輸入刪除書id:");
    book_id = sc.nextInt();
    sql1 = "delete from Book where id=" + book_id;
    st.executeUpdate(sql1);
}catch (SQLException e){
    e.printStackTrace();
}finally{
    JDBCUtil.release(conn, st, rs);
}

釋放連接: sql.T1.JDBCUtil.java

public static void release(Connection conn, Statement st, ResultSet rs)//當(dāng)rs不為空時
{
    closeRs(rs);
    closeSt(st);
    closeConn(conn);
}

public static void release(Connection conn, Statement st)// 當(dāng)rs為空時
{
    closeSt(st);
    closeConn(conn);
}

6.運行結(jié)果演示

sqlserver中的部分?jǐn)?shù)據(jù)：