JDBC_DAO的增删改查二

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/student
name=root
password=123456

创建接口（父类引用指向子类对向）提高代码的复用性（灵活性） 

package com.ny.dao;

public interface UserDao {
	/*
	 * 更新数据
	 */
	void update(int id,String password);
	/*
	 * 删除数据
	 */
	void delete(int id);	
	/*
	 * 插入数据
	 * 要带参数
	 */
	void insert(String username,String password);
	
	/*
	 * 查询所有的数据
	 */
	 void findAll();
}

 实现接口重写接口里面的所有方法

PrepareStatement

 该对象就是替换前面（JDBC_DAO的增删改查一）的statement对象。

1. 相比较以前的statement， 预先处理给定的sql语句，对其执行语法检查。 在sql语句里面使用 ? 占位符来替代后续要传递进来的变量。 后面进来的变量值，将会被看成是字符串，不会产生任何的关键字。

package com.ny.dao.implement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import com.ny.dao.UserDao;
import com.ny.util.MysqlDemoUtil;

public class UserDaoImp implements UserDao{

	@Override
	public void findAll() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//建立连接
			con = MysqlDemoUtil.getCon();
			//创建parperstatement
			String sql = "select * from user";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString("username");
				String password = rs.getString("password");
				
				System.out.println(name + " " + password);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDemoUtil.closeAll(rs, ps, con);
		}
		
	}
	//插入操作
	@Override
	public void insert(String username, String password) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			//建立连接
			con = MysqlDemoUtil.getCon();
			//
			String sql = "insert into user values(null,?,?)";
			ps = con.prepareStatement(sql);
			
			ps.setString(1, username);
			ps.setString(2, password);
			
			int i = ps.executeUpdate();
			if(i > 0) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDemoUtil.closeAll(ps, con);
		}
		
	}
	//删除操作
	public void delete(int id) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			//建立连接
			con = MysqlDemoUtil.getCon();
			//创建parpertatementt
			String sql = "delete from user where id = ?";
			ps = con.prepareStatement(sql);
			
			ps.setInt(1, id);
			int i = ps.executeUpdate();
			if(i > 0) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDemoUtil.closeAll(ps, con);
		}
	}
	
	//更新操作
	public void update(int id, String password ) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			//创建连接
			con = MysqlDemoUtil.getCon();
			//创建parpersattement
			String sql = "update user set password = ? where id = ?";
			ps = con.prepareStatement(sql);
			
			ps.setString(1, password);
			ps.setInt(2, id);
			
			int i = ps.executeUpdate();
			
			if(i > 0) {
				System.out.println("更新成功");
			}else {
				System.out.println("更新失败");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MysqlDemoUtil.closeAll(ps, con);
		}
		
	}
}

创建测试单元 

添加junit的支持    右键工程 --- add Library --- Junit --- Junit4

import org.junit.Test;

import com.ny.dao.UserDao;
import com.ny.dao.implement.UserDaoImp;

public class TestUserDaoImp {
//	@Test
	public void testFindAll() {
		UserDao ud = new UserDaoImp();
		ud.findAll();
	}
//	@Test
	public void testInsert() {
		UserDao ud = new UserDaoImp();
		ud.insert("刘德华", "123456");
	}
//	@Test
	public void testDelete() {
		UserDao ud = new UserDaoImp();
		ud.delete(4);
	}
	@Test
	public void testUpdate() {
		UserDao ud = new UserDaoImp();
		ud.update(1, "12345678900");
	}

}

 创建JDBC的工具类

package com.ny.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class MysqlDemoUtil {
	//static关键字修饰的变量供所有实例共享
	static String driverClass = null;
	static String url = null;
	static String name = null;
	static String password = null;	
	static {
		try{
			//创建属性配置对象
		Properties pro = new Properties();
		//使用类加载器，去读取src底下的资源文件。 后面在servlet
//		InputStream is = MysqlDemoUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
		InputStream is = new FileInputStream("jdbc.properties");
		 //导入输入流
		pro.load(is);
		
		//读取属性
		driverClass = pro.getProperty("driverClass");
		url = pro.getProperty("url");
		name = pro.getProperty("name");
		password = pro.getProperty("password");
		}catch (Exception e) {
				e.printStackTrace();
		}					
	}
	
	//获取连接对象
	
	public static Connection getCon() {
		Connection con = null;
		try {
			//加载驱动程序
			Class.forName(driverClass );
			//建立连接
			con = DriverManager.getConnection(url,name,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con ;
	}	
	
	//这样做提高了代码的复用性  是程序变得更加灵活
	public static void closeAll(ResultSet rs,Statement st,Connection con) {
		rsClose(rs);
		stClose(st);
		conClose(con);
	}
	
	public static void closeAll(Statement st,Connection con) {
		stClose(st);
		conClose(con);
	}
	
	
	public static void rsClose(ResultSet rs) {
		try {
			if(rs != null) {
			rs.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void stClose(Statement st) {
		try {
			if(st != null) {
			st.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void conClose(Connection con) {

		try {
			if(con != null) {
			con.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}
}