package me.chenyi.mm.model;
import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;
import me.chenyi.mm.service.ServiceUtilities;
import me.chenyi.mm.util.SysUtil;
/**
* Class description goes here
*
* @author $Author:$
* @version $Revision:$
*/
public class DatabaseUtil
{
private static Logger logger = Logger.getLogger(DatabaseUtil.class.getName());
// private static boolean isDebug = false;
private static long timeToOpenConnection = -1;
static{
try {
Class.forName("org.hsqldb.jdbcDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static String getDbConnectionUrl()
throws Exception {
File configDir = SysUtil.getConfigDir();
String connectionUrl = "jdbc:hsqldb:file:" + configDir.getAbsolutePath() + "/DB/SMM_DB";
logger.info(String.format("connectionUrl = %s", connectionUrl));
return connectionUrl;
}
public static Connection openConnection() throws Exception {
timeToOpenConnection = System.currentTimeMillis();
return DriverManager.getConnection(getDbConnectionUrl(), "SA", "");
}
public static void closeConnection(Connection connection)
{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
logger.info(String.format("time of session (second) = %d ", ((System.currentTimeMillis() - timeToOpenConnection) / 1000)));
}
public static List<String> initDatabase() {
Connection connection = null;
try {
connection = openConnection();
try {
connection.prepareStatement("select * from Attribute").execute();
} catch (SQLException e) {
e.printStackTrace();
//create table
connection.prepareStatement("CREATE TABLE Attribute (id BIGINT generated by default as identity not null primary key, name varchar(255), type BIGINT)").execute();
connection.prepareStatement("CREATE TABLE NodeType (id BIGINT generated by default as identity not null primary key, name varchar(255))").execute();
connection.prepareStatement("CREATE TABLE Node (id BIGINT generated by default as identity not null primary key, nodeType BIGINT)").execute();
connection.prepareStatement("CREATE TABLE NodeAttrValue (nodeId BIGINT, attrId BIGINT, value varchar(1024))").execute();
// addAttribute(connection, "name", AttributeType.String.ordinal());
// addAttribute(connection, "year", AttributeType.Number.ordinal());
// addAttribute(connection, "score", AttributeType.Number.ordinal());
// addAttribute(connection, "genre", AttributeType.String.ordinal());
// //insert default nodetypes, movie, tv, poeple
// addNodeType(connection, NodeType.TYPE_MOVIE);
// addNodeType(connection, NodeType.TYPE_TV);
// addNodeType(connection, NodeType.TYPE_PEOPLE);
Map<String, Map<String, Class>> attributeMap = ServiceUtilities.getNodeTypeAttributeMap();
for(String nodeTypeName : attributeMap.keySet())
{
addNodeType(connection, nodeTypeName);
Map<String, Class> stringClassMap = attributeMap.get(nodeTypeName);
for(String attributeName : stringClassMap.keySet())
{
Class attributeClass = stringClassMap.get(attributeName);
if (attributeClass.isAssignableFrom(Number.class))
addAttribute(connection, attributeName, AttributeType.Number.ordinal());
else if (attributeClass.isAssignableFrom(File.class))
addAttribute(connection, attributeName, AttributeType.File.ordinal());
else
addAttribute(connection, attributeName, AttributeType.String.ordinal());
}
}
}
} catch (Exception e) {
e.printStackTrace();
return Collections.singletonList(e.toString());
} finally {
try {
connection.close();
} catch (Exception e) {
// e.printStackTrace();
// return Collections.singletonList(e.toString());
}
}
return Collections.emptyList();
}
static long getMaxId(Connection connection, String tableName)
throws Exception {
ResultSet resultSet = connection.prepareStatement("select max(id) from " + tableName).executeQuery();
long maxId = resultSet.getLong(0);
resultSet.close();
return maxId;
}
static Attribute addAttribute(Connection connection, String name, int type)
throws Exception {
// try
// {
// Attribute attribute = getAttribute(connection, name);
// if (isDebug)
// System.out.println("attribute already exist, just return the existing attribute");
// return attribute;
// }
// catch(Exception e)
// {
// //can not find the attribute, try to insert it.
// //e.printStackTrace();
// }
PreparedStatement statement = connection.prepareStatement(String.format("insert into attribute(name, type) values ('%s', %s) ", name, type), Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet.next())
{
long attrId = resultSet.getLong(1);
resultSet.close();
return new Attribute(attrId, name, Attribute.AttributeType.values()[type]);
}
throw new Exception("Failed to get insert attribute");
}
static Attribute getAttribute(Connection connection, String name)
throws SQLException, ObjectNotFoundException
{
ResultSet resultSet = connection.prepareStatement(String.format("select * from attribute where name = '%s') ", name)).executeQuery();
if (resultSet.next())
{
long attrId = resultSet.getLong(1);
int typeId = resultSet.getInt(3);
resultSet.close();
return new Attribute(attrId, name, Attribute.AttributeType.values()[typeId]);
}
else
throw new ObjectNotFoundException();
}
static Attribute getAttribute(Connection connection, int attrId)
throws SQLException, ObjectNotFoundException
{
ResultSet resultSet = connection.prepareStatement(String.format("select * from attribute where id = %s) ", attrId)).executeQuery();
if (resultSet.next())
{
int typeId = resultSet.getInt(3);
String name = resultSet.getString(2);
resultSet.close();
return new Attribute(attrId, name, Attribute.AttributeType.values()[typeId]);
}
else
throw new ObjectNotFoundException();
}
static Collection<Attribute> getAllAttributes(Connection connection) throws SQLException {
ResultSet resultSet = connection.prepareStatement("select * from attribute order by name ").executeQuery();
List<Attribute> attributeList = new ArrayList<Attribute>();
while (resultSet.next()) {
int attrId = resultSet.getInt(1);
int typeId = resultSet.getInt(3);
String name = resultSet.getString(2);
attributeList.add(new Attribute(attrId, name, Attribute.AttributeType.values()[typeId]));
}
resultSet.close();
return attributeList;
}
static Collection<NodeType> getAllNodeTypes(Connection connection) throws SQLException {
ResultSet resultSet = connection.prepareStatement("select * from nodeType order by name ").executeQuery();
List<NodeType> nodeTypeList = new ArrayList();
while (resultSet.next()) {
int typeId = resultSet.getInt(1);
String name = resultSet.getString(2);
nodeTypeList.add(new NodeType(typeId, name));
}
resultSet.close();
return nodeTypeList;
}
static NodeType addNodeType(Connection connection, String name)
throws Exception, ObjectNotFoundException
{
PreparedStatement statement = connection.prepareStatement(String.format("insert into nodetype(name) values ('%s') ", name), Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet.next())
{
int nodeId = resultSet.getInt(1);
resultSet.close();
return new NodeType(nodeId, name);
}
throw new ObjectNotFoundException();
}
static NodeType getNodeType(Connection connection, String name)
throws SQLException, ObjectNotFoundException
{
ResultSet resultSet = connection.prepareStatement(String.format("select * from nodetype where name = '%s' ", name)).executeQuery();
if (resultSet.next())
{
int nodeId = resultSet.getInt(1);
resultSet.close();
return new NodeType(nodeId, name);
}
throw new ObjectNotFoundException();
}
// public static boolean addNodeAttrValue(Connection connection, int nodeId, String attrName, Object value) throws SQLException {
// if (value == null)
// return false;
// AttributeType attributeType = AttributeType.String;
// String strValue = String.valueOf(value);
// if (value instanceof Number) {
// attributeType = AttributeType.Number;
// } else if (value instanceof File) {
// attributeType = AttributeType.File;
// strValue = ((File) value).getAbsolutePath();
// }
// else
// {
// attributeType = AttributeType.String;
// }
//
// Attribute attribute = null;
// try {
// attribute = getAttribute(connection, attrName);
// } catch (SQLException e) {
// try {
// attribute = addAttribute(connection, attrName, attributeType.ordinal());
// } catch (Exception e1) {
// e1.printStackTrace();
// }
// }
// if (attribute == null)
// return false;
//
// //todo: need to check if value already exist, if exist, update it.
// connection.prepareStatement(String.format("insert into NodeAttrValue(nodeId, attrId, value) values (%s, %s, %s)", nodeId, attribute.getId(), strValue)).executeUpdate();
// return true;
// }
static Node updateNode(Connection connection, NodeType nodeType, long nodeId, Map<Attribute, Object> nodeValueMap)
throws Exception
{
List<String> messageList = new ArrayList<String>();
try
{
//todo: should only delete those attribute value in nodeValueMap.
connection.prepareStatement(
String.format("delete from NodeAttrValue where nodeId = %s", nodeId)).executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
messageList.add(e.toString());
}
for(Attribute attr : nodeValueMap.keySet())
{
Object value = nodeValueMap.get(attr);
if(value == null)
{
logger.warning(String.format("value is null for attribute : %s", attr));
continue;
}
try
{
connection.prepareStatement(
String.format("insert into NodeAttrValue(nodeId, attrId, value) values (%s, %s, '%s')", nodeId,
attr.getId(), URLEncoder.encode(String.valueOf(value), "utf-8"))).executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
messageList.add(e.toString());
}
}
return new Node(nodeId, nodeType, nodeValueMap);
}
static Node addNode(Connection connection, NodeType nodeType, Map<Attribute, Object> nodeValueMap)
throws Exception
{
List<String> messageList = new ArrayList<String>();
PreparedStatement statement = connection.prepareStatement(String.format("insert into node(nodetype) values (%s) ", nodeType.getId()), Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet.next())
{
int nodeId = resultSet.getInt(1);
// for (String attrName : nodeValueMap.keySet()) {
// try {
// addNodeAttrValue(connection, nodeId,attrName, nodeValueMap.get(attrName));
// } catch (SQLException e) {
// e.printStackTrace();
// messageList.add(e.toString());
// }
// }
for(Attribute attr : nodeValueMap.keySet())
{
Object value = nodeValueMap.get(attr);
if (value == null)
{
logger.warning(String.format("value is null for attribute : %s", attr));
continue;
}
try
{
connection.prepareStatement(
String.format("insert into NodeAttrValue(nodeId, attrId, value) values (%s, %s, '%s')", nodeId,
attr.getId(), URLEncoder.encode(String.valueOf(value), "utf-8"))).executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
messageList.add(e.toString());
}
}
return new Node(nodeId, nodeType, nodeValueMap);
}
throw new Exception("Fail to insert node");
}
static Collection<Long> getALlNodeIds(Connection connection, NodeType nodeType)
throws Exception
{
ResultSet resultSet = connection.prepareStatement(String.format("select * from node where nodetype = %s", nodeType.getId())).executeQuery();
Collection<Long> nodeList = new ArrayList<Long>();
while (resultSet.next())
{
long nodeId = resultSet.getLong(1);
nodeList.add(nodeId);
}
resultSet.close();
return nodeList;
}
static Collection<Node> getAllNodes(Connection connection, NodeType nodeType)
throws Exception
{
return searchForNode(connection, nodeType, null);
}
static Node getNode(Connection connection, NodeType nodeType, long nodeId)
// static Node getNode(Connection connection, long nodeId)
throws Exception
{
//look up in the db for the node type? or just pass it in?
// ResultSet resultSet = connection.prepareStatement(String.format(
// "select distinct a.id, a.type, v.value, n.nodeType, nt.name from NodeAttrValue v, Attribute a, Node n, NodeType nt where v.attrId = a.id and v.nodeId = %s and v.nodeId = n.id and n.nodeType = nt.id",
// nodeId)).executeQuery();
//
// Node node = null;
// while (resultSet.next())
// {
// long attrId = resultSet.getLong(1);
// int attrType = resultSet.getInt(2);
// Object value = resultSet.getString(3);
// long nodeTypeId = resultSet.getLong(4);
// String nodeTypeName = resultSet.getString(5);
//
// if (node == null)
// node = new Node(nodeId, new NodeType(nodeTypeId, nodeTypeName), new HashMap<Attribute, Object>());
ResultSet resultSet = connection.prepareStatement(String.format("select a.id, a.type, v.value from NodeAttrValue v, Attribute a where v.attrId = a.id and v.nodeId = %s", nodeId)).executeQuery();
Node node = new Node(nodeId, nodeType, new HashMap<Attribute, Object>());
while (resultSet.next())
{
int attrId = resultSet.getInt(1);
int attrType = resultSet.getInt(2);
Object value = resultSet.getString(3);
if (attrType == AttributeType.Number.ordinal())
{
// value = Number.
}
else
{
try
{
value = URLDecoder.decode(String.valueOf(value), "utf8");
}
catch(UnsupportedEncodingException e)
{
e.printStackTrace();
}
if(attrType == AttributeType.File.ordinal())
{
value = new File(SysUtil.getConfigDir().getAbsolutePath() + "/image/" + String.valueOf(value));
}
}
node.addAttrValue(attrId, value);
}
resultSet.close();
return node;
}
static Collection<Node> searchForNode(Connection connection, NodeType nodeType, Map<Attribute, Object> searchCriteria)
throws Exception {
//select unique n.id from NodeAttrValue v, Node n where n.nodeType = XXX and v.nodeId = n.id and (v.attr = XXX and v.value =XXX) and ((v.attr = XXX and v.value =XXX) or (v.attr = XXX and v.value =XXX))
// StringBuffer sb = new StringBuffer("select unique n.id from NodeAttrValue v, Node n where n.nodeType = ");
// sb.append(nodeType.getId());
// sb.append(" and v.nodeId = n.id");
// int index = 0;
// for (Attribute attribute : searchCriteria.keySet()) {
// if (index == 0)
// sb.append(" and ( ");
// else
// sb.append(" or ");
// sb.append(" (v.attrId = ").append(attribute.getId()).append(" and v.value = ").append(searchCriteria.get(attribute)).append(")");
// index ++;
// if (index == searchCriteria.size());
// sb.append(")");
// }
// System.out.println("sb.toString() = " + sb.toString());
StringBuffer sb = new StringBuffer("select v.nodeId, v.attrId, v.value, a.type from NodeAttrValue v, Attribute a where v.attrId = a.id and v.nodeId in (select nodeId from NodeAttrValue where nodeId in (select id from Node where nodeType = ");
sb.append(nodeType.getId()).append(")");
int index = 0;
if (searchCriteria != null) {
for (Attribute attribute : searchCriteria.keySet()) {
if (index == 0)
sb.append(" and ( ");
else
sb.append(" or ");
sb.append(" (attrId = ").append(attribute.getId()).append(" and value = '").append(searchCriteria.get(attribute)).append("')");
index++;
if (index == searchCriteria.size())
sb.append("))");
}
}
// System.out.println("sb.toString() = " + sb.toString());
ResultSet resultSet = connection.prepareStatement(sb.toString()).executeQuery();
Map<Long, Node> nodeMap = new HashMap<Long, Node>();
while (resultSet.next())
{
long nodeId = resultSet.getLong(1);
long attrId = resultSet.getLong(2);
Object value = resultSet.getString(3);
long attrType = resultSet.getLong(4);
if (attrType == AttributeType.Number.ordinal())
{
// value = Number.
}
else if(attrType == AttributeType.File.ordinal())
{
value = new File(SysUtil.getConfigDir().getAbsolutePath() + "/image/" + String.valueOf(value));
}
else
{
try
{
value = URLDecoder.decode(String.valueOf(value), "utf8");
}
catch(UnsupportedEncodingException e)
{
e.printStackTrace();
}
}
Node node = nodeMap.get(nodeId);
if (node == null)
{
node = new Node(nodeId, nodeType, new HashMap<Attribute, Object>());
nodeMap.put(nodeId, node);
}
node.addAttrValue(attrId, value);
}
resultSet.close();
return nodeMap.values();
}
}