前言：
工作中經(jīng)常需要查詢數(shù)據(jù)庫中的數(shù)據(jù),之前一直是通過第三方圖形界面工具來操作的苛预，最近在學(xué)習(xí)java數(shù)據(jù)庫相關(guān)的內(nèi)容，現(xiàn)通過java實(shí)現(xiàn)對數(shù)據(jù)庫的查詢操作祥款。
說明：
該項(xiàng)目是通過maven來構(gòu)建的甜熔，現(xiàn)主要記錄下該項(xiàng)目的整體結(jié)構(gòu)只搁，以及對應(yīng)的代碼，如下：
- 1.項(xiàng)目整體結(jié)構(gòu)圖
- 2.pom.xml中引入依賴
- 3.添加數(shù)據(jù)庫和日志的配置文件
- 4.編寫操作數(shù)據(jù)庫的核心代碼(通過jdbc實(shí)現(xiàn))
- 5.測試結(jié)果
該代碼特點(diǎn)：
查詢出來的數(shù)據(jù)是以字段名組成鍵的鍵值對妖滔，可以直接轉(zhuǎn)換為json數(shù)據(jù)隧哮，如：
- 查詢出來的數(shù)據(jù)格式：
??{total=2, data=[{full_name=null}, {full_name=北京百度有限公司}]} - 轉(zhuǎn)換后的數(shù)據(jù)格式：
??{"total":2,"data":[{"full_name":null},{"full_name":"北京百度有限公司"}]}
項(xiàng)目整體結(jié)構(gòu)圖：
pom.xml中引入依賴：
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.tools</groupId>
<artifactId>operate_db</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<dependencies>
<!-- 解析json -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.39</version>
</dependency>
<!-- 單元測試 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>RELEASE</version>
</dependency>
<!-- hive的jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.1</version>
</dependency>
<!-- postgresql的jdbc -->
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901.jdbc4</version>
</dependency>
<!-- hadoop的組件 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.4.1</version>
</dependency>
<!-- 日志相關(guān) -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.11.2</version>
</dependency>
</dependencies>
<build>
<resources>
<!--表示把java目錄下的有關(guān)xml文件,properties文件編譯/打包的時(shí)候放在resource目錄下-->
<resource>
<directory>${basedir}/src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>${basedir}/src/main/resources</directory>
</resource>
</resources>
</build>
</project>
添加數(shù)據(jù)庫和日志的配置文件：
- 數(shù)據(jù)庫配置文件-DB.properties
# ================ 操作hive庫 =================
# 驅(qū)動(dòng)
#className=org.apache.hive.jdbc.HiveDriver
## 數(shù)據(jù)庫url
#url=jdbc:hive2://127.0.0.1:10000/default
## 用戶名
#username=username
## 密碼
#password=password
# ================ 操作pg庫 =================
# 驅(qū)動(dòng)
className=org.postgresql.Driver
# 數(shù)據(jù)庫url
url=jdbc:postgresql://127.0.0.1:5432/dbname
# 用戶名
username=username
# 密碼
password=password
- 日志配置文件-log4j.properties
# 日志輸出等級
log4j.rootLogger=WARN, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
編寫操作數(shù)據(jù)庫的核心代碼(通過jdbc實(shí)現(xiàn))：
- DBUtils.java
package com.tools.utils;
import java.io.*;
import java.sql.*;
import java.util.*;
public class DBUtils {
private static String className;
private static String url;
private static String username;
private static String password;
static {
//讀取配置文件
try {
// 獲取hive.properties文件的路徑
InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("DB.properties");
Properties prop = new Properties();
prop.load(is);
// 讀取配置文件的值
className = prop.getProperty("className");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//加載驅(qū)動(dòng)
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Map<String, Object> query(String sql) {
Connection conn = null;
PreparedStatement pstsm = null;
ResultSet rs = null;
// 定義map
Map<String, Object> resultMap = new HashMap<String, Object>();
try {
// 獲取連接對象
conn = DriverManager.getConnection(url, username, password);
pstsm = conn.prepareStatement(sql);
rs = pstsm.executeQuery();
// 獲取結(jié)果集的元數(shù)據(jù)信息
ResultSetMetaData rsmd = rs.getMetaData();
// 獲取列字段的個(gè)數(shù)
int colunmCount = rsmd.getColumnCount();
// 存儲列名的數(shù)組
String[] columnNames = new String[colunmCount];
for (int i = 0; i < colunmCount; i++) {
// 獲取所有的字段名稱
columnNames[i] = rsmd.getColumnLabel(i + 1);
}
// 將數(shù)據(jù)存儲到數(shù)據(jù)中
ArrayList<Object> list = new ArrayList<Object>();
while (rs.next()) {
Map<String, Object> perMap = new HashMap<String, Object>();
for (int i = 0; i < colunmCount; i++) {
// 獲取列名
String columnName = columnNames[i];
// 獲取該列對應(yīng)的值
Object value = rs.getObject(columnName);
perMap.put(columnName, value);
}
list.add(perMap);
}
// 計(jì)算數(shù)據(jù)的總數(shù)
int total = list.size();
resultMap.put("data", list);
resultMap.put("total", total);
} catch (Exception e) {
e.printStackTrace();
} finally {
//
close(conn, pstsm, rs);
}
return resultMap;
}
// 關(guān)閉數(shù)據(jù)庫連接
public static void close(Connection conn, PreparedStatement pstsm, ResultSet rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
pstsm.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
測試結(jié)果：
package com.tools.utils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import org.junit.Test;
import java.util.Map;
public class TestHive {
/**
* 查詢數(shù)據(jù)庫數(shù)據(jù)
*/
@Test
public void query() {
// sql語句
String sql = "select * from custom";
// 查詢數(shù)據(jù)
Map<String, Object> data = DBUtils.query(sql);
System.out.println(data);
// 轉(zhuǎn)換為json字符串,WriteMapNullValue:表示保留為null的字段
String result = JSON.toJSONString(data, SerializerFeature.WriteMapNullValue);
System.out.println(result);// {"total":2,"data":[{"full_name":null},{"full_name":"北京百度有限公司"}]}
}
}
喜歡關(guān)注點(diǎn)個(gè)贊！
