JDBC结合DAO模式的简单实现

JDBC结合DAO模式的简单实现

1. 配置文件

jdbc.properties

2. 与MySQL服务器连接的封装

JDBCUtil

package zhouyu05;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
	
	static String driverString = null;
	static String urlString = null;
	static String user = null;
	static String password = null;
	
	//读取配置文件
	static {
		try {
			Properties prop = new Properties();
			//创建在src文件下
			InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
			prop.load(inputStream);
			driverString = prop.getProperty("driverString");
			urlString = prop.getProperty("urlString");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConn(String databaseName) {
		Connection conn = null;
		try {
			//1. 注册驱动--可以省略
			//静态代码块,类似类加载,一上来就执行
			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			//可以省略,文档有解释,调用getConnection时系统会处理
			//Class.forName(driverString);
			//2. 建立连接
			String url = urlString +databaseName;
			conn = DriverManager.getConnection(url,user,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void release(ResultSet rs, Statement st, Connection conn) {
		closeRs(rs);
		closeSt(st);
		closeConn(conn);
	}
	
	private static void closeRs(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			rs = null;
		}
	}
	
	private static void closeSt(Statement st) {
		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			st = null;
		}
	}
	
	private static void closeConn(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn = null;
		}
	}
}

3. 声明interface

4. 实现implements

package zhouyu05;

import zhouyu05.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class UserDaoImpl implements UserDao {
	@Override
	public void findAll() {
	
	}
	
	@Override
	public void login(String username, String password) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = zhouyu05.JDBCUtil.getConn("zhouyu05");
			//3. 创建statement,和数据库打交道
			String sql = "select *from t_user where username=? and password=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2,password);
			rs = ps.executeQuery();
			//6. 遍历查询结果
			while (rs.next()) {
				String name = rs.getString("username");
				String word = rs.getString("password");
				System.out.println(name +"---" + word);
			}
		} catch (Exception execption) {
			execption.printStackTrace();
		} finally {
			JDBCUtil.release(rs,ps,conn);
		}
	}
	
	@Override
	public void insert(String username, String password) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = zhouyu05.JDBCUtil.getConn("zhouyu05");
			//3. 创建statement,和数据库打交道
			String sql = "insert into t_user values(null,?,?);";
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2,password);
			int result = ps.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (Exception execption) {
			execption.printStackTrace();
		} finally {
			JDBCUtil.release(rs,ps,conn);
		}
	}
	
	@Override
	public void delete(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = zhouyu05.JDBCUtil.getConn("zhouyu05");
			//3. 创建statement,和数据库打交道
			String sql = "delete from t_user where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int result = ps.executeUpdate();
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (Exception execption) {
			execption.printStackTrace();
		} finally {
			JDBCUtil.release(rs,ps,conn);
		}
	}
	
	@Override
	public void update(int id, String name) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = zhouyu05.JDBCUtil.getConn("zhouyu05");
			//3. 创建statement,和数据库打交道
			String sql = "update t_user set username = ? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, id);
			int result = ps.executeUpdate();
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (Exception execption) {
			execption.printStackTrace();
		} finally {
			JDBCUtil.release(rs,ps,conn);
		}
	}
}

5. 测试Test

package zhouyu05;

import org.junit.Test;

public class TestUserDaoImpl {
	
	@Test
	public void testFindAll() {
		UserDao dao = new UserDaoImpl();
		dao.findAll();
	}
	
	@Test
	public void testLogin() {
		UserDao dao = new UserDaoImpl();
		dao.login("admin","13838338888");
	}
	
	@Test
	public void testInsert() {
		UserDao dao = new UserDaoImpl();
		dao.insert("zhouyu","15718804712");
	}
	
	@Test
	public void testDelete() {
		UserDao dao = new UserDaoImpl();
		dao.delete(3);
	}
	
	@Test
	public void testUpdate() {
		UserDao dao = new UserDaoImpl();
		dao.update(2,"wangwu");
	}
}