简单的servlet+jsp+mysql结合的管理系统例子

package com.lxn.manager2; /** * @author ：lin xiangnan * @date ：2014年7月28日 下午5:12:12 * @function： */ import java.sql.*; import java.util.ArrayList; public class MySql { private static String driver ="com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/mydb"; private static String mysql_name = "root"; private static String mysql_password = "lxn1992"; private static Connection connect=null; private static Statement stmt=null; private static PreparedStatement pstmt=null; String tableName=""; String result=""; String result2=""; public MySql() throws SQLException { // TODO Auto-generated constructor stub initSql(); } //加载MYSQL JDBC驱动程序 ------------------------------------------------------------------------------------------------ private static void initSql() throws SQLException { // TODO Auto-generated method stub try { Class.forName(driver); //加载MYSQL JDBC驱动程序 // Class.forName("org.gjt.mm.mysql.Driver"); // System.out.println("Success loading Mysql Driver!"); } catch (Exception e) { System.out.print("Error loading Mysql Driver!"); e.printStackTrace(); } } //连接数据库(自动方式)----------------------------------------------------------------------------------------------------------------- private void connectSql() throws SQLException{ try { connect = DriverManager.getConnection( url,mysql_name,mysql_password); //连接URL为 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆用户名和密码 System.out.println("连接数据库"); } catch (Exception e) { System.out.print("get data error!"); e.printStackTrace(); } } //断开数据库连接------------------------------------------------------------------------------------------------------------ private void notConnectSql(Connection connect) throws SQLException{ connect.close(); System.out.println("关闭数据库连接"); } //验证用户登录的用户名密码------------------------------------------------------------------------------------------------- public boolean check_login(String username, String password) throws SQLException { // TODO Auto-generated method stub //连接数据库 this.connectSql(); //在mysql中的usertable表中验证 stmt=connect.createStatement(); ResultSet rs= stmt.executeQuery("select password from usertable where username= '"+username+"'"); while(rs.next()){ if(password.equals(rs.getString("password"))){ //System.out.println(rs.getString("password")); //System.out.println("登录验证成功"); rs.close();stmt.close(); this.notConnectSql(connect); return true; } } //System.out.println("登录验证失败，密码输入错误"); rs.close();stmt.close(); this.notConnectSql(connect); return false; } //创建新用户------------------------------------------------------------------------------------------------------------------ public boolean addUser(String username, String name, String password, String age) throws Exception { // TODO Auto-generated method stub int age2=Integer.valueOf(age); int id=this.getUserNum()+1; //添加JDBC事务管理 try{ //建立非自动连接 this.connectSql(); connect.setAutoCommit(false); //添加用户到usertable中 pstmt=connect.prepareStatement("insert into usertable (id,username,name,password,age)values(?,?,?,?,?) "); pstmt.setInt(1, id); pstmt.setString(2, username); pstmt.setString(3, name); pstmt.setString(4, password); pstmt.setInt(5, age2); pstmt.execute(); //添加用户到usershowtable中 pstmt=connect.prepareStatement("insert into usershowtable (id,username,name,age)values(?,?,?,?) "); pstmt.setInt(1, id); pstmt.setString(2, username); pstmt.setString(3, name); pstmt.setInt(4, age2); pstmt.execute(); //添加用户到username中 pstmt=connect.prepareStatement("insert into username (id,username)values(?,?) "); pstmt.setInt(1, id); pstmt.setString(2, username); pstmt.execute(); //事务提交 connect.commit(); //注册成功 System.out.println("用户信息创建成功"); return true; }catch(Exception e1){ System.out.println("用户信息创建失败"); System.out.println(e1); try{ connect.rollback(); }catch(Exception e2){ System.out.println(e2); return false; }finally{ pstmt.close(); this.notConnectSql(connect); } return false; } //检测用户名是否已注册------------------------------------------------------------------------------------------------- } public boolean check_username(String username) throws SQLException { // TODO Auto-generated method stub this.connectSql(); stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery("select * from usertable where username= '"+username+"'"); while(rs.next()){ if (rs.getInt("username")!=0){ //重复 this.notConnectSql(connect); return false; } } this.notConnectSql(connect); return true; } //获得列表名----------------------------------------------------------------------------------------------------------------- public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } //表格输出------------------------------------------------------------------------------------------------------------------- //表格输出（附带操作，针对管理员输出） public String getResult() { if(!tableName.equals("")){ Connection con=null; Statement stm=null; ResultSet rs=null; StringBuffer buffer=new StringBuffer(); try{ this.connectSql(); stm=connect.createStatement(); String cmd="select * from "+tableName; rs=stm.executeQuery(cmd); int colNum=rs.getMetaData().getColumnCount();//表格列数 buffer.append("<center>"); buffer.append("<form method='get' action='InformManage'>"); buffer.append("<table border=1 cellpadding=5>"); buffer.append("<tr>"); int i=1; while(true){ String name=rs.getMetaData().getColumnName(i).toString(); buffer.append("<th>"+name+"</th>"); i++; if(i>colNum) break; } //-------------------------测试start---------------------------------------- buffer.append("<th>"+"操作栏"+"</th>"); //--------------------------测试end---------------------------------------- buffer.append("</tr>"); int j=1; while(rs.next()){ buffer.append("<tr>"); for(i=1;i<=colNum;i++){ buffer.append("<td align='center'>"+rs.getString(i)+"</td>"); } //-------------------------测试start---------------------------------------- buffer.append("<td>"+"<input type='submit' name='look"+j+"' value='查看'>"+"<input type='submit' name='change"+j+"' value='修改'>"+"<input type='submit' name='delete"+j+"' value='删除'>"+"</td>"); j++; //-------------------------测试end---------------------------------------- buffer.append("</tr>"); } buffer.append("</table>"); buffer.append("</form>"); buffer.append("</center>"); }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(rs!=null&&stm!=null&&con!=null){ rs.close(); stm.close(); this.notConnectSql(con); } }catch(Exception e){ e.printStackTrace(); } } result=new String(buffer); } return resul