springboot使用jdbcTemplate连接数据库
1.pom.xml:
4.0.0 com.example spring-data_JpaAndJdbcTemplate 0.0.1-SNAPSHOT jar spring-mybatis Demo project for Spring Boot org.springframework.boot spring-boot-starter-parent 1.5.10.RELEASE UTF-8 UTF-8 1.8 org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-logging org.springframework.boot spring-boot-starter-actuator org.springframework.boot spring-boot-starter-log4j org.springframework.boot spring-boot-starter-web mysql mysql-connector-java 5.1.21 org.springframework.boot spring-boot-starter-data-jpa org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-maven-plugin
2.application.properties:
spring.datasource.url=jdbc:mysql://localhost:3306/testspring.datasource.username=rootspring.datasource.password=rootspring.datasource.driver-class-name=com.mysql.jdbc.Driver
3.Student:
package com.example.springmybatis.entity;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;@Entitypublic class Student { @Id @GeneratedValue private String name; @Column(nullable = true) private int age; @Column(nullable = true) private String address; private String collage; private String sex; public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getCollage() { return collage; } public void setCollage(String collage) { this.collage = collage; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } @Override public String toString() { return "Student{" + "name='" + name + '\'' + ", age=" + age + ", address='" + address + '\'' + ", collage='" + collage + '\'' + ", sex='" + sex + '\'' + '}'; }} 4.UserService:
package com.example.springmybatis.service_jdbc;import com.example.springmybatis.entity.Student;public interface UserService { void create(String name, Integer age); void deleteByName(String name); Integer getAllUsers(); void deleteAllUsers(); Student select(String name, int age);} 5.UserServiceImpl:
package com.example.springmybatis.service_jdbc;import com.example.springmybatis.entity.Student;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.stereotype.Service;import java.sql.ResultSet;import java.sql.SQLException;@Servicepublic class UserServiceImpl implements UserService { @Autowired private JdbcTemplate jdbcTemplate; @Override public void create(String name, Integer age) { jdbcTemplate.update("insert into student(NAME, AGE) values(?, ?)", name, age); } @Override public void deleteByName(String name) { jdbcTemplate.update("delete from student where NAME = ?", name); } @Override public Integer getAllUsers() { return jdbcTemplate.queryForObject("select count(1) from student", Integer.class); } @Override public void deleteAllUsers() { jdbcTemplate.update("delete from student"); } @Override public Student select(String name, int age) { return jdbcTemplate.queryForObject("select name, age, address, collage, sex from student where NAME = ? and age = ?", new MyRowMapper(), name, age); } public class MyRowMapper implements RowMapper { @Override public Student mapRow(ResultSet resultSet, int i) throws SQLException { String name = resultSet.getString("name"); int age = resultSet.getInt("age"); String address = resultSet.getString("address"); String collage = resultSet.getString("collage"); String sex = resultSet.getString("sex"); Student student = new Student(); student.setName(name); student.setAddress(address); student.setAge(age); student.setCollage(collage); student.setSex(sex); return student; } }} 6.UserController:
package com.example.springmybatis.Controller;import com.example.springmybatis.entity.Student;import com.example.springmybatis.service_jdbc.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*;import java.util.List;@RestControllerpublic class UserController { @Autowired private UserService userService; /** * JdbcTemplate连接方式 * @param name * @param age * @return * @throws Exception */ @RequestMapping(value = "/jdbc", method = RequestMethod.GET) @ResponseBody public Student GetNameJdbc(@RequestParam(value = "name", defaultValue = "易中天") String name , @RequestParam(value = "age", defaultValue = "55") int age)throws Exception{ Student user = userService.select(name, age); return user; }} 7.spring boot启动项：
package com.example.springmybatis;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplicationpublic class SpringDataJpaAPP { public static void main(String[] args) { SpringApplication.run(SpringDataJpaAPP.class, args); }} 8.sql:
CREATE TABLE `student` ( `name` varchar(100) NOT NULL, `age` int(10) NOT NULL, `address` varchar(100) NOT NULL, `collage` varchar(100) NOT NULL, `sex` varchar(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8