1.1 目录树：
├───main
│ ├───java
│ │ └───cn
│ │ └───lemon77
│ │ ├───dao
│ │ │ │ AccountDao.java
│ │ │ │ BaseDao.java
│ │ │ │ PetDao.java
│ │ │ │ PetOwnerDao.java
│ │ │ │ PetStoreDao.java
│ │ │ │
│ │ │ └───impl
│ │ │ AccountDaoImpl.java
│ │ │ PetDaoImpl.java
│ │ │ PetOwnerDaoImpl.java
│ │ │ PetStoreDaoImpl.java
│ │ │
│ │ └───pojo
│ │ Account.java
│ │ Pet.java
│ │ PetOwner.java
│ │ PetStore.java
│ │
│ └───resources
└───test
└───java
1.2 AccountDao.java
package cn.lemon77.dao;
public interface AccountDao {
}
1.3 BaseDao.java
package cn.lemon77.dao;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class BaseDao {
private static String DBURL;
private static String DRIVER;
private static String USERNAME;
private static String PASSWORD;
static {
Properties properties = new Properties();
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
DBURL = properties.getProperty("dburl");
DRIVER = properties.getProperty("driver");
USERNAME = properties.getProperty("username");
PASSWORD = properties.getProperty("password");
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(DBURL, USERNAME, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static int executeUpdate(String sql, Object... params) {
Connection connection = getConnection();
PreparedStatement pstmt = null;
int count = -1;
try {
pstmt = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
count = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(connection, pstmt, null);
}
return count;
}
public static void closeAll(Connection connection, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
1.4 PetDao.java
package cn.lemon77.dao;
public interface PetDao {
}
1.5 PetOwnerDao.java
package cn.lemon77.dao;
public interface PetOwnerDao {
}
1.6 PetStoreDao.java
package cn.lemon77.dao;
public interface PetStoreDao {
}
1.7 AccountDaoImpl.java
package cn.lemon77.dao.impl;
import cn.lemon77.dao.AccountDao;
import cn.lemon77.dao.BaseDao;
public class AccountDaoImpl extends BaseDao implements AccountDao {
}
1.8 PetDaoImpl.java
package cn.lemon77.dao.impl;
import cn.lemon77.dao.BaseDao;
import cn.lemon77.dao.PetDao;
public class PetDaoImpl extends BaseDao implements PetDao {
}
1.9 PetOwnerDaoImpl.java
package cn.lemon77.dao.impl;
import cn.lemon77.dao.BaseDao;
import cn.lemon77.dao.PetOwnerDao;
public class PetOwnerDaoImpl extends BaseDao implements PetOwnerDao {
}
1.10 PetStoreDaoImpl.java
package cn.lemon77.dao.impl;
import cn.lemon77.dao.BaseDao;
import cn.lemon77.dao.PetStoreDao;
public class PetStoreDaoImpl extends BaseDao implements PetStoreDao {
}
## 1.11 Account.java
```java
package cn.lemon77.pojo;
import java.util.Date;
public class Account {
private int id; // 帐目编号
private int dealType; // 交易类型，1商店卖给主人，2反之
private int petId; // 宠物编号
private int sellerId; // 卖家编号
private int buyerId; // 买家编号
private int price; // 交易价格
private Date dealTime; // 交易时间
public Account() {}
public Account(int id, int dealType, int petId, int sellerId, int buyerId, int price, Date dealTime) {
this.id = id;
this.dealType = dealType;
this.petId = petId;
this.sellerId = sellerId;
this.buyerId = buyerId;
this.price = price;
this.dealTime = dealTime;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getDealType() {
return dealType;
}
public void setDealType(int dealType) {
this.dealType = dealType;
}
public int getPetId() {
return petId;
}
public void setPetId(int petId) {
this.petId = petId;
}
public int getSellerId() {
return sellerId;
}
public void setSellerId(int sellerId) {
this.sellerId = sellerId;
}
public int getBuyerId() {
return buyerId;
}
public void setBuyerId(int buyerId) {
this.buyerId = buyerId;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public Date getDealTime() {
return dealTime;
}
public void setDealTime(Date dealTime) {
this.dealTime = dealTime;
}
}
1.12 Pet.java
package cn.lemon77.pojo;
import java.util.Date;
public class Pet {
private int id; //宠物编号
private String name; // 宠物名称
private String typeName; // 种类名称
private int health; // 健康值
private int love; // 亲密度
private Date birthday; // 出生日期
private String ownerId; // 宠物主人编号
private String storeId; // 宠物商店编号
public Pet() {}
public Pet(int id, String name, String typeName, int health, int love, Date birthday, String ownerId, String storeId) {
this.id = id;
this.name = name;
this.typeName = typeName;
this.health = health;
this.love = love;
this.birthday = birthday;
this.ownerId = ownerId;
this.storeId = storeId;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTypeName() {
return typeName;
}
public void setTypeName(String typeName) {
this.typeName = typeName;
}
public int getHealth() {
return health;
}
public void setHealth(int health) {
this.health = health;
}
public int getLove() {
return love;
}
public void setLove(int love) {
this.love = love;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getOwnerId() {
return ownerId;
}
public void setOwnerId(String ownerId) {
this.ownerId = ownerId;
}
public String getStoreId() {
return storeId;
}
public void setStoreId(String storeId) {
this.storeId = storeId;
}
}
1.13 PetOwner.java
package cn.lemon77.pojo;
public class PetOwner {
private int id; // 宠物主人编号
private String name; // 宠物主人名称
private String password; // 密码
private int money; // 元宝数
public PetOwner() {}
public PetOwner(int id, String name, String password, int money) {
this.id = id;
this.name = name;
this.password = password;
this.money = money;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
}
1.14 PetStore.java
package cn.lemon77.pojo;
public class PetStore {
private int id; // 商店编号
private String name; // 商店名称
private String password; // 密码
private int balance; // 余额
public PetStore() {}
public PetStore(int id, String name, String password, int balance) {
this.id = id;
this.name = name;
this.password = password;
this.balance = balance;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getBalance() {
return balance;
}
public void setBalance(int balance) {
this.balance = balance;
}
}
1.18 建表sql文件
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`deal_type` int(4) NULL DEFAULT NULL,
`pet_id` int(4) NULL DEFAULT NULL,
`seller_id` int(4) NULL DEFAULT NULL,
`buyer_id` int(4) NULL DEFAULT NULL,
`price` int(4) NULL DEFAULT NULL,
`deal_time` timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_account_pet`(`pet_id`) USING BTREE,
INDEX `fk_account_petOwner`(`seller_id`) USING BTREE,
CONSTRAINT `fk_account_pet` FOREIGN KEY (`pet_id`) REFERENCES `pet` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_account_petOwner` FOREIGN KEY (`seller_id`) REFERENCES `petowner` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (2, 1, 4, 1, 1, 5, '2016-08-20 00:00:00');
INSERT INTO `account` VALUES (3, 1, 3, 1, 1, 5, '2016-08-20 00:00:00');
INSERT INTO `account` VALUES (4, 1, 3, 1, 1, 5, '2016-09-10 00:00:00');
INSERT INTO `account` VALUES (5, 2, 2, 2, 1, 3, '2016-09-10 00:00:00');
INSERT INTO `account` VALUES (6, 2, 3, 1, 1, 3, '2016-10-15 00:00:00');
INSERT INTO `account` VALUES (7, 2, 1, 1, 2, 5, '2020-10-09 00:00:00');
-- ----------------------------
-- Table structure for pet
-- ----------------------------
DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`typeName` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`health` int(4) NULL DEFAULT NULL,
`love` int(4) NULL DEFAULT NULL,
`birthday` timestamp(0) NULL DEFAULT NULL,
`owner_id` int(4) NULL DEFAULT NULL,
`store_id` int(4) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_pet_petOwner`(`owner_id`) USING BTREE,
INDEX `fk_pet_petStore`(`store_id`) USING BTREE,
CONSTRAINT `fk_pet_petOwner` FOREIGN KEY (`owner_id`) REFERENCES `petowner` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_pet_petStore` FOREIGN KEY (`store_id`) REFERENCES `petstore` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of pet
-- ----------------------------
INSERT INTO `pet` VALUES (1, '花花', 'dog', 1, 50, '2015-08-20 00:00:00', NULL, 2);
INSERT INTO `pet` VALUES (2, '贝贝', 'penguin', 1, 60, '2015-08-20 00:00:00', NULL, 2);
INSERT INTO `pet` VALUES (3, '成成', 'dog', 1, 60, '2015-09-10 00:00:00', NULL, 1);
INSERT INTO `pet` VALUES (4, '露露', 'bird', 1, 70, '2016-01-10 00:00:00', NULL, 1);
INSERT INTO `pet` VALUES (5, '老虎', 'tiger', 1, 2, '2016-02-10 00:00:00', 2, NULL);
INSERT INTO `pet` VALUES (6, '老虎', 'tiger', 1, 2, '2016-03-15 00:00:00', NULL, 1);
INSERT INTO `pet` VALUES (7, '老虎', 'tiger', 1, 11, '2016-02-15 00:00:00', NULL, 1);
INSERT INTO `pet` VALUES (8, '狮子', 'lion', 1, 2, '2016-04-15 00:00:00', NULL, 2);
-- ----------------------------
-- Table structure for petowner
-- ----------------------------
DROP TABLE IF EXISTS `petowner`;
CREATE TABLE `petowner` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`money` int(4) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of petowner
-- ----------------------------
INSERT INTO `petowner` VALUES (1, '小明', '123456', 183);
INSERT INTO `petowner` VALUES (2, '小强', '123456', 498);
-- ----------------------------
-- Table structure for petstore
-- ----------------------------
DROP TABLE IF EXISTS `petstore`;
CREATE TABLE `petstore` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`balance` int(4) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of petstore
-- ----------------------------
INSERT INTO `petstore` VALUES (1, '北京信息中心', '123456', 624);
INSERT INTO `petstore` VALUES (2, '重庆观音桥', '123456', 795);
SET FOREIGN_KEY_CHECKS = 1;
1.19 db.properties
dburl=jdbc:mysql:///epatshop
driver=com.mysql.cj.jdbc.Driver
username=xxxxx
password=xxxxx
![]()
浙公网安备 33010602011771号