tutorialdbmanager.java

Spring framework basic tutorial using Java

package tutorial.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import tutorial.bean.InfoBean;

public class TutorialDBManager implements DBManager {

	DBConnection dbConnection;

	@Override
	public void setDBConnection(DBConnection connection) {
		this.dbConnection = connection;
	}

	@Override
	public InfoBean addInfo(InfoBean bean) {
		Connection con = dbConnection.getConnection();
		String prepSql = Messages.getString("TutorialDBManager.prepsqlInsertValue"); //$NON-NLS-1$
		try {
			PreparedStatement prep = con.prepareCall(prepSql);
			prep.clearParameters();
			prep.setInt(1, bean.getId());
			prep.setString(2, bean.getValue());
			prep.execute();
			return bean;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {

			}
		}
		return null;
	}

	@Override
	public int getMaxID() {
		Connection connection = dbConnection.getConnection();
		String sql = Messages.getString("TutorialDBManager.sqlSelectMaxID"); //$NON-NLS-1$
		try {
			Statement stmt = connection.createStatement();
			ResultSet result = stmt.executeQuery(sql);

			if (result.next()) {
				int lastID = result.getInt(1);
				return lastID;
			}
			connection.close();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return -1;
	}

	@Override
	public List<InfoBean> queryAllInfo() {
		ArrayList<InfoBean> result = new ArrayList<InfoBean>();
		String sql = Messages.getString("TutorialDBManager.sqlSelectAll"); //$NON-NLS-1$
		Connection con = dbConnection.getConnection();
		try {
			Statement stmt = con.createStatement();
			ResultSet resultValue = stmt.executeQuery(sql);
			while (resultValue.next()) {
				int id = resultValue.getInt(1);
				String value = resultValue.getString(2);
				result.add(new InfoBean(id, value));
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public InfoBean queryInfo(int id) throws RuntimeException {
		String prepSql = Messages.getString("TutorialDBManager.prepsqlSelectFromID"); //$NON-NLS-1$
		Connection con = dbConnection.getConnection();
		try {
			PreparedStatement prep = con.prepareCall(prepSql);
			prep.clearParameters();
			prep.setInt(1, id);

			ResultSet resultSet = prep.executeQuery();
			if (resultSet.next()) {
				String value = resultSet.getString(2);
				return new InfoBean(id, value);
			} else {
				throw new RuntimeException("No data in the DB");
			}

		} catch (SQLException e) {

			throw new RuntimeException("No data in the DB");
		}

	}

}