一、创建连接并执行业务逻辑:
package com.hui.xiaoqiang;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
@Component
public class ScheduledTask {
private static final Logger logger = LoggerFactory.getLogger(ScheduledTask.class);
@Scheduled(cron="*/10 * * * * ?")
private void process() throws SQLException {
logger.info("开始-->");
try {
Connection conn_gauss = GaussUttils.getConnection("heheda", "123456");
GaussUttils.executeFileSqls(conn_gauss);
ResultSet duowei = GaussUttils.getset_dwfxhz(conn_gauss);
while(duowei.next()){
System.out.println("1--->");
}
conn_gauss.close();
} catch (Exception e) {
e.printStackTrace();
}
logger.info("结束-->");
}
}
二、数据库通用类:
package com.hui.xiaoqiang;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
public class ConnectionUtil {
private static final Logger logger = LoggerFactory.getLogger(ConnectionUtil.class);
public static Connection getConnection(String username, String passwd) {
String driver = "oracle.jdbc.driver.OracleDriver";
String sourceURL = "jdbc:oracle:thin:@//110.110.110.110:1521/xiaoqiang";
Connection conn;
try {
Class.forName(driver).newInstance();
} catch (Exception e) {
e.printStackTrace();
return null;
}
try {
conn = DriverManager.getConnection(sourceURL, username, passwd);
System.out.println("Connection gauss succeed!");
} catch (Exception e) {
e.printStackTrace();
return null;
}
return conn;
};
public static ResultSet getset_dwfxhz(Connection conn){
String sql = "SELECT * FROM xiaoqiang.gr_js where ROWNUM <=5";
ResultSet set = null;
try {
Statement stmt = null;
stmt = conn.createStatement();
set = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return set;
}
public static void executeFileSqls(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
String[] flieSqls = getSqls("heheda.sql");
for (int i = 0; i < flieSqls.length; i++) {
try {
stmt.execute(flieSqls[i]);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("该语句有问题,请排查-->" + flieSqls[i]);
}
}
stmt.close();
} catch (SQLException e) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}
public static String[] getSqls(String filename) {
try {
InputStream io = Thread.currentThread().getContextClassLoader().getResourceAsStream(filename);
InputStreamReader isr = new InputStreamReader(io, "utf-8");
BufferedReader br = new BufferedReader(isr);
String line;
StringBuilder gaussqls = new StringBuilder();
while ((line = br.readLine()) != null) {
if (!line.contains("--")) {
gaussqls.append(line);
gaussqls.append(" ");
}
}
String[] sqls = gaussqls.toString().split(";");
br.close();
return sqls;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}
三、maven配置:
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.1.0</version>
</dependency>
<dependency>
<groupId>com.huawei.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>com.huawei.gaussDb</groupId>
<artifactId>gsjdbc4</artifactId>
<version>1.0.0</version>
</dependency>
注:有的驱动包maven配置好从网上下载不下来,我这里是都已经有个相应个驱动包,然后手动安装的。
如执行以下命令:mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.1.0 -Dpackaging=jar -Dfile=E:\ojdbc6.jar
四、三种方式读取 Mysql 表:
package com.xiaoqiang.utils;
import com.xiaoqiang.database.JDBCConnPool;
import org.apache.spark.api.java.function.MapFunction;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import scala.Tuple4;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class MySqlUtil {
public static Connection getConnection(String configFile) {
Properties properties = PropertiesUtil.getProperties(configFile);
String sourceURL = properties.getProperty("mysql.url");
String username = properties.getProperty("mysql.username");
String passwd = properties.getProperty("mysql.password");
String driver = properties.getProperty("mysql.driver");
Connection conn;
try {
Class.forName(driver).newInstance();
} catch (Exception e) {
e.printStackTrace();
return null;
}
try {
conn = DriverManager.getConnection(sourceURL, username, passwd);
} catch (Exception e) {
e.printStackTrace();
return null;
}
return conn;
};
private static Tuple4<String, String, String, String> getMySQLInfo(String configFile) {
Properties properties = PropertiesUtil.getProperties(configFile);
String url = properties.getProperty("mysql.url");
String user = properties.getProperty("mysql.username");
String password = properties.getProperty("mysql.password");
String driver = properties.getProperty("mysql.driver");
return new Tuple4<>(url, user, password, driver);
}
public static List<String> getListData(String configFile, String tableName, String typeId){
String sql = "SELECT " + typeId + " FROM " + tableName + " group by " + typeId;
ResultSet set = null;
List<String> strs = new ArrayList<String>();
try {
Statement stmt = null;
Connection conn = getConnection(configFile);
stmt = getConnection(configFile).createStatement();
set = stmt.executeQuery(sql);
while(set.next()){
strs.add(set.getString(typeId));
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return strs;
}
public static List<String> getListDataBySpark(SparkSession spark, String configFile, String tableName, String typeId) {
Tuple4<String, String, String, String> mySQLInfo = getMySQLInfo(configFile);
Properties prop = new Properties();
prop.setProperty("user", mySQLInfo._2());
prop.setProperty("password", mySQLInfo._3());
prop.setProperty("driver", mySQLInfo._4());
Dataset<Row> df = spark.read().jdbc(mySQLInfo._1(), tableName, prop);
List<String> collectAsList = df
.selectExpr(typeId).dropDuplicates()
.map((MapFunction<Row, String>) row -> row.mkString(","), Encoders.STRING()).collectAsList();
return collectAsList;
}
public static List<String> getListDataByDbcp(String tableName, String typeId) {
Connection conn = JDBCConnPool.getConnection();
String selectSql = "SELECT " + typeId + " FROM " + tableName + " group by " + typeId;
List<String> resultList = new ArrayList<>();
try {
PreparedStatement ppst = conn.prepareStatement(selectSql);
ResultSet resultSet = ppst.executeQuery();
while (resultSet.next()) {
resultList.add(resultSet.getString(typeId));
}
JDBCConnPool.closeConnection(ppst, conn);
} catch (SQLException e) {
e.printStackTrace();
}
return resultList;
}
public static void main(String[] args) {
}
}
package com.xiaoqiang.database;
import com.xiaoqiang.utils.PropertiesUtil;
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCConnPool {
private static BasicDataSource dataSource;
private static BasicDataSource getDataSource() {
String configFile = String.format("config-%s.properties", "dev");
Properties properties = PropertiesUtil.getProperties(configFile);
if (dataSource == null) {
dataSource = new BasicDataSource();
dataSource.setDriverClassName(properties.getProperty("mysql.driver"));
dataSource.setUrl(properties.getProperty("mysql.url"));
dataSource.setUsername(properties.getProperty("mysql.username"));
dataSource.setPassword(properties.getProperty("mysql.password"));
dataSource.setInitialSize(3);
dataSource.setMinIdle(3);
dataSource.setMaxIdle(100);
dataSource.setRemoveAbandonedTimeout(180);
dataSource.setTestOnReturn(true);
dataSource.setTestOnBorrow(true);
}
return dataSource;
}
private void closeDataSource() {
if (dataSource != null) {
try {
dataSource.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
public static Connection getConnection() {
Connection conn = null;
try {
if (dataSource != null) {
conn = dataSource.getConnection();
} else {
conn = getDataSource().getConnection();
}
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnection(PreparedStatement ps, Connection conn) {
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}