package center.db;
import ru.vassaev.core.exception.SysException;
import ru.vassaev.core.util.Strings;
import ru.vassaev.core.db.Manager;
import ru.vassaev.core.db.RsField;
import ru.vassaev.core.db.RsParam;
import java.sql.*;
import java.util.*;
/**
* Класс Statement.
* Правила использования:
* 1. new Statement()
* 2. выполнить prepare для запроса с параметрами, предварённые "::"
* 3. установить все параметры
* 4. получить PreparedStatement с помощью getStatement()
* 5. выполнить PreparedStatement
* перед выполнением, если необходимо, можно воспользоваться
* методом setParam(PreparedStatement st...) для изменения параметров
* 6. освободить PreparedStatement с помощью freeStatement()
* 7. --- Вернуться к пункту 3, если необходимо
* 8. выполнить close()
*
* @author Vassaev A.V.
* @version 1.0
*/
public class StatementNew {
public static void clearParams(PreparedStatement st) throws SysException {
try {
st.clearBatch();
st.clearWarnings();
st.clearParameters();
} catch (SQLException ex) {
throw new SysException(ex);
}
}
protected ru.vassaev.core.db.Types tp = null;
protected String idf = "::";
protected String alias = null;
public StatementNew() {
super();
}
public StatementNew(String idf) {
super();
if ((idf != null) && (idf.length() > 0)) {
this.idf = idf;
}
}
public StatementNew(String idf, String alias, String query) throws SysException {
super();
if ((idf != null) && (idf.length() > 0)) {
this.idf = idf;
}
prepare(alias, query);
}
protected class ParamInfo {
public RsParam prm = null;
public String NAME = null;
public ArrayList<Integer> links = new ArrayList<Integer>();
public Object value = null;
public int type = java.sql.Types.VARCHAR;
}
protected RsField[] flds = null;
protected RsParam[] prms = null;
// Список параметров
protected ArrayList<ParamInfo> prmsi = new ArrayList<ParamInfo>();
protected String sql = null;
// Список параметров по именам
protected Map<String, ParamInfo> prmsh = new TreeMap<String, ParamInfo>();
public synchronized void prepare(String alias, String query)
throws SysException {
if (this.alias != null)
throw new SysException("The statement is not closed");
if (alias == null)
throw new SysException("The database's alias can't be null");
int i = 0;
int l = query.length();
StringBuffer sql = new StringBuffer();
int c = 0;
int len;
while (i < l) {
int j = query.indexOf(idf, i);
if (j >= 0) {
sql.append(query.substring(i, j));
i = j + idf.length();
len = Strings.chooseIdentifier(query, i);
if (len > 0) {
i += len;
String name = query.substring(i - len, i);
ParamInfo pi = prmsh.get(name);
if (pi == null) {
pi = new StatementNew.ParamInfo();
pi.NAME = name;
prmsh.put(name, pi);
}
c++;
pi.links.add(c);
prmsi.add(pi);
sql.append('?');
} else {
sql.append(idf);
}
} else {
sql.append(query.substring(i));
i = l;
}
}
this.sql = sql.toString();
Connection con = null;
try {
con = Manager.getConnection(alias);
tp = Manager.getTypes(con);
PreparedStatement st = null;
try {
st = con.prepareStatement(this.sql);
try {
prms = tp.getParams(st);
} catch (Throwable ex) {
prms = new RsParam[prmsi.size()];
}
} catch (SQLException ex) {
throw new SysException(ex);
} finally {
if (st != null)
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} finally {
Manager.freeConnection(con);
}
this.alias = alias;
}
public String getSQL() {
return sql;
}
public boolean isParamExist(String name) {
return (prmsh.get(name) != null);
}
public ArrayList<String> getParamNames() {
Iterator<String> enm = prmsh.keySet().iterator();
ArrayList<String> r = new ArrayList<String>();
while (enm.hasNext())
r.add(enm.next());
return r;
}
private Set<PreparedStatement> stmts = new HashSet<PreparedStatement>();
private HashMap<Connection, PreparedStatement> con_st = new HashMap<Connection, PreparedStatement>();
public void registerOutParameter(CallableStatement stmt, String name, int sqlType) throws SQLException {
ParamInfo pi = prmsh.get(name);
if (pi == null)
return;
for (int i = pi.links.size() - 1; i >= 0; i--) {
int j = pi.links.get(i);
stmt.registerOutParameter(j, sqlType);
}
}
public CallableStatement getCallStatement(Connection con) throws SysException {
try {
if (!alias.equals(Manager.getAlias(con)))
throw new SysException("The connection is had another alias : "
+ alias + " != " + Manager.getAlias(con));
CallableStatement st = (CallableStatement)con_st.get(con);
if (st == null) {
st = con.prepareCall(sql);
con_st.put(con, st);
}
for (int p = prmsi.size() - 1; p >= 0; p--) {
ParamInfo pi = prmsi.get(p);
for (int i = pi.links.size() - 1; i >= 0; i--) {
int j = pi.links.get(i);
if (pi.value == null) {
// st.setNull(j, (pi.prm == null) ? pi.type : pi.type);
} else {
st.setObject(j, pi.value, pi.type);
}
}
}
stmts.add(st);
return st;
} catch (SQLException ex) {
throw new SysException(ex);
}
}
public PreparedStatement getStatement(Connection con) throws SysException {
try {
if (!alias.equals(Manager.getAlias(con)))
throw new SysException("The connection is had another alias : "
+ alias + " != " + Manager.getAlias(con));
PreparedStatement st = con_st.get(con);
if (st == null)
synchronized (con) {
st = con.prepareStatement(sql);
con_st.put(con, st);
}
for (int p = prmsi.size() - 1; p >= 0; p--) {
ParamInfo pi = prmsi.get(p);
for (int i = pi.links.size() - 1; i >= 0; i--) {
int j = pi.links.get(i);
if (pi.value == null) {
// st.setNull(j, (pi.prm == null) ? pi.type : pi.type);
} else {
st.setObject(j, pi.value, pi.type);
}
}
}
stmts.add(st);
return st;
} catch (SQLException ex) {
throw new SysException(ex);
}
}
public void freeStatement(PreparedStatement st) throws SysException {
try {
freeStatement0(st);
} finally {
stmts.remove(st);
}
}
private void freeStatement0(PreparedStatement st) throws SysException {
if (st == null)
return;
try {
st.clearParameters();
st.clearWarnings();
st.clearBatch();
do
try {
ResultSet rs = st.getResultSet();
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
while (st.getMoreResults());
} catch (SQLException e) {
throw new SysException(e);
}
}
public void setParam(String name, Object value, int type)
throws SysException {
ParamInfo pi = prmsh.get(name);
if (pi == null)
return;
pi.value = value;
pi.type = type;
}
public void setParam(PreparedStatement st, String name, Object value, int type)
throws SysException {
if (!stmts.contains(st))
return;
ParamInfo pi = prmsh.get(name);
if (pi == null)
return;
try {
for (int i = pi.links.size() - 1; i >= 0; i--) {
int j = pi.links.get(i);
if (value == null) {
st.setNull(j, (pi.prm == null) ? type : pi.prm.TYPE);
} else {
switch (type) {
case java.sql.Types.BIGINT:
st.setLong(j, new Long(value.toString()));
case java.sql.Types.INTEGER:
st.setInt(j, new Integer(value.toString()));
default:
st.setObject(j, value);
}
}
}
} catch (SQLException e) {
throw new SysException(e);
}
}
public Object getParam(String name) throws SysException {
ParamInfo pi = prmsh.get(name);
if (pi == null)
return null;
return pi.value;
}
public Object getParam(CallableStatement stmt, String name) throws SysException {
ParamInfo pi = prmsh.get(name);
if (pi == null)
return null;
// if (pi.prm.READ_ONLY) {
ArrayList<Integer> ids = pi.links;
if (ids.size() != 1)
return pi.value;
try {
return stmt.getObject(ids.get(0));
} catch (SQLException e) {
throw new SysException(e);
}
// }
}
public void close() throws SysException {
for (PreparedStatement stmt : stmts) {
freeStatement0(stmt);
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
con_st.clear();
stmts.clear();
tp = null;
alias = null;
}
public boolean isClosed() {
return (alias == null);
}
public boolean isOpened() {
return (alias != null);
}
}