|
|
--1992年sql标准，连接条件和过滤条件写在一起。
select ename,dname,grade
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and job<>'CLERK';
--1999年sql标准,连接条件和过滤条件分开
select ename,dname,grade
from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';
increase the size of the pool and retry..] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
PCIS[2010-07-01 12:51:47,456]>> WARN>> [SQL Error: 0, SQLState: null] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
PCIS[2010-07-01 12:51:47,456]>>ERROR>> [Cannot obtain connection: driverURL = jdbc:weblogic:pool:pcis_ts, props = {EmulateTwoPhaseCommit=false, connectionPoolID=pcis_ts, jdbcTxDataSource=true, LoggingLastResource=false, dataSourceName=pcis_ts}] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
PCIS[2010-07-01 12:51:47,456]>> WARN>> [SQL Error: 0, SQLState: null] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
PCIS[2010-07-01 12:51:47,456]>>ERROR>> [weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool pcis_ts to allocate to applications, please increase the size of the pool and retry..] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
PCIS[2010-07-01 12:51:47,456]>> WARN>> [SQL Error: 0, SQLState: null] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
PCIS[2010-07-01 12:51:47,456]>>ERROR>> [Cannot obtain connection: driverURL = jdbc:weblogic:pool:pcis_ts, props = {EmulateTwoPhaseCommit=false, connectionPoolID=pcis_ts, jdbcTxDataSource=true, LoggingLastResource=false, dataSourceName=pcis_ts}] - [org.hibernate.util.JDBCExceptionReporter] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kerne
l.Default (self-tuning)']
[com.pcis.premium.service.PremiumService] -950125750 [[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool pcis_ts to allocate to applications, please increase the size of the pool and retry..; nested exception is weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool pcis_ts to allocate to applications, please increase the size of the pool and retry..
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:379)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:872)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:868)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:226)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:124)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:283)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:168)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:183)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
at org.jasig.cas.client.session.SingleSignOutFilter.doFilter(SingleSignOutFilter.java:99)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
at com.isoftstone.iaeap.web.filter.SetCharacterEncodingFilter.doFilter(Unknown Source)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3393)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(Unknown Source)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2140)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2046)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1366)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:200)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:172)
Caused by: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool pcis_ts to allocate to applications, please increase the size of the pool and retry..
at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:242)
at weblogic.jdbc.pool.Driver.connect(Driver.java:160)
at weblogic.jdbc.jts.Driver.getNonTxConnection(Driver.java:642)
at weblogic.jdbc.jts.Driver.connect(Driver.java:124)
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:339)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:82)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:881)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:374)
... 31 more
原因：主要是weblogic的连接池已满，需要重新设置或者重启weblogic的AdminServer服务器进行释放连接。
操作：进入weblogic控制台，选择：域-->Services-->JDBC-->Data Source-->点击control-->选中AdminServer，对其重启即可。
select chr(65) from dual;
select ascii('A') from dual; --求编码
select ename from emp where lower(ename) like '_a%' -- upper大写
select round(23.652,2) from dual; -- 四舍五入 23.65,round(23.652)为24
select substr(ename,1,3) from emp; --从第一个开始，总接取3个。
select ename,sal,deptno from emp where length(sal)>3;
select to_char(sal,'$99,999.9999') from emp; --'L00000.0000'千位不够补0
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select ename,hiredate from emp where hiredate>to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal>to_number('$1,250.00','$9,999.99');
select ename,sal*12 + nvl(comm,0) from emp; --对空值处理
组函数：
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(*) from emp; -- *求出总记录数, count(comm)求出该列不为空的值。
select deptno,job,max(sal) from emp group by deptno,job; --按照条件组合分组
select ename from emp where sal =(select max(sal) from emp); --子查询
update Web_Bas_Edr_Rsn set c_rsn_txt=REPLACE(c_rsn_txt,'天津','北京') where c_rsn_txt like '%天津%' --批量替换
-- having对分组进行限制，where对单行限制
select avg(sal) from emp where sal>1000 group by deptno having avg(sal) >1500 order by avg(sal) desc;
sqlplus sys/sys as sysdba;
alert user scott account unlock;
desc emp;
select ename,sal*12 from emp;
select sysdate from dual; --dual为空表
select ename,sal*12 annual_sal from emp; --别名
--数值+null 为null
select ename||sal from emp; --拼串
select ename||'aaa''bbb' from emp; --含'的拼串,''替换为'
select distinct deptno,job from emp; --去掉2个字段值的组合
select * from emp where deptno=10 and ename='CLARK';
select ename,sal from emp where sal>1500;
select ename,sal from emp where deptno<>10;
select ename,sal from emp where sal between 800 and 1500; -- sal>=800 and sal<=1500
select ename,sal,comm from emp where comm is null; --is not null
select ename,sal,comm from emp where sal in (800,1500); -- not in (800,1500)
select ename from emp where ename like '_A%' -- %为0个或多个，-为1个
select ename from emp where ename like '%\%%' -- 名字含有%，需要\为转义字符处理
select ename from emp where ename like '%$%%' escape '$' -- 可以使用$作为转义字符
select ename,sal,deptno from emp order by deptno asc,ename desc; -- 升序asc，降序 desc
今天想把tomcat下的程序迁移到weblogic92上，可是程序在发布到weblogic92时报下面的异常：
Home > Summary of Deployments > Summary of JDBC Data Sources > myDataSource > Summary of Deployments :
Messages
An error occurred during activation of changes, please see the log for details.
[HTTP:101064][WebAppModule(nonvhl:WebRoot)] Error parsing descriptor in Web appplication "E:\workspace\nonvhl_policy_TS\WebRoot"
weblogic.application.ModuleException: VALIDATION PROBLEMS WERE FOUND E:\workspace\nonvhl_policy_TS\WebRoot\WEB-INF\web.xml:133:5:133:5: problem: cvc-complex
-type.2.4a: Expected elements 'servlet-class@http://java.sun.com/xml/ns/j2ee jsp-file@http://java.sun.com/xml/ns/j2ee' instead of 'display-
name@http://java.sun.com/xml/ns/j2ee' here in element servlet@http://java.sun.com/xml/ns/j2ee: at weblogic.servlet.internal.WebAppModule.loadDescriptor
(WebAppModule.java:784) at weblogic.servlet.internal.WebAppModule.prepare(WebAppModule.java:275) at
weblogic.application.internal.flow.ScopedModuleDriver.prepare(ScopedModuleDriver.java:176) at
weblogic.application.internal.flow.ModuleListenerInvoker.prepare(ModuleListenerInvoker.java:93) at
weblogic.application.internal.flow.DeploymentCallbackFlow$1.next(DeploymentCallbackFlow.java:360) at weblogic.application.utils.StateMachineDriver.nextState
(StateMachineDriver.java:26) at weblogic.application.internal.flow.DeploymentCallbackFlow.prepare(DeploymentCallbackFlow.java:56) at
weblogic.application.internal.flow.DeploymentCallbackFlow.prepare(DeploymentCallbackFlow.java:46) at weblogic.application.internal.BaseDeployment$1.next
(BaseDeployment.java:621) at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:26) at
weblogic.application.internal.BaseDeployment.prepare(BaseDeployment.java:208) at weblogic.application.internal.DeploymentStateChecker.prepare
(DeploymentStateChecker.java:147) at weblogic.deploy.internal.targetserver.AppContainerInvoker.prepare(AppContainerInvoker.java:61) at
weblogic.deploy.internal.targetserver.operations.ActivateOperation.createAndPrepareContainer(ActivateOperation.java:189) at
weblogic.deploy.internal.targetserver.operations.ActivateOperation.doPrepare(ActivateOperation.java:87) at
weblogic.deploy.internal.targetserver.operations.AbstractOperation.prepare(AbstractOperation.java:217) at
weblogic.deploy.internal.targetserver.DeploymentManager.handleDeploymentPrepare(DeploymentManager.java:718) at
weblogic.deploy.internal.targetserver.DeploymentManager.prepareDeploymentList(DeploymentManager.java:1185) at
weblogic.deploy.internal.targetserver.DeploymentManager.handlePrepare(DeploymentManager.java:247) at
原因：经过分析是web.xml配置的问题，有些servlet上面配置了'display-name'，这个weblogic是不支持的。
解决：在web.xml中把'display-name'删除掉，工程就可以在weblogic下成功发布。
在配置weblogic的数据源时，提示一下信息：
Home > Summary of Deployments > Summary of JDBC Data Sources:
Messages
An error occurred during activation of changes, please see the log for details.
weblogic.application.ModuleException:
weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: Io 异常: The Network Adapter could not establish the connection
解决：主要是我在配置数据源时没有把Oracle的服务启动造成的。
1、今天到客户现场，输入系统地址，发现登录不上，后来查了下是DNS域名的问题。
原因：客户的机器域名解析地址是：google的DNS，首选DNS服务器和备用DNS服务器分别设置为 8.8.8.8和8.8.4.4。由于我们是在本地局域网设置的域名，公网的域名解析器不识别，导致登录不了系统。
解决：设置自动获取DNS。
2、登录系统后，打来相关的页面，又出现页面加载失败
原因：主要是域名解析后，对相应的ip地址有限制。
解决：把域名、ip地址都加入为可信任站点，调低安全级别即可。
1、声明式事务配置
* 配置SessionFactory
* 配置事务管理器
* 事务的传播特性
* 那些类那些方法使用事务
2、编写业务逻辑方法
* 继承HibernateDaoSupport类，使用HibernateTemplate来持久化，HibernateTemplate是
Hibernate Session的轻量级封装
* 默认情况下运行期异常才会回滚（包括继承了RuntimeException子类），普通异常是不会滚的
* 编写业务逻辑方法时，最好将异常一直向上抛出，在表示层（struts）处理
* 关于事务边界的设置，通常设置到业务层，不要添加到Dao上
3、了解事务的几种传播特性
1. PROPAGATION_REQUIRED: 如果存在一个事务，则支持当前事务。如果没有事务则开启
2. PROPAGATION_SUPPORTS: 如果存在一个事务，支持当前事务。如果没有事务，则非事务的执行
3. PROPAGATION_MANDATORY: 如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常。
4. PROPAGATION_REQUIRES_NEW: 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起。
5. PROPAGATION_NOT_SUPPORTED: 总是非事务地执行，并挂起任何存在的事务。
6. PROPAGATION_NEVER: 总是非事务地执行，如果存在一个活动事务，则抛出异常
7. PROPAGATION_NESTED：如果一个活动的事务存在，则运行在一个嵌套的事务中. 如果没有活动事务,
则按TransactionDefinition.PROPAGATION_REQUIRED 属性执行
4、Spring事务的隔离级别
1. ISOLATION_DEFAULT： 这是一个PlatfromTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别.
另外四个与JDBC的隔离级别相对应
2. ISOLATION_READ_UNCOMMITTED： 这是事务最低的隔离级别，它充许令外一个事务可以看到这个事务未提交的数据。
这种隔离级别会产生脏读，不可重复读和幻像读。
3. ISOLATION_READ_COMMITTED： 保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据
4. ISOLATION_REPEATABLE_READ： 这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻像读。
它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了避免下面的情况产生(不可重复读)。
5. ISOLATION_SERIALIZABLE 这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。
除了防止脏读，不可重复读外，还避免了幻像读。
对于出现的乱码有2种解决方法：
1、在JS中，window.showModalDialog传递对象。
  function tool_uploadFile(clmNo,billType,maxFileNum,fileType,singleLimit,totalLimit)  {//解决乱码的问题，增加变量paramObj。10.06.01
  var paramObj = {
 "clmNo" : clmNo,
"billType" : billType,
"maxFileNum" : maxFileNum,
"fileType" : fileType,
"singleLimit" : singleLimit,
"totalLimit" : totalLimit
 };
var r = window.showModalDialog(global.WEB_APP_NAME+"/core/jsp/common/uploadFile.jsp",paramObj,"dialogHeight:610px;dialogWidth:530px;center:1;help: 0; status: 0;");
return r;
 }
