static void attributeMean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
Statement meanQuery = con.createStatement();
ResultSet mean = meanQuery.executeQuery("select AVG(sal) from employ where sal IS NOT NULL");
if (mean.next()) {
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + mean.getInt(1) + ")");
}
/*fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal,AVG(sal))");*/
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println("Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
@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;
}
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);
}
}
}
public Vector<Message> getInboxMessages(String username, int type) {
if (type == 4) username = "******";
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE toName = \""
+ username
+ "\" AND messageType = "
+ type
+ " ORDER BY timeStamp DESC");
Vector<Message> inbox = new Vector<Message>();
try {
while (rs.next()) {
inbox.add(
new Message(
rs.getString("fromName"),
username,
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
}
return inbox;
}
/**
* Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
* error occured to the database connection.
*
* @param name the instructor's user name
* @return a vector containing the list of courses
* @throws InvalidDBRequestException
*/
public Vector getCourseList(String name) throws InvalidDBRequestException {
Vector courseList = new Vector();
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
// get the course list
rs =
stmt.executeQuery(
"select course_num, course_name from course where instructor = '"
+ name
+ "' order by course_num");
while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
return courseList;
}
public static String printView(ResultSet rs) throws Exception {
ResultSetMetaData rsMetaData = rs.getMetaData();
int numCols = rsMetaData.getColumnCount();
String[] headers = new String[numCols];
OpenType[] allTypes = new OpenType[numCols];
Vector[] values = new Vector[numCols];
Object[] allValues = new Object[numCols];
String ret = "";
for (int i = 0; i < numCols; i++) {
if (i == 0) {
ret = rsMetaData.getColumnName(i + 1);
} else {
ret = ret + "," + rsMetaData.getColumnName(i + 1);
}
}
ret = ret + "\n";
while (rs.next()) {
for (int i = 0; i < numCols; i++) {
if (i == 0) {
ret = ret + rs.getString(i + 1);
} else {
ret = ret + ", " + rs.getString(i + 1);
}
}
ret = ret + "\n";
}
return ret;
}
public MetadataBlob[] select(String selectString) {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
ResultSet rs = stmt.executeQuery();
List<MetadataBlob> list = new ArrayList<MetadataBlob>();
while (rs.next()) {
MetadataBlob f =
new MetadataBlob(
rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
list.add(f);
}
return list.toArray(new MetadataBlob[0]);
} catch (SQLException sqe) {
System.err.println("Error selecting: " + selectString);
sqe.printStackTrace();
return null;
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException sqe2) {
sqe2.printStackTrace();
}
}
}
@Override
public List<Ordine> listaOrdini() throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
List<Ordine> ordini = null;
Ordine ordine = null;
try {
String str =
"select ordini.codice as codice,stato,data,cliente,id "
+ "from ordini left outer join clienti on ordini.cliente=clienti.codice";
statement = connection.prepareStatement(str);
ResultSet result = statement.executeQuery();
ordini = new ArrayList<Ordine>();
while (result.next()) {
ordine = new Ordine();
ClienteDAOImpl cliente = new ClienteDAOImpl();
ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
ordine.setCodiceOrdine(result.getString("codice"));
ordine.setData(new java.util.Date(result.getDate("data").getDate()));
ordine.setStato(result.getString("stato"));
ordine.setId(result.getInt("id"));
ordini.add(ordine);
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return ordini;
}
@Override
public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
Ordine o = null;
List<Ordine> lo = new LinkedList<Ordine>();
try {
String str = "select codice,data,stato,id from ordini where cliente=?";
statement = connection.prepareStatement(str);
statement.setInt(1, cliente.getId());
ResultSet result = statement.executeQuery();
while (result.next()) {
o = new Ordine();
o.setCliente(cliente);
o.setCodiceOrdine(result.getString("codice"));
o.setData(result.getDate("data"));
o.setStato(result.getString("stato"));
o.setId(result.getInt("id"));
lo.add(o);
// da completare per restituire ordine completo di righe
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return lo;
}
@Override
public void actionPerformed(ActionEvent axnEve) {
Object obj = axnEve.getSource();
if (obj == replyBut) {
try {
if (Home.composeDial != null && Home.composeDial.isShowing()) {
} else {
Home.composeDial =
new ComposeMailDialog(
msgID, workingSet.getString("mail_addresses"), workingSet.getString("subject"));
}
} catch (SQLException sqlExc) {
sqlExc.printStackTrace();
}
} else if (obj == forwardBut) {
try {
if (Home.composeDial != null && Home.composeDial.isShowing()) {
} else {
Home.composeDial =
new ComposeMailDialog(
msgID,
workingSet.getString("subject") + "\n\n" + workingSet.getString("content"));
}
} catch (SQLException sqlExc) {
sqlExc.printStackTrace();
}
}
}
// 일사용량(7일)
public ArrayList<ArrayList<String>> get7dayUsage(String umdong) {
ArrayList<ArrayList<String>> datas = new ArrayList<ArrayList<String>>();
String sql =
"select sum(CONSUMED), sum(PREDICTED) from CONSUMPTION where CODE in(select CODE from USER where UMDONG = \'"
+ umdong
+ "\') and DATE between '2015-02-22' and '2015-02-28' group by DATE order by DATE DESC;";
try {
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
ArrayList<String> usage = new ArrayList<String>();
usage.add(rs.getString("sum(CONSUMED)"));
usage.add(rs.getString("sum(PREDICTED)"));
datas.add(usage);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return datas;
}
public void doSQL() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
/**
* Gets elements depending on the format specified.
*
* @param format The format specification of the elements to be returned.
* @return a List of all elements in MetaDB with the specified format.
*/
public List<Element> getElementList(String format) {
List<Element> elementList = new ArrayList<Element>();
Connection conn = Conn.initialize();
if (conn != null) {
try {
PreparedStatement getElementListQuery = conn.prepareStatement(GET_ELEMENT_LIST_BY_FORMAT);
getElementListQuery.setString(1, format);
ResultSet getElementListQueryResult = getElementListQuery.executeQuery();
while (getElementListQueryResult.next())
elementList.add(
new Element(
getElementListQueryResult.getString(Global.ELEMENT),
getElementListQueryResult.getString(Global.ELEMENT_FORMAT)));
getElementListQueryResult.close();
getElementListQuery.close();
conn.close();
} catch (Exception e) {
MetaDbHelper.logEvent(e);
}
}
return elementList;
}
static void specificAttributeMean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
fill = con.createStatement();
fill.executeUpdate(
"UPDATE employ a set sal = (select AVG(sal) from employ where sal IS NOT NULL and grade = (select grade from employ b where sal IS NULL and a.grade=b.grade and ROWNUM <= 1)) where sal IS NULL");
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static void duration() {
connectDb();
String sql = "select min(time), max(time), count(*) from " + tablename + ";";
try {
ResultSet rs = query.executeQuery(sql);
rs.next();
int numCols = rs.getMetaData().getColumnCount();
String minTime = rs.getString(1);
String maxTime = rs.getString(2);
String numPackets = rs.getString(3);
System.out.println(
"Experiment "
+ tablename
+ "\n\tfrom: "
+ minTime
+ "\n\tto: "
+ maxTime
+ "\n\tpackets: "
+ numPackets);
} catch (SQLException e) {
System.out.println("SQL Exception: " + e);
System.exit(1);
}
}
/**
* Transforma os dados obtidos atraves de uma consulta a tabela de glosas do prestador em um
* objeto do tipo GlosaPrestador
*
* @param rset - um ResultSet contendo o resultado da consulta a tabela de glosas do prestador
* @return um objeto do tipo GlosaPrestador
*/
private static final GlosaPrestador montaGlosaPrestador(ResultSet rset) throws SQLException {
String numAtendimento = null;
String numDocOrigem = null;
String strLoteNota = "";
String strCodBeneficiario = "";
String strNomeBeneficiario = "";
Timestamp dtGlosa = null;
String strMotivo = "";
String strDescricao = "";
String strObservacoes = "";
numAtendimento = rset.getString("rej_ate_num_atendimento");
dtGlosa = rset.getTimestamp("rej_data_hora");
numDocOrigem = rset.getString("nat_numero_documento_origem");
strLoteNota = rset.getString("Lote_Nota");
strCodBeneficiario = rset.getString("Beneficiario");
strNomeBeneficiario = rset.getString("Nome");
strDescricao = rset.getString("Descricao");
strMotivo = (rset.getString("rej_motivo") == null) ? "" : rset.getString("rej_motivo");
strObservacoes =
(rset.getString("ate_observacoes") == null) ? "" : rset.getString("ate_observacoes");
return (new GlosaPrestador(
numAtendimento,
numDocOrigem,
strLoteNota,
strCodBeneficiario,
strNomeBeneficiario,
dtGlosa,
strMotivo,
strDescricao,
strObservacoes));
} // montaGlosaPrestador()
public void setProperties(ResultSet SYSTEM_CONFIG) throws SQLException {
try {
mailHost = SYSTEM_CONFIG.getString("MailServer");
Source = SYSTEM_CONFIG.getString("SenderMailNameProgram");
WebMaster = SYSTEM_CONFIG.getString("MailWebMaster");
/* Legge la lista amministratori dal file di proprieta */
StringTokenizer myStrTok = getTokenizer(SYSTEM_CONFIG.getString("CarbonCopyMailRecipients"));
StringTokenizer myStrTokName =
getTokenizer(SYSTEM_CONFIG.getString("NameServiceAdministratorsList"));
/* crea un array per la lista degli amministratori */
MailServiceAdministratorsList = new String[myStrTok.countTokens()];
int i = 0;
while (myStrTok.hasMoreTokens()) {
MailServiceAdministratorsList[i++] = myStrTok.nextToken();
}
} catch (SQLException sqle) {
throw sqle;
}
}
// 根据推荐的movie的ID，获得movie的详细信息
public ArrayList<MovieInfo> getMovieByMovieId(List<RecommendedItem> recommendations) {
ArrayList<MovieInfo> movieList = new ArrayList<MovieInfo>();
try {
String sql = "";
conn = ConnectToMySQL.getConnection();
for (int i = 0; i < recommendations.size(); i++) {
sql =
"select m.name,m.published_year,m.type from movies m where m.id="
+ recommendations.get(i).getItemID()
+ "";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
MovieInfo movieInfo = new MovieInfo();
movieInfo.setName(rs.getString(1));
movieInfo.setPublishedYear(rs.getString(2));
movieInfo.setType(rs.getString(3));
movieInfo.setPreference(recommendations.get(i).getValue());
movieList.add(movieInfo);
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
closeAll();
}
return movieList;
}
public static List<RssFeed> loadFeeds() {
loadDriver();
Connection conn = null;
ResultSet rs = null;
List<RssFeed> feeds = new ArrayList<>();
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
rs = conn.createStatement().executeQuery("select id, title, url from rssFeed");
while (rs.next()) {
String title = rs.getString("title");
String url = rs.getString("url");
RssFeed rssFeed = new RssFeed(title, url);
rssFeed.id = rssFeed.link.hashCode();
feeds.add(rssFeed);
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close(rs);
close(conn);
}
return feeds;
}
// 根据用户的id，获得用户的所有电影
public ArrayList<MovieInfo> getMovieByUserId(long userID) {
ArrayList<MovieInfo> movieList = new ArrayList<MovieInfo>();
try {
String sql =
"select m.name,m.published_year,m.type,mp.preference from movie_preferences mp,movies m where mp.movieID=m.id and mp.userID="
+ userID
+ " order by preference desc";
conn = ConnectToMySQL.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
MovieInfo movieInfo = new MovieInfo();
movieInfo.setName(rs.getString(1));
movieInfo.setPublishedYear(rs.getString(2));
movieInfo.setType(rs.getString(3));
movieInfo.setPreference(rs.getInt(4));
movieList.add(movieInfo);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
closeAll();
}
return movieList;
}
private void ListValueChanged(
javax.swing.event.ListSelectionEvent evt) { // GEN-FIRST:event_ListValueChanged
// TODO add your handling code here:
// String part=partno.getText();
try {
String sql =
"SELECT TYPE,ITEM_NAME,QUANTITY,MRP FROM MOTORS WHERE ITEM_NAME='"
+ List.getSelectedValue()
+ "'";
Class.forName("com.mysql.jdbc.Driver");
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
partno.setText(rs.getString("TYPE"));
name.setText(rs.getString("ITEM_NAME"));
qty.setText(rs.getString("QUANTITY"));
rate.setText(rs.getString("MRP"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e.toString());
}
} // GEN-LAST:event_ListValueChanged
public ArrayList<Empleado> listarEmpleado() {
ArrayList<Empleado> emple = new ArrayList<Empleado>();
try {
cn = Conexion.realizarConexion();
st = cn.createStatement();
String sql = "select * from empleado";
rs = st.executeQuery(sql);
while (rs.next()) {
emple.add(
new Empleado(
rs.getString(1),
rs.getString(2),
rs.getString(3),
rs.getString(4),
rs.getString(5),
rs.getString(6),
rs.getString(7),
rs.getString(8)));
}
} catch (ClassNotFoundException ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
} catch (SQLException ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
} finally {
try {
rs.close();
st.close();
cn.close();
} catch (Exception ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
}
}
return emple;
}
/**
* Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
* student's directory which relates to the course. Caution: vizualisation file will be deleted
* eventhough it also relates to another course if the student is also registered to that course.
* (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
* error occured during deletion, or other exception occured.
*
* @param username student's user name
* @param courseID course id (course number + instructor name)
* @throws InvalidDBRequestException
*/
public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
int count = 0;
// check if student registered to the course
rs =
stmt.executeQuery(
"select * from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (!rs.next())
throw new InvalidDBRequestException("Student is not registered to the course");
// remove student from the course
count =
stmt.executeUpdate(
"delete from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");
// delete the quiz visualization files
rs =
stmt.executeQuery(
"select distinct unique_id, s.test_name from scores s, courseTest t "
+ "where s.test_name = t.test_name "
+ "and course_id = '"
+ courseID
+ "' "
+ "and user_login = '******'");
while (rs.next()) {
deleteVisualization(rs.getString(1), username, rs.getString(2));
count =
stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addstudent: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
public static ArrayList<String> getPasser_zone(String zone) throws SQLException {
ArrayList<String> passerArray = new ArrayList<String>();
Connection conn = DBConn.GetConnection();
PreparedStatement st =
conn.prepareStatement(
"SELECT * FROM cs_conversion LEFT JOIN cs_user ON cs_user.username=cs_conversion.username WHERE cs_conversion.isstart=? AND cs_user.zone=? ");
st.setInt(1, 0);
st.setString(2, zone);
ResultSet rs = st.executeQuery();
while (rs.next()) {
if (rs.getString("username").length() != 0) {
if (passerArray.size() == 0) {
passerArray.add(rs.getString("username"));
} else {
if (!passerArray.contains(rs.getString("username"))) {
passerArray.add(rs.getString("username"));
}
}
}
}
rs.close();
st.close();
conn.close();
return passerArray;
}
protected void executeQuery(Statement stmt, String q) throws SQLException {
q = q.replace("$PREFIX", getPrefix());
LOG.info(" Executing " + q);
ResultSet rs = stmt.executeQuery(q);
StringBuilder header = new StringBuilder();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
if (i > 1) {
header.append("|");
}
header.append(rs.getMetaData().getColumnName(i));
}
LOG.info(header);
int seq = 0;
while (true) {
boolean valid = rs.next();
if (!valid) break;
seq++;
StringBuilder line = new StringBuilder();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
if (i > 1) {
line.append("|");
}
line.append(rs.getString(i));
}
LOG.info(seq + ":" + line);
}
}
/**
* Get waiting User name;
*
* @return An waiter ArrayList
*/
public static ArrayList<String> getWaiter() throws SQLException {
ArrayList<String> waiterArray = new ArrayList<String>();
Connection conn = DBConn.GetConnection();
PreparedStatement st = conn.prepareStatement("SELECT * FROM cs_conversion where isstart=?");
st.setInt(1, 1);
ResultSet rs = st.executeQuery();
while (rs.next()) {
if (rs.getString("username").length() != 0) {
if (waiterArray.size() == 0) {
waiterArray.add(rs.getString("username"));
} else {
for (int i = 0; i < waiterArray.size(); i++) {
if (!rs.getString("username").equals(waiterArray.get(i).toString())) {
waiterArray.add(rs.getString("username"));
}
}
}
}
}
rs.close();
st.close();
conn.close();
return waiterArray;
}
@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());
}
}
}
/**
* Returns the messages which ID starts with the given ID
*
* @param ID message ID
* @return text and ID of the message
*/
public synchronized Hashtable getMessagesWithId(String ID) {
// System.out.println("getMessagesWithId:"+ID);
Hashtable res = new Hashtable();
Enumeration enm = dict.keys();
while (enm.hasMoreElements()) {
String key = "" + enm.nextElement();
if (key.startsWith(ID)) res.put(key, dict.get(key));
}
if (res.size() == 0) {
Connection connection = null;
try {
connection = getMessageDBConnection();
PreparedStatement proc =
connection.prepareStatement("SELECT tkey,txt FROM messages_texts WHERE tkey like ?");
proc.setString(1, ID + "%");
ResultSet rs = proc.executeQuery();
while (rs.next()) {
res.put(rs.getString(1), rs.getString(2));
}
connection.close();
} catch (Exception e) {
/*not message connection*/
e.printStackTrace();
}
}
return res;
}
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);
}
}
static void globalConstant() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = " + GlobalConstant + " where sal IS NULL");
// fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + GlobalConstant + ")");
// System.out.println("Entered..!");
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println("Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
