1.所需功能：
游覽所有圖書、查找書籍、建立新圖書，借出和歸還圖書，刪除某一圖書
2.流程：
數(shù)據(jù)庫連接
選擇需要的操作
釋放連接
3.環(huán)境及其語言：
Idea，JDK13，驅(qū)動(dòng)包Microsoft JDBC Driver 8.2.2 for SQL Server
4.數(shù)據(jù)庫建立
數(shù)據(jù)庫：sqlserver2019
Database：library
Table：Book
Book：書籍編號(hào)，書籍名稱，書籍作者，庫存量
管理員名：sa
密碼：123456
5.操作：
數(shù)據(jù)庫連接：sql.T3.Test1.java
官網(wǎng)下載驅(qū)動(dòng)包Microsoft JDBC Driver 8.2.2 for SQL Server并加載到程序中。
//注冊(cè)驅(qū)動(dòng)
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.按編號(hào)查找;2.按書名查找;3.按作者查找;4.放棄操作;");
b_h2 = sc.nextInt();
f_l = true;
switch (b_h2){
case 1:
System.out.println("輸入書籍編號(hào):");
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("對(duì)象已放棄操作");
}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)入：請(qǐng)依次輸入書籍的編號(hà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.按編號(hào)查找;2.按書名查找;3.按作者查找;4.放棄操作;");
b_h2 = sc.nextInt();
f_l = true;
switch (b_h2){
case 1:
System.out.println("輸入書籍編號(hào):");
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("確認(rèn)借出:確認(rèn):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("對(duì)象已放棄操作");
}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不為空時(shí)
{
closeRs(rs);
closeSt(st);
closeConn(conn);
}
public static void release(Connection conn, Statement st)// 當(dāng)rs為空時(shí)
{
closeSt(st);
closeConn(conn);
}
6.運(yùn)行結(jié)果演示
sqlserver中的部分?jǐn)?shù)據(jù)：
idea中的演示結(jié)果：
顯示書籍：
查找書籍及借出：
文章來源：http://www.zghlxwxcb.cn/news/detail-483044.html
歸還書籍及刪除書籍：
文章來源地址http://www.zghlxwxcb.cn/news/detail-483044.html
到了這里，關(guān)于基于java和sqlserver建立的簡(jiǎn)易圖書管理系統(tǒng)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
