SpringJDBC

目录结构：

Pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">   <modelVersion>4.0.0</modelVersion>   <groupId>com.java.jdbc</groupId>   <artifactId>jdbcProject</artifactId>   <packaging>war</packaging>   <version>0.0.1-SNAPSHOT</version>   <name>jdbcProject Maven Webapp</name>   <url>http://maven.apache.org</url>   <dependencies>     <dependency>       <groupId>junit</groupId>       <artifactId>junit</artifactId>       <version>3.8.1</version>       <scope>test</scope>     </dependency>     <!-- spring框架 -->         <dependency>             <groupId>org.springframework</groupId>             <artifactId>spring-context</artifactId>             <version>5.3.8</version>         </dependency>         <!-- spring-test环境 -->         <!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->         <dependency>             <groupId>org.springframework</groupId>             <artifactId>spring-test</artifactId>             <version>5.3.8</version>         </dependency>         <!-- spring-jdbc -->         <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->         <dependency>             <groupId>org.springframework</groupId>             <artifactId>spring-jdbc</artifactId>             <version>5.3.8</version>         </dependency>         <!-- import spring aop -->         <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->         <dependency>             <groupId>org.aspectj</groupId>             <artifactId>aspectjweaver</artifactId>             <version>1.8.9</version>         </dependency>         <!-- spring事务相关 -->         <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->         <dependency>             <groupId>org.springframework</groupId>             <artifactId>spring-tx</artifactId>             <version>5.3.8</version>         </dependency>         <!-- mysql驱动 -->         <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->         <dependency>             <groupId>mysql</groupId>             <artifactId>mysql-connector-java</artifactId>             <version>8.0.25</version>         </dependency>         <!-- c3p0 连接池 -->         <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->         <dependency>             <groupId>com.mchange</groupId>             <artifactId>c3p0</artifactId>             <version>0.9.5.5</version>         </dependency>   </dependencies>   <build>     <finalName>jdbcProject</finalName>   </build> </project>

database_source.properties

jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/RATEDLEODB jdbc.username=sue jdbc.password=900906 #指定连接池的初始化连接数，取值应在minPoolSize与maxPoolSize之间 默认 default 3 initialPoolSize=20 #指定连接池中保留最大的连接数 默认15 maxPoolSize=100 #指定连接池中最小连接数 minPoolSize=10 #最大空闲时间 60秒内未使用则连接被丢弃 若为0则永不丢弃 默认0 maxIdleTime=600 #当连接池中的链接耗尽时c3p0一次同时获取的连接数 默认 3 acquireIncrement=5 #JDBC标准 用以控制数据源内加载的PreparedStatements数量 maxStatement=5 #每60秒检查所有连接池中的空闲链接 默认0 idleConnectionTestPeriod=60

application.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:aop="http://www.springframework.org/schema/aop"     xmlns:task="http://www.springframework.org/schema/task"     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        http://www.springframework.org/schema/aop        http://www.springframework.org/schema/aop/spring-aop.xsd        http://www.springframework.org/schema/task        http://www.springframework.org/schema/task/spring-task.xsd">     <!-- 方式二： 开启IOC注解扫描 -->     <context:component-scan base-package="com" />     <!-- 配置定时任务驱动 开启这个配置，spring才能识别 @Scheduled -->     <task:annotation-driven />     <!-- 加载database_resource.properties 读取文件配置 -->     <context:property-placeholder         location="database_source.properties" />     <!-- c3p0数据源配置 -->     <bean id="dataSource"         class="com.mchange.v2.c3p0.ComboPooledDataSource"         destroy-method="close">         <property name="driverClass" value="${jdbc.driver}" />         <property name="jdbcUrl" value="${jdbc.url}" />         <property name="user" value="${jdbc.username}" />         <property name="password" value="${jdbc.password}" />     </bean>     <!-- 配置jdbcTemplate实例 并注入datasource数据源 -->     <bean id="jdbcTemplate"         class="org.springframework.jdbc.core.JdbcTemplate">         <property name="dataSource" ref="dataSource"></property>     </bean> </beans>

方式一：注解方式加载配置文件

BaseTest.java

package com.test; import javax.annotation.Resource; import org.junit.runner.RunWith; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; /**  * 方式一：注解方式加载配置文件  * @author 13554  *  */ @RunWith(SpringJUnit4ClassRunner.class)//将单元测试运行在spring环境中 @ContextConfiguration(locations= {"classpath:application.xml"})//加载配置文件 public class BaseTest { //JdbcTest需要放开，UserDaoTest需要注释掉     @Resource     public JdbcTemplate jdbcTemplate; }

JdbcTest.java

package com.test; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.junit.Test; import org.springframework.jdbc.core.JdbcTemplate; public class JdbcTest  extends BaseTest{     @Test     public void query() {         String sql="select * from user";         List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);         for(Map<String,Object> map:list) {             System.out.println("username:"+map.get("username"));         }     } }

方式二：通过获取上下文环境初始化jdbcTemplate

BaseTest2.Java

package com.test; import org.junit.Before; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; //方式二：通过获取上下文环境初始化jdbcTemplate public class BaseTest2 {         public JdbcTemplate jdbcTemplate;         @Before     public void init() {         //获取上下文         ApplicationContext ac=new ClassPathXmlApplicationContext("application.xml");         //获取模板类         jdbcTemplate=(JdbcTemplate)ac.getBean("jdbcTemplate");     }     }

JdbcTest2.java

package com.test; import java.util.List; import java.util.Map; import org.junit.Test; public class JdbcTest2 extends BaseTest2{     @Test     public void query() {         String sql="select * from user";         List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);         for(Map<String,Object> map:list) {             System.out.println("username:"+map.get("username"));         }     } }

正式数据：

UserDao.java

package com.dao; import java.util.List; import com.entity.User; public interface UserDao {     /**      * 添加账户 返回更新记录数      * @param user      * @return      */     public int add(User user); }

UserDaoImpl.java

package com.dao.impl; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import com.dao.UserDao; import com.entity.User; @Repository public class UserDaoImpl implements UserDao {         @Resource     private JdbcTemplate jdbcTemplate;     public int add(User user) {         String sql="insert into User(id,username,password,account)values(?,?,?,?)";         Object[] obj= {user.getId(),user.getUsername(),user.getPassword(),user.getAccount()};         return jdbcTemplate.update(sql, obj);     }   }

UserDaoTest.java

package com.test; import org.junit.Test; import org.springframework.test.annotation.Rollback; import javax.annotation.Resource; import com.dao.UserDao; import com.entity.User; public class UserDaoTest extends BaseTest {     @Resource     private UserDao userDao;         @Test     @Rollback     public void testAdd() {         User user=new User();         user.setId(50);         user.setUsername("20161012");         user.setPassword("20210612");         user.setAccount("123456");         int i=userDao.add(user);         System.out.println("新增用户信息条数："+i);     } } 运行后：

备注：学习笔记