conn.java

jsp开发的在线答疑系统

package chatweb;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.util.Vector;
public class conn
{
	Connection connect=null;
	ResultSet rs=null;
	String sDBDriver="sun.jdbc.odbc.JdbcOdbcDriver";
	String SconnStr="jdbc:odbc:chatdb";
	String user="sasa";
	String passwd="3825";
	public conn()
	{
		try
			{
				//显式地加载 JDBC 驱动程序
				Class.forName(sDBDriver);
			}
			catch(ClassNotFoundException e)
			{
				System.err.println("forname:"+e.getMessage());
			}
			
			

			//ResultSet rs=sql.executeQuery("select * from useTable");
		}
		public ResultSet executeQuery(String sql)
		{
			rs=null;
			try
			{
				connect=DriverManager.getConnection(SconnStr,user,passwd);
				Statement stmt=connect.createStatement();
				rs=stmt.executeQuery(sql);
			}
			catch(SQLException ex)
			{
				System.err.println("execute:"+ex.getMessage());
			}
			return rs;
		}
		public boolean executeOrder(String sql)
		{
		  try
			{
				connect=DriverManager.getConnection(SconnStr,user,passwd);
				Statement stmt=connect.createStatement();
				rs=stmt.executeQuery(sql);
			}	
			catch(SQLException ex)
			{
				System.err.println("executeOr:"+ex.getMessage());
				return false;
			}
			return true;
		}
		public int register(String regname,String password,String name,int sex,int age,int tel,String address,String email)
		{
			String strSQL="select *from userTable where username='"+regname+"'";
			ResultSet rs=executeQuery(strSQL);
			try
			{
				if(rs.next())
				return 2;//用户名已存在
			}
			catch(Exception e)
			{
				System.err.println("REGISTER:"+e.getMessage());
				return 3;//插入异常
			}
			String strIns="insert into userTable(username,password,name,sex,age,tel,address,email)Values('"+regname+"','"+password+"','"+name+"','"+sex+"','"+age+"','"+tel+"','"+address+"','"+email+"')";
			 
			 try
			 {
			 	connect=DriverManager.getConnection(SconnStr);
			 	Statement stmt=connect.createStatement();
			 	stmt.executeQuery(strIns);
			 }
			 catch(SQLException ex)
			 {
			 	System.err.println("REGISTER:"+ex.getMessage());
				return 3;//插入异常
			 }
			 return 1;  //注册成功
		}
		public int login(String logname,String password)
		{
			try
			{
				String strSQL="select * from userTable where username='"+logname+"'";
				ResultSet rs=executeQuery(strSQL);
				if(rs.next())
				{
					if(!password.equals(rs.getString("password")))
					return -1;// 密码错误
				    //if(!logname.equals(rs.getString("username")));
				//	return -2;//用户名错误
					else
					{
						executeOrder("update username set lasttime=now() where username='"+logname+"';");
						return rs.getInt("grade");
					}
					
					
				}
				else 
				return -2;
			}
			catch(Exception e)
			{
				System.err.println("LOGIN:"+e.getMessage());
				return -3;//数据库操作异常
			}
			
		}
		public boolean modify(String username,String oldPassword,String newPassword)
		{
			String strSQL="update userTable set password='"+newPassword+"' where username='"+username+"' and password='"+oldPassword+"' and not grade=1";
	         
	         try
	         {
	         	connect=DriverManager.getConnection(SconnStr,user,passwd);
	         	Statement stmt=connect.createStatement();
	         	int updateCount=stmt.executeUpdate(strSQL);
	         	if(updateCount>0)
	         	return true;
	         }
	         catch(SQLException ex)
	         {
	         	System.err.println("EXECUTE:"+ex.getMessage());
	         }
	         return false;
		}
}