@Before public void init() { SqlSession session = MyBatisTestUtil.getSession(); Connection c = session.getConnection(); Statement s = null; try { s = c.createStatement(); s.execute("TRUNCATE TABLE entity"); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (s != null && !s.isClosed()) { s.close(); } if (c != null && c.isClosed()) { c.close(); } } catch (SQLException e) { e.printStackTrace(); } if (session != null) { session.close(); } } }
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { int id = Integer.valueOf(request.getParameter("id")); Connection conexao = null; try { conexao = AbstractConnectionFactory.getConexao(); OrcamentoDao orcamentoDao = new OrcamentoDao(conexao); orcamentoDao.excluirOrcamento(id); write(response, "Orcamento excluido com sucesso"); } catch (ClassNotFoundException e) { e.printStackTrace(); write(response, "ERRO codigo CNFE10 " + e.getMessage()); } catch (SQLException e) { e.printStackTrace(); write(response, "ERRO codigo SQLE10 " + e.getMessage()); } finally { try { conexao.close(); } catch (SQLException e) { e.printStackTrace(); write(response, "ERRO codigo SQLE10 " + e.getMessage()); } } }
public static void release(ResultSet rs, Statement st, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } st = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } }
public static final void unlikes(User user, Post post) { Connection connection = null; PreparedStatement stmt = null; String sql = " DELETE FROM luser_likes_post where luser_id = ? and post_id = ?"; try { connection = DBConnection.getConnection(); stmt = connection.prepareStatement(sql); stmt.setString(1, user.getId()); stmt.setInt(2, post.getId()); stmt.executeUpdate(); } catch (SQLException ex) { Logger.getLogger(PostMapper.class.getName()).log(Level.SEVERE, null, ex); } finally { if (stmt != null) try { stmt.close(); } catch (SQLException e1) { e1.printStackTrace(); } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
public boolean execSQL(String[] sqls) { Statement statement = null; Connection connection = null; try { connection = DBManager.getConnection(); connection.setAutoCommit(false); statement = connection.createStatement(); for (String s : sqls) { statement.addBatch(s); } statement.executeBatch(); connection.commit(); return true; } catch (Exception e) { // TODO: handle exception try { connection.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return false; } finally { try { connection.setAutoCommit(true); statement.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBManager.freeConnection(connection); } }
public boolean outStu(String stuId, String rmark) throws DataBaseException { Statement stmt; stmt = DB.CreateStatement(); String sql = "select StuId from StuDormRoom where StuId='" + stuId + "'"; try { ResultSet rs = stmt.executeQuery(sql); if (rs != null && !rs.next()) { return false; } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { CallableStatement cst = connection.prepareCall("{call proc_StuInOut_SDM_out(?,?)}"); cst.setString(1, stuId); cst.setString(2, rmark); int r = cst.executeUpdate(); cst.close(); if (r > 0) { return true; } else { return false; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } }
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; }
public List<Unit> findAllUnit() { String sql = "select * from Unit"; OracleCachedRowSet ocrs = DbOperation.executeQuery(sql, null); List<Unit> list = new ArrayList<Unit>(); try { while (ocrs.next()) { Unit unit = new Unit(ocrs.getString(1), ocrs.getDouble(2)); list.add(unit); } if (!list.isEmpty()) { return list; } else { return null; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { ocrs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; }
private void insertData(SymptomsData data) { Connection connection = null; Statement statement = null; try { connection = DatabaseHelper.getConnection(); statement = connection.createStatement(); String query = "Insert into medicine_table values(" + data.getId() + ", '" + data.getSymptoms() + "', '" + data.getMedicines() + "')"; statement.executeUpdate(query); } catch (SQLException e) { e.printStackTrace(); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println(e); } } } }
/** * 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; }
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String username = request.getParameter("userName"); if (username != null) { IUserDao userDao = UserDaoImpl.getInstance(); User user = null; try { user = userDao.findUserByName(username); } catch (SQLException e1) { // TODO 自动生成的 catch 块 e1.printStackTrace(); } user.setActived(GenerateLinkUtils.verifyCheckcode(user, request)); int row = 0; try { row = userDao.updateUser(user); } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } if (row > 0) { request.getSession().setAttribute("user", user); request.getRequestDispatcher("/activeSuccess.jsp").forward(request, response); } } }
/** update a project */ public void update(Project project) { PreparedStatement stmt = null; try { String sql = "update Projects " + "set Title = ?, RecordSperImage = ?, FirstYCoord = ?, RecordHeight = ? " + "where ProjectID = ?"; stmt = db.getConnection().prepareStatement(sql); stmt.setString(1, project.getTitle()); stmt.setInt(2, project.getRecordsPerImage()); stmt.setInt(3, project.getFirstYCoord()); stmt.setInt(4, project.getRecordHeight()); stmt.setInt(5, project.getProjectID()); if (stmt.executeUpdate() == 1) { System.out.println("Update success"); } else { System.out.println("Update fail"); } } catch (SQLException e) { System.out.println("Can't execute update"); e.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException e) { System.out.println("Can't execute connect"); e.printStackTrace(); } } }
public void addClientUser(TwitterSession twitterSession, CreateCompletedListener addedListener) { ConnectionSource connectionSource = null; try { DataBaseHelper helper = new DataBaseHelper(Global.getInstance().getApplicationContext()); connectionSource = helper.getConnectionSource(); TableUtils.createTableIfNotExists(connectionSource, ClientUserTable.class); Dao<ClientUserTable, String> dao = helper.getDao(ClientUserTable.class); ClientUserTable table1 = dao.queryForId("" + twitterSession.getUserId()); if (table1 == null) { ClientUserTable table = new ClientUserTable(twitterSession); dao.createOrUpdate(table); new ClientUser( clientUsers.size(), twitterSession, clientUser -> { clientUsers.add(clientUser); addedListener.completed(clientUser); }, TwitterException::printStackTrace); } else { addedListener.completed(null); } } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { if (connectionSource != null) { try { connectionSource.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
@Override public User retrieveUser(User user) { final String SQL = "SELECT * FROM users WHERE username = ?"; PreparedStatement preparedStatement = PreparedStatementCreator.createPreparedStatement(SQL); ResultSet resultSet = null; try { preparedStatement.setString(1, user.getUsername()); resultSet = preparedStatement.executeQuery(); rowAffected = resultSet.getFetchSize(); System.out.println("Row: " + rowAffected); } catch (SQLException e) { e.printStackTrace(); } User retrievedUser = null; try { while (resultSet.next()) { retrievedUser = new User(); retrievedUser.setFirstName(resultSet.getString("first_name")); retrievedUser.setLastName(resultSet.getString("last_name")); retrievedUser.setUsername(resultSet.getString("username")); retrievedUser.setEmail(resultSet.getString("email")); retrievedUser.setPassword(resultSet.getString("password")); retrievedUser.setType(resultSet.getString("user_type")); } } catch (SQLException e) { e.printStackTrace(); } return retrievedUser; }
@Override public synchronized ResultMessage bookCollect(String bookISBN, int memberID) { ResultMessage isExist = queryCollect(memberID, bookISBN); if (isExist.isInvokeSuccess()) { return new ResultMessage(false, null, "已经收藏此书"); } ResultMessage booknum = getCollectedBook(memberID); if (booknum.isInvokeSuccess() && booknum.getResultSet().size() >= Const.MAX_COLLECT) { return new ResultMessage(false, null, "已达书架上线,无法收藏更多图书"); } Connection con = ConnectionFactory.getConnection(); String sql = "insert into collect(memberid,bookisbn) values (?,?)"; PreparedStatement ps; int row = 0; try { ps = con.prepareStatement(sql); ps.setInt(1, memberID); ps.setString(2, bookISBN); row = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } try { con.close(); } catch (SQLException e) { e.printStackTrace(); } if (row != 0) { return new ResultMessage(true, null, "收藏成功!"); } return new ResultMessage(false, null, "收藏失败"); }
public static void close(Connection connection, Statement statement, ResultSet resultSet) { if (resultSet != null) { try { resultSet.close(); resultSet = null; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (statement != null) { try { statement.close(); statement = null; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (connection != null) { try { connection.close(); connection = null; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } }
@Override public synchronized ResultMessage cancelCollect(String bookISBN, int memberID) { ResultMessage isExist = queryCollect(memberID, bookISBN); if (!isExist.isInvokeSuccess()) { return new ResultMessage(false, null, "未曾收藏此书"); } Connection con = ConnectionFactory.getConnection(); String sql = "delete from collect where memberid=? and bookisbn=?"; PreparedStatement ps; int row = 0; try { ps = con.prepareStatement(sql); ps.setInt(1, memberID); ps.setString(2, bookISBN); row = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } try { con.close(); } catch (SQLException e) { e.printStackTrace(); } if (row != 0) { return new ResultMessage(true, null, "删除收藏成功"); } return new ResultMessage(false, null, "删除收藏失败"); }
private void copyBetweenDatabases() { DatabaseHelper dbHelper = new DatabaseHelper(this); try { dbHelper.openDataBase(false); } catch (SQLException e) { e.printStackTrace(); } List<TestContainer> list = dbHelper.getTests(); dbHelper.close(); DatabaseHelper.DB_NAME = DatabaseHelper.DB_NAME.equals(DatabaseHelper.DB_AM) ? DatabaseHelper.DB_RU : DatabaseHelper.DB_AM; dbHelper = new DatabaseHelper(this); try { dbHelper.openDataBase(true); } catch (SQLException e) { e.printStackTrace(); } for (int i = 0; i < list.size(); i++) { int newDbProgress = dbHelper.getTests().get(i).getProgress(); int oldDbProgress = list.get(i).getProgress(); if (oldDbProgress != newDbProgress) { dbHelper.updateProgress(i + 1, oldDbProgress); } } dbHelper.close(); }
/** * Function to create temp visit dimension table using stored proc. * * @param tempTableName * @throws Exception */ public void createTempTable(String tempEncounterMappingTableName) throws I2B2Exception { Connection conn = null; try { // smuniraju: Postgres requires only the IN arguments to be supplied in the call to proc. // CallableStatement callStmt = conn.prepareCall("{call "+ getDbSchemaName() + // "CREATE_TEMP_EID_TABLE(?,?)}"); String prepareCallString = ""; if (dataSourceLookup.getServerType().equalsIgnoreCase(DataSourceLookupDAOFactory.POSTGRES)) { prepareCallString = "{call " + getDbSchemaName() + "CREATE_TEMP_EID_TABLE(?)}"; } else { prepareCallString = "{call " + getDbSchemaName() + "CREATE_TEMP_EID_TABLE(?,?)}"; } conn = getDataSource().getConnection(); CallableStatement callStmt = conn.prepareCall(prepareCallString); callStmt.setString(1, tempEncounterMappingTableName); callStmt.registerOutParameter(2, java.sql.Types.VARCHAR); callStmt.execute(); this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 2); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); throw new I2B2Exception("SQLException occured" + sqlEx.getMessage(), sqlEx); } catch (Exception ex) { ex.printStackTrace(); throw new I2B2Exception("Exception occured" + ex.getMessage(), ex); } finally { if (conn != null) { try { conn.close(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); log.error("Error while closing connection", sqlEx); } } } }
public Instance getDbInstance(int index) { Instance instance = new Instance(InstanceType.GOLD); Connection connection = null; try { connection = DbManager.getConnection(); String query = "SELECT I.id AS id, I.text AS text, AI.codes AS codes, AI.time_spent AS time_spent " + "FROM AnnotationInstance AI " + "JOIN Instance I ON AI.instance_id = I.id " + "WHERE AI.pid=? AND AI.`index`=?"; PreparedStatement statement = connection.prepareStatement(query); statement.setString(1, getPid()); statement.setInt(2, index); ResultSet resultSet = statement.executeQuery(); while (resultSet.next()) { instance.setId(resultSet.getInt("id")); instance.setText(resultSet.getString("text")); instance.setCodes(toInstanceCodes(resultSet.getString("codes"))); instance.setTimeSpent(resultSet.getInt("time_spent")); } resultSet.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (null != connection) connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return instance; }
public static final void reportPost(Report report) { Connection connection = null; PreparedStatement stmt = null; String sql = "INSERT INTO luser_reports_post (post_id,whistleblower_id,wrongdoing_id,category,reason) VALUES (?,?,?,?,?)"; try { connection = DBConnection.getConnection(); stmt = connection.prepareStatement(sql); stmt.setInt(1, report.getPostId()); stmt.setString(2, report.getWhistleblowerId()); stmt.setString(3, report.getWrongdoingId()); stmt.setString(4, report.getCategory()); stmt.setString(5, report.getReason()); stmt.executeUpdate(); } catch (SQLException ex) { Logger.getLogger(PostMapper.class.getName()).log(Level.SEVERE, null, ex); } finally { if (stmt != null) try { stmt.close(); } catch (SQLException e1) { e1.printStackTrace(); } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
public List<Instance> getInstances() { List<Instance> instances = new ArrayList<Instance>(); Connection connection = null; try { connection = DbManager.getConnection(); String query = "SELECT I.id AS id, I.message_id AS message_id, AI.codes AS codes " + "FROM AnnotationInstance AI " + "JOIN Instance I ON AI.instance_id = I.id " + "WHERE AI.pid=? ORDER BY AI.`index` ASC"; PreparedStatement statement = connection.prepareStatement(query); statement.setString(1, getPid()); ResultSet resultSet = statement.executeQuery(); while (resultSet.next()) { Instance instance = new Instance(InstanceType.GOLD); instance.setId(resultSet.getInt("id")); instance.setMessageId(resultSet.getString("message_id")); instance.setCodes(toInstanceCodes(resultSet.getString("codes"))); instances.add(instance); } resultSet.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (null != connection) connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return instances; }
public boolean update(List<T> entitys) { Statement statement = null; Connection connection = null; try { connection = DBManager.getConnection(); connection.setAutoCommit(false); statement = connection.createStatement(); for (T t : entitys) { updateTran(t, connection, statement); } statement.executeBatch(); connection.commit(); return true; } catch (Exception e) { // TODO: handle exception try { connection.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return false; } finally { try { connection.setAutoCommit(true); statement.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBManager.freeConnection(connection); } }
// in this implementation, there is only one order per customer // the data model however allows for multiple orders per customer public CustomerOrder findByCustomer(int customerID) { Customer customer = customerFacade.find(customerID); CustomerOrder order = new CustomerOrder(); Connection con = util.getConnection(); String sql = "select * from customer_order where customer_id=?"; PreparedStatement statement = null; try { statement = con.prepareStatement(sql); statement.setInt(1, customerID); ResultSet rs = statement.executeQuery(); while (rs.next()) { order.setId(rs.getInt(1)); order.setAmount(rs.getBigDecimal(2)); order.setDateCreated(rs.getDate(3)); order.setConfirmationNumber(rs.getInt(4)); order.setCustomer(customer); } } catch (SQLException e) { e.printStackTrace(); } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } return order; // return (CustomerOrder) // em.createNamedQuery("CustomerOrder.findByCustomer").setParameter("customer", // customer).getSingleResult(); }
public void TestTarn() { Statement statement = null; Connection connection = null; try { connection = getTranConnection(); statement = connection.createStatement(); insertTran("", connection, statement); updateTran("", connection, statement); connection.commit(); } catch (Exception e) { // TODO: handle exception try { connection.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } finally { try { connection.setAutoCommit(true); statement.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBManager.freeConnection(connection); } }
public static void main(String[] args) { Connection conn = null; try { // Step 1: connect to database server Driver d = new SimpleDriver(); conn = d.connect("jdbc:simpledb://localhost", null); // Step 2: execute the query Statement stmt = conn.createStatement(); String qry = "select SName, DName " + "from DEPT, STUDENT " + "where MajorId = DId"; ResultSet rs = stmt.executeQuery(qry); // Step 3: loop through the result set System.out.println("Name\tMajor"); while (rs.next()) { String sname = rs.getString("SName"); String dname = rs.getString("DName"); System.out.println(sname + "\t" + dname); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // Step 4: close the connection try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
public int guardarProducto(Producto producto) { Statement stmt = null; Connection conn = null; int registrosInsertados = 0; try { conn = super.getConection(); stmt = conn.createStatement(); registrosInsertados = stmt.executeUpdate( "INSERT INTO productos_aplicables (productos_id, denominacion, precio) " + "VALUES (" + producto.getIdproducto() + " , '" + producto.getDenominacion() + "' , " + producto.getPrecio() + ")"); } catch (SQLException ex) { while (ex != null) { ex.printStackTrace(); ex = ex.getNextException(); } try { stmt.close(); conn.close(); } catch (SQLException e) { while (e != null) { e.printStackTrace(); e = e.getNextException(); } } } return registrosInsertados; }
@Override public synchronized ResultMessage queryCollect(int memberID, String bookISBN) { Connection con = ConnectionFactory.getConnection(); String sql = "select * from collect where memberid=? and bookisbn=?"; PreparedStatement ps; ResultSet resultSet = null; try { ps = con.prepareStatement(sql); ps.setInt(1, memberID); ps.setString(2, bookISBN); resultSet = ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } ArrayList<BookPO> polist = map(resultSet); try { con.close(); } catch (SQLException e) { e.printStackTrace(); } if (polist != null) { return new ResultMessage(true, polist, "返回收藏"); } return new ResultMessage(false, null, "查询收藏失败"); }
/** * excluir * * @param VO Usuario * @return boolean */ public static boolean excluir(Usuario usuario) { SigeDataBase db = new SigeDataBase(); try { db.getConn().setAutoCommit(false); db.prepareStatement("DELETE FROM permissao_usuario WHERE cd_usuario=?"); db.setInt(1, usuario.getCodigo()); db.executeUpdate(); db.getPreparedStatement().close(); db.prepareStatement("DELETE FROM usuarios WHERE cd_usuario=?"); db.setInt(1, usuario.getCodigo()); db.executeUpdate(); db.getConn().commit(); db.closeConnection(); return true; } catch (SQLException ex) { ex.printStackTrace(); try { db.getConn().rollback(); db.closeConnection(); } catch (SQLException ex1) { ex1.printStackTrace(); db.closeConnection(); } return false; } }
@Override public ArrayList<Object> selectDao() { Connection con = MysqlCon.getConnection(); String sql = "select 강좌코드, 학과, 강좌명, 교재명, 교수명 from view_course_depart_prof"; ArrayList<Object> courses = new ArrayList<>(); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { courses.add( new ViewCourse( rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5))); } return courses; } catch (SQLException e) { e.printStackTrace(); return null; } finally { try { pstmt.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } }