java+jdbc简单实现图书，出版社管理

最新推荐文章于 2024-10-31 13:07:14 发布

Zzze0101

最新推荐文章于 2024-10-31 13:07:14 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

文章标签： jdbc 图书管理

本文链接：https://blog.youkuaiyun.com/Zzze0101/article/details/90296662

这篇博客介绍了一个使用Java JDBC实现的图书与出版社管理系统的详细步骤，包括log4j的日志配置、数据库连接参数的管理、DBUtil工具类、IRowMapper接口以及出版社和图书管理的相关操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 需求

已知如下两个表：

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

实现下述功能：

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

2. log4j.properties

程序运行所出现的异常信息有助于我们修改代码，而控制台所展示的信息有限，所以我们使用 log4j来打印异常日志文件帮助我们处理异常。
引入log4j-1.2.15.jar包，配置log4j.properties文件

# DEBUG\u8BBE\u7F6E\u8F93\u51FA\u65E5\u5FD7\u7EA7\u522B\uFF0C\u7531\u4E8E\u4E3ADEBUG\uFF0C\u6240\u4EE5ERROR\u3001WARN\u548CINFO \u7EA7\u522B\u65E5\u5FD7\u4FE1\u606F\u4E5F\u4F1A\u663E\u793A\u51FA\u6765
log4j.rootLogger=DEBUG,Console,RollingFile

#\u5C06\u65E5\u5FD7\u4FE1\u606F\u8F93\u51FA\u5230\u63A7\u5236\u53F0
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
#\u5C06\u65E5\u5FD7\u4FE1\u606F\u8F93\u51FA\u5230\u64CD\u4F5C\u7CFB\u7EDFD\u76D8\u6839\u76EE\u5F55\u4E0B\u7684log.log\u6587\u4EF6\u4E2D
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

3. db.properties

jdbc连接数据库时在DriverManager.getConnection(url, user, password)中的url，user，password经常需要修改，因此我们需要一个配置文件专门配置这些信息。

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

4. PropertiesUtil

package com.jd.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
 * 
 *
 * @author zzs
 */
public class PropertiesUtil {

