Java 如何将数据从jtable插入mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24282159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-14 11:17:22 来源:igfitidea点击:
how to insert data from jtable to mysql database
提问by T.K
I want to insert jtable multiple rows data into mysql database on save button click. It gives some error....
我想在单击保存按钮时将 jtable 多行数据插入到 mysql 数据库中。它给出了一些错误......
This is my code...
这是我的代码...
package my.bill;
import java.awt.*;
import java.awt.event.*;
import java.awt.event.*;
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.Calendar;
import java.util.GregorianCalendar;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.*;
import javax .swing.table.DefaultTableModel;
public class bill extends javax.swing.JFrame {
/**
* Creates new form bill
*/
Object[][] data=null;
String[] columnNames = new String[2];
/*static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL="jdbc:mysql://localhost:3306/gdb?zeroDateTimeBehavior=convertToNull";
//static final String DB_URL = "jdbc:mysql://localhost/";
// Database credentials
static final String USER = "root";
static final String PASS = "root";*/
public bill() {
initComponents();
CurrentDate();
}
public void CurrentDate(){
Calendar cal=new GregorianCalendar();
int month=cal.get(Calendar.MONTH);
int year=cal.get(Calendar.YEAR);
int day=cal.get(Calendar.DAY_OF_MONTH);
Date.setText(day+"-"+(month+1)+"-"+year);
}
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">
private void initComponents() {
jLabel1 = new javax.swing.JLabel();
Date = new javax.swing.JTextField();
jScrollPane2 = new javax.swing.JScrollPane();
jTable1 = new javax.swing.JTable();
netprofit = new javax.swing.JButton();
netsum = new javax.swing.JTextField();
save = new javax.swing.JButton();
jMenuBar1 = new javax.swing.JMenuBar();
jMenu1 = new javax.swing.JMenu();
jMenu2 = new javax.swing.JMenu();
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
jLabel1.setText("Date");
Date.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
DateActionPerformed(evt);
}
});
jTable1.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 0, 0)));
jTable1.setFont(new java.awt.Font("Tahoma", 0, 18)); // NOI18N
jTable1.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
{"", null, null, null, null, null},
{"", null, null, null, "", null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null},
{null, null, null, null, null, null}
},
new String [] {
"Job No", "Item", "Billed Amount", "Parts cost", "Net Profit", "Percentage"
}
));
jTable1.setColumnSelectionAllowed(true);
jTable1.setGridColor(new java.awt.Color(0, 0, 0));
jTable1.setRowHeight(20);
jTable1.setRowMargin(2);
jTable1.setSelectionBackground(new java.awt.Color(255, 255, 255));
jTable1.addInputMethodListener(new java.awt.event.InputMethodListener() {
public void caretPositionChanged(java.awt.event.InputMethodEvent evt) {
}
public void inputMethodTextChanged(java.awt.event.InputMethodEvent evt) {
jTable1InputMethodTextChanged(evt);
}
});
jScrollPane2.setViewportView(jTable1);
jTable1.getColumnModel().getSelectionModel().setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
if (jTable1.getColumnModel().getColumnCount() > 0) {
jTable1.getColumnModel().getColumn(0).setHeaderValue("Job No");
jTable1.getColumnModel().getColumn(1).setHeaderValue("Item");
jTable1.getColumnModel().getColumn(2).setHeaderValue("Billed Amount");
jTable1.getColumnModel().getColumn(3).setHeaderValue("Parts cost");
jTable1.getColumnModel().getColumn(4).setHeaderValue("Net Profit");
jTable1.getColumnModel().getColumn(5).setHeaderValue("Percentage");
}
netprofit.setText("Total Profit");
netprofit.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
netprofitActionPerformed(evt);
}
});
save.setText("Save");
save.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
saveActionPerformed(evt);
}
});
jMenu1.setText("File");
jMenuBar1.add(jMenu1);
jMenu2.setText("Edit");
jMenuBar1.add(jMenu2);
setJMenuBar(jMenuBar1);
javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jScrollPane2)
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(114, 114, 114)
.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 32, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(Date, javax.swing.GroupLayout.PREFERRED_SIZE, 77, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(layout.createSequentialGroup()
.addGap(350, 350, 350)
.addComponent(netprofit)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(netsum, javax.swing.GroupLayout.PREFERRED_SIZE, 75, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(layout.createSequentialGroup()
.addGap(221, 221, 221)
.addComponent(save)))
.addContainerGap(147, Short.MAX_VALUE))
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(20, 20, 20)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 24, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(Date, javax.swing.GroupLayout.PREFERRED_SIZE, 29, javax.swing.GroupLayout.PREFERRED_SIZE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 350, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(18, 18, 18)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(netprofit)
.addComponent(netsum, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(1, 1, 1)
.addComponent(save)
.addContainerGap(32, Short.MAX_VALUE))
);
Date.getAccessibleContext().setAccessibleName("date");
save.getAccessibleContext().setAccessibleName("save");
pack();
}// </editor-fold>
private void DateActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void netprofitActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void saveActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
Connection conn = null;
Statement stmt = null;
int count=jTable1.getRowCount();
int col=jTable1.getColumnCount();
String jobno[] =new String[count]; // name is array and index 4 means no. of row
String item[]=new String[count];
String bill[] =new String[count]; // name is array and index 4 means no. of row
String part[]=new String[count];
String profit[] =new String[count]; // name is array and index 4 means no. of row
String percent[]=new String[count];
for(int i=0;i<=count;i++)
{
for(int j=0;j<=col;j++)
{
no[i]=jTable1.getValueAt(i,j).toString(); // it get value from 0 row and 0 column
it[i]=jTable1.getValueAt(i,j).toString();
amount[i]=jTable1.getValueAt(i,j).toString();
p[i]=jTable1.getValueAt(i,j).toString();
cost[i]=jTable1.getValueAt(i,j).toString();
s[i]=jTable1.getValueAt(i,j).toString();
try{
String sql="INSERT INTO m (no,it,amount,cost,sell,p,date) VALUES('"+no[i]+"','"+it[i]+"','"+amount[i]+"','"+p[i]+"','"+cost[i]+"','"+s[i]+"','"+Date.getText()+"')";
// stmt.execute(sql);
stmt.executeQuery(sql);
//stmt.execute(sql);
/*PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, "");
ps.setString(2, "");
ps.setString(3, "");
ps.setString(4, "");
ps.setString(5, "");
ps.setString(6, "");
ps.setString(7,Date.getText());
ps.execute();*/
JOptionPane.showMessageDialog(null,"saved");
}
catch(Exception e){
JOptionPane.showMessageDialog(null,e);
}
}
}
private void jTable1InputMethodTextChanged(java.awt.event.InputMethodEvent evt) {
// TODO add your handling code here:
}
/**
* @param args the command line arguments
*/
public static void main(String args[]) {
/* Set the Nimbus look and feel */
//<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
/* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
* For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
*/
try {
for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClassName());
break;
}
}
} catch (ClassNotFoundException ex) {
java.util.logging.Logger.getLogger(bill.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
java.util.logging.Logger.getLogger(bill.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
java.util.logging.Logger.getLogger(bill.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {
java.util.logging.Logger.getLogger(bill.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
}
//</editor-fold>
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//Class.forName("jdbc:mysql://localhost:3306/ganpatidb?zeroDateTimeBehavior=convertToNull");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ganpatidb", "root", "root");
//STEP 4: Execute a query
System.out.println("Creating database...");
stmt = conn.createStatement();
}catch(SQLException se){
//Handle errors for JDBC
// se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
// e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
//se.printStackTrace();
}//end finally try
}//end try
//System.out.println("Goodbye!");
//int r=jTable1.selectedrow;
//jTable1.getValue(3,3);
/* Create and display the form */
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new bill().setVisible(true);
}
});
}
// Variables declaration - do not modify
private javax.swing.JTextField Date;
private javax.swing.JLabel jLabel1;
private javax.swing.JMenu jMenu1;
private javax.swing.JMenu jMenu2;
private javax.swing.JMenuBar jMenuBar1;
private javax.swing.JScrollPane jScrollPane2;
private javax.swing.JTable jTable1;
private javax.swing.JButton netprofit;
private javax.swing.JTextField netsum;
private javax.swing.JButton save;
// End of variables declaration
}
how i insert the multiple rows data into mysql database.... Please help....
我如何将多行数据插入到 mysql 数据库中.... 请帮忙....
回答by user3751925
From what it looks like, you are not even initializing the connectionand statementunder the saveActionPerformed method, you are setting them to NULL.
从表面上看，您甚至没有初始化connection并且statement在 saveActionPerformed 方法下，您将它们设置为 NULL。
private void saveActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
Connection conn = null;
Statement stmt = null;
In your main method however you are indeed initializing them..
但是，在您的主要方法中，您确实在初始化它们..
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//Class.forName("jdbc:mysql://localhost:3306/ganpatidb?zeroDateTimeBehavior=convertToNull");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ganpatidb", "root", "root");
//STEP 4: Execute a query
System.out.println("Creating database...");
stmt = conn.createStatement();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
//se.printStackTrace();
}//end finally try
}//end try
You need to either remove the code where you set them to NULL or add the same code directly afterwards and remember you need to put the code INSIDE your try and catch block.
您需要删除将它们设置为 NULL 的代码或之后直接添加相同的代码，并记住您需要将代码放在 try 和 catch 块中。
Something like this:
像这样的东西：
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//Class.forName("jdbc:mysql://localhost:3306/ganpatidb?zeroDateTimeBehavior=convertToNull");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ganpatidb", "root", "root");
//STEP 4: Execute a query
System.out.println("Creating database...");
stmt = conn.createStatement();
int count=jTable1.getRowCount();
int col=jTable1.getColumnCount();
String jobno[] =new String[count];
// name is array and index 4 means no. of row
String item[]=new String[count];
String bill[] =new String[count];
// name is array and index 4 means no. of row
String part[]=new String[count];
String profit[] =new String[count];
// name is array and index 4 means no. of row
String percent[]=new String[count];
for(int i=0;
i<
=count;
i++) {
for(int j=0;
j<
=col;
j++) {
no[i]=jTable1.getValueAt(i,j).toString();
// it get value from 0 row and 0 columN
it[i]=jTable1.getValueAt(i,j).toString();
amount[i]=jTable1.getValueAt(i,j).toString();
p[i]=jTable1.getValueAt(i,j).toString();
cost[i]=jTable1.getValueAt(i,j).toString();
s[i]=jTable1.getValueAt(i,j).toString();
try{
String sql="INSERT INTO m (no,it,amount,cost,sell,p,date) VALUES('"+no[i]+"','"+it[i]+"','"+amount[i]+"','"+p[i]+"','"+cost[i]+"','"+s[i]+"','"+Date.getText()+"')";
stmt.execute(sql);
JOptionPane.showMessageDialog(null,"saved");
}
catch(Exception e){
JOptionPane.showMessageDialog(null,e);
}
}
catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}
catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
finally{
//finally block used to close resources
try{
if(stmt!=null) stmt.close();
}
catch(SQLException se2){
}
// nothing we can do
try{
if(conn!=null) conn.close();
}
catch(SQLException se){
//se.printStackTrace();
}
//end finally try
}
//end try
