spring boot(二): spring boot+jdbctemplate+sql server

標籤：where   nts   val   ...   dem   style   value   cto   san   

前言

       小項目或者做demo時可以使用jdbc+sql server解決即可，這篇就基於spring boot環境使用jdbc串連sql server資料庫，和spring mvc系列保持一致。 在spring boot中使用jdbc 串連sql server資料只需要引入兩個jar：spring-boot-starter-jdbc、spring-boot-starter-data-jpa

 

項目結構

     和上篇spring boot入門保持相同

 

pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <!--<exclusions>-->
            <!--<exclusion>-->
                <!--<groupId>org.springframework.boot</groupId>-->
                <!--<artifactId>spring-boot-starter-tomcat</artifactId>-->
            <!--</exclusion>-->
        <!--</exclusions>-->
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-cache</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <version>1.5.4.RELEASE</version>
    </dependency>
   
</dependencies>

 

Application.java、Dao、Service、model

1、Application.java

@SpringBootApplication(scanBasePackages = "com.autohome")public class Application extends SpringBootServletInitializer{    public static void main(String[] args){        System.out.println("server is running at 8080....");        SpringApplication.run(Application.class,args);    }    @Override    protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {        return builder.sources(Application.class);    }}

 

2、Dao

package com.autohome.dao;import com.autohome.model.User;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.PreparedStatementSetter;import org.springframework.stereotype.Repository;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Types;import java.util.List;@Repositorypublic class UserDao {    @Autowired    JdbcTemplate jdbcTemplate;    public List<User> listAllUser() {        List<User> list = jdbcTemplate.query("select * from t_userinfo",new User());        return list;    }    public int insertUser(final User user) {        int result = jdbcTemplate.update("insert into t_userinfo (name,address) VALUES (?,?)", new PreparedStatementSetter() {            public void setValues(PreparedStatement ps) throws SQLException {                ps.setString(1,user.getName());                ps.setString(2,user.getAddress());            }        });        return result;    }    public int updateUser(final User user) {        int result = jdbcTemplate.update("UPDATE t_userinfo set name=?,address=? where id=?", new PreparedStatementSetter() {            public void setValues(PreparedStatement ps) throws SQLException {                ps.setString(1,user.getName());                ps.setString(2,user.getAddress());                ps.setInt(3,user.getId());            }        });        return result;    }    public int deleteUser(int id) {        int result = jdbcTemplate.update("delete from t_userinfo where id=?",new Object[]{id},new int[]{Types.INTEGER});        return result;    }}

3、Service

package com.autohome.service;import com.autohome.dao.UserDao;import com.autohome.model.User;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;@Servicepublic class UserService {    @Autowired    UserDao userDao;    public List<User> listAllUser(){        return userDao.listAllUser();    }    public int insertUser(User user){        return userDao.insertUser(user);    }    public int updateUser(User user){        return userDao.updateUser(user);    }    public int deleteUser(int id){        return userDao.deleteUser(id);    }}

4、Controller

package com.autohome.controller;import com.autohome.model.User;import com.autohome.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.ResponseBody;import java.util.List;/** * Created by zhangfei on 2017/6/22. */@Controller@RequestMapping("/user")public class UserController {    @ResponseBody    @RequestMapping("/detail")    public  User detail(Integer id){        User user=new User();        user.setId(id);        user.setName("zhangsan");        user.setAddress("china");        return user;    }    @Autowired    UserService userService;    @ResponseBody    @RequestMapping("/list")    public List<User> list(){        List<User> list = userService.listAllUser();        System.out.println("size:"+list.size());        return list;    }    @RequestMapping(value="/insert",method = RequestMethod.POST)    public String insertUser(String name,String address){        User user =new User();        user.setName(name);        user.setAddress(address);        int result = userService.insertUser(user);        if(result>0){            return "{\"returncode\":0,\"message\":\"success\"}";        }else{            return "{\"returncode\":0,\"message\":\"error\"}";        }    }}

　

　

spring boot(二): spring boot+jdbctemplate+sql server