图书管理系统

1、需求
2、建表
3、log4j.properties
4、db.properties
5、PropertiesUtil
6、DBUtil
7、IRowMapper
8、PublisherManager
9、BookManager
10、Client

1、需求

已知如下两个表：

publisher id name(唯一) address

book id isbn name publisher_id

欢迎进入书籍管理系统
1、出版社管理：增、删（name）、改(name)、查（name）
2、书籍管理：增、删（name）、改(name)、查（name）
3、退出

2、建表

create table publisher(
	   id char(36) primary key,
	   name varchar(24) unique,
	   address varchar(120)
)
   
create table book(
	   id char(36) primary key,
	   isbn varchar(12) unique,
	   name varchar(24),
	   publisher_id char(36),
	   #外键约束
	   foreign key(publisher_id) references publisher(id)
)

3、log4j.properties

因为在控制台打印的信息有限，所以需要把相关的日志文件保存在一个日志文件中，便于查找问题

导入log4j的jar包并配置log4j.properties

# DEBUG设置输出日志级别，由于为DEBUG，所以ERROR、WARN和INFO 级别日志信息也会显示出来
log4j.rootLogger=DEBUG,Console,RollingFile

#将日志信息输出到控制台
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern= [%-5p]-[%d{yyyy-MM-dd HH:mm:ss}] -%l -%m%n
#将日志信息输出到操作系统D盘根目录下的log.log文件中
log4j.appender.RollingFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.RollingFile.File=D://log.log
log4j.appender.RollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.RollingFile.layout.ConversionPattern=%d [%t] %-5p %-40.40c %X{traceId}-%m%n

4、db.properties

使用JDBC连接数据库的时候有时候需要修改连接的配置信息，如果每次修改都更改源代码不利于项目的维护，所以这里是使用db.properties文件把JDBC的配置信息保存在其中。

url=jdbc:mysql://127.0.0.1:3306/test
userName=root
password=root

5、PropertiesUtil

上面已经把数据库的相关配置信息保存在了db.properties文件中。我们使用一个PropertiesUtil类读取db.properties文件

/**
 * 获取配置文件中的信息
 *
 * @author Administrator
 */
public class PropertiesUtil {
	private static Properties properties = new Properties();
	static {
		InputStream inputStream = PropertiesUtil.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			properties.load(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取配置信息
	 *
	 * @author Administrator
	 */
	public static String getValue(String key) {
		return properties.getProperty(key);
	}
}

6、DBUtil

在上面已经获得了数据库连接的配置信息，创建一个DBUtil类来对数据库进行增删改查。

/**
 * 数据库连接工具类
 *
 * @author Administrator
 */
public class DBUtil {
	public static Logger logger = Logger.getLogger(DBUtil.class);
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			logger.debug(e.getMessage(), e);
		}
	}
	/**
	 * 获取连接
	 *
	 * @author Administrator
	 */
	private static Connection getConnection() {
		try {
			String url = PropertiesUtil.getValue("url");
			String userName = PropertiesUtil.getValue("userName");
			String password = PropertiesUtil.getValue("password");
			return DriverManager.getConnection(url, userName, password);
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
		}
		return null;
	}
	/**
	 * 工具类处理事务
	 *
	 * @author Administrator
	 */
	public static void transaction(String...sqls ) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String sql : sqls) {
				statement.addBatch(sql);
			}
			statement.executeBatch();
			connection.commit();
		} catch (SQLException e) {
			if(connection!=null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					logger.debug(e1.getMessage(), e1);
				}
			}
			logger.debug(e.getMessage(), e);
		}
	}
	/**
	 * 查询数据库是否存在特定数据
	 *
	 * @author Administrator
	 */
	public static boolean exist(String name, String sql) {
		class RowMapper implements IRowMapper {
			boolean state = false;
			@Override
			public void rowMapper(ResultSet resultSet) {
				try {
					while (resultSet.next()) {
						state = true;
					}
				} catch (SQLException e) {
					logger.debug(e.getMessage(), e);
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		DBUtil.query(sql, rowMapper, name);
		return rowMapper.state;
	}
	/**
	 * 查询（不能防止SQL注入）
	 *
	 * @author Administrator
	 */
	public static void query(String sql, IRowMapper rowMapper) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			rowMapper.rowMapper(resultSet);
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
		} finally {
			close(resultSet, statement, connection);
		}
	}
	/**
	 * 更改(不能防止SQL注入)
	 *
	 * @author Administrator
	 */
	public static boolean update(String sql) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			return statement.executeUpdate(sql) > 0;
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
		} finally {
			close(statement, connection);
		}
		return false;
	}
	/**
	 * 释放资源
	 *
	 * @author Administrator
	 */
	private static void close(Statement statement, Connection connection) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			logger.debug(e.getMessage(), e);
		}
	}
	/**
	 * 释放资源
	 *
	 * @author Administrator
	 */
	private static void close(ResultSet resultSet, Statement statement, Connection connection) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			logger.debug(e.getMessage(), e);
		}

		close(statement, connection);
	}
	
	/**
	 * 更改数据数据，可以防止SQL注入
	 *
	 * @author Administrator
	 */
	public static boolean update(String sql, Object... params) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 1; i <= params.length; i++) {
				preparedStatement.setObject(i, params[i - 1]);
			}
			return preparedStatement.executeUpdate() > 0;
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
		} finally {
			close(preparedStatement, connection);
		}
		return false;
	}
	/**
	 * 查询数据库信息(防止SQL注入)
	 *
	 * @author Administrator
	 */
	public static void query(String sql, IRowMapper rowMapper, Object... params) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 1; i <= params.length; i++) {
				preparedStatement.setObject(i, params[i - 1]);
			}
			resultSet = preparedStatement.executeQuery();
			rowMapper.rowMapper(resultSet);
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
		} finally {
			close(resultSet, preparedStatement, connection);
		}
	}
}

