spring boot怎么配置数据库 spring boot自带的数据库

转载

mob6454cc68959c 2023-09-21 10:28:32

文章标签 spring boot怎么配置数据库 spring bc java 文章分类 spring boot 后端开发

配置数据源

在依赖于Spring boot的spring-boot-starter-data-jpa后，就会默认为你配置数据源，这些默认的数据源主要是内存数据库，如h2,hqldb和Derby等内存数据，有时候这些数据源不满足我们的需求，需要配置自己的数据源。

启动默认数据源，以h2为例
只需在maven中加入它的依赖，如下代码所示：

<dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
</dependency>

这样就可以不使用任何配置数据库的情况下运行spring boot工程了。

配置自定义数据源，以MySQL为例
首先配置依赖

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<!-- 配置MySQL依赖-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<!-- 配置DBCP依赖-->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
		</dependency>

这样做还不够，还需要配置数据库相关信息才能连接到数据库，还要配置application.properties文件已达到配置数据源的效果

spring.datasource.url=jdbc:mysql://localhost:3306/chapter05?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
#指定数据库连接池的类型
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
#最大等待连接中的数量，设0没有限制
spring.datasource.dbcp2.max-idle=10
#最大连接活动数
spring.datasource.dbcp2.max-total=50
#最大等待毫秒数，单位为ms，超过时间会出错误信息
spring.datasource.dbcp2.max-wait-millis=10000
#数据库连接池初始化连接数
spring.datasource.dbcp2.initial-size=5

指定数据库连接池的类型：POOL和DBCP，这里指定DBCP。

监测数据库连接池的类型

package com.springboot.chapter05.db;


import javax.sql.DataSource;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
/**
 * 监测数据库连接池类型
 * @author Administrator
 *
 */
@Component
//实现spring bean生命周期接口ApplicationContextAware
public class DataSourceShow implements ApplicationContextAware {
	ApplicationContext applicationContext = null;
	//spring容器会自动调用这个方法，注入spring IOC容器
	@Override
	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
		// TODO Auto-generated method stub
		this.applicationContext = applicationContext;
		DataSource bean = applicationContext.getBean(DataSource.class);
		System.out.println(bean.getClass().getName());
	}
}

结果org.apache.commons.dbcp2.BasicDataSource

使用Jdbc Template操作数据库

用户pojo

package com.springboot.chapter05.pojo;

import com.springboot.chapter05.enumeration.SexEnum;
public class JDBCUser {
	private Integer id;
	private String user_name; 
	private SexEnum sex;//枚举
	private String note;
	//get和set方法
	@Override
	public String toString() {
		return "JDBCUser [id=" + id + ", user_name=" + user_name + ", sex=" + sex + ", note=" + note + "]";
	}
}

性别枚举类

package com.springboot.chapter05.enumeration;

public enum SexEnum {
	MALE(1,"男"),
	FEMALE(2,"女");
	
	private int id;
	private String name;
	SexEnum(int id,String name){
		this.id=id;
		this.name=name;
	}
	public static SexEnum getEnumById(int id) {
		for(SexEnum sex : SexEnum.values()) {
			if(sex.getId()==id) {
				return sex;
			}
		}
		return null;
	}
	//get和set方法
}

定义用户服务接口

package com.springboot.chapter05.service;

import java.util.List;

import com.springboot.chapter05.pojo.JDBCUser;

public interface JdbcTmplUserService {
	public JDBCUser getUser(Integer id);
	public List<JDBCUser> findUsers(String user_name,String note);
	public int insertUser(JDBCUser user);
	public int updateUser(JDBCUser user);
	public int deleteUser(Integer id);
}

实现用户接口

package com.springboot.chapter05.service.impl;

import java.sql.ResultSet;
import java.util.List;

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 com.springboot.chapter05.enumeration.SexEnum;
import com.springboot.chapter05.pojo.JDBCUser;
import com.springboot.chapter05.service.JdbcTmplUserService;
@Service
public class JdbcTmplUserServiceImpl implements JdbcTmplUserService {
	@Autowired
	private JdbcTemplate jdbcTemplate = null;
	
