oracle 插入自动排序,科学网—java处理数据连接oracle，建表，按要求插入数据，处理数据排序等 - 倪升华的博文...

package com.go;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Timestamp;

import java.util.Date;

import java.util.Scanner;

import java.text.DateFormat;

import java.text.ParseException;

import java.text.SimpleDateFormat;

public class yjshujv {

//createnewtable nt;

String kkid;

String dfs;

Date df;

String dd;

int interval;

public yjshujv() {

// TODO 自动生成的方法存根

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","8621903");

Statement sm=ct.createStatement();

Scanner s=new Scanner(System.in);

System.out.println("输入卡口号");

kkid = s.next();

System.out.println("输入日期");

dfs = s.next();

System.out.println("输入统计间隔");

interval = s.nextInt();

//将string类型转变为Data类型

SimpleDateFormat df1 = new SimpleDateFormat("yyyy/MM/dd");

try {

df = df1.parse(dfs);

SimpleDateFormat df2 = new SimpleDateFormat("yyyy_MM_dd");

dd = df2.format(df);

System.out.println("dd="+dd);

} catch (ParseException e) {

// TODO 自动生成的 catch 块

e.printStackTrace();

}

/*

* SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date now = new Date();

System.out.println("当前时间：" + sdf.format(now));

方法一：

Date afterDate = new Date(now .getTime() + 300000);

System.out.println(sdf.format(afterDate ));

方法二：

Calendar nowTime = Calendar.getInstance();

nowTime.add(Calendar.MINUTE, 5);

System.out.println(sdf.format(nowTime.getTime()));

*

*/

//System.out.println(df+kkid+interval);

//提交语句

String sqltj="commit";

//创建子表

String sqlct="CREATE TABLE "+"cltx_"+dd+"(xh NUMBER NOT NULL,jgsj DATE NOT NULL,clsd NUMBER(4,1) NOT NULL)" ;

System.out.println(sqlct);

sm.executeUpdate(sqlct);

System.out.println(sqltj);

sm.executeUpdate(sqltj);

//插入数据

String sqlin="insert into "+"cltx_"+dd+" (xh,jgsj,clsd)"+" select xh,jgsj,clsd"+" FROM cltx_201203 where kkid='"+kkid+"' and jgsj>=to_date('"+dfs+" 00:00:00','yyyy/mm/dd/ hh24:mi:ss') and jgsj<=(to_date('"+dfs+" 00:00:00','yyyy/mm/dd/ hh24:mi:ss')+1)" ;

System.out.println(sqlin);

sm.executeUpdate(sqlin);

System.out.println(sqltj);

sm.executeUpdate(sqltj);

//处理数据

//处理1分钟分组数据

String sqlcl="select to_char(jgsj,'yyyy/mm/dd hh24:mi'),count(jgsj),avg(clsd) from "+"cltx_"+dd+" group by to_char(jgsj,'yyyy/mm/dd hh24:mi') order by to_char(jgsj,'yyyy/mm/dd hh24:mi')" ;

System.out.println(sqlcl);

ResultSet rs=sm.executeQuery(sqlcl);

//   String sql="select * from"+" cltx_"+dd+"where xh=220338989 " ;

//   ResultSet rs=sm.executeQuery("select * from emp");

//   ResultSet rs1=sm.executeQuery(sql);、

//显示1分钟数据

while(rs.next()){

//    System.out.println("用户名"+rs.getString(2));

System.out.println(rs.getString("count(jgsj)")+","+rs.getString("avg(clsd)"));

//改成csv输出

}

//处理5分钟分组数据

//一个循环处理0-1440分钟，t=1440/interval(math.ceil)

for (int i = 0; i < Math.ceil(1440/(double)interval); i++) {

String sqlcl5="select to_char(jgsj,'yyyy/mm/dd hh24:mi'),count(jgsj),avg(clsd) from "+"cltx_"+dd+" group by to_char(jgsj,'yyyy/mm/dd hh24:mi:ss') order by to_char(jgsj,'yyyy/mm/dd hh24:mi:ss')" ;

System.out.println(sqlcl5);

ResultSet rs1=sm.executeQuery(sqlcl5);

//显示1分钟数据

while(rs1.next()){

System.out.println(rs1.getString("count(jgsj)")+","+rs1.getString("avg(clsd)"));

//改成csv输出

}

}

rs1.close();

rs.close();

sm.close();

ct.close();

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally{

}

}

public static void main(String[] args) {

yjshujv yjsj=new yjshujv();

}

}

//class createnewtable

//{

// String kkid;

// Date df;

// int interval;

//

// public String getKkid() {

//  return kkid;

// }

//

// public void setKkid(String kkid) {

//  this.kkid = kkid;

// }

//

// public Date getDf() {

//  return df;

// }

//

// public void setDf(Timestamp df) {

//  this.df = df;

// }

//

// public int getInterval() {

//  return interval;

// }

//

// public void setInterval(int interval) {

//  this.interval = interval;

// }

// public createnewtable(String kkid,String df, int interval) {

//  this.kkid=kkid;

//  this.interval=interval;

//  SimpleDateFormat df1 = new SimpleDateFormat("yyyy/MM/dd");

//  try {

//   this.df = df1.parse(df);

//  } catch (ParseException e) {

//   // TODO 自动生成的 catch 块

//   e.printStackTrace();

//  }

//  System.out.println(df+kkid+interval);

//

//   try {

//    Class.forName("oracle.jdbc.driver.OracleDriver");

//    Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","8621903");

//    Statement sm=ct.createStatement();

//

//    Scanner s=new Scanner(System.in);

//    System.out.println("输入卡口号");

//    kkid = s.next();

//    System.out.println("输入日期");

//    df = s.next();

//    System.out.println("输入统计间隔");

//    interval = s.nextInt();

//

//    createnewtable nt=new createnewtable(kkid, df, interval);

//

//

//    String sql="select * from cltx_201203 where xh=220338989 " ;

   ResultSet rs=sm.executeQuery("select * from emp");

//    ResultSet rs=sm.executeQuery(sql);

//    while(rs.next()){

//

    System.out.println("用户名"+rs.getString(2));

//     System.out.println(rs.getString("kkid")+","+rs.getString("jgsj")+","+rs.getString("clsd"));

//    }

//

//    rs.close();

//    sm.close();

//    ct.close();

//   } catch (Exception e) {

//    // TODO: handle exception

//    e.printStackTrace();

//   }

//

// }

//

//

//

//

//}

//sm.execute(sqlin);

//while (true) {

//int rowCount = sm.getUpdateCount();

//if (rowCount > 0) {

//int count = 0;

//System.out.println("Rows changed = " + count);

//sm.getMoreResults();

//continue;

//}

//if (rowCount == 0) {

//System.out.println(" No rows changed or statement was DDL command");

//sm.getMoreResults();

//continue;

//}

//ResultSet rs = sm.getResultSet();

//if (rs != null) {

//while (rs.next()) {

//sm.getMoreResults();

//continue;

//}

//break;

//}

//}

转载本文请联系原作者获取授权，同时请注明本文来自倪升华科学网博客。

链接地址：http://blog.sciencenet.cn/blog-785542-722822.html

上一篇：交通数据挖掘笔记

下一篇：九月十号，老师们节日快乐！