java - 在MyBatis中Alter Session设置日期格式

Question

如何在同一个事务 session 中执行此操作？

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss'

有一些过程和插入，我需要在它们之前执行。
我尝试在同一个方法中制作另一种方法，但它仍然不起作用。

我将 MyBatis 与 Spring 集成。不知道有没有区别

任何人都可以帮助我吗？

谢谢。

解决方案:

我通过改变 Spring 和 MyBatis 通过 SqlSession 集成的方式来管理这个工作。

Answer 1

这里有两种可能的解决方案。

扩展 SqlSessionFactory

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.TransactionIsolationLevel;
import org.apache.ibatis.session.defaults.DefaultSqlSessionFactory;
import org.apache.log4j.Logger;

public class CustomSqlSessionFactory extends DefaultSqlSessionFactory
{
    private static Logger   msLogger    = Logger.getLogger(CustomSqlSessionFactory.class);

    public CustomSqlSessionFactory(Configuration configuration)
    {
        super(configuration);
    }

    @Override
    public SqlSession openSession()
    {
        SqlSession session = super.openSession();
        alterSession(session);
        return session;
    }

    protected void alterSession(SqlSession session)
    {
        try
        {
            Statement statement = session.getConnection().createStatement();
            statement.addBatch("alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss'");
            statement.addBatch("ALTER SESSION SET NLS_COMP = LINGUISTIC");
            statement.addBatch("ALTER SESSION SET NLS_SORT = XTURKISH_AI");
            statement.executeBatch();
            msLogger.debug("Altered newly created session parameters.");
            statement.close();
        }
        catch (SQLException e)
        {
            msLogger.error("Alter session failed!", e);
        }
    }

    @Override
    public SqlSession openSession(boolean autoCommit)
    {
        SqlSession session = super.openSession(autoCommit);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(Connection connection)
    {
        SqlSession session = super.openSession(connection);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType)
    {
        SqlSession session = super.openSession(execType);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType, boolean autoCommit)
    {
        SqlSession session = super.openSession(execType, autoCommit);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType, Connection connection)
    {
        SqlSession session = super.openSession(execType, connection);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType, TransactionIsolationLevel level)
    {
        SqlSession session = super.openSession(execType, level);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(TransactionIsolationLevel level)
    {
        SqlSession session = super.openSession(level);
        alterSession(session);
        return session;
    }
}

如果您使用 spring 还创建一个 CustomSqlSessionFactoryBuilder

import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class CustomSqlSessionFactoryBuilder extends SqlSessionFactoryBuilder
{
    @Override
    public SqlSessionFactory build(Configuration config)
    {
        return new CustomSqlSessionFactory(config);
    }
}

并通过修改 SqlSessionFactoryBean 配置附加 CustomSqlSessionFactoryBuilder

<bean id="mySqlSessionFactoryBuilder" class="your.package.CustomSqlSessionFactoryBuilder" />    

<bean id="mySessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="myDataSource" />
    <property name="sqlSessionFactoryBuilder" ref="mySqlSessionFactoryBuilder" />
</bean>

每次借用操作都会更改 session 。但是，如果您使用池化连接，这种方法会降低执行性能。因此，每次从池中 checkout 连接时，都会调用 openSession。

如果您使用的是池数据源，那么在数据源级别处理 session 更改操作会快得多。第二种解决方案修改 C3P0 池化数据源以更改 session 。

修改池化数据源(C3P0 和 Spring)

创建连接定制器类 [1]

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.AbstractConnectionCustomizer;

public class ConnectionCustomizer extends AbstractConnectionCustomizer
{
    private static Logger   msLogger    = Logger.getLogger(ConnectionCustomizer.class);

    public void onAcquire(Connection c, String pdsIdt)
    {
        try
        {
            Statement statement = c.createStatement();
            statement.addBatch("alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss'");
            statement.addBatch("ALTER SESSION SET NLS_COMP = LINGUISTIC");
            statement.addBatch("ALTER SESSION SET NLS_SORT = XTURKISH_AI");
            statement.executeBatch();
            msLogger.debug("Altered newly created session parameters.");
            statement.close();
        }
        catch (SQLException e)
        {
            msLogger.error("Alter session failed!", e);
        }
    }   
}

修改数据源配置 bean 并将创建的类添加为 connectionCustomizerClassName

<!-- 
    driverClass : Driver class that will be used to connect to database.
    jdbcUrl : jdbc url defining the database connection string.
    user : username of the database user.
    password : password of the database user.
    acquireIncrement : how many connections will be created at a time when there will be a shortage of connections.
    idleConnectionTestPeriod : after how much delay a connection will be closed if it is no longer in use.
    maxPoolSize : Max number of connections that can be created.
    maxStatements : Max number of SQL statements to be executed on a connection.
    minPoolSize : Minimum number of connections to be created.
    connectionCustomizerClassName : Custom connection customizer to enable session alterations and jobs on acquiring/closing - checking in/out physical connections 
-->
<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />
    <property name="jdbcUrl" value="${app.jdbc.url}" />
    <property name="user" value="${app.jdbc.username}" />
    <property name="password" value="${app.jdbc.password}" />
    <property name="acquireIncrement" value="3" />
    <property name="maxPoolSize" value="50" />
    <property name="maxStatements" value="50" />
    <property name="minPoolSize" value="5" />
    <property name="idleConnectionTestPeriod" value="60" />
    <property name="preferredTestQuery" value="SELECT 1 FROM DUAL" />
    <property name="testConnectionOnCheckout" value="true" />
    <property name="connectionCustomizerClassName" value="your.package.name.ConnectionCustomizer" />
</bean>


[1]: http://www.mchange.com/projects/c3p0/