@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
return set;
}
public int getReservationCnt(int product_id) {
connect();
ArrayList<reservation> list = new ArrayList<reservation>();
int cnt = 0;
try {
pstmt = con.prepareStatement("select * from reservation where product_id = " + product_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
reservation r = new reservation();
r.setReserve_id(rs.getInt("reserve_id"));
r.setReserve_date(rs.getString("reserve_date"));
r.setPeople_num(rs.getInt("people_num"));
r.setPeople_id(rs.getString("people_id"));
r.setProduct_id(rs.getInt("product_id"));
r.setSeat(rs.getInt("seat"));
r.setPassport_num(rs.getString("passport_num"));
cnt++;
list.add(r);
result++;
}
this.setResult(result);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return cnt;
}
public static int retornaQtdTotal(Connection conn, JobProtheus job) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
int qtdTotal = 0;
int qtdSucata = 0;
String sqlVerQtd =
"select job, sum(qtd_transf), sum(hr_tot), sum(qtd_sucata) from jobLote where job = ? and operacao = ? group by job";
stmt = conn.prepareStatement(sqlVerQtd);
stmt.setString(1, job.getJob().trim());
stmt.setInt(2, job.getOperacao());
rs = stmt.executeQuery();
String qtdJob = "";
double totHora = 0;
while (rs.next()) {
qtdJob = rs.getString(1); // numero job
qtdTotal = rs.getInt(2); // quantidade total
totHora = rs.getInt(3); // hora total em minutos
qtdSucata = rs.getInt(4);
}
return qtdTotal + qtdSucata;
}
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 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 "";
}
}
@BeforeClass
public static void setUpBeforeClass() throws Exception {
con.addCustomer(
"Buddy", "Bear", "1520 Garnet Ave", "", "San Diego", "CA", "92109", "4766666656");
con.addPublication("Runner Magazine", "Sports", 9.80, "Monthly", 5);
ResultSet r = con.searchCustomer(0, "Buddy", "");
try {
while (r.next()) {
testCustID = r.getInt("CustomerID");
}
r.close();
} catch (Exception e) {
e.printStackTrace();
}
ResultSet rs = con.searchPublication(0, "", "Runner Magazine");
try {
while (rs.next()) {
testPubID = rs.getInt("PublicationID");
}
} catch (Exception e) {
e.printStackTrace();
}
subscriptions newSub = new subscriptions(testCustID, testPubID);
}
@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 void updateOptionPrice(
String modelName, String optionSetName, String optionName, float newPrice) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql =
"update options set price = "
+ newPrice
+ " where name= '"
+ optionName
+ "' and option_id= "
+ opsid
+ ";";
statement.executeUpdate(sql); // update it with name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Trago GetById(int id) throws Exception {
try {
Connection con = Conexion.getConexion();
Statement sql = con.createStatement();
ResultSet cmd = sql.executeQuery("Select * from tragos where id=" + id + ";");
Trago tr = null;
while (cmd.next()) {
tr = new Trago();
tr.setDescripcion(cmd.getString("descripcion"));
tr.setId(cmd.getInt("id"));
tr.setEstado((cmd.getInt("estado") == 1) ? true : false);
tr.setGraduacion(cmd.getString("graduacion"));
tr.setModoPreparacion(cmd.getString("modopreparacion"));
tr.setNombre(cmd.getString("nombre"));
tr.setPositivo(cmd.getInt("positivo"));
tr.setNegativo(cmd.getInt("negativo"));
}
cmd.close();
sql.close();
con.close();
if (tr != null) {
tr.setReceta(ADReceta.getInstancia().GetAllByIdTrago(tr.getId()));
}
return tr;
} catch (Exception ex) {
throw ex;
}
}
// Needs a connection so it can fetch more stuff lazily
Copy(ResultSet rs) throws SQLException {
super();
copyId = rs.getInt("copy#");
bibId = rs.getInt("bib#");
note = rs.getString("pac_note");
location = rs.getString("location");
locationName = rs.getString("location_name");
collectionDescr = rs.getString("collection_descr");
collection = rs.getString("collection");
callNumber =
new CallNumber(
rs.getString("call_number"),
rs.getString("call_type"),
rs.getString("copy_number"),
rs.getString("call_type_hint"));
callType = rs.getString("call_type");
callTypeHint = rs.getString("call_type_hint");
callTypeName = rs.getString("call_type_name");
mediaType = rs.getString("media_type");
mediaTypeDescr = rs.getString("media_descr");
summaryOfHoldings = rs.getBoolean("summary_of_holdings");
itemType = rs.getString("itype");
itemTypeDescr = rs.getString("idescr");
}
// metode getRestrictions nodrošina ierobežojumu saraksta izgūšanu no datubāzes
public static List getRestrictions() {
List<restrictionsList> restrList = new ArrayList<>();
try {
Statement stmt = connect.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT R.ID, PT.Type_Name, R.Time , PT.Type_count_day, R.Used FROM RESTRICTIONS R INNER JOIN PROCESS_TYPE PT WHERE R.Type_ID = PT.PT_ID;");
while (rs.next()) {
int restr_id = rs.getInt("ID");
String type_name = rs.getString("Type_Name");
int max_time = rs.getInt("Time");
int used_time = rs.getInt("Type_count_day");
Boolean is_used = rs.getBoolean("Used");
restrictionsList list =
new restrictionsList(restr_id, type_name, max_time, used_time, is_used);
restrList.add(list);
// System.out.println( "TYPE NAME = " + name + " Type Count = " +count);
}
rs.close();
stmt.close();
// System.out.println("Getting type list completed");
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
// System.exit(0);
}
return restrList;
}
// metode getTypeList atgriež sarakstu ar informāciju par visiem tipiem
public static List getTypeList() {
List<typeList> typeList = new ArrayList<>();
try {
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM PROCESS_TYPE ORDER BY Type_Count_Day DESC;");
while (rs.next()) {
int type_id = rs.getInt("PT_ID");
String name = rs.getString("TYPE_NAME");
int count_day = rs.getInt("TYPE_COUNT_DAY");
int count_all = rs.getInt("TYPE_COUNT_ALL");
String currDate = rs.getString("Type_Sys_date");
typeList type = new typeList(type_id, name, count_day, currDate, count_all);
typeList.add(type);
// System.out.println( "TYPE NAME = " + name + " Type Count = " +count);
}
rs.close();
stmt.close();
// System.out.println("Getting type list completed");
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
// System.exit(0);
}
return typeList;
}
// metode getProcessList atgriež sarakstu, ar visiem procesiem un to datiem, kas reģistrēti
// datubāzē
public static List getProcessList() {
List<processList> proctypeList = new ArrayList<>();
try {
Statement stmt = connect.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT * FROM PROCESS INNER JOIN PROCESS_TYPE WHERE PROCESS.TYPE_ID = PROCESS_TYPE.PT_ID ORDER BY PROCESS.Count_Day DESC;");
while (rs.next()) {
String name = rs.getString("NAME");
int count_day = rs.getInt("Count_Day");
String currDate = rs.getString("Sys_date");
String type = rs.getString("Type_Name");
int count_all = rs.getInt("Count_All");
processList proctype = new processList(name, count_day, currDate, type, count_all);
proctypeList.add(proctype);
// System.out.println( "NAME = " + name + " Count = " +count+ " Type:" + type);
}
stmt.close();
rs.close();
// System.out.println("Getting process type completed");
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
// System.exit(0);
}
return proctypeList;
}
public ArrayList<reservationInfo> getInfo() {
connect();
ArrayList<reservationInfo> list = new ArrayList<reservationInfo>();
try {
pstmt = con.prepareStatement(SQL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
reservationInfo r = new reservationInfo();
r.setPassport_num(rs.getString("passport_num"));
r.setPeople_num(rs.getInt("people_num"));
r.setProduct_id(rs.getInt("product_id"));
r.setReserve_date(rs.getString("reserve_date"));
r.setReserve_id(rs.getInt("reserve_id"));
r.setSeat(rs.getInt("seat"));
r.setAirline_name(rs.getString("airline_name"));
r.setName(rs.getString("name"));
r.setArrival_time(rs.getString("arrival_time"));
r.setArrival_date(rs.getString("arrival_date"));
r.setDeparture_time(rs.getString("departure_time"));
r.setDeparture_date(rs.getString("departure_date"));
r.setSeat(rs.getInt("seat"));
r.setSchedule_id(rs.getInt("schedule_id"));
r.setPeople_id(rs.getString("people_id"));
result++;
list.add(r);
}
this.setResult(result);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return list;
}
public ArrayList<rev> getRevId() {
connect();
ArrayList<rev> list = new ArrayList<rev>();
try {
pstmt = con.prepareStatement(SQL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
rev r = new rev();
r.setCost(rs.getInt("cost"));
r.setPassport_num(rs.getString("passport_num"));
r.setPeople_num(rs.getInt("people_num"));
r.setProduct_id(rs.getInt("product_id"));
r.setProduct_name(rs.getString("product_name"));
r.setReserve_date(rs.getString("reserve_date"));
r.setReserve_id(rs.getInt("reserve_id"));
r.setSeat(rs.getInt("seat"));
list.add(r);
result++;
}
this.setResult(result);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return list;
}
/**
* 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;
}
}
/**
* get a project by projectID
*
* @return a project
*/
public Project getProject(int ID) {
PreparedStatement stmt = null;
ResultSet rs = null;
Project project = null;
try {
String sql =
"select ProjectID, Title, RecordSperImage, "
+ "FirstYCoord, RecordHeight from Projects where ProjectID = ?";
stmt = db.getConnection().prepareStatement(sql);
stmt.setInt(1, ID);
rs = stmt.executeQuery();
while (rs.next()) {
Project newProject =
new Project(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getInt(5));
project = newProject;
}
} catch (SQLException e) {
System.out.println("Can't execute query");
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
System.out.println("Can't execute connect");
e.printStackTrace();
}
}
return project;
}
@Override
public ArrayList<Trago> GetAll() throws Exception {
ArrayList<Trago> colTragos = new ArrayList<Trago>();
try {
Connection con = Conexion.getConexion();
Statement sql = con.createStatement();
ResultSet cmd = sql.executeQuery("Select * from tragos;");
while (cmd.next()) {
Trago tr = new Trago();
tr.setDescripcion(cmd.getString("descripcion"));
tr.setId(cmd.getInt("id"));
tr.setEstado((cmd.getInt("estado") == 1) ? true : false);
tr.setGraduacion(cmd.getString("graduacion"));
tr.setModoPreparacion(cmd.getString("modopreparacion"));
tr.setNombre(cmd.getString("nombre"));
tr.setPositivo(cmd.getInt("positivo"));
tr.setNegativo(cmd.getInt("negativo"));
colTragos.add(tr);
}
cmd.close();
sql.close();
con.close();
for (Trago tr1 : colTragos) {
tr1.setReceta(ADReceta.getInstancia().GetAllByIdTrago(tr1.getId()));
}
return colTragos;
} catch (Exception ex) {
throw ex;
}
}
public void deleteOption(String modelName, String optionSetName, String option) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs;
rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql = "delete from options where name= '" + option + "' and option_id= " + opsid;
statement.executeUpdate(sql); // delete it using name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
private Operator fromResultSet(ResultSet rs) throws SQLException {
long id = rs.getLong(1);
if (rs.wasNull()) id = -1;
String name = rs.getString(2);
if (rs.wasNull()) name = "";
int binding = rs.getInt(3);
if (rs.wasNull()) binding = -1;
String notation = rs.getString(4);
if (rs.wasNull()) notation = "";
String symbol = rs.getString(5);
if (rs.wasNull()) symbol = "";
String symbol_intern = rs.getString(6);
if (rs.wasNull()) symbol_intern = "";
int arity = rs.getInt(7);
if (rs.wasNull()) arity = -1;
String description = rs.getString(8);
if (rs.wasNull()) description = "";
String comment = rs.getString(9);
if (rs.wasNull()) comment = "";
Operator operator =
new Operator(
id, name, binding, notation, symbol, symbol_intern, arity, description, comment);
return operator;
}
/**
* ******************************************************** Displays a table listing the team
* names and season records. Since teams have not yet played, all numbers are zero.
*
* @param conn Connection to the database ********************************************************
*/
public static void viewTeams(Connection conn) {
try {
// Create a Statement object.
Statement stmt = conn.createStatement();
// Create a string with a SELECT statement.
String sqlStatement = "SELECT * FROM Teams";
// Send the statement to the DBMS.
ResultSet result = stmt.executeQuery(sqlStatement);
System.out.printf("%-15s %10s %10s %10s\n", "Team Name", "Win", "Lose", "Tie");
// Display the contents of the result set.
// The result set will have 5 columns.
while (result.next()) {
System.out.printf(
"%-15s %10d %10d %10d\n",
result.getString("TeamName"),
result.getInt("Wins"),
result.getInt("Losses"),
result.getInt("Ties"));
}
} catch (Exception ex) {
System.out.println("ERROR: " + ex.getMessage());
}
}
stab(ResultSet k) {
super("PATIENT FOUND");
String[] chd = {
"Date",
"Patient ID",
"Name",
"Gender",
"Age",
"Weight",
"Address",
"Contact No.",
"Doctor Name",
"Symptoms",
"Dignosis",
"Fee: Rs.",
"Blood Group"
};
// DefaultTableModel dtm=new DefaultTableModel();
// jt.setModel(new DefaultTableModel(arr,chd));
jt = new JTable();
jsp = new JScrollPane(jt);
btn = new JButton("CLOSE");
btn.addActionListener(this);
int i = 0;
try {
while (k.next()) {
arr[i][0] = k.getString(1);
// System.out.println("ddddddddddddd"+arr[i][0]);
arr[i][1] = "" + k.getInt(2);
arr[i][2] = k.getString(3) + " " + k.getString(4) + " " + k.getString(5);
arr[i][3] = k.getString(6);
arr[i][4] = "" + k.getInt(7);
arr[i][5] = k.getString(8);
arr[i][6] = k.getString(9);
arr[i][7] = k.getString(10);
arr[i][8] = k.getString(11);
arr[i][9] = k.getString(12);
arr[i][10] = k.getString(13);
arr[i][11] = "" + k.getInt(14);
arr[i][12] = k.getString(15);
arr[i][13] = k.getString(16);
// dtm.insertRow(i,new Object[]{patient.rs.getString(1),
// patient.rs.getInt(2),patient.rs.getString(3)+patient.rs.getString(4)+patient.rs.getString(5),patient.rs.getString(6),patient.rs.getInt(7),patient.rs.getString(8),patient.rs.getString(9),patient.rs.getString(10),patient.rs.getString(11),patient.rs.getString(12),patient.rs.getString(13),patient.rs.getInt(14)});
i++;
}
} catch (Exception e12) {
System.out.println("" + e12);
}
jt.setModel(new DefaultTableModel(arr, chd));
Container con = getContentPane();
con.setLayout(null);
jsp.setBounds(20, 20, 1230, 600);
btn.setBounds(685, 630, 100, 30);
add(jsp);
add(btn);
setSize(1330, 800);
setVisible(true);
}
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;
}
@Test
public void testUpsertValuesWithExpression() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
PreparedStatement upsertStmt = conn.prepareStatement(upsert);
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
upsertStmt = conn.prepareStatement(upsert);
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
conn.close();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT i FROM IntKeyTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(-1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
}
/** @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 + "");
}
}
@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;
}
public List<ClientEventEntry> findLogEvents(
List<SearchConstraint> constraints, List<String> orderBy, int offset, int limit, Connection c)
throws SQLException {
StringBuffer sql =
new StringBuffer(
"select e.event_id, e.customer_id, e.user_id, e.event_time, e.description, e.has_log_file from dat_client_log_events e ");
appendWhere(sql, constraints, s_propToColumnMap);
appendOrderBy(sql, orderBy, "e.event_id desc", s_propToColumnMap);
appendLimits(sql, offset, limit);
Statement stmt = null;
ResultSet rs = null;
try {
stmt = c.createStatement();
rs = stmt.executeQuery(sql.toString());
List<ClientEventEntry> results = new ArrayList<ClientEventEntry>();
while (rs.next()) {
ClientEventEntry entry =
new ClientEventEntry(
rs.getInt(1),
rs.getInt(2),
rs.getInt(3),
resolveDate(rs.getTimestamp(4)),
rs.getString(5),
rs.getInt(6) != 0);
results.add(entry);
}
return results;
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(stmt);
}
}
/**
* {@inheritDoc} This method will get the distance data from a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public Optional<Distance> getDistanceBetween(Classroom classroom1, Classroom classroom2)
throws DataAccessException {
Distance distance = null;
try {
if (selectTwoRooms == null || selectTwoRooms.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns("id,distance")
.addWhereClause(
"(startRoomId=? AND endRoomId=?) OR (startRoomId=? AND endRoomId=?");
selectTwoRooms = connection.prepareStatement(builder.build());
}
selectTwoRooms.setInt(1, classroom1.id);
selectTwoRooms.setInt(4, classroom1.id);
selectTwoRooms.setInt(2, classroom2.id);
selectTwoRooms.setInt(3, classroom2.id);
ResultSet set = selectTwoRooms.executeQuery();
if (set.next()) {
distance = new Distance(set.getInt(1), classroom1, classroom2, set.getInt(2));
}
set.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
return Optional.ofNullable(distance);
}
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();
}
}
public ArrayList<staffEntity> getStaffList() {
connect();
ArrayList<staffEntity> list = new ArrayList<staffEntity>();
try {
pstmt = con.prepareStatement(SQL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
staffEntity s = new staffEntity();
s.setName(rs.getString("name"));
s.setCareer(rs.getInt("career"));
s.setGender(rs.getString("gender"));
s.setPhone(rs.getString("phone"));
s.setAge(rs.getInt("age"));
result++;
list.add(s);
}
this.setResult(result);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return list;
}
