1.1JdbcTemplate开发步骤
(1).导入spring-jdbc和spring-tx坐标
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.0.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.0.5.RELEASE</version> </dependency>
(2).创建数据表和实体
public class Account {
private String name;
private int money;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"name='" + name + '\'' +
", money=" + money +
'}';
}
}
(3).创建jdbcTemplate对象
@Test
public void test1() throws PropertyVetoException {
//创建数据源对象
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai");
dataSource.setUser("root");
dataSource.setPassword("root");
JdbcTemplate jdbcTemplate = new JdbcTemplate();
//设置数据源对象
jdbcTemplate.setDataSource(dataSource);
//执行操作
int tom = jdbcTemplate.update("insert into account values (?,?)", "tom", 100);
System.out.println(tom);
}
(4).执行数据库操作
1.2Spring产生JdbcTemplate对象(模板对象)
我们可以将jdbcTemplate的创建交给Spring,将数据源DateSource的创建页交给Spring,正在Spring容器内部数据源DataSource注入JdbcTemplate模板对象中,配置如下:
(name :注入dataSource,ref:引用容器中的dataSource对象。)
<?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="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property> <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai"></property> <property name="password" value="root"></property> <property name="user" value="root"></property> </bean> <!--jdbc模板对象--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
完善,抽取jdbc.properties
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai jdbc.username=root jdbc.password=root
<?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" 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 "> <!--加载外部jdbc.properties--> <context:property-placeholder location="classpath:jdbc.properties"/> <!--数据源对象--> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.driver}"></property> <property name="jdbcUrl" value="${jdbc.url}"></property> <property name="password" value="${jdbc.password}"></property> <property name="user" value="${jdbc.username}"></property> </bean> <!--jdbc模板对象--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
执行数据库操作
@Test //Spring产生jdbcTemplate对象 public void test2() throws PropertyVetoException { ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = applicationContext.getBean(JdbcTemplate.class); int i = jdbcTemplate.update("insert into account values(?,?)", "张三", 400); System.out.println(i); }
执行结果
1.3JdbcTemplate基本操作-更新(增、删、改)
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
//
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateDDL {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
public void testUpdate(){
jdbcTemplate.update("update account set money=? where name=?",1000000,"tom");
}
@Test
public void testDelete(){
jdbcTemplate.update("delete from account where name=?","tom");
}
@Test
public void testInsert(){
jdbcTemplate.update("insert into account values (?,?)","tom",999999);
}
}
1.3JdbcTemplate基本操作-查询(单-所有-聚合查询)
import com.hzw.domin.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateDDL {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
public void testSelectAll(){
List<Account> tom = jdbcTemplate.query("select * from account ", new BeanPropertyRowMapper<Account>(Account.class));
System.out.println(tom);
}
@Test
public void testSelectOne(){
Account tom = jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class), "tom");
System.out.println(tom);
}
@Test
public void testSelectCount(){
Long aLong = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
System.out.println(aLong);
}}