JDBC中--保存--方法详解

最新推荐文章于 2021-02-24 02:56:49 发布

荒--

最新推荐文章于 2021-02-24 02:56:49 发布

阅读量2.8k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： JDBC

本文链接：https://blog.youkuaiyun.com/wangqing84411433/article/details/69388214

JDBC 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个简单的Java用户管理系统，包括实体类、DAO层及测试方法。系统实现了用户信息的增删改查等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实体类

package day03;
/**
 * 实体类
 * 用于表示数据库中表user
 */
public class User {
	private int id;
	private String name;
	private String password;
	private int money;
	private String email;
	private int deptno;
	
	public User() {
	}
	
	public User(int id, String name, String password, int money, String email, int deptno) {
		super();
		this.id = id;
		this.name = name;
		this.password = password;
		this.money = money;
		this.email = email;
		this.deptno = deptno;
	}

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getMoney() {
		return money;
	}
	public void setMoney(int money) {
		this.money = money;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password + ", money=" + money + ", email=" + email
				+ ", deptno=" + deptno + "]";
	}
	
}

DAO

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import day01.DBUtil2;

/**
 * UserDAO
 * 用于操作数据库User表的DAO
 */
public class UserDAO {
	private static final String FIND_BY_SQL =
			"SELECT * FROM user_wq WHERE id=?";
	private static final String FIND_ALL_SQL = 
			"SELECT * FROM user_wq";
	private static final String SAVE_SQL = 
			"INSERT INTO user_wq VALUES (user_seq.nextval,?,?,?,?,?)";
	private static final String UPDATE_SQL = 
			"UPDATE user_wq SET name=?,password=?,money=?,email=?,deptno=? WHERE id=?";
	private static final String DELETE_SQL =
			"DELETE FROM user_wq WHERE id=?";
	
	//根据id查询对应的User记录
	public User findById(int id){
		try {
			Connection conn = DBUtil2.getConnection();
			PreparedStatement ps = conn.prepareStatement(FIND_BY_SQL);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				int i = rs.getInt("id");
				String name = rs.getString("name");
				String password = rs.getString("password");
				int money = rs.getInt("money");
				String email = rs.getString("email");
				int deptno = rs.getInt("deptno");
				User user = new User(i,name,password,money,email,deptno);
				return user;
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil2.closeConnection();
		}
		return null;
	}
	
	//查询所有的User记录
	public List<User> findAll(){
		try {
			Connection conn = DBUtil2.getConnection();
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery(FIND_ALL_SQL);
			List<User> list = new ArrayList<User>();
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String password = rs.getString("password");
				int money = rs.getInt("money");
				String email = rs.getString("email");
				int deptno = rs.getInt("deptno");
				User user = new User(id,name,password,money,email,deptno);
				list.add(user);
			}
			state.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil2.closeConnection();
		}
		return null;
	}
	
	//保存一个User信息
	public boolean save(User user){
		try {
			Connection conn = DBUtil2.getConnection();
			PreparedStatement ps = conn.prepareStatement(SAVE_SQL,new String[]{"id"});
			ps.setString(1, user.getName());
			ps.setString(2, user.getPassword());
			ps.setInt(3, user.getMoney());
			ps.setString(4, user.getEmail());
			ps.setInt(5, user.getDeptno());
			//User实体类中的id是由数据库序列生成的,在插入时候,并不知道
			//id的值,等插入完成后,返回该id再从新对User中的id赋值
			if(ps.executeUpdate() > 0){
				//插入成功后获取主键的值
				ResultSet rs = ps.getGeneratedKeys();
				rs.next();
				int id = rs.getInt(1);
				user.setId(id);
				ps.close();
				return true;
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil2.closeConnection();
		}
		return false;
	}
	
	//更新一个User信息
	public boolean update(User user){//已经知道该user的id,所以不用返回id
		try {
			Connection conn = DBUtil2.getConnection();
			PreparedStatement ps = conn.prepareStatement(UPDATE_SQL);
			ps.setString(1, user.getName());
			ps.setString(2, user.getPassword());
			ps.setInt(3, user.getMoney());
			ps.setString(4, user.getEmail());
			ps.setInt(5, user.getDeptno());
			ps.setInt(6, user.getId());
			if(ps.executeUpdate() > 0){
				//若更新成功
				ps.close();
				return true;
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil2.closeConnection();
		}
		return false;
	}
	
	//根据id删除一个User信息
	public boolean deleteById(int id){
		try {
			Connection conn = DBUtil2.getConnection();
			PreparedStatement ps = conn.prepareStatement(DELETE_SQL);
			ps.setInt(1, id);
			if(ps.executeUpdate() > 0){
				//删除成功
				ps.close();
				return true;
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil2.closeConnection();
		}
		return false;
	}
}

测试类

package day03;

import java.util.List;
import org.junit.Test;
public class MyUserService {
	@Test//根据id查询对应的User记录
	public void test1(){
		UserDAO dao = new UserDAO();
		User user = dao.findById(2);
		if(user != null){
			System.out.println("欢迎你:"+user.getName());
		}else{
			System.out.println("没有这个人");
		}
	}

	@Test//查询所有的User记录
	public void test2(){
		UserDAO dao = new UserDAO();
		List<User> list = dao.findAll();
		for(User s : list){
			System.out.println(s.getName());
		}
	}
	
	@Test//保存一个User信息
	public void test3(){
		User user = new User();
		user.setName("LUCY");
		user.setPassword("123456");
		user.setMoney(2500);
		user.setEmail("LUCY@qq.com");
		user.setDeptno(10);
		//0 此处可以不用设置id,通过数据库生成
		System.out.println("插入前id:"+user.getId());
		UserDAO dao = new UserDAO();
		System.out.println(user);
		boolean flag = dao.save(user);
		if(flag){
			System.out.println("保存成功");
			//保存成功后User对象中的id就有了值
			System.out.println("插入后id:"+user.getId());
		}else{
			System.out.println("保存失败");
		}
	}
	
	@Test//更新一个User信息
	public void test4(){
		User user = new User();
		user.setId(1002);
		user.setName("蛋蛋");
		user.setPassword("99999");
		user.setEmail("蛋蛋@qq.com");
		user.setMoney(3999);
		user.setDeptno(15);
		UserDAO dao = new UserDAO();
		boolean flag = dao.update(user);
		if(flag){
			System.out.println("成功");
		}else{
			System.out.println("失败");
		}
	}
	
	@Test//根据id删除一个User信息
	public void test5(){
		int id = 1001;//要删除记录的id
		UserDAO dao = new UserDAO();
		boolean flag = dao.deleteById(id);
		if(flag){
			System.out.println("成功");
		}else{
			System.out.println("失败");
		}
	}
}

对于保存方法的测试结果如下：

插入前id:0
User [id=0, name=LUCY1, password=321, money=2500, email=LUCY1@qq.com, deptno=12]
保存成功
插入后id:1030