package com.sql;
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.GregorianCalendar;
|
import java.util.Properties;
|
|
import com.base.Com;
|
import com.config.AppConfig;
|
|
public class Sql_Sybase
|
{
|
public final static int SQL_TYPE_SYBASE_DEMO = 0;
|
public final static int SQL_TYPE_SYBASE = 1;
|
public final static int SQL_TYPE_MSSQLSERVER = 2;
|
private int m_SQL_TYPE = SQL_TYPE_SYBASE_DEMO;
|
//´´½¨Êý¾Ý¿âÁ¬½Ó³Ø
|
//private ComboPooledDataSource sybase_ds = new ComboPooledDataSource();
|
|
private String mSQLServerIp = "127.0.0.1";
|
private String mSQLServerUsrName = "sxfg001";
|
private String mSQLServerPWD = "sxfg001";
|
private int mSQLServerPort = 5000;
|
|
private ResultSet sql_res = null;
|
private Connection sql_conn = null;
|
|
public Sql_Sybase(AppConfig cfg)
|
{
|
m_SQL_TYPE = cfg.getSourceSQLServerType();
|
mSQLServerIp = cfg.getSourceSQLServerIp();
|
mSQLServerUsrName = cfg.getSourceSQLServerUsrName();
|
mSQLServerPWD = cfg.getSourceSQLServerPWD();
|
mSQLServerPort = cfg.getSourceSQLServerPort();
|
|
sql_conn = getConnection();
|
}
|
|
public int get_SQL_TYPE()
|
{
|
return m_SQL_TYPE;
|
}
|
|
public String getQueryVolString(String st_id, GregorianCalendar gc_start,
|
GregorianCalendar gc_end, String time_order)
|
{
|
String str = "";
|
if(SQL_TYPE_SYBASE == m_SQL_TYPE)
|
{
|
str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
|
+ " HistoryBattery" + (gc_start.get(GregorianCalendar.MONTH) + 1)
|
+ " WHERE "
|
+ " StationId=" + st_id
|
+ " AND RecordTime>'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
|
+ " AND RecordTime<='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
|
+ " ORDER BY RecordTime " + time_order;
|
}
|
else if(SQL_TYPE_SYBASE_DEMO == m_SQL_TYPE)
|
{
|
str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
|
+ " HistoryBattery" + (gc_start.get(GregorianCalendar.MONTH) + 1)
|
+ " WHERE "
|
+ " StationId='" + st_id + "'"
|
+ " AND RecordTime>'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
|
+ " AND RecordTime<='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
|
+ " ORDER BY RecordTime " + time_order;
|
}
|
else if(SQL_TYPE_MSSQLSERVER == m_SQL_TYPE)
|
{
|
str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
|
+ " TBL_HistorySignal" + (gc_start.get(GregorianCalendar.MONTH) + 1)
|
+ " WHERE "
|
+ " StationId=" + st_id
|
+ " AND SampleTime>'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
|
+ " AND SampleTime<='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
|
+ " ORDER BY SampleTime " + time_order;
|
}
|
return str;
|
}
|
|
|
/**
|
* ¸ù¾Ý¿ªÊ¼Ê±¼äºÍ½áÊøʱ¼äÒÔ¼°id»ñÈ¡ÏàÓ¦µÄ²éѯÓï¾ä
|
* @param st_id
|
* @param gc_start
|
* @param gc_end
|
* @param time_order
|
* @return
|
*/
|
public String getQueryCurrentString(String st_id, GregorianCalendar gc_start,
|
GregorianCalendar gc_end, String time_order)
|
{
|
String str = "";
|
if(SQL_TYPE_SYBASE == m_SQL_TYPE)
|
{
|
str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
|
+ " HistorySignal" + (gc_start.get(GregorianCalendar.MONTH) + 1)
|
+ " WHERE "
|
+ " StationId=" + st_id
|
+ " AND RecordTime >'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
|
+ " AND RecordTime <='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
|
+ " ORDER BY RecordTime " + time_order;
|
}
|
|
return str;
|
}
|
|
public Connection getConnection(){
|
Connection con = null;
|
if((SQL_TYPE_SYBASE_DEMO == m_SQL_TYPE) || (SQL_TYPE_SYBASE == m_SQL_TYPE))
|
{
|
try {
|
Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
|
String url = "jdbc:sybase:Tds:" + mSQLServerIp + ":" + mSQLServerPort + "/SiteWeb";
|
Properties sysProps = System.getProperties();
|
sysProps.put("user", mSQLServerUsrName);
|
sysProps.put("password", mSQLServerPWD);
|
|
con = DriverManager.getConnection(url, sysProps);
|
} catch (InstantiationException | IllegalAccessException
|
| ClassNotFoundException | SQLException e) {
|
//e.printStackTrace();
|
//System.err.println(e.getMessage());
|
}
|
}
|
else if(SQL_TYPE_MSSQLSERVER == m_SQL_TYPE)
|
{
|
try {
|
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
|
String url = "jdbc:sqlserver://" + mSQLServerIp + ";DatabaseName=SiteWeb";
|
/*
|
String userName = "fuguang";
|
String userPwd = "jian(12)";
|
*/
|
con = DriverManager.getConnection(url, mSQLServerUsrName, mSQLServerPWD);
|
} catch (ClassNotFoundException | SQLException e) {
|
//e.printStackTrace();
|
}
|
}
|
|
return con;
|
}
|
|
/*
|
public class sybaseThread extends Thread
|
{
|
boolean dres;
|
public sybaseThread(boolean res)
|
{
|
dres = res;
|
}
|
|
@SuppressWarnings("deprecation")
|
@Override
|
public void run()
|
{
|
try
|
{
|
long count_tr = 0;
|
System.out.println(Thread.currentThread().getName());
|
while(true)
|
{
|
count_tr++;
|
Connection conn = sql_conn;//sybase_ds.getConnection();//getConnection();//
|
Statement sql = conn.createStatement();//conn .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
|
sql.setQueryTimeout(10);
|
String sqlstr = "select * from tb_battdata"; // ±í"sp_getTestData";//
|
ResultSet rs = sql.executeQuery(sqlstr);
|
|
while(rs.next())
|
{
|
System.out.println(Thread.currentThread().getName() + " " + count_tr + " "
|
+ rs.getString("StationId") + " "
|
+ rs.getString("StationName") + " "
|
+ rs.getTimestamp("rc_time").toLocaleString() +" "//.format(DateTimeFormatter.ofLocalizedDateTime(FormatStyle.FULL)) + " "
|
+ rs.getString("SignalName") + " "
|
+ rs.getString("SignalValue") + " "
|
);
|
}
|
|
sql.close();
|
conn.close();
|
|
//Thread.sleep(10);
|
}
|
}
|
catch (Exception e)
|
{
|
System.out.println(e.getMessage());
|
}
|
}
|
}
|
*/
|
public ResultSet getSqlres()
|
{
|
return sql_res;
|
}
|
public void close_conn() throws SQLException
|
{
|
if(null != sql_conn)
|
{
|
sql_conn.close();
|
sql_conn = null;
|
}
|
}
|
public boolean get_conn_state()
|
{
|
if(null == sql_conn)
|
return false;
|
else return true;
|
}
|
|
/**
|
* ¸ù¾Ý²éѯÓï¾ä»ñÈ¡ ResultSet ¼¯ºÏ
|
* @param sql_str ²éѯÓï¾ä
|
* @return ResultSet¼¯ºÏ
|
* @throws SQLException
|
*/
|
public ResultSet sqlSybaseQuery(String sql_str) throws SQLException
|
{
|
Statement sql = sql_conn.createStatement();
|
sql.setQueryTimeout(30);
|
ResultSet rs = sql.executeQuery(sql_str);
|
return rs;
|
}
|
|
public void sqlSybaseExecute(String str) throws SQLException
|
{
|
Statement sql = sql_conn.createStatement();
|
sql.setQueryTimeout(30);
|
sql.execute(str);
|
}
|
}
|
