maven依赖
mysql
mysql-connector-java
5.1.31
1、获取数据库连接 connection
private static String driverName = "com.mysql.jdbc.Driver";
private static String jdbcUrl = "jdbc:mysql://192.168.1.6:3306/10bei_portal";
private static String user = "root";
private static String pwd = "123456";
public static Connection getConnection() {
Connection conn = null;
// 加载驱动
try {
Class.forName(driverName);
conn = DriverManager.getConnection(jdbcUrl, user, pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
2、用获取到的connection对象执行查询sql
//这里是将 sql 的select 字段映射成map的key值
public static List> execQuerySql(String sql) {
Connection conn = getConnection();
if(conn == null) {
System.err.println("获取数据库连接失败,请重试");
return null;
}
List> resultList = new ArrayList<>();
try {
ListcolumnNames = new ArrayList<>();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
int columnCount = rs.getMetaData().getColumnCount();
MapcolTypeMap = new HashMap<>();
//这里的循环是获取select 语句中的 表字段 名称 及 表字段类型
for(int i = 1;irowMap = new HashMap<>();
for(String colName : columnNames ) {
if("INT".equals(colTypeMap.get(colName))) {
rowMap.put(colName, rs.getInt(colName));
continue;
}
if("VARCHAR".equals(colTypeMap.get(colName))) {
rowMap.put(colName, rs.getString(colName));
continue;
}
if("SMALLINT".equals(colTypeMap.get(colName))) {
rowMap.put(colName, rs.getShort(colName));
continue;
}
if("TINYINT".equals(colTypeMap.get(colName))) {
rowMap.put(colName, rs.getShort(colName));
continue;
}
if("DATETIME".equals(colTypeMap.get(colName))) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
rowMap.put(colName, sdf.format(rs.getDate(colName)));
continue;
}
// 这里的getObject 可以获取 int/varchar/datetime等字段类型的值
rowMap.put(colName, rs.getObject(colName));
}
resultList.add(rowMap);
}
rs.close();
ps.close();
return resultList;
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
以上可以获取通用的select语句
局限是此处的sql语句的条件参数没有实现通用的赋值 ^~^ !!!!!,读者这里可以自行补充。。。。。。。