package com.ibatis.instan;
/**
* Created by IntelliJ IDEA.
* User: lihongya
* Date: 2010-1-25
* Time: 13:39:50
* Desc: ibatis鐨勬搷浣滄暟鎹簱鐨� SqlMapClient
*/
import java.io.Reader;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class MyAppSqlConfig {
private static final SqlMapClient sqlMap;
static {
try {
String resource = "sqlMapConf.xml";
Reader reader = com.ibatis.common.resources.Resources.getResourceAsReader(resource);
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(
"Error initializing MyAppSqlConfig class. Cause: " + e);
}
}
public static SqlMapClient getSqlMapInstance() {
return sqlMap;
}
}
-----------------使用IBATIS连接数据库.
package com.sms.handl;
import org.apache.log4j.Logger;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Properties;
/**
* Created by IntelliJ IDEA.
* User: lihongya
* Date: 2010-1-25
* Time: 15:39:24
* Desc:
*/
public class Param {
private String separator = System.getProperty("file.separator");
private String path = "."+ separator + "sys.properties";
private Properties propertie;
private FileInputStream inputFile;
private FileOutputStream outputFile;
public static String tjny;
public static String export;
public static String model;
static Logger logger = Logger.getLogger(Param.class);
/**
* 类的构造函数
*/
public Param() {
propertie = new Properties();
try {
inputFile = new FileInputStream(path);
propertie.load(inputFile);
inputFile.close();
} catch (FileNotFoundException ex) {
logger.error("读取属性文件--->失败!- 原因:文件路径错误或者文件不存在");
logger.error(ex.getMessage());
} catch (IOException ex) {
logger.error("装载文件--->失败!");
logger.error(ex.getMessage());
}
tjny = propertie.getProperty("tjny", "201010");
export = propertie.getProperty("export", "export.xls");
model = propertie.getProperty("model", "model.xls");
}
public String getTjny() {
return tjny;
}
public static String getExport() {
return export;
}
public static String getModel() {
return model;
}
}
-------读取PROPERTIES配置文件
package com.sms.jexcel;
import com.ibatis.instan.MyAppSqlConfig;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.sms.handl.Param;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class WriteAwardToExcel {
Param param =new Param();
private static String separator = System.getProperty("file.separator");
public String sourcefile = "." + separator + "exportfile" + separator +param.getExport();// + "export.xls" 要锟斤拷取锟侥硷拷锟斤拷路锟斤拷
public String modelfile = "." + separator + "exportfile" + separator +param.getModel();// "model.xls" 要锟斤拷取锟侥硷拷锟斤拷路锟斤拷
private static final SqlMapClient sqlMap = MyAppSqlConfig.getSqlMapInstance();
private static final int start_num =3;
public List getDateBySql(String sql_name,String tjny) {
try {
Map map=new HashMap();
map.put("tjny", tjny);
return sqlMap.queryForList(sql_name, map);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public void writeToExcel( String tjny, List list) throws Exception {
String outputFilePath= sourcefile;
File fl = new File(outputFilePath);
if (!fl.exists()) {
fl.createNewFile();
}
OutputStream os = new FileOutputStream(outputFilePath);
File f2 =new File(modelfile);
System.out.println("sourcefile="+sourcefile);
System.out.println("modelfile="+modelfile);
Workbook w2 = Workbook.getWorkbook(f2);
System.out.println("length="+w2.getSheetNames().length);
WritableWorkbook wwb = Workbook.createWorkbook(os,w2);
for(String shname : w2.getSheetNames() ){
System.out.println("shname:"+shname+",,tjny:"+tjny);
WritableSheet sheet = wwb.getSheet(shname);
List list_date = getDateBySql(shname, tjny);
System.out.println("list_date="+list_date);
for (int i=0 ;i<list_date.size();i++){
Map map= (Map)list_date.get(i);
for(Object key :map.keySet()){
String str_whe =key.toString();
int in_whe =Integer.valueOf(str_whe.substring(1));
String str_date= map.get(key.toString()).toString();
sheet.addCell(new Label(in_whe,i+start_num,str_date));
}
}
}
wwb.write();
wwb.close();
os.close();
}
public static void main(String[] args) {
WriteAwardToExcel writeAwardToExcel = new WriteAwardToExcel();
String tjny =(new Param()).getTjny();
try {
List list = null;
writeAwardToExcel.writeToExcel( tjny, list);
} catch (Exception e) {
e.printStackTrace();
}
}
}
----------------------------主程序。
<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<!--
<properties resource="sqlMapConf.properties"/> -->
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
errorTracingEnabled="true"
maxRequests="32"
maxSessions="10"
maxTransactions="5"
useStatementNamespaces="false"
/>
<!--
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
<property name="Pool.MaximumActiveConnections" value="10"/>
<property name="Pool.MaximumIdleConnections" value="5"/>
<property name="Pool.MaximumWait" value="60000"/>
</dataSource>
</transactionManager>
-->
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@//localhost:1521/oracle"/>
<property name="JDBC.Username" value="USER_A"/>
<property name="JDBC.Password" value="oracle"/>
<property name="JDBC.encoding" value="GBK"/>
<property name="Pool.MaximumActiveConnections" value="10"/>
<property name="Pool.MaximumIdleConnections" value="5"/>
<property name="Pool.MaximumWait" value="60000"/>
</dataSource>
</transactionManager>
<sqlMap resource="sms.xml"/>
</sqlMapConfig>
----------------------IBATIS配置文件
<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="Sms">
<select id="selectAllUserResult" resultClass="java.util.HashMap">
select name n1,#tjny# n20 from test
<!-- select 'aa' name,10 age from dual -->
</select>
</sqlMap>
----------sql文件
---根据EXCEL工作表的名称,在IBATIA配置文件中查找相应的sql语句。EXCEL工作表的名称与IBAIS的id相关联。有一个excel模板,程序会复制一份模板到一份新的excel中。程序会根据EXCEL的工作表名,查找相应的sql语句,并把查询结果插入到EXCEL工作表中。