Spring 2.4.5 + Maven + Hibernate + Sharding Jdbc

最新推荐文章于 2023-04-21 14:00:00 发布

66565906

最新推荐文章于 2023-04-21 14:00:00 发布

阅读量718

点赞数

分类专栏：数据库文章标签： postgresql

本文链接：https://blog.csdn.net/G66565906/article/details/116026766

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

POM

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.5</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>

			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>

		</dependency>
		<dependency>

			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-ehcache</artifactId>

		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>

		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>


		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>5.2.2.RELEASE</version>
		</dependency>


		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
			<version>5.2.2.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
		</dependency>

		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>4.1.1</version>
		</dependency>


		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP-java7</artifactId>
			<version>2.4.11</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop"

	xmlns:context="http://www.springframework.org/schema/context"

	xsi:schemaLocation="http://www.springframework.org/schema/beans
					       http://www.springframework.org/schema/beans/spring-beans.xsd
					       http://www.springframework.org/schema/context
					       http://www.springframework.org/schema/context/spring-context.xsd
					       http://www.springframework.org/schema/tx
					       http://www.springframework.org/schema/tx/spring-tx.xsd				          
						   http://www.springframework.org/schema/mvc
						   http://www.springframework.org/schema/mvc/spring-mvc.xsd
						   http://www.springframework.org/schema/aop
				           http://www.springframework.org/schema/aop/spring-aop.xsd
				           ">
	<!--配置hibernate sessionFacotry -->
	<bean id="sessionFactory" name="sessionFactory"
		class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />


		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>

				<prop key="hibernate.cache.use_second_level_cache">true</prop>
				<prop key="cache.provider_class">org.hibernate.cache.ehcache.EhCacheRegionFactory
				</prop>
				<prop key="hibernate.cache.use_query_cache">true</prop>

				<prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory
				</prop>

				<prop key="hibernate.show_sql">false</prop>
				<prop key="hibernate.hbm2ddl.auto">update</prop> 
			</props>
		</property>

		<property name="packagesToScan">
			<list>
				<value>com.example.</value> 
			</list>
		</property> 
	</bean>

	<!--配置事务管理器 -->
	<bean id="txManager"
		class="org.springframework.orm.hibernate5.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	<tx:annotation-driven
		transaction-manager="txManager" />
	<!-- 强制开启CGLIB代理 --> 
	<aop:aspectj-autoproxy proxy-target-class="true" /> 

</beans>

application.properties

spring.main.allow-bean-definition-overriding=true

Sharding-JDBC配置

强制路由

	static Map<String, DataSource> createDataSourceMap() throws SQLException {

		Map<String, DataSource> dataSourceMap = new HashMap<>();

		HikariDataSource result = new HikariDataSource();
		result.setDriverClassName("org.postgresql.Driver");

		result.setJdbcUrl("jdbc:postgresql://192.168.0.120/ds0");
		result.setUsername("postgres");
		result.setPassword("acmeagle");
		dataSourceMap.put("ds0", result);

		result = new HikariDataSource();
		result.setDriverClassName("org.postgresql.Driver");
		result.setJdbcUrl("jdbc:postgresql://192.168.0.120/ds1");
		result.setUsername("postgres");
		result.setPassword("acmeagle");
		dataSourceMap.put("ds1", result);

		return dataSourceMap;
	}

	@Bean
	public DataSource dataSource() throws SQLException {

		HintShardingStrategyConfiguration hint = new HintShardingStrategyConfiguration(new MyHintShardingAlgorithm()); 
 
		TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("Testuser", "ds${0..1}.Testuser${0..1}");

		orderTableRuleConfig.setDatabaseShardingStrategyConfig(hint);
		orderTableRuleConfig.setTableShardingStrategyConfig(hint);
		 
		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
		shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

		Properties ps = new Properties();

		ps.put("sql.show", true);

		 
		return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, ps);

	}

MyHintShardingAlgorithm.java

package com.example.demo;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.shardingsphere.api.sharding.ShardingValue;
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;

 
public class MyHintShardingAlgorithm  implements HintShardingAlgorithm<Long> {

 
    public    Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Long> shardingValue) {
        
    	List<String > list  = new ArrayList<String>();
    	
    	list.add((String) availableTargetNames.toArray()[1]);
    	
        return list;
    } 
 

}

用法

HintManager.clear();

		try (Session session = s.openSession()) {
			try (HintManager hintManager = HintManager.getInstance()) {
				hintManager.addDatabaseShardingValue("Testuser", 3);
				hintManager.addTableShardingValue("Testuser", 1);

				Transaction tr = session.beginTransaction();

				Testuser user = new Testuser();

				user.setNumber(999);
				session.save(user);

				tr.commit();
			}
		}

取模分片

static Map<String, DataSource> createDataSourceMap() throws SQLException {

		Map<String, DataSource> dataSourceMap = new HashMap<>();

		HikariDataSource result = new HikariDataSource();
		result.setDriverClassName("org.postgresql.Driver");

		result.setJdbcUrl("jdbc:postgresql://192.168.0.120/ds0");
		result.setUsername("postgres");
		result.setPassword("acmeagle");
		dataSourceMap.put("ds0", result);

		result = new HikariDataSource();
		result.setDriverClassName("org.postgresql.Driver");
		result.setJdbcUrl("jdbc:postgresql://192.168.0.120/ds1");
		result.setUsername("postgres");
		result.setPassword("acmeagle");
		dataSourceMap.put("ds1", result);

		return dataSourceMap;
	}

	@Bean
	public DataSource dataSource() throws SQLException {

	 

		TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("testuser",
				"ds${0..1}.testuser${0..1}");
		orderTableRuleConfig.setDatabaseShardingStrategyConfig(
				new InlineShardingStrategyConfiguration("number", "ds${number % 2}"));
		orderTableRuleConfig.setTableShardingStrategyConfig(
				new InlineShardingStrategyConfiguration("number", "testuser${number % 2}"));
		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
		shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
		Properties ps = new Properties();
		ps.put("sql.show", true);

		return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, ps);

	}