javaのJDBCの添削を調べます。

javaを学ぶ16日目

JDBCの添削

JDBCのステップ：

SttementとPreparedStatimentの使用上の違いとSQL注入攻撃

は、SQL操作を簡単にパッケージ化し、以下のパケットを細分化する：

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：   ，       。

SttementとPreparedStatimentの使い分けとSQL注入攻撃

         ，           ，        ，             SQL      ' 。

    SQL          ，      PreparedStatement。  PreparedStatement   SQL  ' ，           '      。

SQL操作を簡単にパッケージ化し、以下のパッケージを細分化する。

util：     
entity：      ，          
dao：        
service：     
controller：     
test：