requestmapping - spring mvc formulario ejemplo

¿Cómo usar 2 o más bases de datos con spring?

Question

Tengo una aplicación que ejecuta Spring MVC.

Lo necesito para acceder a 2 bases de datos diferentes en mi aplicación (una es una PostgreSQL y la otra es una base de datos MySQL).

¿Cómo configuro esto usando solo anotaciones o el archivo application.properties?

Saludos.

Answer 1

Aquí está el código de ejemplo para tener multiple Database/datasource de multiple Database/datasource en Spring-Boot ¡Espero que ayude!

application.properties

spring.ds_items.driverClassName=org.postgresql.Driver
spring.ds_items.url=jdbc:postgresql://srv0/test
spring.ds_items.username=test0
spring.ds_items.password=test0
spring.ds_users.driverClassName=org.postgresql.Driver
spring.ds_users.url=jdbc:postgresql://srv1/test
spring.ds_users.username=test1
spring.ds_users.password=test1 

DatabaseItemsConfig.java

package sb;
import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
@ConfigurationProperties(name = "spring.ds_items")
public class DatabaseItemsConfig extends TomcatDataSourceConfiguration {
@Bean(name = "dsItems")
public DataSource dataSource() {
return super.dataSource();
}
@Bean(name = "jdbcItems")
public JdbcTemplate jdbcTemplate(DataSource dsItems) {
return new JdbcTemplate(dsItems);
}
} 

DatabaseUsersConfig.java

package sb;
import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
@ConfigurationProperties(name = "spring.ds_users")
public class DatabaseUsersConfig extends TomcatDataSourceConfiguration {
@Bean(name = "dsUsers")
public DataSource dataSource() {
return super.dataSource();
}
@Bean(name = "jdbcUsers")
public JdbcTemplate jdbcTemplate(DataSource dsUsers) {
return new JdbcTemplate(dsUsers);
}
} 

ItemRepository.java

package sb;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
@Repository
public class ItemRepository {
protected final Logger log = LoggerFactory.getLogger(getClass());
@Autowired
@Qualifier("jdbcItems")
protected JdbcTemplate jdbc;
public Item getItem(long id) {
return jdbc.queryForObject("SELECT * FROM sb_item WHERE id=?", itemMapper, id);
}
private static final RowMapper<Item> itemMapper = new RowMapper<Item>() {
public Item mapRow(ResultSet rs, int rowNum) throws SQLException {
Item item = new Item(rs.getLong("id"), rs.getString("title"));
item.price = rs.getDouble("id");
return item;
}
};
} 

UserRepository.java

package sb;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
@Repository
public class UserRepository {
protected final Logger log = LoggerFactory.getLogger(getClass());
@Autowired
@Qualifier("jdbcUsers")
protected JdbcTemplate jdbc;
public User getUser(long id) {
return jdbc.queryForObject("SELECT * FROM sb_user WHERE id=?", userMapper, id);
}
private static final RowMapper<User> userMapper = new RowMapper<User>() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User(rs.getLong("id"), rs.getString("name"));
user.alias = rs.getString("alias");
return user;
}
};
} 

Controller.java

package sb;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class Controller {
protected final Logger log = LoggerFactory.getLogger(getClass());
@Autowired
private UserRepository users;
@Autowired
private ItemRepository items;
@RequestMapping("test")
public String test() {
log.info("Test");
return "OK";
}
@RequestMapping("user")
public User getUser(@RequestParam("id") long id) {
log.info("Get user");
return users.getUser(id);
}
@RequestMapping("item")
public Item getItem(@RequestParam("id") long id) {
log.info("Get item");
return items.getItem(id);
}
} 

Application.java

package sb;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
@EnableAutoConfiguration(exclude = DataSourceAutoConfiguration.class)
@Configuration
@ComponentScan(basePackages = "sb")
public class Application {
public static void main(String[] args) throws Throwable {
SpringApplication app = new SpringApplication(Application.class);
app.run();
}
} 

Answer 2

También puede intentar definir múltiples fuentes de datos y asignar una de ellas como primaria.

Aquí está el código de demostración.

La fuente de datos primaria:

