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

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

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