JDBC

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
 
	static String url = null;
	static String name = null;
	static String password = null;
	static String driverClass = null;
	
	static{
		Properties properties = new Properties();
//		java文件编译之后放在bin下，放在src下的jdbc.properties也放在bin下，通过getClassLoader类加载器加载配置文件
		InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//		InputStream is = new FileInputStream("jdbc.properties");文件位于项目根目录下
		driverClass = properties.getProperty("driverClass");
		url = properties.getProperty("url");
		name = properties.getProperty("name");
		password = properties.getProperty("password");
	}
	/**
	 * 注册驱动 建立连接 创建statement 执行sql得到resultset 遍历结果集 释放资源
	 * 
	 * @return
	 */
	public static void select() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			conn = getConn();
			// conn =
			// DriverManager.getConnection("jdbc:mysql://localhost/test","username","password");
//			st = conn.createStatement();
//			String sql = "select * from user where name ='user' and password= 'password'";
//			rs = st.executeQuery(sql);参数中有关键字会出现SQL注入
			String sql = "select * from user where name =? and password= ?";
			ps = conn.prepareStatement(sql);
//			rs = st.executeUpdate(sql);返回影响的行数
			ps.setString(1, "username");
			ps.setString(2, "password");
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(conn, ps, rs);
		}

	}
	public static void test() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			
			conn = getConn();
			// conn =
			// DriverManager.getConnection("jdbc:mysql://localhost/test","username","password");
			st = conn.createStatement();
			String sql = "select * from user";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(conn, st, rs);
		}

	}

	/**
	 * 获取连接
	 * 
	 * @return
	 */
	private static Connection getConn() {
		Connection conn = null;
		try {
			// DriverManager.registerDriver(new com.mysql.jdbc.Driver());//在静态代码块中已经注册，防止重复注册
			Class.forName(driverClass);//jdbc4.0之后会自动注册
//			conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=root");
		    conn = DriverManager.getConnection(url,name,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;

	}

	/**
	 * 释放资源
	 * 
	 * @param conn
	 * @param st
	 * @param rs
	 */
	private static void release(Connection conn, Statement st, ResultSet rs) {
		closeRs(rs);
		closeSt(st);
		closeConn(conn);
	}

	private static void closeRs(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rs = null;
		}
	}

	private static void closeSt(Statement st) {
		try {
			if (st != null) {
				st.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			st = null;
		}
	}

	private static void closeConn(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn = null;
		}
	}
}