package com.sogou.qadev.service.cynthia.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import com.sogou.qadev.service.cynthia.bean.Template;
import com.sogou.qadev.service.cynthia.bean.UUID;
import com.sogou.qadev.service.cynthia.bean.impl.TemplateImpl;
import com.sogou.qadev.service.cynthia.service.DbPoolConnection;
import com.sohu.rd.td.util.xml.XMLUtil;
/**
* @description:template db processor
* @author:liming
* @mail:liming@sogou-inc.com
* @date:2014-5-6 下午5:56:08
* @version:v1.0
*/
public class TemplateAccessSessionMySQL {
private static Logger logger = Logger.getLogger(TemplateAccessSessionMySQL.class.getName());
/**
* @description query template by id
* @author liuyanlei
* @param templateId
* @date 2013-08-26
* */
public Template queryTemplateById(UUID templateId)
{
if (templateId == null) {
return null;
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Template template = null;
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
String sql = "select * from template where id = "+templateId.getValue();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next())
{
boolean isNew = rs.getBoolean("is_new");
String xml = null;
if(isNew)
xml = rs.getString("layout_xml");
else
xml = rs.getString("xml");
Document doc = XMLUtil.string2Document(xml, "UTF-8");
if(doc != null)
{
template = new TemplateImpl(doc,rs.getString("create_user"));
}
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(stmt);
DbPoolConnection.getInstance().closeConn(conn);
}
return template;
}
/**
* @description query all templates
* @author liuyanlei
* @date 2013-08-26
* */
public List<Template> queryAllTemplate()
{
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
List<Template> templateList = new ArrayList<Template>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
pstm = conn.prepareStatement("select * from template where is_valid=? order by name");
pstm.setString(1, "1");
rs = pstm.executeQuery();
while(rs.next())
{
boolean isNew = rs.getBoolean("is_new");
String xml = null;
if(isNew)
xml = rs.getString("layout_xml");
else
xml = rs.getString("xml");
Document doc = null;
try {
doc = XMLUtil.string2Document(xml, "UTF-8");
} catch (Exception e) {
System.out.println(rs.getString("name"));
}
if(doc != null)
{
Template template = new TemplateImpl(doc,rs.getString("create_user"));
// if (template != null && !ConfigUtil.abandonTemplateIdSet.contains(template.getId().getValue())) {
if (template != null) {
templateList.add(template);
}
}
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(pstm);
DbPoolConnection.getInstance().closeConn(conn);
}
return templateList;
}
/**
* @description query template by id
* @author liuyanlei
* @param templateId
* @date 2013-08-26
* */
public boolean addTemplate(Template template)
{
Connection conn = null;
PreparedStatement pstm = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "insert into template (id,name,layout_xml,is_new) values (?,?,?,?)";
pstm = conn.prepareStatement(sql);
pstm.setString(1, template.getId().getValue());
pstm.setString(2, template.getName());
pstm.setString(3, template.toXMLString());
pstm.setBoolean(4, true);
if (pstm.executeUpdate()>0) {
return true;
}else {
return false;
}
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstm);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description update a template
* @author liuyanlei
* @date 2013-08-26
* */
public boolean updateTemplate(Template template)
{
Connection conn = null;
PreparedStatement pstm = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "update template set name = ? , layout_xml = ?, is_new = ? where id = ?";
pstm = conn.prepareStatement(sql);
pstm.setString(1, XMLUtil.toSafeXMLString(template.getName()));
pstm.setString(2, template.toXMLString());
pstm.setBoolean(3, true);
pstm.setString(4, template.getId().getValue());
if (pstm.executeUpdate() >0) {
return true;
}else {
return false;
}
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstm);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description remove a template
* @author liuyanlei
* @date 2013-08-26
* */
public boolean removeTemplateById(UUID templateId)
{
return setTemplateValid(templateId, false);
}
/**
* @description:query templates id name map
* @date:2014-5-6 下午5:56:55
* @version:v1.0
* @return
*/
public Map<String, String> queryIdName(){
Map<String, String> idNameMap = new HashMap<String, String>();
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
String sql = "select id,name from template";
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next())
{
idNameMap.put(rs.getString("id"), rs.getString("name"));
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeAll(rs, pstm, conn);
}
return idNameMap;
}
/**
* @description:set template is_valid
* @date:2014-5-6 下午5:57:09
* @version:v1.0
* @param templateId
* @param isValid
* @return
*/
public boolean setTemplateValid(UUID templateId, boolean isValid ){
Connection conn = null;
PreparedStatement pstm = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "update template set is_valid = ? where id = ?";
pstm = conn.prepareStatement(sql);
pstm.setString(1, isValid ? "1" : "0");
pstm.setString(2, templateId.getValue());
return pstm.executeUpdate() >0 ;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeAll(pstm, conn);
}
}
}