how to create a table in database by using java

[subrat_p]

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
import java.util.*;

public class attendance extends JFrame implements ItemListener, ActionListener
{
static Connection con;
static Statement stmt;
static ResultSet rs;
static ResultSetMetaData rsMeta;
String query;


   	 String ref="",rollno="";
   	String[] heads;
int[] stChange;
    	String[][] cubeData;
int rows, cols, coo;
	
	public attendance(String tit, String qry){
		this.con  = null;
		this.stmt = null;
		this.rs = null;
		this.rsMeta=null;	

		this.query=qry;
		this.title=tit;
		this.rows=0;
		this.cols=0;
		this.coo=0;

		cp = getContentPane();

		try
		{ 
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");		this.con=DriverManager.getConnection("jdbc:mysql://localhost:3307/<databasename>","root","root");	
			this.con.setAutoCommit(true);
			this.stmt=this.con.createStatement();

			this.rs=this.stmt.executeQuery(query);		// execure query and get resultset (rs)
			rsMeta = rs.getMetaData();			// get rs metadata
			this.cols = rsMeta.getColumnCount();		// get number of colums
			this.coo =this.cols-1;
			while(rs.next()) { 				// get rows in resultset
			      ++this.rows; 
			   }

			heads = new String[cols];			// initialize col heads
			
			for (int i = 0; i < cols; i++) {  		// getting colums  heading in heads[]
     				int colindex=i+1;				// column indexes start from 1
				heads[i] = rsMeta.getColumnName(colindex);
				}



			stChange = new int[rows];			//to save value when status changed


			this.rs=this.stmt.executeQuery(query);		// execure query and get resultset (rs)
			cubeData = new String[rows][cols];			// initialize table data
	
			int j=0; int k=0;				// function to get resultset data
			while(rs.next())
			{
				if(j<=rows)
				{	int h=1;
					if(k<=cols)
					{
						for(int cc=0; cc<cols; cc++)
						{	cubeData[j][k]=rs.getString(h);
							h++; 
							k++;
						}
					}
					k=0;h=0;
					j++;
				}
			}


		
		}catch(Exception ex)
		 { System.err.println(ex.getMessage());
		   ex.printStackTrace(System.err);
		 }

	
	cp.setLayout(new GridLayout(rows+2,cols)); // 1 additional Row of Col heading, 1 for save btn

	//---------------- Adding heading in CP---------------------------------------

	for (int i = 0; i < cols; i++) {
		JLabel jl = new JLabel(" [ "+heads[i]+" ] ");
		jl.setOpaque(true);
		jl.setBackground(Color.gray);
		jl.setForeground(Color.white);
		cp.add(jl);
		
	}

	//---------------- Adding data ---------------------------------------


	for(int j=0; j<rows; j++){
		for(int k=0; k<cols; k++)
			{
			 if(k==(cols-1))
				{	 ref="-"+j+"."+k;
					String status=cubeData[j][k]+ref;
					stChange[j]=Integer.parseInt(rollno); // save previously selected status of each 									 		   // row(student) + array address
					Choice jc = new Choice();

						jc.add("Present"+ref);
						jc.add("Leave"+ref);
						jc.add("Absent"+ref);

						jc.select(status);

      						jc.addItemListener(this); 
						cp.add(jc);

				}
			 else
			  cp.add(new JLabel(cubeData[j][k]));
			  rollno=cubeData[j][k];
			}
		}
	addSaveButton();
	//----------------------------------------------------------------------------
	setTitle(title);
	setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	pack();
	setResizable(false);
 	setVisible(true);

	}

	//------------------------SAVE BUTTON-----------------------------------------

	public void addSaveButton(){
		JButton saveBtn = new JButton(" ::::  Save  :::: ");
		saveBtn.addActionListener(this);
		cp.add(saveBtn);
	}


	//------------------------Item Listener -----------------------------------------
  public void itemStateChanged(ItemEvent ie) {
	String s = (String)ie.getItem();
	
	String ro = s.substring(s.indexOf("-")+1,s.indexOf("."));
	String co = s.substring(s.indexOf(".")+1,s.length());

	String stats = s.substring(0,s.indexOf("-"));

	int roo = Integer.parseInt(ro);
	coo = Integer.parseInt(co);
	
	cubeData[roo][coo]=stats;
  }

