Java基础--JDBC-HashMap

Java基础--JDBC-HashMap

package com.JDBCTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class QuerySession {

	private static final String url = "jdbc:mysql://119.254.106.50:3309/test?useUnicode=true&characterEncoding=utf8";
	private static final String user = "rw_all_db";
	private static final String password = "rw_all_db";
	
	/**
	 * 获取连接
	 */
	public static Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");//静态代码块实例化一个Driver对象
			Connection connection = DriverManager.getConnection(url, user, password);
			return connection;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static void closeConn(Connection connection,PreparedStatement statement,ResultSet rs) {
		try {
			if(rs!=null)rs.close();
			if(statement!=null)statement.close();
			if(connection!=null)connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public static List<HashMap<String, Object>> findData(String sql) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		List<HashMap<String, Object>> users = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			users = new ArrayList<>();
			HashMap<String, Object> hashMap = null;
			
			while (rs.next()) {
				hashMap = new HashMap<>();
				hashMap.put("id", rs.getInt("id"));
				hashMap.put("username", rs.getString("username"));
				hashMap.put("address", rs.getString("address"));
				hashMap.put("birthday", rs.getString("birthday"));
				users.add(hashMap);
			}
			return users;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			closeConn(connection, statement, rs);
		}
	}
	
	public static List<HashMap<String, Object>> searchData(String sql2,String address,String username) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		List<HashMap<String, Object>> users = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(sql2);
			statement.setString(1, address);
			statement.setString(2, "%"+username+"%");
			rs = statement.executeQuery();
			users = new ArrayList<>();
			HashMap<String, Object> hashMap = null;
			while (rs.next()) {
				hashMap = new HashMap<>();
				hashMap.put("id", rs.getInt("id"));
				hashMap.put("username", rs.getString("username"));
				hashMap.put("address", rs.getString("address"));
				hashMap.put("birthday", rs.getString("birthday"));
				users.add(hashMap);
			}
			return users;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			closeConn(connection, statement, rs);
		}
	}
	
	public static HashMap<String, Object> findDataPK(String sql1,Integer id) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(sql1);
			statement.setInt(1, id);
			rs = statement.executeQuery();
			HashMap<String, Object> hashMap = null;
			if (rs.next()) {
				hashMap = new HashMap<>();
				hashMap.put("id", rs.getInt("id"));
				hashMap.put("username", rs.getString("username"));
				hashMap.put("address", rs.getString("address"));
				hashMap.put("birthday", rs.getString("birthday"));
			}
			return hashMap;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			closeConn(connection, statement, rs);
		}
	}
	
	
	
	
	public static void main(String[] args) {
//		String sql = "SELECT id,username,address,birthday FROM user;";
//		List<HashMap<String, Object>> users = findData(sql);
//		for (HashMap<String, Object> hashMap : users) {
//			System.out.println(hashMap.get("id")+"=>"+hashMap.get("username")+"=>"+hashMap.get("address")+"=>"+hashMap.get("birthday"));
//		}
//		
//		String sql1 = "SELECT id,username,address,birthday FROM user where id = ?";
//		HashMap<String, Object> hashMap = findDataPK(sql1, 3);
//		if (hashMap != null) {
//			System.out.println(hashMap.get("id")+"=>"+hashMap.get("username")+"=>"+hashMap.get("address")+"=>"+hashMap.get("birthday"));			
//		}
		
		String sql2 = "SELECT id,username,address,birthday FROM user where address = ? and username like ?";
		List<HashMap<String, Object>> users = searchData(sql2, "北京", "a");
		for (HashMap<String, Object> hashMap : users) {
		System.out.println(hashMap.get("id")+"=>"+hashMap.get("username")+"=>"+hashMap.get("address")+"=>"+hashMap.get("birthday"));
	}
	}
}