同样的数据,通过cmd插入ORACLE数据库能正常的插入,但通过JDBC插入会报 :值大于为此列指定的允许精度的错误,请各位大神帮我看看,十分感谢!!
package com.study.jdbc.emppo;public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private int sal;
private int comm;
private int deptno; public int getEmpno() {
return empno;
} public void setEmpno(int empno) {
this.empno = empno;
} public String getEname() {
return ename;
} public void setEname(String ename) {
this.ename = ename;
} public String getJob() {
return job;
} public void setJob(String job) {
this.job = job;
} public int getMgr() {
return mgr;
} public void setMgr(int mgr) {
this.mgr = mgr;
} public String getHiredate() {
return hiredate;
} public void setHiredate(String hiredate) {
this.hiredate = hiredate;
} public int getSal() {
return sal;
} public void setSal(int sal) {
this.sal = sal;
} public int getComm() {
return comm;
} public void setComm(int comm) {
this.comm = comm;
} public int getDeptno() {
return deptno;
} public void setDeptno(int deptno) {
this.deptno = deptno;
}}package com.study.jdbc.emppo;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;import com.study.jdbc.po.GetConnection;public class EmpDao {
private Connection conn = null;
public EmpDao(){
//初始化与数据库的连接
GetConnection getConn = new GetConnection();
conn = getConn.getConnection();
}
// 添加员工信息
public int insertEmpInfo(Emp emp)throws SQLException{
String sql = "INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(?,?,?,?,TO_DATE(?,'YYYY-MM-DD'),?,?,?)";
PreparedStatement stmt = conn.prepareCall(sql);
stmt.setInt(1, emp.getEmpno());
stmt.setString(2, emp.getEname());
stmt.setString(3, emp.getJob());
stmt.setInt(4, emp.getMgr());
stmt.setString(5, emp.getHiredate());
stmt.setInt(6, emp.getSal());
stmt.setInt(7, emp.getComm());
stmt.setInt(8, emp.getDeptno());
System.out.println(sql);
return stmt.executeUpdate();
}
}package com.study.jdbc.emppo;import java.sql.SQLException;public class EmpDaoTest { private EmpDao empDao = new EmpDao();
public void testInsertEmpInfo(){
try {
Emp emp = new Emp();
emp.setDeptno(60);
emp.setComm(200);
emp.setEmpno(1111);
emp.setEname("wsdf");
emp.setJob("学生");
emp.setMgr(33514);
emp.setSal(3500);
emp.setHiredate("2012-11-02");
int refRows = empDao.insertEmpInfo(emp);
System.out.println("refRows->"+refRows);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
EmpDaoTest test = new EmpDaoTest();
test.testInsertEmpInfo(); }}package com.study.jdbc.po;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class GetConnection {
private Connection conn;
private static Connection conn2;
public Connection getConnection() {
try {
Class.forName("oracle.jdbc.OracleDriver");
// 驱动管理器在jvm中寻找已注册的数据库的驱动类(OracleDriver类型的运行期对象Class)
// 并且创建该驱动类的实例,并调用连接方法创建一个和该驱动类指向的数据库的连接对象
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
conn = DriverManager.getConnection(url, user, password); System.out.println(conn != null ? "连接成功" : "连接失败");
conn.setAutoCommit(false);
//Statement stmt = conn.createStatement();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} return conn;
}
}
package com.study.jdbc.emppo;public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private int sal;
private int comm;
private int deptno; public int getEmpno() {
return empno;
} public void setEmpno(int empno) {
this.empno = empno;
} public String getEname() {
return ename;
} public void setEname(String ename) {
this.ename = ename;
} public String getJob() {
return job;
} public void setJob(String job) {
this.job = job;
} public int getMgr() {
return mgr;
} public void setMgr(int mgr) {
this.mgr = mgr;
} public String getHiredate() {
return hiredate;
} public void setHiredate(String hiredate) {
this.hiredate = hiredate;
} public int getSal() {
return sal;
} public void setSal(int sal) {
this.sal = sal;
} public int getComm() {
return comm;
} public void setComm(int comm) {
this.comm = comm;
} public int getDeptno() {
return deptno;
} public void setDeptno(int deptno) {
this.deptno = deptno;
}}package com.study.jdbc.emppo;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;import com.study.jdbc.po.GetConnection;public class EmpDao {
private Connection conn = null;
public EmpDao(){
//初始化与数据库的连接
GetConnection getConn = new GetConnection();
conn = getConn.getConnection();
}
// 添加员工信息
public int insertEmpInfo(Emp emp)throws SQLException{
String sql = "INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(?,?,?,?,TO_DATE(?,'YYYY-MM-DD'),?,?,?)";
PreparedStatement stmt = conn.prepareCall(sql);
stmt.setInt(1, emp.getEmpno());
stmt.setString(2, emp.getEname());
stmt.setString(3, emp.getJob());
stmt.setInt(4, emp.getMgr());
stmt.setString(5, emp.getHiredate());
stmt.setInt(6, emp.getSal());
stmt.setInt(7, emp.getComm());
stmt.setInt(8, emp.getDeptno());
System.out.println(sql);
return stmt.executeUpdate();
}
}package com.study.jdbc.emppo;import java.sql.SQLException;public class EmpDaoTest { private EmpDao empDao = new EmpDao();
public void testInsertEmpInfo(){
try {
Emp emp = new Emp();
emp.setDeptno(60);
emp.setComm(200);
emp.setEmpno(1111);
emp.setEname("wsdf");
emp.setJob("学生");
emp.setMgr(33514);
emp.setSal(3500);
emp.setHiredate("2012-11-02");
int refRows = empDao.insertEmpInfo(emp);
System.out.println("refRows->"+refRows);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
EmpDaoTest test = new EmpDaoTest();
test.testInsertEmpInfo(); }}package com.study.jdbc.po;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class GetConnection {
private Connection conn;
private static Connection conn2;
public Connection getConnection() {
try {
Class.forName("oracle.jdbc.OracleDriver");
// 驱动管理器在jvm中寻找已注册的数据库的驱动类(OracleDriver类型的运行期对象Class)
// 并且创建该驱动类的实例,并调用连接方法创建一个和该驱动类指向的数据库的连接对象
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
conn = DriverManager.getConnection(url, user, password); System.out.println(conn != null ? "连接成功" : "连接失败");
conn.setAutoCommit(false);
//Statement stmt = conn.createStatement();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} return conn;
}
}
SQL> INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(1111,
'wsdf','学生',0521,TO_DATE ('2012-11-02','yyyy-mm-dd'),3500,200,60);已创建 1 行。SQL> SELECT * FROM T_EMP WHERE deptno=60; EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
1111 wsdf 学生 521 02-11月-12 3500 200
60SQL> desc t_emp;
名称 是否为空? 类型
----------------------------------------- -------- ------------------ EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)在java中的结果连接成功
INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(?,?,?,?,TO_DATE(?,'YYYY-MM-DD'),?,?,?)
java.sql.SQLException: ORA-01438: 值大于为此列指定的允许精度 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:3861)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1062)
at com.study.jdbc.emppo.EmpDao.insertEmpInfo(EmpDao.java:31)
at com.study.jdbc.emppo.EmpDaoTest.testInsertEmpInfo(EmpDaoTest.java:19)
at com.study.jdbc.emppo.EmpDaoTest.main(EmpDaoTest.java:30)
往数据库写时就会有异常,而且写不进去。异常:the jar file mysql-connector-java-5.1.22-bin has no source attachment
这个地方不要这样用,
你可以把你的to_date(youdateval,'yyyy-MM-dd')这样看看。
这句话的问题,列MGR为NUMBER(4)
DAO设计模式我这么写关于JDBC的:
package cn.yla.dbc;import java.sql.Connection;
import java.sql.DriverManager;public class DatabaseConnection { private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
private static final String DBURL = "jdbc:mysql://localhost:3306/数据库名";
private static final String DBUSER = "root";
private static final String PASSWORD = "mysqladmin"; private Connection conn;
public DatabaseConnection(){
try {
Class.forName(DBDRIVER);
this.conn = DriverManager.getConnection(DBURL, DBUSER,
PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection(){
return this.conn;
}
public void close(){
if(this.conn != null){
try {
this.conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
不喜欢的话勿喷