/**
* Calcula si un alumno esta expulsado. Se sabe si un alumno está expulsado por el número de dias
* escolares entre la fecha de expulsión y la de parte. Un día escolar es el numero de partes con
* fecha distinta entre dos fechas.
*
* @param anoEscolar Año escola
* @param alumno Alumno
* @param fecha Fecha en la que se quiere saber si el alumno estça expulsado
* @return true si está expulsado en la fecha del parte, false si no lo está
*/
public static Boolean isAlumnoExpulsado(Alumno alumno, GregorianCalendar fecha) {
boolean ret = false;
try {
// Tenemos que ver si hay expulsiones para ese alumno
PreparedStatement st =
(PreparedStatement)
MaimonidesApp.getApplication()
.getConector()
.getConexion()
.prepareStatement("SELECT * FROM expulsiones WHERE alumno_id=? AND fecha<=?");
st.setInt(1, alumno.getId());
st.setDate(2, new java.sql.Date(fecha.getTimeInMillis()));
ResultSet res = st.executeQuery();
while (res.next() && !ret) {
// Si hay expulsiones tenemos que ver si son válidas
// Para eso tenemos que contar los partes desde la fecha de expulsión hasta ahora
GregorianCalendar fechaExpulsion = Fechas.toGregorianCalendar(res.getDate("fecha"));
if (fechaExpulsion != null) {
// Como la fecha de expulsión esta incluida tenemos que quitarle un día
fechaExpulsion.add(GregorianCalendar.DATE, -1);
// Y vemos la diferencia en días
int dias = res.getInt("dias");
long diasTranscurridos =
Fechas.getDiferenciaTiempoEn(fecha, fechaExpulsion, GregorianCalendar.DATE);
ret = diasTranscurridos <= dias;
}
}
Obj.cerrar(st, res);
} catch (SQLException ex) {
Logger.getLogger(Expulsion.class.getName()).log(Level.SEVERE, null, ex);
}
return ret;
}
/**
* Checks if the databasePassword is the same as the provided password.
*
* @param login
* @param password
* @return true / false
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static boolean checkPassword(String login, String password)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
String sql = "SELECT password FROM users WHERE login = ? ";
String serverPassword = "";
Boolean isValid = false;
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setString(1, login);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
serverPassword = rs.getString("password");
if (password.equals(serverPassword)) {
isValid = true;
}
}
ps.close();
connection.close();
return isValid;
}
public static int getNumeroExpulsiones(Alumno alumno, GregorianCalendar fecha) {
int ret = 0;
PreparedStatement st = null;
ResultSet res = null;
try {
// Tenemos que ver si hay expulsiones para ese alumno
st =
(PreparedStatement)
MaimonidesApp.getApplication()
.getConector()
.getConexion()
.prepareStatement(
"SELECT count(*) FROM expulsiones WHERE alumno_id=? AND fecha<=?");
st.setInt(1, alumno.getId());
st.setDate(2, new java.sql.Date(fecha.getTimeInMillis()));
res = st.executeQuery();
if (res.next()) {
ret = res.getInt(1);
}
} catch (SQLException ex) {
Logger.getLogger(Expulsion.class.getName()).log(Level.SEVERE, null, ex);
}
Obj.cerrar(st, res);
return ret;
}
public boolean select(String email) {
con = (Connection) DBConnector.getConnection();
try {
String sql = "SELECT mail_address FROM user WHERE mail_address = ?";
ps = (PreparedStatement) con.prepareStatement(sql);
ps.setString(1, email);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
rs.getString(1);
result = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
/**
* @Description: 在源库中查找最新的期号
*
* @author songjia
* @date Feb 15, 2016 3:29:13 PM
* @return
*/
public SrcDataBean getRecordByIssueNumber(String issueNumber) {
Connection srcConn = ConnectSrcDb.getSrcConnection();
PreparedStatement pstmt = null;
SrcDataBean srcDataBean = null;
String sql =
"SELECT issue_number,no1,no2,no3 FROM "
+ App.srcNumberTbName
+ " WHERE ISSUE_NUMBER = '"
+ issueNumber
+ "'";
try {
pstmt = (PreparedStatement) srcConn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
srcDataBean = new SrcDataBean();
srcDataBean.setIssueId(rs.getString(1));
srcDataBean.setNo1(rs.getInt(2));
srcDataBean.setNo2(rs.getInt(3));
srcDataBean.setNo3(rs.getInt(4));
}
if (rs != null && !rs.isClosed()) {
rs.close();
}
} catch (SQLException e) {
LogUtil.error(e.getMessage(), "sima");
}
return srcDataBean;
}
public JasperPrint relatorioModalidade(String modalidade) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioModalidade.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
modalidade = (new StringBuilder()).append("= '").append(modalidade).append("'").toString();
String query =
(new StringBuilder())
.append(
"SELECT p.nome,a.matricula,m.nome "
+ "AS modalidade FROM pessoas p INNER JOIN alunos a ON p.idPessoa = a.idPessoa"
+ " INNER JOIN adesoes ad ON a.matricula = ad.matricula INNER JOIN planos pl on "
+ "ad.codPlano = pl.`codPlano` INNER JOIN modalidades m ON pl.codModalidade = "
+ "m.codModalidade WHERE m.nome ")
.append(modalidade)
.append(" ")
.append(" ORDER BY p.nome")
.toString();
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
@Override
protected ArrayList<Usuario> doInBackground() {
ArrayList<Usuario> ret = new ArrayList<Usuario>();
if (!Beans.isDesignTime()) {
try {
PreparedStatement st =
(PreparedStatement)
MaimonidesApp.getApplication()
.getConector()
.getConexion()
.prepareStatement(
"SELECT * FROM usuarios WHERE fbaja IS NULL ORDER BY nombre");
ResultSet res = st.executeQuery();
while (res.next()) {
Usuario p = new Usuario();
try {
p.cargarDesdeResultSet(res);
ret.add(p);
} catch (SQLException ex) {
Logger.getLogger(PanelUsuarios.class.getName()).log(Level.SEVERE, null, ex);
} catch (Exception ex) {
Logger.getLogger(PanelUsuarios.class.getName()).log(Level.SEVERE, null, ex);
}
}
Obj.cerrar(st, res);
} catch (SQLException ex) {
Logger.getLogger(PanelUsuarios.class.getName()).log(Level.SEVERE, null, ex);
}
}
return ret; // return your result
}
public List<Funcionario> list() {
String sql = "SELECT * FROM FUNCIONARIO";
Funcionario funcionario = null;
try {
PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
List<Funcionario> funcionarios = new ArrayList<Funcionario>();
while (rs.next()) {
funcionario = new Funcionario();
funcionario.setNumFuncionario(rs.getInt("id_funcionario"));
funcionario.setNomeFuncionario(rs.getString("nome_funcionario"));
funcionario.setComissao(rs.getDouble("comissao"));
funcionario.setEspecialidade(rs.getString("especialidade"));
funcionarios.add(funcionario);
}
stmt.close();
rs.close();
return funcionarios;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public List<DamageItem> findAll() throws Exception {
try {
con = db.getConnect();
String sql = "select * from damaged";
PreparedStatement statement = (PreparedStatement) con.prepareStatement(sql);
ResultSet result = statement.executeQuery();
List<DamageItem> dl = new ArrayList<>();
while (result.next()) {
DamageItem di = new DamageItem();
di.setId(result.getInt("id"));
di.setItem(result.getString("item"));
di.setQty(result.getInt("qty"));
di.setReason(result.getString("reason"));
di.setDate(result.getString("date"));
di.setStaffstamp(result.getString("staffStamp"));
dl.add(di);
}
return dl;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return null;
}
/**
* Get a customer by id
*
* @param id customer id
* @return a customer
*/
public Customer getACustomer(int id) {
String sql = "Select * From Customer where cid = ?";
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setInt(1, id);
ResultSet rs = statement.executeQuery();
if (rs.next()) {
Customer customer = new Customer();
customer.setEmail(rs.getString("email"));
customer.setFirstName(rs.getString("firstName"));
customer.setLastName(rs.getString("lastName"));
customer.setId(rs.getInt("cid"));
if (statement != null) statement.close();
return customer;
} else {
return null;
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Error at get a customer");
return null;
}
}
public void setAlumno(Alumno alumno) {
this.alumno = null;
lInfoAlumno.setText("");
// De primeras ponemos todos los apoyos a false
for (int hora = 0; hora < 6; hora++) {
for (int dia = 0; dia < 5; dia++) {
tabla.getModel().setValueAt(false, hora, dia + 1);
}
}
// Sacamos todos sus apoyos
if (alumno != null) {
lInfoAlumno.setText(alumno.getNombreFormateado());
try {
String sql =
"SELECT distinct h.dia,h.hora FROM horarios_ AS h JOIN apoyos_alumnos AS aa ON aa.horario_id=h.id WHERE aa.alumno_id=? ";
PreparedStatement st =
(PreparedStatement)
MaimonidesApp.getApplication().getConector().getConexion().prepareStatement(sql);
st.setInt(1, alumno.getId());
ResultSet res = st.executeQuery();
while (res.next()) {
tabla.getModel().setValueAt(true, res.getInt("hora") - 1, res.getInt("dia"));
}
Obj.cerrar(st, res);
} catch (SQLException ex) {
Logger.getLogger(PanelApoyos.class.getName()).log(Level.SEVERE, null, ex);
}
}
this.alumno = alumno;
}
/** * @Description: 在源库中查找最新的预测计划，并保证期号在预测范围内，否则报错 * * @author [email protected] * @date Feb 15, 2016 3:29:13 PM * @return */ public Fast3SiMa getSiMaYuceRecordByIssueCode(String issueNumber) { Connection srcConn = ConnectSrcDb.getSrcConnection(); PreparedStatement pstmt = null; Fast3SiMa data = null; String sql = "SELECT ID,YUCE_ISSUE_START,YUCE_ISSUE_STOP,DROWN_PLAN,DROWN_CYCLE,DROWN_ISSUE_NUMBER FROM " + App.simaTbName + " WHERE " + issueNumber + " BETWEEN YUCE_ISSUE_START AND YUCE_ISSUE_STOP ORDER BY ID DESC LIMIT 1"; try { pstmt = (PreparedStatement) srcConn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { data = new Fast3SiMa(); data.setId(rs.getInt(1)); data.setYuceIssueStart(rs.getString(2)); data.setYuceIssueStop(rs.getString(3)); data.setDrownPlan(rs.getString(4)); data.setDrownCycle(rs.getInt(5)); data.setDrownIssueNumber(rs.getString(6)); } if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { LogUtil.error(e.getMessage(), "sima"); } return data; }
/**
* retrieve food items from database
*
* @param type beverage or food , or null for combination
* @return menu - list of items
*/
public ArrayList<Item> getMenu(String type) {
String sql = "SELECT itemName, price, description FROM Menu";
if (type != null) {
sql += " WHERE type=?";
}
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
if (type != null) {
statement.setString(0, type);
}
ResultSet rs = statement.executeQuery();
ArrayList<Item> menu = new ArrayList<Item>(); // maybe food menu if there is beverage menu
while (rs.next()) {
String itemName = rs.getString("itemName");
double price = rs.getDouble("price");
String desc = rs.getString("description");
Item i = new Item(itemName, price, desc);
menu.add(i);
}
rs.close();
statement.close();
return menu;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String username = request.getParameter("contacts");
String username_check = "";
try {
Connection conn = new Database().dbconnection();
PreparedStatement ps =
(PreparedStatement) conn.prepareStatement("select username from Users where username= ?");
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
username_check = rs.getString("username");
}
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (username_check.equals(username) && username != "" && username != null) {
String sender = request.getParameter("username");
RequestDispatcher friendRequest =
request.getRequestDispatcher(
"/jsp/FriendRequestOut.jsp?sender=" + sender + "&reciver=" + username);
friendRequest.forward(request, response);
} else out.println("User is not registered");
}
public JasperPrint relatorioPlanosVencidos() throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioPlanosVencidos.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
String query = "";
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
private String[] getMarketNames() {
ArrayList<String> diffMarket = new ArrayList<String>();
String query = DISTINCT_MARKET_NAME_QUERY;
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(query);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
diffMarket.add(rs.getString("market_name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return diffMarket.toArray(new String[diffMarket.size()]);
}
public Expulsion(int id) throws Exception {
PreparedStatement st =
(PreparedStatement)
MaimonidesApp.getApplication()
.getConector()
.getConexion()
.prepareStatement("SELECT * FROM expulsiones WHERE id=? ");
st.setInt(1, id);
ResultSet res = st.executeQuery();
if (res.next()) {
cargarDesdeResultSet(res);
} else {
throw new InvalidParameterException("No existe ninguna expulsion con ID " + id);
}
Obj.cerrar(st, res);
}
public JasperPrint relatorioAniversariantes(int mes) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioAniversariantes.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
String query =
"SELECT a.matricula,p.nome,p.nascimento,p.celular,(year(curdate()) - year(nascimento)) as anos FROM pessoas p inner join alunos a on p.idPessoa = a.idPessoa WHERE month(p.nascimento) = "
+ mes;
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
/** * @Description: 根据期号在源数据库中获取记录 * * @author [email protected] * @date Feb 15, 2016 4:24:40 PM * @param issueId * @return */ public List<SrcDataBean> getYucePool(String issueCode) { Connection srcConn = ConnectSrcDb.getSrcConnection(); List<SrcDataBean> srcList = new ArrayList<SrcDataBean>(); PreparedStatement pstmt = null; String startDay = DateUtil.getNextNDay(-7); // System.out.println("开始日期"+startDay); String endDay = DateUtil.getNextNDay(-1); // System.out.println("结束日期"+endDay); String code1 = DateUtil.getNextIssueCodeByCurrentIssue(issueCode); String code2 = DateUtil.getNextIssueCodeByCurrentIssue(code1); String code3 = DateUtil.getNextIssueCodeByCurrentIssue(code2); String sql = "SELECT issue_number,no1,no2,no3 FROM " + App.srcNumberTbName + " WHERE substr(ISSUE_NUMBER,1,6) between '" + startDay + "' and '" + endDay + "' AND substr(ISSUE_NUMBER,8) IN ('" + code1 + "','" + code2 + "','" + code3 + "') ORDER BY ISSUE_NUMBER DESC"; // System.out.println(sql); try { pstmt = (PreparedStatement) srcConn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { SrcDataBean srcDataBean = new SrcDataBean(); srcDataBean.setIssueId(rs.getString(1)); srcDataBean.setNo1(rs.getInt(2)); srcDataBean.setNo2(rs.getInt(3)); srcDataBean.setNo3(rs.getInt(4)); srcList.add(srcDataBean); } if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); LogUtil.error(e.getMessage(), "sima/"); } // System.out.println(srcList.size()); return srcList; }
public String[] NamaDokter(String spesialis, int total) throws SQLException {
try {
String[] nama = new String[total];
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(ambilNamaDokter);
ps.setString(1, spesialis);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
nama[rs.getRow() - 1] = rs.getString("nm_dokter");
}
rs.close();
ps.close();
return nama;
} catch (SQLException se) {
JOptionPane.showMessageDialog(
null, se.getMessage(), "String[] Nama Dokter Gagal!", JOptionPane.ERROR_MESSAGE);
return null;
}
}
public String tampilIdByNama(String nama) throws SQLException {
try {
String id = null;
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(tampilIdByNama);
ps.setString(1, nama);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
id = rs.getString("id_dokter");
}
rs.close();
ps.close();
return id;
} catch (SQLException se) {
JOptionPane.showMessageDialog(
null, se.getMessage(), "Tampil ID By Nama Dokter Gagal!", JOptionPane.ERROR_MESSAGE);
return null;
}
}
public String[] getIds(String marketName, int limitNum) {
ArrayList<String> ids = new ArrayList<String>();
try {
PreparedStatement pstmt;
pstmt = (PreparedStatement) conn.prepareStatement(GET_IDS_QUERY);
pstmt.setString(1, marketName);
pstmt.setInt(2, limitNum);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
ids.add(String.valueOf(rs.getInt("p_id")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return ids.toArray(new String[ids.size()]);
}
/**
* Get all search key word and process them into more reasonable words
*
* @return
*/
public String[] getSearchKeywords() {
PreparedStatement pstmt;
ResultSet rs;
ArrayList<String> appNames = new ArrayList<String>();
try {
pstmt = (PreparedStatement) conn.prepareStatement(DISTINCT_APP_NAME_QUERY);
rs = pstmt.executeQuery();
while (rs.next()) {
if (rs.getString(1) != null) appNames.add(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
return null;
}
return processKeywords(appNames.toArray(new String[appNames.size()]));
}
public JasperPrint relatorioIdade(String idadeInicial, String idadeFinal) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioFaixaEtaria.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
String query =
"SELECT p.nome,a.matricula,curdate()as data_atual,p.nascimento,(year(curdate()) - year(p.nascimento)) idade FROM pessoas p INNER JOIN alunos a ON p.idPessoa = a.idPessoa WHERE (year(curdate()) - year(p.nascimento)) >="
+ idadeInicial
+ " AND (year(curdate()) - year(p.nascimento)) <= "
+ idadeFinal;
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
/**
* Get the number of employees by position
*
* @param position position in string
* @return the number of employees by position; 0 if position is not specified, -1 if there are
* errors
*/
public int numOfEmployeesByType(String position) {
String sql = "SELECT count(*)\n" + "FROM Employee\n" + "WHERE position=?";
if (position == null) {
return 0;
}
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setString(1, position);
ResultSet rs = statement.executeQuery();
return rs.getInt(0);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Error! Cannot get numeber of employees by position");
return -1;
}
}
/**
* Get a customer id using email
*
* @param email customer email
* @return a customer id
*/
public int getCID(String email) {
String sql = "Select cID FROM Customer WHERE EMAIL=?";
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setString(1, email);
ResultSet rs = statement.executeQuery();
if (rs.next()) {
return rs.getInt("cid"); // cid
} else {
return -1; // not exist
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Failed: " + e.getMessage());
return -1;
}
}
public String[] getPackageNames() {
PreparedStatement pstmt;
ResultSet rs;
ArrayList<String> allPackageNames = new ArrayList<String>();
String query = DISTINCT_PACKAGE_NAME_QUERY;
Pattern p = Pattern.compile(REGEX_VERIFY_PACKAGE_NAME);
try {
pstmt = (PreparedStatement) conn.prepareStatement(query);
rs = pstmt.executeQuery();
while (rs.next()) {
Matcher m = p.matcher(rs.getString(1));
if (m.find()) allPackageNames.add(rs.getString(1));
else System.out.println("PackageName verification err: " + rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
return allPackageNames.toArray(new String[allPackageNames.size()]);
}
public JasperPrint relatorioHorario(String horaInicio) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioHorario.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
horaInicio = (new StringBuilder()).append("= '").append(horaInicio).append("'))").toString();
String query =
(new StringBuilder())
.append(
"SELECT a.matricula, p.nome, t.horaInicio, t.horaFinal FROM alunos a, pessoas p, turmas t WHERE a.idPessoa = p.idPessoa AND a.matricula IN (SELECT matricula FROM ADESOES WHERE status=true AND codTurma IN (SELECT codTurma FROM TURMAS where horaInicio ")
.append(horaInicio)
.toString();
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
/**
* ユーザー情報をDBから取得する為のメソッド
*
* @return boolean 処理の成否
*/
public boolean selectToUser(int userId, ModifyInformationDTO dto) {
boolean result = false;
con = (Connection) DBConnector.getConnection();
String sql = "SELECT * FROM user WHERE user_id = ? ";
try {
PreparedStatement ps = (PreparedStatement) con.prepareStatement(sql);
ps.setInt(1, userId);
ResultSet resultSet = ps.executeQuery();
if (resultSet.next()) {
dto.setUserName(resultSet.getString("user_name"));
dto.setNameKana(resultSet.getString("name_kana"));
dto.setPostNumber(resultSet.getString("post_number"));
dto.setAddress1(resultSet.getString("street_address1"));
dto.setAddress2(resultSet.getString("street_address2"));
dto.setAddress3(resultSet.getString("street_address3"));
dto.setPhoneNumber(resultSet.getString("phone_number"));
dto.setEmail(resultSet.getString("mail_address"));
dto.setPassword(resultSet.getString("password"));
dto.setCreditNumber(resultSet.getString("credit_number"));
dto.setToken(resultSet.getString("token"));
dto.setUserId(resultSet.getInt("user_id"));
result = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
@Override
public void actionPerformed(ActionEvent arg0) {
if (arg0.getActionCommand().equals("Search")) {
// 获取文件路径及关键词输入框的内容
String textfirst = new String();
textfirst = txtkeyFirst.getText();
txtDate.append(textfirst + "\r\n");
// System.out.println("事件相应"+textfirst);
if (textfirst != null && textfirst.length() != 0) System.out.println(textfirst);
else System.out.println("kong");
try {
// jdbc链接数据库
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/nlp?user=root&password=123456";
Connection cn = (Connection) DriverManager.getConnection(url);
// //执行插入
PreparedStatement ps =
(PreparedStatement) cn.prepareStatement("insert into text (url,tabword) values (?,?)");
// for (int i = 1; i <= output.size(); i = i + 2) {
// ps.setString(1, output.get(i - 1));
// ps.setString(2, output.get(i));
// ps.executeUpdate();
// }
// 查询语句
String keywords = textfirst;
String sqlSelect = "select * from text where tabword='" + keywords + "'";
// 执行查询
ResultSet rs = ps.executeQuery(sqlSelect);
// 执行查询后取值
while (rs.next()) {
// rs.getString("tabword");
System.out.println(rs.getString(2));
// System.out.println(rs.getString(3));
txtDate.append(rs.getString(2) + "\r\n");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}