java之JDBC的增删改查

JDBC的增删改查

//实体类

//实体类
public class Product {
	//创建产品表：包含：ID，名称，类型，价格，描述
	private int id;//ID
	private String name;//名称
	private String type;//类型
	private int price;//价格
	private String descs;//描述
	public Product() {
		super();
	}
	public Product(int id, String name, String type, int price, String descs) {
		super();
		this.id = id;
		this.name = name;
		this.type = type;
		this.price = price;
		this.descs = descs;
	}
	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 getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getDescs() {
		return descs;
	}
	public void setDescs(String descs) {
		this.descs = descs;
	}
	@Override
	public String toString() {
		return "Product [id=" + id + ", name=" + name + ", type=" + type + ", price=" + price + ", descs=" + descs
				+ "]";
	}
}

//工具类

//工具类
public class DBManager {
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		// 1.加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		// 2. 获得连接, 三个参数分别为：url，用户名，密码
		return DriverManager
				.getConnection("jdbc:mysql://localhost:3306/j1904?useUnicode=true&characterEncoding=utf-8", 
				"root", "root");
	}
	
	// 先打开，后关闭
	public static void closeAll(Connection connection, PreparedStatement statement) {
		try {
			if(statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	// 先打开，后关闭
	public static void closeAll(Connection connection, PreparedStatement statement, ResultSet resultSet) {
		try {
			if(resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closeAll(connection, statement);
		}
	}
}

//数据库操作类

//数据库操作类
public class ProductDAO {
	public static boolean insert(Product pro) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = DBManager.getConnection();
			String sql = "INSERT INTO product(name, type, price, descs) VALUES (?, ?, ?, ?);";
			statement = connection.prepareStatement(sql);
			statement.setString(1, pro.getName());
			statement.setString(2, pro.getType());
			statement.setInt(3, pro.getPrice());
			statement.setString(4, pro.getDescs());
			int count = statement.executeUpdate();
			return count > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.closeAll(connection, statement);
		}
		return false;
	}
	
	public static boolean update(Product pro) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = DBManager.getConnection();
			String sql = "UPDATE product SET name = ?, type = ?, price = ?, descs = ? WHERE id = ?;";
			statement = connection.prepareStatement(sql);
			statement.setString(1, pro.getName());
			statement.setString(2, pro.getType());
			statement.setInt(3, pro.getPrice());
			statement.setString(4, pro.getDescs());
			statement.setInt(5, pro.getId());
			int count = statement.executeUpdate();
			return count > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.closeAll(connection, statement);
		}
		return false;
	}
	
	public static boolean delete(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = DBManager.getConnection();
			String sql = "DELETE FROM product WHERE id = ?;";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			int count = statement.executeUpdate();
			return count > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.closeAll(connection, statement);
		}
		return false;
	}
	
	public static Product findById(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DBManager.getConnection();
			String sql = "SELECT * FROM product WHERE id = ?;";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while(resultSet.next()) {
				return new Product(resultSet.getInt("id"), 
						resultSet.getString("name"), resultSet.getString("type"), resultSet.getInt("price"), resultSet.getString("descs"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.closeAll(connection, statement, resultSet);
		}
		return null;
	}
	
	public static List<Product> findAll() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Product> list = new ArrayList<>();
		try {
			connection = DBManager.getConnection();
			String sql = "SELECT * FROM product";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while(resultSet.next()) {
				Product pro = new Product(resultSet.getInt("id"), 
						resultSet.getString("name"), resultSet.getString("type"), 
						resultSet.getInt("price"), resultSet.getString("descs"));
				list.add(pro);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.closeAll(connection, statement, resultSet);
		}
		return list;
	}
}

//测试类

//测试类
public class Test1 {

	public static void main(String[] args) {
//		Product pro = new Product(0, "篮球", "运动", 200, "锻炼身体");
//		System.out.println(ProductDAO.insert(pro));
		
//		Product pro = new Product(4, "篮球", "运动", 300, "锻炼身体");
//		System.out.println(ProductDAO.update(pro));
		
//		System.out.println(ProductDAO.delete(5));
		
//		System.out.println(ProductDAO.findById(4));
		
	List<Product> list = ProductDAO.findAll();
		for (Product pro : list) {
			System.out.println(pro);
		}
	}
}

JDBC的步骤：

JDBC的API都在java.sql包下：
步骤：
1）加载MySQL的驱动（让Java程序识别MySQL数据库）
2）通过DriverManager（驱动管理器）创建Connection（连接）对象
3）通过Statement或者PreparedStatement（命令）对象发送SQL语句给MySQL
4）通过ResultSet（结果集）查询结果
5）关闭连接
DriverManager：驱动管理器，作用是用来获得连接。
Connection：数据库的连接
Statement：数据库的命令操作对象。
PreparedStatement：继承自Statement。预编译操作，比Statement在操作批量数据时性能更好，关键是能够防止SQL注入攻击。尽量使用。
ResultSet：结果集，操作查询结果的。

Statement和PreparedStatement在使用上的区别以及SQL注入攻击

上面的示例我们发现，即使用户和密码完全不对，也会提醒登录成功，原因是因为使用了字符串拼接SQL语言以及使用'号。

为了避免SQL注入攻击的问题的出现，我们可以使用PreparedStatement。使用PreparedStatement不用在SQL中写'号，还可以屏蔽参数中传入的'号和关键字等。

将SQL操作进行一个简单的封装,细分下面的包：

util：表示帮助类
entity：表示数据实体，即数据库对应的实体类
dao：表示数据库操作类
service：表示业务类
controller：表示控制类
test：表示测试类