CODEWITHSUNDEEP
javamysqlsqljdbcresultset
rullzing
rullzing

Reputation: 642

issue with adding rows in a table

So I'm trying to add rows in a table based on a specific table with its columns but I don't know why I'm not getting what I want to get

Here is what I did:

Scanner kb = new Scanner(System.in);
sql = "SELECT * FROM " + TableName;
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount(); 
for(int i = 1; i<=columnsNumber; i++)
{
System.out.print(rsmd.getColumnName(i) + ": ");
row = kb.next();
sql = "INSERT INTO "+TableName+ "("+rsmd.getColumnName(i)+") VALUES ('"+row+"')";
stmt.executeUpdate(sql);
}
System.out.println();

However, when I list the table (after adding values for the first time) I get:

Table Name
=========================================
Col 1               Col 2               
                    name               
1234                null

The SQL result for what I have is:

INSERT INTO Tablename(Col 1) VALUES ('1234')
INSERT INTO Tablename(Col 2) VALUES ('name')

I don't know why it's doing this, what I'm trying to accomplish is: 

Table Name
=========================================
Col 1               Col 2               
1234                name

BTW: Col 1 is the primary key that's the first time,

when I try to add any other values, I get this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

To sum up what I want to accomplish: I'm trying to add an entry for each column in the same row so I want to start from column 1 and add row 1, then go to column 2 and add a value to row 1 as well.

Upvotes: 0

Views: 235

Answers (2)

Brandon
Brandon

Reputation: 10028

INSERT is used to create a new row in a table. Let's step through your code for each iteration.

Table schema

TestTable
ColA  ||  ColB || ColC || ColD
------------------------------

Code:

for(int i = 1; i<=columnsNumber; i++)
{
    System.out.print(rsmd.getColumnName(i) + ": ");
    row = kb.next();
    sql = "INSERT INTO "+TableName+ "("+rsmd.getColumnName(i)+") VALUES ('"+row+"')";
    stmt.executeUpdate(sql);
}

First iteration, you run this SQL:

INSERT INTO TestTable (ColA) VALUES ("first value");

Now your table looks like

TestTable
ColA        || ColB || ColC || ColD
-----------------------------------
first value || null || null | null

Run the code again

INSERT INTO TestTable (ColB) VALUES ("second value");

TestTable
ColA        || ColB         || ColC || ColD
-------------------------------------------
first value || null         || null || null
null        || second value || null || null

One more iteration.

INSERT INTO TestTable (ColC) VALUES ("third value");

TestTable
ColA        || ColB         || ColC        || ColD
--------------------------------------------------
first value || null         || null        || null
null        || second value || null        || null
null        || null         || third value || null

Now, if you have a primary key or unique constraint on ColA, what would happen when trying to insert that third row? That's where your constraint violation is coming from.

I can't offer a solution because I don't understand what you are trying to do. This entire model seems upside down. You are treating rows as columns and columns as rows. Columns are the fixed structure that every row must adhere to. Rows are the dynamic set of entries that you insert, update, or delete.

If you are trying to update the values of each column of a given row, then you need a SQL UPDATE statement rather than INSERT, but you also need to identify the row. You're not identifying any specific rows in your code, so it's not clear which row you should update.

This sounds like an XY problem. You have problem X, think Y is the solution, and are asking for help in doing Y. But if we understood X, we could offer a different solution.

Upvotes: 1

JRD
JRD

Reputation: 1987

You are inserting 2 rows, and only setting 1 column at a time. Looks like you may want an insert statement that looks more like:

insert into Tablename(column 1, column 2, etc...) values (column 1 value, column 2 value, ...);

If you still want to generate the SQL statement dynamically, then you'll want to build the statement, then only execute it once (outside of the loop).

Also, it looks like you have a unique index that is preventing the empty string from being inserted.

Upvotes: 1

Related Questions