JDBC笔记（二）

抽取JDBCUtil和设计DAO包

设计DAO包

1.DAO接口

public interface IStudentDao {
	//添加信息
	public boolean save(Student stu);
	//根据ID删除信息
	public boolean delete(Integer id);
	//根据ID查找信息
	public Student get(Integer id);
	//根据ID修改信息
	public boolean update(int id,Student stu);
	//得到所有信息
	public List<Student> getAll();
}

2.实现DAO接口

public class IStudentDaoImpl implements IStudentDao {

	@Override
	public boolean save(Student stu) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//建立数据库连接
			conn = JDBCUtil.getConn();
			//3.编写sql语句
			String sql="insert into stu value(?,?,?)";
			//4.执行sql语句
			ps = conn.prepareStatement(sql);//预编译
			ps.setInt(1, stu.getId());
			ps.setString(2, stu.getName());
			ps.setInt(3, stu.getAge());
			int row = ps.executeUpdate();
			System.out.println(row);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally{
			try {
				JDBCUtil.close(conn, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public boolean delete(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean flag = false;
		try {
			//2.得到连接对象
			conn=JDBCUtil.getConn();
			//3.编写sql语句
			String sql="delete from stu where id = ?";
			//4.执行sql语句
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int row = ps.executeUpdate();
			if(row>0){
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.close(conn, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public Student get(Integer id) {
		PreparedStatement ps = null;
		ResultSet res = null;
		Connection conn = null;
		Student stu = null;
		try {
			//2.获得数据库连接
			conn = JDBCUtil.getConn();
			//3.编写sql语句
			String sql="select * from stu where id=?";
			//4.执行sql语句
			ps = conn.prepareCall(sql);
			ps.setInt(1, id);
			res = ps.executeQuery();
			if(res.next()){
				stu =new Student();
				stu.setId(res.getInt("id"));
				stu.setName(res.getString("name"));
				stu.setAge(res.getInt("age"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.close(conn, ps, res);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return stu;
	}

	@Override
	public boolean update(int id, Student stu) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean flag = false;
		try {
			//2.得到连接对象
			conn=JDBCUtil.getConn();
			//3.编写sql语句
			String sql="update stu set name = ? ,age = ? where id = ?";
			ps= conn.prepareStatement(sql);
			ps.setString(1, stu.getName());
			ps.setInt(2, stu.getAge());
			ps.setInt(3, id);
			//4.执行sql语句
			int row = ps.executeUpdate();
			if(row>0){//影响行数大于0时，则返回true
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.close(conn, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public List<Student> getAll() {
		List<Student> stuList=null;
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet res = null;
		try {
			stuList=new ArrayList<Student>();
			//2.获取连接对象
			conn = JDBCUtil.getConn();
			//3.编写sql语句
			String sql="select * from stu";
			//4.执行sql语句
			ps = conn.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()){
				Student stu=new Student();
				stu.setId(res.getInt("id"));
				stu.setName(res.getString("name"));
				stu.setAge(res.getInt("age"));
				stuList.add(stu);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				JDBCUtil.close(conn, ps, res);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return stuList;
	}

domain类

public class Student {
	private int id;
	private String name;
	private int age;
	
	public int getId() {
		return id;
	}
	public void setId(Integer 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(Integer age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
}

抽取JDBCUtil

public class JDBCUtil {
	public final static String URL="jdbc:mysql://localhost:3306/jdbc_db";
	public final static String USER="root";
	public final static String PASSWOED="123456";
	public final static String CONN_NAME="com.mysql.jdbc.Driver";
	
	//加载驱动
	static{
		try {
			Class.forName(JDBCUtil.CONN_NAME);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//获取连接对象
	public static Connection getConn() throws SQLException{
		Connection conn = DriverManager.getConnection(JDBCUtil.URL, JDBCUtil.USER, JDBCUtil.PASSWOED);
		return conn;
	}
	
	//关闭资源
	public static void close(Connection conn,Statement st,ResultSet res) throws SQLException{
		if(conn!=null){
			conn.close();			
		}
		if(st!=null){
			st.close();			
		}
		if(res!=null){
			res.close();			
		}
	}
}