JDBC学习笔记 -- day04 使用jdcb实现CRUD操作

一、使用jdcb实现增删改查基本操作

1.创建java项目，并在根目录下创建jdcb.properties配置文件

2.创建DBUtil工具类

package com.jdcb.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DBUtil {

	/**
	 * 定义参数变量
	 */
	public static String driverClass;
	public static String url;
	public static String user;
	public static String password;

	/**
	 * 使用静态代码块加载properties文件获取信息并注册驱动
	 */
	static {
		try {
			ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 获取根目录下的jdbc.properties文件流
			driverClass = bundle.getString("driverClass");
			url = bundle.getString("url");
			user = bundle.getString("user");
			password = bundle.getString("password");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取Connection连接
	 * 
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		// 注册驱动
		Class.forName(driverClass);
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 释放资源
	 * 
	 * @param conn
	 *            连接
	 * @param stat
	 *            执行sql的对象
	 * @param rest
	 *            结果集
	 */
	public static void closeRes(Connection conn, Statement stat, ResultSet rest) {
		if (rest != null) {
			try {
				rest.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rest = null;
		}
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stat = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

3.创建User实体类

package com.jdcb.entity;

import java.util.Date;

public class User {

	private int id;
	private String name;
	private String password;
	private String email;
	private Date birthday;

	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 String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

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

}

4.编写测试方法

package com.jdcb.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

import com.jdcb.entity.User;
import com.jdcb.utils.DBUtil;

public class JdbcTest {

	/**
	 * 测试新增的方法
	 * 
	 * @throws Exception
	 */
	@Test
	public void testCreate() throws Exception {
		// 注册驱动，获取连接
		Connection connection = null;
		// 获取操作sql的Statement对象
		Statement statement = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			// 操作sql语句，并获取结果集
			String sql = "INSERT INTO users(NAME,PASSWORD,email,birthday) VALUES ('zl','123456','zl@qq.com','1991-09-21')";
			int i = statement.executeUpdate(sql);
			if (i > 0) {
				System.out.println("新增成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			DBUtil.closeRes(connection, statement, null);
		}
	}

	/**
	 * 测试查找的方法
	 * 
	 * @throws Exception
	 */
	@Test
	public void testRed() throws Exception {
		// 注册驱动，获取连接
		Connection connection = null;
		// 获取操作sql的Statement对象
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			// 操作sql语句，并获取结果集
			String sql = "SELECT * FROM users";
			resultSet = statement.executeQuery(sql);
			// 处理结果集
			while (resultSet.next()) {
				User user = new User();
				user.setId(resultSet.getInt("id"));
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("password"));
				user.setEmail(resultSet.getString("email"));
				user.setBirthday(resultSet.getDate("birthday"));
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			DBUtil.closeRes(connection, statement, resultSet);
		}
	}

	/**
	 * 测试update的方法
	 * 
	 * @throws Exception
	 */
	@Test
	public void testUpdate() throws Exception {
		// 注册驱动，获取连接
		Connection connection = null;
		// 获取操作sql的Statement对象
		Statement statement = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			// 操作sql语句，并获取结果集
			String sql = "UPDATE users SET PASSWORD='654321' WHERE NAME='zl'";
			int i = statement.executeUpdate(sql);
			// 处理结果集
			if (i > 0) {
				System.out.println("更新成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			DBUtil.closeRes(connection, statement, null);
		}
	}

	@Test
	public void testDelete() throws Exception {
		// 注册驱动，获取连接
		Connection connection = null;
		// 获取操作sql的Statement对象
		Statement statement = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			// 操作sql语句，并获取结果集
			String sql = "DELETE FROM users WHERE id='3'";
			int i = statement.executeUpdate(sql);
			// 处理结果集
			if (i > 0) {
				System.out.println("删除成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			DBUtil.closeRes(connection, statement, null);
		}
	}
}