一个简单的对用户信息进行CRUD操作封装的DAO类

DAO(Data Access Object):封装了数据访问逻辑的对象

准备工作:建一个Maven项目

    将下面的jar包导入项目,或者在pom.xml文件中拷贝下面的配置文件

                <dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.6</version>
		 </dependency>

首先先写一个实体类

package entity;

public class User {

	/*
	 * 为了存放从数据库中查询到的记录信息
	 * 我们可以设计一个对应的类,该类的结构与
	 * 要操作的表一致
	 */
	private int id;
	private String username;
	private String password;
	private String email;
	private String phone;
	

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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 getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email + ", phone="
				+ phone + "]";
	}
	
	
	}

CRUD操作,这是的访问数据库使用的是数据库连接池

package dao;
//这里导包注意导入sql的包
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 entity.User;
import util.DBUtils;


public class UserDAO {
	/*
	 * 验证用户名是否存在,这样查询到到用户的所有数据,方便后续的业务.
	 */
	public User findUsername(String username){
		Connection conn = null;
		PreparedStatement state = null;
		User u = null;
		try {
			conn = DBUtils.getConn();
			String sql = "select * from t_user where username=?";
			state = conn.prepareStatement(sql);
			state.setString(1,username);
			ResultSet rs = state.executeQuery();
			while (rs.next()) {
				u = new User();
				u.setId(rs.getInt(1));
				u.setUsername(rs.getString(2));
				u.setPassword(rs.getString(3));
				u.setPhone(rs.getString(4));
				u.setEmail(rs.getString(5));
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
//这里把异常抛出,出现数据库服务器傻了的时候,处理,使用的时候记得捕获异常,这里的抛出的异常不会提示,下面同理.
		} finally {
			try {
				DBUtils.release(conn, state, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return u;
	}
	/**
	 * 插入某个用户的信息
	 */
	public void save(User user){
		Connection conn = null;
		PreparedStatement state = null;
		try {
			conn = DBUtils.getConn();
			String sql = "insert into t_user values(null,?,?,?,?)";
			state = conn.prepareStatement(sql);
			state.setString(1,user.getUsername());
			state.setString(2,user.getPassword());;
			state.setString(3,user.getEmail());
			state.setString(4,user.getPhone());
			int row = state.executeUpdate();
			System.out.println(row + "行生效");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			try {
				DBUtils.release(conn, state, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 查询出所有员工的信息
	 * @return
	 */
	public List<User> findAll() {
		List<User> userlist = new ArrayList<User>();
		Connection conn = null;
		PreparedStatement state = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			String sql = "select * from t_user";
			state = conn.prepareStatement(sql);
			rs = state.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setPhone(rs.getString(5));
				userlist.add(user);
				
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			try {
				DBUtils.release(conn, state, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return userlist;
	}
	/*
	 * 通过id删除用户信息
	 */
	public void deleteById(int id){
		Connection conn = null;
		PreparedStatement state = null;
		try {
			conn = DBUtils.getConn();
			String sql = "delete from t_user where id = ?";
			state = conn.prepareStatement(sql);
			state.setInt(1,id);
			int row = state.executeUpdate();
			System.out.println(row + "行生效");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			try {
				DBUtils.release(conn, state, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/*
	 * 修改用户信息
	 */
	public void update(User user){
		Connection conn = null;
		PreparedStatement state = null;
		try {
			conn = DBUtils.getConn();
			String sql = "update t_user set username=?,password=?,email=?,phone=? where id=?";
			state = conn.prepareStatement(sql);
			state.setString(1,user.getUsername());
			state.setString(2,user.getPassword());
			state.setString(3,user.getEmail());
			state.setString(4,user.getPhone());
			state.setInt(5,user.getId());
			int row = state.executeUpdate();

			System.out.println(row + "行生效");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtils.release(conn, state, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
}