Java JDBC 小例子

使用MySQL建立一個test數據庫，里面建立一個mytable表，3列（id，name，age）；將url, user, password配制成properties文件，放到 工程resource源代碼包下面，這里將其命名為db_connect.properties文件

    url = jdbc:mysql://localhost:3306/test  
 user = root  
 password = admin  

    package study.jdbc;

import java.io.FileInputStream;

 import java.io.FileNotFoundException;

 import java.io.IOException;

 import java.io.InputStream;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.SQLException;

 import java.util.Properties;
public class DBConnect {

 static String url;  
 static String user;  
 static String password;  

 /** 
  * 獲取一個JDBC連接，返回一個Connection對象 
  * @return connection 
  */  
 public static Connection connectDB() {  
     Connection connection = null;  
     readProperties();  
     try {  
         Class.forName("com.mysql.jdbc.Driver");  
         connection = DriverManager.getConnection(url, user, password);  
     } catch (SQLException e) {  
         e.printStackTrace();  
     } catch (ClassNotFoundException e) {  
         e.printStackTrace();  
     }  
     return connection;  
 }  

 /** 
  * 讀取properties文件，獲取url,user,password 
  */  
 private static void readProperties() {  
     String fileName = "resouce/db_connect.properties"; //相對于工程  
     Properties properties = new Properties();  
     try {  
         InputStream in = new FileInputStream(fileName);  
         properties.load(in);  
         in.close();  
     } catch (FileNotFoundException e) {  
         e.printStackTrace();  
     } catch (IOException e) {  
         e.printStackTrace();  
     }  
     url = properties.getProperty("url");  
     user = properties.getProperty("user");  
     password = properties.getProperty("password");  
 }  


}  </pre> 

 3.  對數據庫進行增刪改查的測試，主要練習使用PreparedStatement 
    package study.jdbc;

import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;
public class TestMain {

 public static void main(String[] args) {  
     Connection connection = DBConnect.connectDB(); //獲取數據庫連接  
     TestMain test = new TestMain();  
     try { //測試  
         test.clear(connection);   
         test.insert(connection); //增  
         test.query(connection);  
         System.out.println("----------");  
         test.delete(connection); //刪    
         test.query(connection);    
         System.out.println("----------");  
         test.update(connection); //改  
         test.query(connection);  //查  
         connection.close(); //關閉數據庫連接  
     } catch (SQLException e) {  
         e.printStackTrace();  
     }  
 }  

 /** 
  * 使用PreparedStatement，效率高 
  * 動態執行SQL(帶參數的SQL語句),是Statement子接口 
  * 對數據庫進行insert，用帶參數的語句批量插入 
  * @param connection 
  * @throws SQLException 
  */  
 public void insert(Connection connection) throws SQLException {  
     String sql = "INSERT INTO mytable(id,name,age) values (?,?,22);";  
     PreparedStatement pr = connection.prepareStatement(sql);  
     for (int i = 1; i <= 3; i++) {  
         pr.setInt(1, i);  
         pr.setString(2, "demo"+i);  
         pr.executeUpdate();  
     }  
 }  

 /** 
  * 對數據庫進行delete 
  * @param connection 
  * @throws SQLException 
  */  
 public void delete(Connection connection) throws SQLException {  
     String sql = "delete from mytable where id=2;";  
     PreparedStatement pr = connection.prepareStatement(sql);  
     pr.executeUpdate();  
 }  

 /** 
  * 對數據庫進行update 
  * @param connection 
  * @throws SQLException 
  */  
 public void update(Connection connection) throws SQLException {  
     String sql = "UPDATE mytable SET name='new' WHERE id=1;";  
     PreparedStatement pr = connection.prepareStatement(sql); // 創建statement對象發送SQL到數據庫  
     pr.executeUpdate(); // 執行UPDATE  

 }  

 /** 
  * 查詢query遍歷結果集 
  * @param connection 
  * @throws SQLException 
  */  
 public void query(Connection connection) throws SQLException {  
     String sql = "select * from mytable;";  
     PreparedStatement st = connection.prepareStatement(sql);  
     ResultSet re = st.executeQuery(); // 查詢，返回單個ResultSet對象  
     while (re.next()) {  
         int id = re.getInt(1);  
         String name = re.getString(2);  
         int age = re.getInt(3);  
         System.out.println(id + "\t" + name + "\t" + age);  
     }// 遍歷結果集  
 }  

 /** 
  * 清空表 
  * @param connection 
  * @throws SQLException 
  */  
 public void clear(Connection connection) throws SQLException {  
     String sql = "delete from mytable;";  
     PreparedStatement pr = connection.prepareStatement(sql);  
     pr.executeUpdate();  
 }  

}  </pre>