主要是登录的问题,一开始是使用 Process p = Runtime.getRuntime().exec( "psql -U username -d dbname -h serverhost -f scripfile.sql"); 尝试在代码中执行export PGPASSWORD=test、创建.pgpass文件、利用p.getOutputStream()对象流输入密码,但效果并不理想,之后发现了这方法,完美解决了我这边的需求,写下来做个记录。
这里利用ProcessBuilder执行psql,通过ProcessBuilder.environment().put()设置环境变量,psql登录时从PGPASSWORD环境变量读取密码,从而不弹出密码输入提示。
import java.io.*;
import java.util.ArrayList;
import java.util.List;
public class Main {
public static void main(String[] args) {
String sql = "drop table table_name1;\n" +
"INSERT INTO public.table_name2 (id, username) VALUES (DEFAULT, 'user');\n" +
"UPDATE public.table_name2 set username = 'xiaoming' where id = 1;";
Execute e = new Execute();
e.sqlExec(sql);
}
}
class Execute {
// 将sql语句写入文件
public String createSqlFile(String sql) {
String sqlFile = "";
try {
File file = File.createTempFile("test", "sql");
FileWriter writer = new FileWriter(file, false);
writer.append(sql);
writer.flush();
// 退出JVM时删除该临时文件
file.deleteOnExit();
sqlFile = file.getAbsoluteFile().toString();
} catch (IOException e) {
e.printStackTrace();
}
return sqlFile;
}
public void sqlExec(String sql) {
String sqlFile = this.createSqlFile(sql);
// 生成psql命令
List commandList = new ArrayList();
commandList.add("psql");
// host
commandList.add("-h");
commandList.add("127.0.0.1");
// username
commandList.add("-U");
commandList.add("test");
// database
commandList.add("-d");
commandList.add("test");
// port
commandList.add("-p");
commandList.add("5432");
// sql file
commandList.add("-f");
commandList.add(sqlFile);
// 不弹出密码输入行
commandList.add("-w");
ProcessBuilder pd = new ProcessBuilder(commandList);
// 从环境变量读取数据库密码
pd.environment().put("PGPASSWORD", "test");
try {
Process p = pd.start();
String line;
BufferedReader input = new BufferedReader(new InputStreamReader(p
.getInputStream()));
while ((line = input.readLine()) != null) {
if (line.equals("")) {
continue;
}
// 将控制台执行结果输出
System.out.println(line);
}
input.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}