spring JDBCTemplate使用
导入依赖
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?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.itheima</groupId> <artifactId>day04_eesy_jdbctemplate</artifactId> <version>1.0-SNAPSHOT</version> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>6</source> <target>6</target> </configuration> </plugin> </plugins> </build> <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.1.5.RELEASE</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>org.springframework.transaction</artifactId> <version>3.2.2.RELEASE</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency> </dependencies> </project>
注意:导入依赖时tx版本必须要5.1.5,不然执行queryForObject时会报错
定义Account类,对应数据库account表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.domain; import java.io.Serializable; public class Account implements Serializable { private Integer id; private String name; private Float 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 Float getMoney() { return money; } public void setMoney(Float money) { this.money = money; } }
bean.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" 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.mysql.cj.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://127.0.0.1:3306/eesy?useSSL=false&serverTimezone=GMT%2B8"></property> <property name="username" value="root"></property> <property name="password" value="nimahaoma49"></property> </bean> </beans>
JDBCTemplate的简单使用
掌握三种方法:update,query,queryForObject
package com.itheima.jdbctemplate; import com.itheima.domain.Account; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.DriverManagerDataSource; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; /** * JdbcTemplate的最基本用法 */ public class JdbcTemplateDemo1 { public static void main(String[] args) { //1.获取容器 ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml"); //2.获取对象 JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate"); //3.执行操作 //保存 jt.update("insert into account(name,money) values(?,?)","eee",3333f); //更新 jt.update("update account set name=?,money=? where id=?","test",4567,7); //删除 jt.update("delete from account where id=?",8); //查询所有 //List<Account> accounts = jt.query("select * from account where money>?",new AccountRowMapper(),1000f); List<Account> accounts = jt.query( //直接进行映射 "select * from account where money>?",new BeanPropertyRowMapper<Account>(Account.class),1000f); for(Account account:accounts) System.out.println(account); //查询一个 System.out.println(accounts.get(0)); //查询返回一行一列(使用聚合函数,但不加group by子句) Long count = jt.queryForObject("select count(*) from account",Long.class); System.out.println(count); } } /** * 定义把结果集的元组封装成一个Account的封装策略 */ class AccountRowMapper implements RowMapper<Account>{ /** * 把结果集中的数据封装到Account中,然后由spring把每个Account加到集合中 * @param resultSet * @param i * @return * @throws SQLException */ @Override 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.getFloat("money")); return account; } }
JDBCTemplate在Dao中的使用
在bean.xml里配置dao
<!--配置账户的持久层--> <bean id="accountDao" class="com.itheima.dao.Impl.AccountDaoImpl"> <!--<property name="jdbcTemplate" ref="jdbcTemplate"></property>--> <property name="dataSource" ref="dataSource"></property> </bean>
在dao实现类中使用JDBCTemplate进行数据库操作
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao { @Override public Account findAccountById(Integer accountId) { List<Account> accountList = getJdbcTemplate().query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),accountId); return accountList.isEmpty()?null:accountList.get(0); } @Override public Account findAccountByName(String accountName) { List<Account> accounts = getJdbcTemplate().query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),accountName); if(accounts.isEmpty()){ return null; } if(accounts.size()>1){ throw new RuntimeException("结果集不唯一"); } return accounts.get(0); } @Override public void updateAccount(Account account) { getJdbcTemplate().update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId()); } }
注:继承的JdbcDaoSupport类 内部有一个JDBCTemplate的引用,所以在bean.xml里配置dao时只要把数据源注入即可