public boolean checkChartUpdateRequired(String doi, Connection con) throws SQLException {
PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
getUpdateStmt.setString(1, doi);
ResultSet rs1 = getUpdateStmt.executeQuery();
int lastNcites = 0;
if (rs1.first()) {
lastNcites = rs1.getInt("lastNcites");
} else {
rs1.close();
getUpdateStmt.close();
return true;
}
rs1.close();
getUpdateStmt.close();
PreparedStatement getNcitesStmt = con.prepareStatement(DEF_GET_NCITES_QUERY);
getNcitesStmt.setString(1, doi);
int ncites = 0;
ResultSet rs2 = getNcitesStmt.executeQuery();
if (rs2.first()) {
ncites = rs2.getInt("ncites");
}
rs2.close();
getNcitesStmt.close();
if (ncites != lastNcites) {
return true;
} else {
return false;
}
} // - checkChartUpdateRequired
public static String getRate(String voter, String ans) {
DBConnector dBConnector = new DBConnector();
Connection dbConnection = dBConnector.getConnection();
String selectTableSQL = "select * from RATING where idvoter= ? and idanswer=?";
String selectTableSQL2 = "select * from user where username= ? ";
PreparedStatement statement;
try {
statement = dbConnection.prepareStatement(selectTableSQL2);
statement.setString(1, voter);
ResultSet rs = statement.executeQuery();
while (rs.next()) {
voter = rs.getObject(1) + "";
}
statement.close();
statement = dbConnection.prepareStatement(selectTableSQL);
statement.setString(1, voter);
statement.setString(2, ans);
rs = statement.executeQuery();
int count = 0;
while (rs.next()) {
count++;
return rs.getObject(4) + "";
}
statement.close();
} catch (SQLException ex) {
Logger.getLogger(Rating.class.getName()).log(Level.SEVERE, null, ex);
}
return "gagal";
}
public long getAverAmount5(String paramString) throws SQLException {
long l = 0L;
PreparedStatement localPreparedStatement = null;
ResultSet localResultSet = null;
try {
this.conn = getConn();
String str =
"SELECT MIN(TradeDate) FROM (SELECT TradeDate FROM HistoryDayData WHERE CommodityID = ? ORDER BY TradeDate DESC) WHERE rownum < 6";
localPreparedStatement = this.conn.prepareStatement(str);
localPreparedStatement.setString(1, paramString);
localResultSet = localPreparedStatement.executeQuery();
Timestamp localTimestamp = null;
if (localResultSet.next()) localTimestamp = localResultSet.getTimestamp(1);
localResultSet.close();
localPreparedStatement.close();
str = "SELECT AVG(TotalAmount) FROM HistoryDayData WHERE CommodityID = ? AND TradeDate >= ?";
localPreparedStatement = this.conn.prepareStatement(str);
localPreparedStatement.setString(1, paramString);
localPreparedStatement.setTimestamp(2, localTimestamp);
localResultSet = localPreparedStatement.executeQuery();
if (localResultSet.next()) l = localResultSet.getLong(1);
} catch (SQLException localSQLException) {
localSQLException.printStackTrace();
} finally {
closeStatement(localResultSet, localPreparedStatement, null);
}
return l;
}
/**
* Returns the normalized Authority value for an author based on the name passed in. If no
* authority exists, null will be returned.
*
* @param author the author to get the authority information for
* @return the normalized authority information or null if no authority exists.
*/
public static String getNormalizedAuthorAuthorityFromDatabase(String author) {
if (!connectToDatabase()) {
return null;
} else {
try {
getPreferredAuthorByOriginalNameStmt.setString(1, author);
// First check without normalization
ResultSet originalNameResults = getPreferredAuthorByOriginalNameStmt.executeQuery();
if (originalNameResults.next()) {
String authority = originalNameResults.getString("normalizedName");
// Found a match
originalNameResults.close();
return authority;
} else {
// No match, check alternate names for the author
String normalizedAuthor = AuthorNormalizer.getNormalizedName(author);
getPreferredAuthorByAlternateNameStmt.setString(1, normalizedAuthor);
ResultSet alternateNameResults = getPreferredAuthorByAlternateNameStmt.executeQuery();
if (alternateNameResults.next()) {
String authority = alternateNameResults.getString("normalizedName");
alternateNameResults.close();
return authority;
}
}
} catch (Exception e) {
logger.error("Error loading authority information from database", e);
}
}
return null;
}
@Override
public UserInfo getUserByAuthToken(String token) throws SQLException {
UserInfo userInfo = null;
Connection connection = null;
PreparedStatement stmt = null;
try {
connection = Database.getConnection();
stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_USER_BY_TOKEN);
stmt.setString(1, token);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
userInfo = new UserInfo();
userInfo.setName(rs.getString("id"));
stmt.close();
stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_ROLES_OF_USER);
stmt.setString(1, userInfo.getName());
rs = stmt.executeQuery();
while (rs.next()) {
String role = rs.getString("role");
userInfo.getRoles().add(Role.valueOf(role));
}
}
} catch (SQLException e) {
throw e;
} finally {
if (stmt != null) stmt.close();
if (connection != null) connection.close();
}
return userInfo;
}
/**
* Load user's info from database.
*
* @param userName
*/
@Override
protected UserIdentity loadUser(String userName) {
try {
try (Connection connection = getConnection();
PreparedStatement statement1 = connection.prepareStatement(_userSql)) {
statement1.setObject(1, userName);
try (ResultSet rs1 = statement1.executeQuery()) {
if (rs1.next()) {
int key = rs1.getInt(_userTableKey);
String credentials = rs1.getString(_userTablePasswordField);
List<String> roles = new ArrayList<String>();
try (PreparedStatement statement2 = connection.prepareStatement(_roleSql)) {
statement2.setInt(1, key);
try (ResultSet rs2 = statement2.executeQuery()) {
while (rs2.next()) {
roles.add(rs2.getString(_roleTableRoleField));
}
}
}
return putUser(
userName,
Credential.getCredential(credentials),
roles.toArray(new String[roles.size()]));
}
}
}
} catch (NamingException e) {
LOG.warn("No datasource for " + _jndiName, e);
} catch (SQLException e) {
LOG.warn("Problem loading user info for " + userName, e);
}
return null;
}
/** Web service operation */
@WebMethod(operationName = "getUserByToken")
@WebResult(name = "User")
public User getUserByToken(@WebParam(name = "token") String token) {
User user = new User();
try {
Statement stmt = conn.createStatement();
String sql;
sql = "SELECT user_id FROM token WHERE token_id = ?";
PreparedStatement dbStatement = conn.prepareStatement(sql);
dbStatement.setString(1, token);
ResultSet rs = dbStatement.executeQuery();
while (rs.next()) {
user.setUserID(rs.getInt("user_id"));
}
rs.close();
String sql2 = "SELECT * FROM user WHERE user_id = ?";
PreparedStatement dbStatement2 = conn.prepareStatement(sql);
dbStatement2.setInt(1, user.getUserID());
ResultSet rs2 = dbStatement2.executeQuery();
while (rs2.next()) {
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
user.setPassword(rs.getString("password"));
}
rs2.close();
stmt.close();
} catch (SQLException ex) {
Logger.getLogger(UserWS.class.getName()).log(Level.SEVERE, null, ex);
}
return user;
}
@Test
public void moveTasksToBacklog() throws Exception {
given()
.header(PermanentUserData.tokenHeader)
.post("/m/backlog/2016/04/26")
.then()
.statusCode(204);
try (Connection conn = ds.getConnection();
PreparedStatement ps =
conn.prepareStatement(
"select count (*) from task where appuser_id = ? and date is null")) {
ps.setLong(1, PermanentUserData.user.getId());
try (ResultSet rs = ps.executeQuery(); ) {
rs.next();
assertThat(rs.getInt(1), equalTo(2));
}
}
given().filter(sessionFilter).post("/web/backlog/2016/05/16").then().statusCode(204);
try (Connection conn = ds.getConnection();
PreparedStatement ps =
conn.prepareStatement(
"select count (*) from task where appuser_id = ? and date is null")) {
ps.setLong(1, PermanentUserData.user.getId());
try (ResultSet rs = ps.executeQuery(); ) {
rs.next();
assertThat(rs.getInt(1), equalTo(3));
}
}
}
public static void fivejp(
ResultSet[] data1, ResultSet[] data2, ResultSet[] data3, ResultSet[] data4, ResultSet[] data5)
throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps1 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
ps1.setInt(1, 1);
data1[0] = ps1.executeQuery();
PreparedStatement ps2 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
ps2.setInt(1, 2);
data2[0] = ps2.executeQuery();
PreparedStatement ps3 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
ps3.setInt(1, 3);
data3[0] = ps3.executeQuery();
PreparedStatement ps4 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
ps4.setInt(1, 4);
data4[0] = ps4.executeQuery();
PreparedStatement ps5 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
ps5.setInt(1, 5);
data5[0] = ps5.executeQuery();
conn.close();
}
public static void selectRows(int p1, int p2, ResultSet[] data1, ResultSet[] data2)
throws SQLException {
System.out.println("selectRows - 2 arg - 2 rs");
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps = conn.prepareStatement("select * from t1 where i = ?");
ps.setInt(1, p1);
data1[0] = ps.executeQuery();
ps = conn.prepareStatement("select * from t1 where i >= ?");
ps.setInt(1, p2);
data2[0] = ps.executeQuery();
if (p2 == 99) data2[0].close();
// return no results
if (p2 == 199) {
data1[0].close();
data1[0] = null;
data2[0].close();
data2[0] = null;
}
// swap results
if (p2 == 299) {
ResultSet rs = data1[0];
data1[0] = data2[0];
data2[0] = rs;
}
conn.close();
}
public static int randomPage() throws SQLException {
String query = "select id from pages where finding=0 order by Rand() limit 1";
// String query="select min(id) from pages";
// String query="SELECT * FROM `pages` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `pages`
// ) ORDER BY id LIMIT 1;";
Connection j = textdisplay.DatabaseWrapper.getConnection();
PreparedStatement p = j.prepareStatement(query);
ResultSet rs;
rs = p.executeQuery(query);
rs.next();
int min = rs.getInt(1);
if (true) {
j.close();
return min;
}
rs = p.executeQuery("select max(id) from pages");
rs.next();
int max = rs.getInt(1);
int count = max - min;
Random generator = new Random();
int rec = generator.nextInt(count);
rec += min;
j.close();
return rec;
}
public static int userCount(String user) throws SQLException {
String query = "select count(id) from pages where finder=?";
System.out.print(query);
Connection j = textdisplay.DatabaseWrapper.getConnection();
PreparedStatement p = j.prepareStatement(query);
p.setString(1, user);
ResultSet rs = p.executeQuery();
rs.next();
int amount = rs.getInt(1);
query = "select * from leaders where uname=?";
p = j.prepareStatement(query);
p.setString(1, user);
rs = p.executeQuery();
if (!rs.next()) {
query = "insert into leaders (uname,count) values(?,0)";
p = j.prepareStatement(query);
p.setString(1, user);
p.execute();
}
query = "update leaders set count=? where uname=?";
p = j.prepareStatement(query);
p.setInt(1, amount);
p.setString(2, user);
p.execute();
j.close();
return amount;
}
public Topic displayques(int alid) throws Exception {
Topic topic = new Topic();
Connection conn = null;
PreparedStatement stmt = null;
PreparedStatement stmt1 = null;
ResultSet rs = null;
try {
conn = Dbtopic.getConn();
stmt = conn.prepareStatement("select * from IMQUES WHERE IMQUES.ALID =?");
stmt1 =
conn.prepareStatement(
"UPDATE imques SET scannumber=scannumber+1 WHERE imques.alid = ? ");
stmt.setInt(1, alid);
stmt1.setInt(1, alid);
rs = stmt.executeQuery();
stmt1.executeQuery();
while (rs != null && rs.next()) {
topic.setAlid(rs.getInt("alid"));
topic.setPubren(rs.getString("pubren"));
topic.setPubtime(rs.getString("pubtime"));
topic.setQues(rs.getString("ques"));
topic.setQuesinfo(rs.getString("quesinfo"));
}
} finally {
Dbtopic.closeconn(conn);
}
return topic;
}
/**
* Consulta as fatos do prestador passado em um determinado periodo
*
* @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as fatos
* @param strNumPeriodo - o periodo de referencia do qual se deseja obter os fatos
* @return um array de fatos do prestador fornecido como paramentro
*/
public static final ResumoFato[] buscaResumoFato(String strCdContrato, String strNumPeriodo)
throws Exception {
Connection con = ConnectionPool.getConnection();
ResumoFato[] fatos = null;
PreparedStatement pst;
ResultSet rset;
int qtdeFatos = 0;
try {
pst = con.prepareStatement(CONSULTA_RESUMO_FATO);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
pst.setString(3, strCdContrato);
pst.setString(4, strNumPeriodo);
pst.setString(5, strCdContrato);
pst.setString(6, strNumPeriodo);
pst.setString(7, strCdContrato);
pst.setString(8, strNumPeriodo);
pst.setString(9, strCdContrato);
pst.setString(10, strNumPeriodo);
rset = pst.executeQuery();
if (!rset.next()) {
return null;
} // if ( ! rset.next() )
do {
qtdeFatos += 1;
} while (rset.next());
System.out.println("qtdeFatos -> " + qtdeFatos);
fatos = new ResumoFato[qtdeFatos];
rset = pst.executeQuery();
qtdeFatos = 0;
while (rset.next()) {
fatos[qtdeFatos] = montaResumoFato(rset);
qtdeFatos++;
} // while
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new Exception(
"Não foi possivel estabelecer conexão com a base de "
+ "dados.Erro:\n"
+ sqle.getMessage());
} finally { // catch()
if (con != null) {
con.close();
System.out.println("Fechou a conexao");
} // if
}
return fatos;
} // buscaResumoFato()
private void testLikeIndex(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
stat.execute("INSERT INTO TEST VALUES(2, 'World')");
stat.execute("create index idxname on test(name);");
PreparedStatement prep, prepExe;
prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
assertEquals(1, prep.getParameterMetaData().getParameterCount());
prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
prep.setString(1, "%orld");
prepExe.setString(1, "%orld");
ResultSet rs = prep.executeQuery();
rs.next();
String plan = rs.getString(1);
assertTrue(plan.indexOf(".tableScan") >= 0);
rs = prepExe.executeQuery();
rs.next();
assertEquals("World", rs.getString(2));
assertFalse(rs.next());
prep.setString(1, "H%");
prepExe.setString(1, "H%");
rs = prep.executeQuery();
rs.next();
String plan1 = rs.getString(1);
assertTrue(plan1.indexOf("IDXNAME") >= 0);
rs = prepExe.executeQuery();
rs.next();
assertEquals("Hello", rs.getString(2));
assertFalse(rs.next());
stat.execute("DROP TABLE IF EXISTS TEST");
}
public ResultSet tranzakcioLekerdezesDatumEsOsszegEsSzamlaszamSzerint(
int datumTol,
int datumIg,
long osszegTol,
long osszegIg,
long szamlaszam,
String rendezesSzempont)
throws SQLException {
if (rendezesSzempont.equals("RendezésDátumSzerint")) {
if (pstmtForTLDEOESZSZRDSZ == null)
pstmtForTLDEOESZSZRDSZ =
conn.prepareStatement(
"select * from tranzakcio where ((datum >= ? and datum <= ?) and (osszeg >= ? and osszeg <= ?) and (szamlaszam = ? or kuldo = ?)) order by datum");
pstmtForTLDEOESZSZRDSZ.setInt(1, datumTol);
pstmtForTLDEOESZSZRDSZ.setInt(2, datumIg);
pstmtForTLDEOESZSZRDSZ.setLong(3, osszegTol);
pstmtForTLDEOESZSZRDSZ.setLong(4, osszegIg);
pstmtForTLDEOESZSZRDSZ.setLong(5, szamlaszam);
pstmtForTLDEOESZSZRDSZ.setLong(6, szamlaszam);
return pstmtForTLDEOESZSZRDSZ.executeQuery();
}
if (pstmtForTLDEOESZSZROSZ == null)
pstmtForTLDEOESZSZROSZ =
conn.prepareStatement(
"select * from tranzakcio where ((datum >= ? and datum <= ?) and (osszeg >= ? and osszeg <= ?) and (szamlaszam = ? or kuldo = ?)) order by osszeg");
pstmtForTLDEOESZSZROSZ.setInt(1, datumTol);
pstmtForTLDEOESZSZROSZ.setInt(2, datumIg);
pstmtForTLDEOESZSZROSZ.setLong(3, osszegTol);
pstmtForTLDEOESZSZROSZ.setLong(4, osszegIg);
pstmtForTLDEOESZSZROSZ.setLong(5, szamlaszam);
pstmtForTLDEOESZSZROSZ.setLong(6, szamlaszam);
return pstmtForTLDEOESZSZROSZ.executeQuery();
}
@Override
public User read(int id) {
try (Connection connection = getConnection()) {
List<Role> roles = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(READ_USER_ROLES_BY_USER_ID)) {
statement.setInt(1, id);
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
roles.add($.roleFrom(resultSet));
}
}
}
try (PreparedStatement statement = connection.prepareStatement(READ_BY_ID)) {
statement.setInt(1, id);
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
return $.userFrom(resultSet, roles);
}
}
}
} catch (SQLException e) {
processException(e);
}
return null;
}
public void listProducts() {
products = new ArrayList<Product>();
try {
ResultSet results;
if (listAllProducts) {
results = listAllProductsStatement.executeQuery();
} else {
listByCustomerStatement.setString(1, customerLogin);
results = listByCustomerStatement.executeQuery();
}
while (results.next()) {
int auctionId = results.getInt("auction_id");
Product product = new Product(session.getDb(), auctionId);
products.add(product);
}
} catch (SQLException e) {
while (e != null) {
debug.println(e.toString());
debug.flush();
e = e.getNextException();
}
}
if (products.size() <= 0) {
productsBox.setLine(0, "");
productsBox.setLine(1, " No products to show.");
for (int i = 2; i < 13; i++) {
productsBox.setLine(i, "");
}
} else {
productsBox.setLine(0, " Name | Status | Highest Bid Amount | Buyer or Highest Bidder");
productsBox.setLine(1, "");
ArrayList<Product> productsOnPage = paginator.paginate(products, curPage, 5);
int lineOffset = 0;
for (int i = 0; i < productsOnPage.size(); i++) {
Product product = productsOnPage.get(i);
// lineOffset works now
lineOffset = i * 2;
productsBox.setLine(
lineOffset + 3,
" "
+ product.name
+ " | "
+ product.status
+ " | $"
+ product.amount
+ " | "
+ product.getHighestBidder());
}
// clear the lines of productBox if there are not as many products as before
int lastLine = productsOnPage.size() * 2 + 2;
for (int i = lastLine; i < 12; i++) {
productsBox.setLine(i, "");
}
productsBox.setLine(12, paginator.getPageMenu(products, curPage, 5));
}
}
// public static void main(String[] args)
// {
void query() {
try {
// System.out.println("hello ");
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// System.out.println("heloo2");
System.out.println("Driver Loading Succesful");
Connection conn =
DriverManager.getConnection(
"jdbc:ucanaccess://C:\\Users\\naman\\Desktop\\Database1.accdb");
System.out.println("connecttion with Database Succesful");
Statement stmt = conn.createStatement();
// access obj()
PreparedStatement ps =
conn.prepareStatement(
"select customer_id,customer_name,phone,address,email from customer where customer_id = ?");
v = Integer.parseInt(st);
ps.setInt(1, v);
PreparedStatement ps1 =
conn.prepareStatement(
"select m.medicine_name,o.quantity from medicine m,order o,new n where n.customer_id=? and n.order_id=o.order_id and o.medicine_id=m.medicine_id");
ps1.setInt(1, v);
// ps.setString(2, "*");
rs1 = ps1.executeQuery();
rs = ps.executeQuery();
// ResultSetMetaData rsmd = rs.getMetaData();
// System.out.println("Result set Created");
// int id;
// int salary;
// System.out.println("id ");
// String s;
// int col=rsmd.getColumnCount();
// System.out.println(col);
// id=rsmd.getColumnCount();
// int i=0;
/* while(rs.next())
{
for ( i = 1; i <= col; i++)
{
s=rs.getString(i);
System.out.println(s);
}
//salary=rs.getInt("salary");
// System.out.println(s+" " );
// s=rs.getString("Medicine_Name");
//System.out.println(s);
//i++;
}
*/
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
private ResultSet executeQuery() throws SQLException {
ResultSet result = null;
if (!mInitialized) {
prepareStatement();
}
// Get the SQL connection
SqlConnector sql_connector = mDataset.getSqlConnector();
if (sql_connector != null) {
// Count number of result
if (mNbRows < 0) {
setParams(mStatCount);
ResultSet tmp = mStatCount.executeQuery();
if (tmp.next()) {
mNbRows = tmp.getInt(1);
}
}
// Execute the query
setParams(mStatQuery);
result = mStatQuery.executeQuery();
}
return result;
}
/**
* Add a new user into GrafixPlane with specified UID
*
* @param UID The specified UID
* @param name The new user's name
* @param passwd The new user's password
* @param level The access level that would be assigned to the user
* @return The newly created user, never null
* @throws SQLException If there are some thing wrong with the database
* @throws NameExistsException if this name already belongs to another user
* @throws UIDExistsException if this UID already belongs to another user
*/
public static User newUser(int UID, String name, String passwd, AccessLevel level)
throws SQLException, NameExistsException, UIDExistsException {
if (userPool.containsValue(UID)) throw new UIDExistsException();
synchronized (userByName) {
userByName.setString(1, name);
if (userByName.executeQuery().first())
throw new NameExistsException(); // A user with same name already exists
synchronized (userByID) {
userByID.setInt(1, UID);
if (userByID.executeQuery().first())
throw new UIDExistsException(); // A user with same UID already exists
}
synchronized (newUser) {
newUser.setInt(1, UID);
newUser.setString(2, name);
newUser.setString(3, passwd);
newUser.setInt(4, level.value);
newUser.setInt(5, 0);
newUser.execute();
}
}
User result = new User(UID, name, level, 0);
synchronized (userPool) {
userPool.put(UID, result);
}
return result;
}
private int checkSituationRecordInDb(MobisTrafficSituationRecord sitRec, Connection con) {
try {
String selectString =
"SELECT * FROM public.mobissituationrecord WHERE providerid='"
+ sitRec.getProviderId()
+ "' AND provider='"
+ sitRec.getProvider()
+ "' AND version="
+ sitRec.getSituationRecordVersion()
+ ";";
PreparedStatement stmt = con.prepareStatement(selectString);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return 0;
} else {
selectString =
"SELECT * FROM public.mobissituationrecord WHERE providerid='"
+ sitRec.getProviderId()
+ "' AND provider='"
+ sitRec.getProvider()
+ "';";
stmt = con.prepareStatement(selectString);
rs = stmt.executeQuery();
if (rs.next()) {
return 1;
} else return 2;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return -1;
}
}
/* ------------------------------------------------------------ */
@Override
protected UserIdentity loadUser(String username) {
try {
if (null == _con) connectDatabase();
if (null == _con) throw new SQLException("Can't connect to database");
PreparedStatement stat = _con.prepareStatement(_userSql);
stat.setObject(1, username);
ResultSet rs = stat.executeQuery();
if (rs.next()) {
int key = rs.getInt(_userTableKey);
String credentials = rs.getString(_userTablePasswordField);
stat.close();
stat = _con.prepareStatement(_roleSql);
stat.setInt(1, key);
rs = stat.executeQuery();
List<String> roles = new ArrayList<String>();
while (rs.next()) roles.add(rs.getString(_roleTableRoleField));
stat.close();
return putUser(
username,
Credential.getCredential(credentials),
roles.toArray(new String[roles.size()]));
}
} catch (SQLException e) {
LOG.warn("UserRealm " + getName() + " could not load user information from database", e);
closeConnection();
}
return null;
}
private void testValues(Connection conn) throws SQLException {
PreparedStatement prep = conn.prepareStatement("values(?, ?)");
prep.setInt(1, 1);
prep.setString(2, "Hello");
ResultSet rs = prep.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
prep = conn.prepareStatement("select * from values(?, ?), (2, 'World!')");
prep.setInt(1, 1);
prep.setString(2, "Hello");
rs = prep.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
rs.next();
assertEquals(2, rs.getInt(1));
assertEquals("World!", rs.getString(2));
prep = conn.prepareStatement("values 1, 2");
rs = prep.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
rs.next();
assertEquals(2, rs.getInt(1));
}
public static ArrayList<StatisticalReports> retrieveStatistics(String username, String password) {
ArrayList<StatisticalReports> allStatistics = new ArrayList<StatisticalReports>();
DBCreation sql = DBCreation.getInstance();
Connection conn;
ResultSet res = null;
PreparedStatement st;
conn = sql.connect();
ArrayList<String> nodes = new ArrayList<String>();
int i;
if (!checkMobileExistance(username, password)) {
System.out.println("Invalid un or pw of android client");
return null;
}
try {
st = conn.prepareStatement("select clientID from clients");
res = st.executeQuery();
while (res.next()) {
nodes.add(res.getString("clientID"));
}
st.clearParameters();
for (i = 0; i < nodes.size(); i++) {
StatisticalReports nodeStatistics = new StatisticalReports();
st = conn.prepareStatement("SELECT * FROM statistics WHERE nodeID = ? ");
st.setString(1, nodes.get(i));
res = st.executeQuery();
ArrayList<StatisticsEntry> statistic = new ArrayList<StatisticsEntry>();
while (res.next()) {
StatisticsEntry stat = new StatisticsEntry();
stat.setNodeID(res.getString("nodeID"));
stat.setInterfaceName(res.getString("interfaceName"));
// stat.setMaliciousPatternID(res.getInt("maliciousPatternID"));
stat.setMaliciousPattern(getMaliciousByID(res.getInt("maliciousPatternID")));
stat.setInterfaceIP(res.getString("interfaceIP"));
stat.setFrequency(res.getInt("frequency"));
statistic.add(stat);
}
nodeStatistics.setStatisticalReportEntries(statistic);
allStatistics.add(nodeStatistics);
st.clearParameters();
}
} catch (SQLException e) {
e.printStackTrace();
// System.out.println("Can't get malicious patterns from db");
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return allStatistics;
}
private void testTempView(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
PreparedStatement prep;
stat.execute("CREATE TABLE TEST(FIELD INT PRIMARY KEY)");
stat.execute("INSERT INTO TEST VALUES(1)");
stat.execute("INSERT INTO TEST VALUES(2)");
prep =
conn.prepareStatement(
"select FIELD FROM "
+ "(select FIELD FROM (SELECT FIELD FROM TEST WHERE FIELD = ?) AS T2 "
+ "WHERE T2.FIELD = ?) AS T3 WHERE T3.FIELD = ?");
prep.setInt(1, 1);
prep.setInt(2, 1);
prep.setInt(3, 1);
ResultSet rs = prep.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
prep.setInt(1, 2);
prep.setInt(2, 2);
prep.setInt(3, 2);
rs = prep.executeQuery();
rs.next();
assertEquals(2, rs.getInt(1));
stat.execute("DROP TABLE TEST");
}
/** Get the Manuscript that contains this particular Folio */
public Manuscript(int folio) throws SQLException {
String query = "select msID from folios where pageNumber=?";
Connection j = null;
PreparedStatement ps = null;
try {
j = DatabaseWrapper.getConnection();
ps = j.prepareStatement(query);
ps.setInt(1, folio);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
this.id = rs.getInt(1);
query = "select city,repository, msIdentifier,archive from manuscript where id=?";
ps = j.prepareStatement(query);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
this.city = rs.getString(1);
this.repository = rs.getString(2);
this.collection = rs.getString(3);
this.archive = rs.getString(4);
}
}
} finally {
if (j != null) {
DatabaseWrapper.closeDBConnection(j);
DatabaseWrapper.closePreparedStatement(ps);
}
}
}
private void testPreparedSubquery(Connection conn) throws SQLException {
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE TEST(ID IDENTITY, FLAG BIT)");
s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(0, FALSE)");
s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(1, FALSE)");
PreparedStatement u = conn.prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
PreparedStatement p = conn.prepareStatement("UPDATE TEST SET FLAG=true WHERE ID=(SELECT ?)");
p.clearParameters();
p.setLong(1, 0);
assertEquals(1, p.executeUpdate());
p.clearParameters();
p.setLong(1, 1);
assertEquals(1, p.executeUpdate());
ResultSet rs = u.executeQuery();
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
assertTrue(rs.getBoolean(2));
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertTrue(rs.getBoolean(2));
p = conn.prepareStatement("SELECT * FROM TEST WHERE EXISTS(SELECT * FROM TEST WHERE ID=?)");
p.setInt(1, -1);
rs = p.executeQuery();
assertFalse(rs.next());
p.setInt(1, 1);
rs = p.executeQuery();
assertTrue(rs.next());
s.executeUpdate("DROP TABLE IF EXISTS TEST");
}
@Override
public RegionDatabase load(UUID uuid) {
HashMap<UUID, ProtectedChunkRegion> protectedChunks = new HashMap<>();
PreparedStatement preparedStatement =
baseSqliteConnection.createPreparedStatement(
"SELECT COUNT(REGION_DATA) AS _SIZE_ FROM UPRO_%w_REGIONS"
.replace("%w", uuid.toString().replace("-", "")));
try {
int size = preparedStatement.executeQuery().getInt("_SIZE_");
preparedStatement =
baseSqliteConnection.createPreparedStatement(
"SELECT REGION_DATA FROM UPRO_%w_REGIONS"
.replace("%w", uuid.toString().replace("-", "")));
ResultSet regions = preparedStatement.executeQuery();
int i = 0;
while (i < size) {
ProtectedChunkRegion protectedChunkRegion =
ProtectedChunkRegion.deserialize(regions.getString(1));
protectedChunks.put(protectedChunkRegion.getUUID(), protectedChunkRegion);
i++;
regions.next();
}
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return new RegionDatabase(protectedChunks);
}
@Override
public List<List<String>> getListFilter() {
List<List<String>> listFilter = new ArrayList<List<String>>();
String result;
Connection connection = SingletonConnection.getConnection();
try {
// for field constructor
List<String> listField = new ArrayList<String>();
PreparedStatement prepStat =
connection.prepareStatement("SELECT DISTINCT Constructor FROM equipment;");
ResultSet resSet = prepStat.executeQuery(); // recovery results
// for each line store result in list of constructor
while (resSet.next()) {
result = resSet.getString("Constructor");
listField.add(result); // add constructor in list
}
listFilter.add(listField); // add list of constructor in list of lists
// for field type
listField = new ArrayList<String>();
prepStat = connection.prepareStatement("SELECT DISTINCT Type FROM equipment;");
resSet = prepStat.executeQuery(); // recovery results
// for each line store result in list of type
while (resSet.next()) {
result = resSet.getString("Type");
listField.add(result); // add type in list
}
listFilter.add(listField); // add list of type in list of lists
// for field statut
listField = new ArrayList<String>();
prepStat = connection.prepareStatement("SELECT DISTINCT Status FROM equipment;");
resSet = prepStat.executeQuery(); // recovery results
// for each line store result in list of statut
while (resSet.next()) {
result = resSet.getString("Status");
listField.add(result); // add statut in list
}
listFilter.add(listField); // add list of statut in list of lists
// for field localisation
listField = new ArrayList<String>();
prepStat = connection.prepareStatement("SELECT DISTINCT Localisation FROM equipment;");
resSet = prepStat.executeQuery(); // recovery results
// for each line store result in list of localisation
while (resSet.next()) {
result = resSet.getString("Localisation");
listField.add(result); // add localisation in list
}
listFilter.add(listField); // add list of localisation in list of lists
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return listFilter; // return list of lists
}
