java连接postgresql代码及maven配置
postgresql数据库有默认数据库用户postgres，密码安装库时自己输入；
当然也可以连接其他用户
java连接postgresql代码及maven配置
postgresql数据库有默认数据库用户postgres，密码安装库时自己输入；
当然也可以连接其他用户；
maven依赖
db2依赖
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <scope>provided</scope> </dependency>
连接postgresql的依赖
<dependency> <groupId>postgresql</groupId> <artifactId>postgresql</artifactId> <version>8.2-504.jdbc3</version> </dependency>
Oracle
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.2.0</version> </dependency>
MySQL
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.0.5</version> </dependency>
QL Server
<dependency> <groupId>net.sourceforge.jtds</groupId> <artifactId>jtds</artifactId> <version>1.2</version> </dependency>
java代码：
package com.weimanage.data;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.springframework.context.annotation.Bean;
import javax.sql.DataSource;
import java.util.Properties;
public class getDataSource {
@Bean(name="dataSource")
public static DataSource getDataSource(){
Properties props = new Properties();
props.setProperty("driver","org.postgresql.Driver");
props.setProperty("url","jdbc:postgresql://127.0.0.1:5432/postgres");
props.setProperty("user","postgres");
props.setProperty("password ","1");
DataSource dataSource = null;
try {
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
e.printStackTrace();
}
return dataSource;
}
}Springboo连接数据库通用代码
创建连接并执行业务逻辑
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 * * * * ?") // 每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) {
// oracle
String driver = "oracle.jdbc.driver.OracleDriver";
String sourceURL = "jdbc:oracle:thin:@//110.110.110.110:1521/xiaoqiang";
// sqlserver
//String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//String sourceURL = "jdbc:sqlserver://110.110.110.110:1433;DatabaseName=小强签名设计";
// GaussDB
//String driver = "org.postgresql.Driver";
//String sourceURL = "jdbc:postgresql://110.110.110.110:25308/db_heheda";
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"; //oracle
ResultSet set = null;
try {
Statement stmt = null;
stmt = conn.createStatement();
set = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return set;
}
// 执行文件中的SQL语句
public static void executeFileSqls(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
String[] flieSqls = getSqls("heheda.sql"); // 把该文件放到resources目录下即可，注意文件命名不要用中文
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配置
<!--oracle--> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.1.0</version> </dependency> <!--sqlserver--> <dependency> <groupId>com.huawei.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>1.0.0</version> </dependency> <!--GaussDB--> <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
以上为个人经验，希望能给大家一个参考，也希望大家多多支持好代码网。
