JAVA操作数据库-->从一张表中取值,经过判断,然后插入另一张表中。
SQL语句如下:两张表 weather 和 weather_process.
id均为自动增长。Oracle中采用序列 Sequence
写的不对的地方,欢迎大家指出。您的建议,也是我的进步。谢谢!
DROP SEQUENCE weather_id;
CREATE SEQUENCE weather_id //创建序列
INCREMENT BY 1 //每次增加1
START WITH 1 //从1开始,即id从1开始 每次增1 如:1,2,3...
NOMAXVALUE //不设置最大值
NOCYCLE; //不用循环
插入值的时候,id 字段写成weather_id.nextval 便完成了自动增加的功能!
INSERT INTO WEATHER(id, weather) values (weather_id.nextval,'晴');
DROPTABLEWEATHER;
CREATETABLEWEATHER (
IDINTPRIMARYKEYNOTNULL,
weatherVARCHAR(20)NOTNULL
);
DROPWEATHER_PROCESS;
CREATETABLEWEATHER_PROCESS(
IDINTPRIMARYKEYNOTNULL,
阴intNOTNULLdefault0,
晴intNOTNULLdefault0,
雨intNOTNULLdefault0
);
DROPSEQUENCEweather_id;
CREATESEQUENCEweather_id
INCREMENTBY1
STARTWITH1
NOMAXVALUE
NOCYCLE;
DROPSEQUENCEweather_process_id ;
CREATESEQUENCEweather_process_id
MINVALUE 1
STARTWITH1
INCREMENTBY1;
INSERTINTOWEATHER(id, weather)values(weather_id.nextval,'晴');
INSERTINTOweather(id, weather)values(weather_id.nextval,'雨');
INSERTINTOweather(id, weather)values(weather_id.nextval,'阴');
INSERTINTOweather(id, weather)values(weather_id.nextval,'雨');
INSERTINTOweather(id, weather)values(weather_id.nextval,'阴');
INSERTINTOweather(id, weather)values(weather_id.nextval,'晴');
INSERTINTOweather(id, weather)values(weather_id.nextval,'雨');
INSERTINTOweather(id, weather)values(weather_id.nextval,'晴');
下面来说说这次的主题:根据weather 表中的weather字段 的值(晴,阴,雨),来决定表weather_process 中 阴 晴 雨 的值。
当weather 为 晴时,weather_process 中的值 设置为:0 1 0 (阴,晴,雨)。
当weather 为 阴时,weather_process 中的值 设置为:1 0 0 (阴,晴,雨)。
当weather 为 雨时,weather_process 中的值 设置为:0 0 1 (阴,晴,雨)。
上述采用JAVA 实现。
连接ORACLE 的代码:
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.SQLException;
/**
* A JDBC test application for Oracle
*
* @author Wang
* @version 1.0.0
* @since JDK1.6
*/
publicclassOracleConnect {
// 定义ORACLE的数据库驱动程序
publicstaticfinalString DBDRIVER ="oracle.jdbc.driver.OracleDriver";
// 定义ORACLE数据库的连接地址
publicstaticfinalString DBURL ="jdbc:oracle:thin:@localhost:1521:ORCL";
//ORACLE数据库的连接用户名
publicstaticfinalString DBUSER ="system";
// ORACLE数据库的连接密码
publicstaticfinalString DBPASS ="www.google.com";
publicConnection getConnection() {
Connection conn =null;// 数据库连接
try{
Class.forName(DBDRIVER) ;
}catch(ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}// 加载驱动程序
System.out.println("加载驱动成功!");
try{
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
}catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("连接成功!");
returnconn;
}
}
处理表的代码:
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
publicclassOracleProcess {
Connection conn =null;
Statement stmt =null;
publicOracleProcess(){
OracleConnect oraconn =newOracleConnect();
conn = oraconn.getConnection();
}
publicConnection getWeather() {
try{
stmt = conn.createStatement();
}catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String sql ="SELECT weather FROM WEATHER";
//String sql1 = "CREATE TABLE WEATHER_PROCESS(ID INT PRIMARY KEY NOT NULL,阴 int NOT NULL ,晴 int NOT NULL,雨 int NOT NULL)";
ResultSet result =null;
try{
stmt.executeUpdate(sql);
result = stmt.executeQuery(sql);
}catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try{
intflag =0;
while(result.next()) {
//System.out.print(result.getString("weather")+"、");
String str = result.getString("weather"); //获取表中weather的值
if("阴".equals(str)) {
stmt = conn.createStatement();
/*
*如果不重新stmt一个 则出现的是表中只会插入一条记录,
*然后整个循环就退出了。
*/
String sql2 ="INSERT INTO WEATHER_PROCESS(id, 晴,阴,雨) values (weather_process_id.nextval,0,1,0)";
stmt.executeUpdate(sql2);
System.out.print(str+"、");
}elseif("晴".equals(str)) {
stmt = conn.createStatement();
String sql2 ="INSERT INTO WEATHER_PROCESS(id, 晴,阴,雨) values (weather_process_id.nextval,1,0,0)";
stmt.executeUpdate(sql2);
System.out.print(str+"、");
}else{
stmt = conn.createStatement();
String sql2 ="INSERT INTO WEATHER_PROCESS(id, 晴,阴,雨) values (weather_process_id.nextval,0,0,1)";
stmt.executeUpdate(sql2);
System.out.print(str+"、");
}
flag ++;
}
System.out.println("\n此次一共更新了"+flag+"条语句");
}catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try{
result.close();
stmt.close();
conn.close();
}catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
returnconn;
}
}
欢迎共同交流,写的不对的地方还望大家谅解,呵呵。