JDBC复习

开发步骤：

1.新建Java项目，项目命名JDBC，JDBC下面两个子目录src和JRE System Library

2.在JDBC项目名下新建lib目录，将classes12.jar包添加到此

3.将鼠标放到JDBC工程名上，右键选择properties，Java Build Path,选择Libraies标签,Add JARS,选择JDBC工程中的classes12.jar文件，OK

4.如何查看.jar文件

先打开解压软件，再打开.jar文件

5.在src中进行编码

使用Statement操作句柄编程示例如下：

package com.useStatement;

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

public class StatementBasic {
		//使用Statement做查询
	public static void main(String[] args) {
		Connection conn =null;
		Statement pstm =null;
		ResultSet rs = null;
		try {
			String sql ="select * from dept";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
		    System.out.println("连接创建");
		    pstm = conn.createStatement();
		    rs = pstm.executeQuery(sql);
		    while(rs.next()) {
		    	int DEPTNO = rs.getInt("DEPTNO");
		    	String DNAME = rs.getString("DNAME");
		    	String LOC  = rs.getString("LOC");
		    	System.out.println(DEPTNO+":"+DNAME+":"+LOC+":");
		    }
		    
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		
	}

}

package com.useStatement;

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

public class StatementBasic2 {
//使用Statement做新增
	public static void main(String[] args) {
		Connection conn=null;
		Statement pstm =null;
		try {
			String sql ="insert into emp values (2228,'小米','律师',7788,to_date('1999-2-2','yyyy-mm-dd'),10000,100,20)";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection
					("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
			pstm = conn.createStatement();
			int len = pstm.executeUpdate(sql);
			if(len >0) {
				System.out.println("新增成功");
			}else {
				System.out.println("新增失败");
			}
			
			

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(pstm !=null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}

}

使用preparedStatement做新增

package com.usepreparedStatement;

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

public class PrepareadStatmentBasic {
	public static void main(String[] args) {
		
		Connection conn= null;
		PreparedStatement pstm = null;
		
		String sql = "insert into stu values(11,'123','a')";
	
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection
						("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
				//3.获取预编译操作句柄
				pstm = conn.prepareStatement(sql);
				pstm.executeUpdate();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}finally {
				if(pstm !=null) {
					try {
						pstm.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}if(conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}

	}

}

package com.usepreparedStatement;

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

public class PreparedStatementBasic2 {
//使用PreparedStatement 做查询
	public static void main(String[] args) {
		Connection conn= null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql = "select * from dept";
	
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection
						("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
				//3.获取预编译操作句柄
				pstm = conn.prepareStatement(sql);
				pstm.executeQuery();
				 rs = pstm.executeQuery(sql);
				while(rs.next()) {
			    	int DEPTNO = rs.getInt("DEPTNO");
			    	String DNAME = rs.getString("DNAME");
			    	String LOC  = rs.getString("LOC");
			    	System.out.println(DEPTNO+":"+DNAME+":"+LOC+":");
			    }
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}finally {
				if(pstm !=null) {
					try {
						pstm.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}if(conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}

	}

}

mysql连接

导入mysql-connector-java-8.0.11.jar

package com;

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

public class mysql {
	static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost:3306/test1?useSSL=false&serverTimezone=UTC";
	static final String USER = "root";
	static final String PASS = "123456";
	
	public static void main(String[] args) {
		
	
		 // 注册 JDBC 驱动
	    try {
			Class.forName(JDBC_DRIVER);
			  // 打开链接
		    System.out.println("连接数据库...");
		    Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
		    // 执行查询
            System.out.println(" 实例化Statement对象...");
           Statement stmt = conn.createStatement();
            String sql;
            sql = "SELECT * from t";
            ResultSet rs = stmt.executeQuery(sql);
        
            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索
              
                String name = rs.getString("username");
                String url = rs.getString("passwd");
                // 输出数据
                
                System.out.print(", 名称: " + name);
                System.out.print(", password: " + url);
                System.out.print("\n");
            }
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	  

	}
}