Could you attach a small but complete code example to illustrate your point? Single java class with main() is ok, but JUnit format is the best, since it will also illustrate what you're actually expecting with assertXXX.
If you're talking about multivalued inserts, MySQL cannot return all autogenerated keys for all values. Because it is not sent with the protocol.
I am not sure I will have time to create an example, but I'll try. The scenario can be very easily reproduced when using the MariaDB Java client to insert multiple rows into a table that has an AutoIncrement index column.
When you say "multivalued" inserts - What do you mean? In my use-case, the table to which I am inserting only has one column which is defined as autoIncrement and it is also the primary key for the table.
Maybe those are multi-row inserts, I forgot the correct name. single insert-multiple rows
Like this
Insert into t
values (1),(2),(3)
Because you're talking about insert that generated multiple autoincrement values, so I was wondering what kind of insert that is. Except the above, there is also insert-select
insert into t select * from t2
however this , just like the previous example, won't return all autogenerated ids.
So, I'm really intrigued by the test case you'll hopefully attach.
Thanks!
We recently moved from working the Connector/J to MariaDB. I know that when we were working with Connector/J, we were able to retrieve multiple generated IDs.
Maybe. Anyway, to understand and to check what worked there and does not work here, I would need a code example, because I do not understand the problem.
Thanks!
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbcp.BasicDataSource;
public class GeneratedKeys {
public static void main(String[] args)
{ String query = "INSERT INTO DEMO (value) VALUES (?),(?),(?)"; List<String> queryParms = new ArrayList<String>(); queryParms.add("1"); queryParms.add("2"); queryParms.add("3"); GeneratedKeys k = new GeneratedKeys(); //Connect and execute with the connector: // List<Integer> keys = k.executeQuery("org.mariadb.jdbc.Driver", query, queryParms); List<Integer> keys = k.executeQuery("org.gjt.mm.mysql.Driver", query, queryParms); System.out.println(""); System.out.println("****** Results ********"); System.out.println("Generated Keys: "+keys.toString()); }public List<Integer> executeQuery(String driverName,String query, List<String> queryParms){
String serverName = "127.0.0.1";
String dbName = "DEMO";
String mysqlConnectionParams = "?useJvmCharsetConverters=true"
+ "&autoReconnect=true"
+ "&noDatetimeStringSync=true";
BasicDataSource DataSource = new BasicDataSource();
DataSource.setDriverClassName(driverName);
DataSource.setUsername("waveDB");
DataSource.setPassword("1179sia");
DataSource.setUrl("jdbc:mysql://" + serverName + "/" + dbName
+ mysqlConnectionParams);
try{
//Connect to the DB
System.out.println("Connecting to DB using connector: "driverName"...");
Connection conn = DataSource.getConnection();
//Execute the query
System.out.println("Executing Query...");
PreparedStatement stmt = conn.prepareStatement(query,
Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < queryParms.size(); ++i) {
if (queryParms.get.getClass().equals(String.class))
}
stmt.executeUpdate();
ResultSet result = stmt.getGeneratedKeys();
List<Integer> generatedKeys = new ArrayList<Integer>();
while (result.next())
conn.close();
stmt.close();
result.close();
return generatedKeys;
}
catch(Exception e)
finally{
try
catch (Exception ee)
{ ee.printStackTrace(); }}
return new ArrayList<Integer>();
}
}
Just added a source code example.
In the example, I'm using the BasicDataSource class (from apache commons). The table named "DEMO" which I'm inserting in is defined using the following SQL:
DROP TABLE IF EXISTS `DEMO`.`DEMO`;
CREATE TABLE `DEMO`.`DEMO` (
`index` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(45) NOT NULL,
PRIMARY KEY (`index`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;
I tried to run this code having the mariaDB JAR in the classpath and using driver name: org.mariadb.jdbc.Driver - The resulting list contains one element (instead of 3)
I tried to run this code with the ConnectorJ JAR in the classpath and using driver name: org.gjt.mm.mysql.Driver - The resulting list contains 3 elements, with the 3 indexes created.
By the way, If I switch the MariaDB JAR in the classpath to the 1.1.0 version, the resulting list will have 3 elements, but the values are wrong.
Indeed, ConnectorJ outsmarted the protocol. so we need to keep up