111

package ${package};

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;


@WebServlet("/${classname}")
public class ${classname} extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		String type = request.getParameter("type");

		try {
			if (type.equals("getAll")) {
				getAll(request, response);
			} else if (type.equals("remove")) {
				delete(request, response);
			} else if (type.equals("modify")) {
				update(request, response);
			} else if(type.equals("insert")){
				insert(request,response);
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void delete(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, ClassNotFoundException {
		

		Connection con = getConnection();
		StringBuilder sb=new StringBuilder(100);
		sb.append("delete from ${tablename} where ");
		<#list primaryKey as key>
			<#if (key_index=0)>
		sb.append("${key.name}=? ");
			</#if>
			<#if (key_index!=0)>
		sb.append(" and ${key.name}=? ");
			</#if>
		</#list>
		PreparedStatement cmd = con.prepareStatement(sb.toString());
		<#list primaryKey as key>
		cmd.setString(${key_index+1}, request.getParameter("${key.name}"));
		</#list>
		cmd.executeUpdate();
		con.close();

	}

	<#assign curIndex=0/>

	private void update(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, ClassNotFoundException {
		Connection con = getConnection();
		StringBuilder sb=new StringBuilder(100);
		sb.append("update ${tablename} set ");
		<#list keys as key>
		sb.append("${key.name}=?<#if key_has_next>,</#if>");
		<#assign curIndex=key_index+1 />
		</#list>

		sb.append(" where ");

		<#list primaryKey as key>
		sb.append("${key.name}=?<#if key_has_next>,</#if>");
		</#list>
		
		PreparedStatement cmd = con.prepareStatement(sb.toString());
		<#list keys as key>
		cmd.setObject(${key_index+1},request.getParameter("${key.name}"));
		</#list>
		
		<#list primaryKey as key>
		cmd.setObject(${curIndex+1},request.getParameter("${key.name}"));
		<#assign curIndex=curIndex+1/>
		</#list>
		cmd.executeUpdate();

		con.close();

	}

	private void insert(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, ClassNotFoundException {
		Connection con = getConnection();
		StringBuilder sb=new StringBuilder(100);
		sb.append("insert into ${tablename} (");
		<#list keys as key>
		sb.append("${key.name}<#if key_has_next>,</#if>");
		</#list>
		sb.append(") values(");
		<#list keys as key>
		sb.append("?<#if key_has_next>,</#if>");
		</#list>
		sb.append(")");
		PreparedStatement cmd = con.prepareStatement(sb.toString());

		<#list keys as key>
		cmd.setObject(${key_index+1},request.getParameter("${key.name}"));
		</#list>

		cmd.executeUpdate();
		con.close();

	}

	private void getAll(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, ClassNotFoundException, IOException {
		Connection con = getConnection();
		List<Map> list = new ArrayList<Map>();
		ResultSet rs = con.createStatement().executeQuery("select * from ${tablename}");
		while (rs.next()) {
			Map map=new HashMap();
			<#list keys as key>
			map.put("${key.name}",rs.getString("${key.name}"));
			</#list>
			
			list.add(map);
		}
		JSONObject obj = new JSONObject();
		obj.put("total", list.size());
		obj.put("rows", list);

		con.close();

		response.getWriter().print(obj.toString());

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

	private Connection getConnection() throws SQLException, ClassNotFoundException {
		String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

		String dbURL = "jdbc:sqlserver://101.251.209.184:24876;DatabaseName=roomdb";

		String userName = "sa";

		String userPwd = "6JYDrwXsDkuBhQ}M";
		Class.forName(driverName);

		Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
		dbConn.setAutoCommit(true);
		return dbConn;
	}

}