在MySQL中,创建一个名为spring的数据库:
CREATE DATABASE spring;
USE `spring`;
DROp TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`balance` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
insert into `account`(`id`,`username`,`balance`) values
(1,'zhangsan',100),(3,'lisi',500),(4,'wangwu',300);
创建Maven项目spring4,在pom.xml里引入Spring 基础包和依赖包、MySQL数据库的驱动JAR包、Spring JDBC的JAR包和Spring事务处理的JAR包:
org.springframework
spring-core
5.2.8.RELEASE
org.springframework
spring-beans
5.2.8.RELEASE
org.springframework
spring-context
5.2.8.RELEASE
org.springframework
spring-expression
5.2.8.RELEASE
commons-logging
commons-logging
1.2
org.springframework
spring-aop
5.2.8.RELEASE
org.aspectj
aspectjrt
1.9.1
org.aspectj
aspectjweaver
1.9.6
org.springframework
spring-jdbc
5.2.10.RELEASE
org.springframework
spring-tx
5.2.10.RELEASE
mysql
mysql-connector-java
8.0.11
runtime
在spring4项目的src/main/resources目录下,创建配置文件applicationContext.xml,在该文件中配置数据源Bean和JDBC模板Bean,并将数据源注入到JDBC模板中:
在src/main/java中创建包com.gz,在com.gz中创建测试类TestJdbcTemplate,在该类的main()方法中获取JdbcTemplate实例,然后调用execute()方法执行创建数据表的SQL语句:
package com.gz;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
public class TestJdbcTemplate {
public static void main(String[] args) {
// 初始化spring容器,加载applicationContext.xml配置
ApplicationContext applicationContext = new
ClassPathXmlApplicationContext("applicationContext.xml");
// 通过容器,获取JdbcTemplate的实例
JdbcTemplate jdTemplate =
(JdbcTemplate) applicationContext.getBean("jdbcTemplate");
// 使用execute()方法执行SQL语句,创建用户账户管理表account
jdTemplate.execute("create table account2(" +
"id int primary key auto_increment," +
"username varchar(50)," +
"balance double)");
System.out.println("账户表account创建成功!");
}
}
创建Account类,在该类中定义属性,以及其对应的getter/setter方法:
package com.gz;
public class Account {
private Integer id; // 账户id
private String username; // 用户名
private Double balance; // 账户余额
//省略 get set
public String toString() {
return "Account [id=" + id + ", "
+ "username=" + username +
", balance=" + balance + "]";
}
创建接口AccountDao,并在接口中定义添加、更新和删除账户的方法:
package com.gz;
import java.util.List;
public interface AccountDao {
// 添加
public int addAccount(Account account);
// 更新
public int updateAccount(Account account);
// 删除
public int deleteAccount(int id);
// 通过id查询
public Account findAccountById(int id);
// 查询所有账户
public List findAllAccount();
}
创建AccountDao接口的实现类AccountDaoImpl,并在类中实现添加、更新和删除账户的方法:
package com.itheima;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
public class AccountDaoImpl implements AccountDao {
// 定义JdbcTemplate属性及其setter方法
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
// 添加账户
public int addAccount(Account account) {
// 定义SQL
String sql = "insert into account(username,balance) value(?,?)";
// 定义数组来存放SQL语句中的参数
Object[] obj = new Object[] {
account.getUsername(),
account.getBalance()
};
// 执行添加操作,返回的是受SQL语句影响的记录条数
int num = this.jdbcTemplate.update(sql, obj);
return num;
}
// 更新账户
public int updateAccount(Account account) {
// 定义SQL
String sql = "update account set username=?,balance=? where id = ?";
// 定义数组来存放SQL语句中的参数
Object[] params = new Object[] {
account.getUsername(),
account.getBalance(),
account.getId()
};
// 执行更新操作,返回的是受SQL语句影响的记录条数
int num = this.jdbcTemplate.update(sql, params);
return num;
}
// 删除账户
public int deleteAccount(int id) {
// 定义SQL
String sql = "delete from account where id = ? ";
// 执行删除操作,返回的是受SQL语句影响的记录条数
int num = this.jdbcTemplate.update(sql, id);
return num;
}
// 通过id查询单个账户信息
public Account findAccountById(int id) {
//定义SQL语句
String sql = "select * from account where id = ?";
// 创建一个新的BeanPropertyRowMapper对象
RowMapper rowMapper =
new BeanPropertyRowMapper(Account.class);
// 将id绑定到SQL语句中,并通过RowMapper返回一个Object类型的单行记录
return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
}
//查询所有账户信息
public List findAllAccount() {
// 定义SQL语句
String sql = "select * from account";
// 创建一个新的BeanPropertyRowMapper对象
RowMapper rowMapper =
new BeanPropertyRowMapper(Account.class);
// 执行静态的SQL查询,并通过RowMapper返回结果
return this.jdbcTemplate.query(sql, rowMapper);
}
}
在applicationContext.xml中,定义一个id为accountDao的Bean,用于将jdbcTemplate注入到accountDao实例中:
创建测试类TestAddAccount,该类主要用于添加用户账户信息:
package com.gz;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestAddAccount {
public static void main(String[] args) {
// 加载配置文件
ApplicationContext applicationContext =new
ClassPathXmlApplicationContext("applicationContext.xml");
// 获取AccountDao实例
AccountDao accountDao =
(AccountDao) applicationContext.getBean("accountDao");
// 创建Account对象,并向Account对象中添加数据
Account account = new Account();
account.setUsername("tom");
account.setBalance(1000.00);
// 执行addAccount()方法,并获取返回结果
int num = accountDao.addAccount(account);
if (num > 0) {
System.out.println("成功插入了" + num + "条数据!");
} else {
System.out.println("插入操作执行失败!");
}
}
}
执行完插入操作后,接下来调用JdbcTemplate类的update()方法执行更新操作:
package com.gz;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestUpdateAccount {
public static void main(String[] args) {
// 加载配置文件
ApplicationContext applicationContext =new
ClassPathXmlApplicationContext("applicationContext.xml");
// 获取AccountDao实例
AccountDao accountDao =
(AccountDao) applicationContext.getBean("accountDao");
// 创建Account对象,并向Account对象中添加数据
Account account = new Account();
account.setId(1);
account.setUsername("tom");
account.setBalance(2000.00);
// 执行updateAccount()方法,并获取返回结果
int num = accountDao.updateAccount(account);
if (num > 0) {
System.out.println("成功修改了" + num + "条数据!");
} else {
System.out.println("修改操作执行失败!");
}
}
}
创建测试类TestDeleteAccount,该类主要用于测试删除用户账户信息:
package com.gz;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestDeleteAccount {
public static void main(String[] args) {
// 加载配置文件
ApplicationContext applicationContext =new
ClassPathXmlApplicationContext("applicationContext.xml");
// 获取AccountDao实例
AccountDao accountDao =
(AccountDao) applicationContext.getBean("accountDao");
// 执行deleteAccount()方法,并获取返回结果
int num = accountDao.deleteAccount(1);
if (num > 0) {
System.out.println("成功删除了" + num + "条数据!");
} else {
System.out.println("删除操作执行失败!");
}
}
}
创建测试类FindAccountByIdTest,用于测试条件查询:
package com.gz;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class FindAccountByIdTest {
public static void main(String[] args) {
// 加载配置文件
ApplicationContext applicationContext =new
ClassPathXmlApplicationContext("applicationContext.xml");
// 获取AccountDao实例
AccountDao accountDao =
(AccountDao) applicationContext.getBean("accountDao");
// 执行findAccountById()方法
Account account = accountDao.findAccountById(2);
System.out.println(account);
}
}
创建测试类FindAllAccountTest,用于查询所有用户账户信息:
package com.gz;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class FindAllAccountTest {
public static void main(String[] args) {
// 加载配置文件
ApplicationContext applicationContext =new
ClassPathXmlApplicationContext("applicationContext.xml");
// 获取AccountDao实例
AccountDao accountDao =
(AccountDao) applicationContext.getBean("accountDao");
// 执行findAllAccount()方法,获取Account对象的集合
List account = accountDao.findAllAccount();
// 循环输出集合中的对象
for (Account act : account) {
System.out.println(act);
}
}
}