一:相应jar包介绍
1.1 druid 数据源
Druid连接池是阿里巴巴开源的数据库连接池项目,后来贡献给Apache开源; Druid的作用是负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个; Druid连接池内置强大的监控功能,其中的StatFilter功能,能采集非常完备的连接池执行信息,方便进行监控,而监控特性不影响性能。 Druid连接池内置了一个监控页面,提供了非常完备的监控信息,可以快速诊断系统的瓶颈。
1.2 dbutils 数据库工具类
DBUtils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用DBUtils能极大简化JDBC编码的工作量,同时也不会影响程序的性能。
1.3 如何使用以及示例代码
新建druid.properties 编写好自己的连接信息
# 连接地址
url=jdbc:mysql://localhost:3306/atm?useSSL=false
#密码
password=root
#用户名
username=root
创建连接池工具类DruidUtils
/**
* Druid DataSource
*/
public class DruidUtils {
private static DataSource ds;
static {
InputStream in = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
Properties properties = new Properties();
try {
properties.load(in);
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try{
return ds.getConnection();
}catch (Exception e){
e.printStackTrace();
}
return null;
}
public static DataSource getDataSource(){
return ds;
}
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
如何使用dbutil
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
1 查询操作
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = this.prepareConnection();return this.query(conn, true, sql, rsh, params);}
查询的三种方式
queryRunner.query("",new BeanHandler<>(User.class)); 返回对象
queryRunner.query("",new BeanListHandler<>(User.class)); 返回集合
queryRunner.query("", new ScalarHandler<>());返回数字
public static void test01(){
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
try {
User query = queryRunner.query("select * from user where icno = ?", new BeanHandler<>(User.class), "6225123456788");
System.out.println("query = " + query);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void test02(){
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
try {
List<User> userList = queryRunner.query("select * from user", new BeanListHandler<>(User.class));
System.out.println("userList = " + userList);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void test03(){
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
try {
Object query = queryRunner.query("select count(*) from user", new ScalarHandler<>());
System.out.println("query = " + query);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
二:项目中使用
2.1原始代码查看
/**
* 管理员登录
* @param name 名字
* @param password 密码
* @return 管理员
*/
public Manager findManager(String name,String password) {
Connection connection = DBUtils.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Manager manager=null;
try {
preparedStatement = connection.prepareStatement("select * from manage where name = ? and password =?");
preparedStatement.setString(1,name);
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
manager= new Manager();
manager.setId(resultSet.getInt(1));
manager.setName(resultSet.getString(2));
manager.setPassword(resultSet.getString(3));
}
return manager;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
DBUtils.release(connection,preparedStatement,resultSet);
}
}
优化后的代码
/**
* 管理员登录
* @param name 名字
* @param password 密码
* @return 管理员
*/
public Manager findManager(String name,String password) {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "select * from manage where name = ? and password =?";
try {
return queryRunner.query(sql,new BeanHandler<>(Manager.class),name,password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
代码变得非常简洁了
同理我们对dao的其他类进行操作
public class RunMessageDao {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
/**
* 添加账单信息
* @param runMessage 账单信息
*/
public void addRunMessage(RunMessage runMessage) {
String sql ="insert into runmessage values(null,?,?,?,?,?)";
try {
queryRunner.update(sql,runMessage.getIcno(),
runMessage.getRm_date(),
runMessage.getRm_currency(),
runMessage.getRm_balance()
,runMessage.getRm_rest());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 账户记录
* @param icno 卡号
* @return 账户记录
*/
public List<RunMessage> findAllDetails(String icno) {
String sql ="select * from runmessage where icno = ? ";
try {
List<RunMessage> runMessageList = queryRunner.query(sql, new BeanListHandler<>(RunMessage.class), icno);
return runMessageList;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 查询分页数据
* @param page 页码
* @param pageSize 页数大小
* @return
*/
public List<RunMessage> findRunMessageByPage(String icno,int page, int pageSize) {
int startIndex=(page-1)*(pageSize);
String sql = "select * from runmessage where icno = ? limit ?,? ";
try {
List<RunMessage> runMessageList = queryRunner.query(sql, new BeanListHandler<>(RunMessage.class), icno, startIndex, pageSize);
return runMessageList;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
package com.atm.dao;
import com.atm.pojo.User;
import com.atm.utill.DBUtils;
import com.atm.utill.DruidUtils;
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 java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
private QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
/**
* 查找所有用户
* @return 所有用户信息
*/
public List<User> findAllUsers() {
try {
return queryRunner.query("select * from user", new BeanListHandler<>(User.class));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 模糊查询 用户信息
* @param name 模糊查询名子
* @return 模糊查询结果
*/
public List<User> queryUsers(String name) {
String sql ="select * from user where name like ?";
try {
return queryRunner.query(sql, new BeanListHandler<>(User.class), "%" + name + "%");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 通过icno 查询
* @param icno 卡号
* @return 用户信息
*/
public User getUserByIcno(String icno) {
String sql ="select * from user where icno = ?";
try {
return queryRunner.query(sql, new BeanHandler<>(User.class), icno);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 更新用户信息
* @param user 用户信息
* @return result
*/
public int updateUser(User user) {
String sql ="update user set name = ?, pwd = ? ,mobile = ?, idcard = ?, balance = ? where icno = ?";
try {
return queryRunner.update(sql, user.getName(), user.getPwd(), user.getMobile(), user.getIdcard(), user.getBalance(), user.getIcno());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 根据icno删除用户
* @param icno 卡号
* @return reasult
*/
public int deleteUser(String icno) {
String sql ="delete from user where icno =?";
try {
return queryRunner.update(sql, icno);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 添加用户信息
* @param user 用户信息
* @return result
*/
public int addUser(User user) {
String sql ="insert into user values(?,?,?,?,?,?)";
try {
return queryRunner.update(sql,
user.getIcno(),
user.getName(),
user.getPwd(),
user.getMobile(),
user.getIdcard(),
user.getBalance());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 根据卡号和密码查询用户信息
* @param icno 卡号
* @param pwd 密码
* @return 用户信息
*/
public User findUserByNameAndPwd(String icno, String pwd) {
String sql ="select * from user where icno = ? and pwd = ?";
try {
return queryRunner.query(sql, new BeanHandler<>(User.class), icno, pwd);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 更新用户信息
* @param icno 卡号
* @param newpwd 新密码
* @return result
*/
public int updateUserPwd(String icno,String newpwd) {
String sql ="update user set pwd = ? where icno=?";
try {
return queryRunner.update(sql, icno, newpwd);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 根据卡号获取资金
* @param icno 卡号
* @return 资金
*/
public double getUserBalance(String icno) {
String sql ="select balance from user where icno=?";
try {
Double query = queryRunner.query(sql, new ScalarHandler<>(), icno);
return query;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 存钱
* @param money 金额
* @param icno 卡号
* @return result
*/
public int saveMoney(int money,String icno) {
String sql ="update user set balance = balance + ? where icno = ?";
try {
return queryRunner.update(sql,money,icno);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 取钱
* @param money 金额
* @param icno 卡号
* @return result
*/
public int fetchMoney(int money, String icno) {
String sql ="update user set balance = balance - ? where icno = ?";
try {
return queryRunner.update(sql, money, icno);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
优化后的代码明显简洁美观,增强了代码的可读性
三:页面效果
基本效果都实现了