Article Three: springboot access MySQL with jdbctemplates

Tag: Engine relies on LIB Public 1.0 Bean ack run UID

This article describes springboot access to relational MySQL through JDBC, through the jdbctemplate of spring.

Preparatory work

• JDK 1.8
• Maven 3.0
• Idea
• Mysql

Initialize MySQL:

-- CREATE table ' account ' DROP table ' account ' IF existscreate table ' account '  (int(one) not NULL Auto_increment,  ' name ' varchar (notnull)  ,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 ', ' ' + '); insert INTO ' account ' values (' 3 ', ' CCC ', ' 1000 ') );

Create a project to introduce dependencies:

The SPRING-BOOT-STARTER-JDBC dependency is introduced in the Pom file:

<dependency>            <groupId>org.springframework.boot</groupId>            <artifactId> Spring-boot-starter-jdbc</artifactid>        </dependency>

Introduce MySQL connection classes and connection pooling:

<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>

To open the Web:

<dependency>            <groupId>org.springframework.boot</groupId>            <artifactId> Spring-boot-starter-web</artifactid>        </dependency>

Configure related files

In the Application.properties file, configure the MySQL driver class, database address, database account, password information.

spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql: // localhost:3306/testspring.datasource.username=rootspring.datasource.password=123456

By introducing these dependencies and configuring some basic information, Springboot can access the database classes.

Specific coded entity classes

 Public class Account {    privateint  ID;     Private String name;     Private Double Money , ..... The getter is omitted. Setter}

DAO layer

 Public Interface Iaccountdao {    int  Add (account account);     int Update (account account);     int Delete (int  ID);    Account Findaccountbyid (int  ID);    List<Account> findaccountlist ();}

The specific implementation class:

 PackageCom.forezp.dao.impl;ImportCom.forezp.dao.IAccountDAO;ImportCom.forezp.entity.Account;Importorg.springframework.beans.factory.annotation.Autowired;ImportOrg.springframework.jdbc.core.BeanPropertyRowMapper;Importorg.springframework.jdbc.core.JdbcTemplate;Importorg.springframework.stereotype.Repository;Importjava.util.List;/*** Created by Fangzhipeng on 2017/4/20.*/@Repository Public classAccountdaoimplImplementsIaccountdao { @Autowired PrivateJdbcTemplate JdbcTemplate; @Override Public intAdd (Account account) {returnJdbctemplate.update ("INSERT into account (name, money) VALUES (?,?)", Account.getname (), Account.getmoney ()); } @Override Public intUpdate (account account) {returnJdbctemplate.update ("Update account SET name=?, money=?") WHERE id=? ", Account.getname (), Account.getmoney (), Account.getid ()); } @Override Public intDeleteintID) {returnJdbctemplate.update ("DELETE from TABLE account where id=?"), id); } @Override PublicAccount Findaccountbyid (intID) {List<Account> list = Jdbctemplate.query ("SELECT * from account where id =?",NewObject[]{id},NewBeanpropertyrowmapper (account.class)); if(list!=NULL&& list.size () >0) { account account= List.get (0); returnAccount ; }Else{            return NULL; }} @Override PublicList<account>findaccountlist () {List<Account> list = Jdbctemplate.query ("SELECT * From Account",Newobject[]{},NewBeanpropertyrowmapper (account.class)); if(list!=NULL&& list.size () >0){            returnlist; }Else{            return NULL; }    }}

Service Layer

 Public Interface Iaccountservice {    int  Add (account account);     int Update (account account);     int Delete (int  ID);    Account Findaccountbyid (int  ID);    List<Account> findaccountlist ();}

Specific implementation classes:

@Service Public classAccountserviceImplementsIaccountservice {@Autowired Iaccountdao Accountdao; @Override Public intAdd (Account account) {returnAccountdao.add (account); } @Override Public intUpdate (account account) {returnaccountdao.update (account); } @Override Public intDeleteintID) {returnaccountdao.delete (ID); } @Override PublicAccount Findaccountbyid (intID) {returnAccountdao.findaccountbyid (ID); } @Override PublicList<account>findaccountlist () {returnaccountdao.findaccountlist (); }}

Build a set of restful APIs to showcase

 PackageCom.forezp.web;ImportCom.forezp.entity.Account;ImportCom.forezp.service.IAccountService;Importorg.springframework.beans.factory.annotation.Autowired;Importorg.springframework.web.bind.annotation.*;Importjava.util.List;/*** Created by Fangzhipeng on 2017/4/20.*/@RestController @requestmapping ("/account") Public classAccountController { @Autowired iaccountservice accountservice; @RequestMapping (Value = "/list", method = Requestmethod.get) PublicList<account>getaccounts () {returnaccountservice.findaccountlist (); } @RequestMapping (Value= "/{id}", method =requestmethod.get) PublicAccount Getaccountbyid (@PathVariable ("id")intID) {        returnAccountservice.findaccountbyid (ID); } @RequestMapping (Value= "/{id}", method =requestmethod.put) PublicString Updateaccount (@PathVariable ("id")intID, @RequestParam (value = "name", required =true) String name, @RequestParam (value= "Money", required =true)DoubleMoney ) { Account Account=NewAccount ();        Account.setmoney (Money);        Account.setname (name);        Account.setid (ID); intt=accountservice.update (account); if(t==1){            returnaccount.tostring (); }Else {            return"Fail"; }} @RequestMapping (Value= "", method =requestmethod.post) PublicString Postaccount (@RequestParam (value = "Name") String name, @RequestParam (value= "Money")DoubleMoney ) { Account Account=NewAccount ();        Account.setmoney (Money);        Account.setname (name); intt=Accountservice.add (account); if(t==1){            returnaccount.tostring (); }Else {            return"Fail"; }    }}

Complete code: http://pan.baidu.com/s/1nuPQC8x

Article Three: springboot access MySQL with jdbctemplates