当前位置：Gxlcms > 数据库问题 > spring4-4-jdbc-01

spring4-4-jdbc-01

时间：2021-07-01 10:21:17 帮助过：7人阅读

1.导入资源文件 --> <context:property-placeholder location="classpath:db.properties"/>

　2.2 配置C3P0数据源:

    <span style="color: #008000;"><!--</span><span style="color: #008000;"> 2.配置C3P0数据源 </span><span style="color: #008000;">--></span>
    <span style="color: #0000ff;"><</span><span style="color: #800000;">bean </span><span style="color: #ff0000;">id</span><span style="color: #0000ff;">="dataSource"</span><span style="color: #ff0000;"> class</span><span style="color: #0000ff;">="com.mchange.v2.c3p0.ComboPooledDataSource"</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="user"</span><span style="color: #ff0000;"> value</span><span style="color: #0000ff;">="${jdbc.user}"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="password"</span><span style="color: #ff0000;"> value</span><span style="color: #0000ff;">="${jdbc.password}"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="driverClass"</span><span style="color: #ff0000;"> value</span><span style="color: #0000ff;">="${jdbc.driverClass}"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="jdbcUrl"</span><span style="color: #ff0000;"> value</span><span style="color: #0000ff;">="${jdbc.jdbcUrl}"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="initialPoolSize"</span><span style="color: #ff0000;"> value</span><span style="color: #0000ff;">="${jdbc.initialPoolSize}"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="maxPoolSize"</span><span style="color: #ff0000;"> value</span><span style="color: #0000ff;">="${jdbc.maxPoolSize}"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
    <span style="color: #0000ff;"></</span><span style="color: #800000;">bean</span><span style="color: #0000ff;">></span>

2.3 配置JdbcTemplate bean:

    <span style="color: #008000;"><!--</span><span style="color: #008000;"> 3.配置 jdbc template </span><span style="color: #008000;">--></span>
    <span style="color: #0000ff;"><</span><span style="color: #800000;">bean </span><span style="color: #ff0000;">id</span><span style="color: #0000ff;">="jdbcTemplate"</span><span style="color: #ff0000;"> class</span><span style="color: #0000ff;">="org.springframework.jdbc.core.JdbcTemplate"</span><span style="color: #0000ff;">></span>
        <span style="color: #0000ff;"><</span><span style="color: #800000;">property </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">="dataSource"</span><span style="color: #ff0000;"> ref</span><span style="color: #0000ff;">="dataSource"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">property</span><span style="color: #0000ff;">></span>
    <span style="color: #0000ff;"></</span><span style="color: #800000;">bean</span><span style="color: #0000ff;">></span>

3.应用

