databaseconnection.java

jsp页面登陆后,实现对Sybase数据库的增删改查的访问.

package com.pack;
import java.io.*;
import java.sql.*;
import java.util.*;


	

//	连接数据库的工具类。
	public class DataBaseConnection {
       
		public DataBaseConnection() {
		}

		/**
		 * 一个静态方法，返回一个数据库的连接。 这样达到了对数据库连接统一控制的目的。
		 * 
		 * @throws SQLException
		 */
		public static Connection getConnection() throws SQLException {
			Properties sysProps;
			sysProps = System.getProperties();
			sysProps.put("user", "sa"); // 设置数据库访问用户名
			sysProps.put("password", ""); // 密码
			try {
				Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
				System.out.println("connection ok");
			    	
			} catch (ClassNotFoundException cf) {
				cf.printStackTrace();
				System.out.println("CLASS.forname()");
			}
			try {
				System.out.println("ok");
				return DriverManager.getConnection(
						"jdbc:sybase:Tds:192.168.0.69:5000/sang?charset=eucgb&jconnect_version=6", sysProps);
				//System.out.println("ok2");
							
			} catch (SQLException sql) {
				throw sql;
			}
		}

		public   void close(Object o) {
			if (o != null) {
				try {
					if (o instanceof ResultSet) {
						((ResultSet) o).close();
					} else if (o instanceof PreparedStatement) {
						((PreparedStatement) o).close();
					} else if (o instanceof Statement) {
						((Statement) o).close();
					} else if (o instanceof Connection) {
						((Connection) o).close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			}
		}
		
		//成员信息输出
		public List getGuestList () throws SQLException
		{   
			Connection con = null;
			con = DataBaseConnection.getConnection();
			
			Statement statement = null;
			statement = con.createStatement();
			List guestList = new ArrayList();
			ResultSet results = statement.executeQuery("SELECT Uname,Utelnum,Usex FROM UserInfo");
			while (results.next())
			{
				Guestbean guest = new Guestbean();
				guest.setname(results.getString(1));
				guest.setetelenum(results.getString(2));
				guest.setUsex(results.getString(3));
				
				guestList.add(guest);
			}
			return guestList;
		}
		
		//登陆用户名和密码的取得
		public  int getLoginData (String x,String y) throws SQLException
		{   
			Connection con = null;
			con = DataBaseConnection.getConnection();
			
			Statement statement = null;
			statement = con.createStatement();
			
			String sql="SELECT Uidentify FROM LoginTable where Uname = '"+x+"' and Upassword ='"+y+"'";
			System.out.println("sql========"+sql);
			ResultSet results = statement.executeQuery(sql);
			
			if (results.next())
			{
				System.out.println("getint========"+results.getInt(1));
				return results.getInt(1);	
			}else{
			return 0;}
			 
			
		}

		 public void addGuest(Guestbean guest){
			try{
			 System.out.println("+++++++++++++++++++");
			 Connection con = null;
				con = DataBaseConnection.getConnection();
			 Statement statement = null;
				statement = con.createStatement();
			 statement.executeUpdate("INSERT INTO UserInfo ( Uname,Utelnum,Usex) VALUES ('"+guest.getname()+"','"+ guest.getetlenum()+"','"+ guest.getUsex()+"')");
			 System.out.println("++++++++"+statement);
			}catch(Exception e){
				System.out.println(e);
			}
		 }
		
		 public void delGuest(String name){
				try{
				 System.out.println("+++++++++++++++++++");
				 Connection con = null;
					con = DataBaseConnection.getConnection();
				 Statement statement = null;
					statement = con.createStatement();
				 statement.executeUpdate("delete from UserInfo where Uname = '"+name+"'");
				 System.out.println("++++++++"+statement);
				}catch(Exception e){
					System.out.println(e);
				}
			 }
		/* public static void main(String []args){
			Connection con = null;
			Statement stmt = null;
			ResultSet rst = null;
			try {
				con = DataBaseConnection.getConnection();
				stmt = con.createStatement();
				rst = stmt.executeQuery("select * from DAZHE_leibie");
				while(rst.next()){
					System.out.println("name=="+rst.getString("firstname"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				try {
					System.out.println("connection is 1  "+con);
					if(rst!=null)
					rst.close();
					if(stmt!=null)
					stmt.close();
					if(con!=null)
					con.close();
				} catch (RuntimeException e) { 
					e.printStackTrace();
				} catch(SQLException e){
					e.printStackTrace();
				}
				System.out.println("connection is 2  "+con);
			}
		}*/
	}