	//获取映射关系
	private RowMapper<JDBCUser> getUserMapper(){
		RowMapper<JDBCUser> userRowMapper = (ResultSet rs,int rownum) ->{
			JDBCUser user = new JDBCUser();
			user.setId(rs.getInt("id"));
			user.setUser_name(rs.getString("user_name"));
			user.setNote(rs.getString("note"));
			int sexId = rs.getInt("sex");
			SexEnum sexEnum = SexEnum.getEnumById(sexId);
			user.setSex(sexEnum);
			return user;
		};
		return userRowMapper;
	}
	@Override
	public JDBCUser getUser(Integer id) {
		// TODO Auto-generated method stub
		String sql = "select id,user_name,sex,note from t_user where id = ?";
		Object[] params = new Object[]{id};
		JDBCUser user = jdbcTemplate.queryForObject(sql, params, getUserMapper());
		return user;
	}
	@Override
	public List<JDBCUser> findUsers(String user_name, String note) {
		// TODO Auto-generated method stub
		String sql = "select id,user_name,sex,note from t_user where user_name like concat('%',?,'%') and note like concat('%',?,'%')";
		Object[] params = new Object[] {user_name,note};
		List<JDBCUser> list = jdbcTemplate.query(sql, params, getUserMapper());
		return list;
	}
	@Override
	public int insertUser(JDBCUser user) {
		// TODO Auto-generated method stub
		String sql = "insert into t_user (user_name,sex,note) values (?,?,?)";
		int i = jdbcTemplate.update(sql, user.getUser_name(),user.getSex().getId(),user.getNote());
		return i;
	}
	@Override
	public int updateUser(JDBCUser user) {
		// TODO Auto-generated method stub
		String sql = "update t_user set user_name = ?,note = ? where id = ?";
		int i = jdbcTemplate.update(sql, user.getUser_name(),user.getNote(),user.getId());
		return i;
	}
	@Override
	public int deleteUser(Integer id) {
		// TODO Auto-generated method stub
		String sql = "delete from t_user where id = ?";
		int i = jdbcTemplate.update(sql, id);
		return i;
	}
}

对jdbctemplate的映射关系是需要自己去实现Rowmapper接口，这样就可以完成数据库数据到POJO对象的映射了。在getUserMapper中使用了lambda表达式。

使用JPA（Hibernate）操作数据

在maven中引入了spring-boot-starter-data-jpa,这样便能够使用JPA编程
定义用户POJO

package com.springboot.chapter05.pojo;

import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import com.springboot.chapter05.converter.SexConverter;
import com.springboot.chapter05.enumeration.SexEnum;
//标明是一个实体类
@Entity(name = "user")
//定义映射的表
@Table(name = "t_user")
public class JPAUser {
	@Id	//标明主键
	@GeneratedValue(strategy = GenerationType.IDENTITY)//注解策略，递增
	private Integer id;
	@Column(name = "user_name") //定义属性和表的映射关系，当属性名和数据库字段名相同时，就不需要定义
	private String userName; 
	@Convert(converter = SexConverter.class)	//定义转换器
	private SexEnum sex;
	private String note;
	//get和set方法
	@Override
	public String toString() {
		return "JPAUser [id=" + id + ", user_name=" + userName + ", sex=" + sex + ", note=" + note + "]";
	}
}

使用注解@Entity标明这是一个实体类，@Table配置的属性name指出它映射数据库的表，这样实体就映射到了对应的表上，@Id标注那个属性为表的键，注解@GeneratedValue则是可以配置采用何种策略生成注解，@Column使属性名称和数据库字段一一对应。这里性别需要特殊的转换，使用@Convert指定了SexConvert作为转换器

package com.springboot.chapter05.converter;

import javax.persistence.AttributeConverter;

import com.springboot.chapter05.enumeration.SexEnum;

public class SexConverter implements AttributeConverter<SexEnum, Integer> {
	//将枚举转换成数据库列
	@Override
	public Integer convertToDatabaseColumn(SexEnum attribute) {
		// TODO Auto-generated method stub
		return attribute.getId();
	}
	//将数据库列转换成枚举
	@Override
	public SexEnum convertToEntityAttribute(Integer dbData) {
		// TODO Auto-generated method stub
		return SexEnum.getEnumById(dbData);
	}
}

