Review

昨日我們講解了JdbcTemplate的是前DB與JAR檔準備，也介紹JdbcTemplate的Insert、BatchInsert、Update

今日我們將繼續透過JdbcTemplate完成後續的CREUD

Select

查詢單一數據

需注意查詢不到時會拋出異常，此時需要使try catch讓其Return null
創建Java Bean

public class Product {
    private String productId;
    private int inventory;
    //getter setter toString 略
}

@Test
public void testDay24(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean24.xml");
    System.out.println("容器啟動完成....");
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

    String sql = "SELECT * FROM PRODUCT WHERE PRODUCT_ID = ?";
    //RowMapper：資料略與Java Bean的映射 -> 實現類BeanPropertyRowMapper
    Product product = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Product.class),"P001");
    System.out.println(product);
}

Result

查詢不到值

//RowMapper：資料略與Java Bean的映射 -> 實現類BeanPropertyRowMapper
Product product = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Product.class),"P111");

Result

修改後程式

    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

    String sql = "SELECT * FROM PRODUCT WHERE PRODUCT_ID = ?";
    Product product = null;
    //RowMapper：資料略與Java Bean的映射
    try{
        product = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Product.class),"P111");
    }catch (Exception e){

    }
    System.out.println(product);//null

查詢多條數據

@Test
public void testDay24(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean24.xml");
    System.out.println("容器啟動完成....");
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

    String sql = "SELECT PRODUCT_ID AS productId ,INVENTORY AS inventory FROM PRODUCT WHERE INVENTORY >?";
    List<Product> product = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Product.class),20);
    System.out.println(product);
}

Result

查詢單一值

@Test
public void testDay24(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean24.xml");
    System.out.println("容器啟動完成....");
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

    String sql = "SELECT MAX(INVENTORY) FROM PRODUCT";
    Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
    System.out.println(count);
}

Result

NameParameter

加入NameParameter設定

<!-- 配置NamedParameterJdbcTemplate   -->
<bean id="NamedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
</bean>

@Test
public void testDay24(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean24.xml");
    System.out.println("容器啟動完成....");
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
    NamedParameterJdbcTemplate nameJdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);

    String sql = "INSERT INTO PRODUCT (PRODUCT_ID,INVENTORY) VALUES (:productId,:inventory)";
    Map<String, Object> map = new HashMap<>();
    map.put("productId","P100");
    map.put("inventory",0);
    int num = nameJdbcTemplate.update(sql,map);
    System.out.println(num);
}

Result

SqlParameterSource傳參

可以透過Java Bean的屬性為Sql傳參數

@Test
public void testDay24(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean24.xml");
    NamedParameterJdbcTemplate nameJdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);
    
    String sql = "INSERT INTO PRODUCT (PRODUCT_ID,INVENTORY) VALUES (:productId,:inventory)";
    Product product = new Product();
    product.setProductId("P101");
    product.setInventory(101);
    int num = nameJdbcTemplate.update(sql,new BeanPropertySqlParameterSource(product));
    System.out.println(num);
}

Result

DAO

<context:component-scan base-package="com.swj"></context:component-scan>

@Repository
public class ProductDao {
    @Autowired
    JdbcTemplate jdbcTemplate;

    public void saveProduct(Product product){
        String sql = "INSERT INTO PRODUCT (PRODUCT_ID,INVENTORY) VALUES (?,?)";
        jdbcTemplate.update(sql,product.getProductId(),product.getInventory());
    }
}

@Test
public void testDay24(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean24.xml");
    ProductDao dao = ioc.getBean(ProductDao.class);
    Product product = new Product();
    product.setProductId("P102");
    product.setInventory(102);
    dao.saveProduct(product);
}

Result