private void closeConnection() {
try {
if (statement != null) statement.close();
if (connection != null) {
JDBCExceptionReporter.logWarnings(connection.getWarnings());
connection.clearWarnings();
connectionProvider.closeConnection(connection);
connectionProvider.close();
}
} catch (Exception e) {
System.err.println("Could not close connection");
e.printStackTrace();
}
}
private void createConnection() throws SQLException {
connectionProvider = ConnectionProviderFactory.newConnectionProvider(properties);
connection = connectionProvider.getConnection();
if (!connection.getAutoCommit()) {
connection.commit();
connection.setAutoCommit(true);
}
}
public static ResultSet SelectData() {
ResultSet rs = null;
try {
Connection con = ConnectionProvider.getcon();
Statement stmt = con.createStatement();
rs = stmt.executeQuery("select * from feedback");
} catch (Exception e) {
}
return rs;
}
public static ResultSet getSelect1(bookprop p) {
ResultSet rs = null;
try {
Connection con = ConnectionProvider.getcon();
Statement stmt = con.createStatement();
rs = stmt.executeQuery("select * from appoint where did='" + p.getabc() + "'");
} catch (Exception e) {
}
return rs;
}
public static ResultSet getSelect() {
ResultSet rs = null;
try {
Connection con = ConnectionProvider.getcon();
Statement stmt = con.createStatement();
rs =
stmt.executeQuery(
"select appointdate,appointtime,appointid from appoint where appointid in (select max(appointid) from appoint)");
} catch (Exception e) {
}
return rs;
}
@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")
public static void execute(Configuration jooqConf, CollectionSchema colSchema) {
ConnectionProvider provider = jooqConf.connectionProvider();
Connection connection = provider.acquire();
Statement st = null;
try {
st = connection.createStatement();
st.executeUpdate("DROP SCHEMA " + colSchema.getName() + " CASCADE");
DSLContext dsl = DSL.using(jooqConf);
int deleted =
dsl.deleteFrom(CollectionsTable.COLLECTIONS)
.where(CollectionsTable.COLLECTIONS.NAME.eq(colSchema.getCollection()))
.execute();
assert deleted == 1;
} catch (SQLException ex) {
throw new ToroImplementationException(ex);
} finally {
AutoCloser.close(st);
}
}
public static void InsertData(feedbackprop p) {
try {
Connection con = ConnectionProvider.getcon();
PreparedStatement stmt = con.prepareStatement("insert into feedback values(?,?,?,?,?)");
stmt.setString(1, p.getFname());
stmt.setString(2, p.getLname());
stmt.setString(3, p.getEmail());
stmt.setString(4, p.getMob());
stmt.setString(5, p.getEnq());
stmt.executeUpdate();
} catch (Exception e) {
System.out.println("" + e);
}
}
public static void insertappoint2(bookprop p) {
try {
Connection con = ConnectionProvider.getcon();
PreparedStatement stmt =
con.prepareStatement(
"insert into appoint(appointdate,appointtime,did,pid,symptoms) values(?,?,?,?,?)");
stmt.setString(1, p.getdate1());
stmt.setString(2, p.gettime1());
stmt.setInt(3, Integer.parseInt(p.getdid()));
stmt.setInt(4, Integer.parseInt(p.getpid()));
stmt.setString(5, p.getSymp());
stmt.executeUpdate();
} catch (Exception e) {
System.out.println("" + e);
}
}
private void initPK(ConnectionProvider cp, String shortTableName)
throws SQLException, DBException {
ResultSet rs;
IndexElement[] iearr = getIndexes();
if (iearr != null) {
for (int i = 0; i < iearr.length; i++)
if (iearr[i].isUnique()) {
UniqueKeyElementImpl ukei =
new UniqueKeyElementImpl(
iearr[i].getName().getName(),
false); // false = not primary key (primary flag is setted later)
UniqueKeyElement uke = new UniqueKeyElement(ukei, (TableElement) element, iearr[i]);
uke.setColumns(iearr[i].getColumns());
changeKeys(new UniqueKeyElement[] {uke}, DBElement.Impl.ADD);
}
UniqueKeyElement[] ukes = ((TableElement) element).getUniqueKeys();
rs =
cp.getDatabaseMetaData()
.getPrimaryKeys(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName);
TreeMap cols = new TreeMap();
Object keySeq;
String colName;
if (rs != null) {
HashMap rset = new HashMap();
while (rs.next()) {
keySeq = rs.getObject("KEY_SEQ"); // NOI18N
colName = rs.getString("COLUMN_NAME").trim(); // NOI18N
cols.put(keySeq, colName); // NOI18N
}
rs.close();
}
boolean primary = false;
if (cols != null && cols.size() > 0) primary = true;
if (primary) {
if (ukes == null || ukes.length == 0) {
// issue 56492: no index defined for the primary key
// generate a UniqueKeyElement and an IndexElement for it
String indexName = "primary_key_index"; // NOI18N
int i = 1;
while (((TableElement) element).getIndex(DBIdentifier.create(indexName)) != null) {
indexName = indexName + i;
i++;
}
LinkedList idxs = new LinkedList();
for (Iterator it = cols.values().iterator(); it.hasNext(); ) {
// non-unique = false, thus the index is unique -- see initIndexes()
idxs.add(indexName + "." + it.next() + ".false"); // NOI18N
}
IndexElementImpl iei = new IndexElementImpl(this, indexName, true);
IndexElement ie = new IndexElement(iei, (TableElement) element);
iei.initColumns(idxs);
changeIndexes(new IndexElement[] {ie}, DBElement.Impl.ADD);
UniqueKeyElementImpl ukei = new UniqueKeyElementImpl(ie.getName().getName(), true);
UniqueKeyElement uke = new UniqueKeyElement(ukei, (TableElement) element, ie);
uke.setColumns(ie.getColumns());
changeKeys(new UniqueKeyElement[] {uke}, DBElement.Impl.ADD);
} else if (ukes.length == 1) ukes[0].setPrimaryKey(primary);
else {
ColumnElement[] ces;
Object[] o = cols.values().toArray();
boolean equals;
for (int i = 0; i < ukes.length; i++) {
ces = ukes[i].getColumns();
if (ces.length != o.length) continue;
else {
equals = true;
for (int j = 0; j < ces.length; j++)
if (!o[j].toString().equals(ces[j].getName().getName())) {
equals = false;
break;
}
if (equals) {
ukes[i].setPrimaryKey(primary);
break;
}
}
}
}
}
}
}
/**
* @param expectRelatedTables specifies whether all related tables are expected to be provided.
*/
private void initFKs(ConnectionProvider cp, String shortTableName, boolean expectRelatedTables)
throws SQLException, DBException {
ResultSet rs;
rs =
cp.getDatabaseMetaData()
.getImportedKeys(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName);
String name, fkColName, pkTableName, pkColName, c1, c2, s1, s2;
if (rs != null) {
HashMap rset = new HashMap();
while (rs.next()) {
// test references between two schemas
c1 = rs.getString("PKTABLE_CAT"); // NOI18N
s1 = rs.getString("PKTABLE_SCHEM"); // NOI18N
c2 = rs.getString("FKTABLE_CAT"); // NOI18N
s2 = rs.getString("FKTABLE_SCHEM"); // NOI18N
name = rs.getString("FK_NAME"); // NOI18N
fkColName = rs.getString("FKCOLUMN_NAME").trim(); // NOI18N
pkTableName = rs.getString("PKTABLE_NAME").trim(); // NOI18N
pkColName = rs.getString("PKCOLUMN_NAME").trim(); // NOI18N
if (comp(c1, c2)) {
if (!comp(s1, s2)) continue;
} else continue;
ColumnPairElement cpe;
if (name == null || name.trim().equals("")) name = "GENERATED_FK_" + pkTableName;
else name = name.trim();
ColumnElement lce = getColumn(DBIdentifier.create(fkColName)); // NOI18N
if (lce == null) // should be null only in same cases when FK is computed for view
continue;
SchemaElement se = ((TableElement) element).getDeclaringSchema();
TableElement fte = se.getTable(DBIdentifier.create(pkTableName));
// table could not be found since all related tables were not necessarily provided
if (fte == null && !expectRelatedTables) {
continue;
}
ColumnElement fce = fte.getColumn(DBIdentifier.create(pkColName));
ColumnPairElementImpl cpei =
new ColumnPairElementImpl(
lce.getName().getFullName() + ";" + fce.getName().getFullName()); // NOI18N
cpe = new ColumnPairElement(cpei, lce, fce, (TableElement) element);
changeColumnPairs(new ColumnPairElement[] {cpe}, DBElement.Impl.ADD);
ForeignKeyElement fk = (ForeignKeyElement) keys.find(DBIdentifier.create(name));
if (fk != null) fk.addColumnPair(cpe); // add pair
else {
ForeignKeyElementImpl fkei = new ForeignKeyElementImpl(this, name);
ForeignKeyElement fke = new ForeignKeyElement(fkei, (TableElement) element);
fke.addColumnPair(cpe);
changeKeys(new ForeignKeyElement[] {fke}, DBElement.Impl.ADD);
}
}
rs.close();
}
}
protected void initIndexes(ConnectionProvider cp, String tbl) {
if (cp != null)
try {
boolean unique;
DatabaseMetaData dmd = cp.getDatabaseMetaData();
String shortTableName;
if (tbl == null) shortTableName = getName().getName();
else shortTableName = tbl;
ResultSet rs;
// rs = dmd.getIndexInfo(cp.getConnection().getCatalog(),
// dmd.getUserName().trim(), shortTableName, false, true);
rs =
dmd.getIndexInfo(
cp.getConnection().getCatalog(), cp.getSchema(), shortTableName, false, true);
String name, columnName;
boolean unq;
LinkedList idxs = new LinkedList();
if (rs != null) {
HashMap rset = new HashMap();
String uniqueStr;
while (rs.next()) {
name = rs.getString("INDEX_NAME"); // NOI18N
columnName = rs.getString("COLUMN_NAME"); // NOI18N
if (columnName != null) columnName = columnName.trim();
unq = rs.getBoolean("NON_UNIQUE"); // NOI18N
// hack for PostgreSQL bug 3480: the driver returns quotes around quoted column names
if (columnName != null
&& columnName.length() >= 2
&& columnName.startsWith("\"")
&& columnName.endsWith("\"")) { // NOI18N
columnName = columnName.substring(1, columnName.length() - 1);
}
if (name == null) continue;
else name = name.trim();
if (unq) idxs.add(name + "." + columnName + ".false"); // NOI18N
else idxs.add(name + "." + columnName + ".true"); // NOI18N
}
rs.close();
}
String info;
int start, end;
for (int i = 0; i < idxs.size(); i++) {
info = idxs.get(i).toString();
start = info.indexOf('.'); // NOI18N
end = info.lastIndexOf('.'); // NOI18N
name = info.substring(0, start);
if ((info.substring(end + 1)).equals("true")) // NOI18N
unique = true;
else unique = false;
if (indexes.find(DBIdentifier.create(name)) != null) continue;
IndexElementImpl iei = new IndexElementImpl(this, name, unique);
IndexElement[] ie = {new IndexElement(iei, (TableElement) element)};
iei.initColumns(idxs);
changeIndexes(ie, DBElement.Impl.ADD);
}
} catch (Exception exc) {
if (Boolean.getBoolean("netbeans.debug.exceptions")) // NOI18N
exc.printStackTrace();
}
}
protected void initColumns(ConnectionProvider cp) {
if (cp != null)
try {
DatabaseMetaData dmd = cp.getDatabaseMetaData();
String shortTableName = getName().getName();
ResultSet rs;
// rs = dmd.getColumns(cp.getConnection().getCatalog(),
// dmd.getUserName().trim(), shortTableName, "%");
rs = dmd.getColumns(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName, "%");
int sqlType;
String sqlTypeName;
String colName, colNull, colSize, colDec;
if (rs != null) {
HashMap rset = new HashMap();
while (rs.next()) {
sqlType = rs.getInt("DATA_TYPE"); // NOI18N
sqlTypeName = rs.getString("TYPE_NAME").trim(); // NOI18N
colName = rs.getString("COLUMN_NAME").trim(); // NOI18N
colNull = Integer.toString(rs.getInt("NULLABLE")); // NOI18N
colSize = rs.getString("COLUMN_SIZE"); // NOI18N
colDec = rs.getString("DECIMAL_DIGITS"); // NOI18N
String dbProductName = dmd.getDatabaseProductName().trim();
// Oracle driver hacks
if (dbProductName.indexOf("Oracle") != -1) { // NOI18N
if (sqlType == 11 || ((sqlType == 1111) && sqlTypeName.startsWith("TIMESTAMP")))
sqlType = Types.TIMESTAMP;
if ((sqlType == 1111) && sqlTypeName.equals("FLOAT")) // NOI18N
sqlType = Types.DOUBLE;
if ((sqlType == 1111) && sqlTypeName.equals("BLOB")) // NOI18N
sqlType = Types.BLOB;
if ((sqlType == 1111) && sqlTypeName.equals("CLOB")) // NOI18N
sqlType = Types.CLOB;
if ((sqlType == 1111) && sqlTypeName.equals("NVARCHAR2")) // NOI18N
sqlType = Types.CHAR;
}
// MySQL driver hacks
if (dbProductName.indexOf("MySQL") != -1) { // NOI18N
if ((sqlType == 1111) && sqlTypeName.equalsIgnoreCase("BIT")) // NOI18N
sqlType = Types.BIT;
}
// workaround for i-net Oranxo driver
// value in int range is expected by JDBC API but 4294967296 is returned
try {
new Integer(colSize);
} catch (NumberFormatException exc) {
colSize = Integer.toString(Integer.MAX_VALUE);
}
ColumnElementImpl cei =
new ColumnElementImpl(colName, Integer.toString(sqlType), colNull, colSize, colDec);
ColumnElement ce = new ColumnElement(cei, (TableElement) element);
ColumnElement[] c = {ce};
changeColumns(c, DBElement.Impl.ADD);
}
rs.close();
}
} catch (Exception exc) {
if (Boolean.getBoolean("netbeans.debug.exceptions")) // NOI18N
exc.printStackTrace();
}
}