定义JPA接口扩展JpaRespository可以获得JPA提供的方法

package com.springboot.chapter05.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.springboot.chapter05.pojo.JPAUser;
/**
 * 定义JpaRepository便可以获得JPA提供的事务管理的方法
 * @author Administrator
 *
 */
public interface JpaUserRepository extends JpaRepository<JPAUser, Integer>{
	/**
	 * 按照用户名称模糊查询
	 * @param userName
	 * @return
	 */
	List<JPAUser> findByUserNameLike(String userName);
	/**
	 * 根据主键ID查询
	 * @param id
	 * @return
	 */
	JPAUser getUserById(Integer id);
	/**
	 * 根据用户名或备注进行模糊查询
	 * @param user_name
	 * @param note
	 * @return
	 */
	List<JPAUser> findByUserNameLikeOrNoteLike(String userName,String note);
}

package com.springboot.chapter05.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.ResponseBody;

import com.springboot.chapter05.dao.JpaUserRepository;
import com.springboot.chapter05.pojo.JPAUser;

@Controller
@RequestMapping("/jpa")
public class JPAUserController {
	@Autowired
	private JpaUserRepository jpaUserRepository;
	
	@RequestMapping("/getUser")
	@ResponseBody
	public JPAUser getUser (Integer id) {
		JPAUser jpaUser = jpaUserRepository.findById(id).get();
		return jpaUser;
	}
	
	@RequestMapping("/findAll")
	@ResponseBody
	public Map<String,Object> findAll (){
		List<JPAUser> list = this.jpaUserRepository.findAll();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("success", list);
		return map;
	}
	@RequestMapping("/findByUserNameLike")
	@ResponseBody
	public List<JPAUser> findByUserNameLike(String userName){
		List<JPAUser> list = jpaUserRepository.findByUserNameLike("%"+userName+"%");
		return list;
	}
	@RequestMapping("/getUserById")
	@ResponseBody
	public JPAUser getUserById(Integer id){
		JPAUser jpaUser = jpaUserRepository.getUserById(id);
		return jpaUser; 
	}
	@RequestMapping("/findByUserNameLikeOrNoteLike")
	@ResponseBody
	public List<JPAUser> findByUserNameLikeOrNoteLike(String userName,String note){
		
		List<JPAUser> list = this.jpaUserRepository.findByUserNameLikeOrNoteLike("%"+userName+"%", "%"+note+"%");
		return list;
	}
}

配置springboot启动文件

//定义JPA接口扫描包路径
@EnableJpaRepositories(basePackages = "com.springboot.chapter05.dao")
//定义Bean扫描路径
@EntityScan(basePackages = "com.springboot.chapter05.pojo")

配置JPA属性

#使用数据库方言
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
#打印数据库sql
spring.jpa.show-sql=true
#选择hibernate数据定义语言（DDL）策略为update
spring.jpa.hibernate.ddl-auto=update

整合Mybatis

引入关于mybatis的starter

<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.1</version>
		</dependency>

定义pojo，指定别名

package com.springboot.chapter05.pojo;

import org.apache.ibatis.type.Alias;

import com.springboot.chapter05.enumeration.SexEnum;
@Alias(value = "user")	//mybatis指定别名
public class User {
	private Integer id;
	private String user_name; 
	private SexEnum sex;
	private String note;
	//get和set方法
	@Override
	public String toString() {
		return "JDBCUser [id=" + id + ", user_name=" + user_name + ", sex=" + sex + ", note=" + note + "]";
	}
}

类型转换
在pojo中有一个枚举类型，需要通过typehandler进行类型转换

package com.springboot.chapter05.typeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import com.springboot.chapter05.enumeration.SexEnum;

