userdaoimpl.java

jdbc个人编写的通讯录

package com.sunny.address.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sunny.address.po.Friend;
import com.sunny.address.po.User;
import com.sunny.address.util.JDBC_Util;

public class UserDaoImpl implements UserDao {

	public void moidfy(Friend friend) {
		String sql="update friend_yy set name=?,gender=? where id=?";
		Connection connection=JDBC_Util.getConnection();
		PreparedStatement preparedStatement=null;
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1, friend.getName());
			preparedStatement.setString(2, friend.getGender());
			preparedStatement.setInt(3, friend.getId());
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		JDBC_Util.close(null,preparedStatement , connection);
	}

	public User login(String name, String password) {
		String sql="select * from user_yy u where u.name=? and u.passward=?";
		Connection connection=JDBC_Util.getConnection();
		ResultSet resultSet=null;
		PreparedStatement preparedStatement=null;
		User user =new User();
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				user.setId(resultSet.getInt("id"));
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("passward"));
				user.setGender(resultSet.getString("gender"));
			}
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC_Util.close(resultSet, preparedStatement, connection);
		return user;
	}

	public void regist(User user) {
		String sql="insert into user_yy(name,passward,gender)values(?,?,?)";
		Connection connection=JDBC_Util.getConnection();
		
		PreparedStatement preparedStatement=null;
		try{
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getGender());
			preparedStatement.execute();
			connection.commit();
			
		}catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		JDBC_Util.close(null,preparedStatement , connection);
	}

	public void addFriend(Friend friend) {
		String sql="insert into friend_yy(name,gender,uid) values(?,?,?)";
		Connection connection=JDBC_Util.getConnection();
		PreparedStatement preparedStatement=null;
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1, friend.getName());
			preparedStatement.setString(2, friend.getGender());
			preparedStatement.setInt(3, friend.getUid());
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		JDBC_Util.close(null, preparedStatement, connection);
	}

	public List listAllFriend(User user) {
		String sql="select * from friend_yy where uid=?";
		Connection connection=JDBC_Util.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		List list =new ArrayList();
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, user.getId());
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				Friend friend =new Friend();
				friend.setId(resultSet.getInt("id"));
				friend.setName(resultSet.getString("name"));
				friend.setGender(resultSet.getString("gender"));
				friend.setUid(resultSet.getInt("uid"));
				list.add(friend);
			}
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		JDBC_Util.close(resultSet, preparedStatement, connection);
		return list;
	}

	public void deleteFriend(int id) {
		String sql="delete from friend_yy where id=?";
		Connection connection=JDBC_Util.getConnection();
		PreparedStatement preparedStatement=null;
		
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			preparedStatement.execute();
			
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		JDBC_Util.close(null, preparedStatement, connection);
	}

	public List findByname(String name ,int id) {
		
		String sql="select * from friend_yy where name=? and uid=?";
		Connection connection=JDBC_Util.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		List list =new ArrayList();
		
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, id);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				Friend friend =new Friend();
				friend.setId(resultSet.getInt("id"));
				friend.setName(resultSet.getString("name"));
				friend.setGender(resultSet.getString("gender"));
				friend.setUid(resultSet.getInt("uid"));
				list.add(friend);
			}
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		JDBC_Util.close(resultSet, preparedStatement, connection);
		return list;
	
	}

	public Friend findById(int id) {
		String sql="select * from friend_yy where id=?";
		Connection connection=JDBC_Util.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		Friend friend =new Friend();
		try {
			connection.setAutoCommit(false);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				friend.setGender(resultSet.getString("gender"));
				friend.setName(resultSet.getString("name"));
				friend.setUid(resultSet.getInt("uid"));
				friend.setId(id);
			}
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return friend;
	}

}