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,'晴');
- DROP TABLE WEATHER;
- CREATE TABLE WEATHER (
- ID INT PRIMARY KEY NOT NULL,
- weather VARCHAR(20) NOT NULL
- );
- DROP WEATHER_PROCESS;
- CREATE TABLE WEATHER_PROCESS(
- ID INT PRIMARY KEY NOT NULL,
- 阴 int NOT NULL default 0,
- 晴 int NOT NULL default 0,
- 雨 int NOT NULL default 0
- );
- DROP SEQUENCE weather_id;
- CREATE SEQUENCE weather_id
- INCREMENT BY 1
- START WITH 1
- NOMAXVALUE
- NOCYCLE;
- DROP SEQUENCE weather_process_id ;
- CREATE SEQUENCE weather_process_id
- MINVALUE 1
- START WITH 1
- INCREMENT BY 1;
- INSERT INTO WEATHER(id, weather) values (weather_id.nextval,'晴');
- INSERT INTO weather(id, weather) values (weather_id.nextval,'雨');
- INSERT INTO weather(id, weather) values (weather_id.nextval,'阴');
- INSERT INTO weather(id, weather) values (weather_id.nextval,'雨');
- INSERT INTO weather(id, weather) values (weather_id.nextval,'阴');
- INSERT INTO weather(id, weather) values (weather_id.nextval,'晴');
- INSERT INTO weather(id, weather) values (weather_id.nextval,'雨');
- INSERT INTO weather(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 的代码:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- /**
- * A JDBC test application for Oracle
- *
- * @author Wang
- * @version 1.0.0
- * @since JDK1.6
- */
- public class OracleConnect {
- // 定义ORACLE的数据库驱动程序
- public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver" ;
- // 定义ORACLE数据库的连接地址
- public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:ORCL" ;
- //ORACLE数据库的连接用户名
- public static final String DBUSER = "system" ;
- // ORACLE数据库的连接密码
- public static final String DBPASS = "www.google.com" ;
- public Connection 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("连接成功!");
- return conn;
- }
- }
处理表的代码:
- import java.sql.Connection;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class OracleProcess {
- Connection conn = null;
- Statement stmt = null;
- public OracleProcess(){
- OracleConnect oraconn = new OracleConnect();
- conn = oraconn.getConnection();
- }
- public Connection 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 {
- int flag = 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+"、");
- }else if ("晴".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();
- }
- return conn;
- }
- }
欢迎共同交流,写的不对的地方还望大家谅解,呵呵。
本文转自 w156445045 51CTO博客,原文链接:http://blog.51cto.com/enetq/502734,如需转载请自行联系原作者