public static List<DBRecord> toRecords(ResultSet rs) throws SQLException { List<DBRecord> records = new ArrayList<DBRecord>(); ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData(); int colNum = rsmd.getColumnCount(); while(rs.next()) { String[] datas = new String[colNum]; for (int i = 0; i < colNum; i++) datas[i] = rs.getString(i+1); records.add(new DBRecord(datas)); } rs.close(); conn.close(); return records; }
public static int toFlag(java.sql.ResultSetMetaData metaData, int column) throws SQLException { int flags = 0; if (metaData.isNullable(column) == 1) { flags |= 0001; } if (metaData.isSigned(column)) { flags |= 0020; } if (metaData.isAutoIncrement(column)) { flags |= 0200; } return flags; }
/** * Verify user in the database * @param user * @param conn * @return */ public boolean authentificationUser(String user, java.sql.Connection conn){ boolean res = false; int count = 0; try{ String demande = "SELECT user FROM users WHERE user='"+user+"'"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ count++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } if(count >= 1){res=true;} if(count < 1){res=false;} return res; }
/** * Verify password in the database for a given user * @param user * @param password * @param conn * @return */ public boolean authentificationPassword(String user, String password, java.sql.Connection conn){ boolean res = false; try{ String demande = "SELECT password FROM users WHERE user='"+user+"'"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ if(result.getString(i).equals(password)){ res = true; } } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return res; }
/** * Catch the display argument of the database for an application * @param name * @param serveur * @param grp * @param conn * @return */ public boolean[] DisplayappliuserBDDList(String name, String serveur,String grp,java.sql.Connection conn){ boolean[] user_name = new boolean[appliuserBDDcount(name,serveur,grp,conn)]; int p=0; try { String demande = "SELECT display FROM application WHERE "+grp+"=1"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ user_name[p]=result.getBoolean(i); p++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return user_name; }
/** * Catch the sound argument of the database for an application * @param name * @param serveur * @param grp * @param conn * @return */ public boolean[] SoundappliuserBDDList(String name, String serveur,String grp,java.sql.Connection conn){ boolean[] user_name = new boolean[appliuserBDDcount(name,serveur,grp,conn)]; int p=0; try { String demande = "SELECT sound FROM application WHERE "+grp+"=1"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ user_name[p]=result.getBoolean(i); p++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return user_name; }
/** * Catch the graphical argument of the database for an application * @param name * @param serveur * @param grp * @param conn * @return */ public boolean[] GraphappliuserBDDList(String name, String serveur,String grp,java.sql.Connection conn){ boolean[] user_name = new boolean[appliuserBDDcount(name,serveur,grp,conn)]; int p=0; try { String demande = "SELECT graph FROM application WHERE "+grp+"=1"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ user_name[p]=result.getBoolean(i); p++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return user_name; }
/** * Catch applications of the database for a given user * @param name * @param serveur * @param grp * @param conn * @return */ public String[] appliuserBDDList(String name, String serveur,String grp,java.sql.Connection conn){ String[] user_name = new String[appliuserBDDcount(name,serveur,grp,conn)]; int p=0; try { String demande = "SELECT appliname FROM application WHERE "+grp+"=1"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ user_name[p]=result.getString(i); p++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return user_name; }
/** * Count applications of the database for a given user * @param name * @param serveur * @param grp * @param conn * @return */ public int appliuserBDDcount(String name, String serveur, String grp,java.sql.Connection conn){ int numberuser = 0; try { String demande = "SELECT appliname FROM application WHERE "+grp+"=1"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ numberuser++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return numberuser; }
/** * Count groups of the database for a given user * @param username * @param serveur * @param conn * @return */ public int grpuserBDDcount(String username, String serveur,java.sql.Connection conn){ int numberuser = 0; try { String demande = "SELECT user FROM usergroup WHERE user='"+username+"'"; java.sql.PreparedStatement prepare = conn.prepareStatement(demande); java.sql.ResultSet result = prepare.executeQuery(); ResultSetMetaData resultMeta = (ResultSetMetaData) result.getMetaData(); while(result.next()){ for(int i = 1; i <= resultMeta.getColumnCount(); i++){ numberuser++; } } result.close(); prepare.close(); } catch (Exception e) { e.printStackTrace(); } return numberuser; }
public String buildCSV(String table, String fromDate, String toDate) throws SQLException { ArrayList<String> row = new ArrayList<String>(); rs = stmt.executeQuery("SELECT * from " + table + " WHERE Username = '" + GUI.username + "' AND Date BETWEEN '" + fromDate + "' AND '" + toDate + "';"); java.sql.ResultSetMetaData rsmd = rs.getMetaData(); int columnsNumber = rsmd.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnsNumber; i++) { String columnValue = rs.getString(i); row.add(columnValue); } row.add("\n"); } return Main.stringify(row); }
public String buildCSV(String table) throws SQLException { ArrayList<String> row = new ArrayList<String>(); rs = stmt.executeQuery("SELECT * from " + table + " WHERE Username = '" + GUI.username + "';"); java.sql.ResultSetMetaData rsmd = rs.getMetaData(); int columnsNumber = rsmd.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnsNumber; i++) { String columnValue = rs.getString(i); row.add(columnValue); } row.add("\n"); } return Main.stringify(row); }
private List pares(ResultSet rs, Object o) throws Exception { List list = new ArrayList(); Class cla = o.getClass(); // 获取所有属性 PropertyDescriptor[] pds = PropertyUtils.getPropertyDescriptors(cla); // 获取表头 ResultSetMetaData rsm = (ResultSetMetaData) rs.getMetaData(); while (rs.next()) { // 获取实例 Object obj = cla.newInstance(); for (PropertyDescriptor pd : pds) { String name = pd.getName(); Boolean find = false; for (int i = 0; i < rsm.getColumnCount(); i++) { if (name.toLowerCase().equals( rsm.getColumnName(i + 1).toLowerCase())) { find = true; break; } } if (find) { String val = rs.getString(name); if (val != null && !"".equals(val)) { BeanUtils.setProperty(obj, name, val); } } } list.add(obj); } return list; }
public static void printResult(ResultSet rs) throws SQLException { ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData(); int colNum = rsmd.getColumnCount(); while(rs.next()) { for (int i = 0; i < colNum; i++) System.out.printf(rs.getString(i+1) + " "); System.out.println(); } }
public DefaultTableModel reportTable(DefaultTableModel model) throws SQLException { String SQL = "Select `StartingKm`, `EndingKm`, `From`, `To`, `ReasonOfTrip`, `RegistryNumber`, `Date` from TripData WHERE Username ='" + GUI.username + "';"; rs = stmt.executeQuery(SQL); ResultSetMetaData rsm = (ResultSetMetaData) rs.getMetaData(); int nrCols = rsm.getColumnCount(); ArrayList<Object[]> t = new ArrayList<Object[]>(); while (rs.next()) { Object[] temp = new Object[7]; temp[0] = rs.getString("StartingKm"); temp[1] = rs.getString("EndingKm"); temp[2] = rs.getString("From"); temp[3] = rs.getString("To"); temp[4] = rs.getString("ReasonOfTrip"); temp[5] = rs.getString("RegistryNumber"); temp[6] = rs.getString("Date"); t.add(temp); } Object[][] data = new Object[t.size()][nrCols]; for (int i = 0; i < t.size(); i++) { for (int j = 0; j < 7; j++) { data[i][j] = t.get(i)[j]; } } Object[] columnNames = { "Start Km", "End Km", "From", "To", "Reason of trip", "Registration number", "Date" }; model.setDataVector(data, columnNames); return model; }
public DefaultTableModel reportTable(DefaultTableModel model, String fromDate, String toDate) throws SQLException { String SQL = "Select `StartingKm`, `EndingKm`, `From`, `To`, `ReasonOfTrip`, `RegistryNumber`, `Date` from TripData WHERE Username ='" + GUI.username + "' AND Date BETWEEN '" + fromDate + "' AND '" + toDate + "';"; rs = stmt.executeQuery(SQL); ResultSetMetaData rsm = (ResultSetMetaData) rs.getMetaData(); int nrCols = rsm.getColumnCount(); ArrayList<Object[]> t = new ArrayList<Object[]>(); while (rs.next()) { Object[] temp = new Object[7]; temp[0] = rs.getString("StartingKm"); temp[1] = rs.getString("EndingKm"); temp[2] = rs.getString("From"); temp[3] = rs.getString("To"); temp[4] = rs.getString("ReasonOfTrip"); temp[5] = rs.getString("RegistryNumber"); temp[6] = rs.getString("Date"); t.add(temp); } Object[][] data = new Object[t.size()][nrCols]; for (int i = 0; i < t.size(); i++) { for (int j = 0; j < 7; j++) { data[i][j] = t.get(i)[j]; } } Object[] columnNames = { "Start Km", "End Km", "From", "To", "Reason of trip", "Registration number", "Date" }; model.setDataVector(data, columnNames); return model; }