oracle 无效的列索引

数据库连接类
public class Conn {
public Statement astmt = null;
public PreparedStatement stmt = null;
public CallableStatement cstmt = null;
public ResultSet rs = null;
private Connection con = null;

// public static String context = null;

public Conn() {
    try {
        // JndiSource ds = new JndiSource();

        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        con = con = DriverManager.getConnection(url, "jxhx", "jxhx");
        // con = ds.getDataSource();
        con.setAutoCommit(false);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public void getAStatement() throws SQLException {
    astmt = con.createStatement();
}

// 一般调用
public void setStatement(String statment) throws SQLException {
    stmt = con.prepareStatement(statment);
}

// 大数据量调用(不可滚动的结果集)
public void setRStatement(String statment) throws SQLException {
    stmt = con.prepareStatement(statment, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
}

// 大数据量调用(可滚动的结果集)
public void setNRStatement(String statment) throws SQLException {
    stmt = con.prepareStatement(statment,
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}

// 调用存储过程
public void setCStatement(String cstatment) throws SQLException {
    cstmt = con.prepareCall(cstatment);
}

// 关闭数据库连接
public void close() throws SQLException {
    if (this.rs != null)
        try {
            this.rs.close();
            this.rs = null;
        } catch (Exception e) {
        }
    if (this.stmt != null)
        try {
            this.stmt.close();
            this.stmt = null;
        } catch (Exception e) {
        }
    if (this.cstmt != null)
        try {
            this.cstmt.close();
            this.cstmt = null;
        } catch (Exception e) {
        }
    if (this.astmt != null)
        try {
            this.astmt.close();
            this.astmt = null;
        } catch (Exception e) {
        }
    if (this.con != null)
        try {
            this.con.close();
            this.con = null;
        } catch (Exception e) {
        }

    System.out.println("com_boco_db_DataAccess_close_Done!");
}

public void rollBack() throws SQLException {
    con.rollback();
}

public void commit() throws SQLException {
    con.commit();
}

public Connection getConnection() {
    return con;
}

public int getRecordCount(String SQL) throws SQLException {
    int retValue = 0;
    try {
        this.setRStatement(SQL);
        this.rs = this.stmt.executeQuery();
        while (this.rs.next()) {
            retValue = this.rs.getInt(1);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        return retValue;
    }
}

}

public class PstnService {
private Conn databassconn;
private InputStream in;
private PstnInfo pin;

public PstnService(File file) {
    databassconn = new Conn();
    try {
        pin = new PstnInfo(file);
        in = new FileInputStream(file);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (JXLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

业务类
public int insertPartition_1() throws Exception {
int k = 0;
int PARTITION_1 = 0;
List list = pin.setPstnDTO();
// for (int i = 0; i < list.size(); i++) {
PstnDTO pd = (PstnDTO) list.get(0);
String sql = "select count(PARTITION_1) from Tel_res_partition_1 where PARTITION_1_NAME=?";
try {
databassconn.setStatement(sql);
databassconn.stmt.setString(1, pd.getPSTN_NAME());
databassconn.rs = databassconn.stmt.executeQuery();
while (databassconn.rs.next()) {
System.out.println(databassconn.rs.getInt(1));
if (databassconn.rs.getInt(1) != 0) {
String sql2 = "update TEL_RES_PARTITION_1 set PARTITION_1_NAME=?,VER_NUMBER=?,INSERT_TIME=systdate,INSERT_FLAG=?,USERNAME=?"
+ " where PARTITION_1=?";
databassconn.setStatement(sql2);
System.out.print(sql2);
databassconn.stmt.setString(1, pd.getPSTN_NAME());
databassconn.stmt.setInt(2, 0);
databassconn.stmt.setInt(3, 0);
databassconn.stmt.setString(4, null);
databassconn.stmt.setInt(5, getid());
databassconn.stmt.executeUpdate();
databassconn.commit();
k = 1;
} else {
String sql1 = "INSERT INTO TEL_RES_PARTITION_1(PARTITION_1,PARTITION_1_NAME,VER_NUMBER,INSERT_TIME,INSERT_FLAG,USERNAME )"
+ "VALUES(?,?,?,systdate,?,?)";

                databassconn.setStatement(sql1);
                databassconn.stmt.setInt(1, getid());
                System.out.println(pd.getPSTN_NAME());
                [color=blue][b]databassconn.stmt.setString(2, pd.getPSTN_NAME());[/b][/color]
                databassconn.stmt.setInt(3, 0);
                databassconn.stmt.setInt(4, 0);
                databassconn.stmt.setString(5, null);
                k = databassconn.stmt.executeUpdate();
                databassconn.commit();
                System.out.println("" + k);
                k = 1;
            }
        }
        databassconn.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return k;
}

每次执行都会出现
0
顺德本地交换局
java.sql.SQLException: 无效的列索引
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4596)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4559)
at com.dbconn.service.PstnService.insertPartition_1(PstnService.java:160)
at com.dbconn.Testconn.main(Testconn.java:24)0
我的表结构是
PARTITION_1为主键,无索引。 PstnService.java:160蓝色部分 指点下 感激不尽,程序没有错误,就是在插入时这个错误 我用的是Oracle 10g ; 我的MSN和邮箱: wangbaoping@boco.com.cn

[b]问题补充:[/b]
我的SQL语句没有问题,我在SQLplus上可以执行的
这个是表结构
字段标识 类型属性 功能简述
PARTITION_1 NUMBER(9) 区1
PARTITION_1_NAME VARCHAR2(50) 区1名称
VER_NUMBER NUMBER(9) vernumber
INSERT_TIME DATE 倒入时间
INSERT_FLAG NUMBER(6) 倒入标志'o'预提交 '1'实提交

USERNAME VARCHAR2(30) 工号

9个回答

是你的databassconn.stmt=null,没有传进值,在
databassconn.setStatement(sql1);
if(databassconn=null){
System.out.println(--------);
}if(databassconn.stmt=null){
System.out.println(aaaaaaa----------);}
试一下。

java.sql.SQLException: 无效的列索引.
首先:确认你的sql语句拼写是否有问题?
你把表结束贴出来!

你仔细看一下!你设置参数类型与元数据列还一致?

看看是不是你的insert语句中的列名和表的列名不一致
或者你的表的列数和insert语句中的列数不一样

(PARTITION_1,PARTITION_1_NAME,VER_NUMBER,INSERT_TIME,INSERT_FLAG,USERNAME )"

  • "VALUES(?,?,?,systdate,?,?)"; 这里应该是六个值,其中一个是常量,那下面就应该set五个值,个人认为你插入的位置顺序不对,修改成这样试试。 databassconn.stmt.setInt(1, getid()); System.out.println(pd.getPSTN_NAME()); databassconn.stmt.setString(2, pd.getPSTN_NAME()); databassconn.stmt.setInt(3, 0); databassconn.stmt.setInt(5, 0); databassconn.stmt.setString(6, null); 没有仔细看,因为是上班,所以就看出了这么点的小问题,如果答对了,记得给我加分啊,为了以后方便我提问题嘛。

你程序请求的表空间和你plsql运行的表空间不一致~

  • "VALUES(?,?,?,systdate,?,?)"; 这前面是不是要加一个空格,没空试,我原来就是这样的错.所以发现了这个问题的写法不行
  • "VALUES(?,?,?,systdate,?,?)"; 这里面的systdate有问题
  • "VALUES(?,?,?,systdate,?,?)"; 这里面的systdate有问题,不是时间函数:sysdate,多了个t
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问