Java基础--JDBC-HashMap

怼烎@ 2022-07-15 11:38 116阅读 0赞

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"));
    	}
    	}
    }