(一)DAO设计及BaseDAO和BookDAO的实现
1.总体架构
MVC 设计模式：
Model：POJO（Plain Old Java Object）
Controller：Servlet
View：JSP + EL + JSTL
2.技术选型
数据库：MySQL
数据源：C3P0
JDBC 工具：DBUtils
事务解决方案：Filter + ThreadLocal
Ajax 解决方案：jQuery + JavaScript + JSON + google-gson
层之间解耦方案：工厂设计模式
3.数据表设计
相关表的创建
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`accountid` INT(11) NOT NULL AUTO_INCREMENT,
`balance` FLOAT DEFAULT NULL,
PRIMARY KEY (`accountid`)
)
INSERT INTO `account`(`accountid`,`balance`) VALUES (1,9740);
DROP TABLE IF EXISTS `mybooks`;
CREATE TABLE `mybooks` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`Author` VARCHAR(30) NOT NULL,
`Title` VARCHAR(30) NOT NULL,
`Price` FLOAT NOT NULL,
`Publishingdate` DATE NOT NULL,
`Salesamount` INT(11) NOT NULL,
`Storenumber` INT(11) NOT NULL,
`Remark` TEXT NOT NULL,
PRIMARY KEY (`Id`)
)
INSERT INTO `mybooks`(`Id`,`Author`,`Title`,`Price`,`Publishingdate`,`Salesamount`,`Storenumber`,`Remark`)
VALUES (1,'Tom','Java 编程思想',50,'2009-06-22',17,83,'Good Java Book'),
(2,'Jerry','Oracle DBA 教材',60,'2009-06-22',12,88,'Good Oracle Book'),
(3,'Bob','Ruby',50,'2009-06-22',12,88,'Good 0'),
(4,'Mike','Javascript',51,'2009-06-22',0,100,'Good 1'),
(5,'Rose','Ajax',52,'2009-06-22',0,100,'Good 2'),
(6,'Backham','Struts',53,'2009-06-22',0,100,'Good 3'),
(7,'Zidon','Hibernate',54,'2009-06-22',2,12,'Good 4'),
(8,'Ronaerdo','Spring',55,'2009-06-22',2,13,'Good 5'),
(9,'Clinsman','Cvs',56,'2009-06-22',0,16,'Good 6'),
(10,'Kaka','Seo',57,'2009-06-22',0,17,'Good 7'),
(11,'Lauer','Lucence',58,'2009-06-22',0,18,'Good 8'),
(12,'Kasi','Guice',59,'2009-06-22',0,19,'Good 9'),
(13,'Prierlo','Mysql',60,'2009-06-22',6,14,'Good 10'),
(14,'Haohaidong','DB2',61,'2009-06-22',9,12,'Good 11'),
(15,'Feige','Sybase',62,'2009-06-22',8,14,'Good 12'),
(16,'Tuoleisi','DBDesign',63,'2009-06-22',0,23,'Good 13'),
(17,'Jielade','Eclipse',64,'2009-06-22',0,24,'Good 14'),
(18,'Teli','Netbeans',65,'2009-06-22',0,25,'Good 15'),
(19,'Lapade','C#',66,'2009-06-22',0,26,'Good 16'),
(20,'Runi','JDBC',67,'2009-06-22',0,27,'Good 17'),
(21,'JoeKeer','Php',68,'2009-06-22',0,28,'Good 18'),
(22,'Jordan','MysqlFront',69,'2009-06-22',5,24,'Good 19'),
(23,'Yaoming','NoteBook',70,'2009-06-22',5,25,'Good 20'),
(24,'Yi','C',71,'2009-06-22',5,26,'Good 21'),
(25,'Sun','Css',72,'2009-06-22',0,32,'Good 22'),
(26,'Xuliang','JQuery',73,'2009-06-22',0,33,'Good 23'),
(27,'Meixi','Ext',74,'2009-06-22',0,34,'Good 24'),
(28,'Apple','iphone',75,'2009-06-22',0,35,'Good 25'),
(29,'Aigo','dc',76,'2009-06-22',0,36,'Good 26'),
(30,'Sony','psp',77,'2009-06-22',0,100,'Good 27'),
(31,'IRiver','mp3',78,'2009-06-22',0,100,'Good 28'),
(32,'Sansing','TV',79,'2009-06-22',0,100,'Good 29');
DROP TABLE IF EXISTS `trade`;
CREATE TABLE `trade` (
`tradeid` INT(11) NOT NULL AUTO_INCREMENT,
`userid` INT(11) NOT NULL,
`tradetime` DATETIME NOT NULL,
PRIMARY KEY (`tradeid`),
KEY `user_id_fk` (`userid`),
CONSTRAINT `user_id_fk` FOREIGN KEY (`userid`) REFERENCES `userinfo` (`userid`)
)
INSERT INTO `trade`(`tradeid`,`userid`,`tradetime`) VALUES (12,1,'2012-11-01 00:00:00'),(13,1,'2012-11-01 00:00:00'),
(14,1,'2012-11-01 00:00:00')
,(15,1,'2012-12-20 00:00:00'),
(16,1,'2012-12-20 00:00:00');
DROP TABLE IF EXISTS `tradeitem`;
CREATE TABLE `tradeitem` (
`itemid` INT(11) NOT NULL AUTO_INCREMENT,
`bookid` INT(11) DEFAULT NULL,
`quantity` INT(11) DEFAULT NULL,
`tradeid` INT(11) DEFAULT NULL,
PRIMARY KEY (`itemid`),
KEY `book_id_fk` (`bookid`),
KEY `trade_id_fk` (`tradeid`),
CONSTRAINT `book_id_fk` FOREIGN KEY (`bookid`) REFERENCES `mybooks` (`Id`),
CONSTRAINT `trade_id_fk` FOREIGN KEY (`tradeid`) REFERENCES `trade` (`tradeid`)
)
INSERT INTO `tradeitem`(`itemid`,`bookid`,`quantity`,`tradeid`) VALUES (22,1,10,12),
(23,2,10,12),(24,3,10,12),(25,1,1,13),(26,13,2,13),(27,14,3,13),(28,15,4,13),(29,1,1,14),
(30,13,2,14),(31,14,3,14),(32,15,4,14),(33,22,5,14),(34,23,5,14),(35,24,5,14),(36,2,1,15),
(37,1,2,15),(38,3,1,15),(39,2,1,16),(40,1,3,16),(41,3,1,16);
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`userid` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) DEFAULT NULL,
`accountid` INT(11) DEFAULT NULL,
PRIMARY KEY (`userid`),
KEY `account_id_fk` (`accountid`),
CONSTRAINT `account_id_fk` FOREIGN KEY (`accountid`) REFERENCES `account` (`accountid`)
)
INSERT INTO `userinfo`(`userid`,`username`,`accountid`) VALUES (1,'Tom',1),(2,'AAA',1),(3,'BB',1),(4,'CC',1),(5,'DD',1),(6,'EE',1);
4.搭建环境
加入 C3P0 数据源
--加入 jar 包
--加入配置文件
--编辑配置文件
<named-config name="javawebapp">
<!--提供获取连接的四个基本信息 -->
<!--连接本地主机的话： jbdc:mysql://localhost:3306/test 可写成jbdc:mysql:///test -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/bookstore</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 对数据库连接池管理的基本信息 -->
<!-- 当数据库连接池中的连接数不够时，c3p0一次向数据库服务器申请的连接数 -->
<property name="acquireIncrement">5</property>
<!-- 初始化时的连接数 -->
<property name="initialPoolSize">10</property>
<!-- 维护的最少连接数 -->
<property name="minPoolSize">10</property>
<!-- 维护的最多的连接数 -->
<property name="maxPoolSize">100</property>
<!-- 最多维护的Satement的个数 -->
<property name="maxStatements">50</property>
<!-- 每个连接最多使用Statement的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config> 加入 dbutils 工具类
加入 JSTL
其它：使用随时加入
5.Dao 层设计
目录结构
Dao.java
package com.aff.bookstore.dao;
import java.util.List;
/**
* Dao 接口, 定义 Dao 的基本操作, 由 BaseDao 提供实现.
* @param
*/
public interface Dao
/\*\*
\* 执行 INSERT 操作, 返回插入后的记录的 ID
\* @param sql: 待执行的 SQL 语句
\* @param args: 填充占位符的可变参数
\* @return: 插入新记录的 id
\*/
long insert(String sql, Object ... args);
/\*\*
\* 执行 UPDATE 操作, 包括 INSERT(但没有返回值), UPDATE, DELETE
\* @param sql: 待执行的 SQL 语句
\* @param args: 填充占位符的可变参数
\*/
void update(String sql, Object ... args);
/\*\*
\* 执行单条记录的查询操作, 返回与记录对应的类的一个对象
\* @param sql: 待执行的 SQL 语句
\* @param args: 填充占位符的可变参数
\* @return: 与记录对应的类的一个对象
\*/
T query(String sql, Object ... args);
/\*\*
\* 执行多条记录的查询操作, 返回与记录对应的类的一个 List
\* @param sql: 待执行的 SQL 语句
\* @param args: 填充占位符的可变参数
\* @return: 与记录对应的类的一个 List
\*/
List<T> queryForList(String sql, Object ... args);
/\*\*
\* 执行一个属性或值的查询操作, 例如查询某一条记录的一个字段, 或查询某个统计信息, 返回要查询的值
\* @param sql: 待执行的 SQL 语句
\* @param args: 填充占位符的可变参数
\* @return: 执行一个属性或值的查询操作, 例如查询某一条记录的一个字段, 或查询某个统计信息, 返回要查询的值
\*/
<V> V getSingleVal(String sql, Object ... args);
/\*\*
\* 执行批量更新操作
\* @param sql: 待执行的 SQL 语句
\* @param args: 填充占位符的可变参数
\*/
void batch(String sql, Object\[\]... params); }
BookDAO.java
package com.aff.bookstore.dao;
import java.util.Collection;
import java.util.List;
import com.aff.bookstore.domain.Book;
import com.aff.bookstore.domain.ShoppingCartItem;
import com.aff.bookstore.web.CriteriaBook;
import com.aff.bookstore.web.Page;
public interface BookDAO {
/\*\*
\* 根据 id 获取指定 Book 对象
\* @param id
\* @return
\*/
public abstract Book getBook(int id);
/\*\*
\* 根据传入的 CriteriaBook 对象返回对应的 Page 对象
\* @param cb
\* @return
\*/
public abstract Page<Book> getPage(CriteriaBook cb);
/\*\*
\* 根据传入的 CriteriaBook 对象返回其对应的记录数
\* @param cb
\* @return
\*/
public abstract long getTotalBookNumber(CriteriaBook cb);
/\*\*
\* 根据传入的 CriteriaBook 和 pageSize 返回当前页对应的 List
\* @param cb
\* @param pageNo
\* @param pageSize
\* @return
\*/
public abstract List<Book> getPageList(CriteriaBook cb,int pageSize);
/\*\*
\* 返回指定 id 的 book 的 storeNumber 字段的值
\* @param id
\* @return
\*/
public abstract int getStoreNumber(Integer id);
/\*\*
\* 根据传入的 ShoppingCartItem 的集合,
\* 批量更新 books 数据表的 storenumber 和 salesnumber 字段的值
\* @param items
\*/
public abstract void batchUpdateStoreNumberAndSalesAmount(
Collection<ShoppingCartItem> items);}
BaseDAO.java
package com.aff.bookstore.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.aff.bookstore.dao.Dao;
import com.aff.bookstore.db.JDBCUtils;
import com.aff.bookstore.utils.ReflectionUtils;
public class BaseDAO
private QueryRunner queryRunner = new QueryRunner();
private Class
public BaseDAO() {
clazz = ReflectionUtils.getSuperGenericType(getClass());
}
@Override
public long insert(String sql, Object... args) {
// 我们需要返回它的主键id值
// update 中的insert 操作没有返回值
// quertRunner也没有返回值
// 此时需要使用原生的jdbc写
long id = 0;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql, Statement.RETURN\_GENERATED\_KEYS);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args\[i\]);
}
}
preparedStatement.executeUpdate();
// 获取生成的主键值
resultSet = preparedStatement.getGeneratedKeys();
if (resultSet.next()) {
id = resultSet.getLong(1);// 第一列主键值 id
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(resultSet, preparedStatement);
JDBCUtils.release(connection);
}
return id;
}
@Override
public void update(String sql, Object... args) {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
queryRunner.update(connection, sql,args);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(connection);
}
}
@Override
public T query(String sql, Object... args) {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
return queryRunner.query(connection, sql, new BeanHandler<>(clazz), args);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(connection);
}
return null;
}
@Override
public List<T> queryForList(String sql, Object... args) {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler<>(clazz), args);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(connection);
}
return null;
}
@Override
public <V> V getSingleVal(String sql, Object... args) {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
return (V) queryRunner.query(connection, sql, new ScalarHandler(), args);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(connection);
}
return null;
}
@Override
public void batch(String sql, Object\[\]... params) {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
queryRunner.batch(connection, sql, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(connection);
}
} }
BookDaoImpl.java
package com.aff.bookstore.dao.impl;
import java.util.Collection;
import java.util.List;
import com.aff.bookstore.dao.BookDAO;
import com.aff.bookstore.domain.Book;
import com.aff.bookstore.domain.ShoppingCartItem;
import com.aff.bookstore.web.CriteriaBook;
import com.aff.bookstore.web.Page;
public class BookDaoImpl extends BaseDAO
@Override
public Book getBook(int id) {
String sql = "select id, author, title, price ,publishingDate,salesAmount,storeNumber,remark from mybooks where id = ? ";
return query(sql, id);
}
@Override
public Page<Book> getPage(CriteriaBook cb) {
Page page = new Page<>(cb.getPageNo());
page.setTotalItemNumber(getTotalBookNumber(cb));
// 检验 pageNo的合法性
cb.setPageNo(page.getPageNo());
page.setList(getPageList(cb, 3));
return page;
}
// 获取在这个价格区间的有多少条数目
@Override
public long getTotalBookNumber(CriteriaBook cb) {
String sql = "select count(id) from mybooks where price >=? and price <= ? ";
return getSingleVal(sql, cb.getMinPrice(), cb.getMaxPrice());
}
@Override
public List<Book> getPageList(CriteriaBook cb, int pageSize) {
// 起始从0 开始的，所以 (cb.getPageNo()- 1) \*pageSize,pageSize
String sql = "select id, author, title, price ,publishingDate,salesAmount,storeNumber,remark from mybooks where price >=? and price <= ? limit ?,?";
return queryForList(sql, cb.getMinPrice(), cb.getMaxPrice(), (cb.getPageNo() - 1) \* pageSize, pageSize);
}
@Override
public int getStoreNumber(Integer id) {
String sql = "select storeNumber from mybooks where id = ?";
return getSingleVal(sql, id);
}
@Override
public void batchUpdateStoreNumberAndSalesAmount(Collection<ShoppingCartItem> items) {
} }
手机扫一扫
移动阅读更方便
你可能感兴趣的文章