Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- public class DBConnection {
- public static Connection con = null;
- public static String user_id = null;
- public static DataSource ds = null;
- public static Connection getConnection() {
- try {
- con = ds.getConnection();
- con.setAutoCommit(false);
- } catch (SQLException ex) {
- Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
- }
- return con;
- }
- static {
- con = establishConnection();
- }
- public static void closeConnection() {
- if (con != null) {
- try {
- if (!con.isClosed()) {
- try {
- con.close();
- } catch (SQLException ex) {
- Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
- }
- }
- } catch (SQLException ex) {
- Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
- }
- }
- }
- }
- <Context path="/KnestAdmin">
- <Resource name="dbconn" auth="Container" type="javax.sql.DataSource"
- maxActive="100" maxIdle="30" maxWait="10000"
- removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
- username="root" password="" driverClassName="com.mysql.jdbc.Driver"
- url="jdbc:mysql://localhost:3306/mobitute_lms_data?useEncoding=true&characterEncoding=UTF-8"/>
- </Context>
- <resource-env-ref>
- <description>DB Connection</description>
- <resource-env-ref-name>dbconn</resource-env-ref-name>
- <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
- <res-auth>Container</res-auth>
- </resource-env-ref>
- public class Category extends HttpServlet {
- Connection connection = null;
- Statement statement = null;
- IST ist;
- PrintWriter out;
- protected void processRequest(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- response.setContentType("text/html;charset=UTF-8");
- out = response.getWriter();
- }
- public void connectToServer() throws SQLException, NamingException {
- connection = DBConnection.getConnection();
- statement = connection.createStatement();
- ist = new IST();
- }
- @Override
- protected void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- processRequest(request, response);
- try {
- connectToServer();
- switch (request.getParameter("mode")) {
- case "checkAvailability": {
- String category_name = request.getParameter("category_name");
- String sql = "Select category_name from course_category where category_name='" + category_name + "'";
- ResultSet resultset_val = statement.executeQuery(sql);
- if (resultset_val.next()) {
- out.print("exist");
- }
- break;
- }
- case "checkAvailability1": {
- String category_id = request.getParameter("category_id");
- String category_name = request.getParameter("category_name");
- String sql = "Select category_name from course_category where category_name='" + category_name + "' and category_id!='" + category_id + "'";
- ResultSet resultset_val = statement.executeQuery(sql);
- if (resultset_val.next()) {
- out.print("exist");
- }
- break;
- }
- case "checkwarning": {
- String category_id = request.getParameter("category_id");
- String warning = "";
- String sql_query = "select * from course where category_id='" + category_id + "' and status='Active'";
- ResultSet resultset_val = statement.executeQuery(sql_query);
- if (resultset_val.next()) {
- warning = "yes";
- }
- String sql_query1 = "select * from assessment where category_id='" + category_id + "' and status='Active'";
- ResultSet resultset_val1 = statement.executeQuery(sql_query1);
- if (resultset_val1.next()) {
- warning = "yes";
- }
- if (warning.equals("yes")) {
- throw new Exception();
- }
- break;
- }
- case "active_inactive": {
- String category_id = request.getParameter("category_id");
- String status = request.getParameter("status");
- String sql = "Update course_category set status='" + status + "',last_updated_user='" + request.getSession(false).getAttribute("log_user_id") + "',last_updated_ts='" + ist.getLastUpdatedts() + "' where category_id='" + category_id + "'";
- int i = statement.executeUpdate(sql);
- if (i > 0) {
- }
- connection.commit();
- break;
- }
- default:
- break;
- }
- } catch (Exception ex) {
- Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
- try {
- response.setStatus(400);
- if (!connection.isClosed()) {
- connection.rollback();
- }
- } catch (SQLException ex1) {
- Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex1);
- }
- } finally {
- try {
- out.flush();
- out.close();
- if (!statement.isClosed()) {
- statement.close();
- }
- DBConnection.closeConnection();
- connection=null;
- } catch (SQLException ex) {
- Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
- }
- }
- }
- @Override
- protected void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- processRequest(request, response);
- try {
- connectToServer();
- switch (request.getParameter("mode")) {
- case "add": {
- String category_name = request.getParameter("category_name");
- String category_desc = request.getParameter("category_desc");
- String status = request.getParameter("status");
- String sql = "INSERT INTO course_category(category_name,category_desc,status,created_user,created_ts,last_updated_user,last_updated_ts) VALUES ('" + category_name + "', '" + category_desc + "','" + status + "','" + request.getSession(false).getAttribute("log_user_id") + "','" + ist.getLastUpdatedts() + "','" + request.getSession(false).getAttribute("log_user_id") + "','" + ist.getLastUpdatedts() + "')";
- statement.executeUpdate(sql);
- connection.commit();
- break;
- }
- case "edit": {
- String category_id = request.getParameter("category_id");
- String category_name = request.getParameter("category_name");
- String category_desc = request.getParameter("category_desc");
- String sql = "update course_category set category_name='" + category_name + "',category_desc='" + category_desc + "',last_updated_user='" + request.getSession(false).getAttribute("log_user_id") + "',last_updated_ts='" + ist.getLastUpdatedts() + "' where category_id='" + category_id + "'";
- statement.executeUpdate(sql);
- connection.commit();
- break;
- }
- default:
- break;
- }
- } catch (Exception ex) {
- try {
- Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
- if (ex instanceof SQLIntegrityConstraintViolationException) {
- response.setStatus(1);
- }else{
- response.setStatus(2);
- }
- if (!connection.isClosed()) {
- connection.rollback();
- }
- } catch (SQLException ex1) {
- Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex1);
- }
- } finally {
- try {
- out.flush();
- out.close();
- if (!statement.isClosed()) {
- statement.close();
- }
- DBConnection.closeConnection();
- connection=null;
- } catch (SQLException ex) {
- Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
- }
- }
- }
- @Override
- public String getServletInfo() {
- return "Short description";
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement