mysql template sql_SpringBoot-JdbcTemplates-MySQL

配置 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 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 query = jdbcTemplate.query(sql, new Object[]{id}, new BeanPropertyRowMapper(Account.class));

if (query != null){

return query.get(0);

}

return null;

}

@Override

public List findAccountList() {

String sql = "select id,name,money from account";

List accounts = jdbcTemplate.query(sql,new Object[]{}, new BeanPropertyRowMapper(Account.class));

return accounts;

}

public List find() {

String sql = "select id,name,money from account";

List accounts = jdbcTemplate.query(sql, new Object[]{}, new RowMapper() {

List lists = new ArrayList();

@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 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 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 findAll(){

System.out.println("query");

return accountService.findAccountList();

}

}