Member Avatar for chixm8_49

Good day!

I would like to ask on how to transfer the contents of JTable to mySQL database. I have seen a lot of codes of putting contents to JTable from database but I need to know the other way around. ^^" I do not have any idea on how to start it.

Thanks for the help.

  • 2 Contributors
  • 1 Reply
  • 500 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by sciwizeh

Dani AI

Generated

— a practical, safe pattern is to iterate the JTable's TableModel and write rows with a parameterized PreparedStatement instead of concatenating SQL strings. 's use of the TableModel to drive the insert loop is a good start, but direct string concatenation invites quoting bugs, type mismatches and SQL injection; PreparedStatement binds values and is precompiled by the DB for repeated execution. (docs.oracle.com)

Minimal, production-ready pattern (build placeholders once, set parameters per row, add to a batch, execute periodically, commit at the end):

// build a parameterized INSERT once
String sql = "INSERT INTO my_table (col1,col2,col3) VALUES (?,?,?)";
Connection conn = dataSource.getConnection();
try {
  conn.setAutoCommit(false);
  try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (int r = 0; r < model.getRowCount(); r++) {
      ps.setObject(1, model.getValueAt(r, 0));
      ps.setObject(2, model.getValueAt(r, 1));
      ps.setObject(3, model.getValueAt(r, 2));
      ps.addBatch();
      if ((r % 500) == 0) ps.executeBatch(); // flush in chunks
    }
    ps.executeBatch();
  }
  conn.commit();
} catch (SQLException ex) {
  conn.rollback();
  throw ex;
} finally {
  conn.close();
}

Use setObject (or type-specific setters) and setNull for null cells so JDBC maps Java types correctly. For details on parameter binding and setObject, see the JDBC API docs. (docs.oracle.com)

If some JTable rows might already exist in MySQL, convert the INSERT to an upsert using INSERT ... ON DUPLICATE KEY UPDATE (MySQL will update rows that conflict on a UNIQUE/PRIMARY KEY). That avoids a separate SELECT/UPDATE step. (dev.mysql.com)

Batching substantially reduces round trips; disable auto-commit, pick a sensible batch size (e.g., 200–1000), and always wrap batch work in a transaction so rollback is possible on error. MySQL Connector/J offers rewriteBatchedStatements to speed multi-row inserts but it can change behavior (generated keys, result counts), so test before enabling. (docs.oracle.com)

Member Avatar for sciwizeh

Do you have any code? Are you already connected to the database? If so you can use the table model to insert each row into your database:

TableModel m = table.getModel();
String SQL = "insert into YOUR_TABLE (";
for(int i =0; i<m.getColumnCount()-1; i++){
    SQL+="'"+m.getColumnName(i)+"',";
}
SQL+="'"+m.getColumnName(m.getColumnCount()-1)+"') values ";
for(int row = 0; row<m.getRowCount(); row++){
    SQL+="(";
    for(int col = 0; col<m.getColumnCount()-1; col++){
        SQL+=m.getValueAt(row,col);
    }
    SQL+=m.getValueAt(row,m.getColumnCount()-1)+")";
    if(row<m.getRowCount()-1){
        SQL+=",";
    }
}
//SUBMIT QUERY using sql

NOTE: I did not attempt to compile the above code and it may not work properly or produce a syntatically correct mySQL query.

the above code assumes that the JTable column names are the same as your DB column names and that all the rows in the JTable will be new to the DB, otherwise an update statement would need to be substituted for the insert. also note that there should also probably be more in the way of error checking.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.