SpringBoot用JdbcTemplates访问Mysql实例代码

本文介绍springboot通过jdbc访问关系型mysql,通过spring的jdbctemplate去访问。

准备工作

1. jdk 1.8
2. maven 3.0
3. idea
4. mysql

初始化mysql:

-- create table `account`
drop table `account` if exists
create table `account` (
 `id` int(11) not null auto_increment,
 `name` varchar(20) not null,
 `money` double default null,
 primary 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');

创建工程

引入依赖：

在pom文件引入spring-boot-starter-jdbc的依赖：

<dependency>
      <groupid>org.springframework.boot</groupid>
      <artifactid>spring-boot-starter-jdbc</artifactid>
    </dependency>

引入mysql连接类和连接池：

<dependency>
      <groupid>mysql</groupid>
      <artifactid>mysql-connector-java</artifactid>
      <scope>runtime</scope>
    </dependency>

    <dependency>
      <groupid>com.alibaba</groupid>
      <artifactid>druid</artifactid>
      <version>1.0.29</version>
    </dependency>

开启web:

<dependency>
      <groupid>org.springframework.boot</groupid>
      <artifactid>spring-boot-starter-web</artifactid>
    </dependency>

配置相关文件

在application.properties文件配置mysql的驱动类，数据库地址，数据库账号、密码信息。

spring.datasource.driver-class-name=com.mysql.jdbc.driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456

通过引入这些依赖和配置一些基本信息，springboot就可以访问数据库类。

具体编码

实体类

public class account {
  private int id ;
  private string name ;
  private double money;

....省略了getter. setter

}

dao层

public interface iaccountdao {
  int add(account account);

  int update(account account);

  int delete(int id);

  account findaccountbyid(int id);

  list<account> findaccountlist();
}

具体的实现类：

package com.forezp.dao.impl;

import com.forezp.dao.iaccountdao;
import com.forezp.entity.account;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.jdbc.core.beanpropertyrowmapper;
import org.springframework.jdbc.core.jdbctemplate;
import org.springframework.stereotype.repository;

import java.util.list;

/**
 * created by fangzhipeng on 2017/4/20.
 */
@repository
public class accountdaoimpl implements iaccountdao {

  @autowired
  private jdbctemplate jdbctemplate;
  @override
  public int add(account account) {
    return jdbctemplate.update("insert into account(name, money) values(?, ?)",
       account.getname(),account.getmoney());

  }

  @override
  public int update(account account) {
    return jdbctemplate.update("update account set name=? ,money=? where id=?",
        account.getname(),account.getmoney(),account.getid());
  }

  @override
  public int delete(int id) {
    return jdbctemplate.update("delete from table account where id=?",id);
  }

  @override
  public account findaccountbyid(int id) {
    list<account> list = jdbctemplate.query("select * from account where id = ?", new object[]{id}, new beanpropertyrowmapper(account.class));
    if(list!=null && list.size()>0){
      account account = list.get(0);
      return account;
    }else{
      return null;
    }
  }

  @override
  public list<account> findaccountlist() {
    list<account> list = jdbctemplate.query("select * from account", new object[]{}, new beanpropertyrowmapper(account.class));
    if(list!=null && list.size()>0){
      return list;
    }else{
      return null;
    }
  }
}

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
  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.findaccountlist();
  }
}

构建一组restful api来展示

package com.forezp.web;

import com.forezp.entity.account;
import com.forezp.service.iaccountservice;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.web.bind.annotation.*;

import java.util.list;

/**
 * created by fangzhipeng on 2017/4/20.
 */

@restcontroller
@requestmapping("/account")
public class accountcontroller {

  @autowired
  iaccountservice accountservice;

  @requestmapping(value = "/list",method = requestmethod.get)
  public list<account> getaccounts(){
    return accountservice.findaccountlist();
  }

  @requestmapping(value = "/{id}",method = requestmethod.get)
  public account getaccountbyid(@pathvariable("id") int id){
    return accountservice.findaccountbyid(id);
  }

  @requestmapping(value = "/{id}",method = requestmethod.put)
  public string updateaccount(@pathvariable("id")int id , @requestparam(value = "name",required = true)string name,
  @requestparam(value = "money" ,required = true)double money){
    account account=new account();
    account.setmoney(money);
    account.setname(name);
    account.setid(id);
    int t=accountservice.update(account);
    if(t==1){
      return account.tostring();
    }else {
      return "fail";
    }
  }

  @requestmapping(value = "",method = requestmethod.post)
  public string postaccount( @requestparam(value = "name")string name,
                 @requestparam(value = "money" )double money){
    account account=new account();
    account.setmoney(money);
    account.setname(name);
    int t= accountservice.add(account);
    if(t==1){
      return account.tostring();
    }else {
      return "fail";
    }

  }

}

可以通过postman来测试，具体的我已经全部测试通过，没有任何问题。注意restful构建api的风格。

源码下载：https://github.com/forezp/springbootlearning

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。