Java 调查问卷简易_调查问卷_

package com.sql; 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 com.object.OPT; import com.object.Question; import com.object.Questionnaire; import com.object.user; public class sql { public Connection Con() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/questionnairesystem","root","zxy486426..."); return con; } public int register(user u) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("insert into user(uid,uname,upw,ustate) value(?,?,?,?)"); ps.setObject(1, u.getUid()); ps.setObject(2, u.getUname()); ps.setObject(3, u.getUpw()); ps.setObject(4, u.getUstate()); int row =ps.executeUpdate(); con.close(); ps.close(); return row; } public user login(int id) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select * from user where uid=?"); ps.setObject(1, id); ResultSet rs=ps.executeQuery(); user u=null; while(rs.next()) { u=new user(); u.setUid(rs.getInt("uid")); u.setUname(rs.getString("uname")); u.setUpw(rs.getString("upw")); u.setUstate(rs.getString("ustate")); } con.close(); ps.close(); rs.close(); return u; } public int addQ(String qname,int qnid,String[] os,List<Integer> date) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("insert into questionnaire(qname,questionId,type) value(?,?,0)"); ps.setObject(1, qname); ps.setObject(2, qnid); ps.executeUpdate(); ps=con.prepareStatement("select qid from questionnaire where qname=?"); ps.setObject(1, qname); ResultSet rs=ps.executeQuery(); int qid=0; while(rs.next()) { qid=rs.getInt("qid"); } con.close(); ps.close(); rs.close(); return addO(qid,os,date); } public int addQB(String qname,int qnid,List<Integer> date) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("insert into questionnaire(qname,questionId,type) value(?,?,1)"); ps.setObject(1, qname); ps.setObject(2, qnid); ps.executeUpdate(); ps=con.prepareStatement("select qid from questionnaire where qname=?"); ps.setObject(1, qname); ResultSet rs=ps.executeQuery(); int qid=0; while(rs.next()) { qid=rs.getInt("qid"); } con.close(); ps.close(); rs.close(); return 1; } public int addQB2(String qname,int qnid,int qids) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("insert into questionnaire(qname,questionId,type) value(?,?,1)"); ps.setObject(1, qname); ps.setObject(2, qnid); ps.executeUpdate(); ps=con.prepareStatement("select qid from questionnaire where qname=?"); ps.setObject(1, qname); ResultSet rs=ps.executeQuery(); int qid=0; while(rs.next()) { qid=rs.getInt("qid"); } ps=con.prepareStatement("update opt set qid=? where qid =?"); ps.setObject(1, qid); ps.setObject(2, qids); ps.executeUpdate(); con.close(); ps.close(); rs.close(); return 1; } public int addQN(String qname,List<Integer> date) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("insert into question(name) value(?)"); ps.setObject(1, qname); ps.executeUpdate(); ps=con.prepareStatement("select id from question where name=?"); ps.setObject(1, qname); ResultSet rs=ps.executeQuery(); int qid=0; while(rs.next()) { qid=rs.getInt("id"); } con.close(); ps.close(); rs.close(); return qid; } public int addO(int qid,String[] os,List<Integer> date) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("insert into OPT(oid,oname,ochoice,qid) values(?,?,?,?)"); int row=0; for(int i=0;i<os.length;i++) { ps.setObject(1, (i+1)); ps.setObject(2, os[i]); if(date==null) { ps.setObject(3, 0); }else { if(i<=date.size()-1) { ps.setObject(3, date.get(i)); }else { ps.setObject(3, 0); } } ps.setObject(4, qid); row+=ps.executeUpdate(); } con.close(); ps.close(); return row; } public int seleQ(String qname) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select qid from questionnaire where qname=?"); ps.setObject(1, qname); ResultSet rs=ps.executeQuery(); int qid=0; while(rs.next()) { qid=rs.getInt("qid"); } con.close(); ps.close(); rs.close(); return qid; } public int seleQN(String qname) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select id from question where name=?"); ps.setObject(1, qname); ResultSet rs=ps.executeQuery(); int qid=0; while(rs.next()) { qid=rs.getInt("id"); } con.close(); ps.close(); rs.close(); return qid; } public List<Question> allQ() throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select * from questionnaire"); ResultSet rs=ps.executeQuery(); List<Question> qs=new ArrayList<Question>(); Question q=null; while(rs.next()) { q=new Question(); q.setQid(rs.getInt("qid")); q.setQname(rs.getString("qname")); q.setQuestionid(rs.getInt("questionid")); qs.add(q); } con.close(); ps.close(); rs.close(); return allO(qs); } public List<Question> selectQ(int id) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select * from questionnaire where questionId=?"); ps.setObject(1, id); ResultSet rs=ps.executeQuery(); List<Question> qs=new ArrayList<Question>(); Question q=null; while(rs.next()) { q=new Question(); q.setQid(rs.getInt("qid")); q.setQname(rs.getString("qname")); q.setQuestionid(rs.getInt("questionid")); q.setType(rs.getInt("type")); qs.add(q); } con.close(); ps.close(); rs.close(); return allO(qs); } public List<Question> selectQ0(int id) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select * from questionnaire where questionId=? and type=0"); ps.setObject(1, id); ResultSet rs=ps.executeQuery(); List<Question> qs=new ArrayList<Question>(); Question q=null; while(rs.next()) { q=new Question(); q.setQid(rs.getInt("qid")); q.setQname(rs.getString("qname")); q.setQuestionid(rs.getInt("questionid")); qs.add(q); } con.close(); ps.close(); rs.close(); return allO(qs); } public List<Question> selectQ1(int id) throws ClassNotFoundException, SQLException { Connection con=this.Con(); PreparedStatement ps=con.prepareStatement("select * from questionnaire where questionId=? and type=1"); ps.setObject(1, id); ResultSet rs=ps.executeQuery(); List<Question> qs=new ArrayList<Question>(); Question q=null; while(rs.next()) { q=new Question(); q.setQid(rs.getInt("qid")); q.setQname(rs.getString("qname")); q.setQuestionid(rs.getInt("questionid")); qs.add(q); } con.close(); ps.close(); rs.close(); return qs; } public List<Questionnaire> allQN() throws C