SpringJDBCTemplate

向右看齐 2022-05-23 06:42 186阅读 0赞

配置依赖：

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.2</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>4.3.14.RELEASE</version>
    </dependency>

外部文件：

user=root
    password=123456
    url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=true&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
    driver=com.mysql.jdbc.Driver
    
    initPoolSize=5
    maxPoolSize=10

配置文件：

<?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.xsd">
    
        
        <context:property-placeholder location="db.properties"/>
    
        
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="${driver}"/>
            <property name="jdbcUrl" value="${url}"/>
            <property name="user" value="${user}"/>
            <property name="password" value="${password}"/>
    
            <property name="initialPoolSize" value="${initPoolSize}"/>
            <property name="maxPoolSize" value="${maxPoolSize}"/>
        </bean>
    
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"/>
        </bean>
    
    
    </beans>

测试类：

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.RowMapper;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    /** * @author chenpeng * @date 2018/6/4 10:45 */ public class JDBCTest {
        private ApplicationContext context = null;
        private JdbcTemplate jdbcTemplate;
        {
            context = new ClassPathXmlApplicationContext("applicationContext.xml");
            jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
    
        }
    
        /** * 执行INSERT,UPDATE----- */ @Test
        public void testUpdate(){
            String str = "update t_department set d_name=? where id=?";
            jdbcTemplate.update(str,"实习部",2);
        }
    
        /** * 批量执行INSERT,UPDATE----- * 最后一个参数是Object[ ]的 List类型，因为修改一条记录需要一个Object数组，多条就需要多个 * * @throws SQLException * */ @Test
        public void updateBatch(){
            String str = "update t_department set d_name=? where id=?";
    
            List<Object[]> batchArgs = new ArrayList<Object[]>();
            batchArgs.add(new Object[]{
      "aa",1});
            batchArgs.add(new Object[]{
      "bb",2});
            jdbcTemplate.batchUpdate(str,batchArgs);
        }
    
        /** * RowMapper:指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper * 并且使SQL中列的别名完成列名和类属性名的映射，如d_name name * 不支持级联属性，到底jdbcTemplate是一个小工具不是ORM框架 */ @Test
        public void query(){
            String str = "select id,d_name name from t_department where id=? ";
            RowMapper mapper = new BeanPropertyRowMapper(Department.class);
            List<Department> department = jdbcTemplate.query(str,mapper,1);
            System.out.println(department);
        }
    
    
    
        @Test
        public void testDataSource() throws SQLException {
            DataSource dataSource = (DataSource) context.getBean("dataSource");
            System.out.println(dataSource.getConnection());
        }
    }

关于NamedParameterJdbcTemplate

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

/** * 可以为参数取名字 * 好处：有多个参数不必再去对应位置 * 缺点：麻烦 */ @Test
    public void testNamedParameterJdbcTemplate(){
        String str = "insert into t_department(d_name) values(:name)";
        Map paraMap = new HashMap();
        paraMap.put("name","123456");
        namedParameterJdbcTemplate.update(str,paraMap);
    }