Using jdbc to connect to sql server database based on spring boot environment

Preface

Small projects or demos can be solved by using jdbc+sql server. This article uses jdbc to connect to the sql server database based on the spring boot environment, which is consistent with the spring mvc series. To use jdbc in spring boot to connect sql server data, you only need to introduce two jars: spring-boot-starter-jdbc, spring-boot-starter-data-jpa

Project structure

Remains the same as the previous spring boot introduction

pom.xml

fce2022be5e87c17c94245fd7ccbf1d9
    b4b38e33757a6497aa8690936b905cc1
        05a8acc5c31084a4f61ade01873802caorg.springframework.boot192ca2f7b8c770b01c8f81e6bdd5b947
        9bc4cbb67f66e148869423c0d27e5f90spring-boot-starter-webb68fb17cb904a46b73e6272850323873
        64633c609cdcf8ce01e08a6b4a9a10e1-->
            450e5abe4edae9e772114d6a93973ecc-->
                <!--05a8acc5c31084a4f61ade01873802caorg.springframework.boot192ca2f7b8c770b01c8f81e6bdd5b947-->
                <!--9bc4cbb67f66e148869423c0d27e5f90spring-boot-starter-tomcatb68fb17cb904a46b73e6272850323873-->
            1b16f501fedaca7403965c6b4a7024aa-->
        daa28b718598738f6a4e4f17a729f373-->
    09a0e22e5aaafd848ae04665be625b91
    b4b38e33757a6497aa8690936b905cc1
        05a8acc5c31084a4f61ade01873802caorg.springframework.boot192ca2f7b8c770b01c8f81e6bdd5b947
        9bc4cbb67f66e148869423c0d27e5f90spring-boot-starter-cacheb68fb17cb904a46b73e6272850323873
    09a0e22e5aaafd848ae04665be625b91
    b4b38e33757a6497aa8690936b905cc1
        05a8acc5c31084a4f61ade01873802caorg.springframework.boot192ca2f7b8c770b01c8f81e6bdd5b947
        9bc4cbb67f66e148869423c0d27e5f90spring-boot-starter-jdbcb68fb17cb904a46b73e6272850323873
    09a0e22e5aaafd848ae04665be625b91
    b4b38e33757a6497aa8690936b905cc1
        05a8acc5c31084a4f61ade01873802caorg.springframework.boot192ca2f7b8c770b01c8f81e6bdd5b947
        9bc4cbb67f66e148869423c0d27e5f90spring-boot-starter-data-jpab68fb17cb904a46b73e6272850323873
        3d689bd3819ead35ed794427bd12f4591.5.4.RELEASE83a577b3f930c490b31329be5e672d0b
    09a0e22e5aaafd848ae04665be625b91

d233ceef72c18d2307de4871b5eff5ad

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);
    }

    @Overrideprotected 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;


@Repository
public 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;



@Service
public 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\"}";
        }
    }
}

The above is the detailed content of Using jdbc to connect to sql server database based on spring boot environment. For more information, please follow other related articles on the PHP Chinese website!