在uploadFile.jsp中，通过js获得参数。
 <script>
var paramObj = window.dialogArguments;
var clmNo = paramObj.clmNo;
var billType = paramObj.billType;
var maxFileNum = paramObj.maxFileNum;
var fileType = paramObj.fileType;
var singleLimit = paramObj.singleLimit;
var totalLimit = paramObj.totalLimit;
tool.loadApplet('<%=agentIp%>','<%=agentPort%>','<%=orgId%>',clmNo,billType,maxFileNum,fileType,singleLimit,totalLimit);
</script>
2、在JS中，window.showModalDialog通过？传递参数。
function tool_uploadFile(clmNo,billType,maxFileNum,fileType,singleLimit,totalLimit) {
billType = encodeURIComponent(billType);
var r = window.showModalDialog(global.WEB_APP_NAME+"/core/jsp/common/uploadFile.jsp?clmNo="+clmNo+"&billType="+billType+"&maxFileNum="+maxFileNum+"&fileType="+fileType+"&singleLimit="+singleLimit+"&totalLimit="+totalLimit,"","dialogHeight:610px;dialogWidth:530px;center:1;help: 0; status: 0;");
return r;
}
在uploadFile.jsp中，通过java解析获得参数。
String paramStr = request.getQueryString();
if (paramStr == null || paramStr.equals("")) {
return;
}
HashMap<String, ArrayList<String>> parameter = new HashMap<String, ArrayList<String>>();
String[] paramArr = paramStr.split("&+");
String[] arr = null;
for (int i = 0; i < paramArr.length; i++) {
arr = paramArr[i].split("=", 2);
ArrayList<String> lst = parameter.get(arr[0]);
if (lst == null) {
lst = new ArrayList<String>();
parameter.put(arr[0], lst);
}
if (arr.length < 2) {
lst.add("");
} else {
lst.add(URLDecoder.decode(arr[1], "UTF-8"));
}
}
ArrayList<String> paramlst = parameter.get("billType");
String billType = paramlst.get(0);
以上两种方法，对应用服务器设置的url编码没有关系。
还有一种方法，就是转换成GB2312，不过此种方法跟服务器编码设置还有关系，有时还会出现乱码：
billType = new String(billType.getBytes(" ISO-8859-1"),"GB2312");
|