SpringBoot-JdbcTemplates-MySQL
SpringBoot 中用 JdbcTemplate 访问MySQL
一. 准备工作
- IDEA
- docker : 运行MySql
二. 启动 docker mysql 容器并进行表的创建
启动 docker,查看docker mysql 镜像,启动
- docker images : 查看全部的docker 镜像 - docker run -d -p 3333:3306 -e MYSQL_ROOT_PASSWORD=123 mysql:letest(mysql对应的images : id) - 启动 Navicat 连接mysql - 创建数据库 springboot,创建表 create table account( id int(11) not null auto_increment, name varchar(20) not null, money double default null, premary key(id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO `account` VALUES ('1', 'aaa', '1000'); INSERT INTO `account` VALUES ('2', 'bbb', '1000'); INSERT INTO `account` VALUES ('3', 'ccc', '1000');
三. 使用IDEA 创建 springBoot项目,选择 web,MySQL,jpa
四. 配置相关的文件
配置 application.yml
spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3333/springboot username: root password: 123
- 配置实体类 类名为 Account
DAO层
接口 :public interface IAccountDao { int add(Account account); int update(Account account); int delete(int id); Account findAccountById(int id); List<Account> findAccountList(); }
实现类 :
@Repository public class AccountDao implements IAccountDao { @Autowired private JdbcTemplate jdbcTemplate; @Override public int add(Account account) { int add = jdbcTemplate.update("INSERT INTO account(NAME,money) values(?,?)", new Object[]{account.getName(), account.getMoney()}); return add; } @Override public int update(Account account) { String sql = " update account set name = ?,money = ? where id = ?"; int update = jdbcTemplate.update(sql, new Object[]{account.getName(),account.getMoney(),account.getId()}); return update; } @Override public int delete(int id) { String sql = "delete from account where id = ?"; return jdbcTemplate.update(sql,new Object[]{id}); } @Override public Account findAccountById(int id) { String sql = "select id,name,money from account where id = ?"; List<Account> query = jdbcTemplate.query(sql, new Object[]{id}, new BeanPropertyRowMapper(Account.class)); if (query != null){ return query.get(0); } return null; } @Override public List<Account> findAccountList() { String sql = "select id,name,money from account"; List<Account> accounts = jdbcTemplate.query(sql,new Object[]{}, new BeanPropertyRowMapper(Account.class)); return accounts; } public List<Account> find() { String sql = "select id,name,money from account"; List<Account> accounts = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<Account>() { List<Account> lists = new ArrayList<Account>(); @Override public Account mapRow(ResultSet resultSet, int i) throws SQLException { Account account = new Account(); account.setId(resultSet.getInt(1)); account.setName(resultSet.getString(2)); account.setMoney(resultSet.getDouble(3)); return account; } }); return accounts; } }
service层
接口 :public interface IAccountService { int add(Account account); int update(Account account); int delete(int id); Account findAccountById(int id); List<Account> findAccountList(); }
实现类 :
@Service public class AccountService implements IAccountService { @Autowired private IAccountDao accountDao; @Override public int add(Account account) { return accountDao.add(account); } @Override public int update(Account account) { return accountDao.update(account); } @Override public int delete(int id) { return accountDao.delete(id); } @Override public Account findAccountById(int id) { return accountDao.findAccountById(id); } @Override public List<Account> findAccountList() { return accountDao.find(); }
controller层
控制层@RestController @RequestMapping("account") public class AccountController { @Autowired private IAccountService accountService; @RequestMapping(value = "add",method = RequestMethod.POST) public int insert(@RequestParam(value = "name")String name,@RequestParam(value = "money") Double money){ Account account = new Account(); account.setName(name); account.setMoney(money); System.out.println("add"); int add = accountService.add(account); return add; } @RequestMapping(value = "delete",method = RequestMethod.GET) public int delete(@RequestParam("id") int id){ System.out.println("delete"); return accountService.delete(id); } @RequestMapping("update") public int modify(@RequestParam("id") int id,@RequestParam("name") String name,@RequestParam("money") Double money){ System.out.println("update"); Account account = new Account(); account.setId(id); account.setName(name); account.setMoney(money); return accountService.update(account); } @RequestMapping(value = "/{id}",method = RequestMethod.GET) public Account findOne(@PathVariable("id") int id){ System.out.println("queryById"); return accountService.findAccountById(id); } @RequestMapping(value = "findAll",method = RequestMethod.GET) public List<Account> findAll(){ System.out.println("query"); return accountService.findAccountList(); } }
五. 使用postman进行测试
没有安装的话可以使用 brew cask install postman; 安装完成后直接打开就可以了 全部测试通过;
六. 参考
[SpringBoot非官方教程 | 第三篇:SpringBoot用JdbcTemplates访问Mysql][1]
相关推荐
adsadadaddadasda 2020-08-19
yongyoumengxiang 2020-08-02
thunderstorm 2020-06-06
幸运小侯子 2020-05-25
白净垃圾桶 2020-05-02
csuzxm000 2020-01-08
空杯心态虚怀若谷 2012-08-24
JudeJoo 2019-07-01
seawaycao 2019-07-01
cyAspNet 2019-07-01
tangxiong0 2019-07-01
xiaoxiangyu 2019-06-30
剑铭 2015-07-13
oxuzhenyi 2014-08-20
愤怒的砖头 2013-10-11
Excalibur0 2013-08-06