欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SpringBoot用JdbcTemplates访问Mysql实例代码

程序员文章站 2024-02-23 22:30:10
本文介绍springboot通过jdbc访问关系型mysql,通过spring的jdbctemplate去访问。 准备工作 jdk 1.8 maven 3...

本文介绍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

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