userdao.java

这是个利用J2EE编写的如何利用java语言进行与数据库MySQL连接的程序

package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import po.UserPO;
import util.Tools;

public class UserDAO {
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;
	//logon
	public boolean isLogon(UserPO upo) {
		boolean b = false;
		try {
			con = Tools.getConnection();
			stmt = con.createStatement();
			String sql = "select * from user where name='"+upo.getName()+"' and password = '"+upo.getPassword()+"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null) {
					rs.close();
				}
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return b;
	}
	//check user
	public boolean checkUser(String name) {
		boolean b = false;
		try {
			con = Tools.getConnection();
			stmt = con.createStatement();
			String sql = "select * from user where name= '"+name+"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null) {
					rs.close();
				}
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return b ;
	}
	//regist
	public boolean regist(UserPO upo) {
		boolean b = false;
		if(checkUser(upo.getName())) {
			System.out.println("error ");
			return b;
		}
		try {
			con = Tools.getConnection();
			stmt = con.createStatement();
			String sql ="insert into user values('"+upo.getName()+"','"+upo.getPassword()+"','"+upo.getEmail()+"','"+upo.getAddress()+"')";
			int i = stmt.executeUpdate(sql);
			if(i>0) {
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return b;
	}
	//logon off
	public boolean logonOff(UserPO upo) {
		boolean b =false;
		try {
			con = Tools.getConnection();
			stmt = con.createStatement();
			String sql = "delete from user where name ='"+upo.getName()+"'";
			int i = stmt.executeUpdate(sql);
			if(i>0) {
				b  = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return b;
	}
	//update 
	public boolean updateUser(UserPO upo) {
		boolean b = false;
		try {
			con = Tools.getConnection();
			stmt = con.createStatement();
			String sql = "update user set password='"+upo.getPassword()+"',email='"+upo.getEmail()+"',address='"+upo.getAddress()+"' where name='"+upo.getName()+"'";
			int i = stmt.executeUpdate(sql);
			if(i>0) {
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return b;
	}
	//show all users;
	public List showUser() {
		List l = null;
		try {
			con = Tools.getConnection();
			stmt = con.createStatement();
			String sql="select count(*) from user";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				int i = rs.getInt(1);
				if(i>0) {
					l = new ArrayList();
					rs = stmt.executeQuery("select * from user");
					while(rs.next()) {
						UserPO upo = new UserPO();
						upo.setName(rs.getString(1));
						upo.setPassword(rs.getString(2));
						upo.setEmail(rs.getString(3));
						upo.setAddress(rs.getString(4));
						l.add(upo);
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(stmt!=null) {
					stmt.close();
				}
				if(con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return l;
	}
}