7、IRowMapper

/**
 * 实现JDBC查询数据库的接口
 *
 * @author Administrator
 */
public interface IRowMapper {
	void rowMapper(ResultSet resultSet);
}

通过接口回调可以确保在ResultSet关闭之前操作ResultSet对象

8、PublisherManager

出版社管理系统具体代码实现

/**
 * 出版社管理
 *
 * @author Administrator
 */
public class PublisherManager {
	/**
	 * 出版社管理菜单
	 *
	 * @author Administrator
	 */
	public  void publisherManagerMenu() {
		System.out.println("1.增加出版社");
		System.out.println("2.删除出版社");
		System.out.println("3.修改出版社");
		System.out.println("4.查询出版社");
		Scanner scanner = new Scanner(System.in);
		String choosePublisher = scanner.nextLine();
		switch (choosePublisher) {
		case "1": {
			addPublisher();
			break;
		}
		case "2": {
			deletePublisher();
			break;
		}
		case "3": {
			updatePublisher();
			break;
		}
		case "4": {
			selectPublisher();
			break;
		}
		default:
			System.out.println("请输入1-4");
			break;
		}
	}
	/**
	 * 查询出版社
	 *
	 * @author Administrator
	 */
	private static void selectPublisher() {
		System.out.println("请输入要查询的出版社名字");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "select * from publisher where name = ?";
		IRowMapper rowMapper = new IRowMapper() {
			@Override
			public void rowMapper(ResultSet resultSet) {
				try {
					if (resultSet.next()) {
						System.out.println("出版社名" + resultSet.getString(2));
						System.out.println("出版社地址" + resultSet.getString(3));
					} else {
						System.out.println("要查询的" + name + "出版社不存在");
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		};
		DBUtil.query(sql, rowMapper, name);
	}
	/**
	 * 修改出版社
	 *
	 * @author Administrator
	 */
	private static void updatePublisher() {
		System.out.println("请输入要修改的出版社名字");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "select * from publisher where name = ?";
		if (DBUtil.exist(name, sql)) {
			System.out.println("请输入新的出版社名字");
			String newName = scanner.nextLine();
			if (!DBUtil.exist(newName, sql)) {
				System.out.println("请输入新的出版社地址");
				String newAddress = scanner.nextLine();
				sql = "update publisher set name = ? ,address = ? where name=?";
				if (DBUtil.update(sql, newName, newAddress, name)) {
					System.out.println("修改成功");
				}
			} else {
				System.out.println("出版社" + newName + "已经存在");
			}
		} else {
			System.out.println(name + "的不存在");
		}

	}
	/**
	 * 删除出版社
	 *
	 * @author Administrator
	 */
	private static void deletePublisher() {
		System.out.println("请输入要删除的出版社名字");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		//因为book表中的publisher_id指向publisher表中的id
		//所以在删除出版社的时候需要删除book表中对应该出版社的所有图书
		String sql = "select id from publisher where name = ?";
		if (DBUtil.exist(name, sql)) {
			class RowMapper implements IRowMapper {
				String id;
				@Override
				public void rowMapper(ResultSet resultSet) {
					try {
						if (resultSet.next()) {
							id = resultSet.getString("id");
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			RowMapper rowMapper = new RowMapper();
			DBUtil.query(sql, rowMapper, name);
			String publishId = rowMapper.id;
			sql = "delete from book where publisher_id = ?";
			DBUtil.update(sql, publishId);
			sql = "delete from publisher where name = ?";
			if (DBUtil.update(sql, name)) {
				System.out.println("删除成功");
			}
		} else {
			System.out.println(name + "出版社不存在");
		}
	}
	/**
	 * 添加出版社
	 *
	 * @author Administrator
	 */
	private static void addPublisher() {
		System.out.println("请输入要添加的出版社的名字");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "select id from publisher where name = ?";
		if (DBUtil.exist(name, sql)) {
			System.out.println(name + "出版社已经存在");
			return;
		}
		System.out.println("请输入要添加的出版社的地址");
		String address = scanner.nextLine();
		sql = "insert publisher (id,name,address) values (?,?,?)";
		String id = UUID.randomUUID().toString();
		DBUtil.update(sql, id, name, address);
		System.out.println("添加成功");
	}
}

9、BookManager

书籍管理系统具体代码实现

/**
 * 书籍管理
 *
 * @author Administrator
 */
public class BookManager {
	/**
	 * 书籍管理菜单
	 *
	 * @author Administrator
	 */
	public void bookManagerMenu() {
		System.out.println("1.增加图书");
		System.out.println("2.删除图书");
		System.out.println("3.修改图书");
		System.out.println("4.查询图书");
		Scanner scanner = new Scanner(System.in);
		String choosebook = scanner.nextLine();
		switch (choosebook) {
		case "1": {
			addBook();
			break;
		}
		case "2": {
			deleteBook();
			break;
		}
		case "3": {
			updateBook();
			break;
		}
		case "4": {
			selectBook();
			break;
		}
		default:
			System.out.println("请输入1-4");
			break;
		}
	}

	/**
	 * 查询书籍
	 *
	 * @author Administrator
	 */
	private static void selectBook() {
		System.out.println("请输入要查询的图书的书名");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "select * from book where name = ?";
		class RowMapper implements IRowMapper {
			String publisherId;

			@Override
			public void rowMapper(ResultSet resultSet) {
				try {
					if (resultSet.next()) {
						System.out.println("书名" + name);
						String isbn = resultSet.getString("isbn");
						System.out.println("isbn" + isbn);
						publisherId = resultSet.getString("publisher_id");
					} else {
						System.out.println(name + "不存在");
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		DBUtil.query(sql, rowMapper, name);
		if (rowMapper.publisherId != null) {
			class RowMapperPublisher implements IRowMapper {
				@Override
				public void rowMapper(ResultSet resultSet) {
					try {
						if (resultSet.next()) {
							String publisherName = resultSet.getString("name");
							System.out.println("出版社" + publisherName);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			sql = "select * from publisher where id = ?";
			RowMapperPublisher rowMapperPublisher = new RowMapperPublisher();
			DBUtil.query(sql, rowMapperPublisher, rowMapper.publisherId);
		}
	}

	/**
	 * 修改书籍
	 *
	 * @author Administrator
	 */
	private static void updateBook() {
		System.out.println("请输入要修改的图书的书名");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "select * from book where name = ?";
		if (DBUtil.exist(name, sql)) {
			class RowMapper implements IRowMapper {
				String publisherId;
				@Override
				public void rowMapper(ResultSet resultSet) {
					try {
						if (resultSet.next()) {
							publisherId = resultSet.getString("publisher_id");
						} else {
							System.out.println(name + "不存在");
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			RowMapper rowMapper = new RowMapper();
			DBUtil.query(sql, rowMapper, name);
			if (rowMapper.publisherId != null) {
				System.out.println("请输入新的出版社");
				String publisherName = scanner.nextLine();
				sql = "select * from publisher where name = ?";
				class RowMapperPublisherForBook implements IRowMapper {
					String rowMapperIdForPublisher;
					boolean state = false;
					@Override
					public void rowMapper(ResultSet resultSet) {
						try {
							if (resultSet.next()) {
								state = true;
								rowMapperIdForPublisher = resultSet.getString("id");
							} else {
								System.out.println(publisherName + "出版社不存在");
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
				RowMapperPublisherForBook rowMapperPublisherForBook = new RowMapperPublisherForBook();
				DBUtil.query(sql, rowMapperPublisherForBook, publisherName);
				if (rowMapperPublisherForBook.state) {
					System.out.println("请输入新的ISBN");
					String newIsbn = scanner.nextLine();
					System.out.println("请输入新的书名");
					String newName = scanner.nextLine();
					sql = "update book set isbn=?,name=?,publisher_id=? where name = ?";
					DBUtil.update(sql, newIsbn, newName, rowMapperPublisherForBook.rowMapperIdForPublisher, name);
					System.out.println("修改成功");
				}
			}
		} else {
			System.out.println("要修改的书籍不存在");
		}
	}

	/**
	 * 删除书籍
	 *
	 * @author Administrator
	 */
	private static void deleteBook() {
		System.out.println("请输入要删除的图书的书名");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "delete from book where name=?";
		if (DBUtil.update(sql, name)) {
			System.out.println("删除成功");
		} else {
			System.out.println("书名为" + name + "的书籍不存在");
		}
	}

	/**
	 * 添加书籍
	 *
	 * @author Administrator
	 */
	private static void addBook() {
		System.out.println("请输入要增加的图书的书名");
		Scanner scanner = new Scanner(System.in);
		String name = scanner.nextLine();
		String sql = "select id from book where name = ?";
		if (DBUtil.exist(name, sql)) {
			System.out.println(name + "图书已经存在");
			return;
		}
		System.out.println("请输入要添加的书籍isbn");
		String isbn = scanner.nextLine();
		System.out.println("请输入要添加的图书所属的出版社");
		String publisherName = scanner.nextLine();
		String id = UUID.randomUUID().toString();
		sql = "select id from publisher where name = ?";
		class RowMapper implements IRowMapper {
			String publisher_id;

			@Override
			public void rowMapper(ResultSet resultSet) {
				try {
					if (resultSet.next()) {
						publisher_id = resultSet.getString("id");
					} else {
						System.out.println("出版社不存在");
						return;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		DBUtil.query(sql, rowMapper, publisherName);
		if (rowMapper.publisher_id != null) {
			sql = "insert book (id,isbn,name,publisher_id) values (?,?,?,?)";
			DBUtil.update(sql, id, isbn, name, rowMapper.publisher_id);
			System.out.println("添加成功");
		}
	}
}

10、Client

简单的管理界面

/**
 * 书籍管理系统
 *
 * @author Administrator
 */
public class Client {
	public static void main(String[] args) {
		while (true) {
			menu();
		}
	}
	/**
	 * 主菜单
	 *
	 * @author Administrator
	 */
	private static void menu() {
		System.out.println("1、出版社管理：");
		System.out.println("2、书籍管理：");
		System.out.println("3、退出");
		Scanner scanner = new Scanner(System.in);
		String choose = scanner.nextLine();
		switch (choose) {
		case "1": {
			new PublisherManager().publisherManagerMenu();
			break;
		}
		case "2": {
			new BookManager().bookManagerMenu();
			break;
		}
		case "3": {
			System.exit(0);
			break;
		}
		default:
			System.out.println("请输入1-3的数字");
		}
	}
}

练习-Java代码实现简单的图书管理系统1、需求2、建表3、log4j.properties4、db.properties5、PropertiesUtil6、DBUtil7、IRowMapper8、PublisherManager9、BookManager10、Client

图书管理系统

1、需求

2、建表

3、log4j.properties

4、db.properties

5、PropertiesUtil

6、DBUtil

7、IRowMapper

8、PublisherManager

9、BookManager

10、Client

继续阅读

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

NOSQL安全攻击

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

登录plsql 报错 the account is locked --用户被锁

Effective Java 8:通用程序设计

SequoiaDB巨杉数据库C++驱动概述

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method