3.1加载Sping IOC容器

    <span style="color: #0000ff;">private</span> ApplicationContext ctx = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> JdbcTemplate jdbcTemplate;
    @Before
    </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> init(){
        ctx </span>= <span style="color: #0000ff;">new</span> ClassPathXmlApplicationContext("applicationContext-jdbc.xml"<span style="color: #000000;">);
        jdbcTemplate </span>= ctx.getBean("jdbcTemplate",JdbcTemplate.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
    }
    @Test
    </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> testJdbc() <span style="color: #0000ff;">throws</span><span style="color: #000000;"> SQLException {
        DataSource dataSource </span>= ctx.getBean("dataSource",DataSource.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
        System.out.println(dataSource.getConnection());
    }</span>

3.2测试连接:

        DataSource dataSource = ctx.getBean("dataSource",DataSource.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
        System.out.println(dataSource.getConnection());</span>

3.3 测试插入,更新,删除

        <span style="color: #008000;">//</span><span style="color: #008000;">insert</span>
        String insertSql = "insert employees(name,email,dept_id) values(?,?,?)"<span style="color: #000000;">;
        jdbcTemplate.update(insertSql, </span>"张三","zhangsan@si.com","3"<span style="color: #000000;">);
        
        </span><span style="color: #008000;">//</span><span style="color: #008000;">update</span>
        String updateSql = "update employees set name= ?  where id = ?"<span style="color: #000000;">;
        jdbcTemplate.update(updateSql, </span>"张四","1"<span style="color: #000000;">);
        
        </span><span style="color: #008000;">//</span><span style="color: #008000;">delete</span>
        String deleteSql = "delete from  employees where id=?"<span style="color: #000000;">;
        jdbcTemplate.update(deleteSql, </span>"1");

3.4 批处理插入,修改,删除

<span style="color: #008000;">　　　　//</span><span style="color: #008000;">insert</span>
        String insertSql = "insert employees(name,email,dept_id) values(?,?,?)"<span style="color: #000000;">;
        List</span><Object[]> batchArgs = <span style="color: #0000ff;">new</span> ArrayList<Object[]><span style="color: #000000;">();
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"张三","zhang@si.com","1"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"李四","li@si.com","2"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"王五","wang@si.com","3"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"钱六","qian@si.com","4"<span style="color: #000000;">});
        jdbcTemplate.batchUpdate(insertSql, batchArgs);
        
        </span><span style="color: #008000;">//</span><span style="color: #008000;"> update</span>
        String updateSql = "update employees set email=? where id=?"<span style="color: #000000;">;
        batchArgs.clear();
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"zhangsan@si.com","1"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"lisi@si.com","2"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"wangwu@si.com","3"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"qianliu@si.com","4"<span style="color: #000000;">});
        jdbcTemplate.batchUpdate(updateSql, batchArgs);
        
        </span><span style="color: #008000;">//</span><span style="color: #008000;"> delete</span>
        String deleteSql = "delete from employees  where id = ?"<span style="color: #000000;">;
        batchArgs.clear();
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"1"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"2"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"3"<span style="color: #000000;">});
        batchArgs.add(</span><span style="color: #0000ff;">new</span> Object[]{"4"<span style="color: #000000;">});
        jdbcTemplate.batchUpdate(deleteSql, batchArgs);</span>

4. 查询一行,返回对象

<span style="color: #008000;">/**</span><span style="color: #008000;">
     * 从数据库中获取一条记录, 实际得到对应的一个对象
     * 注意不是调用 queryForObject(String sql, Class<Employee> requiredType, Object... args) 方法!
     * 而需要调用 queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)
     * 1. 其中的 RowMapper 指定如何去映射结果集的行, 常用的实现类为 BeanPropertyRowMapper
     * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射. 例如 last_name lastName
     * 3. 不支持级联属性. JdbcTemplate 到底是一个 JDBC 的小工具, 而不是 ORM 框架
     </span><span style="color: #008000;">*/</span><span style="color: #000000;">
    @Test
    </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> testQueryForObject(){
        String sql </span>= "SELECT id, last_name lastName, email, dept_id as \"department.id\" FROM employees WHERE id = ?"<span style="color: #000000;">;
        RowMapper</span><Employee> rowMapper = <span style="color: #0000ff;">new</span> BeanPropertyRowMapper<>(Employee.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
        Employee employee </span>= jdbcTemplate.queryForObject(sql, rowMapper, 1<span style="color: #000000;">);
        
        System.out.println(employee);
    }</span>

5.查询类的集合

<span style="color: #008000;">/**</span><span style="color: #008000;">
     * 查到实体类的集合
     * 注意调用的不是 queryForList 方法
     </span><span style="color: #008000;">*/</span><span style="color: #000000;">
    @Test
    </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> testQueryForList(){
        String sql </span>= "SELECT id, last_name lastName, email FROM employees WHERE id > ?"<span style="color: #000000;">;
        RowMapper</span><Employee> rowMapper = <span style="color: #0000ff;">new</span> BeanPropertyRowMapper<>(Employee.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
        List</span><Employee> employees = jdbcTemplate.query(sql, rowMapper,5<span style="color: #000000;">);
        
        System.out.println(employees);
    }</span>

6.查询单列值

<span style="color: #008000;">/**</span><span style="color: #008000;">
     * 获取单个列的值, 或做统计查询
     * 使用 queryForObject(String sql, Class<Long> requiredType) 
     </span><span style="color: #008000;">*/</span><span style="color: #000000;">
    @Test
    </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> testQueryForObject2(){
        String sql </span>= "SELECT count(id) FROM employees"<span style="color: #000000;">;
        </span><span style="color: #0000ff;">long</span> count = jdbcTemplate.queryForObject(sql, Long.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
        
        System.out.println(count);
    }</span>

spring4-4-jdbc-01

标签：

spring4-4-jdbc-01

3.应用

人气教程排行