* mvc:三层架构思想
*
* view:视图层
* 展示页面效果(SE阶段:测试类,EE阶段:jsp,html展示效果)
* model:业务层的数据的数据处理
* web层以及service(业务接口层/业务实现层)
* controller:控制层
* 调用dao层来去连接数据库,操作数据库
* 增删查改!返回给用户信息
*
* 代码架构:
* xx.xx.entity
* 实体类的属性---需要数据库表中字段对应(映射关系)
* xx.xx.service
* 业务接口层
* xx.xx.service.impl
* 业务接口实现层
* xx.xx.web
* 后台处理,调用层
*
* 需求:
* 有一个用户:User类----- > 某个数据库中:user表
* 用户id:userId userId
* 用户姓名:username username
* 用户密码:password passwoed
* 用户名地址:address address
* 电话:phone phone
*
* 对用户完成增删查改操作,使用JDBC
* 按照上面的格式:
*
* 分析:
* 1)创建一个数据库,创建一张表:user
* 2)对user类的属性产生一个映射关系(符合javaBean规范)
* 3)业务接口层
* 4)业务接口实现层
* 5)数据库访问层
* 6)数据库访问实现层
package com.lin.test;
import java.util.List;
import org.junit.Test;
import com.lin.entity.User;
import com.lin.service.UserService;
import com.lin.service.impl.UserServiceImpl;
public class UserTest {
private UserService userService = new UserServiceImpl() ;
@Test
public void testAdd() {
//创建User对象
User user = new User() ;
user.setUsername("张三");
user.setPassword("2222");
user.setAddress("甘肃");
user.setPhone("13688889999");
//需要调用service层
//调用add功能
int count = userService.add(user) ;
System.out.println("影响了"+count+"行");
}
@Test
public void testUpdate() {
User user = new User() ;
user.setUserId(4);
user.setUsername("李四");
user.setPassword("123456");
user.setAddress("北京");
user.setPhone("13888888888");
int count = userService.update(user) ;
System.out.println(count);
}
@Test
public void testDelete() {
User user = new User() ;
user.setUserId(6);
int count = userService.delete(user.getUserId()) ;
System.out.println(count);
//请求转发.重定向到指定jsp/html页面中
}
@Test
public void testFindAll() {
List<User> list = userService.selectAll() ;
for(User user :list) {
System.out.println(user);//执行实体类中toString()
}
}
//测试:通过用户id查询用户信息
@Test
public void testFindById() {
User user2 = userService.find(3) ;
System.out.println(user2);
if(user2 !=null) {
System.out.println(user2.getUserId()+"---"+user2.getUsername()+"---"+user2.getPassword()
+"---"+user2.getAddress()+"---"+user2.getPhone());
}
}
}
package com.lin.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.lin.dao.UserDao;
import com.lin.entity.User;
import com.lin.utils.JdbcUtils;
public class UserDaoImpl implements UserDao {
private Connection conn;
private PreparedStatement stmt;
private ResultSet rs;
@Override
public int add(User user) {
try {
// 获取连接
conn = JdbcUtils.getConnection();
// 准备sql
String sql = "insert into user(username,password,address,phone)values(?,?,?,?);";
// 编译sql
stmt = conn.prepareStatement(sql);
// 给参数赋值
stmt.setString(1,user.getUsername());
stmt.setString(2,user.getPassword());
stmt.setString(3,user.getAddress());
stmt.setString(4,user.getPhone());
// PreparedStatement对象中执行sql
return stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
@Override
public int updata(User user) {
try {
// 获取连接对象
conn = JdbcUtils.getConnection();
// 准备sql
String sql = "updata user set username=?,set username=?,password=?,address=?,phone=? where userId=?;";
// 预编译sql
stmt = conn.prepareStatement(sql);
//设置参数
stmt.setString(1,user.getUsername());
stmt.setString(2,user.getPassword());
stmt.setString(3,user.getAddress());
stmt.setString(4,user.getPhone());
stmt.setInt(5,user.getUserId());
// 执行sql
return stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
// 根据用户id删除用户信息
@Override
public int delete(int id) {
try {
conn=JdbcUtils.getConnection();
String sql="delete from user where id=?;";
stmt=conn.prepareStatement(sql);
//设置参数
stmt.setInt(1,id);
//执行
return stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
@Override
public List<User> selectAll() {
//构造一个空的ArrayList
ArrayList <User>list;
try {
list=new ArrayList();
conn=JdbcUtils.getConnection();
String sql="select * from user;";
stmt=conn.prepareStatement(sql);
// 执行sql
rs=stmt.executeQuery();
User user=null;
// 遍历集合结果,封装到User对象中
while(rs.next()) {
// 通过列的名称获取
int userId=rs.getInt("userId");
String username=rs.getString("username");
String password=rs.getString("password");
String address=rs.getString("address");
String phone=rs.getString("phone");
user=new User();
user.setUserId(userId);
user.setUsername(username);
user.setPassword(password);
user.setAddress(address);
user.setPhone(phone);
//添加到集合中
list.add(user);
return list;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.close(rs, stmt, conn);
}
return null;
}
//通过id查询用户
@Override
public User findById(int id) {
try {
User user=new User();
conn=JdbcUtils.getConnection();
String sql="select * from user where useeId=?; ";
// 编译sql
stmt=conn.prepareStatement(sql);
//设置参数
stmt.setInt(1,id);
// 执行查询
rs=stmt.executeQuery();
while(rs.next()) {
int userId=rs.getInt("userId");
String username=rs.getString("username");
String password=rs.getString("password");
String address=rs.getString("address");
String phone=rs.getString("phone");
// 封装User对象
user.setUserId(userId);
user.setUsername(username);
user.setPassword(password);
user.setAddress(address);
user.setPhone(phone);
return user;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.close(rs, stmt, conn);
}
return null;
}
}
package com.lin.dao;
import java.util.List;
import com.lin.entity.User;
/**
* 持久层(用户数据访问接口层)
* @author wl
* */
public interface UserDao {
// 添加用户信息
public abstract int add(User user);
// 更新操作
public abstract int updata(User user);
// 删除:通过编号删除用户
public abstract int delete(int id);
// 查询所有
public abstract List<User> selectAll();
// 通过用户id查询用户
public abstract User findById(int id);
}
package com.lin.service.impl;
import java.util.List;
/**
* 用户的业务接口实现层
* 调用dao层完成增删查改业务
* @author wl
* */
import com.lin.dao.UserDao;
import com.lin.dao.impl.UserDaoImpl;
import com.lin.entity.User;
import com.lin.service.UserService;
public class UserServiceImpl implements UserService {
// 在创建一个实例:持久层对象dao层
private UserDao ud=new UserDaoImpl();
@Override
public int add(User user) {
return ud.add(user);
}
@Override
public int update(User user) {
return ud.updata(user);
}
@Override
public int delete(int id) {
return ud.delete(id);
}
@Override
public List<User> selectAll() {
return ud.selectAll();
}
@Override
public User find(int id) {
return ud.findById(id);
}
@Override
public long findCount() {
return 0;
}
}
package com.lin.service;
import java.util.List;
import com.lin.entity.User;
/*
* 针对用户操作的业务接口层
* */
public interface UserService {
//添加用户信息
public abstract int add(User user);
//更新操作
public abstract int update( User user);
//删除:通过编号删除用户
public abstract int delete(int id);
//查询所有
public abstract List<User> selectAll();
//通过用户id查询所有
public abstract User find(int id);
// 查询user表中记录
public abstract long findCount();
}
package com.lin.entity;
public class User {
//对应user表中的字段
private int userId;
private String username;
private String password;
private String address;
private String phone;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(int userId, String username, String password, String address, String phone) {
super();
this.userId = userId;
this.username = username;
this.password = password;
this.address = address;
this.phone = phone;
}
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 String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "User [userId=" + userId + ", username=" + username + ", password=" + password + ", address=" + address
+ ", phone=" + phone + ", getClass()=" + getClass() + ", hashCode()=" + hashCode() + ", toString()="
+ super.toString() + "]";
}
}