登陆功能实现（二）

• 新建工具类

package com.xiaowang.login.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Connection;

public class ConnectionUtils {
	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;	
	private static Properties props = new Properties();
	
	//ThreadLocal:保证线程中只能有一个连接
	private static ThreadLocal<Connection> t1 = new ThreadLocal<>();
	/**
	 * 静态代码块读取db.properits
	 */
	static {
		//类加载器读取文件
		InputStream in = 
				ConnectionUtils.class.getClassLoader().getResourceAsStream("db.properits");
		try {
			props.load(in);
			
			driver = props.getProperty("jdbc.driver");
			url = props.getProperty("jdbc.url");
			username = props.getProperty("jdbc.username");
			password = props.getProperty("jdbc.password");
			
			Class.forName(driver);
						
		}  catch (Exception e) {
			e.printStackTrace();
		}					
	}
	/**
	 * 获取连接方法
	 * @throws Exception 
	 */
	public static Connection getConn() throws Exception {
		//先尝试从t1中获取
		Connection conn = t1.get();
		if(conn == null) {
			conn = (Connection) DriverManager.getConnection(url, username, password);
			t1.set(conn);
		}		
		return conn;
		
	}
	
	/**
	 * 关闭连接方法
	 */
	
	public static void closeConn() throws Exception{
		//先尝试从t1中获取
		Connection conn = t1.get();
		if(conn != null) {
			conn.close();
		}
		t1.set(null);
	}
	
	
}

• 把实现类中代码替换成工具类连接数据库

package com.xiaowang.login.dao;

import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.xiaowang.login.beans.User;
import com.xiaowang.login.utils.ConnectionUtils;

public class UserDaoIml implements UserDao {

	@Override
	public User getUserByUsernameAndPassword(String username, String password) {
		User u = new User();
		// JDBC:获取连接 编写SQL语句 预编译SQL 设置参数 封装结果 关闭连接
		// 获取连接
		try {
			/*
			 * Class.forName("com.mysql.jdbc.Driver"); String url =
			 * "jdbc:mysql://localhost:3306/bigdata"; String usr = "root"; String passwd =
			 * "root"; java.sql.Connection conn = DriverManager.getConnection(url, usr,
			 * passwd);
			 */
			Connection conn = null;
			try {
				conn = ConnectionUtils.getConn();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				String sql = "select id,username,password from tb_user where username=? password=?";
				PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);

				ps.setString(1, username);
				ps.setString(2, password);

				ResultSet rs = (ResultSet) ps.executeQuery();

				if (rs.next()) {
					u.setID(rs.getInt("id"));
					u.setUsername(rs.getString("username"));
					u.setPassword(rs.getString("password"));

				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		} finally {
			try {
				ConnectionUtils.closeConn();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return u;
	}

}