利用java和sqlserver建立简易图书管理系统的完整步骤

目录1.所需功能：2.流程：3.环境及其语言：4.数据库建立5.操作：数据库连接：sql.T3.Test1.java选择需要的操作：sql.T3.Test1.java1.游览所有图书2.查找书籍3.建立新图书4借出图书5. 归还图书删除某一图书6.删除某一图书释放连接: sql.T1.JDBCUtil.java总结

1.所需功能：

游览所有图书、查找书籍、建立新图书，借出和归还图书，删除某一图书

2.流程：

数据库连接

选择需要的操作释放连接

3.环境及其语言：

Idea，JDK13，驱动包Microsoft JDBC Driver 8.2.2 for SQL Server

4.数据库建立

5.操作：

数据库连接：sql.T3.Test1.java

官网下载驱动包Microsoft JDBC Driver 8.2.2 for SQL Server并加载到程序中。

//注册驱动

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();

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:

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");

ggiBnv 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();

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();

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:

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;

http:// 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)//当rs不为空时

{

closeRs(rs);

closeSt(st);

closeConn(conn);

}

public static void release(Connection conn, Statement st)// 当rs为空时

{

closeSt(st);

closeConn(conn);

}

6.运行结果演示

sqlserver中的部分数据：

idea中的演示结果：

显示书籍：

查找书籍及借出：

归还书籍及删除书籍：

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。