JAVA数据库操作二（多个数据库操作+Spring data + jpa)

最新推荐文章于 2025-08-08 15:07:26 发布

原创最新推荐文章于 2025-08-08 15:07:26 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #mysql #jpa #spring

java 专栏收录该内容

20 篇文章

订阅专栏

本文介绍如何使用Java同时操作Oracle和MySQL两个数据库，包括配置文件、实体类定义及简单测试用例。

缺的文件请参考上一篇文章http://blog.youkuaiyun.com/makefriend7/article/details/53941458

本文我们将主要实现同时操作两个数据库，（oracle 和mysql)

当然为了展示技巧，本文将以java配置为主

首先是daoContext.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:jdbc="http://www.springframework.org/schema/jdbc"
	   xmlns:c="http://www.springframework.org/schema/c"
	   xmlns:context="http://www.springframework.org/schema/context"
	   xmlns:p="http://www.springframework.org/schema/p"
	   xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	   xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">

	<bean class = "db.MyConfig"/>
	<bean class = "mysqldb.MySqlConfig"/>

</beans>

好吧。其实有用的就两行。

oralce的配置文件就MyConfig 内容如下

package db;
import com.mysql.cj.jdbc.MysqlDataSource;
import oracle.jdbc.pool.OracleDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@EnableJpaRepositories(basePackages = "db",
        entityManagerFactoryRef="entityManagerFactoryOracle",
        transactionManagerRef="transactionManagerOracle"
)
public class MyConfig {
    @Bean
    public DataSource dataSourceOracle()   throws SQLException {
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser("abc");
        dataSource.setPassword("abc");
        dataSource.setURL("jdbc:oracle:thin:@192.168.1.2:1521:orcl");
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setFastConnectionFailoverEnabled(true);
        return dataSource;
    }
    @Bean
    public HibernateJpaVendorAdapter jpaVendorAdapterOracle() {
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.ORACLE);
        adapter.setShowSql(true);
        adapter.setGenerateDdl(false);
        return adapter;
    }
    @Bean(name = "entityManagerFactoryOracle")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryOracle()  throws SQLException{
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSourceOracle());
        emf.setPersistenceUnitName("myPersistence1");
        emf.setJpaVendorAdapter(jpaVendorAdapterOracle());
        emf.setPackagesToScan("entity");
        return emf;
    }
    @Bean(name="transactionManagerOracle")
    public JpaTransactionManager transactionManagerOracle()  throws SQLException{
        JpaTransactionManager txManager = new JpaTransactionManager(entityManagerFactoryOracle().getObject());
        return txManager;
    }
}

mysql的配置文件如下

package mysqldb;

import com.mysql.cj.jdbc.MysqlDataSource;
import org.hibernate.jpa.boot.spi.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@Configuration
@EnableJpaRepositories(basePackages = "mysqldb",
        entityManagerFactoryRef="entityManagerFactoryMySql",
        transactionManagerRef="transactionManagerMySql"
)
public class MySqlConfig {
    @Bean
    public DataSource dataSourceMySql()   throws SQLException {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUser("abc");
        dataSource.setPassword("abc");
        dataSource.setURL("jdbc:mysql://localhost:3306/test?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC");
        return dataSource;
    }
    @Bean
    public HibernateJpaVendorAdapter jpaVendorAdapterMySql() {
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.MYSQL);
        adapter.setShowSql(true);
        adapter.setGenerateDdl(true);
        return adapter;
    }
    @Bean(name = "entityManagerFactoryMySql")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryMySql()  throws SQLException{
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSourceMySql());
        emf.setPersistenceUnitName("myPersistence2");
        emf.setJpaVendorAdapter(jpaVendorAdapterMySql());
        emf.setPackagesToScan("mysqlentity");
        return emf;
    }
    @Bean(name="transactionManagerMySql")
    public JpaTransactionManager transactionManagerMySql()  throws SQLException{
        return new JpaTransactionManager(entityManagerFactoryMySql().getObject());
    }
}

这两个配置文件一出，大部分的工作就完成啦。

作为测试。我们分别定义表MyEntity在 oracle中，表MySqlEntity在mysql中

package entity;
import javax.persistence.*;
import java.math.BigDecimal;

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "prooduct_id_seq")
    @SequenceGenerator(name="prooduct_id_seq", sequenceName = "PRODUCT_ID_SEQ", allocationSize = 100)
    private Integer id;

    @Version
    private Integer version;

    private String testString;
    private BigDecimal testNumber;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }

    public String getTestString() {
        return testString;
    }

    public void setTestString(String testString) {
        this.testString = testString;
    }

    public BigDecimal getTestNumber() {
        return testNumber;
    }

    public void setTestNumber(BigDecimal testNumber) {
        this.testNumber = testNumber;
    }
}

MySqlEntity的内容如下

package mysqlentity;

import javax.persistence.*;
import java.math.BigDecimal;

@Entity
public class MySqlEntity {
    @Id
    @Column(name = "id", nullable = false, length = 64)
    private Integer id;

    @Version
    private Integer version;

    private String testString;
    private BigDecimal testNumber;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }

    public String getTestString() {
        return testString;
    }

    public void setTestString(String testString) {
        this.testString = testString;
    }

    public BigDecimal getTestNumber() {
        return testNumber;
    }

    public void setTestNumber(BigDecimal testNumber) {
        this.testNumber = testNumber;
    }
}

测试嘛。就随便弄俩个数据库操作

oracle 的

package db;

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

import java.math.BigDecimal;
import java.util.List;
public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    List<MyEntity> findByVersion(Integer version);
}

mysql的

package mysqldb;

import entity.MyEntity;
import mysqlentity.MySqlEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface MySqlEntityRepository extends JpaRepository<MySqlEntity, Long> {
    List<MySqlEntity> findByVersion(Integer version);
}

最后就是测试用的MyService

package service;
import db.MyEntityRepository;
import entity.MyEntity;
import mysqldb.MySqlEntityRepository;
import mysqlentity.MySqlEntity;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;
public class MyService {
    @Autowired
    public MyEntityRepository myEntityRepository;
    @Autowired
    public MySqlEntityRepository mySqlEntityRepository;
    public String processCommand(String anything)
    {
        String str = "hello";
        List<MyEntity> myEntityList = myEntityRepository.findAll();
        List<MyEntity> myEntityList2 = myEntityRepository.findByVersion(3);
        List<MySqlEntity> myEntityList3 =  mySqlEntityRepository.findAll();
        return str;
    }
}

怎么样？代码都跑起来了吧。。很容易就实现了JAVA同时操作两个数据库，下一篇将介绍如何操作ES