	//---------------------Action Listener-----------------------------------------
  public void actionPerformed(ActionEvent ae) {
       
	try{
		for(int j=0; j<rows; j++)
		{
			String updt = "update students set Status= '"+cubeData[j][coo]+"' where Rollno="+stChange[j];
			this.con=DriverManager.getConnection(this.dbURL);
			this.stmt.executeUpdate(updt);
		}
			this.con.commit();
			System.out.println("Records updated..!");
			con.close();

	}catch(Exception ex)
	 { System.err.println(ex.getMessage());
	   ex.printStackTrace(System.err);
	 }
  }





public static void main(String args[])
	{
	 new attendance("..:: Attendance Form ::.. ",  "SELECT *  FROM students order by Rollno");
	}

}

but i want to create another table in the name of current date when I click on save button where all data from this table store in the new table. I have a problem that I can create another new table but can not insert all data from this table to the new table. So please help me on this

[masijade]

Creating a new table is completely contradictory to the purpose of a relational db.

As far as doing it, if you insist and we all know you will, and will come up with all sorts of reasons as to why it has to be that way, you use a Statement to execute a create table, then use a Statement (or PreparedStatement if there is "configurable" parameters) to set off an insert ... select statement. If you do not know how to do a "create table" or an "insert ... select" then find an SQL (not any kind of Java) forum.

[subrat_p]

I know about create new table in database through java. But I can't insert the data into this created table. Because there is some parameter passed into the table
//---------------------Action Listener-----------------------------------------

public void actionPerformed(ActionEvent ae) {

try
{
String create="CREATE TABLE <table name> (`Name` VARCHAR(50), `Regd_No` INT, `Status` VARCHAR(20) NOT NULL)";
this.con=DriverManager.getConnection(this.dbURL);
this.stmt.executeUpdate(updt);
}
{ System.err.println(ex.getMessage());
ex.printStackTrace(System.err);
}

through this code when i clicked the save button it create a new table but I don't know how to insert data into this new table. Because in the below code I update a existing column through passing some objects i.e.cubeData[j][coo] & stChange[j]. But I want to create a new table in the name of current date and insert data through these objects cubeData[j][coo] & stChange[j]

try{
for(int j=0; j<rows; j++)
{
String updt = "update students set Status= '"+[B]cubeData[j][coo][/B]+"' where Rollno="+[B]stChange[j][/B];
this.con=DriverManager.getConnection(this.dbURL);
this.stmt.executeUpdate(updt);
}
this.con.commit();
System.out.println("Records updated..!");
con.close();

}catch(Exception ex)
{ System.err.println(ex.getMessage());
ex.printStackTrace(System.err);
}
}

[toplinecar]

You should write DSN name in the Driver connection methods so you will get some idea about the errors.

[masijade]

So then use an insert and not an update. Jeez. Learn SQL. And do not cobble together statements like that unless want an SQL Injection attack or spurious invalid SQL errors.

[subrat_p]

You should write DSN name in the Driver connection methods so you will get some idea about the errors.

this.con=DriverManager.getConnection("jdbc:mysql://localhost:3307/<databasename>","root","root");

I used MySQL so its not required any DSN name.

[subrat_p]

So then use an insert and not an update. Jeez. Learn SQL. And do not cobble together statements like that unless want an SQL Injection attack or spurious invalid SQL errors.

try{
for(int j=0; j<rows; j++)
{
String ins="INSERT INTO <tablename>(`col1`, `col2`, `con3`) VALUES ('xyz', 123456, 'abc');"

but see in the update statement there is some object passes cubeData[j][coo] & stChange[j] to the query which are derived from the existing frame where only status column is updated where Rollno is find. But how can i insert these into new table?

String updt = "update students set Status= '"+cubeData[j][coo]+"' where Rollno="+stChange[j];
this.con=DriverManager.getConnection(this.dbURL);
this.stmt.executeUpdate(updt);
}
this.con.commit();
System.out.println("Records updated..!");
con.close();
}catch(Exception ex)
{ System.err.println(ex.getMessage());
ex.printStackTrace(System.err);
}
}

[masijade]

By inserting them. I don't know where the problem is you insert both cubeData[j][coo] & stChange[j] rather than updating with one and matching with the other. Where is the problem?

[subrat_p]

i can not solve this please help me I have 3 fields to fill by insert for Name, Regd_no & status but I have only 2 objects cubeData[j][coo] & stChange[j] for status and regd_no but how can i get all names from the frame and insert into the new table?

[masijade]

