---
# System prepended metadata

title: Java with DB SQL Server JDBC by b

---

# 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();
		}
	}

}


```