operationdb.java

一个可以连接Access MySQL Oracle SQL Server ..数据库的bean

//下面对数据库的操作都以Access为例,如果换成其它数据库,对SQL语句做一点改动即可
/*Access 中插入数据时需要在最前端多加一个字段,因为Access自动自生成 自动编号*/

/*
 使用PreparedStatement，而不使用Statement原因有:
 可以防止SQL注入攻击，防止数据库缓冲池溢出，提高代码的可读性，可维护性.

 但两者的效率是要根据不同的sql语句而定的.
*/


//Access数据库名:UserInfo,其中有一个表用于测试,表名:USERINFO,七列:自动编号,ID,PASSWORD,NAME,POSTCODE,TELNUM,EMAIL

import java.sql.*;



public class OperationDB{

		int autoid = 1;
		String id = "7";
		String password = "e";
		String name = "c";
		String postcode = "3";
		String telnum = "ed";
		String email = "com";
		ConnectionDBBean connectionDB = new ConnectionDBBean(ConnectionDBBean.MSACCESSDB);
	public OperationDB(){
		
	}

	



	public boolean insertInfo(){
		Connection connection = null;
		PreparedStatement statement = null;
		String InsertStmt = "INSERT INTO USERINFO VALUES(?,?,?,?,?,?,?)";
		try{
			connection = connectionDB.connectionDB();

			statement = connection.prepareStatement(InsertStmt);
			
			statement.setString(1,(new Integer(autoid)).toString());
			statement.setString(2,id);
			statement.setString(3,password);
			statement.setString(4,name);
			statement.setString(5,postcode);
			statement.setString(6,telnum);
			statement.setString(7,email);
			statement.executeUpdate();
			statement.close();	
			connection.close();

				return true;


		}catch(java.lang.Exception e){
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
			
			System.out.println(e);
			return false;
			
		}finally{
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
		}
		
	
	}

	public boolean deleteInfo(){
		Connection connection = null;
		PreparedStatement statement = null;
		String DeleteStmt = "DELETE FROM USERINFO WHERE ID=?";
		try{
			connection = connectionDB.connectionDB();

			statement = connection.prepareStatement(DeleteStmt);
			statement.setString(1,"2");
			statement.executeUpdate();
			statement.close();	
			connection.close();

				return true;


		}catch(java.lang.Exception e){
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
			
			System.out.println(e);
			return false;
			
		}finally{
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
			
		}
	}

	public boolean updateInfo(){
		Connection connection = null;
		PreparedStatement statement = null;
		String UpdateStmt = "UPDATE USERINFO SET PASSWORD=? WHERE ID=?";
		try{
			connection = connectionDB.connectionDB();

			statement = connection.prepareStatement(UpdateStmt);
			
			statement.setString(1,"OK");
			statement.setString(2,"7");
			statement.executeUpdate();
			statement.close();	
			connection.close();

				return true;


		}catch(java.lang.Exception e){
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
			
			System.out.println(e);
			return false;
			
		}finally{
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
		
		}
	}

	public boolean selectInfo(){
		Connection connection = null;
		PreparedStatement statement = null;
		boolean flag = false;
		String SelectStmt = "SELECT * FROM USERINFO WHERE ID=?";
		try{
			connection = connectionDB.connectionDB();

			statement = connection.prepareStatement(SelectStmt);
			statement.setString(1,id);

			ResultSet rs = statement.executeQuery();
	
			while(rs.next()){
				flag = true;
				System.out.println(rs.getString(3));
			
			}
			
			statement.close();	
			connection.close();


		}catch(java.lang.Exception e){
			try{
				statement.close();	
				connection.close();
			}catch(java.lang.Exception e1){}
			
			System.out.println(e);
			flag = false;
			
		}
		return flag;
	}


	public static void main(String[] args){
	
	
	
	/*	
		
		String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
		//String url = "jdbc:odbc:UserInfo";//使用此方法需要配ODBC数据源
		String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=DB\\UserInfo.mdb";//DBQ后可以是绝对路径也可以是相对路径
		String userName  = "";
		String userPassword = "";
		

	    int autoid = 1;
		String id = "7";
		String password = "e";
		String name = "c";
		String postcode = "3";
		String telnum = "ed";
		String email = "com";

		*/
		//String InsertStmt = "INSERT INTO USERINFO VALUES('1','1','1234','xiaochen','267676','5672443','[e-mail]')";//测试之用
		   

//		int num;

/*-----------------------------------使用Statement类


String InsertStmt = "INSERT INTO USERINFO VALUES('" +autoid+ "','"+id+"','"+password+"','"+name+"','"+postcode+"','"+telnum+"','"+email+"')";
Connection connection = null;
Statement statement = null;
		try{
			Class.forName(driver);
			connection = DriverManager.getConnection(url,userName,userPassword);
			statement = connection.createStatement();
			statement.executeUpdate(InsertStmt);
			statement.close();	
			connection.close();
		}catch(java.lang.ClassNotFoundException cnfe){
			try{
			statement.close();	
			connection.close();
			}catch(SQLException sqle){}
			System.err.println("error loading driver: "+ cnfe);
		}catch(SQLException sqle){
			try{
			statement.close();	
			connection.close();
			}catch(SQLException e){}
			System.err.println("error with connection: "+ sqle);
	    }catch(java.lang.Exception ex) {
			try{
			statement.close();	
			connection.close();
			}catch(SQLException sqle){}
			System.out.println("DB Insert Error2 in UserDataContainer" + ex);
			
		}

*/

/*-----------------------------------使用Statement类

	String InsertStmt = "INSERT INTO USERINFO VALUES(?,?,?,?,?,?,?)";
	Connection connection = null;
	PreparedStatement statement = null;

		try{
			Class.forName(driver);
			connection = DriverManager.getConnection(url,userName,userPassword);

			statement = connection.prepareStatement(InsertStmt);
			
			statement.setString(1,(new Integer(autoid)).toString());
			statement.setString(2,id);
			statement.setString(3,password);
			statement.setString(4,name);
			statement.setString(5,postcode);
			statement.setString(6,telnum);
			statement.setString(7,email);
			statement.executeUpdate();
			statement.close();	
			connection.close();
		}catch(java.lang.ClassNotFoundException cnfe){
			System.err.println("error loading driver: "+ cnfe);
			try{
			statement.close();	
			connection.close();
			}catch(SQLException sqle){}

		}catch(SQLException sqle){
			System.err.println("error with connection: "+ sqle);
			try{
				statement.close();	
				connection.close();
			}catch(SQLException e){}
	    }catch(java.lang.Exception ex) {
			System.out.println("DB Insert Error2 in UserDataContainer" + ex);
			try{
			statement.close();	
			connection.close();
			}catch(SQLException sqle){}
		}






*/






	}

}