Saturday, August 29, 2009

Inserting into mysql table from Java code

This post gives sample implementation of an interface which will insert records in a given table.

Assumptions:
1. mysql_connector driver is in build path
2. List of records is built off line.
3. Database connection is already made.

Procedure:
As shown in the snippet of code, the method takes 5 parameters, as mentioned in the comment.
/**
* @param tableName name of the table
* @param numValues number of values to insert < = num columns
* @param dataTypes their datatypes (string, int etc)
* @param columnNames column names to add values for
* @param values actual values in same sequence
*/
public void insertIntoDb(String tableName,
int numValues,
ArrayList_of_String dataTypes,
ArrayList_of_String columnNames,
ArrayList_of_ArrayList_of_String values) {


One you have a list of column names, you can easuly concatenate them to form a string say,
String col = "(col1, col2, col3)";


Similarly, for values, using numValues, you can easily form a string say,
String val = "(?, ?, ?)";


Once this is done, make a query string like:
String query = "insert into " + tableName + " " + col + " values " + val + ";";


This will form a quert string similar to following:
"insert into myTable (name, id) values (?, ?);"


Then, write a loop which will iterate through the given ArrayList of values (actual records) and using PreparedStatement interface, fill in the values for '?' in the query string. Here you will use dataTypes provided; for example, if the corresponding data type is "string", write:
cs.setString(index_of_corresponding_?, current);


else for "integer", write:
cs.setInt(index_of_corresponding_?, Integer.parseInt(current));


where "current" is the current piece of text that you can get from ArrayList of records i.e. values.

You can verify whether your final prepared statement is correctly built, using toString() method for PreparedStatement interface. It will show something like:
"objectID1: insert into myTable (name, id) values ('abc', 1);"
"objectID2: insert into myTable (name, id) values ('xyz', 2);"


Then invoke executeQuery() method of the interface PreparedStatement which will actually insert these records in the table.

Easy!! No? :)

Friday, August 28, 2009

Select k records randomly from n records in Java

Assume a situation wherein you have an array of records, let them be anything varying from marks (integers), names (string), student information (user defined class object), or ANYTHING ARBITRARY which derives itself from Java's object. Suppose, you have such n records, each with an identifier say, 1 to n, such that you have then stored in array of any form in Java.

Now, suppose you want to chose k random records from this array, this is how you proceed.

Procedure:
Given n, generate a random permutation of {1, 2, ... n} as {1', 2', ... , n'} and pick first k numbers from the permuted array.
For example, say you want to pick 3 numbers randomly from 8, s.t. n = 8, k = 3.
1. {1, ... 8} ===> {2,3,1,7,6,4,8,5} ===> {2,3,1}
2. {1, ... 8} ===> {8,4,3,1,6,5,7,2} ===> {8,4,3}
Thus, once you have k such indexes, you just need to probe your original array with these indexes and get corresponding k random records.

The Java code snippet is as shown:






The output as expected is :
randomly selecting 3 out of them
1 a
7 g
5 e
randomly selecting 5 out of them
6 f
5 e
7 g
8 h
2 b



I needed this for:
I had to partition dataset containing some @1,000 records, for cross-validation. It is a method of training a classifier when you have small number of training records. In this, you will first decide how many folds of dataset you want to make, say 5-fold, then 1,000 records will be partitioned in the ration 4 : 1 such that every time 4/5 th of the data records will be used for training and remaining 1/5 th for testing (validating in this case). Thus, I needed to randomly pick 4/5 * 1000 of my records every time, for which I wrote above piece of code.

Note 1: Not listing the code, but adding image so that, whoever wants to try it, will actually type in the things, and thus they will never forget the trick! :)
Note 2: Reference - Weka software