Can you help me with my problem of inserting rows to a table whose columns/ fields change dynamically. I have a table named schedule, in which depending the fields increase or decrease dynamically. My requirement is to insert data into this table. The problem is that, I am unsure of how many fields are there, because, it increases or decreases dynamically.
My idea: I have used ResultSetMetaData to dynamically know the field names. With this, I can use the insert query which wil be like:
ResultSet res = stat.executeUpdate("insert into scheduletab (name,date,shift,'"+col_name[i]+"') values (....., ...., .....)");
The problem here is that how will I initialize or specify the value of "i" ?
My code is :
Note: b34 is the button to submit.
if (e.getSource() == b34)
{
String[] col_name = new String[25];
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost/nursedb", "root", "123456");
try
{
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM scheduletab");
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount();
System.out.println("Number of Column : " + col);
System.out.println("Columns Name: ");
for (int i = 1; i <= col; i++)
{
col_name[i] = md.getColumnName(i);
System.out.println(col_name[i]);
}
}
catch (SQLException s)
{
System.out.println("SQL statement is not executed!");
}
// Start Inserting data into Schedule Tab by specifying the
// column names.
Statement stat = con.createStatement();
// ResultSet res =
// stat.executeUpdate("insert into scheduletab (name,date,shift,'"values();
// End Inserting data into Schedule Tab by specifying the column
// names.
}
catch (Exception ex)
{
ex.printStackTrace();
}
}