java jdbc map_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> findData(String sql) {

Connection connection = null;

PreparedStatement statement = null;

ResultSet rs = null;

List> users = null;

try {

connection = getConnection();

statement = connection.prepareStatement(sql);

rs = statement.executeQuery();

users = new ArrayList<>();

HashMap 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> searchData(String sql2,String address,String username) {

Connection connection = null;

PreparedStatement statement = null;

ResultSet rs = null;

List> users = null;

try {

connection = getConnection();

statement = connection.prepareStatement(sql2);

statement.setString(1, address);

statement.setString(2, "%"+username+"%");

rs = statement.executeQuery();

users = new ArrayList<>();

HashMap 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 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 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> users = findData(sql);

//for (HashMap 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 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> users = searchData(sql2, "北京", "a");

for (HashMap hashMap : users) {

System.out.println(hashMap.get("id")+"=>"+hashMap.get("username")+"=>"+hashMap.get("address")+"=>"+hashMap.get("birthday"));

}

}

}