ormdbuser.java

采用java实现的网上商店

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

public Connection getConnection(){
	Connection conn=null;
	String url="jdbc:mysql://192.168.2.188:3306/shoppingweb?useUnicode=true&characterEncoding=gbk";
	String user="root";
	String password="root";
	try{
	Class.forName("com.mysql.jdbc.Driver");
	conn=DriverManager.getConnection(url,user,password);
	}catch(Exception e){
		e.printStackTrace();
	}
	return conn;
  
}

public ArrayList select(String sql){
	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.setManager(rs.getBoolean("isManager"));
			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"));
			usr.setRemainMoney(rs.getDouble("remainMoney"));
			result.add(usr);
		}	
	} catch(Exception e){
		e.printStackTrace();
	}finally{
		try{
			if(rs!=null)
				rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		try{
			if(stmt!=null)
				stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	return result;
}

public void insert(User usr){
	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,false);
		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.setDouble(9, usr.getRemainMoney());
		ps.executeUpdate();
		
	}
	catch(Exception e){
		e.printStackTrace();
	}finally{
		try{
			if(ps!=null)
				ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
 
public void delete(User usr){
	String name=usr.getUserName();
	String pass=usr.getPassWord();
	Connection conn=null;
	PreparedStatement ps=null;
	String sql="delete from user where userName=? AND passWord=?";
	try{   
		conn=getConnection();
		ps=conn.prepareStatement(sql);
		ps.setString(1,name);
		ps.setString(2, pass);
		ps.executeUpdate();
		}
	catch(Exception e){
		e.printStackTrace(); 
	}finally{
		try{
			if(ps!=null)
				ps.close();
		}catch(Exception e){
			e.printStackTrace();
		} 
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
} 


public void update(User usr){
	boolean ismanager=usr.isManager();
	boolean ismale=usr.isMale();
	String tel=usr.getUserTel();
	String addr=usr.getUserAddr();
	String email=usr.getUserEmail();
	Date date=(Date )usr.getUserRegDate();
	double money=usr.getRemainMoney();
	String name=usr.getUserName();
	Connection conn=null;
	PreparedStatement ps=null;
	String sql="update user set isManager=?,isMale=?,userTel=?,userAddr=?,userEmail=?,userRegDate=?,remainMoney=? where userName=?";
	try{ 
		conn=getConnection();
		ps=conn.prepareStatement(sql); 
		ps.setBoolean(1,ismanager);
		ps.setBoolean(2,ismale);
		ps.setString(3,tel);
		ps.setString(4,addr);
		ps.setString(5,email);
		ps.setDate(6,date);
		ps.setDouble(7,money);
		ps.setString(8, name);
		
		ps.executeUpdate();
		}
	catch(Exception e){
		e.printStackTrace();  
	}finally{
		try{
			if(ps!=null)
				ps.close();
		}catch(Exception e){
			e.printStackTrace();
		} 
		try{
			if(conn!=null)
				conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}   
}   

public static void main(String[] args) {
	 ORMDBUser dbu=new ORMDBUser();
	 String sql="select *from user";
	 ArrayList list=dbu.select(sql);
	 User usr = (User)list.get(0);
   	 System.out.println("用户名："+usr.getUserName());
   	 System.out.println("密码："+usr.getPassWord()); 
   	
   	 usr.setPassWord("ggg");
   	 usr.setUserName("gggjj");
   	 System.out.println("用户名："+usr.getUserName());
   	 System.out.println("密码："+usr.getPassWord()); 
   	
	 dbu.insert(usr);
}    
}