使用jdbc对MySQL表完成增删改查

//使用jdbc对MySQL表完成增删改查
//工具 myEclipse 以及mysql数据库
//第一步 建立接口 写上抽象方法
public interface UserDao {
public boolean addUser(User user);
public boolean updateUser(User user);
public boolean deleteUser(User user);
public User findById(User user);
public List<User> findAll();
}
//第二步 建立实体模型 写上set和get方法 以及toString方法
public class User {
private int id;
private String  name;
private int age;
private String  sex;
private String  address;
public int getId() {
	return id;
}
public void setId(int id) {
	this.id = id;
}
public String getName() {
	return name;
}
public void setName(String name) {
	this.name = name;
}
public int getAge() {
	return age;
}
public void setAge(int age) {
	this.age = age;
}
public String getSex() {
	return sex;
}
public void setSex(String sex) {
	this.sex = sex;
}
public String getAddress() {
	return address;
}
public void setAddress(String address) {
	this.address = address;
}
@Override
public String toString() {
	return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="
			+ sex + ", address=" + address + "]";
}
}
//第三部 写上工具类 连接以及关闭数据库方法
public class BDao {
private Connection con;

public Connection getCon()
{
	try {
		Class.forName("com.mysql.jdbc.Driver");//加载MySQL连接驱动
	    con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "root");
		
		
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return con;
}

public void closeCon(Connection con,PreparedStatement psmt,ResultSet rs)
{
	try {
		if(rs!=null)
		{
			rs.close();
		} 
		if(psmt!=null)
		{
			psmt.close();
		} 
		if(con!=null)
		{
			con.close();
		} 
	} catch (Exception e) {
		// TODO: handle exception
	}
}
//public static void main(String[] args) {
//	System.out.println(new BDao().getCon());//如果打印出地址则代表已连接数据库
//}
}
//第四步 实现接口
public class UserDaoImp implements UserDao{
	private Connection con=null;
	private PreparedStatement psmt=null;
	private ResultSet rs=null;
	@Override
	public boolean addUser(User user) {
		BDao bd=new BDao();
		con=bd.getCon();
//		System.out.println(con);
		boolean flag=false;
		try {
			psmt=con.prepareStatement("insert into users(id,name,age,sex,address) values(?,?,?,?,?)");
			psmt.setInt(1, user.getId());
		    psmt.setString(2, user.getName());
			psmt.setInt(3, user.getAge());
			psmt.setString(4, user.getSex());
			psmt.setString(5, user.getAddress());
			int num=psmt.executeUpdate();
			if(num>0)
			{
				flag=true;
			}
		
		} catch (Exception e) {
			// TODO: handle exception
		}finally
		{
			bd.closeCon(con,psmt,rs);
		}
	
		return flag;
	}

	@Override
	public boolean updateUser(User user) {
		BDao bd=new BDao();
		con=bd.getCon();
		boolean flag=false;
		try {
			psmt=con.prepareStatement("update users set name=?,age=?,sex=?,address=? where id=?");
			
		    psmt.setString(1, user.getName());
			psmt.setInt(2, user.getAge());
			psmt.setString(3, user.getSex());
			psmt.setString(4, user.getAddress());
			psmt.setInt(5, user.getId());
			int num=psmt.executeUpdate();
			if(num>0)
			{
				flag=true;
			}
		
		} catch (Exception e) {
			// TODO: handle exception
		}finally
		{
			bd.closeCon(con,psmt,rs);
		}
	
		return flag;
	}

	@Override
	public boolean deleteUser(User user) {
		BDao bd=new BDao();
		con=bd.getCon();
		boolean flag=false;
		try {
			psmt=con.prepareStatement("delete from users where id=?");
			psmt.setInt(1, user.getId());
			int num=psmt.executeUpdate();
			if(num>0)
			{
				flag=true;
			}
		
		} catch (Exception e) {
			// TODO: handle exception
		}finally
		{
			bd.closeCon(con,psmt,rs);
		}
	
		return flag;
	}

	@Override
	public User findById(User user)  {
		BDao bd=new BDao();
		con=bd.getCon();
			try {
				psmt=con.prepareStatement("select*from users where id=?");
				psmt.setInt(1, user.getId());
				rs=psmt.executeQuery();
				System.out.println(rs.next());
				if(rs.next())
				{
					user.setId(rs.getInt("id"));
					user.setName(rs.getString("name"));
					user.setAge(rs.getInt("age"));
					user.setSex(rs.getString("sex"));
					user.setAddress(rs.getString("address"));
					
				}
				System.out.println(user);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		finally{
			bd.closeCon(con,psmt,rs);
		}
		return user;
	}

	@Override
	public List<User> findAll() {
		  List<User> list=new ArrayList<User>();
		  BDao bd=new BDao();
		  User user=null;
		try {
		    con=bd.getCon();
		    String sql="select * from users";
			psmt=con.prepareStatement(sql);
			rs=psmt.executeQuery();
	//System.out.println(psmt);
			while(rs.next())
			{
				user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getString("sex"));
				user.setAddress(rs.getString("address"));
				list.add(user);
				
			}

		} catch (Exception e) {
			// TODO: handle exception
		}finally
		{
			bd.closeCon(con,psmt,rs);
		}
	
		return list;
	}

}
//第五步 测试

public class UserTest {
public static void main(String[] args) throws Exception {
	User user=new User();
	UserDaoImp ud=new UserDaoImp();
	while(true){
	System.out.println("欢迎来到用户信息管理系统！");
	System.out.println("请选择您要的操作：1.增加信息2修改信息3删除信息4查询单个信息5查询所以信息");
	Scanner sc=new Scanner(System.in);
	int a=sc.nextInt();
	switch(a)
	{
	case 1:
		System.out.println("请输入姓名：");
		user.setName(sc.next());
		System.out.println("请输入年龄：");
		user.setAge(sc.nextInt());
		System.out.println("请输入性别：");
		user.setSex(sc.next());
		System.out.println("请输入地址：");
		user.setAddress(sc.next());
		ud.addUser(user);
		break;
	case 2:
		System.out.println("请输入要修改的编号：");
		user.setId(sc.nextInt());
		System.out.println("请输入修改后的姓名：");
		user.setName(sc.next());
		System.out.println("请输入修改后的年龄：");
		user.setAge(sc.nextInt());
		System.out.println("请输入修改后的性别：");
		user.setSex(sc.next());
		System.out.println("请输入修改后的地址：");
		user.setAddress(sc.next());
		
		ud.updateUser(user);
		break;
	case 3:
		System.out.println("请输入要删除的编号：");
		user.setId(sc.nextInt());
		ud.deleteUser(user);
		break;
	case 4:
		System.out.println("请输入要查询的编号：");
		int b=sc.nextInt();
		user.setId(b);
		
		
		ud.findById(user);
		break;
	case 5:
		  List list=ud.findAll();
		    Iterator it=list.iterator();
		    while(it.hasNext())
		    {
		    	System.out.println(it.next());
		    }
		 break;
	default:
		break;
	}

	}
}
}

搞定！