一、连接数据库:
package com.scujcc; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import com.scujcc.Quiz; public class DB { private Connection conn; public void connect() { Context ctx; try { ctx=new InitialContext(); DataSource ds=(DataSource) ctx.lookup("java:comp/env/mypool"); conn=ds.getConnection(); System.out.println("成功获取连接"+conn); } catch(NamingException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); } } /** * 此方法可向数据库中添加一个心理测试题目 * @param quiz 即将添加的测试题目 * @return 若添加成功则返回true,否则返回false */ public int add1(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update csone set c_content=?,c_answer=? where id=1"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } public int add2(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update csone set c_content=?,c_answer=? where id=2"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } public int add3(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update csone set c_content=?,c_answer=? where id=3"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } public int add4(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update csone set c_content=?,c_answer=? where id=4"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } }
package com.scujcc; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import com.scujcc.Quiz; public class DB2 { private Connection conn; public void connect() { Context ctx; try { ctx=new InitialContext(); DataSource ds=(DataSource) ctx.lookup("java:comp/env/mypool"); conn=ds.getConnection(); System.out.println("成功获取连接"+conn); } catch(NamingException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); } } /** * 此方法可向数据库中添加一个心理测试题目 * @param quiz 即将添加的测试题目 * @return 若添加成功则返回true,否则返回false */ public int add1(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update ceshitwo set c_content=?,c_answer=? where id=1"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } public int add2(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update ceshitwo set c_content=?,c_answer=? where id=2"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } public int add3(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update ceshitwo set c_content=?,c_answer=? where id=3"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } public int add4(Quiz quiz) { int result=-1; connect(); try { PreparedStatement ps=conn.prepareStatement("update ceshitwo set c_content=?,c_answer=? where id=4"); ps.setString(1, quiz.getTitle()); ps.setString(2, quiz.getContent()); int count=ps.executeUpdate(); if(count>0) { ResultSet generatedKeys = ps.getGeneratedKeys(); if (generatedKeys.next()) { result = generatedKeys.getInt(1); } } } catch(SQLException e) { e.printStackTrace(); } finally { try { if(null!=conn) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } }
二、servlet:
package com.scujcc; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet implementation class QuizServlet */ @WebServlet("/admin/quiz") public class QuizServlet extends HttpServlet { private static final long serialVersionUID = 54115222L; /** * @see HttpServlet#HttpServlet() */ public QuizServlet() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.getWriter().append("Served at: ").append(request.getContextPath()); DB d=new DB(); d.connect(); response.getWriter().append("数据库连接池测试"); } }
三、
心理测试后台
package com.scujcc; import java.io.IOException; import java.io.PrintWriter; 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.List; 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 com.rui.Choice; /** * Servlet implementation class Ttest1 */ @WebServlet("/admin/ttest1") public class Ttest1 extends HttpServlet { private static final long serialVersionUID = 15424529677L; List choices=new ArrayList(); private void data2() { Connection conn=null; ResultSet rs=null; try { //1连接mysql Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://127.0.0.1:3306/jsp?serverTimezone=GMT"; conn=DriverManager.getConnection(url, "root", "123456"); //2执行sql String sql="select * from csone"; PreparedStatement ps=conn.prepareStatement(sql); rs=ps.executeQuery(); //3读取结果 while(rs.next()) { Choice c= new Choice(); c.setId(rs.getInt("id")); c.setContent(rs.getString("c_content")); c.setAnswer(rs.getString("c_answer")); choices.add(c); } } catch(ClassNotFoundException e) { System.out.println("找不到mysql驱动程序com.jdbc.Driver"); } catch(SQLException e) { e.printStackTrace(); } finally { try { if(rs!=null) { rs.close(); } if(conn!=null) { conn.close(); } } catch(SQLException e) { System.out.print("关闭连接时出错了!"); } } } /** * @see HttpServlet#HttpServlet() */ public Ttest1() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.setCharacterEncoding("GB18030"); response.getWriter().append("Served at: ").append(request.getContextPath()); data2(); request.setAttribute("choices", choices); getServletContext() .getRequestDispatcher("/shanchu1.jsp") .forward(request, response); } /** * @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); String choice=request.getParameter("choice"); int choicei=0; if(null != choice) { choicei=Integer.parseInt(choice); } //根据choicei的值(可能为1,2,3,4。来输出其对应的答案) Choice userChoice=null; for(Object c: choices) { Choice cc=(Choice) c; if(cc.getId()==choicei) { userChoice=cc; break; } } String msg= ""; int[] result = {0,0,0,0}; int y=userChoice.getId(); Delete1 d=new Delete1(); if(y==1) { result[0]=d.add1(); } else if(y==2) { result[1]=d.add2(); } else if(y==3) { result[2]=d.add3(); } else if(y==4) { result[3]=d.add4(); } // if(result[m]>0) // { // msg[m] = "成功添加测试题:" + quiz.getTitle()+"id为:"+result; // } PrintWriter out=response.getWriter(); out.append(msg); request.setAttribute("msg", msg); getServletContext().getAttribute("userCounter"); getServletContext().getRequestDispatcher("/admin/ttest1").forward(request, response); } }
private static final long serialVersionUID = 154529677L; List choices=new ArrayList(); private void data2() { Connection conn=null; ResultSet rs=null; try { //1连接mysql Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://127.0.0.1:3306/jsp?serverTimezone=GMT"; conn=DriverManager.getConnection(url, "root", "123456"); //2执行sql String sql="select * from ceshitwo"; PreparedStatement ps=conn.prepareStatement(sql); rs=ps.executeQuery(); //3读取结果 while(rs.next()) { Choice c= new Choice(); c.setId(rs.getInt("id")); c.setContent(rs.getString("c_content")); c.setAnswer(rs.getString("c_answer")); choices.add(c); } } catch(ClassNotFoundException e) { System.out.println("找不到mysql驱动程序com.jdbc.Driver"); } catch(SQLException e) { e.printStackTrace(); } finally { try { if(rs!=null) { rs.close(); } if(conn!=null) { conn.close(); } } catch(SQLException e) { System.out.print("关闭连接时出错了!"); } } } /** * @see HttpServlet#HttpServlet() */ public Ttest2() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.setCharacterEncoding("GB18030"); response.getWriter().append("Served at: ").append(request.getContextPath()); data2(); request.setAttribute("choices", choices); getServletContext() .getRequestDispatcher("/shanchu2.jsp") .forward(request, response); } /** * @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); String choice=request.getParameter("choice"); int choicei=0; if(null != choice) { choicei=Integer.parseInt(choice); } //根据choicei的值(可能为1,2,3,4。来输出其对应的答案) Choice userChoice=null; for(Object c: choices) { Choice cc=(Choice) c; if(cc.getId()==choicei) { userChoice=cc; break; } } String msg= ""; int[] result = {0,0,0,0}; int y=userChoice.getId(); Delete2 d=new Delete2(); if(y==1) { result[0]=d.add1(); } else if(y==2) { result[1]=d.add2(); } else if(y==3) { result[2]=d.add3(); } else if(y==4) { result[3]=d.add4(); } // if(result[m]>0) // { // msg[m] = "成功添加测试题:" + quiz.getTitle()+"id为:"+result; // } PrintWriter out=response.getWriter(); out.append(msg); request.setAttribute("msg", msg); getServletContext().getAttribute("userCounter"); getServletContext().getRequestDispatcher("/admin/ttest2").forward(request, response); } }
四、主页后台
package com.scujcc; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet implementation class Fhxszhuye2 */ @WebServlet("/admin/fhxszhuye2") public class Fhxszhuye2 extends HttpServlet { private static final long serialVersionUID = 767868767L; /** * @see HttpServlet#HttpServlet() */ public Fhxszhuye2() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.getWriter().append("Served at: ").append(request.getContextPath()); getServletContext() .getRequestDispatcher("/WEB-INF/xszhuye2.jsp") .forward(request, response); } /** * @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); } }