	private static Properties properties = new Properties();
	static {
		InputStream inputStream = PropertiesUtil.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			properties.load(inputStream);//将properties文件中的每对key=value变为map集合中的键值对
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取key的value值
	 *
	 * @author zzs
	 */
	public static String getValue(String key) {
		return properties.getProperty(key);
	}
}

5. DBUtil

关于DButil工具类的描述详见简单实现DButil工具类

package com.jd.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import org.apache.log4j.Logger;

/**
 * 数据库工具类
 *
 * @author zzs
 */
public class DButil {
	static Connection con =null;
	static Statement sta =null;
	static String sql =null;
	static ResultSet result = null;
	/**
	 * 获取连接
	 *
	 * @author zzs
	 */
	private static Logger logger = Logger.getLogger(DButil.class);
	public static Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = PropertiesUtil.getValue("url");
			String userName = PropertiesUtil.getValue("user_name");
			String password = PropertiesUtil.getValue("password");
			return DriverManager.getConnection(url,userName,password);	
		} catch (Exception e) {
			logger.debug(e.getMessage(),e);
		}
		return null;
	}
	
	/**
	 * 数据库修改方法
	 *
	 * @author zzs
	 */
	public static boolean upDate(String sql) {
		con = getConnection();	
		try {
			sta = con.createStatement();
			return sta.executeUpdate(sql)>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(result, sta, con);
		}
		return false;
	}
	/**
	 * 数据库修改方法，防止SQL注入
	 *
	 * @author zzs
	 */
	public static boolean upDate(String sql,Object...array) {
		con = getConnection();
		PreparedStatement preparedStatement =null;
		try {
			preparedStatement = con.prepareStatement(sql);
			for (int i = 1; i <=array.length; i++) {
				preparedStatement.setObject(i, array[i-1]);
			}
			return preparedStatement.executeUpdate()>0;
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			close(result, preparedStatement, con);
		}
		return false;
	}	
	/**
	 * 数据库查询方法
	 *
	 * @author zzs
	 */
	public static void sel(IRowMapper rowMapper,String sql) {
		try {
			con = getConnection();
			sta = con.createStatement();
			result = sta.executeQuery(sql);
			rowMapper.rowMapper(result);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(result, sta, con);
		}
	}
	/**
	 * 数据库查询方法，防止SQL注入
	 *
	 * @author zzs
	 */
	public static void sel(IRowMapper rowMapper,String sql,Object...array) {
		Scanner sc = new Scanner(System.in);
		try {
			con = getConnection();
			PreparedStatement preparedStatement = con.prepareStatement(sql);
			for (int i = 1; i <=array.length; i++) {
				preparedStatement.setObject(i, array[i-1]);
			}
			result = preparedStatement.executeQuery();
			rowMapper.rowMapper(result);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(result, sta, con);
		}
		
	}
	/**
	 * 资源释放方法
	 *
	 * @author zzs
	 */
	private static void close(Statement statement,Connection connection) {
		try {
			if (statement!=null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (connection!=null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 资源释放方法
	 *
	 * @author zzs
	 */
	private static void close(ResultSet result,Statement statement,Connection connection) {
		try {
			if (result!=null) {
				result.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(statement,connection);
	}
}

6. IRowMapper

定义该接口，目的是利用Java多态的特征——使用内部类和接口回调的方式在ResultSet结果集关闭之前获取到结果集的内容，便于代码重用。

public interface IRowMapper {
	void rowMapper(ResultSet rs);
}

7. PublisherManager

此类中封装了insertPublisher()添加出版社信息方法，updatePublisher()删除出版社信息方法，updatePublisher()修改出版社信息方法，selectPublisher()查询出版社信息方法。

package project3;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import java.util.UUID;

import com.jd.util.DButil;
import com.jd.util.IRowMapper;

public class PublisherManage {
	static String sql=null;
	/**
	 * 判断是否在表中存在待查信息
	 *
	 * @author zzs
	 */
	public static boolean p_exist(String name) {
		return getId(name)!=null;
	}
	public static boolean exist(String sql) {
		class RowMapper implements IRowMapper{
			boolean state=false;

			@Override
			public void rowMapper(ResultSet rs) {
				try {
					if(rs.next()) {
						state = true;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper, sql);
		return rowMapper.state;
	}
	/**
	 * 添加出版社信息
	 *
	 * @author zzs
	 */
	public static void insertPublisher() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入出版社名称：");
		String publisherName = sc.nextLine();
		System.out.println("请输入出版社地址");
		String publisherAddress = sc.nextLine();
		if(p_exist(publisherName)) {
			System.out.println("出版社已存在！");
			return;
		}
		String uuid = UUID.randomUUID().toString();
		sql = "insert into publisher (id,name,address) values (?,?,?)";
		if(DButil.upDate(sql,uuid,publisherName,publisherAddress)) {
			System.out.println("添加成功！");
		}
		
	}
	/**
	 * 删除出版社信息
	 *
	 * @author zzs
	 */
	public static void deletePublisher() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入出版社名称：");
		String publisherName = sc.nextLine();
		if(p_exist(publisherName)) {
			
			String id = getId(publisherName);
			sql = "select * from book where id='"+id+"'";
			if(exist(sql)) {
				System.out.println("删除失败！书籍表中有属于该出版社的书籍");
				return;
			}
			sql="delete from publisher where name='"+publisherName+"'";
			if(DButil.upDate(sql)) {
				System.out.println("删除成功");
				return;
			}
			System.out.println("不存在该出版社");
		}
	}
	/**
	 * 修改出版社信息
	 *
	 * @author zzs
	 */
	public static void updatePublisher() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入出版社名称：");
		String publisherName = sc.nextLine();
		if(p_exist(publisherName)) {
			System.out.println("请输入出版社地址：");
			String publisherAddress = sc.nextLine();
			sql="update publisher set address='"+publisherAddress+"' where name='"+publisherName+"'";
			if(DButil.upDate(sql)) {
				System.out.println("修改成功");
				return;
			}
			
		}
		System.out.println("不存在该出版社");
	}
	/**
	 * 查询出版社信息
	 *
	 * @author zzs
	 */
	public static void selectPublisher() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入出版社名称：");
		String publisherName = sc.nextLine();
		String sql = "select * from publisher where name='"+publisherName+"'";
		class RowMapper implements IRowMapper{
			@Override
			public void rowMapper(ResultSet rs) {
				try {
					if(rs.next()) {
						System.out.println(rs.getString("id")+","+rs.getString("name")+","+rs.getString("address"));
					}else {
						System.out.println("不存在该出版社");
						return;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper, sql);
	}
	/**
	 * 根据名字获取id
	 *
	 * @author zzs
	 */
	public static String getId(String name) {
		String sql = "select id from publisher where name=?";
		class RowMapper implements IRowMapper{
			
			String id;
			@Override
			public void rowMapper(ResultSet rs) {
				try {
					if(rs.next()) {
						id = rs.getString("id");
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper,sql,name);
		return rowMapper.id;
	}
}

8. BookManager

此类中封装了insertBook()添加书籍信息方法，updateBook()删除书籍信息方法，updateBook()修改书籍信息方法，selectBook()查询书籍信息方法。

package project3;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import java.util.UUID;

import com.jd.util.DButil;
import com.jd.util.IRowMapper;

public class BookManage {

	/**
	 * 判断表中是否存在
	 *
	 * @author zzs
	 */
	public static boolean exist(String sql) {
		class RowMapper implements IRowMapper{
			boolean state=false;

			@Override
			public void rowMapper(ResultSet rs) {
				try {
					if(rs.next()) {
						state = true;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper, sql);
		return rowMapper.state;
	}
	/**
	 * 添加书籍
	 *
	 * @author zzs
	 */
	public static void insertBook() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入书籍名称：");
		String bookName = sc.nextLine();
		System.out.println("请输入书籍isbn");
		String bookIsbn = sc.nextLine();
		String sql = "select * from book where isbn=?";
		class ISBNRowMapper implements IRowMapper{
			boolean state=false;

			@Override
			public void rowMapper(ResultSet rs) {
				try {
					if(rs.next()) {
						state = true;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		}
		ISBNRowMapper isbnRowMapper = new ISBNRowMapper();
		DButil.sel(isbnRowMapper, sql,bookIsbn);
		if(isbnRowMapper.state) {
			System.out.println("isdn重复，书籍已存在！");
			return;
		}
		String uuid = UUID.randomUUID().toString();
		System.out.println("请选择输入书籍出版社：");
		sql = "select * from publisher";
		class RowMapper implements IRowMapper{
			@Override
			public void rowMapper(ResultSet rs) {
				try {
					while(rs.next()) {
						System.out.println(rs.getString("name"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper, sql);
		String publisherName = sc.nextLine();
		String id = getId(publisherName);
		String uuid1 = UUID.randomUUID().toString();
		sql = "insert into book (id,isbn,name,publisher_id) values ('"+uuid+"','"+bookIsbn+"','"+bookName+"','"+id+"')";
		if(DButil.upDate(sql)) {
			System.out.println("添加成功！");
		}
	}
	/**
	 * 删除书籍
	 *
	 * @author zzs
	 */
	public static void deleteBook() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入书籍名称：");
		String bookName = sc.nextLine();
		String sql = "select * from book where name='"+bookName+"'";
		if(exist(sql)) {
			sql="delete from book where name='"+bookName+"'";
			if(DButil.upDate(sql)) {
				System.out.println("删除成功");
				return;
			}
			System.out.println("不存在该书籍");
		}
	}
	/**
	 * 修改书籍信息
	 *
	 * @author zzs
	 */
	public static void updateBook() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入书籍名称：");
		String bookName = sc.nextLine();
		String sql = "select * from book where name='"+bookName+"'";
		if(exist(sql)) {
			System.out.println("请输入书籍isbn");
			String bookIsbn = sc.nextLine();
			sql = "update book set isbn='"+bookIsbn+"' where name='"+bookName+"'";
			if(DButil.upDate(sql)) {
				System.out.println("修改书籍信息成功");
				return;
			}
		}
		System.out.println("不存在该书籍");
	}
	/**
	 * 查询书籍信息
	 *
	 * @author zzs
	 */
	public static void selectBook() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入书籍姓名：");
		String bookName = sc.nextLine();
		String sql = "select address,p.name,b.name,isbn " + 
				"from book b " + 
				"inner join publisher p on p.id=publisher_id " + 
				"where b.name like '%"+bookName+"%'";
		class RowMapper implements IRowMapper{

			@Override
			public void rowMapper(ResultSet rs) {
				try {
					while(rs.next()) {
						System.out.println(rs.getString("isbn")+","+rs.getString("b.name")+","+rs.getString("p.name")+","+rs.getString("address"));
						
					}
					return;
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println("不存在该书籍");
			}	
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper, sql);
	}
	/**
	 * 根据名字获取id
	 *
	 * @author zzs
	 */
	public static String getId(String name) {
		String sql = "select id from publisher where name=?";
		class RowMapper implements IRowMapper{
			
			String id;
			@Override
			public void rowMapper(ResultSet rs) {
				try {
					if(rs.next()) {
						id = rs.getString("id");
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		RowMapper rowMapper = new RowMapper();
		DButil.sel(rowMapper,sql,name);
		return rowMapper.id;
	}
}

9. Menu

操作菜单

package project3;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

import com.jd.util.DButil;
import com.jd.util.IRowMapper;

public class Menu {

	static Connection con =null;
	static Statement sta =null;
	static String sql =null;
	static ResultSet result = null;
	public static void main(String[] args) {
		welcome();
		menus();
		Scanner sc = new Scanner(System.in);	
		while (true) {
			switch (sc.nextInt()) {
			case 1:
				System.out.println("1、出版社管理");
				pManager();
				boolean flag1 = true;
				while (flag1) {
					switch (sc.nextInt()) {
					case 1:
						System.out.println("(1)、增加出版社信息");
						PublisherManage.insertPublisher();
						break;
					case 2:
						System.out.println("(2)、删除出版社信息");
						PublisherManage.deletePublisher();
						break;
					case 3:
						System.out.println("(3)、修改出版社信息");
						PublisherManage.updatePublisher();
						break;
					case 4:
						System.out.println("(4)、查询出版社信息");
						PublisherManage.selectPublisher();
						break;
					case 5:
						System.out.println("(5)、返回上级操作");
						flag1 = false;
						break;
					default:
						System.out.println("输入不合法");
					}
				}
				break;
			case 2:
				System.out.println("2、书籍管理");
				bManager();
				boolean flag = true;
				while (flag) {
					switch (sc.nextInt()) {
					case 1:
						System.out.println("(1)、增加书籍信息");
						BookManage.insertBook();
						break;
					case 2:
						System.out.println("(2)、删除书籍信息");
						BookManage.deleteBook();
						break;
					case 3:
						System.out.println("(3)、修改书籍信息");
						BookManage.updateBook();
						break;
					case 4:
						System.out.println("(4)、查询书籍信息");
						BookManage.selectBook();
						break;
					case 5:
						System.out.println("(5)、返回上级操作");
						flag = false;
						break;
					default:
						System.out.println("输入不合法");
					}
				}
				break;
			case 3:
				System.out.println("3、退出");
				System.exit(0);
			}
		}
	}
	public static void welcome() {
		System.out.println("*********************************");
		System.out.println("*\t\t\t\t*");
		System.out.println("*\t欢迎使用书籍信息管理系统\t*");
		System.out.println("*\t\t\t\t*");
		System.out.println("*********************************");
	}
	public static void menus() {
		System.out.println("1、出版社管理");
		System.out.println("2、书籍管理");
		System.out.println("3、退出");
	}
	public static void pManager() {
		System.out.println("(1)、增加出版社信息");
		System.out.println("(2)、删除出版社信息");
		System.out.println("(3)、修改出版社信息");
		System.out.println("(4)、查询出版社信息");
		System.out.println("(5)、返回上级操作");

	}
	public static void bManager() {
		System.out.println("(1)、增加书籍信息");
		System.out.println("(2)、删除书籍信息");
		System.out.println("(3)、修改书籍信息");
		System.out.println("(4)、查询书籍信息");
		System.out.println("(5)、返回上级操作");

	}
}