Showing posts with label course management. Show all posts
Showing posts with label course management. Show all posts

Saturday, July 24, 2010

JExcel API

In my last assignment, i got requirment to write data into excel file.I got the solution JExcel API.I got solution in the form of JExcel.Today, i will try to demonstrate JExcel API.
JExcelApi allows developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically.It also contains a mechanism which allows java applications to read in a spreadsheet, modify some cells and write out the new spreadsheet.

Download JExcelApi JAR files from

http://jexcelapi.sourceforge.net/

Benifit of this API is:
Any operating system which can run a Java virtual machine (i.e., not just Windows) can both process and deliver Excel

spreadsheets. Because it is Java, the API can be invoked from within a servlet, thus giving access to Excel spreadsheets over

internet and intranet web applications.

Demo Application:

imported packages are following:
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public ByteArrayOutputStream generateExcelReport() throws IOException, WriteException {
/* Stream containing excel data */
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

/* Create Excel WorkBook and Sheet */
WritableWorkbook workBook = Workbook.createWorkbook(outputStream);

or writing data in to generated xls
//Workbook workbook = Workbook.getWorkbook(new File(“Deepak.xls”));
WritableSheet sheet = workBook.createSheet("Project List", 0);

/* Generates Headers Cells */
WritableFont headerFont = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD);
WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
headerCellFormat.setBackground(Colour.PALE_BLUE);
sheet.addCell(new Label(1, 1, "Project Id", headerCellFormat));
sheet.addCell(new Label(2, 1, "Project Name", headerCellFormat));

/* Generates Data Cells */
WritableFont dataFont = new WritableFont(WritableFont.TAHOMA, 12);
WritableCellFormat dataCellFormat = new WritableCellFormat(dataFont);

int currentRow = 2;
for (User user : getUsers()) {
sheet.addCell(new Label(1, currentRow, user.getLastName(),dataCellFormat));
sheet.addCell(new Label(2, currentRow, user.getFirstName(),dataCellFormat));
currentRow++;
}

/* Write & Close Excel WorkBook */
workBook.write();
workBook.close();

return outputStream;
}

public List getUsers() {

try{
Class.forName("com.mysql.jdbc.Driver");
Connection

con=DriverManager.getConnection("jdbc:mysql://localhost/pmsdatabase2?user=root&password=mysql");
PreparedStatement ps=con.prepareStatement("select project_code,project_name from project");
ResultSet rs=ps.executeQuery();
while(rs.next()){
users.add(new User(rs.getString(1),rs.getString(2)));
}

}catch(Exception e){
System.out.println("Coming here!!"+e);
}
return users;
}

COde for User.java:

class User{

private String firstName;
private String lastName;
//getter and setter of firstName and lastName
public User(){}

public User(String lastName,String firstName){
this.firstName=firstName;
this.lastName=lastName;

}

}

Call this method by using any servlet.
A complete document is available on sourceforge or other sites.

Thanks!
Deepak

Saturday, June 19, 2010

A basic application for iBatis use

Last month i got chance to learn some new techonologies like GWT (Google Web ToolKit) and iBatis. Firstly i would like to demonstrate application on iBatis. In upcoming posts, i would cover GWT.

I found iBatis very useful because With Ibatis I find myself getting the Job done faster, I create any POJO any set of tables, and I link them, much flixability in that field, but little extra work, building Queries and managing Maps (Little time required here). I never worked or got chance to learn hibernate.So bit difficult for me to tell that which is much better.But anyway here i developed a small application which only tells you that how you can use iBatis in your projects.

Download iBatis.jar
http://ibatis.apache.org/javadownloads.html

iBatis:

Step1: create SQLMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
<settings useStatementNamespaces="true"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL"
value="jdbc:mysql://localhost:3306/mhrddemo"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value="mysql"/>
</dataSource>
</transactionManager>
<sqlMap resource="DataBean.xml"/>
</sqlMapConfig>

you can create property file and then use that values in this config file.

2)create another xml where you write query:
DataBean.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Contact">
<!--- Showing all data of table -->



<!--- Showing all data of table -->
<select id="getAll" resultClass="ibdeep.DataBean">
select university_id,entity_type,entity_description from entity_type
</select>

<resultMap id="result" class="ibdeep.DataBean">
<result property="university_id" column="university_id"/>
<result property="entity_type" column="entity_type"/>
<result property="entity_description" column="entity_description"/>

</resultMap>
<select id="getById" resultMap="result">
select * from entity_type where entity_type=#entity_type#
</select>


</sqlMap>

3) create a java file which has getter and setter of table fields or properties.

package ibdeep;

class DataBean
{

private String university_id;

private String entity_type;

private String entity_description;

public DataBean(){

}

public DataBean(String university_id,String entity_type,String entity_description){

this.university_id=university_id;
this.entity_type=entity_type;
this.entity_description=entity_description;

}

public String getUniversity_id(){

return university_id;

}

public void setUniversity_id(String university_id){

this.university_id=university_id;

}

public String getEntity_type(){

return entity_type;

}

public void setEntity_type(String entity_type){

this.entity_type=entity_type;

}

public String getEntity_description(){

return entity_description;

}

public void setEntity_description(String entity_description){

this.entity_description=entity_description;

}

}

Step 4): Create another java file
package ibdeep;

import com.ibatis.common.resources.Resources;

import com.ibatis.db.sqlmap.SqlMap;

import com.ibatis.sqlmap.client.*;

import java.io.Reader;

public class SqlMapManager {
private static SqlMap sqlMap = null;
static SqlMapManager smm;

public static SqlMapClient getSqlMapClient() {
try {
Reader reader = Resources
.getResourceAsReader("ibdeep/SqlMapConfig.xml");
SqlMapClient sqlMapper = SqlMapClientBuilder
.buildSqlMapClient(reader);
reader.close();

return sqlMapper;
} catch (Exception e) {
System.out.println("Map Exception");
e.printStackTrace();
throw new RuntimeException(e.getMessage(), e);
}
}


public void setSqlMap(SqlMap sqlMap) {
SqlMapManager.sqlMap = sqlMap;
}


public static SqlMap getSqlMap() {
return sqlMap;
}
}

Step 5) create properties file.you can use it.You can pass these values directly

url=jdbc:mysql://localhost:3306/mhrddemo
login=root
password=mysql

Step 6) Finally create java file which uses

package ibdeep;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.*;
import java.sql.SQLException;
import java.util.*;

class IBatisExample
{
public static void main(String[] args)
throws IOException,SQLException{
Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
SqlMapClient sqlMap =
SqlMapClientBuilder.buildSqlMapClient(reader);

//Output all contacts

System.out.println("All Contacts");
List<DataBean> contacts = (List<DataBean>)
sqlMap.queryForList("Contact.getAll",null);
DataBean contact = null;
for (DataBean c : contacts) {
System.out.print(" " + c.getUniversity_id());
System.out.print(" " + c.getEntity_type());
System.out.print(" " + c.getEntity_description());
contact = c;
System.out.println("");
}
}
}

I hope it is useful.