# Java with DB SQL Server JDBC by b
---
```
package practice0329;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.FileNotFoundException;
public class main {
static Connection conn = null;
static Statement st;
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
// createTable();
// ArrayList<String[]> a = readFile();
// ArrayList<String[]> b = extractInfo(a);
// insert(b);
// update();
query();
// insert();
// query();
// delete();
// query();
// readFile();
// writeFile("米家有壓哩,AERA\nwhoerlk,你好啊");
// readFile();
}
public static ArrayList<String []> extractInfo(ArrayList<String []> arr) {
if (arr.size() < 1) {
return new ArrayList<>();
}
ArrayList<String []> result = new ArrayList<>();
for(int i = 0; i < arr.size(); i ++) {
String [] line = new String [3];
String x = arr.get(i)[0];
String y = arr.get(i)[1];
String furColor = arr.get(i)[8];
line[0] = x;
line[1] = y;
line[2] = furColor;
result.add(line);
}
return result;
}
public static void createTable() {
conn = getConnection();
try {
String sql = "CREATE TABLE test3("
+ "x float, "
+ "y float, "
+ "furColor varchar(50))";
st = (Statement) conn.createStatement();
int re = ((java.sql.Statement) st).executeUpdate(sql);
System.out.println("re " + re );
conn.close();
} catch (SQLException e) {
System.out.println("新增表格失敗" + e.getMessage());
}
}
public static void insert(ArrayList<String []> arr) {
conn = getConnection();
try {
String sql = "INSERT ALL ";
for(int i = 1 ; i < arr.size(); i ++) {
sql += "INTO test3(x, y, furColor) VALUES ("+ arr.get(i)[0] +","+ arr.get(i)[1] + ",'"+ arr.get(i)[2]+"')";
if(i == 5) {
break;
}
}
sql += "SELECT 1 FROM DUAL";
st = (Statement) conn.createStatement();
int count = ((java.sql.Statement) st).executeUpdate(sql);
System.out.println("向staff表中插入 " + count + " 條資料");
conn.close();
} catch (SQLException e) {
System.out.println("插入資料失敗" + e.getMessage());
}
}
public static void update() {
conn = getConnection();
try {
String sql = "UPDATE test3 set x = "+15+", furColor = 'ORANGE' where x = -73.9561344937861";
st = (Statement) conn.createStatement(); //建立用於執行靜態sql語句的Statement物件，st屬區域性變數
int count = ((java.sql.Statement) st).executeUpdate(sql);
System.out.println("test2表中更新 " + count + " 條資料");
conn.close();
} catch (SQLException e) {
System.out.println("更新資料失敗");
}
}
public static void query() {
conn = getConnection();
try {
String sql = "select x, sum(Y) from test3 group by x having x < 0 order by sum(Y)";
st = (Statement) conn.createStatement(); //建立用於執行靜態sql語句的Statement物件，st屬區域性變數
ResultSet rs = ((java.sql.Statement) st).executeQuery(sql);
System.out.println("最後的查詢結果為：");
ArrayList<String[]> arr = new ArrayList<>();
while (rs.next()) { // 判斷是否還有下一個資料
String [] line = new String[3];
String x = rs.getString("x");
float y = rs.getFloat("sum(Y)");
// String furColor = rs.getString("furColor");
// line[0] = x;
// line[1] = y;
arr.add(line);
System.out.println("x "+x);
System.out.println("y "+y);
// System.out.println("furColor "+furColor);
// // 根據欄位名獲取相應的值
// String name = rs.getString("name");
// int age = rs.getInt("age");
// String sex = rs.getString("sex");
// String address = rs.getString("address");
// String depart = rs.getString("depart");
// String worklen = rs.getString("worklen");
// String wage = rs.getString("wage");
//
// //輸出查到的記錄的各個欄位的值
// System.out.println(name + " " + age + " " + sex + " " + address
// + " " + depart + " " + worklen + " " + wage);
}
conn.close(); //關閉資料庫連線
} catch (SQLException e) {
System.out.println("查詢資料失敗");
}
}
public static void delete() {
conn = getConnection();
try {
String sql = "delete from test2 where x < 1";
st = (Statement) conn.createStatement(); //建立用於執行靜態sql語句的Statement物件，st屬區域性變數
int count = ((java.sql.Statement) st).executeUpdate(sql);
System.out.println("表中刪除 " + count + " 筆資料\n");
conn.close();
} catch (SQLException e) {
System.out.println("刪除資料失敗");
}
}
/* 獲取資料庫連線的函式*/
public static Connection getConnection() {
Connection con = null; //建立用於連線資料庫的Connection物件
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); //Driver name
String url = "jdbc:oracle:thin:@localhost:1521/xe";
String user = "sys as sysdba";
String password = "1234";
con = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
if(con==null){
System.out.println("與oracle資料庫連線失敗！");
}else{
System.out.println("與oracle資料庫連線成功！");
}
return con; //返回所建立的資料庫連線
}
public static ArrayList<String []> readFile() {
FileReader fr;
ArrayList<String []> content = new ArrayList<>();
try {
fr = new FileReader("C:\\Users\\TW0345\\Desktop\\2018_Central_Park_Squirrel_Census_-_Squirrel_Data.csv");
// fr = new FileReader("C:\\Users\\TW0345\\Desktop\\test.csv");
BufferedReader br = new BufferedReader(fr);
String str = "";
while (br.ready()) {
String [] line = br.readLine().split(",");
content.add(line);
// System.out.println(line[8]);
// str += br.readLine() + "\n";
}
fr.close();
// for (int i = 0 ; i< content.size(); i ++) {
// for (int j = 0 ; j< content.get(i).length; j ++) {
// System.out.println(content.get(i)[j]);
// }
//
// }
// writeFile(str);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch(IOException e) {
e.printStackTrace();
}
return content;
}
public static void writeFile(String value) {
FileWriter fw;
try {
fw = new FileWriter("C:\\Users\\TW0345\\Desktop\\test.csv");
BufferedWriter bw = new BufferedWriter(fw);
bw.write(value);
bw.flush();
bw.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
```
Sign in with Wallet
Connect another wallet