java – 如何修复com.mysql.jdbc.exceptions.jdbc4.MySQLNonTran
发布时间：2020-12-15 04:24:50 所属栏目：Java 来源：网络整理
导读：这是我保存记录的查询. public void saveTotal(JTextField txtGtotal,JTextField txtPTotal) { try { ResultSet rs = JDBC.getData("select MAX(or_id) as or_id from `order`"); if (rs.first()) { if (rs.getInt("or_id") 0) { try { String date1 = new V
|
这是我保存记录的查询.
public void saveTotal(JTextField txtGtotal,JTextField txtPTotal) {
try {
ResultSet rs = JDBC.getData("select MAX(or_id) as or_id from `order`");
if (rs.first()) {
if (rs.getInt("or_id") > 0) {
try {
String date1 = new Validation().today();
boolean b1 = JDBC.putData("insert into transaction(tr_date,amount,tr_type) values ('" + date1 + "','" + txtGtotal.getText() + "','order')");
if (b1) {
try {
ResultSet rs1 = JDBC.getData("select MAX(tr_id) as tr_id from transaction");
if (rs1.first()) {
try {
boolean b2 = JDBC.putData("insert into transaction(tr_date,'" + txtPTotal.getText() + "','profit')");
if (b2) {
try {
ResultSet rs2 = JDBC.getData("select MAX(tr_id) as tr_id from transaction");
if (rs2.first()) {
try {
boolean b3 = JDBC.putData("insert into o_de(or_id,tr_id,oday,gtotal) values ('" + rs.getInt("or_id") + "','" + rs1.getInt("tr_id") + "','" + date1 + "','" + txtGtotal.getText() + "' )");
if (b3) {
try {
boolean b4 = JDBC.putData("insert into order_profit(or_id,ptotal) values ('" + rs.getInt("or_id") + "','" + txtPTotal.getText() + "' )");
if (b4) {
JDBC.commit();
JOptionPane.showMessageDialog(null,"Order Saved Sucessfully..");
JDBC.putClose();
JDBC.conClose();
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
} finally {
JDBC.putClear();
JDBC.conClear();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
}
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
}
}
} catch (Exception e) {
JDBC.rollback();
e.printStackTrace();
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
这是我的JDBC类. package Modle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JDBC {
static Connection con = null;
static boolean b;
static PreparedStatement state;
public static void setCon() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lottery","root","123");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static Connection getCon() throws Exception {
if (con == null) {
setCon();
}
return con;
}
public static boolean putData(String sql) {
try {
getCon().setAutoCommit(false);
state = getCon().prepareStatement(sql);
state.executeUpdate();
b = true;
} catch (Exception e) {
e.printStackTrace();
b = false;
}
return b;
}
// connection commit
public static void commit() {
try {
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
// rollback data
public static void rollback() {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,null,ex);
}
}
}
// close statement
public static void putClose() {
try {
state.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
// close connection
public static void conClose() {
try {
con.setAutoCommit(true);
con.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
// clear prepared statement
public static void putClear() {
try {
if (state != null && !state.isClosed()) {
state.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
// clear the connection
public static void conClear() {
try {
if (con != null && !con.isClosed()) {
con.setAutoCommit(true);
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
public static ResultSet getData(String sql) throws Exception {
Statement state = getCon().createStatement();
ResultSet rs = state.executeQuery(sql);
return rs;
}
}
这是我在mysql中第一次尝试事务处理.我知道这不是借口.但我对这种连接处理只有一个粗略的想法.如果我的代码不完善,请给我一个演示答案. 如何解决此问题连接关闭异常后是否允许操作？谢谢. 添加了堆栈跟踪.我认为还有另外2个地方也发现了同样的错误.他们在运行此查询之前来了. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:525) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1206) at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1198) at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2484) at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2466) at Modle.JDBC.getData(JDBC.java:115) at Controler.NewOrderCon2.saveTotal(NewOrderCon2.java:196) at lottery.NewOrder.jButton6ActionPerformed(NewOrder.java:2350) at lottery.NewOrder.access$1200(NewOrder.java:28) at lottery.NewOrder$13.actionPerformed(NewOrder.java:537) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252) at java.awt.Component.processMouseEvent(Component.java:6505) at javax.swing.JComponent.processMouseEvent(JComponent.java:3321) at java.awt.Component.processEvent(Component.java:6270) at java.awt.Container.processEvent(Container.java:2229) at java.awt.Component.dispatchEventImpl(Component.java:4861) at java.awt.Container.dispatchEventImpl(Container.java:2287) at java.awt.Component.dispatchEvent(Component.java:4687) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422) at java.awt.Container.dispatchEventImpl(Container.java:2273) at java.awt.Window.dispatchEventImpl(Window.java:2719) at java.awt.Component.dispatchEvent(Component.java:4687) at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:729) at java.awt.EventQueue.access$200(EventQueue.java:103) at java.awt.EventQueue$3.run(EventQueue.java:688) at java.awt.EventQueue$3.run(EventQueue.java:686) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76) at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87) at java.awt.EventQueue$4.run(EventQueue.java:702) at java.awt.EventQueue$4.run(EventQueue.java:700) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76) at java.awt.EventQueue.dispatchEvent(EventQueue.java:699) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138) at java.awt.EventDispatchThread.run(EventDispatchThread.java:91) 解决方法
您是否意识到您实际上是在GUI中访问后端？您正在将文本字段中的参数直接传递到数据库.这是主要的fuckups的来源.至少验证您的输入,或
Little Bobby Tables将提前终止您的工作合同.
至于你的错误：对不起,但这段代码需要重大的重构.单独按行计数这个代码做得太多了.第一条黄金法则：保持你的方法简短.第二条黄金法则：缩短它们. 事实上,你自己不明白发生了什么事情对你来说是个大红灯,并表明你需要重新考虑你的设计. >使用JDBC.putData()独立编写内容的方法. 我想它是JDBC中对connection.close()的过早调用.通过将操作分成更原子的操作,您可以更好地推理代码,从而理解手头的错误. 很抱歉没有提供解决方案,但从长远来看,您最好遵守一些代码原则.学习他们！越快越好,因为我需要更多的业力： 然后你将走上启蒙的道路,因此使用DAOFactory(暗示)和DAO设计模式(也暗示)并成为一个编码器之神.恭喜！ 好吧,这里有一个关于重构如何看的小指南.没有完成和未经测试,我想我已经完成了SQL插入序??列(不知道在哪里使用了transactionId).但我希望你能得到一个主意. package mysqlfix;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTextField;
public class JDBC {
static Connection con = null;
static boolean b;
static PreparedStatement state;
public static void setCon() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lottery","123");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static Connection getCon() throws Exception {
if (con == null) {
setCon();
}
return con;
}
public static boolean putData(String sql) {
try {
getCon().setAutoCommit(false);
state = getCon().prepareStatement(sql);
state.executeUpdate();
getCon().commit();
b = true;
} catch (Exception e) {
e.printStackTrace();
b = false;
}
return b;
}
// connection commit
public static void commit() {
try {
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
// rollback data
public static void rollback() {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
}
// close statement
public static void putClose() {
try {
state.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
// close connection
public static void conClose() {
try {
con.setAutoCommit(true);
con.close();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
// clear prepared statement
public static void putClear() {
try {
if (state != null && !state.isClosed()) {
state.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
// clear the connection
public static void conClear() {
try {
if (con != null && !con.isClosed()) {
con.setAutoCommit(true);
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
}
public static ResultSet getData(String sql) throws Exception {
Statement state = getCon().createStatement();
ResultSet rs = state.executeQuery(sql);
return rs;
}
public void saveTotal(JTextField txtGtotal,JTextField txtPTotal) {
SuperDAO superDAO = new SuperDAO();
if (superDAO.getMaxIdFromOrder() > 0) {
Date date1;
date1 = new Date();
String txtGTotalFromTextField = txtGtotal.getText();
String txtPTotalFromTextField = txtPTotal.getText();
boolean b1 = false;
//regarding the transaction id...
//this changes whilst updating the table transaction.
int transactionId = -1;
if (txtGTotalFromTextField.matches("[a-zA-Z]")) {
transactionId = superDAO.insertOrderIntoTransaction(date1,txtGTotalFromTextField);
//b1 = JDBC.putData("insert into transaction(tr_date,'" + txtGTotalFromTextField + "','order')");
}
if (transactionId > 0) {
try {
} catch (Exception ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE,ex);
}
if (txtPTotalFromTextField.matches("[a-zA-Z]")) {
transactionId = superDAO.insertProfitIntoTransaction(date1,txtGTotalFromTextField);
}
JDBC.putData("insert into o_de(or_id,gtotal) values ('" + superDAO.getMaxIdFromOrder() + "','" + transactionId + "','" + txtGtotal.getText() + "' )");
JDBC.putData("insert into order_profit(or_id,ptotal) values ('" + superDAO.getMaxIdFromOrder() + "','" + txtPTotal.getText() + "' )");
//JDBC.commit();
//JOptionPane.showMessageDialog(null,"Order Saved Sucessfully..");
JDBC.putClose();
JDBC.conClose();
}
}
}
}
package mysqlfix;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author edm
*/
public class SuperDAO {
Connection conn;
public SuperDAO() {
try {
this.conn = JDBC.getCon();
} catch (Exception ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE,ex);
}
}
public int getMaxIdFromOrder() {
try {
ResultSet rs = JDBC.getData("select MAX(or_id) as or_id from `order`");
if (rs.first()) {
return rs.getInt("or_id");
}
} catch (SQLException ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE,ex);
} catch (Exception ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE,ex);
}
return -1;
}
public int getMaxIdFromTransaction() {
ResultSet rs;
try {
rs = JDBC.getData("select MAX(tr_id) as tr_id from transaction");
if (rs.first()) {
return rs.getInt("tr_id");
}
} catch (Exception ex) {
Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE,ex);
}
return -1;
}
public int insertOrderIntoTransaction(Date date,String text) {
JDBC.putData("insert into transaction(tr_date,tr_type) values ('" + date + "','" + text + "','order')");
return getMaxIdFromTransaction();
}
public int insertProfitIntoTransaction(Date date,'profit')");
return getMaxIdFromTransaction();
}
}
当然旅程并不止于此.我没有完成JDBC saveTotal().我刚刚开始,你做其余的. 请注意,我没有针对数据库测试此代码(某些sql ddl文件丢失).另外,我没有使用回滚机制.此外,saveTotal()存在于JDBC中,它不属于JDBC.在GUI中使用saveTotal(如果需要),让所有数据库访问都通过SuperDAO.这不是最好的设计,但它不是太抽象,您可以轻松地看到关注点的分离如何使您的代码更具可读性和可维护性. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |