宠物商店项目（控制台输出）。
使用的环境如下：
（1）eclipse
（2）mysql
（3）连接数据库使用的5.0jar包
项目结构如下：
数据库表如下所示：
/*
Navicat MySQL Data Transfer
Source Server : demo
Source Server Version : 50527
Source Host : localhost:3306
Source Database : epetshop
Target Server Type : MYSQL
Target Server Version : 50527
File Encoding : 65001
Date: 2019-08-13 15:25:36
*/
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) DEFAULT NULL,
`pet_id` int(4) DEFAULT NULL,
`seller_id` int(4) DEFAULT NULL,
`buyer_id` int(4) DEFAULT NULL,
`price` int(4) DEFAULT NULL,
`deal_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_account_pet` (`pet_id`),
KEY `fk_account_petOwner` (`seller_id`),
CONSTRAINT `fk_account_petOwner` FOREIGN KEY (`seller_id`) REFERENCES `petowner` (`id`),
CONSTRAINT `fk_account_pet` FOREIGN KEY (`pet_id`) REFERENCES `pet` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- 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');
-- ----------------------------
-- Table structure for pet
-- ----------------------------
DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
`typeName` char(10) DEFAULT NULL,
`health` int(4) DEFAULT NULL,
`love` int(4) DEFAULT NULL,
`birthday` timestamp NULL DEFAULT NULL,
`owner_id` int(4) DEFAULT NULL,
`store_id` int(4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_pet_petOwner` (`owner_id`),
KEY `fk_pet_petStore` (`store_id`),
CONSTRAINT `fk_pet_petStore` FOREIGN KEY (`store_id`) REFERENCES `petstore` (`id`),
CONSTRAINT `fk_pet_petOwner` FOREIGN KEY (`owner_id`) REFERENCES `petowner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of pet
-- ----------------------------
INSERT INTO `pet` VALUES ('1', '花花', 'dog', '1', '50', '2015-08-20 00:00:00', '1', '1');
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', '1');
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) DEFAULT NULL,
`password` char(10) DEFAULT NULL,
`money` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of petowner
-- ----------------------------
INSERT INTO `petowner` VALUES ('1', '小明', '123456', '178');
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) DEFAULT NULL,
`password` char(10) DEFAULT NULL,
`balance` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of petstore
-- ----------------------------
INSERT INTO `petstore` VALUES ('1', '北京信息中心', '123456', '624');
INSERT INTO `petstore` VALUES ('2', '重庆观音桥', '123456', '800');
实体类如下：entity
/**
*
*/
package entity;
import java.util.Date;
/**
* 宠物商店台帐类
*
*/
public class Account {
/**
* 帐单标识符
*/
private long id;
/**
* 交易类型，1--代表商店卖给宠物主人 2--代表宠物主人卖给商店 3---宠物主人之间交易
*/
private int dealType;
/**
* 宠物标识符
*/
private long petId;
/**
* 卖家标识符
*/
private long sellerId;
/**
* 买家标识符
*/
private long buyerId;
/**
* 交易价格
*/
private double price;
/**
* 交易时间
*/
private Date dealTime;
/**
* @return the buyerId
*/
public long getBuyerId() {
return buyerId;
}
/**
* @param buyerId
* the buyerId to set
*/
public void setBuyerId(long buyerId) {
this.buyerId = buyerId;
}
/**
* @return the dealTime
*/
public Date getDealTime() {
return dealTime;
}
/**
* @param dealTime
* the dealTime to set
*/
public void setDealTime(Date dealTime) {
this.dealTime = dealTime;
}
/**
* @return the dealType
*/
public int getDealType() {
return dealType;
}
/**
* @param dealType
* the dealType to set
*/
public void setDealType(int dealType) {
this.dealType = dealType;
}
/**
* @return the id
*/
public long getId() {
return id;
}
/**
* @param id
* the id to set
*/
public void setId(long id) {
this.id = id;
}
/**
* @return the petId
*/
public long getPetId() {
return petId;
}
/**
* @param petId
* the petId to set
*/
public void setPetId(long petId) {
this.petId = petId;
}
/**
* @return the price
*/
public double getPrice() {
return price;
}
/**
* @param price
* the price to set
*/
public void setPrice(double price) {
this.price = price;
}
/**
* @return the sellerId
*/
public long getSellerId() {
return sellerId;
}
/**
* @param sellerId
* the sellerId to set
*/
public void setSellerId(long sellerId) {
this.sellerId = sellerId;
}
}
/**
*
*/
package entity;
import java.util.Date;
/**
* 宠物实体类
*
*/
public class Pet {
/*
* 宠物标识符
*/
private long id;
/**
* 宠物名称
*/
private String name;
/**
* 宠物类别
*/
private String typeName;
/**
* 宠物健康指数
*/
private int health;
/**
* 宠物爱心
*/
private int love;
/**
* 宠物生日
*/
private Date birthday;
/**
* 宠物所属宠物主人标识符
*/
private int ownerId;
/**
* 宠物所属宠物商店标识符
*/
private long storeId;
/**
* @return the ownerId
*/
public int getOwnerId() {
return ownerId;
}
/**
* @param ownerId
* the ownerId to set
*/
public void setOwnerId(int ownerId) {
this.ownerId = ownerId;
}
/**
* @return the birthdate
*/
public Date getBirthday() {
return birthday;
}
/**
* @return the storeId
*/
public long getStoreId() {
return storeId;
}
/**
* @param storeId
* the storeId to set
*/
public void setStoreId(long storeId) {
this.storeId = storeId;
}
/**
* @param birthdate
* the birthday to set
*/
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
/**
* @return the health
*/
public int getHealth() {
return health;
}
/**
* @param health
* the health to set
*/
public void setHealth(int health) {
this.health = health;
}
/**
* @return the id
*/
public long getId() {
return id;
}
/**
* @param id
* the id to set
*/
public void setId(long id) {
this.id = id;
}
/**
* @return the love
*/
public int getLove() {
return love;
}
/**
* @param love
* the love to set
*/
public void setLove(int love) {
this.love = love;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name
* the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the typeName
*/
public String getTypeName() {
return typeName;
}
/**
* @param typeName
* the typeName to set
*/
public void setTypeName(String typeName) {
this.typeName = typeName;
}
}
package entity;
/**
* 宠物主人实体类
*
*/
public class PetOwner {
/**
* 宠物主人标识符
*/
private int id;
/**
* 宠物主人名称
*/
private String name;
/**
* 宠物主人密码
*/
private String password;
/**
* 宠物主人元宝数
*/
private double money;
/**
* @return the money
*/
public double getMoney() {
return money;
}
/**
* @param money
* the money to set
*/
public void setMoney(double money) {
this.money = money;
}
/**
* @return the id
*/
public int getId() {
return id;
}
/**
* @param id
* the id to set
*/
public void setId(int id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name
* the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the password
*/
public String getPassword() {
return password;
}
/**
* @param password
* the password to set
*/
public void setPassword(String password) {
this.password = password;
}
}
/**
*
*/
package entity;
/**
* 宠物商店实体类
*
*/
public class PetStore {
/**
* 宠物商店id
*/
private long id;
/**
* 宠物商店名称
*/
private String name;
/**
* 宠物商店密码
*/
private String password;
/**
* 宠物商店资金
*/
private double balance;
/**
* @return the id
*/
public long getId() {
return id;
}
/**
* @param id
* the id to set
*/
public void setId(long id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name
* the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the password
*/
public String getPassword() {
return password;
}
/**
* @param password
* the password to set
*/
public void setPassword(String password) {
this.password = password;
}
/**
* @return the balance 得到宠物商店结余
*/
public double getBalance() {
return balance;
}
/**
* @param balance
* the balance to set
*/
public void setBalance(double balance) {
this.balance = balance;
}
}
dao层如下：
package dao;
import java.util.List;
import entity.Account;
public interface AccountDao {
/**
* 更新台帐信息
*/
public abstract int updateAccount(String sql, Object[] param);
/**
* 根据查询条件查询出宠物商店帐单
*/
public abstract List<Account> getPetStoreAccount(String sql, String[] param);
}
package dao;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import dao.BaseDao;
/**
* 数据库操作基类
*/
public class BaseDao {
public static String DRIVER; // 数据库驱动
public static String URL ; // url
public static String DBNAME; // 数据库用户名
public static String DBPASS; // 数据库密码
Connection conn = null;// 数据连接对象
static{//静态代码块,在类加载的时候执行
init();
}
/**
* 初始化连接参数,从配置文件里获得
*/
public static void init(){
Properties params=new Properties();
String configFile = "database.properties";//配置文件路径
//加载配置文件到输入流中
InputStream is=BaseDao.class.getClassLoader().getResourceAsStream(configFile);
try {
//从输入流中读取属性列表
params.load(is);
} catch (IOException e) {
e.printStackTrace();
}
//根据指定的获取对应的值
DRIVER=params.getProperty("driver");
URL=params.getProperty("url");
DBNAME=params.getProperty("user");
DBPASS=params.getProperty("password");
}
/**
* 得到数据库连接
*
* @throws ClassNotFoundException
* @throws SQLException
* @return 数据库连接
*/
public Connection getConn() throws ClassNotFoundException, SQLException {
Connection conn = null;
try {
Class.forName(DRIVER); // 注册驱动
conn = DriverManager.getConnection(URL, DBNAME, DBPASS); // 获得数据库连接
} catch (SQLException e) {
e.printStackTrace();
}
return conn; // 返回连接
}
/**
* 释放资源
*
* @param conn
* 数据库连接
* @param pstmt
* PreparedStatement对象
* @param rs
* 结果集
*/
public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
/* 如果rs不空，关闭rs */
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/* 如果pstmt不空，关闭pstmt */
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/* 如果conn不空，关闭conn */
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行SQL语句，可以进行增、删、改的操作，不能执行查询
*
* @param sql
* 预编译的 SQL 语句
* @param param
* 预编译的 SQL 语句中的‘？’参数的字符串数组
* @return 影响的条数
*/
public int executeSQL(String preparedSql, Object[] param) {
Connection conn = null;
PreparedStatement pstmt = null;
int num = 0;
/* 处理SQL,执行SQL */
try {
conn = getConn(); // 得到数据库连接
pstmt = conn.prepareStatement(preparedSql); // 得到PreparedStatement对象
if (param != null) {
for (int i = 0; i < param.length; i ) {
pstmt.setObject(i 1, param[i]); // 为预编译sql设置参数
}
}
// System.out.println(preparedSql);
num = pstmt.executeUpdate(); // 执行SQL语句
} catch (ClassNotFoundException e) {
e.printStackTrace(); // 处理ClassNotFoundException异常
} catch (SQLException e) {
e.printStackTrace(); // 处理SQLException异常
} finally {
this.closeAll(conn, pstmt, null);
}
return num;
}
}
package dao;
import java.util.List;
import entity.Pet;
public interface PetDao {
/**
* 查询所有宠物信息
*/
public abstract List<Pet> getAllPet();
/**
* 根据已知宠物的信息查询宠物信息
*/
public abstract List<Pet> selectPet(String sql, String[] param);
/**
* 更新宠物信息
*/
public abstract int updatePet(String sql, Object[] param);
}
package dao;
import java.util.List;
import entity.PetOwner;
public interface PetOwnerDao {
/**
* 查询所有宠物主人信息
*/
public abstract List<PetOwner> getAllOwner();
/**
* 更新宠物主人信息
*/
public abstract int updateOwner(String sql, String[] param);
/**
* 根据查询条件查询宠物主人信息
*/
public abstract PetOwner selectOwner(String sql, String[] param);
}
package dao;
import java.util.List;
import entity.PetStore;
public interface PetStoreDao {
/**
* 查询出所有宠物商店
*/
public abstract List<PetStore> getAllStore();
/**
* 根据查询条件查询出宠物商店
*/
public abstract PetStore getPetStore(String sql, String[] param);
/**
* 更新宠物商店信息
*/
public abstract int updateStore(String sql, Object[] param);
}剩余的业务逻辑层，下集更精彩。
