Spring对 JDBC 的支持

引用的外部jar包

aspectj-1.8.13.jar

c3p0-0.9.5.2.jar

commons-logging-1.2.jar

mchange-commons-java-0.2.11.jar

mysql-connector-java-5.1.41-bin.jar

spring-aop-4.3.2.RELEASE.jar

spring-beans-4.3.2.RELEASE.jar

spring-context-4.3.2.RELEASE.jar

spring-core-4.3.2.RELEASE.jar

spring-expression-4.3.2.RELEASE.jar

spring-jdbc-4.3.2.RELEASE.jar

spring-tx-4.3.2.RELEASE.jar

 

 

JdbcTemplate

JdbcTemplate 类被设计成为线程安全的, 所以可以在 IOC 容器中声明它的单个实例, 并将这个实例注入到所有的 DAO 实例中.

 

在 JDBC 模板中使用具名参数

在经典的 JDBC 用法中, SQL 参数是用占位符 ? 表示,并且受到位置的限制. 定位参数的问题在于, 一旦参数的顺序发生变化, 就必须改变参数绑定.

在 Spring JDBC 框架中, 绑定 SQL 参数的另一种选择是使用具名参数(named parameter).

JDBCTest

package com.spring.jdbc;
 
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.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 org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
 
class JDBCTest {
       private ApplicationContext ctx = null;
       private JdbcTemplate jdbcTemplate = null;
       private NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;
       {
              ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
              jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate");
              namedParameterJdbcTemplate = (NamedParameterJdbcTemplate)ctx.getBean("namedParameterJdbcTemplate");
       }
      
       @Test
       void testDataSource() {
              DataSource dataSource = ctx.getBean(DataSource.class);
              try {
                     System.out.println(dataSource.getConnection());
              } catch (SQLException e) {
                     e.printStackTrace();
              }
              /*
com.mchange.v2.c3p0.impl.NewProxyConnection@210ab13f [wrapping: com.mysql.jdbc.JDBC4Connection@20b2475a]
               * */
       }
       /**
        * 测试INSERT、UPDATE、DELETE
        */
       @Test
       void testUpdata() {
              String sql ="update login set password=? where username=?";
              jdbcTemplate.update(sql, "asdf","11111");
       }
       /**
        * 执行批量更新，批量的insert、updata、delete
        * 最后一个参数是Object[]的List类型：一条记录需要一个Object数组
        */
       @Test
       void testBatchUpdata() {
              String sql = "insert into login values(?,?)";
              List<Object[]> batchArgs = new ArrayList<>();
              batchArgs.add(new Object[]{"aa","aapassword"});
              batchArgs.add(new Object[]{"bb","bbpassword"});
              batchArgs.add(new Object[]{"cc","ccpassword"});
              batchArgs.add(new Object[]{"dd","ddpassword"});
              jdbcTemplate.batchUpdate(sql, batchArgs);
       }
       /**
        * 从数据库获取一条记录，实际得到对应的一条记录
        * 调用queryForObject(String sql, RowMapper<Login> rowMapper, Object... args)方法
        * 1.RowMapper指定如何映射结果集的行，常用的实现类BeanPropertyRowMapper<>
        * 2.使用sql中列的别名完成列名和类的属性名的映射
        * 3.不支持级联属性
        */
       @Test
       void testQueryForObject() {
              String sql = "select * from login where username = ?";
              RowMapper<Login> rowMapper = new BeanPropertyRowMapper<>(Login.class);
              Login login = jdbcTemplate.queryForObject(sql, rowMapper,"aa");
              System.out.println(login);
              /*
Login [username=aa, password=aapassword]
               * */
       }
       /**
        * 获取单个列的值，或做统计查询
        */
       @Test
       void testQueryForObject2() {
              String sql = "select count(username) from login";
              long count = jdbcTemplate.queryForObject(sql, long.class);
              System.out.println(count);
       }
       /**
        * 查实体类的集合
        */
       @Test
       void testQueryForList() {
       String sql = "select * from login";
       RowMapper<Login> rowMapper = new BeanPropertyRowMapper<>(Login.class);
       List<Login> logins = jdbcTemplate.query(sql, rowMapper);
       System.out.println(logins);
       /*
[Login [username=11111, password=asdf], Login [username=aa, password=aapassword],
Login [username=admin, password=admin], Login [username=bb, password=bbpassword],
Login [username=cc, password=ccpassword], Login [username=dd, password=ddpassword],
Login [username=ding, password=1996], Login [username=fff, password=fpassword],
Login [username=gg, password=ggpassword], Login [username=null, password=null],
Login [username=wwww, password=wwwww]]
        * */
       }
       /**
        * 可以为参数起名字
        * 优：参数较多时可以不用对应位置，直接对应参数名
        * 缺：较为麻烦
        */
       @Test
       void testNamedParameterJdbcTemplate() {
              String sql = "insert into login values(:username,:password)";
              Map<String,Object> paramMap = new HashMap<>();
              paramMap.put("username", "fff");
              paramMap.put("password", "fpassword");
              namedParameterJdbcTemplate.update(sql, paramMap);
       }
       /**
        * 具名参数，使用update(String sql, SqlParameterSource paramSource)进行更新操作
        * 1.sql语句中的参数名要和类中保持一致
        * 2.使用SqlParameterSource的实现类BeanPropertySqlParameterSource，实体类作为参数
        */
       @Test
       void testNamedParameterJdbcTemplate2() {
              String sql = "insert into login values(:username,:password)";
              Login login = new Login();
              login.setUsername("gg");
              login.setPassword("ggpassword");
              SqlParameterSource paramSource = new BeanPropertySqlParameterSource(login);
              namedParameterJdbcTemplate.update(sql, paramSource );
       }
}

applicationContext.xml

<?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"
       xmlns:tx="http://www.springframework.org/schema/tx"
       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.3.xsd
              http://www.springframework.org/schema/tx
              http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">
             
       <context:component-scan base-package="com.spring"></context:component-scan>
      
       <!-- 导入资源文件 -->
       <context:property-placeholder location="classpath:db.properties"/>
       <!-- 配置C3P0数据源 -->
       <bean id="datasSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
              <property name="user" value="${jdbc.user}"></property>
              <property name="password" value="${jdbc.password}"></property>
              <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
              <property name="driverClass" value="${jdbc.driverClass}"></property>
                    
              <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>     
              <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>     
      
       </bean>
       <!-- 配置Spring的JdbcTemplate -->
       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
              <property name="dataSource" ref="datasSource"></property>
       </bean>
      
       <!--配置NamedParameterJdbcTemplate，使用具名参数，必须为构造器指定参数  -->
       <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
              <constructor-arg ref="datasSource"></constructor-arg>
       </bean>
      
       <!-- 配置事务管理器 -->
       <bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
              <property name="dataSource" ref="datasSource"></property>
       </bean>
      
       <!-- 启用事务注解 -->
       <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>

db.properties

jdbc.user=root
jdbc.password=1996
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql:///javaee
 
jdbc.initPoolSize=5
jdbc.maxPoolSize=10

Login

package com.spring.jdbc;
 
public class Login {
       private String username;
       private String password;
       public String getUsername() {
              return username;
       }
       public void setUsername(String username) {
              this.username = username;
       }
       public String getPassword() {
              return password;
       }
       public void setPassword(String password) {
              this.password = password;
       }
       @Override
       public String toString() {
              return "Login [username=" + username + ", password=" + password + "]";
       }
      
}