Spring的JDBC模板
ORM持久化技术 | 模板类 |
---|---|
JDBC | org.springframework.JdbcTemplate |
Hibernate3.0 | org.springframework.orm.hibernate3.HibernateTemplate |
IBatis(Mybatis) | org.springframework.ibatis.SqlMapClientTemplate |
JPA | org.springframework.ibatis.orm.jpa.JpaTemplate |
第一步:引入jar包pom.xml配置文件如下
<?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.pp</groupId>
<artifactId>spring_day4</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<!--springJDBC支持的包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.5.RELEASE</version>
</dependency>
<!--数据库驱动包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
</project>
第二步:配置applicationContex.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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
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
">
<!--注解扫描-->
<context:component-scan base-package="com.pp"/>
<!--<bean id="userDao" class="com.pp.demo1.UserDaoImpl"/>
<bean id="myAspectXml" class="com.pp.demo1.MyAspectXml"/>-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///spring_jdbc"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</bean>
</bean>
连接数据库的方式,一种是上面的在applicationContext.xml配置文件中配置。另一种方式如下,在根目录下创建一个com.pp包。然后新建一个demo1的包,然后新建一个JdbcTest类。两个配置效果的等效的,
- 连接数据库的方法一
具体代码如下:
package com.pp.demo1;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class JdbcTest {
@Test
public void run(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///spring_jdbc");
dataSource.setUsername("root");
dataSource.setPassword("123");
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);
jdbcTemplate.update("insert into t_account values (3,?,?)","李四","5000");
}
}
由于使用这种方式不方便维护我们的项目,因此这种方式不建议使用。建议使用配置文件的形式去连接数据库。
- 连接数据库的方法二(也就是下面第三步、第四步使用的方法)
第三步:创建TestDemo类用配置文件的形式去连接数据库
package com.pp.demo1;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestDemo {
@Resource(name = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
...
}
这里使用@RunWith进行注解,相当于前面使用的@Test用于测试。@ContextConfiguration注解用于加载我们的配置文件,这里是加载applicationContext.xml。 @Resource(name = “jdbcTemplate”)的使用需要在applicationContext.xml中进行配置管理才能使用此注解通过id去找到jdbcTemplate。其中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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
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
">
<!--注解扫描-->
<context:component-scan base-package="com.pp"/>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///spring_jdbc"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="accountDao" class="com.pp.demo2.AccountDaoImpl">
<property name="dataSource" ref="dataSource"/>
</bean>
</bean>
上面的配置除了配置了数据库的连接,还管理了jdbcTemplate。这样通过注解访问id值就可以实例化该对象,从而连接数据库。
第四步:编写TestDemo测试类的内容
...
...
...
public class TestDemo {
@Resource(name = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void run(){
jdbcTemplate.update("insert into t_account values (4,?,?)","李四","6000");
}
}
这里对代码进行了简写,省略的部分前面有,因为我们在配置文件进行了注解配置,所以这里使用 @Resource(name = “jdbcTemplate”)就可以通过spring工厂进行创建jdbcTemplate对象,所以我们在测试方法run()中,只需要使用jdbcTemplate.update(sql语句)就可以对数据库进行修改。具体的增删查改方法如下代码:
package com.pp.demo1;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestDemo {
@Resource(name = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void run(){ //插入语句
jdbcTemplate.update("insert into t_account values (4,?,?)","李四","6000");
}
@Test
public void run1(){ //更新语句
jdbcTemplate.update("update t_account set name=? where id=?","李四dsd","4");
}
@Test
public void run2(){ //删除语句
jdbcTemplate.update("delete from t_account where id=?","4");
}
@Test
public void run3(){ //按照具体要求查询语句
Account account = jdbcTemplate.queryForObject("select * from t_account where id=?", new BeanMapper(), 1);
System.out.println(account);
}
@Test
public void run4(){ //查询全部语句
List<Account> account = jdbcTemplate.query("select * from t_account ", new BeanMapper());
System.out.println(account);
}
//这里的jdbcTemplate查询方法需要有一个BeanMapper,所以这里自己创建了一个,让其实现RowMapper<Account>
//平时开发的时候也是需要一个实体对象Domain,和数据库的数据表中的属性一一对应。
//所以这里又创建了一个Account类
class BeanMapper implements RowMapper<Account>{
public Account mapRow(ResultSet resultSet, int i) throws SQLException {
Account account = new Account();
account.setId(resultSet.getInt("id"));
account.setName(resultSet.getString("name"));
account.setMoney(resultSet.getDouble("money"));
return account;
}
}
}
上面代码提到的Account实体类代码如下:
package com.pp.demo1;
public class Account {
/* `id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`money` double DEFAULT NULL,*/
private Integer id;
private String name;
private Double money;
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
}