javaweb开发（十三）：数据库连接池和Apache DBUtils操作数据库
-
构建web项目
-
配置tomcat
-
导入依赖
commons-dbutils-1.7.jar是数据库工具包
commons-dbcp2-2.7.0.jar是数据库连接池包
- src目录下编写database.properties
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false
username = root
password = 123456
initialSize=10 # 连接池建立时创建的连接的数量
maxActive=30 # 连接池同一时间内最多能够分配的活动连接的数量
- 编写DataSourceUtil
public class DataSourceUtil {
private static DataSource dataSource;
static {
try{
InputStream in = DataSourceUtil.class.getClassLoader().getResourceAsStream("database.properties");
Properties p = new Properties();
p.load(in);
dataSource = BasicDataSourceFactory.createDataSource(p);
}catch (Exception e){
e.printStackTrace();
throw new ExceptionInInitializerError("初始化DBPC失败");
}
}
/**
* 获取连接池
* @return
*/
public static DataSource getDataSource(){
return dataSource;
}
}
- Apache dbutils⼯具包简介
# QueryRunner
查询执⾏器，提供对sql语句操作的API
update(String sql,Object...params) 可执⾏ 增-INSERT、删-DELETE、改-UPDATE
query(String sql,ResultSetHandler rsh,Object...params) 可执⾏ 查询-SELECT
# ResultSetHandler
结果集处理类，执⾏处理⼀个结果集对象，将数据转变并处理为任何⼀种形式
BeanHandler 结果集中的第⼀⾏数据封装到⼀个对应的JavaBean实例
BeanListHandler 结果集中的每⼀⾏数据都封装到⼀个对应的JavaBean实例中，
存放到List⾥
MapHandler 结果集中的第⼀⾏数据封装到⼀个Map⾥，key是列名，value就是
对应的值
MapListHandler 结果集中的每⼀⾏数据都封装到⼀个Map⾥，然后再存放到List
ScalarHandler 结果集中第⼀⾏数据指定列的值,常⽤来进⾏单值查询
-
删除database.properties中的中文注释 -
导入如下依赖
-
编写实体类
public class User {
private int id;
private String phone;
private String pwd;
private int sex;
private String img;
private Date createTime;
private int role;
private String username;
private String wechat;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getImg() {
return img;
}
public void setImg(String img) {
this.img = img;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public int getRole() {
return role;
}
public void setRole(int role) {
this.role = role;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getWechat() {
return wechat;
}
public void setWechat(String wechat) {
this.wechat = wechat;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", phone='" + phone + '\'' +
", pwd='" + pwd + '\'' +
", sex=" + sex +
", img='" + img + '\'' +
", createTime=" + createTime +
", role=" + role +
", username='" + username + '\'' +
", wechat='" + wechat + '\'' +
'}';
}
}
- 编写UserDao
public class UserDao {
private QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
//开启驼峰映射
private BeanProcessor bean = new GenerousBeanProcessor();
private RowProcessor processor = new BasicRowProcessor(bean);
/**
* 根据id找用户
* @param id
* @return
*/
public User findById(int id){
String sql = "select * from user where id=?";
User user = null;
try {
user = queryRunner.query(sql,new BeanHandler<>(User.class,processor),id);
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
/**
* 查询所有
* @return
*/
public List<User> list(){
String sql = "select * from user";
List<User> list = null;
try {
list = queryRunner.query(sql,new BeanListHandler<>(User.class,processor));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 保存
* @param user
* @return
* @throws Exception
*/
public int save(User user) throws Exception {
String sql = "insert into user (phone,pwd,sex,img,create_time,role,username,wechat) values(?,?,?,?,?,?,?,?)";
Object [] params = {
user.getPhone(),
user.getPwd(),
user.getSex(),
user.getImg(),
user.getCreateTime(),
user.getRole(),
user.getUsername(),
user.getWechat()
};
int i = 0;
try{
i = queryRunner.update(sql,params);
}catch (Exception e){
e.printStackTrace();
throw new Exception();
}
return i;
}
/**
* 根据id查询
* @param id
* @return
*/
public Map<String,Object> findByIdWithMap(int id){
String sql = "select * from user where id=?";
Map<String,Object> map = null;
try {
map = queryRunner.query(sql,new MapHandler(),id);
} catch (SQLException e) {
e.printStackTrace();
}
return map;
}
/**
* 查找全部用户 MapListHandler
* @return
*/
public List<Map<String,Object>> listWithMap(){
String sql = "select * from user";
List<Map<String,Object>> list = null;
try {
list = queryRunner.query(sql,new MapListHandler());
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 统计个数
* @return
*/
public int countUser(){
String sql = "select count(*) from user";
Long count = null;
try{
count = (Long)queryRunner.query(sql,new ScalarHandler<>());
}catch (Exception e){
e.printStackTrace();
}
return count.intValue();
}
}
- 编写业务层
public interface UserService {
User findById(int id);
List<User> list();
Map<String,Object> findByIdWithMap(int id);
List<Map<String,Object>> listWithMap();
int countUser();
int save(User user);
}
public class UserServiceImpl implements UserService {
private UserDao userDao = new UserDao();
@Override
public User findById(int id) {
return userDao.findById(id);
}
@Override
public List<User> list() {
return userDao.list();
}
@Override
public Map<String, Object> findByIdWithMap(int id) {
return userDao.findByIdWithMap(id);
}
@Override
public List<Map<String, Object>> listWithMap() {
return userDao.listWithMap();
}
@Override
public int countUser() {
return userDao.countUser();
}
@Override
public int save(User user) {
try {
int rows = userDao.save(user);
return rows;
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
}
- 编写控制层
@WebServlet(name = "userServlet",urlPatterns = {"/user"})
public class UserServlet extends HttpServlet {
private UserService userService = new UserServiceImpl();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
// 根据id查询
if(method.equals("findById")){
String idStr = req.getParameter("id");
int userId = Integer.parseInt(idStr);
User user = userService.findById(userId);
System.out.println(user.toString());
}
// 查询所有
if(method.equals("list")){
List<User> list = userService.list();
System.out.println(list.toString());
}
// 根据id查询
if(method.equals("findByIdWithMap")){
String idStr = req.getParameter("id");
int userId = Integer.parseInt(idStr);
Map<String,Object> map = userService.findByIdWithMap(userId);
System.out.println(map.toString());
}
// 查询所有
if(method.equals("listWithMap")){
List<Map<String,Object>> list = userService.listWithMap();
System.out.println(list.toString());
}
// 统计个数
if(method.equals("count")){
int total = userService.countUser();
System.out.println("user表行数 = "+total);
}
// 保存
if(method.equals("save")){
User user = new User();
user.setPhone("sdfsdfwe");
user.setPwd("pwd");
user.setSex(1);
user.setImg("xdclass.net");
user.setCreateTime(new Date());
user.setRole(1);
user.setUsername("二当家小D");
user.setWechat("xdclass6");
int row = userService.save(user);
System.out.println("row="+row);
if(row == 1){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
}
}
}
- 测试
# 浏览器
http://localhost:8080/user?method=findById&id=3
# 控制台
User{id=3, phone='2323432', pwd='xdclass-lw', sex=1, img='wwwww', createTime=2020-05-20 12:54:42.0, role=1, username='?当家?
D', wechat='xdclass1'}
# 浏览器
http://localhost:8080/user?method=list
# 控制台
[User{id=1, phone='123', pwd='666', sex=1, img='xdclass.net', createTime=2021-09-09 08:00:00.0, role=1, username='jack', wechat='xdclass6'}, User{id=3, phone='2323432', pwd='xdclass-lw', sex=1, img='wwwww', createTime=2020-05-20 12:54:42.0, role=1, username='?当家?
D', wechat='xdclass1'}, User{id=4, phone='2323432', pwd='3232323', sex=1, img='wwwww', createTime=2020-05-20 12:55:07.0, role=1, username='?
王', wechat='xdclass-lw'}, User{id=5, phone='null', pwd='123456', sex=1, img='null', createTime=2022-04-15 14:43:30.0, role=2, username='二当家小D', wechat='null'}]
# 浏览器
http://localhost:8080/user?method=findByIdWithMap&id=1
# 控制台
{id=1, phone=123, pwd=666, sex=1, img=xdclass.net, create_time=2021-09-09 08:00:00.0, role=1, username=jack, wechat=xdclass6}
# 浏览器
http://localhost:8080/user?method=listWithMap
# 控制台
[{id=1, phone=123, pwd=666, sex=1, img=xdclass.net, create_time=2021-09-09 08:00:00.0, role=1, username=jack, wechat=xdclass6}, {id=3, phone=2323432, pwd=xdclass-lw, sex=1, img=wwwww, create_time=2020-05-20 12:54:42.0, role=1, username=?当家?
D, wechat=xdclass1}, {id=4, phone=2323432, pwd=3232323, sex=1, img=wwwww, create_time=2020-05-20 12:55:07.0, role=1, username=?
王, wechat=xdclass-lw}, {id=5, phone=null, pwd=123456, sex=1, img=null, create_time=2022-04-15 14:43:30.0, role=2, username=二当家小D, wechat=null}]
# 浏览器
http://localhost:8080/user?method=count
# 控制台
user表行数 = 4
# 浏览器
http://localhost:8080/user?method=count
# 控制台
user表行数 = 4
# 浏览器
http://localhost:8080/user?method=save
# 控制台
row=1
插入成功
- 其他操作
public int save(User user) throws Exception {
// 插入操作
// String sql = "insert into user (phone,pwd,sex,img,create_time,role,username,wechat) values(?,?,?,?,?,?,?,?)";
// Object [] params = {
// user.getPhone(),
// user.getPwd(),
// user.getSex(),
// user.getImg(),
// user.getCreateTime(),
// user.getRole(),
// user.getUsername(),
// user.getWechat()
// };
// 删除操作
// String sql = "delete from user where id= ?";
// Object [] params = { 1};
// 更新操作
String sql = "update user set phone= ?, pwd= ? where id= ?;";
Object [] params = {
user.getPhone(),
user.getPwd(),
user.getId()
};
// 影响行数
int i = 0;
try{
i = queryRunner.update(sql,params);
}catch (Exception e){
e.printStackTrace();
throw new Exception();
}
return i;
}
