Adding database capability in Struts 2.2.1 application

To add the database capability we have used Data Access Object (DAO) design pattern.

ConnectionFactory.java

package net.roseindia.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {
	String driverName = "com.mysql.jdbc.Driver";
	String conUrl = "jdbc:mysql://localhost:3306/studentadmissionprocess";
	String dbUser = "root";
	String dbPwd = "root";

	private static ConnectionFactory connectionFactory = null;

	private ConnectionFactory() {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public Connection getConnection() throws SQLException {
		Connection conn = null;
		conn = DriverManager.getConnection(conUrl, dbUser, dbPwd);
		return conn;
	}

	public static ConnectionFactory getInstance() {
		if (connectionFactory == null) {
			connectionFactory = new ConnectionFactory();
		}
		return connectionFactory;
	}
}

ApplicationDAO.java

package net.roseindia.dao;

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

import net.roseindia.model.SearchModel;
import net.roseindia.model.StudentAdmissionModel;
import net.roseindia.model.UpdateModel;

public class ApplicationDAO {

	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet rs = null;

	public ApplicationDAO() {

	}

	private Connection getConnection() throws SQLException {
		java.sql.Connection con;
		con = ConnectionFactory.getInstance().getConnection();
		return con;
	}

	public void add(StudentAdmissionModel studentAddmission) {

		try {
			String queryString = "INSERT INTO stud_admission(s_name, f_name, dob, c_date, class1, address, phoneno, fee) VALUES(?,?,?,?,?,?,?,?)";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setString(1, studentAddmission.getName());
			ptmt.setString(2, studentAddmission.getFname());
			ptmt.setString(3, studentAddmission.getDob());
			ptmt.setString(4, studentAddmission.getCurrentDate());
			ptmt.setString(5, studentAddmission.getClass1());
			ptmt.setString(6, studentAddmission.getAddress());
			ptmt.setString(7, studentAddmission.getPhoneno());
			ptmt.setLong(8, studentAddmission.getFee());
			ptmt.executeUpdate();
			System.out.println("Data Added Successfully");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	public StudentAdmissionModel search(SearchModel model) {
		StudentAdmissionModel student=new StudentAdmissionModel();
		String query;
		query = "select * from stud_admission where s_name='"
				+ model.getS_name() + "' or roll_no='" + model.getS_roll()
				+ "' or class1='" + model.getCalss1() + "'";
		System.out.println(query);
		try {
			connection = getConnection();
			ptmt = connection.prepareStatement(query);
			rs = ptmt.executeQuery(query);
			System.out.println("Hello" + rs);
			while (rs.next()) {
				student.setName(rs.getString("s_name"));
				student.setFname(rs.getString("f_name"));
				student.setDob(rs.getString("dob"));
				student.setCurrentDate(rs.getString("c_date"));
				student.setClass1(rs.getString("class1"));
				student.setAddress(rs.getString("address"));
				student.setPhoneno(rs.getString("phoneno"));
				student.setFee(rs.getLong("fee"));
				student.setRoll(rs.getInt("roll_no"));
			}
		} catch (Exception e) {
			e.toString();
		}
	finally {
		try {
			if (ptmt != null)
				ptmt.close();
			if (connection != null)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
		return student;
	}
	
	public StudentAdmissionModel search(int roll){
		try{
			connection=getConnection();
			StudentAdmissionModel model=new StudentAdmissionModel();
			String query="select * from stud_admission where roll_no='"+roll+"'";
			ptmt=connection.prepareStatement(query);
			rs=ptmt.executeQuery();
			while(rs.next()){
				model.setName(rs.getString("s_name"));
				model.setFname(rs.getString("f_name"));
				model.setDob(rs.getString("dob"));
				model.setCurrentDate(rs.getString("c_date"));
				model.setClass1(rs.getString("class1"));
				model.setAddress(rs.getString("address"));
				model.setPhoneno(rs.getString("phoneno"));
				model.setFee(rs.getLong("fee"));
				model.setRoll(rs.getInt("roll_no"));
			}			
			return model;
		}
		catch(Exception e){
			e.toString();
		}
		finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	public void delete(int roll){
		try{
			connection=getConnection();
			String query="delete from stud_admission where roll_no=?";
			ptmt=connection.prepareStatement(query);
			ptmt.setInt(1, roll);
			ptmt.executeUpdate();
			System.out.println("Rocord deleted");
			}
		catch(Exception e){
			e.toString();
		}
		finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void update(UpdateModel model){
		String query="update stud_admission set s_name=?, f_name=?, dob=?, c_date=?, class1=?, address=?, phoneno=?, fee=? where roll_no=?";
		try{
			connection=getConnection();
			ptmt=connection.prepareStatement(query);
			ptmt.setString(1, model.getName());
			ptmt.setString(2, model.getFname());
			ptmt.setString(3, model.getDob());
			ptmt.setString(4, model.getCurrentDate());
			ptmt.setString(5, model.getClass1());
			ptmt.setString(6, model.getAddress());
			ptmt.setString(7, model.getPhoneno());
			ptmt.setLong(8, model.getFee());
			ptmt.setInt(9, model.getRoll());
			ptmt.executeUpdate();
			System.out.println("Table Updated Suucessfully");
			}
		catch(Exception e){
			e.toString();
		}
		finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
}

Related Tags for Adding Databse Capibility to Struts2.2.1 :