@MapperScan(basePackages = "com.demo.mysqldao",
sqlSessionFactoryRef = "mysqlSqlSessionFactory")
@Configuration
public class MysqlDatabaseConfig {
@Value("${mysql.datasource.url}")
String jdbcUrl;
@Value("${mysql.datasource.username}")
String jdbcUser;
@Value("${mysql.datasource.password}")
String jdbcPass;
@Value("${mysql.datasource.driverClassName}")
String jdbcProvider;
BasicDataSource src = null;
Logger log = LoggerFactory.getLogger(MysqlDatabaseConfig.class);
@Bean(name = "mysqlDataSource")
@Primary
@PostConstruct
public DataSource mysqlDataSource() {
if (jdbcUrl == null) {
throw new RuntimeException("initialization datasource error with null jdbcUrl");
}
log.info("Using JDBC ------------> " + jdbcUrl);
if (src == null) {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(jdbcProvider);
dataSource.setUrl(jdbcUrl);
dataSource.setUsername(jdbcUser);
dataSource.setPassword(jdbcPass);
dataSource.setMaxActive(100);
dataSource.setMinIdle(3);
dataSource.setMaxIdle(10);
dataSource.setMinEvictableIdleTimeMillis(60 * 1000);
dataSource.setNumTestsPerEvictionRun(100);
dataSource.setRemoveAbandoned(true);
dataSource.setRemoveAbandonedTimeout(60 * 1000);
dataSource.setTestOnBorrow(true);
dataSource.setTestOnReturn(true);
dataSource.setTestWhileIdle(true);
dataSource.setTimeBetweenEvictionRunsMillis(30 * 60 * 1000);
src = dataSource;
}
return src;
}
@Autowired
@Qualifier(value = "mysqlDataSource")
DataSource mysqlDataSource;
@Bean("mysqlTransactionManager")
@Primary
public DataSourceTransactionManager mysqlTransactionManager() {
return new DataSourceTransactionManager(mysqlDataSource);
}
@Bean("mysqlSqlSessionFactory")
@Primary
public SqlSessionFactory mysqlSqlSessionFactory() throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(mysqlDataSource);
return sessionFactory.getObject();
}

Otra fuente de datos (en mi demo, es sqlserver)

@MapperScan(basePackages = "com.demo.sqlserverdao",
sqlSessionFactoryRef = "sqlserverSqlSessionFactory")
@Configuration
public class SQLServerDatabaseConfig {
@Value("${sqlserver.datasource.url}")
String jdbcUrl;
@Value("${sqlserver.datasource.username}")
String jdbcUser;
@Value("${sqlserver.datasource.password}")
String jdbcPass;
@Value("${sqlserver.datasource.driverClassName}")
String jdbcProvider;
BasicDataSource src = null;
@Bean(name = "sqlServerDataSource")
@PostConstruct
public DataSource sqlServerDataSource() {
if (jdbcUrl == null) {
throw new RuntimeException("initialization sqlserver datasource error with null jdbcUrl");
}
if (src == null) {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(jdbcProvider);
dataSource.setUrl(jdbcUrl);
dataSource.setUsername(jdbcUser);
dataSource.setPassword(jdbcPass);
dataSource.setMaxActive(100);
dataSource.setMinIdle(3);
dataSource.setMaxIdle(10);
dataSource.setMinEvictableIdleTimeMillis(60 * 1000);
dataSource.setNumTestsPerEvictionRun(100);
dataSource.setRemoveAbandoned(true);
dataSource.setRemoveAbandonedTimeout(60 * 1000);
dataSource.setTestOnBorrow(true);
dataSource.setTestOnReturn(true);
dataSource.setTestWhileIdle(true);
dataSource.setTimeBetweenEvictionRunsMillis(30 * 60 * 1000);
src = dataSource;
}
return src;
}
@Autowired
@Qualifier(value = "sqlServerDataSource")
DataSource sqlServerDataSource;
@Bean("sqlserverTransactionManager")
public DataSourceTransactionManager sqlserverTransactionManager() {
return new DataSourceTransactionManager(sqlServerDataSource);
}
@Bean("sqlserverSqlSessionFactory")
public SqlSessionFactory sqlserverSqlSessionFactory() throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(sqlServerDataSource);
return sessionFactory.getObject();
}

Ref: https://.com/a/27679997/6037575

Answer 3

así es como configura múltiples fuentes de datos en el archivo Spring xml, aquí está el mío, por ejemplo, espero que ayude

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/gl?characterEncoding=UTF-8" />
<property name="username" value="root" />
<property name="password" value="2238295" />
</bean>
<bean id="mainDataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/gl_main?characterEncoding=UTF-8" />
<property name="username" value="root" />
<property name="password" value="2238295" />
</bean>
<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="sfAccounting"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="packagesToScan" value="com.gl.domain.accounting" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.connection.useUnicode">true</prop>
<prop key="hibernate.connection.characterEncoding">UTF-8</prop>
<prop key="hibernate.connection.charSet">UTF-8</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
<prop key="hibernate.show_sql">false</prop>
</props>
</property>
</bean>
<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="sfCommon"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="mainDataSource" />
<property name="packagesToScan" value="com.gl.domain.common" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.connection.useUnicode">true</prop>
<prop key="hibernate.connection.characterEncoding">UTF-8</prop>
<prop key="hibernate.connection.charSet">UTF-8</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
<prop key="hibernate.show_sql">false</prop>
</props>
</property>
</bean>
<tx:annotation-driven transaction-manager="txnManagerAccounting"/>
<tx:annotation-driven transaction-manager="txnManagerCommon"/>
<bean id="txnManagerAccounting"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sfAccounting" />
</bean>
<bean id="txnManagerCommon"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sfCommon" />
</bean>
<bean id="persistenceExceptionTranslationPostProcessor"
class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />