使用JDBC完成数据的增删改查

一、创建javabean：User

public class User {
	private int id;
	private String username;
	private String password;
	private String email;
	private Date birthday;
//补充getter setter

二、创建操作数据库的工具类

public class JDBCUtils {
	// 加载驱动，并建立数据库连接
	public static Connection getConnection() throws SQLException,
				ClassNotFoundException {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/jdbc";
			String username = "root";
	    		String password = "root";
			Connection conn = DriverManager.getConnection(url, username, 
					password);
			return conn;
		}
		// 关闭数据库连接，释放资源
		public static void release(Statement stmt, Connection conn) {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	     public static void release(ResultSet rs, Statement stmt, 
	     		Connection conn){
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			release(stmt, conn);
		}
}

三、创建UsersDao

public class UsersDao {
	// 添加用户的操作
	public boolean insert(User user) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		// 获得数据的连接
		conn = JDBCUtils.getConnection();
		// 获得Statement对象
		stmt = conn.createStatement();
		// 发送SQL语句
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String birthday = sdf.format(user.getBirthday());
	    String sql = "INSERT INTO users(id,username,password,email,birthday) "+
					"VALUES("
					+ user.getId()
					+ ",'"
					+ user.getUsername()
					+ "','"
					+ user.getPassword()
					+ "','"
					+ user.getEmail()
					+ "','"
					+ birthday + "')";
			int num = stmt.executeUpdate(sql);
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs, stmt, conn);
		}
		return false;
	}
	// 查询所有的User对象
	public ArrayList<User> findAll() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<User> list = new ArrayList<User>();
		try {
			// 获得数据的连接
			conn = JDBCUtils.getConnection();
			// 获得Statement对象
			stmt = conn.createStatement();
			// 发送SQL语句
			String sql = "SELECT * FROM users";
			rs = stmt.executeQuery(sql);
			// 处理结果集
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				list.add(user);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs, stmt, conn);
		}
		return null;
	}
	// 根据id查找指定的user
	public User find(int id) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 获得数据的连接
			conn = JDBCUtils.getConnection();
			// 获得Statement对象
			stmt = conn.createStatement();
			// 发送SQL语句
			String sql = "SELECT * FROM users WHERE id=" + id;
			rs = stmt.executeQuery(sql);
			// 处理结果集
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
				}
				return null;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return null;
		}
		// 删除用户
		public boolean delete(int id) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				// 获得数据的连接
				conn = JDBCUtils.getConnection();
				// 获得Statement对象
				stmt = conn.createStatement();
				// 发送SQL语句
				String sql = "DELETE FROM users WHERE id=" + id;
				int num = stmt.executeUpdate(sql);
				if (num > 0) {
					return true;
				}
				return false;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return false;
		}
		// 修改用户
		public boolean update(User user) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				// 获得数据的连接
				conn = JDBCUtils.getConnection();
				// 获得Statement对象
				stmt = conn.createStatement();
				// 发送SQL语句
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String birthday = sdf.format(user.getBirthday());
				String sql = "UPDATE users set username='" + user.getUsername()
						+ "',password='" + user.getPassword() + "',email='"
						+ user.getEmail() + "',birthday='" + birthday
						+ "' WHERE id=" + user.getId();
				int num = stmt.executeUpdate(sql);
				if (num > 0) {
					return true;
				}
				return false;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtils.release(rs, stmt, conn);
			}
			return false;
		}
	}

四、创建测试类(使用junit5)

public class UsersDaoTest {
	private UsersDao ud;

	@BeforeEach
	void init() {
		ud = new UsersDao();
	}

	@Test
	void insertTest() {
		// 向users表插入一个用户信息
		User user = new User();

		user.setUsername("hl");
		user.setPassword("123");
		user.setEmail("hl@sina.com");
		user.setBirthday(new Date());
		boolean b = ud.insert(user);
		System.out.println(b);
	}

	@Test
	void findAllUsersTest() {

		// 将UsersDao对象的findAll()方法执行后的结果放入list集合
		ArrayList<User> list = ud.findAll();
		// 循环输出集合中的数据
		for (int i = 0; i < list.size(); i++) {
			System.out.println("第" + (i + 1) + "条数据的username值为:" + list.get(i).getUsername());
		}
	}
	
	@Test
	void findUserByIdTest() {
		User user = ud.find(1);
		System.out.println("id为1的User对象的name值为：" + user.getUsername());
	}

	@Test
	void updateUserTest() {
		User user = new User();
		user.setId(4);
		user.setUsername("zhaoxiaoliu");
		user.setPassword("456");
		user.setEmail("zhaoxiaoliu@sina.com");
		user.setBirthday(new Date());
		boolean b = ud.update(user);
		System.out.println(b);
	}
	
	@Test
	void deleteUserTest() {
		boolean b = ud.delete(4);
 		System.out.println(b);
	}
}