|
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] 下一页 |
