package com.zyh.test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.zyh.bean.Country;

public class TestJdbc {

    private ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
    private DataSource dataSource = ac.getBean(DataSource.class);
    private JdbcTemplate jdbcTemplate = ac.getBean(JdbcTemplate.class);
    private NamedParameterJdbcTemplate named = ac.getBean(NamedParameterJdbcTemplate.class);
    
    @Test
    public void test() throws SQLException {
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }
    
    @Test
    public void test01() throws SQLException {
        System.out.println(jdbcTemplate);
    }
    
    /**
     * 更新信息
     * **/
    @Test
    public void testUpdate() {
        String sql = "update user set u_username=? where u_id=?";
        int update = jdbcTemplate.update(sql,"隔壁老樊",1);
        System.out.println(update);
    }
    
    /**
     * 批量插入信息
     * **/
    @Test
    public void testInsert() {
        String sql = "insert into country(c_countryname,c_capital) value(?,?)";
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"蒙古","乌兰巴托"});
        batchArgs.add(new Object[]{"西班牙","马德里"});
        batchArgs.add(new Object[]{"荷兰","阿姆斯特丹"});
        
        jdbcTemplate.batchUpdate(sql,batchArgs);
    }
    
    /**
     *  查询c_id为8的数据库记录，并封装为一个java对象
     *无论是查询结果是单个数据还是对象，都用queryForObject 
     * 
     * **/
    
    @Test
    public void test04() {
        String sql = "select * from country where c_id=?";
        String sql1 = "select c_id from country where c_capital=?";
        //自定义类型
        Country country = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Country.class), 8);
        //原生jdk类型
        Integer c_id = jdbcTemplate.queryForObject(sql1, Integer.class,"阿姆斯特丹");
        System.out.println(country);
        System.out.println("c_id="+c_id);
    }
    
    /**
     * 将结果封装为list集合
     * **/
    @Test
    public void test05() {
        String sql = "select * from country";
        List<Country> cList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Country.class));
        for (Country country : cList) {
            System.out.println(country);
        }
    }
    
    /**
     * 1.使用代用具名参数的SQL语句插入一条记录，分别以Map形式和SqlParameterSource形式传入参数值
     *      具名参数：具有名字的参数，(格式为 （:参数名）)
     *      占位符参数：？的顺序不能出错
     *      Spring有一个支持具名参数功能的JdbcTemplate
     * 
     * **/
    @Test
    public void test06() {
        String sql = "insert into country(c_countryname,c_capital) value(:c_countryname,:c_capital)";
//      Map<String, Object> cMap = new HashMap<>();
//      cMap.put("c_countryname", "俄罗斯");
//      cMap.put("c_apital", "莫斯科");
//      int updateCMap = named.update(sql, cMap);
        
        Country country = new Country();
        country.setC_countryname("德国");
        country.setC_capital("柏林");
        int updateSource = named.update(sql, new BeanPropertySqlParameterSource(country));
        
        System.out.println(updateSource);
    }
}

配置文件

    <context:property-placeholder location="classpath:db.properties"/>
    <context:component-scan base-package="com.zyh"></context:component-scan>
    
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name = "driverClass" value = "${jdbc_driver}"/>
        <property name = "jdbcUrl" value = "${jdbc_url}"/>
        <property name = "user" value = "${jdbc_user}"/>
        <property name = "password" value = "${jdbc_password}"/>
    </bean>
    
    <bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name = "dataSource" ref = "dataSource"></constructor-arg>
    </bean>
    
    <bean id = "named" class = "org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg name = "dataSource" ref = "dataSource"></constructor-arg>
    </bean>