2Spring JdbcTemplate
在spring中为了更加方便的操作JDBC,在JDBC的基础之上定义了一个抽象层,此设计的目的是为不同类型的JDBC操作提供模板方法,每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务,通过这种方式,可以尽可能保留灵活性,将数据库存取的工作量讲到最低。
1、配置并测试数据源(druid提供数据源)
-
导入坐标
<?xml version="1.0" encoding="UTF-8"?> <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/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>mashibing_spring_oap_02</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <dependency> <groupId>cglib</groupId> <artifactId>cglib</artifactId> <version>3.3.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver --> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.9.5</version> </dependency> <!-- https://mvnrepository.com/artifact/aopalliance/aopalliance --> <dependency> <groupId>aopalliance</groupId> <artifactId>aopalliance</artifactId> <version>1.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>5.2.3.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/junit/junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>5.3.0</version> </dependency> <!--springioc的配置--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.2.3.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.1</version> </dependency> <!--添加数据库支持--> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> </dependencies> </project>
-
dbconfig.properties
-
如果将链接数据库信息直接写死,不利于后面的更改,因此将数据源信息存放于.properties文件中,在xml配置文件读取即可,降低程序的耦合性
jdbc.username=root password=root url=jdbc:mysql://localhost:3306/eesy driverClassName=com.mysql.jdbc.Driver
-
-
注意:必须加上jdbc. 否则会报错
-
配置applictionContext.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: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/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!--加载配置文件--> <context:property-placeholder location="dbconfig.properties"></context:property-placeholder> <!--注入数据源--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="username" value="${jdbc.username}"></property> <property name="password" value="${password}"></property> <property name="url" value="${url}"></property> <property name="driverClassName" value="${driverClassName}"></property> </bean> <!--配置JdbcTemplate--> <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate"> <!--注入数据源--> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
-
进行数据源测试(正常输出)
@Test public void test() { //获取配置文件信息 ApplicationContext ac = new ClassPathXmlApplicationContext("applictionContext.xml"); //获取数据源对象 DruidDataSource source = ac.getBean("dataSource", DruidDataSource.class); System.out.println(source);
2、给spring容器添加JdbcTemplate并连接测试
JdbcTemplate是spring提供的数据库连接池,进行数据库链接时,需要数据源,druid提供数据源,JdbcTemplate获取druid提供的数据库数据源后进行数据库链接
-
添加坐标依赖pom.xml
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>5.2.3.RELEASE</version> </dependency>
-
添加配置文件applictionContext.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: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/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!--加载配置文件--> <context:property-placeholder location="dbconfig.properties"></context:property-placeholder> <!--注入数据源--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="username" value="${username}"></property> <property name="password" value="${password}"></property> <property name="url" value="${url}"></property> <property name="driverClassName" value="${driverClassName}"></property> </bean> <!--配置JdbcTemplate--> <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate"> <!--注入数据源--> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
-
连接测试(正常运行)
@Test public void test() { //获取配置文件信息 ApplicationContext ac = new ClassPathXmlApplicationContext("applictionContext.xml"); //获取数据源对象 JdbcTemplate source = ac.getBean("template", JdbcTemplate.class); System.out.println(source); }
3、所有测试
-
插入测试(测试成功)
@Test public void test() { //获取配置文件信息 ApplicationContext ac = new ClassPathXmlApplicationContext("applictionContext.xml"); //获取数据源对象 JdbcTemplate jdbcTemplate = ac.getBean("template", JdbcTemplate.class); //插入数据 String sql="insert into account(name,money) values(?,?)"; jdbcTemplate.update(sql,"s",11);
-
查询测试并返回数组
@Test public void test() { //获取配置文件信息 ApplicationContext ac = new ClassPathXmlApplicationContext("applictionContext.xml"); //获取bean对象 JdbcTemplate jdbcTemplate = ac.getBean("template", JdbcTemplate.class); //查询 String sql="select * from account where name=?"; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, "a"); System.out.println(list.get(0)); }
-
查询测试并返回对象
需要创建pojo类进行封装
@Test public void test() { //获取配置文件信息 ApplicationContext ac = new ClassPathXmlApplicationContext("applictionContext.xml"); //获取bean对象 JdbcTemplate jdbcTemplate = ac.getBean("template", JdbcTemplate.class); //查询 String sql="select * from account where name=?"; account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(account.class), "江"); System.out.println(account); }
-
查询并返回对象集合
@Test public void test() { //获取配置文件信息 ApplicationContext ac = new ClassPathXmlApplicationContext("applictionContext.xml"); //获取bean对象 JdbcTemplate jdbcTemplate = ac.getBean("template", JdbcTemplate.class); //sql语句 String sql = "select * from account where name=?"; //查询返回对象集合 List<account> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(account.class), "a"); for(account a:list){ System.out.println(a); }
4、整合对象进行查询
-
在配置文件中添加扫描包
<context:component-scan base-package="com.mashibing"></context:component-scan>
-
实现
@Autowired private JdbcTemplate jdbcTemplate; public void save(account a){ String sql = "insert into account(name,money) values(?,?)"; int update = jdbcTemplate.update(sql, a.getname(), a.gemoney()); System.out.println(update); }
5、流程总结:
- 导入开发包
- 添加配置文件
- druid提供数据源–测试
- jdbcTemplate获取druid提供的数据源进行数据库链接–测试
- 创建实体类进行封装测试