1.1JdbcTemplate 概述
它是 spring 框架中提供的一个对象,是对原始 Jdbc API 对象的简单封装。spring 框架为我们提供了很多
的操作模板类。
操作关系型数据的:
JdbcTemplate
HibernateTemplate
操作 nosql 数据库的:
RedisTemplate
操作消息队列的:
JmsTemplate
我们今天的主角在 spring-jdbc-5.0.2.RELEASE.jar 中,我们在导包的时候,除了要导入这个 jar 包
外,还需要导入一个 spring-tx-5.0.2.RELEASE.jar(它是和事务相关的)。
首先需要创建maven项目,并导入所需要的依赖包:
<?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>com.mingqi</groupId> <artifactId>springjdbcdemo</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.0.2.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.0.2.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.0.2.RELEASE</version> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> </dependencies> </project>
(1) 直接声明对象复制使用jdbcTemplate
public static void demo1() { DriverManagerDataSource ds = new DriverManagerDataSource(); ds.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); ds.setUrl("jdbc:sqlserver://localhost:1433;DatabaseName=test"); ds.setUsername("sa"); ds.setPassword("123sa"); //1.创建JdbcTemplate对象 JdbcTemplate jt = new JdbcTemplate(); //给jt设置数据源 jt.setDataSource(ds);
List<User> user= jt.query("select * from Users ",new BeanPropertyRowMapper<User>(User.class),null );
for(User user1:user)
{
System.out.println(user1);
}
}
运行结果:
(2)使用配置文件实现JdbcTemplate
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <!--配置JdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 配置数据源--> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property> <property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=test"></property> <property name="username" value="sa"></property> <property name="password" value="123sa"></property> </bean> </beans>
调用方法:
public static void demo02() { //1.获取 Spring 容器 ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml"); //2.根据 id 获取 bean 对象 JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate"); //JdbcTemplate jt = ac.getBean("jdbcTemplate",JdbcTemplate.class); List<User> user= jt.query("select * from Users ",new BeanPropertyRowMapper<User>(User.class),null ); for(User user1:user) { System.out.println(user1); } }
(3)使用dao持久层实现JdbcTemplate
配置文件:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <!-- 配置用户的持久层--> <bean id="UserDao" class="com.mingqi.dao.impl.UserDao"> <property name="jdbcTemplate" ref="jdbcTemplate"></property> <!-- <property name="dataSource" ref="dataSource"></property>--> </bean> <!--配置JdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 配置数据源--> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property> <property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=test"></property> <property name="username" value="sa"></property> <property name="password" value="123sa"></property> </bean> </beans>
dao接口声明和实现:
package com.mingqi.dao; import com.mingqi.domin.User; import java.util.List; /*** * 用户持久层接口 */ public interface IUserDao { /** * 根据用户Id查询用户 */ User FindUserById(String Id); /* 根据名称查账户 */ List<User> findUserByName(String Name); void Updateuser(User user); }
package com.mingqi.dao.impl; import com.mingqi.dao.IUserDao; import com.mingqi.domin.User; import org.springframework.jdbc.core.BeanPropertyRowMapper; import java.util.List; public class UserDao extends JdbcDaoSupport implements IUserDao { public User FindUserById(String Id) { List<User> users=super.getJdbcTemplate().query("select * from Users where Id=?",new BeanPropertyRowMapper<User>(User.class),Id); return users.isEmpty()?null:users.get(0); } public List<User> findUserByName(String Name) { List<User> users=super.getJdbcTemplate().query("select * from Users where name like '%?%'",new BeanPropertyRowMapper<User>(User.class),Name); return users.isEmpty()?null:users; } public void Updateuser(User user) { super.getJdbcTemplate().update("Update Users set Name=? where id=?",user.getName(),user.getID()); } }
package com.mingqi.dao.impl; import org.springframework.jdbc.core.JdbcTemplate; import javax.activation.DataSource; public class JdbcDaoSupport { private JdbcTemplate jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setDataSource(DataSource dataSource) { if(jdbcTemplate == null){ jdbcTemplate = createJdbcTemplate(dataSource); } } private JdbcTemplate createJdbcTemplate(DataSource dataSource){ return new JdbcTemplate((javax.sql.DataSource) dataSource); } }
调用
public static void demo03() { ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml"); //2.获取对象 IUserDao accountDao = ac.getBean("UserDao",IUserDao.class); User u=accountDao.FindUserById("23C3E9FC-6D8A-4EA0-925A-0A0671D61378"); System.out.println(u); }
更新及删除操作值需要修改对应的SQL语句和对应的调用方法即可,这里就不再演示了,以后的文章中会逐渐增加基于注解的使用,