简单的图书管理系统(练习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";

	@Override
	public Connection getConn() throws Exception {
		// TODO Auto-generated method stub
		Connection conn = null;
		try {
			Class.forName(DRIVER_CLASS);
			conn = DriverManager.getConnection(URL, USER, PWD);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		return conn;
	}

	@Override
	public void closeConn(ResultSet rs, PreparedStatement ps, Connection conn)
			throws Exception {
		// TODO Auto-generated method stub
		try {
			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();

	@Override
	public User login(User user) throws Exception {
		// TODO Auto-generated method stub
		User 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;

	@Override
	public List<Book> findBooks() throws Exception {
		// TODO Auto-generated method stub
		List<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;
	}

	@Override
	public int addBook(Book book) throws Exception {
		// TODO Auto-generated method stub
		try {
			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;
	}

	@Override
	public Book findBookByBookName(String bookName) throws Exception {
		// TODO Auto-generated method stub
		Book 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;
	}

	@Override
	public int deleteBook(String bookName) throws Exception {
		// TODO Auto-generated method stub
		try {
			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;
	}

	@Override
	public int modifyBook(Book book) throws Exception {
		// TODO Auto-generated method stub
		try {
			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数据库用着还是比较的顺手,感觉很好用,下阶段我要继续加油哦!加油!加油!加油!重要的事情说三遍!


©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页