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;
}
}