|
package com.cwbnig;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
public class DatabaseConn
{
public DatabaseConn()
{
}
public static synchronized Connection getConnection()throws Exception
{
Connection conn = null;
try
{
Context initCtx=new InitialContext();
Context envCtx=(Context)initCtx.lookup("java:comp/env");
DataSource ds=(DataSource)envCtx.lookup("jdbc/jspdev");
return ds.getConnection();
}
catch(SQLException e)
{
throw e;
}
catch(NamingException e)
{
throw e;
}
}
}
package com.cwbnig;
import java.sql.*;
import java.util.Vector;
public class OperateDB
{
private Connection conn=null;
private Statement stmt=null;
PreparedStatement pstmt=null;
CallableStatement cstmt=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private String strsql="";
public OperateDB()
{
try
{
conn = DatabaseConn.getConnection();
}
catch (Exception e)
{
System.out.println("Error:com.cwbnig.OperateDB:Structure Method");
}
}
public Connection getConnection() throws SQLException
{
return this.conn;
}
public ResultSet executeQuery(String sql) throws SQLException
{
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
return stmt.executeQuery(sql);
}
public ResultSet listDataFromMSSQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws SQLException
{
int SELECTNUM=(curpage-1)*pagesize+1;
if(order.equalsIgnoreCase("asc"))
{
String min="SELECT MAX("+sid+") AS id FROM (SELECT TOP "+SELECTNUM+" "+sid+" FROM "+tablename+" ORDER BY "+sid+" ASC) tbl";
strsql="SELECT TOP "+pagesize+" * FROM "+tablename+" WHERE "+sid+" >="+min+con+" ORDER BY "+sid+" ASC";
}
else
{
String max="SELECT MIN("+sid+") AS id FROM (SELECT TOP "+SELECTNUM+" "+sid+" FROM "+tablename+" ORDER BY "+sid+" DESC) tbl";
strsql="SELECT TOP "+pagesize+" * FROM "+tablename+" WHERE "+sid+" <="+max+con+" ORDER BY "+sid+" DESC";
}
return this.executeQuery(strsql);
}
public ResultSet listDataFromMySQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws SQLException
{
int SELECTNUM=(curpage-1)*pagesize;
if(order.equalsIgnoreCase("asc"))
{
strsql="SELECT * FROM "+tablename+con+" ORDER BY "+sid+" ASC LIMIT "+SELECTNUM+","+pagesize;
}
else
{
strsql="SELECT * FROM "+tablename+con+" ORDER BY "+sid+" DESC LIMIT "+SELECTNUM+","+pagesize;
}
return this.executeQuery(strsql);
}
public ResultSet listDataFromOracle(int curpage,int pagesize,String tablename,String sid,String con,String order) throws SQLException
{
int SELECTNUM=(curpage-1)*pagesize+2;
if(order.equalsIgnoreCase("asc"))
{
String min="SELECT MAX("+sid+") AS SID FROM (SELECT "+sid+" FROM "+tablename+" WHERE ROWNUM <"+SELECTNUM+" ORDER BY "+sid+" ASC) TBL";
strsql="SELECT * FROM "+tablename+" WHERE "+sid+" >=("+min+") AND ROWNUM <"+(pagesize+1)+con+" ORDER BY "+sid+" ASC";
}
else
{
String max="SELECT MIN("+sid+") AS SID FROM (SELECT "+sid+" FROM "+tablename+" WHERE ROWNUM <"+SELECTNUM+" ORDER BY "+sid+" DESC) TBL";
strsql="(SELECT * FROM "+tablename+" WHERE "+sid+" <=("+max+") ORDER BY "+sid+" DESC)TBL2";
strsql="SELECT * FROM "+strsql+" WHERE ROWNUM<"+(pagesize+1)+con;
}
return this.executeQuery(strsql);
}
public Vector getDataFromMSSQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws Exception
{
Vector v=new Vector();
rs=this.listDataFromMSSQL(curpage,pagesize,tablename,sid,con,order);
rsmd=rs.getMetaData();
int colnum=rsmd.getColumnCount();
while(rs.next())
{
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=new String(this.getCol(rs,rsmd.getColumnType(i+1),i+1));
}
v.add(obj);
}
stmt.close();
rs.close();
conn.close();
return v;
}
public Vector getDataFromMySQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws Exception
{
Vector v=new Vector();
rs=this.listDataFromMySQL(curpage,pagesize,tablename,sid,con,order);
rsmd=rs.getMetaData();
int colnum=rsmd.getColumnCount();
while(rs.next())
{
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=new String(this.getCol(rs,rsmd.getColumnType(i+1),i+1));
}
v.add(obj);
}
stmt.close();
rs.close();
conn.close();
return v;
}
public Vector getDataFromOracle(int curpage,int pagesize,String tablename,String sid,String con,String order) throws Exception
{
Vector v=new Vector();
rs=this.listDataFromOracle(curpage,pagesize,tablename,sid,con,order);
rsmd=rs.getMetaData();
int colnum=rsmd.getColumnCount();
while(rs.next())
{
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=new String(this.getCol(rs,rsmd.getColumnType(i+1),i+1));
}
v.add(obj);
}
stmt.close();
rs.close();
conn.close();
return v;
}
public String getCol(ResultSet rs,int type,int colNum)throws Exception
{
String ret="";
switch(type)
{
case(1):ret=rs.getString(colNum);break;
case(2):ret=rs.getString(colNum);break;
case(4):ret=String.valueOf(rs.getInt(colNum));break;
case(5):ret=String.valueOf(rs.getInt(colNum));break;
case(6):ret=String.valueOf(rs.getFloat(colNum));break;
case(8):ret=String.valueOf(rs.getDouble(colNum));break;
case(12):ret=rs.getString(colNum);break;
default:ret="not know";
}
return ret;
}
public Object[] getColName(String tablename)throws Exception
{
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery("SELECT * FROM "+tablename+" WHERE ROWNUM<2");
rsmd=rs.getMetaData() ;
int colnum=rsmd.getColumnCount();
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=rsmd.getColumnName(i+1);
}
&n [1] [2] 下一页
[Web开发]JSP入门学习经验总结 [Sql Server]Sql精妙语句--各种求值函数
[网页制作]网页表格之---多个表格纵向排列 [网页制作]JavaScript另类用法--读取和写入cookie
[网页制作]号称非常安全的上网工具---360安全浏览器介绍 [办公软件]信息技术教学篇---Word工具栏的显示、隐藏及四种菜…
[操作系统]开始菜单---运行命令大总结 [操作系统]网络转载---64位操作系统与32位的区别
[操作系统]ldap:///(没有响应)Windows无法访问指定设备、路径… [网络技术]安全篇---交换机设置方法介绍
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
