spring详解（四）——JDBC

Spring  JDBC

1、在这里使用的是C3P0数据源，所以需要导入jar包，和额外的spring包

 

 2、spring配置文件中配置数据源bean和配置JDBCTemplate的bean

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">
    <!-- 配置数据源：数据库交互的  C3P0 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="root"/>
        <property name="password" value="zjc"/>
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/student"/>
    </bean>
    <!-- 配置springjdbc的模板类 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

 

3、测试

package com.zhiyou100.zjc.test;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

public class SpringJdbcTest {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        ApplicationContext app = new ClassPathXmlApplicationContext("springjdbc.xml");
        DataSource data = (DataSource) app.getBean("dataSource");
        System.out.println(data);
    }

}

 

CRUD 

package day090801;

import static org.junit.jupiter.api.Assertions.*;

import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.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.RowMapper;

import com.zhiyou100.zjc.bean.User;

class SpringJdbcTest {
    static JdbcTemplate jdbcTemplate=null;
    @BeforeAll
    static void setUpBeforeClass() throws Exception {
        ApplicationContext app = new ClassPathXmlApplicationContext("springjdbc.xml");
        jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
    }

    
    @Test//添加一条记录
    void updatetest() {
        String sql = "insert into users(username,password) values(?,?)";
        jdbcTemplate.update(sql, "sss","rrr");
    }
    @Test//添加多条记录
    void batchUpdatetest() {
        String sql = "insert into users(username,password) values(?,?)";
        List<Object[]> list = new ArrayList<Object[]>();
        Object[] obj1 = {"aaa","bbb"};
        list.add(obj1);
        Object[] obj2 = {"ccc","ddd"};
        list.add(obj2);
        Object[] obj3 = {"eee","fff"};
        list.add(obj3);
        jdbcTemplate.batchUpdate(sql, list);
    }
    @Test//删除多条记录
    void batchdeletetest() {
        String sql = "delete from users where username=? and password=?";
        List<Object[]> list = new ArrayList<Object[]>();
        Object[] obj1 = {"aaa","bbb"};
        list.add(obj1);
        Object[] obj2 = {"ccc","ddd"};
        list.add(obj2);
        Object[] obj3 = {"eee","fff"};
        list.add(obj3);
        jdbcTemplate.batchUpdate(sql, list);
    }
    @Test//查询一条记录
    void selectTest() {
        String sql = "select * from users where id=?";
        RowMapper<User> rm = new  BeanPropertyRowMapper<User>(User.class);
        User user = jdbcTemplate.queryForObject(sql, rm, 2);
        System.out.println(user);
    }
    @Test//查询多条记录
    void selectAllTest() {
        String sql = "select * from users";
        RowMapper<User> rm = new  BeanPropertyRowMapper<User>(User.class);
        List<User> list = jdbcTemplate.query(sql, rm);
        System.out.println(list);
    }
    @Test//查询单列值
    void selectAnCroTest() {
        String sql = "select count(*) from users";
        RowMapper<User> rm = new  BeanPropertyRowMapper<User>(User.class);
        Integer name = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(name);
    }

}