七十四、Spring与DAO操作 query（）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select * from account;
Empty set (0.00 sec)

mysql> insert into account(username,balance) value("孙悟空",100);
Query OK, 1 row affected (0.04 sec)

mysql> insert into account(username,balance) value("唐僧",1000.00);
Query OK, 1 row affected (0.01 sec)

mysql> insert into account(username,balance) value("猪八戒",2000.00);
Query OK, 1 row affected (0.01 sec)

mysql> insert into account(username,balance) value("沙僧",5000.00);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+-----------+---------+
| id | username  | balance |
+----+-----------+---------+
|  1 | 孙悟空    |     100 |
|  2 | 唐僧      |    1000 |
|  3 | 猪八戒    |    2000 |
|  4 | 沙僧      |    5000 |
+----+-----------+---------+
4 rows in set (0.00 sec)

1
2
3
4
5
	// 通过id查询
	public Account findAccountByid(int id);

	// 查询所有账户
	public List<Account> finfAllccount();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
	// 查询账户信息
	@Override
	public Account findAccountByid(int id) {
		// 定义SQL语句
		String sql = "select * from account where id=?";
		// 创建一个新的rowMapper对象
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		// 将id 绑定到SQL语句中，通过RowMapper返回一个Object类型的当行记录
		return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
	}

	// 查询所有账户信息
	@Override
	public List<Account> finfAllccount() {
		// 定义SQL
		String sql = "select * from account";
		// 创建一个新的rowMapper对象
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		// 执行静态的SQL查询，通过RowMapper返回结果
		return this.jdbcTemplate.query(sql, rowMapper);
	}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.Example.jdbc;

import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class JdbcTemplateTest_delete {
	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("删除操作执行失败！");
		}
	}

	@Test
	public void findAccountByIdTest() {
		// 加载配置文件
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
		// 获取AccountDao实例
		AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
		// 执行findAccountById()方法
		Account account = accountDao.findAccountById(1);
		System.out.println(account);
	}

	@Test
	public void findAllAccountTest() {
		// 加载配置文件
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
		// 获取AccountDao实例
		AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
		// 执行findAllAccount()方法,获取Account对象的集合
		List<Account> account = accountDao.findAllAccount();
		// 循环输出集合中的对象
		for (Account act : account) {
			System.out.println(act);
		}
	}

}