简单的图书管理系统（练习JDBC+Oracle数据库）

这是一个简单的图书管理系统，对部分功能的进行了实现，主要就是练习对数据库的增删改查和逻辑的运用！

1、使用的Oracle数据库，先建数据库，并插入几条简单的数据

2、在MyEclipse中建立Web Project，命名为bookManager，建包结构如下

3、首先写dao包中的IBaseDao,然后用OracleBaseDao实现方法连接数据库（getConnection）和关闭连接（closeCon）,代码如下 

package cn.dao.utils.impl;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import cn.dao.utils.interfaces.IBaseDao;/** * 使用Oracle数据库 *  * @author CCQ *  */public class oracleBaseDao implements IBaseDao {static String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";static String URL = "jdbc:oracle:thin:@localhost:1521:orcl";static String USER = "hope";static String PWD = "123456";@Overridepublic Connection getConn() throws Exception {// TODO Auto-generated method stubConnection conn = null;try {Class.forName(DRIVER_CLASS);conn = DriverManager.getConnection(URL, USER, PWD);} catch (Exception e) {throw new Exception(e.getMessage());}return conn;}@Overridepublic void closeConn(ResultSet rs, PreparedStatement ps, Connection conn)throws Exception {// TODO Auto-generated method stubtry {if (rs != null) {rs.close();}if (ps != null) {ps.close();}if (conn != null) {conn.close();}} catch (Exception e) {throw new Exception(e.getMessage());}}}

4、书写dao包中的UserDaoImpl，实现用户登录，再写bookDaoImpl，实现对图书的操作，代码如下

package cn.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import cn.dao.interfaces.IUserDao;import cn.dao.utils.impl.oracleBaseDao;import cn.dao.utils.interfaces.IBaseDao;import cn.po.User;/** * 用户操作实现类 * @author CCQ * */public class UserDaoImpl implements IUserDao {IBaseDao baseDao = new oracleBaseDao();@Overridepublic User login(User user) throws Exception {// TODO Auto-generated method stubUser users = null;try {Connection conn = baseDao.getConn();String sql = "select * from users where username = ? and userpwd = ?";PreparedStatement ps = conn.prepareStatement(sql);ps.setObject(1, user.getUserName());ps.setObject(2, user.getUserPwd());ResultSet rs = ps.executeQuery();if(rs.next()){users = new User();users.setUserId(rs.getInt("userId"));users.setUserName(rs.getString("userName"));users.setUserPwd(rs.getString("userPwd"));}baseDao.closeConn(rs, ps, conn);} catch (Exception e) {throw new Exception(e);}return users;}}

package cn.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import cn.dao.interfaces.IBookDao;import cn.dao.utils.impl.oracleBaseDao;import cn.dao.utils.interfaces.IBaseDao;import cn.po.Book;/** * 对图书操作的实现类 *  * @author CCQ *  */public class BookDaoImpl implements IBookDao {IBaseDao baseDao = new oracleBaseDao();private Connection conn = null;private PreparedStatement ps = null;private ResultSet rs = null;int r = 0;@Overridepublic List<Book> findBooks() throws Exception {// TODO Auto-generated method stubList<Book> books = new ArrayList<Book>();try {conn = baseDao.getConn();String sql = "select * from books order by bookid";ps = conn.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {Book book = new Book();book.setBookId(rs.getInt("bookId"));book.setBookName(rs.getString("bookName"));book.setBookAutor(rs.getString("bookAutor"));book.setBookType(rs.getString("bookType"));book.setBookPrice(rs.getDouble("bookPrice"));books.add(book);}baseDao.closeConn(rs, ps, conn);} catch (Exception e) {throw new Exception(e);}return books;}@Overridepublic int addBook(Book book) throws Exception {// TODO Auto-generated method stubtry {conn = baseDao.getConn();String sql = "insert into books values(?,?,?,?,?)";ps = conn.prepareStatement(sql);ps.setObject(1, book.getBookId());ps.setObject(2, book.getBookName());ps.setObject(3, book.getBookAutor());ps.setObject(4, book.getBookType());ps.setObject(5, book.getBookPrice());r = ps.executeUpdate();baseDao.closeConn(rs, ps, conn);} catch (Exception e) {throw new Exception(e);}return r;}@Overridepublic Book findBookByBookName(String bookName) throws Exception {// TODO Auto-generated method stubBook book = null;try {conn = baseDao.getConn();String sql = "select * from books where bookname = ?";ps = conn.prepareStatement(sql);ps.setObject(1, bookName);rs = ps.executeQuery();if (rs.next()) {book = new Book();book.setBookId(rs.getInt("bookId"));book.setBookName(rs.getString("bookName"));book.setBookAutor(rs.getString("bookAutor"));book.setBookType(rs.getString("bookType"));book.setBookPrice(rs.getDouble("bookPrice"));}baseDao.closeConn(rs, ps, conn);} catch (Exception e) {throw new Exception(e);}return book;}@Overridepublic int deleteBook(String bookName) throws Exception {// TODO Auto-generated method stubtry {conn = baseDao.getConn();String sql = "delete books where bookname = ?";ps = conn.prepareStatement(sql);ps.setObject(1, bookName);r = ps.executeUpdate();baseDao.closeConn(rs, ps, conn);} catch (Exception e) {throw new Exception(e);}return r;}@Overridepublic int modifyBook(Book book) throws Exception {// TODO Auto-generated method stubtry {conn = baseDao.getConn();String sql = "update books set bookname = ?,bookautor = ?,booktype = ?,bookprice = ? where bookid = ?";ps = conn.prepareStatement(sql);ps.setObject(1, book.getBookName());ps.setObject(2, book.getBookAutor());ps.setObject(3, book.getBookType());ps.setObject(4, book.getBookPrice());ps.setObject(5, book.getBookId());r = ps.executeUpdate();baseDao.closeConn(rs, ps, conn);} catch (Exception e) {throw new Exception(e);}return r;}}

5、书写services包，中的接口类，和实现类，也就是调用相应的dao包的中方法(简单)。

6、书写一个BookManagerSys类来通过控制台输入验证功能是否正确，代码如下：

package cn.test;import java.util.ArrayList;import java.util.List;import java.util.Scanner;import cn.po.Book;import cn.po.User;import cn.services.impl.BookServiceImpl;import cn.services.impl.UserServiceImpl;import cn.services.interfaces.IBookService;import cn.services.interfaces.IUserService;/** * 图书管理系统界面 *  * @author CCQ *  */public class BookManagerSys {IUserService userService = new UserServiceImpl();IBookService bookService = new BookServiceImpl();Scanner input = new Scanner(System.in);public void menu() throws Exception {User user = new User();System.out.println("\n\t图书管理系统登录界面");System.out.print("用户名：");user.setUserName(input.next());System.out.print("密    码：");user.setUserPwd(input.next());user = userService.login(user);if (user != null) {bmenu();} else {System.out.println("用户名或密码错误，请重新输入！");menu();}}public void bmenu() throws Exception {System.out.println("\n\t图书管理系统主界面");System.out.println("1.添加图书");System.out.println("2.查看所有图书信息");System.out.println("3.删除图书");System.out.println("4.修改图书信息");System.out.println("5.退出系统");System.out.print("请选择(1-5)：");int op = input.nextInt();switch (op) {case 1:addBook();break;case 2:findBooks();break;case 3:deleteBook();break;case 4:modifyBook();break;case 5:System.out.println("谢谢使用图书管理系统！");System.exit(0);default:System.out.println("输入错误，请输入1-5数字");}}public void addBook() throws Exception {Book book = new Book();System.out.println("\n\t图书信息添加界面");System.out.print("图书编号：");book.setBookId(input.nextInt());System.out.print("图书名称：");book.setBookName(input.next());if (bookService.findBookByBookName(book.getBookName()) == null) {System.out.print("图书作者：");book.setBookAutor(input.next());System.out.print("图书类别：");book.setBookType(input.next());System.out.print("图书价格：");book.setBookPrice(input.nextDouble());int r = bookService.addBook(book);if (r != 0) {System.out.println(book.getBookName() + "添加成功！");bmenu();}} else {System.out.println(book.getBookName() + "已经存在，不可重复添加！");addBook();}}public void findBooks() throws Exception {System.out.println("\n\t\t查看所有图书信息界面");System.out.println("图书编号\t图书名称\t作者\t类别\t图书价格");List<Book> books = new ArrayList<Book>();books = bookService.findBooks();for (Book book : books) {System.out.println(book.getBookId() + "\t" + book.getBookName()+ "\t" + book.getBookAutor() + "\t" + book.getBookType()+ "\t" + book.getBookPrice());}bmenu();}public void deleteBook() throws Exception {System.out.println("\n\t\t删除图书信息界面");System.out.print("请输入删除图书名称：");String bookName = input.next();if (bookService.findBookByBookName(bookName) != null) {int r = bookService.deleteBook(bookName);if(r != 0){System.out.println("删除"+bookName+"成功！");bmenu();}} else {System.out.println("您输入的"+bookName+"不存在！");bmenu();}}public void modifyBook() throws Exception {Book book = new Book();System.out.println("\n\t\t修改图书信息界面");System.out.print("请输入修改的图书名称：");book.setBookName(input.next());if (bookService.findBookByBookName(book.getBookName()) != null) {book.setBookId(bookService.findBookByBookName(book.getBookName()).getBookId());System.out.print("请输入修改的图书作者：");book.setBookAutor(input.next());System.out.print("请输入修改的图书类别：");book.setBookType(input.next());System.out.print("请输入修改的图书价格：");book.setBookPrice(input.nextDouble());int r = bookService.modifyBook(book);if(r != 0){System.out.println("修改"+book.getBookName()+"成功！");bmenu();}else{System.out.println("修改失败！");}} else {System.out.println("您输入的"+book.getBookName()+"不存在！");bmenu();}}}

7、结果展示：

8、总结：

经过这一个星期的学习，具体把Java中的面对对象的思想进行了深刻的掌握，然后学习Oracle中的简单建表和一些简单地SQL的增删改查！感觉掌握的还是不错的，Oracle数据库用着还是比较的顺手，感觉很好用，下阶段我要继续加油哦！加油！加油！加油！重要的事情说三遍！