数据仓库与数据挖掘课设

基础必做题：

Java实现数据库抽取转化到数据仓库。
教材：《数据仓库与数据挖掘应用教程》李春葆 -清华大学出版社

---

准备内容：

数据库，Java开发环境，数据库中有相应的数据
Java在创建项目时采用lib为1.7版本!

配置数据库连接：具体过程查看下面教程
连接数据库

下载相应的JDBC，并将相应的包导入Java创建的lib中；

代码如下：

package DB_Process;

import java.sql.*;//主要使用，Connection、DriverManager、ResultSet、Statement来进行和数据库打交道

public class DataExtraction {
   

	public static void main(String [] args){
   
		
		try{
   
			String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";//显示加载数据库
			Class.forName(driverName);
			String dbURL_Src="jdbc:sqlserver://localhost:1433;DatabaseName=";
			String userName="sa";
			String userPwd="wang1102";
			Connection connecteSrc= DriverManager.getConnection(dbURL_Src+"OnRet",userName,userPwd);//创建连接OnRet对象
			Connection connecte= DriverManager.getConnection(dbURL_Src+"SDWtest",userName,userPwd);//创建连接SDW对象
			Statement stSrc = connecteSrc.createStatement();//获得对象,声明执行SQL语句对象
			Statement st = connecte.createStatement();//获得对象,声明执行SQL语句对象
			
			String sqlcreate = "create table Dates(Date_key INT,日期 CHAR(25),年份 CHAR(10),月份 CHAR(20),季度 CHAR(30),"
					+ "PRIMARY KEY(Date_key))";
			int resultcraet = st.executeUpdate(sqlcreate);//创建DATES表
			
			sqlcreate ="create table Age(Age_key INT,年龄 INT,年龄层次 CHAR(10),PRIMARY KEY(Age_key))";
			resultcraet = st.executeUpdate(sqlcreate);//创建AGE表
			
			sqlcreate = "create table Education(Educ_key INT,学历 CHAR(10),学历层次 CHAR(10),PRIMARY KEY(Educ_key))";
			resultcraet = st.executeUpdate(sqlcreate);//创建Education表
			
			sqlcreate = "create table Locates(Locate_key INT,地区 CHAR(25),省份 CHAR(10),市 CHAR(20),县 CHAR(10),"
					+ "PRIMARY KEY(Locate_key))";
			resultcraet = st.executeUpdate(sqlcreate);//创建Locates表
			
			sqlcreate = "create table Products(Prod_key INT,分类 CHAR(20),子类 CHAR(20),品牌 CHAR(20),"
					+ "PRIMARY KEY(Prod_key))";
			resultcraet = st.executeUpdate(sqlcreate);//创建Products表
			
			sqlcreate = "create table Sales(Date_key INT, Age_key INT, Educ_key INT,"
					+ "Locate_key INT,Prod_key INT,数量 INT, 金额 INT,"
					+ "PRIMARY KEY(Date_key,Age_key,Educ_key,Locate_key,Prod_key),"
					+ "FOREIGN KEY(Date_key) REFERENCES Dates(Date_key),"
					+ "FOREIGN KEY(Age_key) REFERENCES Age(Age_key),"
					+ "FOREIGN KEY(Educ_key) REFERENCES Education(Educ_key),"
					+ "FOREIGN KEY(Locate_key) REFERENCES Locates(Locate_key),"
					+ "FOREIGN KEY(Prod_key) REFERENCES Products(Prod_key))";
			resultcraet = st.executeUpdate(sqlcreate);//创建核心表
	
			String sql="select Sales.日期, Customers.年龄 ,Customers.学历,Customers.地区,Customers.省份,Customers.市,Customers.县,Sales.分类,"
					+ "Sales.子类,Sales.品牌,SUM(Sales.数量) AS 数量, SUM(Sales.金额) AS 金额 "
					+ "FROM Customers,Sales "
					+ "WHERE Customers.用户名 = Sales.用户名"
					+ " GROUP BY Sales.日期, Customers.年龄,Customers.学历,Customers.地区,Customers.省份,Customers.市,Customers.县,Sales.分类, Sales.子类, Sales.品牌";/*SQL命令*/
			ResultSet result = stSrc.executeQuery(sql);//执行select语句，返回的单个对象，连接表
			
			String time, Age, educ, dq, sf, cs, xm, fl, zl, pp, sl, jr;
			while(result.next()) {
   
				time = result.getString("日期");
				int Date_number = ProcessDates(time,st);
				Age	= result.getString("年龄");
				int Age_number = ProcessAge(Age,st);				
				educ = result.getString("学历");
				int Educ_number = ProcessEduc(educ.trim(),st);
				dq = result.getString("地区");
				sf = result.getString("省份");
				cs = result.getString(