ormdbtil.java

采用java实现的网上商店

package com.ata.java2.shoppingcart;
import java.sql.*;
import java.sql.Date;
import java.util.*;
public class ORMDBtil {
	public ORMDBtil() {
		super();
	}

public Connection getConnection()throws SQLException,
         InstantiationException,IllegalAccessException,
         ClassNotFoundException{
	Connection conn=null;
	Class.forName("com.mysql.jdbc.Driver");
	String url="jdbc:mysql://192.168.2.188:3306/shoppingweb";
	String user="root";
	String password="root";
	conn=DriverManager.getConnection(url,user,password);
	return conn;

}

public ArrayList select(String sql)throws Exception{
	ArrayList result=new ArrayList();
	Connection conn=null;
	Statement stmt=null;
	ResultSet rs=null;
	try{
		conn=getConnection();
		stmt=conn.createStatement();
		rs=stmt.executeQuery(sql);
		while(rs.next()){
			User usr=new User();
			usr.setUserName(rs.getString("userName"));
			usr.setPassWord(rs.getString("passWord"));
			usr.setManiger(rs.getBoolean("isManiger"));
			usr.setMale(rs.getBoolean("isMale"));
			usr.setUserTel(rs.getString("userTel"));
			usr.setUserAddr(rs.getString("userAddr"));
			usr.setUserEmail(rs.getString("userEmail"));
			usr.setUserRegDate(rs.getDate("userRegDate"));
		}	
	}catch(SQLException sqle){
		throw new SQLException("select data ecception:"+sqle.getMessage());
	}catch(Exception e){
		throw new Exception("select data ecception:"+e.getMessage());
	}finally{
		try{
			if(rs!=null)
				rs.close();
		}catch(Exception e){
			throw new Exception("resultset close exeption:"+e.getMessage());
		}
		try{
			if(stmt!=null)
				stmt.close();
		}catch(Exception e){
			throw new Exception("statement close exeption:"+e.getMessage());
		}
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			throw new Exception("connection close exeption:"+e.getMessage());
		}
	}
	return result;
}

public void insert(User usr)throws Exception{
	Connection conn=null;
	PreparedStatement ps=null;
	String sql="insert into user values(?,?,?,?,?,?,?,?)";
	try{
		conn=getConnection();
		ps=conn.prepareStatement(sql);
		
		ps.setString(1,usr.getUserName());
		ps.setString(2,usr.getPassWord()); 
		ps.setBoolean(3,usr.isManiger());
		ps.setBoolean(4,usr.isMale());
		ps.setString(5,usr.getUserTel());
		ps.setString(6,usr.getUserAddr());
		ps.setString(7,usr.getUserEmail());
		ps.setDate(8, (Date) usr.getUserRegDate());
		ps.executeUpdate();
		
	}
	catch(SQLException sqle){
		throw new Exception("insert data ecception:"+sqle.getMessage());
	}finally{
		try{
			if(ps!=null)
				ps.close();
		}catch(Exception e){
			throw new Exception("preparedstatement close exeption:"+e.getMessage());
		}
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			throw new Exception("connection close exeption:"+e.getMessage());
		}
	}
}

public void delete(User usr)throws Exception{
	Connection conn=null;
	PreparedStatement ps=null;
	String sql="delete from user where userName="+usr.getUserName();
	try{
		conn=getConnection();
		ps=conn.prepareStatement(sql);
		ps.executeUpdate();
		
	}
	catch(SQLException sqle){
		throw new Exception("insert data ecception:"+sqle.getMessage());
	}finally{
		try{
			if(ps!=null)
				ps.close();
		}catch(Exception e){
			throw new Exception("preparedstatement close exeption:"+e.getMessage());
		}
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			throw new Exception("connection close exeption:"+e.getMessage());
		}
	}
}

public static void main() throws Exception{
	 ORMDBtil db=new ORMDBtil();
	 String sql="select *from user";
	 ArrayList result=db.select(sql);
	 User usr=(User) result.get(0);
	 System.out.print(usr.getUserName());
}
}