JDBC

 * 总结：1，当执行查询操作时用ResultSet，因为查询时候有返回值需要创建一个ResultSet对象来接收查询到的结果， 执行更新，插入，删除操作的时候不需要ResultSet对象。

 * 2，当执行更新，插入，删除这些操作有条件传入的时候由于安全、代码的可读性和可维护性，提高性能来考虑所以选择preparedStatement。  对于只执行一次的SQL语句选择Statement是最好的. 相反, 如果SQL语句被多次执行选用PreparedStatement是最好的.且执行语句中没有输入的操作条件的时候建议用Statement

http://zhidao.baidu.com/link?url=K2bn-KJ6swPX0mhfXzJsWof50r3cBN_mdLAOoFVh2eihAk7EVa_HQeTVlsqtCqgHIMdGEV7MGgtbo3cHckLnWK

http://www.cnblogs.com/yezhenhan/archive/2011/04/21/2023195.html

public class HelperDao {
	static Connection conn;

	static Connection getConn() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String user = "root";
		String password = "root";
		String url = "jdbc:mysql://192.168.0.80:3306/test";
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	static void close() throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}
}

public class UserSQLDao {

	// 查询数据库全部数据
	List<Student> query() throws Exception {
		String sql = "select * from student";
		List<Student> list = new ArrayList<Student>();
		Connection con = (Connection) HelperDao.getConn();
		
		java.sql.Statement sta = con.createStatement();
		ResultSet result = sta.executeQuery(sql);
		while (result.next()) {
			Student student = new Student();
			student.setName(result.getString("name"));
			student.setAge(result.getInt("age"));
			student.setSchool(result.getString("school"));
			list.add(student);
		}
		result.close();
		sta.close();
		con.close();
		return list;
	}
	//根据id查询数据
	public List<Student> queryId(int id){
		String sql = "select * from student where id = ?";
		List<Student> item =new ArrayList<Student>();
		Connection connection = null;
		PreparedStatement ps = null ;
		ResultSet rs= null;
	
		try {
			connection  =(Connection) HelperDao.getConn();
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				Student student = new Student();
				student.setName(rs.getString("name"));
				student.setAge(rs.getInt("age"));
				student.setSchool(rs.getString("school"));
				item.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return item ;
	}
	//根据用户名查询数据
	public List<Student> queryName(Student student){
		String sql = "select * from student where name = ?";
		List<Student>  item = new ArrayList<Student>();
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = (Connection) HelperDao.getConn();
			ps = (PreparedStatement) connection.prepareStatement(sql);
			
			ps.setString(1, student.getName());
			rs = ps.executeQuery();
			while(rs.next()){
				Student student1 = new Student();
				
				student1.setName(rs.getString("name"));
				student1.setAge(rs.getInt("age"));
				student1.setSchool(rs.getString("school"));
				item.add(student1);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs.close();
			ps.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return item ;
	}
	// 插入一条数据
	public int insert(Student student) throws SQLException {
		int i = 0;
		PreparedStatement ps = null;
		Connection connection = null;
		String sql = "insert into student(name,age,school) values(?,?,?)";

		try {
			connection = (Connection) HelperDao.getConn();
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setString(1, student.getName());
			ps.setInt(2, student.getAge());
			ps.setString(3, student.getSchool());
			i = ps.executeUpdate();

		} catch (SQLException e) {

			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ps.close();
			connection.close();
		}
		return i;
	}
	
	//根据id修改数据
	public int update(Student student,int id) throws Exception{
		int i = 0;
		String sql = "update student set name =? ,age = ? , school=? where id =? ";
		Connection connection = null;
		PreparedStatement ps = null;
		connection = (Connection) HelperDao.getConn();
		
		ps = (PreparedStatement) connection.prepareStatement(sql);
		ps.setString(1, student.getName());
		ps.setInt(2, student.getAge());
		ps.setString(3, student.getSchool());
		ps.setInt(4, id);
		i = ps.executeUpdate();
		
		ps.close();
		connection.close();
		return i;
	}
	//根据id删除一条数据
	public int deleteId(int id){
		String url = "delete from student where id = ? ";
		int i =0;
		Connection connection = null;
		PreparedStatement ps =null;
		
		connection = (Connection) HelperDao.getConn();
		try {
			ps = (PreparedStatement) connection.prepareStatement(url);
			ps.setInt(1, id);
			
			i = ps.executeUpdate();
			
		} catch (SQLException e) {
			try {
				ps.close();
				connection.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return i;
	}
}

测试：上面的各个数据库方法：

public class TestJDBC {
	/**
	 * 测试
	 * 
	 */
	public static void main(String[] args) throws Exception {
		UserSQLDao sql = new UserSQLDao();

		// 插入一条数据
//		Student student = new Student();
//		student.setName("张洋");
//		student.setAge(25);
//		student.setSchool("北京");
//		int a = sql.insert(student);
//		System.out.println("插入了"+a+"条数据");
		
		//根据id修改数据
//		Student student = new Student();
//		student.setName("张洋");
//		student.setAge(25);
//		student.setSchool("天津大学");
//		int update = sql.update(student, 4);
//		System.out.println("修改了"+update+"条数据");
		
		//根据用户名查询数据
//		Student student =new  Student();
//		student.setName("张洋");
//		List<Student> list1 = sql.queryName(student);
//		System.out.println(list1);
		
		// 查询数据库表中所有数据
//		List<Student> list = sql.query();
//		System.out.println(list);
	
		//根据id删除一条数据
//		int c = sql.deleteId(2);
//		System.out.println(c);
		
		//根据id查询
		List<Student> list = sql.queryId(1);
		System.out.println(list);
	}

}

实体类

public class Student {
	String name; 
	String school;
	int age;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSchool() {
		return school;
	}
	public void setSchool(String school) {
		this.school = school;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String toString() {
		return "Student [name=" + name + ", school=" + school + ", age=" + age
				+ "]";
	}
	

}