数据库连接类
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) 工号
