在开发过程中数据库访问代码应和业务代码彻底分离
将数据层抽离出来,将数据访问的代码以对象的方式进行封装,便于业务层进行复用
数据访问对象称为DAO(DAO Data Access Object 数据访问对象)
DAO应根据数据表为单位进行独立的封装,如现在有Users表以及Dep表,在设计DAO时应封装UserDao和DepDao两个数据访问对象;UserDao中仅封装和Users表有关的数据访问方法,DepDao封装和dep有关的数据访问方法
DAO层属于项目结构中相对底层的调用,因此在业务层访问数据层时应确保DAO编写的准确性,每个DAO都应进行测试
测试分为白盒测试和黑盒测试
白盒测试:测试的过程是能看得到的
黑盒:只看结果运行是否通过,不看过程
导入单元测试的jar包:File——ProjectStructure——Libraries——点击加号——导入“C3P0”包
使用数据池(连接池)技术解决应用程序与数据库通信连接消耗资源过大的问题
常用的连接池:
c3p0、DBCP、Duird
导入连接池jar包:
<c3p0-config>
<default-config>
<!--配置连接池初始的连接数-->
<property name="initialPoolSize">10</property>
<!--配置最大的连接数-->
<property name="maxPoolSize">25</property>
<!--配置最小的连接数-->
<property name="minPoolSize">5</property>
<!--配置驱动-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!--配置URL-->
<property name="jdbcUrl">jdbc:mysql:\\localhost:3306/bbs_db</property>
<!--配置连接数据库的用户名-->
<property name="user">root</property>
<!--配置密码-->
<property name="password">tjhilu</property>
</default-config>
</c3p0-config>
创建和数据的连接有两种方法:
1.使用静态块加载驱动
Class.forName("com.mysql.jdbc.Driver"),
在获取连接的方法中通过
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/geekhome", "root", "root")
获取连接对象
2.使用数据池进行加载数据
/**
* 连接池的使用
*/
public class Demo8 {
public static void main(String[] args) {
try {
//创建连接池对象
DataSource dataSource = new ComboPooledDataSource();
//从连接池中获取连接对象
Connection con = dataSource.getConnection();
System.out.println(con);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
将数据层和业务层分离出来,需要创建一个Dao包将数据类都封装起来
数据库连接的类:
/**
* 封装了数据库连接和关闭的工具类
*/
public class DaoUtil {
//使用静态块加载驱动
// static{
// try {
// Class.forName("com.mysql.jdbc.Driver");
// } catch (ClassNotFoundException e) {
// e.printStackTrace();
// }
// }
//数据池
private static DataSource dataSource;
static{
//初始化连接池
dataSource = new ComboPooledDataSource();
}
/**
* 获得连接
* @return
*/
public static Connection openConnection(){
Connection con = null;
// try {
// con = DriverManager.getConnection("jdbc:mysql://localhost:3306/geekhome", "root", "root");
// } catch (Exception e) {
// e.printStackTrace();
// }
//通过连接池获取连接对象
if(dataSource != null){
try {
con = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
return con;
}
/**
* 关闭数据库对象:判断只要不为null就要关闭
* @param con
* @param pstmt
* @param rs
*/
public static void close(Connection con, PreparedStatement pstmt, ResultSet rs){
try {
if(rs != null){
rs.close();
}
if(pstmt != null){
pstmt.close();
}
if(con != null){
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
用户类的Dao包:
将业务中需要对数据进行的操作方法写在数据类中,业务层中直接调用方法得到返回值即可
package com.igeek.dao;
import com.igeek.pojo.User;
import java.sql.*;
import java.util.ArrayList;
/**
* 封装users表相关的数据访问操作
*/
public class UserDao {
/**
* 删除用户
* @param userId
*/
public void delete(int userId){
//打开数据库连接
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
if(con != null){
try {
//创建处理器
pstmt = con.prepareStatement("delete from users where userid=?");
//注入参数
pstmt.setInt(1, userId);
//执行删除
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally{
//关闭数据库对象
DaoUtil.close(con, pstmt, null);
}
}
}
/**
* 添加用户数据
* @param user
*/
public void addUser(User user){
//打开数据库连接
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
if(con != null){
try {
pstmt = con.prepareStatement("insert into users values(null,?,?,null,null,null,null,?)");
//注入参数
pstmt.setString(1, user.getUserName());
//pstmt.setTimestamp(2, new Timestamp(user.getBirthday().getTime()));
pstmt.setTimestamp(2, null);
pstmt.setString(3, user.getPassword());
//执行新增
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DaoUtil.close(con, pstmt, null);
}
}
}
/**
* 修改用户
* 根据用户的编号进行修改,修改用户的密码和生日
* @param user
*/
public void update(User user){
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
if(con != null){
try {
pstmt = con.prepareStatement("update users set password=?,birthday=? where userid=?");
//注入参数
pstmt.setString(1, user.getPassword());
pstmt.setDate(2, new Date(user.getBirthday().getTime()));
pstmt.setInt(3, user.getUserId());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DaoUtil.close(con, pstmt, null);
}
}
}
/**
* 根据用户编号查询
* @param userId
* @return
*/
public User findById(int userId){
User user = null;
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
if(con != null){
try {
pstmt = con.prepareStatement("select userid,username,birthday,password from users where " +
"userid=? ");
pstmt.setInt(1, userId);
//执行查询
rs = pstmt.executeQuery();
while(rs.next()){
user = new User();
user.setUserId(rs.getInt(1));
user.setUserName(rs.getString(2));
user.setBirthday(rs.getTimestamp(3));
user.setPassword(rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DaoUtil.close(con, pstmt, rs);
}
}
return user;
}
/**
* 查找users表的所有数据:返回一个user集合
*/
public ArrayList<User> findAll(){
ArrayList<User> list = new ArrayList<>();
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
if(con != null){
try {
pstmt = con.prepareStatement("select userid,username,password,birthday from users");
rs = pstmt.executeQuery();
while(rs.next()){
//创建User对象
User user = new User();
//封装列值至user对象
user.setUserId(rs.getInt(1));
user.setUserName(rs.getString(2));
user.setPassword(rs.getString(3));
user.setBirthday(rs.getDate(4));
//将user对象添加至集合保存
list.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DaoUtil.close(con, pstmt, rs);
}
}
return list;
}
/**
* 查找users表中的所有用户:返回一个user集合
* 按照页面进行查找
*pstmt = con.prepareStatement("select userid,username,password,birthday " +
"from users limit ?,3");//第一个参数表示从什么位置开始,第二个参数表示每页显示多少条内容
*pstmt.setInt(1, (page-1)*3); //注入占位符处的参数
*/
public ArrayList<User> findByPage(int page){
//要先实例化,不然要是找到以后在实例化,那么每次都会重新new
ArrayList<User> list = new ArrayList<>();
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
if(con != null){
try {
pstmt = con.prepareStatement("select userid,username,password,birthday " +
"from users limit ?,3");
pstmt.setInt(1, (page-1)*3);
rs = pstmt.executeQuery();
while(rs.next()){
//创建User对象
User user = new User();
//封装列值至user对象
user.setUserId(rs.getInt(1));
user.setUserName(rs.getString(2));
user.setPassword(rs.getString(3));
user.setBirthday(rs.getDate(4));
//将user对象添加至集合保存
list.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DaoUtil.close(con, pstmt, rs);
}
}
return list;
}
/**
* 根据用户名查询
* @param userName
* @return
*/
public User findByName(String userName){
User user = null;
Connection con = DaoUtil.openConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
if(con != null){
try {
pstmt = con.prepareStatement("select 1userid,username,birthday,password from users where " +
"userName=? ");
pstmt.setString(1, userName);
//执行查询
rs = pstmt.executeQuery();
while(rs.next()){
user = new User();
user.setUserId(rs.getInt(1));
user.setUserName(rs.getString(2));
user.setBirthday(rs.getTimestamp(3));
user.setPassword(rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DaoUtil.close(con, pstmt, rs);
}
}
return user;
}
}
user类:
package com.igeek.pojo;
import java.util.Date;
/**
* users表的映射类,用于封装users表的数据行
*/
public class User {
private int userId;
private String userName;
private String password;
private Date birthday;
public User() {
}
public User(String userName, String password, Date birthday) {
this.userName = userName;
this.password = password;
this.birthday = birthday;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
业务类:
package com.igeek;
import com.igeek.dao.UserDao;
import com.igeek.pojo.User;
import java.util.Scanner;
/**
* 在业务中使用DAO实现数据访问
*/
public class Demo7 {
private UserDao userDao;
private Scanner sc;
public Demo7() {
userDao = new UserDao();
sc = new Scanner(System.in);
}
public void start(){
System.out.println("1. 登录");
System.out.println("2. 注册");
System.out.println("请选择:");
int chooice = sc.nextInt();
switch(chooice){
case 1:
login();
break;
case 2:
regist();
break;
}
}
/**
* 登录
*/
public void login(){
System.out.println("请输入用户名:");
String userName = sc.next();
System.out.println("请输入密码:");
String password = sc.next();
//根据用户名查找
User user = userDao.findByName(userName);
//判断用户是否找到
if(user != null){
//判断密码
if(password.equals(user.getPassword())){
System.out.println("登录成功!");
}
else{
System.out.println("密码错误!");
login();
}
}
else{
System.out.println("用户名不存在!");
login();
}
}
/**
* 注册
*/
public void regist(){
System.out.println("请输入用户名:");
String userName = sc.next();
System.out.println("请输入密码:");
String password = sc.next();
//查询用户名是否被注册
User user = userDao.findByName(userName);
if(user == null){
//创建要注册的用户对象
User registUser = new User();
registUser.setUserName(userName);
registUser.setPassword(password);
userDao.addUser(registUser);
System.out.println("注册成功!");
start();
}
else{
System.out.println("用户名已被注册!");
regist();
}
}
public static void main(String[] args) {
Demo7 demo = new Demo7();
demo.start();
}
}