Java JDBC 学习笔记

本文是用的数据库为 MySQL

涉及到的知识点如下：

1.连接数据库

 
 
			
  
  //连接数据库
 
 
 
 
			
  
  //加载数据库
 
 
 
 
			
  
  Class
  
  .
  
  forName
  
  (
  
  "com.mysql.jdbc.Driver"
  
  );
 
 
 
 
			
  
  con 
  
  = 
  
  DriverManager
  
  .
  
  getConnection
  
  (
  
  "jdbc:mysql://127.0.0.1:3306/air?useUnicode=true&characterEncoding=UTF-8"
  
  , 
  
  "root"
  
  , 
  
  "root"
  
  );
 
 

2.数据库查找操作

 
 
			 
  
  //执行数据库的查找功能
 
 
 
 
			 
  
  sql 
  
  = 
  
  con
  
  .
  
  prepareStatement
  
  (
  
  "select * from airinfo"
  
  );
 
 
 
 
			 
  
  res 
  
  = 
  
  sql
  
  .
  
  executeQuery
  
  ();
 
 

3.数据库的删除操作

 
 
			 
  
  //删除一条数据
 
 
 
 
			 
  
  sql 
  
  = 
  
  con
  
  .
  
  prepareStatement
  
  (
  
  "DELETE FROM airinfo WHERE air_id = ?"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  setInt
  
  (
  
  1
  
  , 
  
  1
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  executeUpdate
  
  ();
 
 

4.数据库的增加操作

 
 
			 
  
  //增加一条数据
 
 
 
 
			 
  
  sql 
  
  = 
  
  con
  
  .
  
  prepareStatement
  
  (
  
  "INSERT INTO airinfo(air_flight_number,air_destination,air_flight_date) VALUES(?,?,?);"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  setString
  
  (
  
  1
  
  , 
  
  "Test"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  setString
  
  (
  
  2
  
  , 
  
  "BJ"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  setString
  
  (
  
  3
  
  , 
  
  "2014-12-24"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  executeUpdate
  
  ();
 
 

5.数据库的修改操作

 
 
			 
  
  //改
 
 
 
 
			 
  
  sql 
  
  = 
  
  con
  
  .
  
  prepareStatement
  
  (
  
  "update airinfo set air_flight_date = ? WHERE air_id = ?"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  setString
  
  (
  
  1
  
  , 
  
  "2222-02-22"
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  setInt
  
  (
  
  2
  
  ,
  
  5
  
  );
 
 
 
 
			 
  
  sql
  
  .
  
  executeUpdate
  
  ();
6.数据库的预处理语句，这里用增加操作来举例子：

  
  
static PreparedStatement sql;
  
  
   
   
			 
    
    //增加一条数据
   
   
   
   
			 
    
    sql 
    
    = 
    
    con
    
    .
    
    prepareStatement
    
    (
    
    "INSERT INTO airinfo(air_flight_number,air_destination,air_flight_date) VALUES(?,?,?);"
    
    );
   
   
   
   
			 
    
    sql
    
    .
    
    setString
    
    (
    
    1
    
    , 
    
    "Test"
    
    );
   
   
   
   
			 
    
    sql
    
    .
    
    setString
    
    (
    
    2
    
    , 
    
    "BJ"
    
    );
   
   
   
   
			 
    
    sql
    
    .
    
    setString
    
    (
    
    3
    
    , 
    
    "2014-12-24"
    
    );
   
   
   
   
			 
    
    sql
    
    .
    
    executeUpdate
    
    ();
   
   
数据库的预处理是结合通配符完成的，当然前提是要声明一个预处理的变量，这里是 sql
  
  
VALUES(?,?,?) 中有3个问号，这是通配符
  
  
setString(1,"Test") 是指第一个通配符代表的是 Test 字符串
  
  

  
  
代码全文如下，当然数据库需要自己建，可以参考我的另一篇数据库建立博文虽然很渣 嘿嘿~~~：
  
  

  
  

  
  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Test {

	/**
	 * @param args
	 */
	static Connection con;
	static PreparedStatement sql;
	static ResultSet res;
	
	public static void main(String[] args) {
		Test c = new Test();//创建本类对象
		con = c.getConnection();
		try {
			 //要执行 SQL 语句，首先要获得 Statement 类对象
			 sql = con.prepareStatement("select * from airinfo");
			 //执行数据库的查找功能
			 res = sql.executeQuery();
			 System.out.println("执行操作前的数据");
			 while(res.next()){
				 String id = res.getString("air_id");
				 System.out.println("air_id is:"+id);
				 String air_flight_number = res.getString("air_flight_number");
				 System.out.println("air_flight_number is:"+air_flight_number);
				 String air_destination = res.getString("air_destination");
				 System.out.println("air_id is:"+air_destination);
				 String air_flight_date = res.getString("air_flight_date");
				 System.out.println("air_flight_date is:"+air_flight_date);	 
			 }
			 //增加一条数据
			 sql = con.prepareStatement("INSERT INTO airinfo(air_flight_number,air_destination,air_flight_date) VALUES(?,?,?);");
			 sql.setString(1, "Test");
			 sql.setString(2, "BJ");
			 sql.setString(3, "2014-12-24");
			 sql.executeUpdate();
			 
			 //删除一条数据
			 sql = con.prepareStatement("DELETE FROM airinfo WHERE air_id = ?");
			 sql.setInt(1, 1);
			 sql.executeUpdate();
			 
			 //改
			 sql = con.prepareStatement("update airinfo set air_flight_date = ? WHERE air_id = ?");
			 sql.setString(1, "2222-02-22");
			 sql.setInt(2,5);
			 sql.executeUpdate();
			 
			 //执行数据库的查找功能
			 sql = con.prepareStatement("select * from airinfo");
			 res = sql.executeQuery();
			 System.out.println("执行操作后的数据");
			 while(res.next()){
				 String id = res.getString("air_id");
				 System.out.println("air_id is:"+id);
				 String air_flight_number = res.getString("air_flight_number");
				 System.out.println("air_flight_number is:"+air_flight_number);
				 String air_destination = res.getString("air_destination");
				 System.out.println("air_id is:"+air_destination);
				 String air_flight_date = res.getString("air_flight_date");
				 System.out.println("air_flight_date is:"+air_flight_date);	 
			 }
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	//连接数据库的工具类
	private Connection getConnection() {
		try {
			//连接数据库
			//加载数据库
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/air?useUnicode=true&characterEncoding=UTF-8", "root", "root");
			System.out.println("数据库加载成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
		
	}

}