目录
2.2 连接数据库的配置文件db.properties如下：
2.3.2 BookDaoImpl实现类：（利用反射实现数据库字段与Java对象的映射并且使用通用化增删改方法）
2.3.2.1 selectList(String sql, Class class1, Object... params)方法：
2.3.2.2 executeUpdate(String sql, Object... params)方法：
2.3.3 BookDaoImpl实现类：（没有利用反射实现映射以及通用增删改）
功能：实现对Book表的增删改查。不操作书籍类别表以及书籍表所以没有进行对应实体类的创建。
三层架构：将程序划分为表示层 、 业务逻辑层、数据访问层三层，各层之间采用接口相互访问，并通过实体类对象作为数据传递的载体。
使用三层架构模式：
- 表示【界面】层（User Interface Layer）。
- 业务逻辑【服务】层（Business Logic Layer）。
- 数据访问【持久】层（Data Access Layer）。
目录结构：
1. 数据库以及表的创建：
use qf_book_db;
#用户表
CREATE TABLE user(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
realname VARCHAR(20),
email VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
flag INT(11),
role INT(11)
);
#书籍类别表
CREATE TABLE category(
cid INT PRIMARY KEY,
cname VARCHAR(30) NOT NULL
);
#书籍表
CREATE TABLE book(
id INT PRIMARY KEY AUTO_INCREMENT ,
title VARCHAR(50) NOT NULL,
author VARCHAR(20) NOT NULL,
public_date DATETIME,
publisher VARCHAR(50),
isbn VARCHAR(15) NOT NULL,
price DECIMAL(8,2) NOT NULL,
picture varchar(50),
cid INT,
CONSTRAINT FOREIGN KEY(cid) REFERENCES category(cid)
);
#user添加数据
INSERT INTO user (username,password,realname,email,gender,flag,role) VALUES('admin','888','李明','liming@qq.com','男',1,0);
INSERT INTO user (username,PASSWORD,realname,email,gender,flag,role) VALUES('jiangjiang','123456','犟犟','jiangjiang@163.com','男',1,1);
INSERT INTO user (username,PASSWORD,realname,email,gender,flag,role) VALUES('yitao','123456','艺涛','shuliang@163.com','女',1,1);
#categroy添加数据
INSERT INTO category (cid,cname) VALUES(10,'科技'),(11,'教育'),(12,'小说'),(13,'文艺'),(14,'经管'),(15,'成功'),(16,'生活');
#添加书籍
INSERT INTO book(title,author,public_date,publisher,isbn,price,picture,cid)
VALUES('Java核心技术 卷I 基础知识','霍斯特曼','2019-12-1','机械工业出版社','9787111636663',102.80,null,10)
,('高性能MySQL（第3版）','特卡琴科','2013-05-10','电子工业出版社','9787121198854',122.90,null,10)
,('Java从入门到精通（第5版）','明日科技','2019-03-1','清华大学出版社','9787302517597',61.40,null,10)
,('Java编程思想（第4版）','Bruce Eckel','2007-06-1','机械工业出版社','9787111213826',100.30,null,10)
,('深入理解Java虚拟机','周志明','2013-06-1','机械工业出版社','9787111421900',62.40,null,10)
,('高等数学（第七版）（上册）','同济大学数学系','2014-07-1','高等教育出版社','9787040396638',40.20,null,11)
,('管理学(第13版)','斯蒂芬·P·罗宾斯','2017-01-1','中国人民大学出版社','9787300234601',66.50,null,14)
,('红楼梦原著版（上、下册）','曹雪芹','2013-01-1','人民文学出版社','9787020002207',38.9,null,12)
,('水浒传（上下册）（全两册）','施耐庵 ,罗贯中','2004-09-1','人民文学出版社','9787020008742',32.9,null,12)
,('西游记（共两册）','吴承恩 ','2007-05-1','人民文学出版社','9787020051564',48.00,null,12);
2. Java代码编写：
2.1 实体类：Book类放在entity包下
package gp16.day26.entity;
import java.math.BigDecimal;
import java.util.Date;
public class Book {
private Integer id;
private String title;
private String author;
private Date publicDate;
private String publisher;
private String isbn;
private BigDecimal price;
private String picture;
private Integer cid;
public Book() {
}
public Book(Integer id, String title, String author, Date publicDate, String publisher, String isbn, BigDecimal price, String picture, Integer cid) {
this.id = id;
this.title = title;
this.author = author;
this.publicDate = publicDate;
this.publisher = publisher;
this.isbn = isbn;
this.price = price;
this.picture = picture;
this.cid = cid;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getPublicDate() {
return publicDate;
}
public void setPublicDate(Date publicDate) {
this.publicDate = publicDate;
}
public String getPublisher() {
return publisher;
}
public void setPublisher(String publisher) {
this.publisher = publisher;
}
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public String getPicture() {
return picture;
}
public void setPicture(String picture) {
this.picture = picture;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", title='" + title + '\'' +
", author='" + author + '\'' +
", publicDate=" + publicDate +
", publisher='" + publisher + '\'' +
", isbn='" + isbn + '\'' +
", price=" + price +
", picture='" + picture + '\'' +
", cid=" + cid +
'}';
}
}
2.2 连接数据库的配置文件db.properties如下：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/qf_book_db?useSSL=false&characterEncoding=utf-8
username=root
password=sxh3293292.3 数据访问【持久】层放在dao包下：
2.3.1 BookDao接口：
package gp16.day26.service;
import gp16.day26.entity.Book;
import java.util.List;
public interface BookService {
/**查询
*
* @return
*/
List<Book> queryAll();
/**根据id查询
*
* @param id
* @return
*/
Book queryById(int id);
/**添加
*
* @param book
*/
void add(Book book);
/**修改
*
* @param book
*/
void modify(Book book);
/**删除
*
* @param id
*/
void remove(int id);
}
2.3.2 BookDaoImpl实现类：（利用反射实现数据库字段与Java对象的映射并且使用通用化增删改方法）
注：反射实现数据库字段方法selectList(String sql, Class<T> class1, Object... params)以及Java对象的映射并且使用通用化增删改方法executeUpdate(String sql, Object... params)都放在util包下的Dbutils类中。
2.3.2.1 selectList(String sql, Class<T> class1, Object... params)方法：
//sql: select * from book where id=? -----> Book对象
// select * from student ----> Student对象
// select * from user -----> User对象
//数据库字段与Java对象映射的实现
public static <T> List<T> selectList(String sql, Class<T> class1, Object... params){
Connection conn=null;
PreparedStatement pstat=null;
ResultSet rs=null;
try {
conn=getConnection();
pstat=conn.prepareStatement(sql);
//执行
//参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstat.setObject(i+1,params[i]);
}
}
rs=pstat.executeQuery();
//获取结果集的标题
ResultSetMetaData metaData = rs.getMetaData(); //结果集标题
List<T> list=new ArrayList<>();
//处理
while(rs.next()){
//创建对象
T t = class1.newInstance(); //Book book=new Book();
//属性赋值
for (int i = 0; i < metaData.getColumnCount(); i++) {
//获取结果集标题 getColumnLabel获取别名
String columnLabel = metaData.getColumnLabel(i + 1); //id ,title ,author ...
//System.out.println(columnLabel);
try {
//创建属性描述对象
PropertyDescriptor pd=new PropertyDescriptor(columnLabel,class1); // public_date
//获取set方法
Method writeMethod = pd.getWriteMethod();
//调用方法
writeMethod.invoke(t,rs.getObject(columnLabel));
} catch (Exception e) {
continue;//继续执行下一次循环
}
}
list.add(t);
}
return list;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
closeAll(conn,pstat,rs);
}
}
2.3.2.2 executeUpdate(String sql, Object... params)方法：
/**
* 添加通用增删改方法
*
* @param sql
* @param params
* @return "Object... params" 表示可以传入多个 Object 类型的参数。你可以像传递普通参数一样，将多个参数以逗号分隔传递给这个方法。
* 在方法内部，可以通过 params 参数来获取传入的参数值。你可以根据具体的业务逻辑，使用这些参数来构建 SQL 语句或者设置 PreparedStatement 对象的参数值。
* 例如，如果你调用这个方法时传入了两个参数，可以通过 params[0] 和 params[1] 来获取这两个参数的值。
*/
public static int executeUpdate(String sql, Object... params) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
if (pstmt != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeAll(connection, pstmt, null);
}
}
2.3.2.3 BookDaoImpl实现类：
package gp16.day26.dao.impl;
import gp16.day26.dao.BookDao;
import gp16.day26.entity.Book;
import gp16.day26.util.DbUtils;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BookDaoImpl implements BookDao {
/**
* 查询
*
* @return
*/
public List<Book> selectAll() {
String sql = "select id,title,author,public_date as publicDate,publisher,isbn,price,picture,cid from book ";
return DbUtils.selectList(sql, Book.class);
}
/**
* 根据id查询
*
* @return
*/
public Book selectById(int id) {
String sql = "select id,title,author,public_date as publicDate,publisher,isbn,price,picture,cid from book where id = ?";
List<Book> books = DbUtils.selectList(sql, Book.class, id);
return books.get(0);
}
/**
* 插入
*
* @param book
*/
public void insert(Book book) {
String sql = "insert into book value(?,?,?,?,?,?,?,?,?)";
Object[] params = {book.getId(), book.getTitle(), book.getAuthor(), book.getPublicDate(), book.getPublisher(), book.getIsbn(), book.getPrice(), book.getPicture(), book.getCid()};
DbUtils.executeUpdate(sql, params);
}
/**
* 更新
*
* @param book
*/
public void update(Book book) {
String sql = "update book set title = ?,author = ?,public_date = ?,publisher = ?,isbn = ?,price = ?,picture = ?,cid = ? where id = ?";
Object[] params = {book.getTitle(), book.getAuthor(), book.getPublicDate(), book.getPublisher(), book.getIsbn(), book.getPrice(), book.getPicture(), book.getCid(), book.getId()};
DbUtils.executeUpdate(sql, params);
}
/**
* 删除
*
* @param id
*/
public void remove(int id) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = DbUtils.getConnection();
pstmt = connection.prepareStatement("delete from book where id = ? ");
pstmt.setObject(1, id);
int count = pstmt.executeUpdate();
if (count == 0) {
throw new RuntimeException("图书不存在");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DbUtils.closeAll(connection, pstmt, null);
}
}
}
2.3.3 BookDaoImpl实现类：（没有利用反射实现映射以及通用增删改）
package gp16.day26.dao.impl;
import gp16.day26.dao.BookDao;
import gp16.day26.entity.Book;
import gp16.day26.util.DbUtils;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BookDaoImpl implements BookDao {
/**
* 查询
*
* @return
*/
public List<Book> selectAll() {
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Book> list = new ArrayList<>();
try {
connection = DbUtils.getConnection();
pstmt = connection.prepareStatement("select * from book");
rs = pstmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String title = rs.getString("title");
String author = rs.getString("author");
Date publicDate = rs.getDate("public_date");
String publisher = rs.getString("publisher");
String isbn = rs.getString("isbn");
BigDecimal price = rs.getBigDecimal("price");
String picture = rs.getString("picture");
int cid = rs.getInt("cid");
Book book = new Book(id, title, author, publicDate, publisher, isbn, price, picture, cid);
list.add(book);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DbUtils.closeAll(connection, pstmt, rs);
}
}
/**
* 根据id查询
*
* @return
*/
public Book selectById(int id) {
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Book book = null;
try {
connection = DbUtils.getConnection();
pstmt = connection.prepareStatement("select * from book where id = ?");
pstmt.setObject(1, id);
rs = pstmt.executeQuery();
while (rs.next()) {
int id2 = rs.getInt("id");
String title = rs.getString("title");
String author = rs.getString("author");
Date publicDate = rs.getDate("public_date");
String publisher = rs.getString("publisher");
String isbn = rs.getString("isbn");
BigDecimal price = rs.getBigDecimal("price");
String picture = rs.getString("picture");
int cid = rs.getInt("cid");
book = new Book(id2, title, author, publicDate, publisher, isbn, price, picture, cid);
}
return book;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DbUtils.closeAll(connection, pstmt, rs);
}
}
/**
* 插入
*
* @param book
*/
public void insert(Book book) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = DbUtils.getConnection();
pstmt = connection.prepareStatement("insert into book value(?,?,?,?,?,?,?,?,?)");
pstmt.setObject(1, book.getId());
pstmt.setObject(2, book.getTitle());
pstmt.setObject(3, book.getAuthor());
pstmt.setObject(4, book.getPublicDate());
pstmt.setObject(5, book.getPublisher());
pstmt.setObject(6, book.getIsbn());
pstmt.setObject(7, book.getPrice());
pstmt.setObject(8, book.getPicture());
pstmt.setObject(9, book.getCid());
pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DbUtils.closeAll(connection, pstmt, null);
}
}
/**
* 更新
*
* @param book
*/
public void update(Book book) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = DbUtils.getConnection();
pstmt = connection.prepareStatement("update book title = ?,author = ?,public_date = ?,publisher = ?,isbn = ?,price = ?,picture = ?,cid = ? where set id = ?");
pstmt.setObject(1, book.getId());
pstmt.setObject(2, book.getTitle());
pstmt.setObject(3, book.getAuthor());
pstmt.setObject(4, book.getPublicDate());
pstmt.setObject(5, book.getPublisher());
pstmt.setObject(6, book.getIsbn());
pstmt.setObject(7, book.getPrice());
pstmt.setObject(8, book.getPicture());
pstmt.setObject(9, book.getCid());
pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DbUtils.closeAll(connection, pstmt, null);
}
}
/**
* 删除
*
* @param id
*/
public void remove(int id) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = DbUtils.getConnection();
pstmt = connection.prepareStatement("delete from book where id = ? ");
pstmt.setObject(1, id);
int count = pstmt.executeUpdate();
if (count == 0) {
throw new RuntimeException("图书不存在");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DbUtils.closeAll(connection, pstmt, null);
}
}
}
2.4 业务逻辑【服务】层放在service包下：
2.4.1 ServiceDao接口：
package gp16.day26.service;
import gp16.day26.entity.Book;
import java.util.List;
public interface BookService {
/**查询
*
* @return
*/
List<Book> queryAll();
/**根据id查询
*
* @param id
* @return
*/
Book queryById(int id);
/**添加
*
* @param book
*/
void add(Book book);
/**修改
*
* @param book
*/
void modify(Book book);
/**删除
*
* @param id
*/
void remove(int id);
}
2.4.2 ServiceDaoImpl实现类：
package gp16.day26.service.impl;
import gp16.day26.dao.BookDao;
import gp16.day26.dao.impl.BookDaoImpl;
import gp16.day26.entity.Book;
import gp16.day26.service.BookService;
import java.util.List;
public class BookServiceImpl implements BookService {
private BookDao bookDao = new BookDaoImpl();
/**
* 查询
*
* @return
*/
public List<Book> queryAll() {
return bookDao.selectAll();
}
/**
* 根据id查询
*
* @param id
* @return
*/
public Book queryById(int id) {
return bookDao.selectById(id);
}
/**
* 添加
*
* @param book
*/
public void add(Book book) {
bookDao.insert(book);
}
/**
* 修改
*
* @param book
*/
public void modify(Book book) {
bookDao.update(book);
}
/**
* 删除
*
* @param id
*/
public void remove(int id) {
bookDao.remove(id);
}
}
2.5 配置类放在util包下：
2.5.1未使用Druid数据库连接池：
package gp16.day26.util;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class DbUtils {
private static String driver;
private static String url;
private static String name;
private static String password;
public static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
/**
* 注册驱动
*/
static {
try {
Properties properties = new Properties();
FileInputStream fileInputStream = new FileInputStream("D:\\JDBC\\jdbc\\src\\gp16\\day26\\db.properties");
properties.load(fileInputStream);
fileInputStream.close();
driver = properties.getProperty("driver");
url = properties.getProperty("url");
name = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
System.out.println("注册失败：" + e.getMessage());
}
}
/**
* 获取连接
*
* @return
*/
public static Connection getConnection() {
Connection connection = threadLocal.get();
try {
if (connection == null) {
connection = DriverManager.getConnection(url, name, password);
threadLocal.set(connection);//绑定到线程
}
return connection;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 释放资源
*/
public static void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
try {
if (connection != null) {
connection.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (resultSet != null) {
if (connection.getAutoCommit()) {//true没有开启事务，false开启事务
connection.close();
threadLocal.remove();//解除绑定
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//与事务有关的四个方法
/**
* 开启事务
*
* @throws SQLException
*/
public static void begin() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.setAutoCommit(false);
}
}
/**
* 提交事务
*
* @throws SQLException
*/
public static void commit() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.commit();
}
}
/**
* 回滚事务
*
* @throws SQLException
*/
public static void rollback() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.rollback();
}
}
/**
* 释放开启事务之后的连接资源
*
* @throws SQLException
*/
public static void close() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.close();
threadLocal.remove();
}
}
/**
* 添加通用增删改方法
*
* @param sql
* @param params
* @return "Object... params" 表示可以传入多个 Object 类型的参数。你可以像传递普通参数一样，将多个参数以逗号分隔传递给这个方法。
* 在方法内部，可以通过 params 参数来获取传入的参数值。你可以根据具体的业务逻辑，使用这些参数来构建 SQL 语句或者设置 PreparedStatement 对象的参数值。
* 例如，如果你调用这个方法时传入了两个参数，可以通过 params[0] 和 params[1] 来获取这两个参数的值。
*/
public static int executeUpdate(String sql, Object... params) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
if (pstmt != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeAll(connection, pstmt, null);
}
}
//sql: select * from book where id=? -----> Book对象
// select * from student ----> Student对象
// select * from user -----> User对象
//数据库字段与Java对象映射的实现
public static <T> List<T> selectList(String sql, Class<T> class1, Object... params){
Connection conn=null;
PreparedStatement pstat=null;
ResultSet rs=null;
try {
conn=getConnection();
pstat=conn.prepareStatement(sql);
//执行
//参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstat.setObject(i+1,params[i]);
}
}
rs=pstat.executeQuery();
//获取结果集的标题
ResultSetMetaData metaData = rs.getMetaData(); //结果集标题
List<T> list=new ArrayList<>();
//处理
while(rs.next()){
//创建对象
T t = class1.newInstance(); //Book book=new Book();
//属性赋值
for (int i = 0; i < metaData.getColumnCount(); i++) {
//获取结果集标题 getColumnLabel获取别名
String columnLabel = metaData.getColumnLabel(i + 1); //id ,title ,author ...
//System.out.println(columnLabel);
try {
//创建属性描述对象
PropertyDescriptor pd=new PropertyDescriptor(columnLabel,class1); // public_date
//获取set方法
Method writeMethod = pd.getWriteMethod();
//调用方法
writeMethod.invoke(t,rs.getObject(columnLabel));
} catch (Exception e) {
continue;//继续执行下一次循环
}
}
list.add(t);
}
return list;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
closeAll(conn,pstat,rs);
}
}
}
2.5.2 使用Druid数据库连接池：
数据库连接池配置文件druid.properties如下：
druid.driverClassName=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/qf_book_db?useSSL=false&characterEncoding=utf-8
druid.username=root
druid.password=sxh329329
initialSize=10
maxActive=50
minIdle=5
maxWait=3000package gp16.day26.util;
import com.alibaba.druid.pool.DruidDataSource;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class DbUtils_druid {
private static DruidDataSource dataSource;
public static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
/**
* 注册驱动
*/
static {
try {
Properties properties = new Properties();
FileInputStream fileInputStream = new FileInputStream("D:\\JDBC\\jdbc\\src\\gp16\\day26\\druid.properties");
properties.load(fileInputStream);
fileInputStream.close();
dataSource = new DruidDataSource();
dataSource.configFromPropety(properties);
} catch (Exception e) {
System.out.println("注册失败：" + e.getMessage());
}
}
/**
* 获取连接
*
* @return
*/
public static Connection getConnection() {
Connection connection = threadLocal.get();
try {
if (connection == null) {
connection = dataSource.getConnection();
threadLocal.set(connection);//绑定到线程
}
return connection;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 释放资源
*/
public static void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
try {
if (connection != null) {
connection.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (resultSet != null) {
if (connection.getAutoCommit()) {//true没有开启事务，false开启事务
connection.close();
threadLocal.remove();//解除绑定
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//与事务有关的四个方法
/**
* 开启事务
*
* @throws SQLException
*/
public static void begin() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.setAutoCommit(false);
}
}
/**
* 提交事务
*
* @throws SQLException
*/
public static void commit() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.commit();
}
}
/**
* 回滚事务
*
* @throws SQLException
*/
public static void rollback() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.rollback();
}
}
/**
* 释放开启事务之后的连接资源
*
* @throws SQLException
*/
public static void close() throws SQLException {
Connection conn = getConnection();
if (conn != null) {
conn.close();
threadLocal.remove();
}
}
/**
* 添加通用增删改方法
*
* @param sql
* @param params
* @return "Object... params" 表示可以传入多个 Object 类型的参数。你可以像传递普通参数一样，将多个参数以逗号分隔传递给这个方法。
* 在方法内部，可以通过 params 参数来获取传入的参数值。你可以根据具体的业务逻辑，使用这些参数来构建 SQL 语句或者设置 PreparedStatement 对象的参数值。
* 例如，如果你调用这个方法时传入了两个参数，可以通过 params[0] 和 params[1] 来获取这两个参数的值。
*/
public static int executeUpdate(String sql, Object... params) {
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
if (pstmt != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeAll(connection, pstmt, null);
}
}
public static <T> List<T> selectList(String sql, Class<T> tClass, Object... params) {
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
rs = pstmt.executeQuery();
//获取结果集的标题
ResultSetMetaData metaData = rs.getMetaData();
List<T> list = new ArrayList<>();
//处理
while (rs.next()) {
//创建对象
T t = tClass.newInstance(); //Book book = new Book();
//属性赋值
for (int i = 0; i < metaData.getColumnCount(); i++) {
//获取结果集标题 getColumnLabel获取字段名（同时也可以获取别名）
String columnLabel = metaData.getColumnLabel(i + 1);
try {
//创建属性描述对象
PropertyDescriptor pd = new PropertyDescriptor(columnLabel, tClass); //public_date
//获取set方法
Method writeMethod = pd.getWriteMethod();
//调用方法
writeMethod.invoke(t, rs.getObject(columnLabel));
} catch (Exception e) {
continue;//继续执行下一次循环
}
}
list.add(t);
}
return list;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
closeAll(connection, pstmt, rs);
}
}
}
2.6 表示【界面】层放在view包下：
package gp16.day26.view;
import gp16.day26.entity.Book;
import gp16.day26.service.BookService;
import gp16.day26.service.impl.BookServiceImpl;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class BookSystem {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
boolean flag = true;
//创建业务对象
BookService bookService = new BookServiceImpl();
do {
System.out.println("------------1.查询所有 2.根据编号查询 3.添加 4.修改 5.删除 0. 退出----------------");
System.out.println("请选择：");
int n = sc.nextInt();
switch (n) {
case 1:
try {
List<Book> list = bookService.queryAll();
if (list != null) {
for (Book book : list) {
System.out.println(book.toString());
}
}
} catch (Exception e) {
System.out.println("查询失败：" + e.getMessage());
}
break;
case 2:
try {
System.out.println("请输入图书编号：");
int id = sc.nextInt();
Book book = bookService.queryById(id);
if (book != null) {
System.out.println(book.toString());
} else {
System.out.println("图书不存在");
}
} catch (Exception e) {
System.out.println("查询失败：" + e.getMessage());
}
break;
case 3:
try {
Book book = new Book(11, "Java语言基础", "李逵", new Date(), "千锋出版社", "12314123", new BigDecimal(9.9), " ", 10);
bookService.add(book);
System.out.println("添加成功");
} catch (Exception e) {
System.out.println("添加失败：" + e.getMessage());
}
break;
case 4:
try {
Book book = new Book(11, "Java语言基础Plus", "李大逵", new Date(), "千锋出版社", "12314123", new BigDecimal(19.9), " ", 10);
bookService.modify(book);
System.out.println("修改成功");
} catch (Exception e) {
System.out.println("修改失败：" + e.getMessage());
}
break;
case 5:
try {
System.out.println("请输入删除的图书编号：");
int i = sc.nextInt();
bookService.remove(i);
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
}
break;
case 0:
flag = false;
break;
default:
System.out.println("输入有误，请重新输入：");
break;
}
} while (flag);
}
}
扫一扫
2463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
