oracle行列转换问题,请高手指点谢谢

发布网友发布时间：2022-04-10 01:35

共2个回答

热心网友时间：2022-04-10 03:04

1. 首先是jdbc.properties属性文件的编写，便于数据库移植：
datasource.driverClassName=oracle.jdbc.driver.OracleDriver
datasource.url=jdbc:oracle:thin:@10.6.1.11:1521:student
datasource.username=zs
datasource.password=zs
datasource.defaultAutoCommit=true
hibernate.dialect=org.hibernate.dialect.Oracle9Dialect

#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3
c3p0.acquireIncrement=5
#初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3
c3p0.initialPoolSize=10
#每60秒检查所有连接池中的空闲连接。Default: 0
c3p0.idleConnectionTestPeriod=600
#-连接池中保留的最小连接数。
c3p0.minPoolSize=5
#连接池中保留的最大连接数。Default: 15
c3p0.maxPoolSize=50
#JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements
#属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
#如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0
c3p0.maxStatements=100
#c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能
#通过多线程实现多个操作同时被执行。Default: 3
c3p0.numHelperThreads=10
#最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0
c3p0.maxIdleTime=600

#hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.jdbc.batch_size=25
hibernate.jdbc.fetch_size=50
hibernate.show_sql=true
hibernate.connection.release_mode=after_transaction

2. 其次是spring配置文件的数据源配置：
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
  <value>/WEB-INF/classes/conf/jdbc/jdbc.properties</value>
</property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" dependency-check="none">
<property name="driverClass">
  <value>${datasource.driverClassName}</value>
</property>
<property name="jdbcUrl">
  <value>${datasource.url}</value>
</property>
<property name="user">
  <value>${datasource.username}</value>
</property>
<property name="password">
  <value>${datasource.password}</value>
</property>

<property name="acquireIncrement">
  <value>${c3p0.acquireIncrement}</value>
</property>

<property name="initialPoolSize">
  <value>${c3p0.initialPoolSize}</value>
</property>

<property name="minPoolSize">
  <value>${c3p0.minPoolSize}</value>
</property>

<property name="maxPoolSize">
  <value>${c3p0.minPoolSize}</value>
</property>

<property name="maxIdleTime">
  <value>${c3p0.maxPoolSize}</value>
</property>

<property name="idleConnectionTestPeriod">
  <value>${c3p0.idleConnectionTestPeriod}</value>
</property>

<property name="maxStatements">
  <value>${c3p0.maxStatements}</value>
</property>

<property name="numHelperThreads">

热心网友时间：2022-04-10 04:22

SQL> create table a(s_id number,s_name varchar2(20),c_name varchar2(20),f_half_score number,s_half_score number);
Table created

SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(1,'张三','语文','89','86');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(1,'张三','英语','50','64');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(1,'张三','数学','90','89');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(2,'李四','语文','80','81');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(2,'李四','英语','70','80');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(2,'李四','数学','65','80');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(3,'王五','语文','70','80');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(3,'王五','英语','70','90');
1 row inserted
SQL> insert into a(s_id,s_name, c_name, f_half_score, s_half_score)
2 values(3,'王五','数学','90','80');
1 row inserted

SQL> select * from a;
S_ID S_NAME C_NAME F_HALF_SCORE S_HALF_SCORE
---------- -------------------- -------------------- ------------ ------------
1 张三语文 89 86
1 张三英语 50 64
1 张三数学 90 89
2 李四语文 80 81
2 李四英语 70 80
2 李四数学 65 80
3 王五语文 70 80
3 王五英语 70 90
3 王五数学 90 80
9 rows selected

SQL> select s_name, sum(decode(c_name, '语文', f_half_score, 0)) as 语文_上半年,
2 sum(decode(c_name, '数学', f_half_score, 0)) as 语文_上半年,
3 sum(decode(c_name, '英语', f_half_score, 0)) as 语文_上半年,
4 sum(decode(c_name, '语文', s_half_score, 0)) as 语文_下半年,
5 sum(decode(c_name, '数学', s_half_score, 0)) as 语文_下半年,
6 sum(decode(c_name, '英语', s_half_score, 0)) as 语文_下半年
7 from a
8 group by s_id,s_name
9 order by s_id;
S_NAME 语文_上半年语文_上半年语文_上半年语文_下半年语文_下半年语文_下半年
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
张三 89 90 50 86 89 64
李四 80 65 70 81 80 80
王五 70 90 70 80 80 90