private void saveToDb() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(UPDATE_WARE);
System.out.println(UPDATE_WARE);
pstmt.setString(1, StringUtils.toChinese(this.Pname));
pstmt.setString(2, StringUtils.toChinese(this.Pmodel));
pstmt.setString(3, StringUtils.toChinese(this.Pcost));
pstmt.setString(4, StringUtils.toChinese(this.Pheft));
pstmt.setString(5, StringUtils.toChinese(this.Pfacturer));
pstmt.setString(6, StringUtils.toChinese(this.Pnote));
pstmt.setInt(7, this.Status);
pstmt.setInt(8, this.Id);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("错误位置: DbWare.java:saveToDb(): " + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
Connection conn = null;
PreparedStatement pstmt = null;
try {
System.out.println("Enrollno: 130050131049");
// STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
// STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
// STEP 2: Executing query
String sql = "SELECT * FROM logindetails WHERE name = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "Krut");
ResultSet rs = pstmt.executeQuery();
out.print("| <b>Name</b>| ");
out.print("<b>Password</b>| ");
out.println("</br>\n-------------------------------</br>");
while (rs.next()) {
out.println();
out.print("| " + rs.getString(1));
out.print("| " + rs.getString(2) + "|");
out.println("</br>");
}
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (pstmt != null) conn.close();
} catch (SQLException se) {
} // do nothing
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
} // end finally try
} // end try
}
private void insertIntoDb() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(INSERT_WARE);
pstmt.setInt(1, this.Id);
pstmt.setString(2, StringUtils.toChinese(this.Pname));
pstmt.setString(3, StringUtils.toChinese(this.Pmodel));
pstmt.setString(4, StringUtils.toChinese(this.Pcost));
pstmt.setString(5, StringUtils.toChinese(this.Pheft));
pstmt.setString(6, StringUtils.toChinese(this.Pfacturer));
pstmt.setString(7, StringUtils.toChinese(this.Pnote));
pstmt.setString(8, StringUtils.toChinese(this.Createdate));
pstmt.setInt(9, this.Status);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("错误位置: Dbware:insertIntoDb()-" + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private void DELToDb() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(Del_ware);
System.out.println(Del_ware);
pstmt.setInt(1, this.Status);
pstmt.setInt(2, this.Id);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("错误位置: DbShop.java:DELToDb(): " + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
@SuppressWarnings("unchecked")
protected void BindRow() {
try {
view_plantillas_modulo pNode = new view_plantillas_modulo();
pNode.setID_Plantilla(m_RS.getInt("ID_Plantilla"));
pNode.setbID_Empleado(m_RS.getBoolean("bID_Empleado"));
pNode.setbNomina(m_RS.getBoolean("bNomina"));
pNode.setbTipo_Nomina(m_RS.getBoolean("bTipo_Nomina"));
pNode.setbCompania_Sucursal(m_RS.getBoolean("bCompania_Sucursal"));
pNode.setCompania(m_RS.getString("Compania"));
pNode.setFecha(m_RS.getDate("Fecha"));
pNode.setID_Movimiento(m_RS.getInt("ID_Movimiento"));
pNode.setID_Empleado(m_RS.getString("ID_Empleado"));
pNode.setMovimiento(m_RS.getString("Movimiento"));
pNode.setDescripcion(m_RS.getString("Descripcion"));
pNode.setAplicacion(m_RS.getString("Aplicacion"));
pNode.setCalcular(m_RS.getBoolean("Calcular"));
m_Rows.addElement(pNode);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.toString());
}
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter toClient = res.getWriter();
toClient.println("<!DOCTYPE HTML>");
toClient.println("<html>");
toClient.println("<head><title>Books</title></head>");
toClient.println("<body>");
toClient.println("<a href=\"index.html\">Home</A>");
toClient.println("<h2>List of books</h2>");
HttpSession session = req.getSession(false);
if (session != null) {
String name = (String) session.getAttribute("name");
if (name != null) {
toClient.println("<h2>name: " + name + "</h2>");
}
}
toClient.print("<form action=\"bookOpinion\" method=GET>");
toClient.println("<table border='1'>");
String sql = "Select code, title, author FROM books";
System.out.println(sql);
try {
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(sql);
while (result.next()) {
toClient.println("<tr>");
String codeStr = result.getString("code");
toClient.println(
"<td><input type=\"radio\" name=\"book" + "\" value=\"" + codeStr + "\"></td>");
toClient.println("<td>" + codeStr + "</td>");
toClient.println("<td>" + result.getString("title") + "</td>");
toClient.println("<td>" + result.getString("author") + "</td>");
toClient.println("</tr>");
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Resulset: " + sql + " Exception: " + e);
}
toClient.println("</table>");
toClient.println("<textarea rows=\"8\" cols=\"60\" name=\"comment\"></textarea><BR>");
toClient.println("<input type=submit>");
toClient.println("</form>");
toClient.println("</body>");
toClient.println("</html>");
toClient.close();
}
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
System.out.println("Clearing changes");
Connection c = null;
try {
DriverManager.registerDriver(new AppEngineDriver());
c = DriverManager.getConnection("jdbc:google:rdbms://trmrphdn:veebirakendused/andmebaas");
String statement = "delete from kandidaat where nimi = 'Tandi Kaat'";
PreparedStatement stmt = c.prepareStatement(statement);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
@SuppressWarnings("unchecked")
protected void BindRow() {
try {
view_usuarios_submodulo_roles pNode = new view_usuarios_submodulo_roles();
pNode.setID_Usuario(m_RS.getString("ID_Usuario"));
pNode.setID_Rol(m_RS.getString("ID_Rol"));
pNode.setNombre(m_RS.getString("Nombre"));
pNode.setEnrolado(m_RS.getBoolean("Enrolado"));
m_Rows.addElement(pNode);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.toString());
}
}
@SuppressWarnings("unchecked")
protected void BindRow() {
try {
view_cont_ce_modulo pNode = new view_cont_ce_modulo();
pNode.setMes(m_RS.getByte("Mes"));
pNode.setAno(m_RS.getInt("Ano"));
pNode.setCerrado(m_RS.getBoolean("Cerrado"));
pNode.setGenerado(m_RS.getString("Generado"));
pNode.setErrores(m_RS.getInt("Errores"));
pNode.setAlertas(m_RS.getInt("Alertas"));
m_Rows.addElement(pNode);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.toString());
}
}
@SuppressWarnings("unchecked")
protected void BindRow() {
try {
TBL_CATALOGOS pNode = new TBL_CATALOGOS();
pNode.setID_Catalogo(m_RS.getInt("ID_Catalogo"));
pNode.setNombre(m_RS.getString("Nombre"));
pNode.setSelect_Clause(m_RS.getString("Select_Clause"));
pNode.setPriDefault(m_RS.getString("PriDefault"));
pNode.setSecDefault(m_RS.getString("SecDefault"));
pNode.setSeguridad(m_RS.getString("Seguridad"));
pNode.setAplRep(m_RS.getBoolean("AplRep"));
m_Rows.addElement(pNode);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.toString());
}
}
public void service(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
try {
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
Connection connection =
DriverManager.getConnection("jdbc:mysql://127.0.0.1/school", "root", "password");
PreparedStatement preparedStatement =
connection.prepareStatement("update student set name=?, per=? where roll=?");
preparedStatement.setString(1, request.getParameter("name"));
preparedStatement.setFloat(2, Float.parseFloat(request.getParameter("per")));
preparedStatement.setInt(3, Integer.parseInt(request.getParameter("roll")));
preparedStatement.execute();
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
RequestDispatcher requestDispatcher = request.getRequestDispatcher("/Display");
requestDispatcher.forward(request, response);
}
public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
String fName = req.getParameter("fName");
String lName = req.getParameter("lName");
String party = req.getParameter("party");
String area = req.getParameter("area");
Connection c = null;
try {
DriverManager.registerDriver(new AppEngineDriver());
c =
DriverManager.getConnection(
"jdbc:google:rdbms://netivalimised2013:netivalimised/evalimised");
String statement;
if ((fName.equals("") || fName == null)
&& (lName.equals("") || lName == null)
&& (party.equals("") || party == null)
&& (area.equals("") || area == null)) {
System.out.println("Getting all candidates");
statement =
"SELECT Person.FirstName, Person.LastName, Party.PartyName, Area.AreaName "
+ "FROM Person JOIN Party ON Person.PartyID = Party.Party_Id JOIN Area ON Person.AreaID = Area.Area_Id";
} else statement = createQuery(fName, lName, party, area);
PreparedStatement stmt = c.prepareStatement(statement);
ResultSet rs = stmt.executeQuery();
String jsonData = createJSON(rs, party, area);
resp.setContentType("application/json");
resp.setCharacterEncoding("UTF-8");
resp.getWriter().write(jsonData);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (c != null) {
try {
c.close();
} catch (SQLException ignore) {
}
}
}
// resp.setHeader("Refresh","3; url=/evalimised.jsp");
}
private static String createJSON(ResultSet rs, String party, String area) {
List<Candidate> candidates = new ArrayList<Candidate>();
try {
while (rs.next()) {
Candidate candidate = new Candidate();
candidate.setFName(rs.getString("FirstName"));
candidate.setLName(rs.getString("LastName"));
if (party.equals("") || party == null) {
candidate.setParty(rs.getString("PartyName"));
}
if (area.equals("") || area == null) {
candidate.setArea(rs.getString("AreaName"));
}
candidates.add(candidate);
}
} catch (SQLException e) {
e.printStackTrace();
}
Gson gson = new GsonBuilder().create();
String candidatesJson = gson.toJson(candidates);
return candidatesJson;
}
protected static void clear() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(CLEAR_WARE);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("SQLException in DbTChatRooms.java:clearTChatRooms(): " + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private List<LicenseData> getSearchByFieldResults(
String reseller, String parameter, String type) {
List<LicenseData> list = new ArrayList<LicenseData>();
Connection con = null;
try {
Statement pst = null;
con = getConnectiontoDB();
StringBuffer sql = new StringBuffer();
if (type.equalsIgnoreCase("sno")) {
sql.append(
" select distinct ib.item,o.orderkey,'1',so_header.so_number,so_header.end_user,TO_CHAR(TO_TIMESTAMP(so_header.ship_date/1000), 'YYYY-MM-DD'), ");
} else {
sql.append(
" select distinct so_item.item,so_item.entitlementkey,so_item.quantity,so_header.so_number,so_header.end_user,TO_CHAR(TO_TIMESTAMP(so_header.ship_date/1000), 'YYYY-MM-DD'), ");
}
sql.append(
"o.hmid, CASE TO_CHAR(TO_TIMESTAMP(o.substartdate/1000), 'YYYY-MM-DD') WHEN '1969-12-31' THEN '' WHEN TO_CHAR(TO_TIMESTAMP(o.subenddate/1000), 'YYYY-MM-DD') THEN '' ELSE TO_CHAR(TO_TIMESTAMP(o.substartdate/1000), 'YYYY-MM-DD') END, ");
sql.append(
"CASE TO_CHAR(TO_TIMESTAMP(o.subenddate/1000), 'YYYY-MM-DD') WHEN '1969-12-31' THEN '' WHEN TO_CHAR(TO_TIMESTAMP(o.substartdate/1000), 'YYYY-MM-DD') THEN '' ELSE TO_CHAR(TO_TIMESTAMP(o.subenddate/1000), 'YYYY-MM-DD') END, so_header.po_check_number,so_header.reseller, ");
sql.append(
" CASE WHEN so_item.producttype='Support' THEN TO_CHAR(TO_TIMESTAMP(o.startdate/1000), 'YYYY-MM-DD') ELSE '' END, ");
sql.append(
" CASE WHEN so_item.producttype='Support' THEN TO_CHAR(TO_TIMESTAMP(o.enddate/1000), 'YYYY-MM-DD') ELSE '' END ");
if (type.equalsIgnoreCase("sno")) {
sql.append(" ,ib.serialnumber");
sql.append(
" from ns.so_header so_header inner join ns.ib ib on ib.salesordernumber =so_header.so_number ");
sql.append(" inner join orderkey_information o on so_header.entitlement_key=o.orderkey");
sql.append(
" inner join ns.temp_so_item so_item on so_header.entitlement_key=so_item.entitlementkey");
} else {
sql.append(
" from ns.so_header so_header inner join ns.temp_so_item so_item on so_header.entitlement_key=so_item.entitlementkey ");
sql.append(" inner join orderkey_information o on so_header.entitlement_key=o.orderkey ");
}
if (type.equalsIgnoreCase("sno"))
sql.append(" where ib.serialnumber ILIKE '%" + parameter.trim() + "%' ");
if (type.equalsIgnoreCase("so"))
sql.append("where so_header.so_number='" + parameter.trim() + "' ");
if (type.equalsIgnoreCase("enduser"))
sql.append(
"where so_header.end_user ILIKE '%" + parameter.trim().replace("'", "''") + "%'");
if (type.equalsIgnoreCase("ek"))
sql.append("where so_header.entitlement_key ILIKE '%" + parameter.trim() + "%'");
if (type.equalsIgnoreCase("po"))
sql.append("where so_header.po_check_number ILIKE '%" + parameter.trim() + "%'");
if (type.equalsIgnoreCase("hm"))
sql.append("where o.hmid ILIKE '%" + parameter.trim() + "%'");
if (reseller != null && !reseller.isEmpty() && !reseller.equalsIgnoreCase("%admin%"))
sql.append(" and so_header.reseller ILIKE '" + reseller.trim() + "'");
if (type.equalsIgnoreCase("sno")) sql.append(" order by so_header.so_number desc ");
pst = con.createStatement();
ResultSet rs = pst.executeQuery(sql.toString());
log.info("Search Fields : SQL Query " + sql);
while (rs.next()) {
LicenseData data = new LicenseData();
data.setEntitlementKey(rs.getString(2));
data.setSku(rs.getString(1));
data.setQuantity(rs.getString(3));
data.setSoNumbber(rs.getString(4));
;
data.setEndUser(rs.getString(5));
data.setShipDate(rs.getString(6));
data.setHmId(rs.getString(7));
data.setLicenseStartDate(rs.getString(8));
data.setLicenseEndDate(rs.getString(9));
data.setPoNumber(rs.getString(10));
data.setNumber(rs.getString(4));
data.setBillingCustomer(rs.getString(11));
data.setSupportStartDate(rs.getString(12));
data.setSupportEndDate(rs.getString(13));
if (type.equalsIgnoreCase("sno")) data.setSerialNumber(rs.getString(14));
list.add(data);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
HttpSession session = request.getSession(false);
if (session == null) {
session = request.getSession();
}
PrintWriter out = response.getWriter();
Connection conn = null;
Statement stmt = null;
try {
System.out.println("Enrollno: 130050131067");
// STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
// STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
stmt = conn.createStatement();
// STEP 2: Executing query
String name = "asdf";
String rollno = "34";
String branch = "CSE";
String sql =
"INSERT INTO student(rollno, name, branch) VALUES ('"
+ rollno
+ "', '"
+ name
+ "', '"
+ branch
+ "')";
if (stmt.executeUpdate(sql) != 0) {
out.println("Record has been inserted</br>");
} else {
out.println("Sorry! Failure</br>");
}
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null) conn.close();
} catch (SQLException se) {
} // do nothing
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
} // end finally try
} // end try
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("gb2312");
response.setContentType("text/html; charset=gb2312");
out = response.getWriter();
session = request.getSession();
time = new Time();
str = new Str();
db = new Db();
// 取得
try {
id = Integer.parseInt((String) request.getParameter("id"));
} catch (Exception e) {
id = 0;
}
password = request.getParameter("password");
password = str.inStr(password);
sqlsp = "SELECT * FROM password WHERE employeeid=" + id;
sqlse = "SELECT employeeid FROM eminfo WHERE employeeid=" + id;
sqlu =
"UPDATE password SET time='"
+ time.getYMDHMS()
+ "',password='******' WHERE employeeid="
+ id;
sqli =
"INSERT INTO password(employeeid,password,time) VALUES("
+ id
+ ",'"
+ password
+ "','"
+ time.getYMDHMS()
+ "')";
try {
stmt = db.getStmtread();
rs = stmt.executeQuery(sqlsp);
// 不是第一次设置更新数据库
if (rs.next()) {
db.close();
stmt = db.getStmt();
temp = 0;
temp = stmt.executeUpdate(sqlu);
if (temp > 0) {
request.setAttribute("msg", "设置成功");
} else {
request.setAttribute("msg", "设置失败");
}
db.close();
} else {
// 第一次设置
db.close();
temp = 0;
stmt = db.getStmtread();
rs = stmt.executeQuery(sqlse);
if (rs.next()) {
// id存在
rs.close();
stmt.close();
temp = 0;
stmt = db.getStmt();
temp = stmt.executeUpdate(sqli);
if (temp > 0) {
request.setAttribute("msg", "设置成功");
} else {
request.setAttribute("msg", "设置失败");
}
db.close();
} else {
// id不存在
db.close();
request.setAttribute("msg", "员工序号不存在");
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
RequestDispatcher dispatcher = request.getRequestDispatcher("set1.jsp");
dispatcher.forward(request, response);
}
}
