springboot整合jdbc
1.在项目的pom.xml中添加相关依赖
<!--jdbc--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> </dependency> <!--数据库驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <!--Druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.12</version> </dependency>
2.配置数据源:
方法1:在resource下创建jdbc.properties文件 存储数据库的配置信息
jdbc.driver-class-name=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false jdbc.password=xxxxxxx jdbc.username=root
然后就是spring的基于注解配置数据源的那一套了
创建配置类
package com.mc_74120.springbootjdbc.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;
@Configuration
@PropertySource("classpath:jdbc.properties")
public class SpringjdbcConfig {
@Value("${jdbc.username}")
private String userName;
@Value("${jdbc.password}")
private String password;
@Value("${jdbc.url}")
private String url;
@Value("${jdbc.driver-class-name}")
private String className;
@Bean
public DataSource getDataSource(){
DruidDataSource dataSource=new DruidDataSource();
dataSource.setDriverClassName(className);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(password);
return dataSource;
}
}
当springboot扫描到该配置类后 会将数据源加入容器
方法2:将
jdbc.driver-class-name=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false jdbc.password=xxxxxxx jdbc.username=root
写入application.properties文件中
并创建配置类：
package com.mc_74120.springbootjdbc.config; import org.springframework.boot.context.properties.ConfigurationProperties; //@ConfigurationProperties(prefix = "jdbc") //是springboot的注解 不能读取其他配置文件 只能读取springboot的application配置文件 public class JbdcConfiguration { private String driver; private String url; private String password; private String userName; public String getDriver() { return driver; } public String getUrl() { return url; } public String getPassword() { return password; } public String getUserName() { return userName; } public void setDriver(String driver) { this.driver = driver; } public void setUrl(String url) { this.url = url; } public void setPassword(String password) { this.password = password; } public void setUserName(String userName) { this.userName = userName; } } import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.context.properties.EnableConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.PropertySource; import javax.print.DocFlavor; import javax.sql.DataSource; //@Configuration //@EnableConfigurationProperties(JbdcConfiguration.class)//指定加载哪个配置信息属性类 public class JdbcConfig { @Bean public DataSource getDataSource(){ DruidDataSource druidDataSource=new DruidDataSource(); return druidDataSource; } }
读取application.properties中前缀为：jdbc的属性 并把对应的值一一赋值给JbdcConfiguration中的变量(以jdbc为前缀的属性名 去掉jdbc的部分 必须与jdbcConfigration中的变量名一一对应 比如说 driver必须有jdbc.driver与其对应)
JdbcConfig类读取JbdcConfiguration中的值 并创建datasource
方法3:
//@Configuration public class JdbcConfig { //@ConfigurationProperties(prefix = "jdbc") @Bean public DataSource getDataSource(){ DruidDataSource druidDataSource=new DruidDataSource(); return druidDataSource; } }
直接读取 application.perperties中以jdbc为前缀的值 并实例化 datasource
方法4:
spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.datasource.username=root spring.datasource.password=xxxx spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
在application文件中直接配置datasource并加入容器
测试增删改查功能
1.创建实体类:
package com.mc_74120.springbootjdbc.domain;
public class User {
private Integer id;
private String userName;
private String sex;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
2.创建表 略过
3.controller层
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/addUser")
public String addUser(User user){
try {
userService.addUser(user);
}
catch ( Exception e){
e.printStackTrace();
return "error";
}
return "redirect:/ok";
}
@RequestMapping("/selectUser")
public String selectUser(Model model){
List<User> users=userService.selectUser();
model.addAttribute("userList",users);
return "selectUser";
}
@GetMapping("/preUpdate")
public String preUpdate(Integer id,Model model){
try {
User user= userService.findUserById(id);
model.addAttribute("user",user);
}
catch ( Exception e){
e.printStackTrace();
return "error";
}
return "updateUser";
}
@PostMapping("/updateUser")
public String updateUser(User user){
userService.updateUser(user);
return "redirect:/ok";
}
@GetMapping("/deleteUser")
public String deleteUser(Integer id){
try {
userService.deleteUser(id);
}
catch ( Exception e){
e.printStackTrace();
return "error";
}
return "redirect:/ok";
}
}
4.service层：
package com.mc_74120.springbootjdbc.service;
import com.mc_74120.springbootjdbc.domain.User;
import java.util.List;
public interface UserService {
void addUser(User user);
List<User> selectUser();
User findUserById(Integer id);
void updateUser(User user);
void deleteUser(Integer id);
}
package com.mc_74120.springbootjdbc.dao.Impl;
import com.mc_74120.springbootjdbc.dao.UserDao;
import com.mc_74120.springbootjdbc.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void InsertUser(User user) {
String sql = "insert into user(userName,sex) value(?,?)";
jdbcTemplate.update(sql, user.getUserName(), user.getSex());
}
@Override
public List<User> selectUser() {
String sql = "select * from user";
List<User> users = jdbcTemplate.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
String userName= resultSet.getString("userName");
String sex=resultSet.getString("sex");
Integer id=resultSet.getInt("id");
User user=new User();
user.setId(id);
user.setSex(sex);
user.setUserName(userName);
return user;
}
});
return users;
}
@Override
public User findUserById(Integer id) {
String sql="select * from user where id=?";
User user=new User();
Object[] objs=new Object[]{id};
jdbcTemplate.query(sql,objs, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
user.setId(resultSet.getInt("id"));
user.setUserName(resultSet.getString("userName"));
user.setSex(resultSet.getString("sex"));
}
});
return user;
}
@Override
public void updateUser(User user) {
String sql="update user set sex=?,userName=? where id=?";
jdbcTemplate.update(sql,user.getSex(),user.getUserName(),user.getId());
}
@Override
public void deleteUser(Integer id) {
String sql="delete from user where Id=?";
jdbcTemplate.update(sql,id);
}
}
5.dao层
package com.mc_74120.springbootjdbc.dao;
import com.mc_74120.springbootjdbc.domain.User;
import org.springframework.stereotype.Repository;
import java.util.List;
public interface UserDao {
void InsertUser(User user);
List<User> selectUser();
User findUserById(Integer id);
void updateUser(User user);
void deleteUser(Integer id);
}
package com.mc_74120.springbootjdbc.dao.Impl;
import com.mc_74120.springbootjdbc.dao.UserDao;
import com.mc_74120.springbootjdbc.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void InsertUser(User user) {
String sql = "insert into user(userName,sex) value(?,?)";
jdbcTemplate.update(sql, user.getUserName(), user.getSex());
}
@Override
public List<User> selectUser() {
String sql = "select * from user";
List<User> users = jdbcTemplate.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
String userName= resultSet.getString("userName");
String sex=resultSet.getString("sex");
Integer id=resultSet.getInt("id");
User user=new User();
user.setId(id);
user.setSex(sex);
user.setUserName(userName);
return user;
}
});
return users;
}
@Override
public User findUserById(Integer id) {
String sql="select * from user where id=?";
User user=new User();
Object[] objs=new Object[]{id};
jdbcTemplate.query(sql,objs, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
user.setId(resultSet.getInt("id"));
user.setUserName(resultSet.getString("userName"));
user.setSex(resultSet.getString("sex"));
}
});
return user;
}
@Override
public void updateUser(User user) {
String sql="update user set sex=?,userName=? where id=?";
jdbcTemplate.update(sql,user.getSex(),user.getUserName(),user.getId());
}
@Override
public void deleteUser(Integer id) {
String sql="delete from user where Id=?";
jdbcTemplate.update(sql,id);
}
}
浙公网安备 33010602011771号