@Override
public List<MstGasto> read() {
Connection cn;
PreparedStatement pst;
ResultSet rs;
String sql;
List<MstGasto> lst = new ArrayList();
try {
Class.forName(bd.getDriver());
cn = DriverManager.getConnection(bd.getUrl(), bd.getUser(), bd.getPasswd());
sql = "select * from mst_tipo_gastos order by corr_gasto";
pst = cn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
lst.add(
new MstGasto(
rs.getInt("cod_residencial"),
rs.getInt("corr_gasto"),
rs.getString("desc_gasto"),
rs.getString("cod_cta_conta"),
rs.getDouble("valor_gasto"),
rs.getDate("fecha_creacion"),
rs.getString("cod_usuario"),
rs.getString("activo")));
}
rs.close();
pst.close();
cn.close();
} catch (SQLException e) {
log.severe(e.toString());
} catch (Exception e) {
log.severe(e.toString());
}
return lst;
}
@Override
public User getUserDetails(String uname) {
Connection con = null;
try {
con = DBConnectionFactory.getConnection();
Statement st = con.createStatement();
String sql =
"select fname, lname, email, mobile, usertype "
+ "from user_details "
+ "where uname='"
+ uname
+ "'";
ResultSet rs = st.executeQuery(sql);
if (rs.next()) {
User u = new User();
u.setUname(uname);
u.setFname(rs.getString(1));
u.setLname(rs.getString(2));
u.setEmail(rs.getString(3));
u.setMobile(rs.getString(4));
u.setUsertype(rs.getString(5));
return u;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
public static void main(String[] args) throws Exception {
Connection con =
DriverManager.getConnection("jdbc:db2://localhost:50000/sample", "db2test", "db2testpass");
// con.setAutoCommit(false);
try {
PreparedStatement ps = con.prepareStatement("SELECT * FROM demo.employee ORDER BY empno");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println("" + rs.getString("empno") + ", " + rs.getString("firstnme"));
}
rs.close();
ps.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
con.close();
}
}
public void updateDb(Connection con) {
try (Statement statement = con.createStatement();
ResultSet resultSet =
statement.executeQuery("SELECT * FROM contact where firstname = \"Michael\"")) {
resultSet.absolute(1); // 1 zmienic na dokładny wiersz w ktorym jest michael - metadata?
resultSet.updateString("phoneNumber", "+009392032302");
resultSet.updateRow();
System.out.println(" After the update");
System.out.println("ID \tfName \tsurname \temail \t\tphoneNo");
resultSet.beforeFirst();
while (resultSet.next()) {
System.out.println(
resultSet.getInt("id")
+ "\t"
+ resultSet.getString("name")
+ "t\""
+ resultSet.getString("surname")
+ "t\""
+ resultSet.getString("email")
+ "t\""
+ resultSet.getString("phoneNumber"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void addToDb(Contact c, Connection con) {
try (Statement s = con.createStatement();
ResultSet resultSet = s.executeQuery("SELECT * FROM contact")) {
resultSet.moveToInsertRow();
resultSet.updateInt("ID", c.getId());
resultSet.updateString("name", c.getName());
if (c.getClass().getName().equals("jdbase.model.Person"))
resultSet.updateString("surname", ((Person) c).getSurname);
else resultSet.updateString("surname", " ---- ");
resultSet.updateString("email", c.getEmail());
resultSet.updateString("phoneNumber", c.getPhoneNumber());
resultSet.insertRow();
System.out.println(" After adding :");
System.out.println("ID \tfName \tsurname \temail \t\tphoneNo");
while (resultSet.next()) {
System.out.println(
resultSet.getInt("id")
+ "\t"
+ resultSet.getString("name")
+ "t\""
+ resultSet.getString("surname")
+ "t\""
+ resultSet.getString("email")
+ "t\""
+ resultSet.getString("phoneNumber"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 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;
}
private void getAndPopulateRooms() {
// Get and populate rooms
try {
Statement stmnt = conn.createStatement();
String strQuery =
"select top 1 opsal_id, opsalnamn, platsnamn, plats_id from "
+ Config.DATABASE_OPERATION_VIEW
+ " WHERE platsnamn = 'Huddinge'";
ResultSet res = stmnt.executeQuery(strQuery);
while (res.next()) {
String roomID = res.getString("opsal_id");
if (!OrbitStamps.operatingRooms.containsKey(roomID)) {
Room r = new Room(roomID);
r.locationID = res.getString("plats_id");
r.locationName = res.getString("platsnamn");
r.roomName = res.getString("opsalnamn");
OrbitStamps.operatingRooms.put(roomID, r);
}
}
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
}
public ArrayList<String> getAllData(String name) {
String tname = '%' + name + '%';
System.out.println(tname);
ArrayList<String> details = new ArrayList<>();
try {
String query = "SELECT * FROM `patient` WHERE First_name LIKE ? ORDER BY First_name";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, tname);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
System.out.println("Records from the database");
while (rs.next()) {
details.add(rs.getString("First_name")); // 0
details.add(rs.getString("Date_of_birth")); // 1
details.add(rs.getString("Address")); // 2
details.add(rs.getString("Patient_Id")); // 2
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return details;
}
@Override
public Client search(int id) {
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement("SELECT * FROM clients");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
if (Integer.valueOf(resultSet.getString("id_client")) == id) {
return new Client(
resultSet.getString("name"),
resultSet.getString("login"),
resultSet.getString("phone"),
resultSet.getString("email"),
resultSet.getString("password"));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public String query7() {
String name, coach, output;
int budget;
String sqlText;
try {
sql = connection.createStatement();
sqlText =
"CREATE VIEW topScorer AS SELECT cid, MAX(goals) AS scores FROM player GROUP BY cid";
sql.executeUpdate(sqlText);
sqlText = "CREATE VIEW maxScore AS SELECT MAX(scores) AS maxscore FROM topScorer";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW topScorerTeam AS SELECT cid FROM topScorer JOIN maxScore ON (topScorer.scores = maxScore.maxscore)";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW budgetTeam AS SELECT cid, SUM(value) AS budget FROM player GROUP BY cid";
sql.executeUpdate(sqlText);
sqlText = "CREATE VIEW lowestBudget AS SELECT MIN(budget) AS minbudget FROM budgetTeam";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW lowestBudgetTeam AS SELECT cid, minbudget AS budget FROM lowestBudget JOIN budgetTeam ON (lowestBudget.minbudget = budgetTeam.budget)";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW countryLowestBudgetTopScorer AS SELECT topScorerTeam.cid AS cid, budget FROM topScorerTeam JOIN lowestBudgetTeam ON topScorerTeam.cid = lowestBudgetTeam.cid";
sql.executeUpdate(sqlText);
// We project Query7 over here.
sqlText =
"SELECT name, coach, budget FROM countryLowestBudgetTopScorer JOIN country ON (countryLowestBudgetTopScorer.cid = country.cid)";
rs = sql.executeQuery(sqlText);
if (!rs.isBeforeFirst()) {
return "";
} else {
rs.next();
// Query7 (String name, String coach, integer budget)
name = rs.getString("name");
coach = rs.getString("coach");
budget = rs.getInt("budget");
output = name + ":" + coach + ":" + budget;
while (rs.next()) {
name = rs.getString("name");
coach = rs.getString("coach");
budget = rs.getInt("budget");
output += "#" + name + ":" + coach + ":" + budget;
}
return output;
}
} catch (SQLException e) {
return "";
}
}
@Override
public ArrayList<Emp> getList() {
ArrayList<Emp> emps = new ArrayList<Emp>(100);
if (conn == null) {
System.err.println("No connect!!");
return emps;
}
try {
Statement st = conn.createStatement();
ResultSet rs =
st.executeQuery(
"" + "SELECT e.id,e.lname,e.fname,e.mname,e.d_hire,e.d_fire " + "FROM DDT_EMP AS e");
while (rs.next()) {
Emp emp = new Emp();
emp.setId(rs.getInt("id"));
emp.setLastName(rs.getString("lname"));
emp.setFirstName(rs.getString("fname"));
emp.setMiddleName(rs.getString("mname"));
emp.setHireDate(rs.getDate("d_hire"));
emp.setFireDate(rs.getDate("d_fire"));
emps.add(emp);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return emps;
}
public String selectionQuery() {
Connection koneksi = null;
Statement stat = null;
String str = "";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
koneksi =
DriverManager.getConnection("jdbc:mysql://localhost/shopkartdb", "shopkart", "welcome");
System.out.println(koneksi);
stat = koneksi.createStatement();
ResultSet hasil = stat.executeQuery("SELECT * FROM loginInfo");
while (hasil.next()) {
str = str + (hasil.getString(1)) + hasil.getString(2);
}
stat.close();
koneksi.close();
} catch (SQLException sqle) {
str = "SQLException error";
} catch (ClassNotFoundException cnfe) {
str = "ClassNotFoundException error";
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return str;
}
public ArrayList<String> getEquipos() throws SQLException, ClassNotFoundException {
ArrayList<String> foo = new ArrayList();
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:Equipos.db");
c.setAutoCommit(false);
System.out.println("Tabla Equipos abierta");
stmt = c.createStatement();
try (ResultSet rs = stmt.executeQuery("SELECT Nombre FROM Equipos;")) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("Nombre");
String form = rs.getString("Formacion");
int h1 = rs.getInt("Arquero");
int h2 = rs.getInt("Defensor");
int h3 = rs.getInt("Mediocampo");
int h4 = rs.getInt("Ataque");
foo.add(Integer.toString(id));
foo.add(name);
foo.add(form);
foo.add(Integer.toString(h1));
foo.add(Integer.toString(h2));
foo.add(Integer.toString(h3));
foo.add(Integer.toString(h4));
}
}
stmt.close();
c.close();
System.out.println("Operation done successfully");
return foo;
}
public void updateScore(List<Player> players) {
try {
if (connection == null || players.size() <= 0) {
return;
}
List<String> playersName = new ArrayList<String>();
for (Player player : players) {
playersName.add("'" + player.getName() + "'");
}
String arrs = join(playersName, ",");
String strSql = "SELECT * FROM player WHERE name IN (" + arrs + ")";
cacheScores.clear();
Map<String, Object> map;
Statement sql = connection.createStatement();
ResultSet result = sql.executeQuery(strSql);
while (result.next()) {
map = new HashMap<String, Object>();
map.put("kills", result.getInt("kills"));
map.put("deaths", result.getInt("deaths"));
map.put("mobs", result.getInt("mobs"));
map.put("prefix", result.getString("prefix"));
cacheScores.put(result.getString("name"), map);
}
result.close();
sql.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void testReadDomainsAndGuids() throws Exception {
CustomerDAO dao = new CustomerDAO();
IConfiguration configuration = EasyMock.createStrictMock(IConfiguration.class);
dao.setConfiguration(configuration);
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Set<String> domains = new HashSet<String>();
Set<GlobalIdentifier> guids = new HashSet<GlobalIdentifier>();
int custId = 34;
int cloudService = 2;
int replicationZone = 453;
int cloudService2 = 1;
int replicationZone2 = 13;
int cloudService3 = 3;
// first exec of loop
EasyMock.expect(resultSet.getString(10)).andReturn("domain123");
EasyMock.expect(resultSet.getString(11)).andReturn("guid123");
EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService);
EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getInt(1)).andReturn(custId);
// second exec of loop
EasyMock.expect(resultSet.getString(10)).andReturn("domain456");
EasyMock.expect(resultSet.getString(11)).andReturn("guid456");
EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService2);
EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone2);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getInt(1)).andReturn(custId);
// third exec of loop (guid not valid with no cloud service)
EasyMock.expect(resultSet.getString(10)).andReturn("domain456");
EasyMock.expect(resultSet.getString(11)).andReturn("guid789");
EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService3);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getInt(1)).andReturn(custId + 1); // ends loop with mismatched custid
EasyMock.replay(resultSet);
assertTrue(
"Should have another item even.",
dao.readDomainsAndGuids(resultSet, custId, domains, guids));
EasyMock.verify(resultSet);
assertEquals("Should have 2 domains.", 2, domains.size());
assertTrue("Domain123 not found.", domains.contains("domain123"));
assertTrue("Domain456 not found.", domains.contains("domain456"));
assertEquals("Should have 2 guids.", 2, guids.size());
for (GlobalIdentifier guid : guids) {
if (guid.getGuid().equals("guid123")) {
assertEquals("Wrong cloud service in guid123", CloudService.GOOGLE, guid.getService());
assertEquals("Wrong replication zone.", replicationZone, guid.getReplicationZone());
} else {
assertEquals("Wrong cloud service in guid456", CloudService.OFFICE365, guid.getService());
assertEquals("Wrong replication zone.", replicationZone2, guid.getReplicationZone());
}
}
}
/**
* metoda pobierajaca wszystkie rekordy z BD1.
*
* @return
*/
public ArrayList<StudentFirst> getAllStudentFromDBfirst() {
logger.info("getAllContactFromDB");
ResultSet result = null;
ArrayList<StudentFirst> students = new ArrayList<>();
try {
prepStmt = conn.prepareStatement("SELECT * FROM student1");
result = prepStmt.executeQuery();
int id, index;
String name, surname, university, faculty, field;
for (int i = 0; result.next(); i++) {
id = result.getInt("stud1_id");
index = result.getInt("stud1_index");
name = result.getString("stud1_name");
surname = result.getString("stud1_lastname");
university = result.getString("stud1_university");
faculty = result.getString("stud1_faculty");
field = result.getString("stud1_field");
students.add(new StudentFirst(id, index, name, surname, university, faculty, field));
logger.info(students.get(i).toString());
}
if (students.size() == 0) {
logger.info("Pusta BD?");
}
return students;
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
e1.printStackTrace();
return null;
}
}
public void printDadoTable() {
out("----------------------Dado----------------------");
try {
stmt = c.createStatement();
rs = stmt.executeQuery("SELECT * FROM " + tableDadoName + ";");
while (rs.next()) {
int id = rs.getInt("id");
int id_codi = rs.getInt("id_cod");
String data = rs.getString("data");
String local = rs.getString("local");
String acao = rs.getString("acao");
String detalhes = rs.getString("detalhes");
out("ID = " + id);
out("id_cod = " + id_codi);
out("data = " + data);
out("local = " + local);
out("acao = " + acao);
out("detalhes = " + detalhes);
System.out.println();
}
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
public int getChannelNumber(int pId, String date, int did, String time) {
int channelNum = -1;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` WHERE Date =? AND Doctor_Id =? ORDER by Time";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
count = 1;
while (rs.next()) {
if (Integer.parseInt(rs.getString("Patient_Id")) != pId) {
count++;
}
if (Integer.parseInt(rs.getString("Patient_Id")) == pId) {
channelNum = count;
}
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channelNum;
}
@Test
public void testUpsertValuesWithDate() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("create table UpsertDateTest (k VARCHAR not null primary key,date DATE)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("upsert into UpsertDateTest values ('a',to_date('2013-06-08 00:00:00'))");
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
ResultSet rs =
conn.createStatement().executeQuery("select k,to_char(date) from UpsertDateTest");
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("2013-06-08 00:00:00", rs.getString(2));
}
public ArrayList<String> getNaturalJoinList(String date) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `channelling` NATURAL JOIN `patient` WHERE Date =? ORDER by Time";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientRealname = rs.getString("First_name"); // 0
channel.add(patientRealname);
String patientDOB = rs.getString("Date_of_birth"); // 1
channel.add(patientDOB);
String patientAddress = rs.getString("Address"); // 2
channel.add(patientAddress);
String patientId = rs.getString("Patient_Id"); // 3
channel.add(patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public String getPatient_RealName(int pId) {
String fName;
String lName;
String Name = "null";
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
fName = rs.getString("First_name");
lName = rs.getString("Last_name");
Name = fName + " " + lName;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return Name;
}
/**
* Прочитать из таблиц и вывести всех провайдеров
*
* @throws IOException
* @throws SQLException
*/
public static void task1() throws IOException, SQLException {
DBHelper DBHelper = new DBHelper();
Connection con = DBHelper.getConnection();
Statement statement = con.createStatement();
ResultSet rs =
statement.executeQuery(
"SELECT pv.id prvid, pv.name prvname,"
+ " STRING_AGG(DISTINCT pc.currency, ',' ORDER BY pc.currency) currencies,"
+ " STRING_AGG(DISTINCT pcn.country, ',' ORDER BY pcn.country) countries"
+ " FROM provider pv"
+ " JOIN provider_curr pc"
+ " ON pv.id = pc.provider_id"
+ " JOIN provider_country pcn"
+ " ON pv.id = pcn.provider_id"
+ " GROUP BY pv.id, pv.name;");
while (rs.next()) {
int prId = rs.getInt("prvid");
String prName = rs.getString("prvname");
String prCurrencies = rs.getString("currencies");
String prCountries = rs.getString("countries");
System.out.printf("%s (id %d) - [%s], [%s]\n", prName, prId, prCurrencies, prCountries);
}
DBHelper.closeConnection(con);
}
@Override
public Emp get(Integer objectId) {
if (conn == null) {
System.err.println("No connect!!");
return null;
}
Emp emp = null;
try {
final String sql =
"SELECT " + "id,lname,fname,mname,d_hire,d_fire " + "FROM DDT_EMP" + " WHERE id = ?";
PreparedStatement st = conn.prepareStatement(sql);
st.setInt(1, objectId.intValue());
ResultSet rs = st.executeQuery();
while (rs.next()) {
emp = new Emp();
emp.setId(rs.getInt("id"));
emp.setLastName(rs.getString("lname"));
emp.setFirstName(rs.getString("fname"));
emp.setMiddleName(rs.getString("mname"));
emp.setHireDate(rs.getDate("d_hire"));
emp.setFireDate(rs.getDate("d_fire"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
private void getIgnoreList() {
Connection con = null;
try {
con = pool.getConnection(timeout);
Statement s = con.createStatement();
s.executeQuery("SELECT `name`, `type` FROM `ignores`");
ResultSet rs = s.getResultSet();
this.ignore_list.clear();
this.soft_ignore_list.clear();
while (rs.next()) {
if (rs.getString("type").equals("hard")) {
this.ignore_list.add(rs.getString("name").toLowerCase());
} else {
this.soft_ignore_list.add(rs.getString("name").toLowerCase());
}
}
rs.close();
s.close();
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
/** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter pw = null;
try {
pw = response.getWriter();
String name = request.getParameter("myval");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
Statement st = conn.createStatement();
String str = "select * from student where name like %" + name + "%";
ResultSet rs = st.executeQuery(str);
while (rs.next()) {
pw.println(
rs.getString(1) + " " + rs.getInt(2) + " " + rs.getString(3) + " " + rs.getInt(4));
}
conn.close();
} catch (Exception e) {
pw.print(e + "");
}
}
private ImmutableMap<String, JdbcTable> computeTables() {
Connection connection = null;
ResultSet resultSet = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
resultSet = metaData.getTables(catalog, schema, null, null);
final ImmutableMap.Builder<String, JdbcTable> builder = ImmutableMap.builder();
while (resultSet.next()) {
final String tableName = resultSet.getString(3);
final String catalogName = resultSet.getString(1);
final String schemaName = resultSet.getString(2);
final String tableTypeName = resultSet.getString(4);
// Clean up table type. In particular, this ensures that 'SYSTEM TABLE',
// returned by Phoenix among others, maps to TableType.SYSTEM_TABLE.
// We know enum constants are upper-case without spaces, so we can't
// make things worse.
final String tableTypeName2 = tableTypeName.toUpperCase().replace(' ', '_');
final TableType tableType = Util.enumVal(TableType.class, tableTypeName2);
final JdbcTable table = new JdbcTable(this, catalogName, schemaName, tableName, tableType);
builder.put(tableName, table);
}
return builder.build();
} catch (SQLException e) {
throw new RuntimeException("Exception while reading tables", e);
} finally {
close(connection, null, resultSet);
}
}
public JobIniciado retornaUltimoLote(Connection conn, String job, int operacao)
throws SQLException {
String sql =
" select job, operacao, tripulacao,recurso, max(data_fim) \n"
+ "from joblote where job = ? and operacao = ? \n"
+ "group by job, operacao, tripulacao, recurso ";
PreparedStatement stmt = null;
ResultSet rs = null;
JobIniciado iniciado = null;
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.trim().replace(".", ""));
stmt.setInt(2, operacao);
rs = stmt.executeQuery();
if (rs.next()) {
iniciado = new JobIniciado();
iniciado.setJob(rs.getString(1));
iniciado.setOperacao(rs.getInt(2));
iniciado.setTripulacao(rs.getDouble(3));
iniciado.setRecurso(rs.getString(4));
Timestamp data = rs.getTimestamp(5);
iniciado.setData(Validacoes.getDataHoraString(data));
}
return iniciado;
}
@Override
public Project get(int id) {
Project result = null;
try (Connection connection = dbDataSourceProvider.getConnection()) {
try (PreparedStatement statement = connection.prepareStatement(sqlProvider.get4Load())) {
statement.setInt(1, id);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
result =
new Project(
resultSet.getString("name"),
resultSet.getInt("goal"),
resultSet.getDate("deadline_date"));
result.setShortDescription(resultSet.getString("description"));
result.setBalance(resultSet.getInt("balance"));
result.setDemoLink(resultSet.getString("demo_link"));
result.setCreateDate(resultSet.getDate("create_date"));
result.setId(id);
}
}
connection.commit();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return result;
}
public boolean authenticatePassword(String username, String password) {
String query = "SELECT iMaildropID, vchUsername, vchPassword FROM m_Maildrop WHERE tiLocked=0;";
try (Statement statement = conn.createStatement()) {
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
if (username.equals(rs.getString("vchUsername"))) {
if (password.equals(rs.getString("vchPassword"))) {
seissionUserID = rs.getInt("iMaildropID");
String setMaildropToLocked =
"UPDATE m_Maildrop SET tiLocked=1 WHERE iMaildropID=" + seissionUserID + ";";
statement.executeUpdate(setMaildropToLocked);
conn.commit();
return true;
} else {
return false;
}
}
}
} catch (SQLException e) {
printSQLException(e);
}
return false;
}
public List<Empleado> getAllEmpleados() {
List ll = new LinkedList();
Statement st;
ResultSet rs;
String url = "jdbc:mysql://localhost:3306/food";
String user = "******";
String pass = "******";
String driver = "com.mysql.jdbc.Driver";
try (Connection connection = DriverManager.getConnection(url, user, pass)) {
Class.forName(driver);
st = connection.createStatement();
String recordQuery = ("Select * from empleados");
rs = st.executeQuery(recordQuery);
while (rs.next()) {
Integer id = rs.getInt("idEmpleados");
String name = rs.getString("Nombre");
String telf = rs.getString("Telefono");
ll.add(new Empleado(id, name, telf));
/// System.out.println(id +","+ name +","+ apellido +","+ cedula +","+ telf +"
// "+direccion+"");
}
} catch (ClassNotFoundException | SQLException ex) {
Logger.getLogger(MenuEmpleados.class.getName()).log(Level.SEVERE, null, ex);
}
return ll;
}