Well, what is the definition of regd_no? Figure that out and implement a solution for its value (I wouldn't doubt that its nothing but a one-up index number). As far as "how to get it from the frame", how are you "getting the other data"?

My God. You don't seem to even want to try. Well, I can tell you right now, this is probably my last post in this thread until you actually do something.

[subrat_p]

public void actionPerformed(ActionEvent ae) {

	try{
		String create="CREATE TABLE civ_fourth (`Name` VARCHAR(50), `Regd_No` INT, `Status` VARCHAR(20) NOT NULL)";
                con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
                stmt.executeUpdate(create);
                con.close();
            }
            catch(Exception ex)
             { System.err.println(ex.getMessage());
             ex.printStackTrace(System.err);
             }

             try{
                for(int j=0; j<rows; j++)
		{
			String ins="INSERT INTO civ_fourth (`Regd_No`,`Status`) VALUES('"+stChange[j]+","+cubeData[j][coo]+")";

			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");


                        PreparedStatement ps=con.prepareStatement(ins);
		}
			//this.con.commit();
                        con.setAutoCommit(true);
			System.out.println("Records updated..!");
			con.close();

	}catch(Exception ex)
	 { System.err.println(ex.getMessage());
	   ex.printStackTrace(System.err);
	 }
  }

But its create the table but inserting data is not possible.

[subrat_p]

public void actionPerformed(ActionEvent ae) {

	try{
		String create="CREATE TABLE civ_fourth (`Name` VARCHAR(50), `Regd_No` INT, `Status` VARCHAR(20) NOT NULL)";
                con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
                stmt.executeUpdate(create);
                con.close();
            }
            catch(Exception ex)
             { System.err.println(ex.getMessage());
             ex.printStackTrace(System.err);
             }

             try{
                for(int j=0; j<rows; j++)
		{
			String ins="INSERT INTO civ_fourth (`Regd_No`,`Status`) VALUES('"+stChange[j]+","+cubeData[j][coo]+")";

			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");


                        PreparedStatement ps=con.prepareStatement(ins);
		}
			//this.con.commit();
                        con.setAutoCommit(true);
			System.out.println("Records updated..!");
			con.close();

	}catch(Exception ex)
	 { System.err.println(ex.getMessage());
	   ex.printStackTrace(System.err);
	 }
  }

But its create the table but inserting data is not possible.

[masijade]

Of course not with that query. Where did "Rollno" go? What about the advice about PreparedStatement? Posting the complete stacktrace would help as well.

[subrat_p]

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;

public class civil_fst extends JFrame implements ItemListener, ActionListener{

    static Connection con;
    static Statement stmt;
    static ResultSet rs;
    static ResultSetMetaData rsMeta;

    String query;
    String ref="",rollno="";
    String[] heads;
    int[] stChange;
    String[][] cubeData;
    int rows, cols, coo;

    /** Creates new form civil_fst */
    public civil_fst() {
        initComponents();

        con  = null;
        stmt = null;
        rs = null;
        rsMeta=null;

        query="SELECT *  FROM  civilfattnd order by Regd_No";
        rows=0;
        cols=0;
        coo=0;

        try
            {
                Class.forName("com.mysql.jdbc.Driver");
                con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
                con.setAutoCommit(true);
                stmt=con.createStatement();

                rs=stmt.executeQuery(query); //execute the query and get resultset(rs)
                rsMeta=rs.getMetaData(); //get rs metadata
                cols=rsMeta.getColumnCount(); // get number of columns
                coo=cols-1;

                while(rs.next()) { 				// get rows in resultset
			      ++rows;
			   }
                heads = new String[cols];			// initialize col heads

                for (int i = 0; i < cols; i++) {  		// getting colums  heading in heads[]
                    int colindex=i+1;				// column indexes start from 1
                    heads[i] = rsMeta.getColumnName(colindex);
                }
                stChange = new int[rows];			//to save value when status changed


                rs=stmt.executeQuery(query);		// execure query and get resultset (rs)
                cubeData = new String[rows][cols];			// initialize table data

                int j=0; int k=0;				// function to get resultset data
                while(rs.next())
                {
                    if(j<=rows)
                    {
                        int h=1;
                        if(k<=cols)
                        {
                            for(int cc=0;cc<cols; cc++)
                            {
                                cubeData[j][k]=rs.getString(h);
                                h++;
                                k++;
                            }
                        }
                        k=0;h=0;
                        j++;
                    }
                }
        }
        catch(Exception ex)
		 { System.err.println(ex.getMessage());
		   ex.printStackTrace(System.err);
		 }

        cp.setLayout(new GridLayout(rows+2,cols)); // 1 additional Row of Col heading, 1 for save btn


        //---------------- Adding heading in CP---------------------------------------

	for (int i = 0; i < cols; i++) {
		JLabel jl = new JLabel(" [ "+heads[i]+" ] ");
		jl.setOpaque(true);
		jl.setBackground(Color.gray);
		jl.setForeground(Color.white);
		cp.add(jl);

	}

	//---------------- Adding data ---------------------------------------


	for(int j=0; j<rows; j++){
		for(int k=0; k<cols; k++)
			{
			 if(k==(cols-1))
				{	 ref="-"+j+"."+k;
					String status=cubeData[j][k]+ref;
					stChange[j]=Integer.parseInt(rollno); // save previously selected status of each 									 		   // row(student) + array address
					Choice jc = new Choice();

						jc.add("Present"+ref);
						jc.add("Leave"+ref);
						jc.add("Absent"+ref);
                                                

						jc.select(status);

      						jc.addItemListener(this);
						cp.add(jc);

				}
			 else
			  cp.add(new JLabel(cubeData[j][k]));
			  rollno=cubeData[j][k];
			}
		}
	addSaveButton();
    }
//------------------------SAVE BUTTON-----------------------------------------

	public void addSaveButton(){
		JButton saveBtn = new JButton(" ::::  Save  :::: ");
		saveBtn.addActionListener(this);
		cp.add(saveBtn);
	}




	//------------------------Item Listener -----------------------------------------
  public void itemStateChanged(ItemEvent ie) {
	String s = (String)ie.getItem();

	String ro = s.substring(s.indexOf("-")+1,s.indexOf("."));
	String co = s.substring(s.indexOf(".")+1,s.length());

	String stats = s.substring(0,s.indexOf("-"));

	int roo = Integer.parseInt(ro);
	coo = Integer.parseInt(co);

	cubeData[roo][coo]=stats;
  }

	//------------------------Action Listener-----------------------------------------
  public void actionPerformed(ActionEvent ae) {

	try{
		String create="CREATE TABLE civ_fourth (`Name` VARCHAR(50), `Regd_No` INT, `Status` VARCHAR(20) NOT NULL)";
                con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
                stmt.executeUpdate(create);
                //con.close();
            }
            catch(Exception ex)
             { System.err.println(ex.getMessage());
             ex.printStackTrace(System.err);
             }

             try{
                for(int j=0; j<rows; j++)
		{
			//String updt = "update civilfattnd set Status= '"+cubeData[j][coo]+"' where Regd_No="+stChange[j];
                        String ins="INSERT INTO civ_fourth (`Regd_No`, `Status`) VALUES('"+stChange[j]+","+cubeData[j][coo]+")";

			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");

			//stmt.executeUpdate(updt);
                        PreparedStatement ps=con.prepareStatement(ins);
		}
			//this.con.commit();
                        con.setAutoCommit(true);
			System.out.println("Records updated..!");
			con.close();

	}catch(Exception ex)
	 { System.err.println(ex.getMessage());
	   ex.printStackTrace(System.err);
	 }
  }
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    private void initComponents() {

        cp = new javax.swing.JPanel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("...:::Civil First Sem:::...");
        setBounds(new java.awt.Rectangle(250, 250, 0, 0));
        setLocationByPlatform(true);

        cp.setBackground(new java.awt.Color(154, 95, 125));

        javax.swing.GroupLayout cpLayout = new javax.swing.GroupLayout(cp);
        cp.setLayout(cpLayout);
        cpLayout.setHorizontalGroup(
            cpLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 539, Short.MAX_VALUE)
        );
        cpLayout.setVerticalGroup(
            cpLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 308, Short.MAX_VALUE)
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(cp, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(cp, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        );

        pack();
    }// </editor-fold>                        

    /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new civil_fst().setVisible(true);
            }
        });
    }

where civilfattnd is the name of table and student is a database in mysql from where all data from database table derived to the running frame. civ_fourth is the new table which I want to create. "INSERT INTO civ_fourth (`Regd_No`, `Status`) VALUES('"+stChange[j]+","+cubeData[j][coo]+")";is the query for mysql.Its compile and running and new table created and when i click the save button all data updated but when ever I see the new table its empty.

[subrat_p]

please help me ...its urgent...i'm in between middle of the project