数据库原理：作业 06
实验六：JDBC进阶（2）
一、相关知识点
- JDBC基本概念
- PreparedStatement的用法
- JDBC数据增、删、改，事务控制等
二、实验目的：
理解Java连接数据库的基本概念。理解利用Statement对象、PreparedStatement对象进行增、删、改操作，理解事务的概念和JDBC编程方式。
三、实验内容：
1、利用PreparedStatement进行查询。
- 在
PublisherManager类中增加方法public List<BeanPublisher> searchPublisher(String keyword) throws BaseException方法，要求根据关键字在出版社表中查询满足条件的出版社（出版社名称或地址中包含参数中的关键字），参考loadAllPublisher()方法，将查询结果封装为List<BeanPublisher> - 在main函数中编写测试代码进行该方法的调用测试。
目前beanpublisher内数据为：
public List<BeanPublisher> searchPublisher(String keyword) throws BaseException{
List<BeanPublisher> result = new ArrayList<BeanPublisher>();
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from beanpublisher where address like ? or publisherName like ?;";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, "%" + keyword + "%");
pst.setString(2, "%" + keyword + "%");
java.sql.ResultSet rs = pst.executeQuery();
while (rs.next()) {
BeanPublisher p = new BeanPublisher();
p.setPubid(rs.getString(1));
p.setPublisherName(rs.getString(2));
p.setAddress(rs.getString(3));
result.add(p);
}
} catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
if (result.size() == 0) {
System.out.println("没有结果！");
return null;
} else {
return result;
}
}
在BeanPublisher.java重写toString():
@Override
public String toString() {
return "BeanPublisher{" +
"pubid='" + pubid + '\'' +
", publisherName='" + publisherName + '\'' +
", address='" + address + '\'' +
'}';
}
2、利用Statement对象进行数据添加。
- 修改
PublisherManager类的createPublisher方法，将其中的insert语言改成用Statement对象执行； - 运行图书管理系统，进行添加出版社测试。
写出替换的代码部分
public void createPublisher(BeanPublisher p) throws BaseException {
if (p.getPubid() == null || "".equals(p.getPubid()) || p.getPubid().length() > 20) {
throw new BusinessException("出版社编号必须是1-20个字");
}
if (p.getPublisherName() == null || "".equals(p.getPublisherName()) || p.getPublisherName().length() > 50) {
throw new BusinessException("出版社名称必须是1-50个字");
}
if (p.getAddress() == null || "".equals(p.getAddress()) || p.getAddress().length() > 100) {
throw new BusinessException("出版地址必须是1-100个字");
}
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from BeanPublisher where pubid=?";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, p.getPubid());
java.sql.ResultSet rs = pst.executeQuery();
if (rs.next()) {
throw new BusinessException("出版社编号已经被占用");
}
rs.close();
pst.close();
sql = "select * from BeanPublisher where publisherName=?";
pst = conn.prepareStatement(sql);
pst.setString(1, p.getPublisherName());
rs = pst.executeQuery();
if (rs.next()) {
throw new BusinessException("出版社名称已经存在");
}
rs.close();
pst.close();
Statement statement = conn.createStatement();
sql = "insert into BeanPublisher(pubid,publisherName,address) values('"+p.getPubid()+"', '"+p.getPublisherName()+"', '"+p.getAddress()+"');";
int result = statement.executeUpdate(sql);
if (result != 0) {
System.out.println("插入成功！");
}else {
System.out.println("插入失败！");
}
statement.close();
} catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3、利用insert语句添加数据时，未指定字段值处理。
-
将数据库表beanreadertype的readerTypeId的自动递增属性去掉。
-
SourceURL:file:///Users/owem/Desktop/Courses/数据库原理/实验/实验06/实验3.doc
-
-
运行图书管理系统，打开读者类别管理界面，并尝试添加一个读者类别；系统将会报一个错误，请分析说明错误原因。
-
应该如何修改程序，使新增读者类别的ID为表中现有数据的最大ID值+1。
去除自动增加属性
package cn.edu.zucc.booklib.Test;
import cn.edu.zucc.booklib.util.DBUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @description:
* @author: Owemshu
* @create: 2022-05-13 14:50
*/
public class RemoveAutoIncrementAttribute {
public static void main(String[] args) {
Connection conn = null;
try {
conn = DBUtil.getConnection();
Statement statement = conn.createStatement();
String sql = "alter table beanreadertype modify readerTypeId int not null;";
int result = statement.executeUpdate(sql);
if (result != 0) {
System.out.println("修改成功！");
}else {
System.out.println("修改失败！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
4、利用PreparedStatement对象进行数据修改。
在SystemUserManager类中，新建一个modifyUserName方法，实现用户名称（username字段）的修改功能。并修改其main函数，将admin用户的名称改为：超级管理员。
请提供方法代码和main函数代码
public void modifyUserName(String userid, String newUsername) throws BaseException{
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from beansystemuser where userid = ?;";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, userid);
java.sql.ResultSet rs = pst.executeQuery();
if (!rs.next()) {
throw new BusinessException("登陆账号不 存在");
}
rs.close();
pst.close();
sql = "update beansystemuser set username = ? where userid = ?;";
pst = conn.prepareStatement(sql);
pst.setString(1, newUsername);
pst.setString(2, userid);
pst.execute();
pst.close();
}catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
思考：如果上述方法的返回值为布尔类型，即如果成功修改了用户名称，则返回true，如果用户不存在或修改失败返回false。应该如何完善代码。提示：主要statement或PreparedStatement对象的execute方法和executeUpdate方法的区别。
修改如下：
try{
......
int result = pst.executeUpdate();
pst.close();
return result >= 1;
}
5、Delete语句的执行。修改用户管理类中的用户删除方法，用删除数据库表中数据的形式代替现有软删除模式。
修改后的sql语句部分是
try{
......
sql = "delete from beansystemuser where userid=?";
......
}
如果对删除函数进行限制，要求不能删除已经有过借阅操作的用户。应如何修改代码。提示：可参考读者管理类中的读者类别删除方法。
public void deleteUser(String userid) throws BaseException {
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select removeDate from BeanSystemUser where userid=?";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, userid);
java.sql.ResultSet rs = pst.executeQuery();
if (!rs.next()) throw new BusinessException("登陆账号不 存在");
if (rs.getDate(1) != null) throw new BusinessException("该账号已经被删除");
String deleteName = rs.getString(1);
System.out.println(deleteName);
rs.close();
pst.close();
sql = "select count(*) from beanbooklendrecord where lendOperUserid=?";
pst = conn.prepareStatement(sql);
pst.setString(1, userid);
rs = pst.executeQuery();
rs.next();
int n = rs.getInt(1);
if (n > 0) {
throw new BusinessException(deleteName + "已经执行过" + n + "个借阅操作，不能删除");
}
sql = "delete from beansystemuser where userid=?";
pst = conn.prepareStatement(sql);
pst.setString(1, userid);
pst.execute();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
6、在数据库中建立一张BeanBookLendRecord_backup表，用于保存已经归还图书的借阅记录。其表结构与BeanBookLendRecord表完全一致。要求在借阅管理类中，增加方法，实现已经归还数据的备份功能（备份完成后，在原表中删除备份成功的数据）。提示：注意事务控制。
请提供备份表的建表语句
CREATE TABLE `beanbooklendrecord_backup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`readerid` varchar(20) NOT NULL,
`bookBarcode` varchar(20) NOT NULL,
`lendDate` datetime NOT NULL,
`returnDate` datetime DEFAULT NULL,
`lendOperUserid` varchar(20) NOT NULL,
`returnOperUserid` varchar(20) DEFAULT NULL,
`penalSum` double DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_book_idx_backup` (`bookBarcode`),
KEY `fk_reader_idx_backup` (`readerid`),
KEY `fk_lendOper_idx_backup` (`lendOperUserid`),
KEY `fk_returnOper_idx_backup` (`returnOperUserid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
请提供备份函数代码
public void backupReturnedRecord()throws DbException{
List<BeanBookLendRecord> list = new ArrayList<BeanBookLendRecord>();
Connection conn = null;
SimpleDateFormat dateFor = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
try {
conn = DBUtil.getConnection();
String sql = "select * from beanbooklendrecord;";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
java.sql.ResultSet rs = pst.executeQuery();
while(rs.next()) {
BeanBookLendRecord bm = new BeanBookLendRecord();
bm.setId(rs.getInt(1));
bm.setReaderid(rs.getString(2));
bm.setBookBarcode(rs.getString(3));
bm.setLendDate(rs.getTimestamp(4));
bm.setReturnDate(rs.getTimestamp(5));
bm.setLendOperUserid(rs.getString(6));
bm.setReturnOperUserid(rs.getString(7));
bm.setPenalSum(rs.getDouble(8));
list.add(bm);
}
for (BeanBookLendRecord bm : list) {
if (bm.getReturnDate() != null) {
sql = "insert into beanbooklendrecord_backup(id,readerid,bookbarcode,lenddate,returndate,lendoperuserid,returnoperuserid,penalsum) values(?,?,?,?,?,?,?,?)";
pst = conn.prepareStatement(sql);
pst.setInt(1, bm.getId());
pst.setString(2, bm.getReaderid());
pst.setString(3, bm.getBookBarcode());
pst.setTimestamp(4, new java.sql.Timestamp(bm.getLendDate().getTime()));
pst.setTimestamp(5, new java.sql.Timestamp(bm.getReturnDate().getTime()));
pst.setString(6, bm.getLendOperUserid());
pst.setString(7, bm.getReturnOperUserid());
pst.setDouble(8, bm.getPenalSum());
pst.execute();
sql = "delete from beanbooklendrecord where id=?";
pst = conn.prepareStatement(sql);
pst.setInt(1, bm.getId());
pst.execute();
pst.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
修改前：
修改后：
7、如果需要记录图书的入库时间（需要包含时分秒），应如何修改数据库表结构和相关代码？
数据库设置：
alter table beanbook add storagetime datetime default null;
代码设置：
public void createBook(BeanBook b) throws BaseException {
if (b.getBarcode() == null || "".equals(b.getBarcode()) || b.getBarcode().length() > 20) {
throw new BusinessException("条码必须是1-20个字");
}
if (b.getBookname() == null || "".equals(b.getBookname()) || b.getBookname().length() > 50) {
throw new BusinessException("图书名称必须是1-50个字");
}
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from BeanBook where barcode=?";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, b.getBarcode());
java.sql.ResultSet rs = pst.executeQuery();
if (rs.next()) throw new BusinessException("条码已经被占用");
rs.close();
pst.close();
//修改处
sql = "insert into BeanBook(barcode,bookname,pubid,price,state,storagetime) values(?,?,?,?,'在库',?)";
pst = conn.prepareStatement(sql);
pst.setString(1, b.getBarcode());
pst.setString(2, b.getBookname());
pst.setString(3, b.getPubid());
pst.setDouble(4, b.getPrice());
//修改处
pst.setTimestamp(5, new java.sql.Timestamp(System.currentTimeMillis()));
pst.execute();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public class BeanBook {
...
private Date storageTime;
public void setStorageTime(Date date) {this.storageTime = date;}
public Date getStorageTime() {return storageTime;}
}
public BeanBook loadBook(String barcode) throws DbException {
Connection conn = null;
try {
conn = DBUtil.getConnection();
//修改处
String sql = "select b.barcode,b.bookname,b.pubid,b.price,b.state,b.storagetime,p.publishername " +
" from beanbook b left outer join beanpublisher p on (b.pubid=p.pubid)" +
" where b.barcode=? ";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, barcode);
java.sql.ResultSet rs = pst.executeQuery();
if (rs.next()) {
BeanBook b = new BeanBook();
b.setBarcode(rs.getString(1));
b.setBookname(rs.getString(2));
b.setPubid(rs.getString(3));
b.setPrice(rs.getDouble(4));
b.setState(rs.getString(5));
//修改处
b.setStorageTime(rs.getDate(6));
b.setPubName(rs.getString(7));
return b;
}
} catch (SQLException e) {
e.printStackTrace();
throw new DbException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return null;
}
本文是原创文章，采用 CC BY-NC-ND 4.0 协议，完整转载请注明来自 Owen
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果