public void close() throws SQLException {
if (mStatQuery != null && !mStatQuery.isClosed()) {
mStatQuery.close();
}
if (mStatCount != null && !mStatCount.isClosed()) {
mStatCount.close();
}
mClose = true;
}
public boolean deleteProduit(int id) {
Connection conn = new ConnectionInit().getCon();
PreparedStatement pstmt = null;
String sql = "DELETE FROM aphrodis.produit WHERE id = '" + id + "'";
try {
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
return true;
} catch (SQLException ex) {
Logger.getLogger(ProduitDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
return false;
} finally {
try {
if (!pstmt.isClosed()) {
pstmt.close();
}
if (!conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
Logger.getLogger(UserDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
private void closePreviousResources() {
try {
// rSet.isClosed is not supported by some JDBC drivers so don't check it
if (rSet != null) {
rSet.close();
}
} catch (SQLException ex) {
// do nothing
} finally {
rSet = null;
}
try {
if (preparedStatement != null && !preparedStatement.isClosed()) {
preparedStatement.close();
}
} catch (SQLException ex) {
// do nothing
} finally {
preparedStatement = null;
}
try {
if (callableStatement != null && !callableStatement.isClosed()) {
callableStatement.close();
}
} catch (SQLException ex) {
// do nothing
} finally {
callableStatement = null;
}
}
public boolean updateProduit(Produit produit) {
Connection conn = new ConnectionInit().getCon();
PreparedStatement pstmt = null;
try {
String sql = "UPDATE aphrodis.produit SET name = ?, quantite = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, produit.getName());
pstmt.setDouble(2, produit.getQuantité());
pstmt.executeUpdate();
return true;
} catch (SQLException ex) {
Logger.getLogger(ProduitDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
return false;
} finally {
try {
if (!pstmt.isClosed()) {
pstmt.close();
}
if (!conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
Logger.getLogger(UserDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
/**
* {@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);
}
/**
* {@inheritDoc} This method will delete 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 boolean delete(Distance distance) throws DataUpdateException, DataAccessException {
if (distance == null || distance.id < 0) {
return false;
}
try {
if (delete == null || delete.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.DELETE).addWhereClause("id=?");
delete = connection.prepareStatement(builder.build());
}
delete.setInt(1, distance.id);
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing DataAccessException!");
throw new DataAccessException(e);
}
try {
delete.executeUpdate();
return true;
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
}
public Produit createProduit(Produit produit) {
Connection conn = new ConnectionInit().getCon();
PreparedStatement pstmt = null;
try {
String sql = "INSERT INTO aphrodis.produit(name, quantite) VALUES (?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, produit.getName());
pstmt.setDouble(2, produit.getQuantité());
pstmt.executeUpdate();
return produit;
} catch (SQLException ex) {
Logger.getLogger(ProduitDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
return null;
} finally {
try {
if (!pstmt.isClosed()) {
pstmt.close();
}
if (!conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
Logger.getLogger(UserDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
@Test
public void shouldCreateStatement() throws Exception {
PreparedStatement stmt =
conn.prepareStatement("SELECT * FROM Item WHERE stringKey = ? OR intKey = ?");
assertNotNull(stmt);
stmt.close();
assertTrue(stmt.isClosed());
}
/** Local PreparedStatement insert WITHOUT bind variables */
@Test
public void testPreparedStatementInsert() throws Exception {
Connection connection = makeConnection(new ArrayList<JdbcTest.Employee>());
assertFalse(connection.isClosed());
String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
assertFalse(preparedStatement.isClosed());
boolean status = preparedStatement.execute();
assertFalse(status);
ResultSet resultSet = preparedStatement.getResultSet();
assertTrue(resultSet == null);
int updateCount = preparedStatement.getUpdateCount();
assertTrue(updateCount == 1);
}
/**
* {@inheritDoc} This method will insert the distance data into a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public int insert(Distance distance) throws DataUpdateException, DataAccessException {
int id = -1;
if (distance == null
|| distance.endRoom == null
|| distance.startRoom == null
|| distance.distance < 0) {
return id;
}
try {
if (insert == null || insert.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.INSERT)
.addColumns("startRoomId", "endRoomId", "distance")
.addValue("?,?,?");
insert = connection.prepareStatement(builder.build(), Statement.RETURN_GENERATED_KEYS);
}
insert.setInt(1, distance.startRoom.id);
insert.setInt(2, distance.endRoom.id);
insert.setInt(3, distance.distance);
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataUpdateException(e);
}
try {
insert.executeUpdate();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
try {
ResultSet set = insert.getGeneratedKeys();
if (set.next()) {
id = set.getInt(1);
}
set.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
}
return id;
}
@Override
public void cancel() throws JdbcException {
try {
if (preparedStatement != null && !preparedStatement.isClosed()) {
preparedStatement.cancel();
}
} catch (SQLException ex) {
throw newJdbcException(ex);
}
try {
if (callableStatement != null && !callableStatement.isClosed()) {
callableStatement.cancel();
}
} catch (SQLException ex) {
throw newJdbcException(ex);
}
}
/** Remote PreparedStatement insert WITHOUT bind variables */
@Test
public void testRemotePreparedStatementInsert() throws Exception {
final Connection connection =
DriverManager.getConnection(
"jdbc:avatica:remote:factory=" + LocalServiceModifiableFactory.class.getName());
assertThat(connection.isClosed(), is(false));
String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
assertThat(preparedStatement.isClosed(), is(false));
boolean status = preparedStatement.execute();
assertThat(status, is(false));
ResultSet resultSet = preparedStatement.getResultSet();
assertThat(resultSet, nullValue());
int updateCount = preparedStatement.getUpdateCount();
assertThat(updateCount, is(1));
}
@Test
public void shouldExecutePreparedStatement() throws Exception {
PreparedStatement stmt =
conn.prepareStatement(
"SELECT stringKey, intKey FROM Item WHERE stringKey = ? OR intKey = ?");
assertNotNull(stmt);
stmt.setString(1, "1");
stmt.setInt(2, 1);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertThat(rs.getString("stringKey"), equalTo("1"));
assertThat(rs.getInt("intKey"), equalTo(1));
stmt.close();
assertTrue(stmt.isClosed());
}
public void closeTStatement() {
try {
if (stmt != null) {
if (stmt instanceof Statement) {
Statement st = (Statement) stmt;
if (st.isClosed() == false) {
st.close();
if (LOG.isDebugEnabled())
LOG.debug(serverWorkerName + ". T2 st.close(" + stmtLabel + ")");
}
} else if (stmt instanceof PreparedStatement) {
PreparedStatement pst = (PreparedStatement) stmt;
if (pst.isClosed() == false) {
pst.close();
if (LOG.isDebugEnabled())
LOG.debug(serverWorkerName + ". T2 pst.close(" + stmtLabel + ")");
}
}
}
} catch (SQLException sql) {
}
reset();
}
@Test
public void testRemotePreparedStatementFetch() throws Exception {
final Connection connection =
DriverManager.getConnection(
"jdbc:avatica:remote:factory=" + LocalServiceMoreFactory.class.getName());
assertThat(connection.isClosed(), is(false));
String sql = "select * from \"foo\".\"bar\"";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
assertThat(preparedStatement.isClosed(), is(false));
boolean status = preparedStatement.execute();
assertThat(status, is(true));
ResultSet resultSet = preparedStatement.getResultSet();
assertThat(resultSet, notNullValue());
int count = 0;
while (resultSet.next()) {
assertThat(resultSet.getObject(1), notNullValue());
count += 1;
}
assertThat(count, is(101));
}
/** {@inheritDoc} */
@Override
public boolean deleteAll() throws DataAccessException, DataUpdateException {
boolean success = false;
try {
if (delete == null || delete.isClosed()) {
delete = connection.prepareStatement("DELETE FROM distance;");
}
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
try {
delete.execute();
success = true;
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
return success;
}
@BeforeClass
public static void setUpObjects() throws Exception {
// (Note: Can't use JdbcTest's connect(...) for this test class.)
connection = new Driver().connect("jdbc:drill:zk=local", JdbcAssert.getDefaultProperties());
plainStatement = connection.createStatement();
preparedStatement = connection.prepareStatement("VALUES 'PreparedStatement query'");
try {
connection.prepareCall("VALUES 'CallableStatement query'");
fail("Test seems to be out of date. Was prepareCall(...) implemented?");
} catch (SQLException | UnsupportedOperationException e) {
// Expected.
}
try {
connection.createArrayOf("INTEGER", new Object[0]);
fail("Test seems to be out of date. Were arrays implemented?");
} catch (SQLException | UnsupportedOperationException e) {
// Expected.
}
resultSet = plainStatement.executeQuery("VALUES 'plain Statement query'");
resultSet.next();
resultSetMetaData = resultSet.getMetaData();
databaseMetaData = connection.getMetaData();
// Self-check that member variables are set:
assertFalse("Test setup error", connection.isClosed());
assertFalse("Test setup error", plainStatement.isClosed());
assertFalse("Test setup error", preparedStatement.isClosed());
assertFalse("Test setup error", resultSet.isClosed());
// (No ResultSetMetaData.isClosed() or DatabaseMetaData.isClosed():)
assertNotNull("Test setup error", resultSetMetaData);
assertNotNull("Test setup error", databaseMetaData);
}
/**
* {@inheritDoc} This method will update the distance data in a MariaDB database. This method
* assumes the connection member is not null and open. Therefore, should be called through
* MariaDaoManager.
*/
@Override
public boolean update(Distance distance) throws DataUpdateException, DataAccessException {
if (distance == null
|| distance.endRoom == null
|| distance.startRoom == null
|| distance.id < 0
|| distance.distance < 0) {
return false;
}
try {
if (update == null || update.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.UPDATE)
.addSetClauses("startRoomId=?", "endRoomId=?", "distance=?")
.addWhereClause("id=?");
update = connection.prepareStatement(builder.build());
}
update.setInt(1, distance.startRoom.id);
update.setInt(2, distance.endRoom.id);
update.setInt(3, distance.distance);
update.setInt(4, distance.id);
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing DataAccessException!");
throw new DataAccessException(e);
}
try {
update.executeUpdate();
return true;
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataUpdateException!");
throw new DataUpdateException(e);
}
}
/**
* {@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 List<Distance> getAll() throws DataAccessException {
ArrayList<Distance> distances = new ArrayList<>();
try {
if (selectAll == null || selectAll.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns(
"distance.id",
"distance.startRoomId",
"distance.endRoomId",
"classroom1.roomName",
"classroom1.buildingId",
"building1.buildingName",
"classroom1.subjectId",
"subject1.subjectName",
"classroom2.roomName",
"classroom2.buildingId",
"building2.buildingName",
"classroom2.subjectId",
"subject2.subjectName",
"distance.distance")
.addJoinClause(
new JoinClause(
JoinType.INNER,
"classroom classroom1",
"classroom1.id=distance.startRoomId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "building building1", "building1.id=classroom1.buildingId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "subject subject1", "subject1.id=classroom1.subjectId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "classroom classroom2", "classroom2.id=distance.endRoomId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "building building2", "building2.id=classroom2.buildingId"))
.addJoinClause(
new JoinClause(
JoinType.INNER, "subject subject2", "subject2.id=classroom2.subjectId"));
selectAll = connection.prepareStatement(builder.build());
}
ResultSet set = selectAll.executeQuery();
while (set.next()) {
Subject subject1 = new Subject(set.getInt(7), set.getString(8));
Subject subject2 = new Subject(set.getInt(12), set.getString(13));
Building building1 = new Building(set.getInt(5), set.getString(6));
Building building2 = new Building(set.getInt(10), set.getString(11));
Classroom classroom1 = new Classroom(set.getInt(2), set.getString(4), building1, subject1);
Classroom classroom2 = new Classroom(set.getInt(3), set.getString(9), building2, subject2);
distances.add(new Distance(set.getInt(1), classroom1, classroom2, set.getInt(14)));
}
set.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
return distances;
}
public static final void fecharPreparedStatement(PreparedStatement pStmt) throws SQLException {
if (pStmt != null && !pStmt.isClosed()) {
pStmt.close();
}
}
/**
* {@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 List<Distance> getAllDistancesFrom(Classroom classroom) throws DataAccessException {
ArrayList<Distance> distances = new ArrayList<>();
if (classroom == null || classroom.id < 0) {
return distances;
}
try {
if (selectAllRoomStart == null || selectAllRoomStart.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns(
"distance.id",
"classroom.id",
"classroom.roomName",
"building.id",
"buildingName",
"subject.id",
"subject.subjectName",
"distance.distance")
.addJoinClause(
new JoinClause(JoinType.INNER, "classroom", "distance.endRoomId=classroom.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "building", "classroom.buildingId=building.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "subject", "classroom.subjectId=subject.id"))
.addWhereClause("distance.startRoomId=?");
selectAllRoomStart = connection.prepareStatement(builder.build());
}
selectAllRoomStart.setInt(1, classroom.id);
ResultSet startSet = selectAllRoomStart.executeQuery();
while (startSet.next()) {
Distance distance =
new Distance(
startSet.getInt(1),
classroom,
new Classroom(
startSet.getInt(2),
startSet.getString(3),
new Building(startSet.getInt(4), startSet.getString(5)),
new Subject(startSet.getInt(6), startSet.getString(7))),
startSet.getInt(8));
distances.add(distance);
}
startSet.close();
if (selectAllRoomEnd == null || selectAllRoomEnd.isClosed()) {
SqlBuilder builder =
new SqlBuilder("distance", StatementType.SELECT)
.addColumns(
"distance.id",
"classroom.id",
"classroom.roomName",
"building.id",
"buildingName",
"subject.id",
"subject.subjectName",
"distance.distance")
.addJoinClause(
new JoinClause(
JoinType.INNER, "classroom", "distance.startRoomId=classroom.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "building", "classroom.buildingId=building.id"))
.addJoinClause(
new JoinClause(JoinType.INNER, "subject", "classroom.subjectId=subject.id"))
.addWhereClause("distance.endRoomId=?");
selectAllRoomEnd = connection.prepareStatement(builder.build());
}
selectAllRoomEnd.setInt(1, classroom.id);
ResultSet endSet = selectAllRoomEnd.executeQuery();
while (endSet.next()) {
Distance distance =
new Distance(
endSet.getInt(1),
classroom,
new Classroom(
endSet.getInt(2),
endSet.getString(3),
new Building(endSet.getInt(4), endSet.getString(5)),
new Subject(endSet.getInt(6), endSet.getString(7))),
endSet.getInt(8));
distances.add(distance);
}
endSet.close();
} catch (SQLException e) {
Log.debug("Caught [" + e + "] so throwing a DataAccessException!");
throw new DataAccessException(e);
}
return distances;
}
public boolean isClosed() throws SQLException {
if (ps != null) {
return ps.isClosed();
}
return true;
}
public int selectAccountID(String accountName, int accountNum, int routingNum, int totalCost)
throws SQLException, ClassNotFoundException {
int accountID = -2;
PreparedStatement prepStmt = null;
Connection myConn = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
myConn =
DriverManager.getConnection(
"jdbc:mysql://cse.unl.edu:3306/dmcmanus", "dmcmanus", "mcmanus");
// String sqlAccountExists = "SELECT accountNum FROM dmcmanus.Accounts WHERE "
// +"accountNum = ?";
// make sure account is correct and get id
String sqlAccountSame =
"SELECT accountID, balance FROM dmcmanus.Accounts WHERE "
+ "accountName = ? AND accountNum = ? AND routingNum = ?";
// update account balance
String sqlUpdateBalance = "UPDATE dmcmanus.Accounts SET balance = ? WHERE accountID = ?";
// String sqlAccountInsert = "INSERT INTO dmcmanus.Accounts (accountName, accountNum,
// routingNum) VALUE"
// +"(?, ?, ?)";
prepStmt = myConn.prepareStatement(sqlAccountSame);
prepStmt.setString(1, accountName);
prepStmt.setInt(2, accountNum);
prepStmt.setInt(3, routingNum);
rs = prepStmt.executeQuery();
if (rs.next()) { // all account info matches
accountID = rs.getInt(1);
double balance = rs.getDouble(2);
// checks if account balance is enough to pay for the cost
if (balance >= totalCost) { // if enough update account balance
balance -= totalCost;
prepStmt = myConn.prepareStatement(sqlUpdateBalance);
prepStmt.setDouble(1, balance);
prepStmt.setInt(2, accountID);
prepStmt.executeUpdate();
} else { // otherwise return specific error code
accountID = -3;
}
}
// code from when I could make a new account
/*
prepStmt=myConn.prepareStatement(sqlAccountExists);
prepStmt.setInt(1, accountNum);
rs=prepStmt.executeQuery();
if(rs.next()){ //accountNum already exists
prepStmt=myConn.prepareStatement(sqlAccountSame);
prepStmt.setString(1, accountName);
prepStmt.setInt(2, accountNum);
prepStmt.setInt(3, routingNum);
rs=prepStmt.executeQuery();
if(rs.next()){ //all account info matches
accountID = rs.getInt(1);
} else{ //accountNum matches but others do not send up error
//do nothing int accountID is still -1
}
} else{ //accountNum doesn't exist create account
prepStmt=myConn.prepareStatement(sqlAccountInsert);
prepStmt.setString(1, accountName);
prepStmt.setInt(2, accountNum);
prepStmt.setInt(3, routingNum);
prepStmt.executeUpdate(); //insert account
//get accountID
prepStmt=myConn.prepareStatement(sqlAccountSame);
prepStmt.setString(1, accountName);
prepStmt.setInt(2, accountNum);
prepStmt.setInt(3, routingNum);
rs=prepStmt.executeQuery();
rs.next();
accountID = rs.getInt(1);
}*/
} finally {
if (rs != null && !rs.isClosed()) rs.close();
if (prepStmt != null && !prepStmt.isClosed()) prepStmt.close();
if (myConn != null && !myConn.isClosed()) myConn.close();
}
return accountID;
}
public boolean isClosed() throws SQLException {
return delegate.isClosed();
}
@Override
public boolean isClosed() throws SQLException {
return stmt.isClosed();
}
private void prepareStatement() throws SQLException {
// Get the SQL connection
SqlConnector sql_connector = mDataset.getSqlConnector();
if (sql_connector != null) {
try {
Connection connection = sql_connector.getConnection();
// Check statements are still valid
if (mStatQuery == null || mStatQuery.isClosed()) {
// Create the query statement
mStatQuery = connection.prepareStatement(mQuery);
mStatQuery.setFetchSize(1000);
}
if (mStatCount == null || mStatCount.isClosed()) {
// Create the count statement
mStatCount = connection.prepareStatement("SELECT COUNT(*) FROM (" + mQuery + ")");
mStatCount.setFetchSize(1000);
}
} catch (IOException e) {
mNbRows = -1;
Factory.getLogger().log(Level.SEVERE, e.getMessage(), e);
close();
}
}
mInitialized = true;
}