//声明jdbcType为整型
@MappedJdbcTypes(JdbcType.INTEGER)
//声明javatype为SexEnum
@MappedTypes(value = SexEnum.class)
public class SexTypeHandler extends BaseTypeHandler<SexEnum> {
	//通过列明获取性别
	@Override
	public SexEnum getNullableResult(ResultSet rs, String columnName) throws SQLException  {
		// TODO Auto-generated method stub
		int sex = rs.getInt(columnName);
		if(sex!=1&&sex!=2) {
			return null;
		}
		return SexEnum.getEnumById(sex);
	}
	//通过下标获取性别
	@Override
	public SexEnum getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		int sex = rs.getInt(columnIndex);
		if(sex!=1&&sex!=2) {
			return null;
		}
		return SexEnum.getEnumById(sex);
	}
	//通过存储过程获取性别
	@Override
	public SexEnum getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		int sex = cs.getInt(columnIndex);
		if(sex!=1&&sex!=2) {
			return null;
		}
		return SexEnum.getEnumById(sex);
	}
	//设置非空性别参数
	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, SexEnum sex, JdbcType jdbcType)
			throws SQLException {
		// TODO Auto-generated method stub
		ps.setInt(i, sex.getId());
	}
}

配置用户映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.springboot.chapter05.dao.MyBatisUserDao">
	<select id="getUser" parameterType="Integer" resultType="com.springboot.chapter05.pojo.User">
		select id,user_name,sex,note from t_user where id=#{id}
	</select>
</mapper>

定义mybatis操作接口

package com.springboot.chapter05.dao;

import org.springframework.stereotype.Repository;

import com.springboot.chapter05.pojo.User;
@Repository
public interface MyBatisUserDao {
	public User getUser(Integer id);
}

配置映射文件和扫描别名

#mybatis映射文件通配
mybatis.mapper-locations=classpath:com/springboot/chapter05/mapper/*.xml
#mybatis扫描别名包，和注解@Alias联用
mybatis.type-aliases-package=com.springboot.chapter05.pojo
#配置typeHandler的扫描包
mybatis.type-handlers-package=com.springboot.chapter05.typeHandler

在启动类中使用@MapperScan定义扫描

//定义mybatis扫描
@MapperScan(
		//指定扫描包
		basePackages = "com.springboot.chapter05.*",
		//指定sqlsessionfactory，如果sqlsessiontemplate存在，则作废
		sqlSessionFactoryRef = "sqlSessionFactory",
		//指定sqlsessiontemplate，如果sqlsessionfactory存在，则作废
		sqlSessionTemplateRef = "sqlSessionTemplate",
		annotationClass = Repository.class)

@MapperScan允许通过扫描加载mybatis的mapper。

拦截器配置

package com.springboot.chapter05.plugin;

import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
@Intercepts(
	@Signature(type = StatementHandler.class, args = { Connection.class,Integer.class }, method = "prepare")
)
public class MyPlugin implements Interceptor {
	Properties properties = null;
	//拦截方法逻辑
	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		// TODO Auto-generated method stub
		System.out.println("插件拦截方法**************");
		return invocation.proceed();
	}
	//生成mybatis拦截器对象
	@Override
	public Object plugin(Object target) {
		// TODO Auto-generated method stub
		return Plugin.wrap(target, this);
	}
	//设置插件属性
	@Override
	public void setProperties(Properties properties) {
		// TODO Auto-generated method stub
		this.properties = properties;
	}
}

在application.properties文件增加下面的配置

mybatis.config-location=classpath:mybatis/mybatis-config.xml

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<plugins>
		<plugin interceptor="com.springboot.chapter05.plugin.MyPlugin">
			<property name="key1" value="key1"/>
			<property name="key2" value="key2"/>
			<property name="key3" value="key3"/>
		</plugin>
	</plugins>
</configuration>

如果不使用配置文件，也可以使用编码的形式处理

public class Chapter05Application {
	@Autowired
	SqlSessionFactory sqlSessionFactory = null;
	public void initMyBatis() {
		//插件实例
		Interceptor interceptor = new MyPlugin();
		//设置插件属性
		Properties properties = new Properties();
		properties.setProperty("key1", "value1");
		properties.setProperty("key2", "value2");
		properties.setProperty("key3", "value3");
		interceptor.setProperties(properties);
		//在sqlsessionfactory中添加插件
		sqlSessionFactory.getConfiguration().addInterceptor(interceptor);
	}
	public static void main(String[] args) {
		SpringApplication.run(Chapter05Application.class, args);
	}
}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。